Error while restoring MySQL database?
#1
Hi,

I have been runnig Kodi on 2 Intel NOC connected to a MySQL server on my main computer. It have been working great acouple of years even after several reinstallations.

I was forced to reinstall Windows 10 on the main computer. To be sure that no data was lost I did 2 kind of exports with the MySQL Workbench 8, one selfcontained and one folder backup.

After reinstallation of Windows and MySQL server (including workbench) I do not get the import to work, I get the following error

17:40:56 Restoring I:\Reinstall 20181118\Kodi\MySQL backup\Dump20181118.sql
Running: mysql.exe --defaults-file="c:\users\jimmy\appdata\local\temp\tmpr3pqm2.cnf"  --protocol=tcp --host=127.0.0.1 --user=root --port=3306 --default-character-set=utf8 --comments --database=mysql  < "I:\\Reinstall 20181118\\Kodi\\MySQL backup\\Dump20181118.sql"
ERROR 3734 (HY000) at line 1951: Failed to add the foreign key constraint. Missing column 'country_id' for constraint 'fk_city_country' in the referenced table 'country'

Operation failed with exitcode 1
17:41:39 Import of I:\Reinstall 20181118\Kodi\MySQL backup\Dump20181118.sql has finished with 1 errors


When running the folder import I get over 300 errors.

No datatables are created and the only already existing item is a sys database.

Could someone help me out here?

BestRegards
Reply
#2
After checking the self containing file I can see that the Contraint 'fk_city_country while creating the city table. The country table is created a couple of rows down. I could switch places on them but this is on row 130-150 så its not impossible that there will be alot of errors like this.
Reply
#3
(2018-11-18, 18:51)snowjim Wrote: To be sure that no data was lost I did 2 kind of exports with the MySQL Workbench 8, one selfcontained and one folder backup.
Did you also tick the "Include create schema" in MySQL Workbench during the data export? That will insert "create if not exist" commands for database tables.
Image
Reply
#4
hmm, its possible that I forgott to check that one, strange that its not by default.

I manually created the 6 databases/schemas and then I imported the folder backup again. This time I got 4 errors :

Code:

18:56:16 Restoring mymusic60 (Views, routines, events etc)
Running: mysql.exe --defaults-file="c:\users\jimmy\appdata\local\temp\tmp8sq4vx.cnf"  --protocol=tcp --host=127.0.0.1 --user=root --port=3306 --default-character-set=utf8 --comments --database=mymusic60 < "I:\\Reinstall 20181118\\Kodi\\MySQL backup\\db\\mymusic60_routines.sql"
ERROR 1146 (42S02) at line 160: Table 'mymusic60.artist' doesn't exist

Operation failed with exitcode 1
18:56:17 Restoring mymusic60 (album)

18:56:24 Restoring myvideos107 (Views, routines, events etc)
Running: mysql.exe --defaults-file="c:\users\jimmy\appdata\local\temp\tmpsowzsc.cnf"  --protocol=tcp --host=127.0.0.1 --user=root --port=3306 --default-character-set=utf8 --comments --database=myvideos107 < "I:\\Reinstall 20181118\\Kodi\\MySQL backup\\db\\myvideos107_routines.sql"
ERROR 1146 (42S02) at line 279: Table 'myvideos107.musicvideo' doesn't exist

Operation failed with exitcode 1
18:56:25 Restoring myvideos107 (actor)

18:56:55 Restoring myvideos90 (Views, routines, events etc)
Running: mysql.exe --defaults-file="c:\users\jimmy\appdata\local\temp\tmpollrpr.cnf"  --protocol=tcp --host=127.0.0.1 --user=root --port=3306 --default-character-set=utf8 --comments --database=myvideos90 < "I:\\Reinstall 20181118\\Kodi\\MySQL backup\\db\\myvideos90_routines.sql"
ERROR 1146 (42S02) at line 252: Table 'myvideos90.episode' doesn't exist

Operation failed with exitcode 1
18:56:56 Restoring myvideos90 (actorlinkepisode)

18:57:29 Restoring myvideos93 (Views, routines, events etc)
Running: mysql.exe --defaults-file="c:\users\jimmy\appdata\local\temp\tmph02xaf.cnf"  --protocol=tcp --host=127.0.0.1 --user=root --port=3306 --default-character-set=utf8 --comments --database=myvideos93 < "I:\\Reinstall 20181118\\Kodi\\MySQL backup\\db\\myvideos93_routines.sql"
ERROR 1146 (42S02) at line 252: Table 'myvideos93.musicvideo' doesn't exist

Operation failed with exitcode 1
18:57:29 Restoring myvideos93 (actor)

I checked the schema and the tables did exist so I tried to import again and this time I got 0 errors Smile

Should there really be 6 schemas? like mymusic 48, mymusic52, mymusic60, myvideo107, myvideo90 and myvideo93? Or could a delete some of them?
Reply
#5
For Kodi you will have 2 schemas: 1 for videos and 1 for music.
Older Kodi databases can be deleted if you are very sure you won't need them anymore.

Tick the "Include Create Schema", and do the data export of the schema(s) necessary. Restoring should also work via the MySQL Workbench tool.
Reply

Logout Mark Read Team Forum Stats Members Help
Error while restoring MySQL database?0