Options disappeared from database

I've been adding all my products to my PS3 store and each item has two options. I add the "color" option first and the "size" option second. The options appeared in that order until I added the options for one of the products at about the 110th item. I entered the options in the same sequence, but they appeared backwards, top to bottom. So I checked the front end of the website and all but the latest item had no options. They had all disappeared. I looked at the database tables and all of my options were gone, with the exception of the last entry. The option ID's started at number 517. So how did 516 options all delete themselves all by themselves? I need to add a couple hundred options with about 30 values per option, but I'm worried that I may loose all the data again. And what about the stability the application? I've never had a database auto delete entries like this in the 15 years I've been building websites. Please help, explain before I waste another 12 hours of work.

PS3 does not do any auto delete unless you deleted the option your self. One thing i would check is to see if the size of the database can handle that number of entries. Because adding options adds few rows to your database.

Also, if you look at your ps3_productoptions table do you see the entries for OptionsID 1 - 516 in there.

I'm running considerably larger and more complex databases on the same MySQL server with no memory problems, CPU issues or limitations that would adversely effect this database.

Well PS3 shouldn't do any auto delete and I know there is no admin area to do that, unless you deleted the item options and their values one by one. Trust me, I did not go in and take the time to delete all my options and their values one by one. Never the less, your application errored somehow or MySQL threw an error and deleted the values for just a few tables, which seems unlikely. But then I have never heard of something like this, nor have any of my programers on staff. Somehow the options and their associated values all disappeared in a matter of seconds. I checked IP's that have logged into the database to see if anyone hacked the database, and only know IP's have logged in. Still not ruling that out.

Please see attached logs and see if there is anything in them that you notice that is abnormal. Note how few seconds it took for the options to be deleted.

I've attached screenshots of the tables that show the ID's and the current new values so you can get an idea of how I'm using the options for this website.

I'd appreciate any help you can give me. This site is supposed to be live next week and I'm nervous to tell the client it's ready to go and have their website lose info.

I have asked around to get some ideas of a scenario that this could happen and the only possibility if the database was changed manually and then try to delete an option and since the database is modified manually the delete will actually not work correctly.

I would recommend that when you starting inserting the products, to create a mysql dump each time you add a certain number of products (20 - 50), so you will have a back up to go back to.

I'm not sure if you are planning to insert the products again, but i recommend that you clear the tables before doing that by running a truncate table command.

let me know if you have any other questions.

I have been doing a manual SQL dump after every couple of hours of work. I also have been adding the options back into the application since it has to be online in another week. I also have started an hourly backup at the server level, versus a daily backup.

I did not manually alter the database before any entries were made. This store was upgraded from version 2 to 3 though if that makes any difference.

Would having two people in the admin at the same time possibly cause any of these issues? I did not see any documentation on limitations of how many people can administer the store at the same time.

A separate question is on the sorting of the menu system.
My products list displays correctly on the side bar navigation;
alpha sort A to Z
On the main menu it sorts Z to A
See attachments for screenshots of menus. Anyway of easily fixing this so they both sort A to Z?

There is no limitation on who is logged in to the admin area. but i can see a possibility of this causing an error if the admin users are inserting products, although i don't think this is the case here.

in regard to the sorting, you can change the sorting by opening the menu.php in the CSSMenuWriter/cssmw_home

and change line 91 to

$query_menuwriter_categories = "SELECT CategoryName, CategoryID FROM ps3_categories WHERE CategoryParent = 0 Order by CategoryName";

let me know if you still have any other questions.

I may have a clue to what caused the missing records. I was entering a simple option and went to edit it and the edit and delete links would not work. The rollover highlight worked, but it did not send a new URL to the browser. The entry for the option was Petite: 20.5-21.5"
I thought about the quote sign being used to display inches being the culprit. Sure enough I made an entry without the quote symbol and then I could edit it. I made one with the quote symbol and then it would not edit.

I went to edit older records and I found that all my options were missing from the store database. This is the second time I have lost all the options in this database. Doesn't give me a lot since of reliability with this application. I looked at the database and about 3500 records are missing in the options table, all but the last few entries. This happened at the same time as my above problem. Is it possible that this anomaly in the application could cause the deletion of all data within a certain table? I need two make sure of what is causing this problem before going live with this site. Or for that matter spending anymore time working on the site until I can find an answer to my problem. HELP :-)

I was able to reproduce the issue of not being able to edit the option when it includes quotes in the name. I have logged a bug in our bug base and will try to have a fix for this issue in future releases.

However, i could not reproduce the issue with the options being deleted from the database.

I recommend that you clean up your tables before adding any more options (with out using quotes), i also recommend that you use a different database if that is possible.

sorry for the inconvenience.

Could you reply with the best way to clean up the database, within the confines of not wanting to reenter any data. You also mentioned cleaning up the tables, besides looking for any quotes, what are you referring to in the clean up process?

I agree on a new database, but are you suggesting making a fresh database with all new tables with no data in them? If so I have two concerns.

(1) I have over 3,600 options entered and 170 products in this database, so the idea of rekeying this is pretty much out of the question unless absolutely necessary.

(2) If I dump just the data from the existing tables and import the data into the new database with new tables, won't that just carry over any errors that might exist in the current database?

Any help on the steps I might take to make the database more reliable would be appreciated. Oh, one more thing, this powerstore was up dated from version 2 to version 3, so all the old tables still exist in the current database along with the new table structure, this has always bothered me, not knowing which old tables could be removed from the database.

the tables that are used in powerstore v3 are prefixed with ps3_, in addition to the wa_settings and the kfm_ tables (those are used for the upload).

If reentering the data is not an option, then i recommend that you clear only the options tables. So this will remove all the options for all your products, but it will keep your products that you already inserted. The tables that you need to clear are:

ps3_options, ps3_productoptions, ps3_simpleoptions and ps3_optiongroups

you can clear those tables using this syntax:

truncate table ps3_optiongroups;
truncate table ps3_options;
truncate table ps3_productoptions;
truncate table ps3_simpleoptions;

I recommend that you dump the entire database for back up.