Fabrik and InnoDB Support

[pastvne]

I read here: http://fabrikar.com/forums/index.php?wiki/prerequisites/

That at Fabrik 3.4.2., InnoDB is required. I'd like to move to InnoDB to obtain transaction support but I also read some cautions as late at 2017 here:

http://fabrikar.com/forums/index.php?threads/does-fabrik-support-innodb.46349/

Are there caveats when using InnoDB?
If moving to InnoDB, should I move all tables to InnoDB or just the tables for which I need to use transactions?

Thank you,
Bruce Decker

 

[troester]

Following this thread http://fabrikar.com/forums/index.php?threads/does-fabrik-support-innodb.46349/ there should be no problem to use InnoDB data tables (Joomla and Fabrik core tables are InnoDB anyway).

We don't support InnoDB FK constraints. Fabrik will work with simple InnoDB tables.

It's just reflex to say we don't, as that question usually comes from people who have designed and built a database with some kind of workbench, which has created Innodb with FK constraints.
...
The reason being that Fabrik essentially handles it's own foreign key constraints, which kind of conflict with the way Innodb enforces them.

We have an application running with all tables InnoDB, created via Django, including FK constraints. In this case you can't use Fabrik's delete buttons out of the box at some lists, but this is the only issue we've seen.

How do you want to use transactions?

 

[pastvne]

Hi Troester:
I have a new version of my software using latest Fabrik. The database schema for the new version has changed. One of the main tables is very large (2.5M records). I wrote a PHP script to move and covert records from the old database to the new database. The script is using the Joomla jDatabase API. The inserts started quickly but as the database has been loaded and has become large, the inserts are going very slowly. I insert records in batches of 500 records. I had hoped to perform a transaction_start before the inserts and then a transaction_commit after the 500 inserts. I've read that this will greatly improve performance for bulk loads but is only supported in InnoDB and not myISAM. Adding a transaction wrapper to the scripts would be the easiest thing to try.

Alternatives:
1) Write a PHP script that does not use the Joomla Database API and turn off indexing for the inserts to the MyISAM table
2) Write a LOAD file with indexing turned off and then load the new records conventionally.
3) Other suggestions?

My main goal is to convert my data more quickly. As it stands, it may take a week to load the rest of the data.

-BD

 

[cheesegrits]

Do you have any unique keys on your fields?

Just to confirm Troester's comments, the only issue I'm aware of with Fabrik and InnoDB is row deletion on lists with joins (if you've selected "delete joined rows") that use FK constraints, as Fabrik is basically trying to do FK constraints "by hand", and we're not deleting things in the order that InnoDB wants. It's probably fixable, and something I might look at in 4.0. There may also be an issue with creating many-to-many joins, although it's a long time since I tested that one.

-- hugh

 

[pastvne]

Thanks Hugh and Troester. I think I do not delete any joined records on this list so I may experiment with moving it to InnoDb for importing.