search

vsc55 / ombi_sqlite_mysql

Migration tool from SQLite to MySql/MariaDB for ombi
GNU General Public License v3.0
3 stars 9 forks source link

Tried several permutations and combinations, and always lead back to MySQL Error [1146]: Table 'xxx' doesn't exist. #24

Closed cthierman closed 2 years ago

cthierman commented 2 years ago

So, I say Table 'xxx' doesn't exist, cause if I let the job run, it's a multitude of tables. Don't understand who was supposed to create them I've been following the guide: https://docs.ombi.app/guides/migrating-databases/#coming-soon

Describe the bug I followed the steps outlined here but ran into a problem at step 4.1 (Start the Migration):

https://docs.ombi.app/guides/migrating-databases/#coming-soon

To Reproduce Steps to reproduce the behavior: Followed these steps with no errors: https://docs.ombi.app/guides/migrating-databases/#coming-soon

Expected behavior Database migration would occur without errors when I execute the command: python3 /etc/Ombi/Git/ombi_sqlite_mysql/ombi_sqlite2mysql.py -c /etc/Ombi --host 127.0.0.1 --db Ombi --user ombi --passwd xxx

Screenshots If applicable, add screenshots to help explain your problem. Here is the output: run_debug.txt

Ombi Version: Ombi 4.0.975 MySql version: mysql Ver 14.14 Distrib 5.7.33, for Linux (x86_64) using EditLine wrapper Ubuntu version: 16.04

Logs (Logs directory where Ombi is located) /etc/Ombi/Logs

Executed the migration and here is the output:

First issue I ran into was the message below, but I got around it, but thought it worth including for information:

`# python3 /etc/Ombi/Git/ombi_sqlite_mysql/ombi_sqlite2mysql.py -c /etc/Ombi --host 127.0.0.1 --db Ombi --user ombi --passwd xxx Migration tool from SQLite to MySql/MariaDB for ombi (3.0.8) By VSC55

Generate file "database.json":

MySQL > Connecting... [✓]

MySQL > Disconnecting... [✓] ` So I created that table using the following command in mysql:

CREATE TABLE__EFMigrationsHistory(MigrationIdnvarchar(150) NOT NULL,ProductVersionnvarchar(32) NOT NULL, PRIMARY KEY (MigrationId) );

Which got me past that error. Only to get the output I included in the file attached above.

I must be missing a step, but for the life of me, I can't figure out what, as it would seem some tables are not getting created.

vsc55 commented 2 years ago

Hi @cthierman, Assuming ombi is installed in /opt/ombi try the following:

  1. Delete all tables from the database.
  2. Run: 
    # python3 ombi_sqlite2mysql.py -c /etc/Ombi --only_db_json --host 127.0.0.1 --db Ombi --user ombi --passwd ombi
# /opt/Ombi/Ombi --migrate
# python3 ombi_sqlite2mysql.py -c /etc/Ombi --host 127.0.0.1 --db Ombi --user ombi --passwd ombi

    Note: "/opt/Ombi/Ombi --migrate" is the command that is responsible for creating all the tables.

cthierman commented 2 years ago

I'll try that again. But I tried that a couple of times and same problem. Let me try one more time. After all, sometimes you do one thing wrong... I'll let you know.

On Fri, Mar 25, 2022 at 6:06 PM Javier Pastor @.***> wrote:

Hi @cthierman https://github.com/cthierman, Assuming ombi is installed in /opt/ombi try the following:

  1. Delete all tables from the database.
  2. Run:

python3 ombi_sqlite2mysql.py -c /etc/Ombi --only_db_json --host 127.0.0.1 --db Ombi --user ombi --passwd ombi# /opt/Ombi/Ombi --migrate# python3 ombi_sqlite2mysql.py -c /etc/Ombi --host 127.0.0.1 --db Ombi --user ombi --passwd ombi

Note: "/opt/Ombi/Ombi --migrate" is the command that is responsible for creating all the tables.

— Reply to this email directly, view it on GitHub https://github.com/vsc55/ombi_sqlite_mysql/issues/24#issuecomment-1079525915, or unsubscribe https://github.com/notifications/unsubscribe-auth/AGHZ2HBVXRQ4EURNIQBEJ2DVBZIGTANCNFSM5RVSOAVQ . You are receiving this because you were mentioned.Message ID: @.***>

cthierman commented 2 years ago

Hi, Tried again, exactly as you indicated above. And, I get an error from MySql of Ombi not existing. I will, create the database and try again. Here is the output 

root@Utilities-desktop:/opt/Ombi# python3 /etc/Ombi/Git/ombi_sqlite_mysql/ombi_sqlite2mysql.py -c /etc/Ombi --only_db_json --host 127.0.0.1 --db Ombi --user ombi --passwd ombi
Migration tool from SQLite to MySql/MariaDB for ombi (3.0.8) By VSC55

Generate file "database.json":
- Saving in (/etc/Ombi/database.json)... [✓]

root@Utilities-desktop:/opt/Ombi# cat /etc/Ombi/database.json
{"ExternalDatabase": {"ConnectionString": "Server=127.0.0.1;Port=3306;Database=Ombi;User=ombi;Password=ombi", "Type": "MySQL"}, "OmbiDatabase": {"ConnectionString": "Server=127.0.0.1;Port=3306;Database=Ombi;User=ombi;Password=ombi", "Type": "MySQL"}, "SettingsDatabase": {"ConnectionString": "Server=127.0.0.1;Port=3306;Database=Ombi;User=ombi;Password=ombi", "Type": "MySQL"}}root@Utilities-desktop:/opt/Ombi#
root@Utilities-desktop:/opt/Ombi# ./Ombi --migrate
Hello, welcome to Ombi
Valid options are:
Ombi 4.0.975
Copyright (C) 2022 Ombi

  --host       (Default: http://*:5000) Set to a semicolon-separated (;) list of
               URL prefixes to which the server should respond. For example,
               http://localhost:123. Use "localhost" to indicate that the server
               should listen for requests on any IP address or hostname using
               the specified port and protocol (for example,
               http://localhost:5000). The protocol (http:// or https://) must
               be included with each URL. Supported formats vary between
               servers.

  --storage    Storage path, where we save the logs and database

  --baseurl    The base URL for reverse proxy scenarios

  --demo       Demo mode, you will never need to use this, fuck that fruit
               company...

  --migrate    Will run the migrations then exit the application

  --help       Display this help screen.

  --version    Display version information.

Migrate in progress...
Migrate complete.
root@Utilities-desktop:/opt/Ombi# python3 /etc/Ombi/Git/ombi_sqlite_mysql/ombi_sqlite2mysql.py -c /etc/Ombi --host 127.0.0.1 --db Ombi --user ombi --passwd ombi
Migration tool from SQLite to MySql/MariaDB for ombi (3.0.8) By VSC55

Generate file "database.json":
- Saving in (/etc/Ombi/database.json)... [✓]

MySQL > Connecting... [!!]
* MySQL Error [1049]: Unknown database 'Ombi'
root@Utilities-desktop:/opt/Ombi#

Tried to recreate database and run again... and, back where I started.... If I create the Table, I will get the same result as I got before. For some reason the /opt/Ombi/Ombi --migration is not creating the tables... any guesses. I'm running as root but logging into mysql using userid ombi. I assume /opt/Ombi/Ombi reads the databases.json file to get the login. No?

vsc55 commented 2 years ago

Hi @cthierman, In which folder are the databases "ombi.db, ombiexternal.db and ombisettings.db"? I see that you are using Ombi 4.0.975, it would be good to update to the latest version before migrating the data.

cthierman commented 2 years ago

Hi vcs55,

-rw-r--r-- 1 ombi ombi    1925 Mar 25 16:30 appsettings.json
-rw-r--r-- 1 ombi ombi     377 Mar 26 14:38 database.json
-rw-r--r-- 1 ombi ombi     336 Mar 25 16:41 database.json+
drwxr-xr-x 3 ombi ombi    4096 Mar 24 21:28 Git
-rw-r--r-- 1 ombi ombi 3599077 Mar 25 18:57 insert_error.log
drwxr-xr-x 2 ombi ombi    4096 Mar 25 16:25 Logs
-rw-r--r-- 1 ombi ombi     291 Mar 24 22:56 migration.json
-rw-r--r-- 1 ombi ombi     326 Mar 24 21:49 migration.json--
-rw-r--r-- 1 ombi ombi  675840 Mar 22 14:45 Ombi.db
-rw-r--r-- 1 ombi ombi  634880 Nov 15  2020 Ombi.db.backup
-rw-r--r-- 1 ombi ombi  565248 Mar 24 21:15 OmbiExternal.db
-rw-r--r-- 1 ombi ombi   24576 Oct  6 23:13 OmbiSettings.db
-rw-r--r-- 1 ombi ombi   24576 Mar 25 16:25 Settings.db

I will remove the database.json, start up Ombi and upgrade. So now I'm at 4.0.1601.

Files still in /etc/Ombi code in /opt/Ombi

# ls -l /etc/Ombi
total 5440
-rw-r--r-- 1 ombi ombi    1925 Mar 25 16:30 appsettings.json
-rw-r--r-- 1 ombi ombi     336 Mar 25 16:41 database.json+
-rw-r--r-- 1 ombi ombi     377 Mar 26 14:38 database.json-cet
-rw-r--r-- 1 ombi ombi 3599077 Mar 25 18:57 insert_error.log
drwxr-xr-x 2 ombi ombi    4096 Mar 26 23:15 Logs
-rw-r--r-- 1 ombi ombi     291 Mar 24 22:56 migration.json
-rw-r--r-- 1 ombi ombi     326 Mar 24 21:49 migration.json--
-rw-r--r-- 1 ombi ombi  684032 Mar 26 23:32 Ombi.db
-rw-r--r-- 1 ombi ombi  634880 Nov 15  2020 Ombi.db.backup
-rw-r--r-- 1 ombi ombi  565248 Mar 26 23:33 OmbiExternal.db
-rw-r--r-- 1 ombi ombi   24576 Mar 26 23:32 OmbiSettings.db
-rw-r--r-- 1 ombi ombi   24576 Mar 25 16:25 Settings.db

Ran through the same steps, got the same results. Initially running /opt/Ombi/Ombi --migrate with no databases. Comes back with error

First step

# python3 /etc/Ombi/Git/ombi_sqlite_myql/ombi_sqlite2mysql.py -c /etc/Ombi --only_db_json --host 127.0.0.1 --db Ombi --user ombi --passwd ombi

Second Step

# pwd
 /opt/Ombi/
# ./Ombi --migrate
Hello, welcome to Ombi
Valid options are:
Ombi 4.0.1601
Copyright (C) 2022 Ombi

  --host       (Default: http://*:5000) Set to a semicolon-separated (;) list of
               URL prefixes to which the server should respond. For example,
               http://localhost:123. Use "localhost" to indicate that the server
               should listen for requests on any IP address or hostname using
               the specified port and protocol (for example,
               http://localhost:5000). The protocol (http:// or https://) must
               be included with each URL. Supported formats vary between
               servers.

  --storage    Storage path, where we save the logs and database

  --baseurl    The base URL for reverse proxy scenarios

  --demo       Demo mode, you will never need to use this, fuck that fruit
               company...

  --migrate    Will run the migrations then exit the application

  --help       Display this help screen.

  --version    Display version information.

Migrate in progress...
Migrate complete.

Final data migration step.

root@Utilities-desktop:/opt/Ombi# python3 /etc/Ombi/Git/ombi_sqlite_mysql/ombi_sqlite2mysql.py -c /etc/Ombi  --host 127.0.0.1 --db Ombi --user ombi --passwd ombi
Migration tool from SQLite to MySql/MariaDB for ombi (3.0.8) By VSC55

Generate file "database.json":
- Saving in (/etc/Ombi/database.json)... [✓]

MySQL > Connecting... [!!]
* MySQL Error [1049]: Unknown database 'Ombi'
root@Utilities-desktop:/opt/Ombi#

Manually created the Ombi database in mysql and ran the above command again.

# mysql -u ombi -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5642703
Server version: 5.7.33-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql> CREATE DATABASE IF NOT EXISTS `Ombi` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
Query OK, 1 row affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON `Ombi`.* TO 'ombi'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

mysql> quit

Then reran final step above:

# python3 /etc/Ombi/Git/ombi_sqlite_mysql/ombi_sqlite2mysql.py -c /etc/Ombi  --host 127.0.0.1 --db Ombi --user ombi --passwd ombi
Migration tool from SQLite to MySql/MariaDB for ombi (3.0.8) By VSC55

Generate file "database.json":
- Saving in (/etc/Ombi/database.json)... [✓]

MySQL > Connecting... [✓]
- Reading   [............................................................] 0/1
- Error: Table "__EFMigrationsHistory" requiered is not exist in the server MySQL!!!
Read tables [!!]

MySQL > Disconnecting... [✓]
#

Same problem....

cthierman commented 2 years ago

OK, Finally got it to go.... First mistake... yes, running /opt/Ombi/Ombi --migrate without the --storage option pointing to my db's at /etc/Ombi.

Second mistake. I needed to add SslMode=None to all my MySQL transactions to allow /opt/Ombi/Ombi --migrate to connect to the MySql database... Thanks for all the help...