Error: Duplicate entry in admin_activity_log

[lat9]

I've been asked to look at an installation (Zen Cart v1.5.1, running on Windows Server 2008 using PHP 5.3.28) where the admin-access is not working.

There's a log generated each time an admin attempts to log in:

PHP Fatal error:  1062:Duplicate entry '236817' for key 'PRIMARY' :: insert into admin_activity_log (access_date, admin_id, page_accessed, page_parameters, ip_address, gzpost, flagged, attention) values (now(), '0', 'login.php ', 'camefrom=index.php&', '<edited>', '', '0', '')

This is coming from /admin/includes/init_includes/init_admin_history.php, line 57, and it's always that duplicate entry 236817. How could the database get so that an auto-increment field won't increment?

I'm currently waiting to get access credentials for the server to see what I can see. Any ideas would be much appreciated.

[Ajeh]

Is this a clean v151 or an upgraded one?

[lat9]

It's a highly customized original-install Zen Cart 1.5.1.

[RodG]

How could the database get so that an auto-increment field won't increment?

One possibility (speculation) is if the DB somehow managed to have a record inserted with a higher value than the current auto increment value. All will be fine until the auto-increment value 'catches up' to the erroneous entry, at which time the duplicate entry error will occur and further increments could fail.

I'm probably not going to be suggesting anything new to you here, but the 1st thing I'd be doing is searching the DB for the '236817' entry and see how it 'fits' with all the other auto-increment values.

I further suspect that simply dropping and recreating the table may be the easiest of solutions, although doing this wan't reveal the actual cause (and I'm actually quite curious about this one).

Cheers
RodG

[lat9]

Thanks, RodG! After the MySQL server was restarted, the issue "disappeared" and I haven't been back to the site to check that out ... but I will.

[DrByte]

I've been asked to look at an installation (Zen Cart v1.5.1, running on Windows Server 2008 using PHP 5.3.28) where the admin-access is not working.

There's a log generated each time an admin attempts to log in:

PHP Fatal error:  1062:Duplicate entry '236817' for key 'PRIMARY' :: insert into admin_activity_log (access_date, admin_id, page_accessed, page_parameters, ip_address, gzpost, flagged, attention) values (now(), '0', 'login.php ', 'camefrom=index.php&', '<edited>', '', '0', '')

This is coming from /admin/includes/init_includes/init_admin_history.php, line 57, and it's always that duplicate entry 236817. How could the database get so that an auto-increment field won't increment?

In v1.5.1 the primary key is on log_id which is defined as a "bigint" datatype, capable of accepting values as high as 9223372036854775807. Ref: http://dev.mysql.com/doc/refman/5.7/...ger-types.html

And, since the INSERT statement you quoted doesn't specify log_id, the log_id is being assigned by auto-increment. So, as you implied, the first problem is that the autoincrement is producing a duplicate. That in itself implies a bug at the database level.
Second, since the "duplicate" is on a non-remarkable 6-digit value (non-remarkable as in it doesn't correlate to any of the "limits" imposed by the other integer data-type sizes that MySQL offers), that suggests either that there is general corruption in the database table (try a REPAIR TABLE?) or perhaps the table schema has been altered in some bizarre way (compare schema vs original install sql).


You said a server restart helped. That might have triggered some internal table-repair routine. I might still suggest that you perform a repair on all the tables to address possible other corruption issues that might have been caused by some abrupt server crash/restart that happened before you were brought in to investigate.
And make a full db backup for the site-owner to archive "just in case", as there might be other reliability concerns that this situation might be flagging, and now is a good time to take some protective measures.

[lat9]

Thanks for the detailed update, DrByte. You and RodG have confirmed for me the direction(s) in which I'll head next!

[zcers]

ALTER TABLE `admin_activity_log` CHANGE `log_id` `log_id` BIGINT(15) NOT NULL AUTO_INCREMENT;