UPLOAD & Table Check Errors on Apache 2.2 PHP 5.1.2 MySQL 5.0.18

[ArtM]

I am going through a server upgrade by Installing EE 1.5 RC4 on Apache 2.2 PHP 5.1.2 MySQL 5.0.18 NT. (Windows XP-P)

EE seems to work fine viewing Gallerys, pictures, thumbs, etc.

When trying a test picture UPLOAD I get an error. A white screen at top filled with repeated copies of:

"ee_store_message_write: Duplicate Entry '0' for Key1"

repeated maybe 50 times.

Underneath is a light orange box containing:

"Exhibit Engine MySQL Error!
ERROR FROM QUERY do_a_photo_lang (eng) in page 'ftp upload db insert':

----------------------------
Duplicate Entry '40' for Key 1
----------------------------

If you encounter this error with EE please mail etc etc."

I have mailed a pic of the error to ee@photography-on-the.net


Don't know if this is connected - but I also get an error from phpMyAdmin when checking EE Tables.
All tables show 'OK' on table check. But I get two errors at bottom of the check that says:

"Problems with indexes of table 'ee-photo'
PRIMARY and INDEX keys should not both be set for column `ee_photo_id`"

"Problems with indexes of table `ee_photo_eng`
PRIMARY and INDEX keys should not both be set for column `ee_photo_id`"

Any suggestions on the solutions to the UPLOAD and Table Check errors Welcome!

[Pekka]

This may be MySQL 5 specific problem (have not tested 5 in full yet). Sounds like some auto-increment rows do not work in your MySQL.

If you give command

SHOW CREATE TABLE ee_message

it should give

CREATE TABLE `ee_message` (
`ee_message_id` int(11) NOT NULL auto_increment,
`ee_message_text` text NOT NULL,
`ee_message_originator` varchar(255) NOT NULL default '',
`ee_message_timestamp` timestamp(14) NOT NULL,
PRIMARY KEY (`ee_message_id`)
) TYPE=MyISAM

and

SHOW CREATE TABLE ee_photo_eng

should give

CREATE TABLE `ee_photo_eng` (
`ee_photo_id` int(11) NOT NULL auto_increment,
`ee_photo_header` varchar(255) NOT NULL default 'New Photo',
`ee_photo_text` text NOT NULL,
`ee_photo_exif_flash` varchar(255) NOT NULL default 'no',
`ee_photo_retouch` text NOT NULL,
`ee_photo_darkroom` text NOT NULL,
`ee_photo_camerasupport` varchar(255) NOT NULL default 'handheld',
`ee_photo_meta_keywords` text NOT NULL,
PRIMARY KEY (`ee_photo_id`),
KEY `ee_photo_id` (`ee_photo_id`)
) TYPE=MyISAM PACK_KEYS=1

In both the important thing is "auto_increment". If you do not see it then you must change the id table to "auto_increment" (Structure -> change button -> Extra field in PHPmyAdmin). You must do the same for all ee_photo language tables (ee_photo_xxx).

"Problems with indexes of table 'ee-photo'
PRIMARY and INDEX keys should not both be set for column `ee_photo_id`"

"Problems with indexes of table `ee_photo_eng`
PRIMARY and INDEX keys should not both be set for column `ee_photo_id`"

To fix these you should remove index "ee_photo_id" from ee_photo and ee_photo_xxx. This might cause problem in photo and list page which can be fixed with small code change (let me know if you need it).

[ArtM]

Pekka - thank you for your prompt response. Unfortunately I cannot say as quickly whether everything worked out yet because of the following problems.


If you give command

SHOW CREATE TABLE ee_message

it should give

CREATE TABLE `ee_message` (
`ee_message_id` int(11) NOT NULL auto_increment,
`ee_message_text` text NOT NULL,
`ee_message_originator` varchar(255) NOT NULL default '',
`ee_message_timestamp` timestamp(14) NOT NULL,
PRIMARY KEY (`ee_message_id`)
) TYPE=MyISAM
I show

CREATE TABLE `ee_message` (\n
`ee_message_id` int(11) NOT NULL,\n
`ee_message_text` text NOT NULL,\n
`ee_message_originator` varchar(255) NOT NULL default '',\n `ee_message_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,\n
PRIMARY KEY (`ee_message_id`)\n)
ENGINE=MyISAM DEFAULT CHARSET=latin1

and

SHOW CREATE TABLE ee_photo_eng

should give

CREATE TABLE `ee_photo_eng` (
`ee_photo_id` int(11) NOT NULL auto_increment,
`ee_photo_header` varchar(255) NOT NULL default 'New Photo',
`ee_photo_text` text NOT NULL,
`ee_photo_exif_flash` varchar(255) NOT NULL default 'no',
`ee_photo_retouch` text NOT NULL,
`ee_photo_darkroom` text NOT NULL,
`ee_photo_camerasupport` varchar(255) NOT NULL default 'handheld',
`ee_photo_meta_keywords` text NOT NULL,
PRIMARY KEY (`ee_photo_id`),
KEY `ee_photo_id` (`ee_photo_id`)
) TYPE=MyISAM PACK_KEYS=1
I show

CREATE TABLE `ee_photo_eng` (\n
`ee_photo_id` int(11) NOT NULL,\n
`ee_photo_header` varchar(255) NOT NULL default 'New Photo',\n `ee_photo_text` text NOT NULL,\n
`ee_photo_exif_flash` varchar(255) NOT NULL default 'no',\n `ee_photo_retouch` text NOT NULL,\n
`ee_photo_darkroom` text NOT NULL,\n
`ee_photo_camerasupport` varchar(255) NOT NULL default 'handheld',\n `ee_photo_meta_keywords` text NOT NULL,\n
PRIMARY KEY (`ee_photo_id`),\n
KEY `ee_photo_id` (`ee_photo_id`)\n)
ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1

I am definitely missing the 'auto_increment" !

In both the important thing is "auto_increment". If you do not see it then you must change the id table to "auto_increment" (Structure -> change button -> Extra field in PHPmyAdmin).You must do the same for all ee_photo language tables (ee_photo_xxx).
Changed tables ee_message & ee_photo_eng ee_photo_id table EXTRA column to auto_increment with phpMyAdmin.

Tried UPLOADing again. Died! Canceled with browser. Subsequently realized that a) I had 'localized' my Windows 'hosts' file to make all EE URL's point to my Localhost and b) EE I don't think checks the Windows FTP 'hosts' file, so I had long ago hard coded LAN IP address of FTP server. But I treally wanted my Localhost where I am testing all of this! Then c)I did not even have a local FTP server running!. So now I must work on setting up a Local FTP server.

Pekka would it be possible, for use in test setups such as this, to have 'direct' local file updating? Using the same basic data as in the FTP server setups. Or am I missing an obvious way to do this?! At this time I cannot confirm UPLOAD is back in action!

You must do the same for all ee_photo language tables
To fix these you should remove index "ee_photo_id" from ee_photo and ee_photo_xxx. This might cause problem in photo and list page which can be fixed with small code change (let me know if you need it).
OK tried to remove the index "ee-photo_id" from table ee_photo & ee_photo_eng. Fell into another hole! When trying to drop the ee_photo_id, phpMyAdmin responds




Forbidden

You don't have permission to access /sql.php on this server.

At this point I don't know why!? Hopefully I WILL figure it out; in which case I can report if I really do need the patch for photo & list pages!

To be continued .... !

[Pekka]

Mysql 5 auto-increment should work as other versions (http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html )

All I can think of (based on access errors and other oddities) that you should try reinstalling mysql as Administrator. I'll check out mysql 5 on my XP soon, but AFAIK there should be no big differences apart from character set system, updating from 4 to 5 needs to be done carefully and sequentially as explained in http://dev.mysql.com/doc/refman/5.0/en/upgrade.html .

Your EE's backup was done with mysql 5, right?

For uploading manually see http://photography-on-the.net/forum/showthread.php?t=30778

But as you get fundamental database system problem with auto-increment, manual upload or inserting data manually is not a solution.

[ArtM]

A few more pieces of the 'puzzle' ....

My existing server runs phpMyAdmin Version 2.6.1-pl3 MySQL version 4.02a-nt-max. Running Windows XP-P Old AMD k62/450 256MB 80+120GB.

The server MyAdmin shows 'Auto Increment' on both ee_message & ee_photo_eng.

It also shows for both tables ee_photo and ee_photo_eng
PRIMARY and INDEX keys should not both be set for column 'ee_photo_id'

This is consistent with my newer versions of phpMyAdmin and MySQL.

I also did an export of my EE Database using the Server phpMyAdmin(2.6.1);
and imported onto my new setup PC using the newer version of phpMyAdmin(2.8.0.1) and MySQL(5.0.18)

I was able to set 'Auto Increment' on both tables OK with phpMyAdmin;
but could not delete/Alter/Drop the second index. MyAdmin returns an error - 'Forbidden sql.php' etc.

The only solution I can think of at this time is to downgrade MySQL on the new system to the older server version. But have not tried that yet.

And the saga continues ... !

[Pekka]

Just a note: sql.php is not EE file, Looks like it is http://pear.php.net/package/VFS/docs/0.0.5/VFS/_VFS-0.0.5_VFS_sql_php.html

This could mean that your PHP's MySQL support is compiled in wrong way (version mismatch).

[ArtM]

OK. Seems to be working.

I encountered errors as above when Uploading.

The error showed :

Exhibit Engine MySQL error!
ERROR FROM QUERY do_a_photo in page 'ftp upload db insert':

------------------------------
Duplicate entry '0' for key 1
------------------------------

If you encounter this error with unmodified EE source code please mail this error to EE developers with version number, detailed circumstances when it happened and preferably with a database backup dump. All reports helps improving EE to your satisfaction. Thank You.

The Upload messages showed:

msg 3531 | ftp: EE2MINIPIX: DISCONNECTED
msg 3530 | ftp: EE2MINIPIX:
'2004_0221_124233AA.JPG' UPLOADED SUCCESFULLY
SIZE: 2048x1536 => 43x32 microthumb (IMAGEMAGICK 43) , 12 KB in server.
msg 3529 | imagemagick: IMAGEMAGICK COMMAND (resize+sharpen) | convert -size 43x32 UPLOAD/2004_0221_124233AA.JPG -resize 43x32! +profile '*' -unsharp 0.6x0.7+2+0.1 -quality 67 UPLOAD/ee_temp_upload/2004_0221_124233AA.JPG | EXECUTED OK
msg 3528 | ftp: EE2MINIPIX: Working directory changed to '/testa/microthumbs'
msg 3527 | ftp: EE2MINIPIX: Logged in as 'ee2minipix'
msg 3526 | ftp: EE2MINIPIX: Connected to '127.0.0.1'
msg 3525 | ftp: EE2MINIPIX: DISCONNECTED
msg 3524 | ftp: EE2MINIPIX:
'2004_0221_124233AA.JPG' UPLOADED SUCCESFULLY
SIZE: 2048x1536 => 110x82 thumb (IMAGEMAGICK 110) , 15 KB in server.
msg 3523 | imagemagick: IMAGEMAGICK COMMAND (resize+sharpen) | convert -size 110x82 UPLOAD/2004_0221_124233AA.JPG -resize 110x82! +profile '*' -unsharp 0.4x0.5+2+0.1 -quality 80 UPLOAD/ee_temp_upload/2004_0221_124233AA.JPG | EXECUTED OK
msg 3522 | ftp: EE2MINIPIX: Working directory changed to '/testa/thumbs'
msg 3521 | ftp: EE2MINIPIX: Logged in as 'ee2minipix'
msg 3520 | ftp: EE2MINIPIX: Connected to '127.0.0.1'
msg 3519 | ftp: IMAGEDB: DISCONNECTED
msg 3518 | ftp: IMAGEDB:
'2004_0221_124233AA.JPG' UPLOADED SUCCESFULLY
SIZE: 2048x1536 => 2048x1536 NONE , 1167 KB in server.
msg 3517 | ftp: IMAGEDB: Working directory changed to '/testa/originals/'
msg 3516 | ftp: IMAGEDB: Logged in as 'imagedb'
msg 3515 | ftp: IMAGEDB: Connected to '127.0.0.1'
msg 3514 | ftp: IMAGEDB: DISCONNECTED
msg 3513 | ftp: IMAGEDB:
'2004_0221_124233AA.JPG' UPLOADED SUCCESFULLY
SIZE: 2048x1536 => 400x300 SVGA800 (IMAGEMAGICK inside 740x300) , 36 KB in server.
msg 3512 | imagemagick: IMAGEMAGICK COMMAND (resize+sharpen) | convert -size 400x300 UPLOAD/2004_0221_124233AA.JPG -resize 400x300! +profile '*' -unsharp 0.5x0.2+1+0.3 -quality 75 UPLOAD/ee_temp_upload/2004_0221_124233AA.JPG | EXECUTED OK
msg 3511 | ftp: IMAGEDB: Working directory changed to '/testa/svga800/'
msg 3510 | ftp: IMAGEDB: Logged in as 'imagedb'
msg 3509 | ftp: IMAGEDB: Connected to '127.0.0.1'
msg 3508 | ftp: IMAGEDB: DISCONNECTED
msg 3507 | ftp: IMAGEDB:
'2004_0221_124233AA.JPG' UPLOADED SUCCESFULLY
SIZE: 2048x1536 => 320x240 VGA640 (IMAGEMAGICK inside 600x240) , 28 KB in server.
msg 3506 | imagemagick: IMAGEMAGICK COMMAND (resize+sharpen) | convert -size 320x240 UPLOAD/2004_0221_124233AA.JPG -resize 320x240! +profile '*' -unsharp 0.5x0.2+1+0.3 -quality 75 UPLOAD/ee_temp_upload/2004_0221_124233AA.JPG | EXECUTED OK
msg 3505 | ftp: IMAGEDB: Working directory changed to '/testa/vga640/'
msg 3504 | ftp: IMAGEDB: Logged in as 'imagedb'
msg 3503 | ftp: IMAGEDB: Connected to '127.0.0.1'
msg 3502 | ftp: IMAGEDB: DISCONNECTED
msg 3501 | ftp: IMAGEDB:
'2004_0221_124233AA.JPG' UPLOADED SUCCESFULLY
SIZE: 2048x1536 => 533x400 XVGA1024 (IMAGEMAGICK inside 960x400) , 52 KB in server.
msg 3500 | imagemagick: IMAGEMAGICK COMMAND (resize+sharpen) | convert -size 533x400 UPLOAD/2004_0221_124233AA.JPG -resize 533x400! +profile '*' -unsharp 0.5x0.2+1+0.3 -quality 75 UPLOAD/ee_temp_upload/2004_0221_124233AA.JPG | EXECUTED OK
msg 3499 | ftp: IMAGEDB: Working directory changed to '/testa/xvga1024/'
msg 3498 | ftp: IMAGEDB: Logged in as 'imagedb'
msg 3497 | ftp: IMAGEDB: Connected to '127.0.0.1'
msg 3496 | exif: EXIF of 'UPLOAD/2004_0221_124233AA.JPG':

* Make =>FUJIFILM
It looks like the Upload actually went OK - but EE had a problem at the end writing the Message log.

Anyway, I again reloaded the EE database from the server. Simply used phpMyaAdmin to make anorher DB copy; and then FTP'ed that copy back to my local testing PC. Wherupon I simply Copied it to my MySQL database. All .frm, MYD, myi files.

phpMyAdmin continues to show duplicate Indexes on tables as above; but don't think that was the cause of my 'Abort' error.

The only other thing that I can think of that I did
was to put BOTH php_mysql.dll & php_mysqli.dll

extension=php_mysqli.dll
extension=php_mysql.dll

in that order. Dunno whether that helped or not.

So my Uploads & EE inquiries work fine now locally.

Again check the versions I'm using as above of all the component server pieces. Also I used binaries from http://www.apachelounge.com

Next big task is to transfer all the server stuff to my real server - and all the edits I made to get it to work !!

Thanks Pekka for your help.

I - as others - and I'm sure yourself - await the next generation of EE as well.

One tip. Follow through on what it takes to make an easy switch from server to local PC. FTP names hosts etc. that will make users life easier.

- Art