Database errors

[towersecurity]

Something went wrong with my shop and was inaccessible.

looking at my backups, i noticed that some time in July, the size of the database had increased,
the June SQL export was about 8MB, the September one was 66MB. the maximum import for sql files was 50MB

I couldn't see any reason for this and the only option I could see was to delete all the shop files and database to start again from the June backup.

That all went well:
 

I then noticed in a couple of days I had 5 thousands entries in the system error log .

Looking in Advanced/Maintenace/Database.. were many errors like:

• "CubeCart_category.cat_parent_id has no key set. Expecting KEY."
• "CubeCart_admin_users.admin_id has a key type KEY but expecting PRIMARY."

a previous post on the forum pointed me to use the PHP admin to Amend, delete and add indexes and unique settings.

Also some tables had duplicate entries in the primary fields, for example the currency table. - i was able to delete duplicates in CC admin.
 

Uncleared Errors on the database report are:

• Errors referring to a field that does not appear in the table structer
  • CubeCart_documents.doc_privacy has no key set. Expecting KEY.
  • CubeCart_inventory.live_from has no key set. Expecting KEY.
  • CubeCart_sessions.acp has no key set. Expecting KEY.
• I'm unable to set this as a unique field, all records seem to be empty
  • CubeCart_order_summary.custom_oid has no key set. Expecting UNIQUE KEY.

I've cleared most of the problems, and the shop seems to work, but in the last 15 minutes there are 273 items in the error log:

• File: [catalogue.class.php] Line: [70] "SELECT SQL_CALC_FOUND_ROWS MIN(`live_from`) AS `next_time` FROM `CubeCart_inventory` WHERE `live_from` > UNIX_TIMESTAMP() LIMIT 1;" - Unknown column 'live_from' in 'field list'

This seems to refer to the 2nd uncleared issue above.

Does anyone know what might be going on and how to clear these database errors.

Many Thanks.

 

[bsmither]

That is a large jump in size. If the 8MB backup has a .sql.zip suffix, while the 50MB backup has simply .sql, that would explain the difference as the first has been ZIP compressed.

The advisories concerning missing keys or wrong keys will not cause any problems -- maybe a few milliseconds additional time fetching database records.

The 'live_from' error could be the result of having restored a database backup that belongs to a previous version of the CubeCart code.

Please determine the version of CubeCart code (admin, Dashboard, Store Overview tab) and compare with the database schema version (admin, Maintenance, Upgrade tab).

[havenswift-hosting]

16 hours ago, towersecurity said:

June SQL export was about 8MB, the September one was 66MB. the maximum import for sql files was 50MB

I couldn't see any reason for this and the only option I could see was to delete all the shop files and database to start again from the June backup.

That could have been normal growth, due to huge numbers of records in some log tables or possibly due to tables needing optimising.  The maximum import size for a file is 50 MB in phpMyAdmin but if the file is zipped it is obviously much smaller and so will normally upload.  Alternatively, any sized .sql file can be imported from the command line which is done via ssh and if you dont have that then your hosting company would have been able to help (whether they would be willing to is another story !).  So in short, you didnt ever need to go back and lose three months of data !

14 hours ago, bsmither said:

The advisories concerning missing keys or wrong keys will not cause any problems -- maybe a few milliseconds additional time fetching database records.

This is correct but as fixing it is so simple, why not simply go through phpMyAdmin and add / delete indexes where indicated - stops your error_log file building up to silly size

14 hours ago, bsmither said:

The 'live_from' error could be the result of having restored a database backup that belongs to a previous version of the CubeCart code.

This will also be correct - "live_from" was added in 6.2.3 and your files will be running a later version (6.2.6 or even 6.2.7 if you keep up to date) but the database you restored would have been from before 6.2.3.  You would to upload the setup directory from the version that matches your code and run the setup which will upgrade your database

Ian

[towersecurity]

 

17 hours ago, bsmither said:

That is a large jump in size. If the 8MB backup has a .sql.zip suffix, while the 50MB backup has simply .sql, that would explain the difference as the first has been ZIP compressed.

they are both just .sql ... not .zip.
I suspected the increase in size was due to these error logs, but couldn't see why 5k entries would cause 60MB increase..
Over night, the error log shows 1400 errors. 

 

 

2 hours ago, havenswift-hosting said:

This will also be correct - "live_from" was added in 6.2.3 and your files will be running a later version (6.2.6 or even 6.2.7 if you keep up to date) but the database you restored would have been from before 6.2.3. 

Unfortunately, I don't remember the version number of my June SQL back up.. 

I updated to 6.2.5 in July, the useable .SQL file was created at this time.. the increase in the .SQL files started after the update.

so.. two options:

• ZIP the 66MB .SQL from September (is this possible?)
  • restore the .SQL.ZIP into my current set up
• Go back to CC 6.2.2
  • delete everything on my site,
  • re install CC 6.2.2
  • restore the .SQL from June

I'll try the 1st.. if that doesn't work I'll try the second.

I'll let you know how it goes.

Many thanks BSMITHER and HAVENSWIFT

 

BUT....

i went through the upgrade process for 6.2.5 in the normal way.

Even if it i upgraded from a lower version than 6.2.2, why would the live_from be missing?

Also.. my september .sql file must be suffering from the same issues.. there no point in trying to zip it up.

[havenswift-hosting]

Why dont you ask whether your hosting company has a later backup of the database that they can restore for you ?  While most hosting companies donmt guarantee backups (unless you pay for a service) almost all will be doing backups of some sort

You can zip up a .sql file and then restore that through phpMyAdmin but that will still be a while out of date.

Whatever backup you get restored and no reason why you shouldnt at least get the Sept backup restored

Ian

[towersecurity]

Ok I can ask the host. but given:

18 minutes ago, towersecurity said:

BUT....

i went through the upgrade process for 6.2.5 in the normal way.

Even if it i upgraded from a lower version than 6.2.2, why would the live_from be missing?

Also.. my september .sql file must be suffering from the same issues.. there no point in trying to zip it up.

Is is possible to that i could use PHPMyAdmin to add in the 4 missing fields? If so can you tell me the formats.

 

It's not a disater if i lose the last few months order history.

 

[bsmither]

In the file /setup/db/install/structure.sql, the details of all tables are given (respective of that version of CubeCart).

[towersecurity]

Many Thanks for the info.

I added the missing fields and deleted and reinstated the one where I couldn't set the unique status.

Looks like to error messages have stopped.

Upgradrd to 6.2.8 with no appaerent problems.

I'll monitor it a bit more closely now and hope fully it wont re-occur.