[Solved]: Database query error (?)

[aquiros]

Hello, I've been using C1.2 and now 1.3 with success. But just now something weird happened.
It started when I tried to upload a pic. It came back with " database query error..."
I read some of the posts about this and found out how to use the Admin Tools to get the Debug info. Please help me out cuz I have no big experience with mySql or php (but I now my way around a computer and can follow instructions pretty good)


***********************************
USER:
------------------
Array
(
   [ID] => f3a8d8544875fe87a8087f6948d762b6
   [am] => 1
   [liv] => Array
       (
           

• => 163
             [1] => 165
             [2] => 144
             [3] => 175
             [4] => 177
         )
  
     [uid] => 10
  )
  
  ==========================
  USER DATA:
  ------------------
  Array
  (
     [user_id] => 2
     [user_group] => 1
     [user_active] => YES
     [user_name] => aquiros
     [user_password] => ********
     [user_lastvisit] => 2004-07-23 23:45:48
     [user_regdate] => 2004-03-14 17:47:43
     [user_group_list] =>
     [user_email] => aquiros@costaricaaviation.com
     [user_website] => http://www.costaricaaviation.com
     [user_location] => Miami, FL
     [user_interests] =>
     [user_occupation] => Flight Instructor
     [user_actkey] =>
     [disk_max] => 0
     [disk_min] => 0
     [can_rate_pictures] => 1
     [can_send_ecards] => 1
     [ufc_max] => 1
     [ufc_min] => 1
     [custom_user_upload] => 0
     [num_file_upload] => 10
     [num_URI_upload] => 1
     [can_post_comments] => 1
     [can_upload_pictures] => 1
     [can_create_albums] => 1
     [has_admin_access] => 1
     [pub_upl_need_approval] => 0
     [priv_upl_need_approval] => 0
     [group_name] => Administrators
     [upload_form_config] => 1
     [group_quota] => 0
     [can_see_all_albums] => 1
     [group_id] => 1
     [groups] => Array
         (
             [1] => 1
         )
  
  )
  
  ==========================
  Queries:
  ------------------
  Array
  (
     
• => SELECT extension, mime, content FROM cpg11d_filetypes;
     [1] => SELECT * FROM cpg11d_users WHERE user_id='2'AND user_active = 'YES' AND user_password != '' AND BINARY MD5(user_password) = 'eb6d408e8dedf0c7a4619f91ee8f53c9'
     [2] => SELECT MAX(group_quota) as disk_max, MIN(group_quota) as disk_min, MAX(can_rate_pictures) as can_rate_pictures, MAX(can_send_ecards) as can_send_ecards, MAX(upload_form_config) as ufc_max, MIN(upload_form_config) as ufc_min, MAX(custom_user_upload) as custom_user_upload, MAX(num_file_upload) as num_file_upload, MAX(num_URI_upload) as num_URI_upload, MAX(can_post_comments) as can_post_comments, MAX(can_upload_pictures) as can_upload_pictures, MAX(can_create_albums) as can_create_albums, MAX(has_admin_access) as has_admin_access, MIN(pub_upl_need_approval) as pub_upl_need_approval, MIN( priv_upl_need_approval) as  priv_upl_need_approval FROM cpg11d_usergroups WHERE group_id in (1)
     [3] => SELECT group_name FROM  cpg11d_usergroups WHERE group_id= 1
     [4] => DELETE FROM cpg11d_banned WHERE expiry < 1090644764
     [5] => SELECT * FROM cpg11d_banned WHERE ip_addr='66.32.91.208' OR ip_addr='66.32.91.208' OR user_id=2
     [6] => SELECT cid, name, description, thumb FROM cpg11d_categories WHERE parent = ''  ORDER BY pos
     [7] => SELECT aid FROM cpg11d_albums WHERE category = 8
     [8] => SELECT count(*) FROM cpg11d_pictures, cpg11d_albums WHERE cpg11d_pictures.aid = cpg11d_albums.aid AND category = 8
  )
  
  ==========================
  GET :
  ------------------
  Array
  (
  )
  
  ==========================
  POST :
  ------------------
  Array
  (
  )
  
  ==========================
  VERSION INFO :
  ------------------
  PHP version: 4.3.6 - OK
  ------------------
  mySQL version: 4.0.18-standard
  ==========================
  Module: gd
  ------------------
  GD Support enabled
  GD Version bundled (2.0.22 compatible)
  FreeType Support enabled
  FreeType Linkage with freetype
  GIF Read Support enabled
  JPG Support enabled
  PNG Support enabled
  WBMP Support enabled
  XBM Support enabled
  ==========================
  Module: mysql
  ------------------
  Active Persistent Links 0
  Active Links 1
  Client API version 3.23.49
  MYSQL_MODULE_TYPE builtin
  MYSQL_SOCKET /var/tmp/mysql.sock
  MYSQL_INCLUDE no value
  MYSQL_LIBS no value
  ==========================
  Module: zlib
  ------------------
  ZLib Support enabled
  Compiled Version 1.1.4
  Linked Version 1.1.4
  ==========================
  Server restrictions (safe mode)?
  ------------------
  Directive | Local Value | Master Value
  safe_mode | Off | Off
  safe_mode_exec_dir | no value | no value
  safe_mode_gid | Off | Off
  safe_mode_include_dir | no value | no value
  safe_mode_exec_dir | no value | no value
  sql.safe_mode | Off | Off
  disable_functions | no value | no value
  file_uploads | On | On
  include_path | .:/usr/lib/php:/usr/local/lib/php | .:/usr/lib/php:/usr/local/lib/php
  open_basedir | /home/aquiros:/usr/lib/php:/usr/local/lib/php:/tmp | no value
  ==========================
  email
  ------------------
  Directive | Local Value | Master Value
  sendmail_from | no value | no value
  sendmail_path | /usr/sbin/sendmail -t -i | /usr/sbin/sendmail -t -i
  SMTP | localhost | localhost
  smtp_port | 25 | 25
  ==========================
  Size and Time
  ------------------
  Directive | Local Value | Master Value
  max_execution_time | 30 | 30
  max_input_time | -1 | -1
  upload_max_filesize | 2M | 2M
  post_max_size | 55M | 55M
  ==========================
  Page generated in 0.073 seconds - 9 queries in 0.002 seconds - Album set :
  
  
  ************************************************************************
  
  And here;s the ERROR:
  
  
  
  While executing query "SELECT count(*) FROM cpg11d_pictures, cpg11d_albums WHERE cpg11d_pictures.aid = cpg11d_albums.aid AND category = 8" on 0
  
  mySQL error: Can't open file: 'cpg11d_pictures.MYI'. (errno: 144)
  **********************************************************************
  
  Thanks in advance for any help.
  
  Andre

[kegobeer]

Your table(s) may be corrupted.  Try repairing the cpg11d_pictures table.  Using phpmyadmin, tick the checkbox next to the table name, and in the drop down box (it says "With selected:") select Repar table.

[aquiros]

Hi, Thanks. I tried what you suggested and I got the following error (on phpMyAdmin):

*************************
SQL result

Host: localhost
Database : aquiros_copper1
Generation Time: Jul 24, 2004 at 12:25 AM
Generated by: phpMyAdmin 2.5.6-rc1 / MySQL 4.0.18-standard
SQL-query: REPAIR TABLE `cpg11d_pictures` ;
Rows: 3
Table                                                 Op    Msg_type        Msg_text
aquiros_copper1.cpg11d_pictures    repair         error               28 when fixing table
aquiros_copper1.cpg11d_pictures    repair         error               Can't copy datafile-header to tempfile, error 9
aquiros_copper1.cpg11d_pictures    repair         status              Operation failed

******************************************

Is there something else I can try? or is there any other info about the problem or the gallery that you might need and I'm not putting here? Thanks for your help.

Andre

[kegobeer]

You definitely have a corrupted table.  Sounds like you need root access to do the repair.  At this point I recommend you ask your host to do the repairs for you.  You can ask them to upgrade to the latest version of phpmyadmin, too (2.5.7).

[aquiros]

Thanks kegobeer, I've put the requests with my host. Hopefully they can do something. I'll let you know the results and if it worked...now, what happens if that doesn't work? Thanks.

Andre

[kegobeer]

If a repair isn't possible, you'll have to create new tables.  You'll have to export the data (not the structure), drop your tables, create new tables, and import your data.  The easiest way is to use phpmyadmin to export the data from all your tables, then delete the install.lock file, run install.php again, then use phpmyadmin to import the data back into the tables.

[aquiros]

Hello,....I haven't done anything but my gallery seems to work now (maybe the host did it) and I can see the pics and browse thru the gallery. BUT now I cannot upload pictures. 
I get the "0 pictures Uploaded succesfully" message. I search on the Support boards and checked some of the posts.
- I do have a /temp and /tmp folders at the root with 777 , and the /albums, /edit and /userpics are also 777 (which according to my host are to enable the write for everybody)
- I'm trying to upload jpgs with a max size of 1024, and it worked fine before all the problems started.
any pointers? THank you.

Andre  :-\\

[Joachim Müller]

try uploading a known good jpg with small resolution and file size. Please post a link and test user account (non-admin) with upload privileges.

GauGau

[aquiros]

Thanks, I tried with a 172kb, 800 pixels, good file and still "0 Uploads successfull".

http://www.costaricaaviation.com/photos

Username: test   
Pass: test

Do you want me to turn the debug on for everyone so you can see it? If you need something else let me know.
Thank you.

Andre

[Joachim Müller]

I was able to upload just fine (the pic needs admin approval though).

GauGau

[Tarique Sani]

You had not run update.php - I just ran it for you - please try again!

[aquiros]

Hi..GauGau Thanks, I saw your file and approved it...it came out nice.

- I tried uploading different jpg files and even some of the same ones I already have on the gallery but I still get "0 uploads were successfull". All these files were much smaller than what I currently have onthe gallery.
- I tried with my ADMIN account, with the TEST account, with another USER account and still get the same.

Thanks Tarique, I didn't realize I did not run the update. Anyways, I tried uploading and nothing. 
I read again the General Upload and other posts, and one thing I notice when looking a my debug info is this line:

   max_input_time | -1 | -1     Can this be affecting?

It's really weird GauGau was able to upload and I can't? You guys think there's something else I need to check, test or do? Thank you very much for helping.

Andre

[Tarique Sani]

Even I was able to upload an image using test .... you can delete it

Can you try using another computer, also try deleting the cookies of coppermine from your computer and try again

[aquiros]

Hello Tarique..I saw your pic. This is getting weird. 
- I just tried to upload a 38kb jpg from my roommate's computer (using the TEST account)  and nothing. The same message. We are both connected to a dsl and router but I haven't changed anything on the router or network before this started.
- Deleted the cookies and tried uploading 60kb jpg, and still the same message.


any other ideas? Thanks guys for your kind help.

Andre

[Tarique Sani]

Yes last one - try from another ISP...

[aquiros]

Hi, well, 2 friends of mine which succesfully uploaded files in the past (and are registered users of the gallery) tried to upload pictures today and they both got:

"0 uploads were successful."

They both are located in Costa Rica and in different parts of the country. They tried with the same type of files that we normally upload (less tha 450kb and less that 1024 pixels).

Any guess now?  ???I'm really puzzled right now (not that I know much but still kind of confusing!) Thanks for your help.

Andre

[Joachim Müller]

do try another coppermine install on the same server (upload the package to a folder on your server called "cpgtest" or similar, make sure to use a different mySQL table prefix). You can try a minimum install to save bandwidth by only uploading the folder "classic" within the themes folder and only the english language file in the "lang" folder. Please try the test install, if you can upload correctly there then the issue with the broken mySQL table persists. If this should be the case, we'll have to apply some plastic surgery to your mySQL database 

GauGau

[aquiros]

Hello GauGau, and thanks again.

-I uploaded a new, fresh CPG 1.3.1 into a new dir (http://www.costaricaaviation.com/cpgtest)
-chmod the albums. edit, userpics, include folders to 777 (in my server it's supposed to be all access)
-Created a new database:  cpgtestdb (in the mySQL part of my cpanel all the tables say OK, when running the "check" option)
-Crated a new user: cpgadmin (with all priviledges)
-Proceed to install.php, enter the mySQL info and got the new test CPG running
-created 2 accounts, the admin and a test......now the funny part:

With the TEST account (same pass as post above), I tried uploading files from 16 to 450kb (16 files), and only 1 was uploaded successfully
With the ADMIN account, I did the same, and the SAME pict got uploaded while the others got "0 uploads successfull".

The pict that got uploaded was the 16kb pict (384x256).

Is this weird or am I just too tired?  :-\\ I really appreciate your help and time.
Andre

[Joachim Müller]

I was able to upload smaller pics, all medium sized pics won't work. Please ask your webhost what might cause this - I guess some settings in php.ini keep you from uploading files with medium filesize and resolution. Please refer to the sticky threads on the upload sub-board for details.

GauGau

[aquiros]

Problem has been fixed. I send to my host a description of the problem and I guess they fixed it. Told them to review the PHP.INI and about the uploading problems...a few days later it was ok. They didn't told me what they did though.
If I ever find out I'll post it for others to know. Thank you for all your help.

Andre