Level 80
@gabriel27 Would you not be better off creating SQL import scripts, rather than trying to read data into PHP, to write it back out to a new database?
1 like
Dec 5, 2023
5
Level 1
Parse big database to convert data from old system to Laravel
Hello,
I have a PHP LMS. I created a new Laravel based LMS. I have a big MySQL database with millions of records devided in tens of tables.
The table structure is completely different so I need to query the old database, process data and insert the data in the new structure.
I split the data in tables. The scripts timed out.
I also split the data in the tables in years but still timed out.
I created in Laravel 2 MySQL connections (one to the old DB and one to the new DB). I created jobs and chained them to connect to each DB. If I try to parse and insert over 80k rows the queue is killed.
I may have tried to save the last ID saved correctly and using transactions to restart where it left of but I believe is quite complicated considering the number of tables.
The only solution that worked is to create external PHP scripts, export the data from old DB in jsons split on tables and years with the structure of the new table and then import them in Laravel using chained queues and commands. This way we also split the data conversion in one execution and the import in another execution.
Are there any better ways to parse and convert mysql data with a high number of rows?
Thank you.
Level 1
@martinbean If I understand correctly you recommend to create php scripts to export the parsed data from the old database into SQL import scripts. After I should try to import those scripts in the new database?
We tried to do something like this but instead of SQL scripts we exported as JSON files with the array structure of the new database. This way we just insert them using Eloquent.
Did I understand correctly?
Thank you.
Level 80
you recommend to create php scripts
@gabriel27 No. Just stop thinking about importing anything into PHP at all. Just write a SQL import script that moves the data between the two databases directly.
Level 1
@martinbean Thank you for your reply. I believe your solution is excellent for most of the cases. We will need to develop our SQL skills in order to make this happen as the conversion process is quite complex.
For example we have one table with data which will split into 4 tables each with new ids and relationships which we need to manage.
I will check if we can grow our skills in order to make this solution possible.
Thank you.
Level 80
@gabriel27 As part of the import you could add legacy_id columns or whatever that you store the “old” IDs in, and then remove these columns at the end of the import.
1 like
Please or to participate in this conversation.