[Maria-discuss] Missing table rows when importing a MySQL 5.7.8 database into MariaDB 10.4.8 database
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 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. 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@elliotmywebguy.com https://www.ElliotMyWebGuy.com
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@elliotmywebguy.com https://www.ElliotMyWebGuy.com
-- Elliot Holden (404)884-8593 elliot@elliotmywebguy.com https://www.ElliotMyWebGuy.com
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@elliotmywebguy.com https://www.ElliotMyWebGuy.com
-- Elliot Holden (404)884-8593 elliot@elliotmywebguy.com https://www.ElliotMyWebGuy.com
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
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@elliotmywebguy.com https://www.ElliotMyWebGuy.com
-- Elliot Holden (404)884-8593 elliot@elliotmywebguy.com https://www.ElliotMyWebGuy.com
_______________________________________________ Mailing list:https://launchpad.net/~maria-discuss Post to :maria-discuss@lists.launchpad.net Unsubscribe :https://launchpad.net/~maria-discuss More help :https://help.launchpad.net/ListHelp
-- Elliot Holden (404)884-8593 elliot@elliotmywebguy.com https://www.ElliotMyWebGuy.com
Hi
I suspect you've got some utf data in your MySQL and it's being excluded
with the Latin1 character set. Try creating your MariaDB database with UTF8
as it's character set.
Cheers
Peter
On Wed, 19 Aug 2020 at 14:44, Elliot Holden
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-8593elliot@elliotmywebguy.comhttps://www.ElliotMyWebGuy.com
-- Elliot Holden (404)884-8593elliot@elliotmywebguy.comhttps://www.ElliotMyWebGuy.com
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Peter McLarty Leader and Technologist 0402094238 http://petermclarty.setmore.com/
Thanks Peter, Yes I did not the character set was different so I tried your suggestion: First I tried: alter database my_database character set = 'utf8'; I tried the import again and I still got missing rows. Then I tried: drop database my_database; create database myw_database character set = 'utf8'; Again, I tried the import and I still got missing rows. Also tried stopping and restarting the mariadb daemon and did the import again to no avail. E. On 8/19/20 5:27 AM, Peter McLarty wrote:
Hi I suspect you've got some utf data in your MySQL and it's being excluded with the Latin1 character set. Try creating your MariaDB database with UTF8 as it's character set.
Cheers Peter
On Wed, 19 Aug 2020 at 14:44, Elliot Holden
mailto:elliot@elliotmywebguy.com> 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@elliotmywebguy.com mailto:elliot@elliotmywebguy.com https://www.ElliotMyWebGuy.com
-- Elliot Holden (404)884-8593 elliot@elliotmywebguy.com mailto:elliot@elliotmywebguy.com https://www.ElliotMyWebGuy.com
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net mailto:maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Peter McLarty Leader and Technologist 0402094238 http://petermclarty.setmore.com/
-- Elliot Holden (404)884-8593 elliot@elliotmywebguy.com https://www.ElliotMyWebGuy.com
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).
Noted, 29 not 600.
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 am guessing it's 29 in 'all' of 'all rows are imported'. Does wp_backup.sql actually have *all* 600?
H Andrei, Thanks for the reply. To answer your question when I import the *my_dump.sql* (yes it does contain all 600 rows in that dump file) into a database on the MySQL server (which uses *utf8* charset) then all works fine. All 600 rows are imported. But when I used that same *my_dump.sql* file to import into a server running MariaDB then only 29 rows get imported. The MariaDB server is has *latin1* as the default character set. So I tried to do an alter command on the specific database in question to change it's character set to *utf8*. When that did not work I dropped the database all together and re-created it using the *utf8* database and that still did not work. I run all the commands on the MariaDB server because I wanted to make sure the version of *msyql* or *mysqldump* was not the issue. Which it does not seem to be the issue since I can export and import remotely to the MySQL server just fine (from the MariaDB server) On 8/20/20 5:00 AM, andrei.elkin@pp.inet.fi wrote:
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).
Noted, 29 not 600.
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 am guessing it's 29 in 'all' of 'all rows are imported'. Does wp_backup.sql actually have *all* 600?
-- Elliot Holden (404)884-8593 elliot@elliotmywebguy.com https://www.ElliotMyWebGuy.com
Elliot, howdy. Thanks for narrating it pretty verbose! I can only suggest to trace or bisect the 600 rows insertion to catch where it fails. Cheers, Andrei
H Andrei,
Thanks for the reply. To answer your question when I import the my_dump.sql (yes it does contain all 600 rows in that dump file) into a database on the MySQL server (which uses utf8 charset) then all works fine. All 600 rows are imported.
But when I used that same my_dump.sql file to import into a server running MariaDB then only 29 rows get imported. The MariaDB server is has latin1 as the default character set. So I tried to do an alter command on the specific database in question to change it's character set to utf8. When that did not work I dropped the database all together and re-created it using the utf8 database and that still did not work.
I run all the commands on the MariaDB server because I wanted to make sure the version of msyql or mysqldump was not the issue. Which it does not seem to be the issue since I can export and import remotely to the MySQL server just fine (from the MariaDB server)
On 8/20/20 5:00 AM, andrei.elkin@pp.inet.fi wrote:
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).
Noted, 29 not 600.
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 am guessing it's 29 in 'all' of 'all rows are imported'. Does wp_backup.sql actually have *all* 600?
Thank you for the reply. Yes I tried to take another users advice and do SHOW WARNINGS but I kinda need a little more detail how to do this. See below: 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/20/20 12:11 PM, andrei.elkin@pp.inet.fi wrote:
Elliot, howdy.
Thanks for narrating it pretty verbose!
I can only suggest to trace or bisect the 600 rows insertion to catch where it fails.
Cheers,
Andrei
H Andrei,
Thanks for the reply. To answer your question when I import the my_dump.sql (yes it does contain all 600 rows in that dump file) into a database on the MySQL server (which uses utf8 charset) then all works fine. All 600 rows are imported.
But when I used that same my_dump.sql file to import into a server running MariaDB then only 29 rows get imported. The MariaDB server is has latin1 as the default character set. So I tried to do an alter command on the specific database in question to change it's character set to utf8. When that did not work I dropped the database all together and re-created it using the utf8 database and that still did not work.
I run all the commands on the MariaDB server because I wanted to make sure the version of msyql or mysqldump was not the issue. Which it does not seem to be the issue since I can export and import remotely to the MySQL server just fine (from the MariaDB server)
On 8/20/20 5:00 AM, andrei.elkin@pp.inet.fi wrote:
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).
Noted, 29 not 600.
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 am guessing it's 29 in 'all' of 'all rows are imported'. Does wp_backup.sql actually have *all* 600?
-- Elliot Holden (404)884-8593 elliot@elliotmywebguy.com https://www.ElliotMyWebGuy.com
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
That's better take for analysis. Could you bisect table_dump.sql INSERT's row list to find out the first that won't make it to maria server? If you'll find such row, the next step I suggest to report a bug on Jira providing the table definition and the INSERT(s) which would be pretty easy to reproduce and hopefully to fix too. Thanks as well! Andrei
Thank you Benoit, Andrei, Michael, Peter and anyone else. Long story short, I updated MariaDB Server from 10.4.8 to 10.4.14 and problem solved. I don't have the energy to go back and install 10.4.8 on another server just to see if I can recreate the problem (maybe later) but lesson learned, just do an update first, SMH literally shaking my head. Sorry for the bother and thank you for the suggestions as I did learn a couple tips about checking the Char Set. But just a note, apparently the Char Set had nothing to do with it because I ended up importing utf8 into *latin1* just fine. And looking back I see Michael mentioned:///"//Database character set shouldn't have an effect as the CREATE TABLE in the mysqldump output should specify the character sets for all columns for the used tables."/ Sincerely, Elliot On 8/20/20 1:00 PM, andrei.elkin@pp.inet.fi wrote:
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
That's better take for analysis. Could you bisect table_dump.sql INSERT's row list to find out the first that won't make it to maria server?
If you'll find such row, the next step I suggest to report a bug on Jira providing the table definition and the INSERT(s) which would be pretty easy to reproduce and hopefully to fix too.
Thanks as well!
Andrei
-- Elliot Holden (404)884-8593 elliot@elliotmywebguy.com https://www.ElliotMyWebGuy.com
Am 27.08.20 um 03:30 schrieb Elliot Holden:
Long story short, I updated MariaDB Server from 10.4.8 to 10.4.14 and problem solved.
what was the point using a nearly one year old version to begin with (sorry for not realize the outfated version in the subject after your first post) i won't even consider touching the second bugfix release of a new major vesion in production and the first thing i do when whatever softtware has issues "is there a newer version which probably fix that" Name Release Date Release Status 10.4.14 2020-08-10 Stable 10.4.13 2020-05-12 Stable 10.4.12 2020-01-28 Stable 10.4.11 2019-12-11 Stable 10.4.10 2019-11-08 Stable 10.4.8 2019-09-11 Stable 10.4.7 2019-07-31 Stable 10.4.6 2019-06-18 Stable
Am 27.08.20 um 08:19 schrieb elliot@elliotmywebguy.com:
No reason. I stated what version I was using in my first post and even asked if that could have been the issue. Lesson learned.
indeed, in the middle of the post "Maybe do I need to upgrade to MariaDB 10.4.14" the amount of bugfixes is amazing in every point release https://mariadb.com/kb/en/mariadb-1049-changelog/ https://mariadb.com/kb/en/mariadb-10410-changelog/ https://mariadb.com/kb/en/mariadb-10411-changelog/ https://mariadb.com/kb/en/mariadb-10412-changelog/ https://mariadb.com/kb/en/mariadb-10413-changelog/ https://mariadb.com/kb/en/mariadb-10414-changelog/
On Aug 27, 2020 2:05 AM, Reindl Harald
wrote: Am 27.08.20 um 03:30 schrieb Elliot Holden: > Long story short, I updated MariaDB Server from 10.4.8 to 10.4.14 and > problem solved.
what was the point using a nearly one year old version to begin with (sorry for not realize the outfated version in the subject after your first post)
i won't even consider touching the second bugfix release of a new major vesion in production and the first thing i do when whatever softtware has issues "is there a newer version which probably fix that"
Name Release Date Release Status 10.4.14 2020-08-10 Stable 10.4.13 2020-05-12 Stable 10.4.12 2020-01-28 Stable 10.4.11 2019-12-11 Stable 10.4.10 2019-11-08 Stable 10.4.8 2019-09-11 Stable 10.4.7 2019-07-31 Stable 10.4.6 2019-06-18 Stable
Hi!
Hi!
Wed, 12 Aug 2020, 06:26 Elliot Holden,
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 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 am not aware of any bug where one would lose data when importing data from mysqldump (except if client character sets are different, which isn't in this case). Database character set shouldn't have an effect as the CREATE TABLE in the mysqldump output should specify the character sets for all columns for the used tables. You can of course try to change the character set of the database and see if it helps. If there would be any problems, you should get warnings/errors when trying to execute mysql < backup.sql. Any chance you could upload the dataset to our ftp server so that I can try to debug this? https://mariadb.com/kb/en/meta/mariadb-ftp-server/ I would like to have it even if changing the character set for the database would fix the problem, as I want to know why you didn't get a proper error message for the failure. If you send me an email when you have uploaded the data I will take a look at it and report my findings. Regards, Monty
participants (7)
-
andrei.elkin@pp.inet.fi
-
Benoit Plessis
-
Elliot Holden
-
elliot@elliotmywebguy.com
-
Michael Widenius
-
Peter McLarty
-
Reindl Harald