Custom Options mass import: SQLSTATE[23000]: Integrity constraint violation:

[oasisfleeting]

I just ran the Custom Options plugin through DataPump api.
About 30,000 inserts. I chose to go with Custom Options because this shop will not include inventory control.
My syntax was mostly made of drop downs.
Size:drop_down:0:0
l did not receive any errors from the shell output when running the script.
On a second look, I realized I shouldn't have used 0:0 on all of them. I manually attempted to edit the order of the custom attributes and I receive a foreign key constraint error.

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (mydatabase.catalog_product_option_title, CONSTRAINT FK_CAT_PRD_OPT_TTL_OPT_ID_CAT_PRD_OPT_OPT_ID FOREIGN KEY (option_id) REFERENCES catalog_product_option (option_id) ON DELET)

I'm not sure if this is relative to Magmi or Custom Options plugin or Magento itself, but I figured I would put a post up here just in case.

Thanks.

Magento 1.9.0.1
PHP 5.3.28
Apache/2.2.26 (Unix) mod_ssl/2.2.26 OpenSSL/0.9.8i mod_bwlimited/1.4
WebServer to PHP Interface cgi-fcgi
MySQL 5.1.73
Linux Centos (not sure which version anymore)

[oasisfleeting]

Update.
I ran the utility again in order to set the correct order on my custom options.
I again got the foreign key contraint error when trying to save a simple product.
I went to configuration and turned on flat tables and re-indexed using the Magento core indexer and I no longer get the foreign key error when saving a product.

However, the second run, the option groups inserted in the correct order, but the options belonging to the drop_down were not inserted in the correct order in all entries, when comparing against my xml file.

If I try to edit the order of the options I get the foreign key contraint error again

Note: I can save the products fine as long as I don't mess with any of the Custom Options.

Magento 1.9.0.1
PHP 5.3.28
Apache/2.2.26 (Unix) mod_ssl/2.2.26 OpenSSL/0.9.8i mod_bwlimited/1.4
WebServer to PHP Interface cgi-fcgi
MySQL 5.1.73
Linux Centos (not sure which version anymore)

[dweeves]

hi, magmi plugin for custom options destroys all custom options & recreate them. This is what causes discrepancies with magento indexes (unless rebuilding them).

I think custom options might not be the right path to go , configurables should work even without inventory control.

for custom options ordering, there is a way to control it using the long value syntax with: (colon) separator in the option value itself. Ordering is the last part of long value syntax (see http://sourceforge.net/apps/mediawiki/magmi/index.php?title=Custom_Options , last sample of syntax paragraph)

[oasisfleeting]

Thanks for the reply. I will indeed use the long syntax when I rerun my datapump script the next time so that I can re-order the option values as well as the options themselves.

I have been considering creating one or more attribute sets and tying all or some simples to configurables, but I'm not ready for that yet.

It will take a lot of clean up on the old database output to do this as it was not an eav model database so it's the old garbage in garbage out thing again. For now I'm just writing the queries in sql server to fetch the data in xml and running the xml against magmi to see how things look and behave.

This is my first Magento in over 5 years and my first time using magmi.

Feel free to close this at any time as this issue is resolved.
Thank you.

[dweeves]

Just to add, you may not need to use xml intermediate format , magmi has plugins to consume input data from SQL source as long as you can setup connectivity to it.

[oasisfleeting]

Yes, I noticed that in the documentation, but as you pointed out connectivity is an issue as well as my poor T-SQL skills. You have no idea how many times I have wished for an agg_array function like the one in PostgreSQL.