Error: There was an error while processing a database query. when uploading a pi

[phoenix4]

Hi,

Since a few days i get this very weird error, when i try to upload a pic:

There was an error while processing a database query.

Code Select Expand

While executing query "INSERT INTO cpg130d_pictures (pid, aid, filepath, filename, filesize, total_filesize, pwidth, pheight, ctime, owner_id, owner_name, title, caption, keywords, approved, user1, user2, user3, user4, pic_raw_ip, pic_hdr_ip) VALUES ('', '17', 'userpics/10001/', 'IMAGE0043.JPG', '23081', '27022', '500', '375', '1100364320', '0', 'admin','', '', '', 'YES', '', '', '', '', '62.221.224.95', '62.221.224.95')" on 0

mySQL error: Duplicate entry '2147483647' for key 1


File: ../include/functions.inc.php - Line: 105

I don't know why it happened, neither howto solve this  :-\\
And I already searched the forum, but didn't find the right solution....

Somebody help please?

thq, phoeniX4

[phoenix4]

somehow, it helped when i deleted some pictures. i deleted 5 and i was able to ulpoad 5 more new pictures, but then the error occured again.


what's wrong?? is my album full?? or is the database full or something?

[Tranz]

Did you check your webspace storage use?

[phoenix4]

No I didn't.. but i can upload fine via FTP, even 40MB files... but could it be that the mySQL database is full? i dont know exactly how much storage it can have, but my "cpg130d_pictures" database is 523,6 KB with 1.934 entries of pictures.

So, is my database full? or is there another reason for why i can't upload?


I think it has to do with this quote:

mySQL error: Duplicate entry '2147483647' for key 1

??

[phoenix4]

Code Select Expand

USER:
------------------
Array
(
   [ID] => f258eddc1115a07964e1cca6bca7a4c4
   [am] => 1
   [liv] => Array
       (
           [0] => 1314
           [1] => 1224
           [2] => 1531
           [3] => 80
           [4] => 2147483647
       )

   [search] => ###pic
)

==========================
USER DATA:
------------------
Array
(
   [user_id] => 1
   [user_group] => 1
   [user_active] => YES
   [user_name] =>
   [user_password] =>
   [user_lastvisit] => 2004-11-13 22:56:16
   [user_regdate] => 2004-06-21 23:07:26
   [user_group_list] =>
   [user_email] =>
   [user_website] =>
   [user_location] =>
   [user_interests] =>
   [user_occupation] =>
   [user_actkey] =>
   [disk_max] => 0
   [disk_min] => 0
   [can_rate_pictures] => 1
   [can_send_ecards] => 1
   [ufc_max] => 3
   [ufc_min] => 3
   [custom_user_upload] => 1
   [num_file_upload] => 10
   [num_URI_upload] => 10
   [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] => 3
   [group_quota] => 0
   [can_see_all_albums] => 1
   [group_id] => 1
   [groups] => Array
       (
           [1] => 1
       )

)

==========================
Queries:
------------------
Array
(
   [0] => SELECT extension, mime, content FROM cpg130d_filetypes;
   [1] => SELECT * FROM cpg130d_users WHERE user_id='1'AND user_active = 'YES' AND user_password != '' AND BINARY MD5(user_password) = 'e36169b7953c8561a7cc96d97b98eef5'
   [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 cpg130d_usergroups WHERE group_id in (1)
   [3] => SELECT group_name FROM  cpg130d_usergroups WHERE group_id= 1
   [4] => DELETE FROM cpg130d_banned WHERE expiry < 1100369295
   [5] => SELECT * FROM cpg130d_banned WHERE ip_addr='62.221.224.95' OR ip_addr='62.221.224.95' OR user_id=1
   [6] => SELECT category FROM cpg130d_albums WHERE aid='44'
   [7] => INSERT INTO cpg130d_pictures (pid, aid, filepath, filename, filesize, total_filesize, pwidth, pheight, ctime, owner_id, owner_name, title, caption, keywords, approved, user1, user2, user3, user4, pic_raw_ip, pic_hdr_ip) VALUES ('', '44', 'userpics/10001/', 'IMAGE0006.JPG', '25253', '29540', '500', '375', '1100369295', '0', 'shaggy','', '', '', 'YES', '', '', '', '', '62.221.224.95', '62.221.224.95')
)

==========================
GET :
------------------
Array
(
)

==========================
POST :
------------------
Array
(
   [album] => 44
   [title] =>
   [caption] =>
   [keywords] =>
   [control] => phase_2
   [unique_ID] => c53ab07b
)

==========================
VERSION INFO :
------------------
PHP version: 4.3.9 - OK
------------------
mySQL version: 3.23.39
------------------
Coppermine version: 1.3.2
==========================
Module: gd
------------------
GD Support enabled
GD Version bundled (2.0.28 compatible)
FreeType Support enabled
FreeType Linkage with freetype
T1Lib Support enabled
GIF Read Support enabled
GIF Create 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 external
MYSQL_SOCKET /var/run/mysqld/mysqld.sock
MYSQL_INCLUDE -I/usr/include/mysql
MYSQL_LIBS -L/usr/lib -lmysqlclient  
==========================
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/share/php | .:/usr/share/php
open_basedir | /www/hosts:/www/shared:/www/tmp:/tmp:/var/tmp | /www/hosts:/www/shared:/www/tmp:/tmp:/var/tmp
==========================
email
------------------
Directive | Local Value | Master Value
sendmail_from | me@localhost.com | me@localhost.com
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 | 120 | 120
max_input_time | -1 | -1
upload_max_filesize | 8M | 8M
post_max_size | 8M | 8M
==========================
Page generated in 0.434 seconds - 8 queries in 0.055 seconds - Album set :

[kegobeer]

Have you done any manual editing of your Coppermine tables?

This error isn't related to the size of your db, per se, but the maximum size of the integer that's allowed in the INT datatype (values from -2147483647 to 2147483647).  I find it hard to believe you have 2147483647 files in your database, so there must be something else going on.  This has been reported on beta versions of MySQL, so that could be a problem if your host is using an older version of MysQL.  You can also look in your cpg130d_pictures table for the number 2147483647 in the PID column.  The PID column is auto-incremented, so if you replace that value with a lower number (if you have 5000 pictures in your database, use 5001) your problem should be fixed.

[phoenix4]

I haven't done any manual editing from the sql table, but somehow, every new pic gets the id 2147483647. in fact, its id must be 2022, since there are 2022 pics in my gallery. my sql auto-creates the new pics with all the same id (2147483647). How can i get rid of this? because i don't want to edit every picture's id manually, so how can i get this back to 2022, and so on?

[Joachim Müller]

do as kegobeer suggested and try to find out what mysql version your server/webhost is running. Maybe you could even ask your webhost for support on this?

Joachim

[kegobeer]

Have you looked at the table I told you to examine?

[phoenix4]

yes i did. i changed the pid from  2147483647 to 2022, but when i upload another picture, it automatically gets pid  2147483647 again, and the next one i upload gets the same pid. i think  2147483647 is the maximum, but i dont understand why each pic gets this pid.. this never happened before, does somebody know how to get the pid back to normal? (automatically start with 2023, 2034, 2035 and so on..)

[kegobeer]

From what I've read, there's some corruption in your table that's causing it.  I was hoping the PID change would fix it.  Since it didn't, try exporting the data and schema from the pictures table, rename the pictures table to something else, and then import the data back into your db.

Back up your database first, of course.

[Nibbler]

You need to reset the autoincrement. Delete the pic with pid '2147483647' and then run the query:

Code Select Expand

ALTER TABLE cpg130d_pictures AUTO_INCREMENT=0

[phoenix4]

when i try to do a sql query, it says this error:

Code Select Expand

Error

SQL-query : 

ALTER TABLE cpg130d_pictures AUTO_INCREMENT =0

MySQL said:


#1142 - alter command denied to user: '********@*********' for table 'cpg130d_pictures'



Do I need to let the host run this query then? Because i can't do any queries at all 

What's another option than taking another host? :-\\

[Joachim Müller]

your mysql user needs alter permissions - if you don't have those permissions, you have to ask your webhost to grant them. If your webhost refuses, you should look for a new webhost.

Joachim

[phoenix4]

I didn't contact my webhost yet, but i did do some research in the tables. i think some settings in it are wrong. so i made these screenshots so you can see if there's something wrong...

i think it has to do with the INT or something... or with its function, since the pid isn't the "last pid + 1" but it starts with maximum pid's.

i've been busy with changing the pid 2147483647 back to what it should be, after every upload, and this works, but it's too much work to do it every time :-\\

So if you can see anything unusual in the tables and tell me how to put it back, it would help me a lot! 

[Casper]

Try this.  In the pictures table, click on the 'operations' tab in the menu.  At the bottom of this page, you should be able to reset the  auto-increment number.

[phoenix4]

yes, i have found it. it says 2147483647 again, so what do i need to change it into? 0 ?

[kegobeer]

Set it to 1.  MySQL will not overwrite any previous values, it will start at one and go until it finds a spot.

Just for my information, what version of MySQL are you running?  If it's 3.23.25-beta, I've read upgrading to a newer MySQL (after resetting auto_increment) will fix it also.

[phoenix4]

it worked! thanks a lot by the way, my sql version is 3.23.29.