Re: [Maria-discuss] [EXTERNAL] Re: Copying database files
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 ___ 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
Re: [Maria-discuss] [EXTERNAL] Re: Copying database files
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 secur...@mariadb.org ___ 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
Re: [Maria-discuss] [EXTERNAL] Re: Copying database files
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 ___ 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
Re: [Maria-discuss] [EXTERNAL] Re: Copying database files
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 ___ 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
Re: [Maria-discuss] [EXTERNAL] Re: Copying database files
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 secur...@mariadb.org ___ 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
Re: [Maria-discuss] [EXTERNAL] Re: Copying database files
> >>> 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 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 ___ 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
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 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-databases-and-mariadb-servers/ 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 ___ 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
