← Back to team overview

maria-discuss team mailing list archive

Re: Missing table rows when importing a MySQL 5.7.8 database into MariaDB 10.4.8 database

 

Thanks Bernoit,


How do I check the result of "SHOW WARNINGS"?

I did read that I can start the mysql client with --show-warnings but as far as showing the warnings when I'm importing a database or table using:

mysql -u username -pPassword --show-warnings -h localhost my_database < my_dump.sql


I got no warnings when using the above command.

Also I did make a dump of just the table in question and nothing changed in my results. So I did mysqldump -u username -pPassword -h remote_hosts my_remote_database my_table > table_dump.sql


Thank you for the help

E.

On 8/19/20 4:45 AM, Benoit Plessis wrote:
Hi Elliot,

You should try editing your backup file to only importing wp_options data and check the status of the requests, when an insert execute there is a return string which indicate how many records where created and how many errors/warnings there where.

Then check the result of "SHOW WARNINGS" and "|SHOW INNODB STATUS"|
|
|
|Also the "--opt" should be the default in mysqldump but you could verify that your sql dump file does have charset setup and everything
|

Regards,

On 19/08/2020 06:44, Elliot Holden wrote:

Sending this again because I got no response the first time and didn't know if it was because I did something wrong protocol wise or if it was just because no one had any idea on what my issue could be.

If anyone has a link they can point me to in either case it would be greatly appreciated. I will keep searching as well.

Thanks


E.

On 8/11/20 11:25 PM, Elliot Holden wrote:

Hello All,

New to the list, so maybe an expert has the easy answer to this one...

When I use mysqldump to backup a MySQL 5.7.8 database then import that database into a MariaDB 10.4.8 database, at least one (maybe more) of the imported tables is missing rows. This is a WordPress database and the table in question is the *wp_options* table. I'm only getting 29 rows imported when there should be over 600 (in the original MySQL database).

I'm using the *mysqldump* and *mysql* utilities located on the MariaDB server:

mysqldump  Ver 10.17 Distrib 10.4.8-MariaDB, for Linux (x86_64)
mysql  Ver 15.1 Distrib 10.4.8-MariaDB, for Linux (x86_64) using readline 5.1


My steps as performed on the MariaDB server (the server I'm importing to).

1. mysqldump -u username -pPassword -h remote_mysql_server database_name > wp_backup.sql

2.mysql -u username -pPassword -h localhost database_name < wp_backup.sql

_*
*_

When I run *mysql* (on the local MariaDB server) and import the wp_backup.sql to the remote_mysql_server everything works fine and all rows are importing into a test database out there_*:*_

mysql -u username -pPassword -h remote_mysql_server database_name < wp_backup.sql

So the above works


I know I'm probably missing something simple but just don't know... they both seem to use the same InnoDB engine. I did notice the Db character set is different between the databases though (highlighted in blue below).

Maybe do I need to upgrade to MariaDB *10.4.14*

Any help would be greatly appreciated.....?


Details about each server listed below:

_*
*_

_*MySQL*_

Server version:        5.7.28-log Source distribution
Protocol version:    10
Server characterset:    latin1
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8

*************************** 1. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES

_*
*__*MariaDB*_

Server version:        5.5.5-10.4.8-MariaDB MariaDB Server
Protocol version:    10
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8

*************************** 7. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, foreign keys and encryption for tables
Transactions: YES
          XA: YES
  Savepoints: YES

--
Elliot Holden
‪(404)884-8593‬
elliot@xxxxxxxxxxxxxxxxxx
https://www.ElliotMyWebGuy.com
--
Elliot Holden
‪(404)884-8593‬
elliot@xxxxxxxxxxxxxxxxxx
https://www.ElliotMyWebGuy.com

_______________________________________________
Mailing list:https://launchpad.net/~maria-discuss
Post to     :maria-discuss@xxxxxxxxxxxxxxxxxxx
Unsubscribe :https://launchpad.net/~maria-discuss
More help   :https://help.launchpad.net/ListHelp


--
Elliot Holden
‪(404)884-8593‬
elliot@xxxxxxxxxxxxxxxxxx
https://www.ElliotMyWebGuy.com


References