Re: [Maria-discuss] [EXTERNAL] Re: Copying database files
Am 13.08.2018 um 11:57 schrieb Ling, Andy:
I'm using MariaDB 10.2.13 on Windows 7. All tables are using the Aria engine.
I am trying to initialise a slave database by copying the files between PCs, but when I try and use the copied files I get the error
Table is from another system and must be zerofilled or repaired to be usable on this system
Repairing the tables fixes this, but I'd rather not have to so that.
I have tried various things suggested to stop this, but nothing seems to help. On the source server I have run aria_chk -zerofill on all the tables. Before copying the tables I run FLUSH TABLES <table list> FOR EXPORT, but I still get the error. jesus don't copy tbale files around while the server is running
no idea why you repsond off-list
Does that mean this is not valid then...
did you read and understand it
https://mariadb.com/kb/en/library/copying-tables-between-different-mariadb-d...
how make you sure "The server is not accessing the tables during the copy process" "The advantage of FLUSH TABLES table_name FOR EXPORT is that the table is read locked until UNLOCK TABLES is executed" is not really true because that's only valid for the running connection "Warning: If you do the above live copy, you are doing this on your own risk" should be clear in short: nobody right in his mind copies database tables hot
I'm using MariaDB 10.2.13 on Windows 7. All tables are using the Aria engine.
I am trying to initialise a slave database by copying the files between PCs, but when I try and use the copied files I get the error
Table is from another system and must be zerofilled or repaired to be usable on this system
Repairing the tables fixes this, but I'd rather not have to so that.
I have tried various things suggested to stop this, but nothing seems to help. On the source server I have run aria_chk -zerofill on all the tables. Before copying the tables I run FLUSH TABLES <table list> FOR EXPORT, but I still get the error. jesus don't copy tbale files around while the server is running
no idea why you repsond off-list
I forgot to use reply all. Most lists, just reply works. Sorry
Does that mean this is not valid then...
did you read and understand it
Yes I read and understood it. I've been doing this for the last 10 years with MySQL without any problem. I was assuming I could do the same with MariaDB.
how make you sure "The server is not accessing the tables during the copy process"
"The advantage of FLUSH TABLES table_name FOR EXPORT is that the table is read locked until UNLOCK TABLES is executed" is not really true because that's only valid for the running connection
Surely not. If you've locked a table for read only nothing should be able to write to it from any connection otherwise what's the point of a multi-threaded server? Tests suggest otherwise.
"Warning: If you do the above live copy, you are doing this on your own risk" should be clear
in short: nobody right in his mind copies database tables hot
The code doing this stops everything talking to the database (Yes, I know there could be someone fiddling remotely in a shell, but this is very unlikely). Even tables which are never written to cause this error So if I'm careful, is there a way to make this work without stopping the server? Thanks for the help Regards Andy Ling
Hi, Andy! On Aug 13, Ling, Andy wrote:
The code doing this stops everything talking to the database (Yes, I know there could be someone fiddling remotely in a shell, but this is very unlikely). Even tables which are never written to cause this error
So if I'm careful, is there a way to make this work without stopping the server?
I'd expect FLUSH TABLES ... FOR EXPORT to work. What exactly are you getting? Can you perhaps create a test case? Or just report a bug at jira.mariadb.org? Regards, Sergei Chief Architect MariaDB and security@mariadb.org
Am 13.08.2018 um 12:27 schrieb Sergei Golubchik:
On Aug 13, Ling, Andy wrote:
The code doing this stops everything talking to the database (Yes, I know there could be someone fiddling remotely in a shell, but this is very unlikely). Even tables which are never written to cause this error
So if I'm careful, is there a way to make this work without stopping the server?
I'd expect FLUSH TABLES ... FOR EXPORT to work.
What exactly are you getting? Can you perhaps create a test case? Or just report a bug at jira.mariadb.org?
https://blogs.vmware.com/vsphere/2014/12/mysql-backup-with-vdp.html "Another item to be aware of with this statement is that the read lock is released as soon as the session that made the call ends" is still true for MariaDB 10.2.x
The code doing this stops everything talking to the database (Yes, I know there could be someone fiddling remotely in a shell, but this is very unlikely). Even tables which are never written to cause this error
So if I'm careful, is there a way to make this work without stopping the server?
I'd expect FLUSH TABLES ... FOR EXPORT to work.
What exactly are you getting? Can you perhaps create a test case? Or just report a bug at jira.mariadb.org?
It looks like I need to run aria_chk -z every time before I do the copy. Is that expected? I guess if something has been "freed" in the table the free block needs to be zero filled, presumably for security reasons. For my application I don't care, so I'm wondering if there is an option to avoid this. Thanks for your help. Andy Ling ********************************************************************** DISCLAIMER: Privileged and/or Confidential information may be contained in this message. If you are not the addressee of this message, you may not copy, use or deliver this message to anyone. In such event, you should destroy the message and kindly notify the sender by reply e-mail. It is understood that opinions or conclusions that do not relate to the official business of the company are neither given nor endorsed by the company. Thank You.
On Aug 13, Ling, Andy wrote:
The code doing this stops everything talking to the database (Yes, I know there could be someone fiddling remotely in a shell, but this is very unlikely). Even tables which are never written to cause this error
So if I'm careful, is there a way to make this work without stopping the server?
I'd expect FLUSH TABLES ... FOR EXPORT to work.
What exactly are you getting? Can you perhaps create a test case? Or just report a bug at jira.mariadb.org?
If I do a check table I get.. MariaDB [quentin_v3]> check table aaf; +----------------+-------+----------+---------------------------------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------------+-------+----------+---------------------------------------------------------------------------------------------+ | quentin_v3.aaf | check | error | Table is from another system and must be zerofilled or repaired to be usable on this system | | quentin_v3.aaf | check | error | Corrupt | +----------------+-------+----------+---------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) Regards Andy Ling ********************************************************************** DISCLAIMER: Privileged and/or Confidential information may be contained in this message. If you are not the addressee of this message, you may not copy, use or deliver this message to anyone. In such event, you should destroy the message and kindly notify the sender by reply e-mail. It is understood that opinions or conclusions that do not relate to the official business of the company are neither given nor endorsed by the company. Thank You.
Am 13.08.2018 um 12:22 schrieb Ling, Andy:
"The advantage of FLUSH TABLES table_name FOR EXPORT is that the table is read locked until UNLOCK TABLES is executed" is not really true because that's only valid for the running connection
Surely not. If you've locked a table for read only nothing should be able to write to it from any connection otherwise what's the point of a multi-threaded server? Tests suggest otherwise.
surely YES https://blogs.vmware.com/vsphere/2014/12/mysql-backup-with-vdp.html "Another item to be aware of with this statement is that the read lock is released as soon as the session that made the call ends. When the pre-freeze-script finishes, the session is closed" - try it out and how do you make a script that locks the table and continues without closing the session to mysqld? -- "I forgot to use reply all. Most lists, just reply works" - FOR THE SAKE OF GOD reply just to the list, no reply all, no reply to from-address - that's why proper mail clients have a reply-list button in case headers in the mail exists which is true here and with your idiotic reply-all you broke my reply-list button and that breaks threading now
"The advantage of FLUSH TABLES table_name FOR EXPORT is that the
is read locked until UNLOCK TABLES is executed" is not really true because that's only valid for the running connection
Surely not. If you've locked a table for read only nothing should be able to write to it from any connection otherwise what's the point of a multi-
Am 13.08.2018 um 12:22 schrieb Ling, Andy: table threaded server? Tests suggest otherwise.
"Another item to be aware of with this statement is that the read lock is released as soon as the session that made the call ends. When the pre-freeze-script finishes, the session is closed" - try it out
and how do you make a script that locks the table and continues without closing the session to mysqld?
This is being driven by a Java program. It opens a connection using the mariadb-java-client. That connection isn't released until the end of the copying process when the tables are unlocked and then it only goes back into a pool, so may not be closed at all. Regards Andy Ling ********************************************************************** DISCLAIMER: Privileged and/or Confidential information may be contained in this message. If you are not the addressee of this message, you may not copy, use or deliver this message to anyone. In such event, you should destroy the message and kindly notify the sender by reply e-mail. It is understood that opinions or conclusions that do not relate to the official business of the company are neither given nor endorsed by the company. Thank You.
Hi, Reindl! On Aug 13, Reindl Harald wrote:
Am 13.08.2018 um 12:22 schrieb Ling, Andy:
"The advantage of FLUSH TABLES table_name FOR EXPORT is that the table is read locked until UNLOCK TABLES is executed" is not really true because that's only valid for the running connection
Surely not. If you've locked a table for read only nothing should be able to write to it from any connection otherwise what's the point of a multi-threaded server? Tests suggest otherwise.
surely YES
https://blogs.vmware.com/vsphere/2014/12/mysql-backup-with-vdp.html "Another item to be aware of with this statement is that the read lock is released as soon as the session that made the call ends. When the pre-freeze-script finishes, the session is closed" - try it out
and how do you make a script that locks the table and continues without closing the session to mysqld?
It's possible. In one of my personal projects I've used something like echo "flush tables with read lock;\n\! mysqlhotcopy ..." | mysql Regards, Sergei Chief Architect MariaDB and security@mariadb.org
Am 13.08.2018 um 14:33 schrieb Sergei Golubchik:
On Aug 13, Reindl Harald wrote:
Am 13.08.2018 um 12:22 schrieb Ling, Andy:
"The advantage of FLUSH TABLES table_name FOR EXPORT is that the table is read locked until UNLOCK TABLES is executed" is not really true because that's only valid for the running connection
Surely not. If you've locked a table for read only nothing should be able to write to it from any connection otherwise what's the point of a multi-threaded server? Tests suggest otherwise.
surely YES
https://blogs.vmware.com/vsphere/2014/12/mysql-backup-with-vdp.html "Another item to be aware of with this statement is that the read lock is released as soon as the session that made the call ends. When the pre-freeze-script finishes, the session is closed" - try it out
and how do you make a script that locks the table and continues without closing the session to mysqld?
It's possible. In one of my personal projects I've used something like
echo "flush tables with read lock;\n\! mysqlhotcopy ..." | mysql
please read again what i quoted from the article! it is *not* possible except for some very limited usecases * vmware calls a shell script which should lock tables * vmware does fs-freeze, the script is completly finished * vmware takes a snaphot of the guest * from the moment the script with "flush tables with read lock;" has finsihed the lock is gone
participants (3)
-
Ling, Andy -
Reindl Harald -
Sergei Golubchik