Over 3.000 transactions in CakePHP. How?

Question

We are doing an import of Magento-products into a CakePHP environment over SOAP. We have like 3.000 products and of course we want to keep the database clean of duplicate entrys.

We use InnoDB as product-databse in CakePHP.

We had different approaches:

1. We set the validate var on the product model and want the product-id to be unique. Problem: The transactions are limited to roundabout 2.000. Since the unique-rule for validation requires SELECT-statements for each product, nothing happens because the transactions are "consumed".
2. Set product-id unique in the database itself. Seems good, only problem is, after the first import it always throws a "duplicate entry"-warning (which is correct) and doesn't go on. I found no way how to use "ignore" on that query.

Any ideas?

Answer 1

Since the problem is SQL related and it is an import, I would build the queries manually. Perhaps write out to a file in the tmp/ directory. All of these inserts can make up 1 transaction. This way you can add the on duplicate key ignore to the queries.

By its very nature, CakePHP runs numerous queries and since you are limited on transactions, I wouldn't go with cake.

Of course you can dig into cake a little bit deeper and figure out how its innodb class works and see if there is a way to wrap queries into a transaction.