From h.reindl@thelounge.net Mon Aug 13 10:02:21 2018
From: Reindl Harald <h.reindl@thelounge.net>
To: discuss@lists.mariadb.org
Subject: Re: [Maria-discuss] [EXTERNAL] Re:  Copying database files
Date: Mon, 13 Aug 2018 12:02:20 +0200
Message-ID: <b4804796-fe4b-04bc-02ed-d9e88281f785@thelounge.net>
In-Reply-To: <bb720946e630422c8e49622bcf4671f1@DCRIC1EXC05PA.mcp.local>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============7407937496919889056=="

--===============7407937496919889056==
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable



Am 13.08.2018 um 11:57 schrieb Ling, Andy:
>>> I'm using MariaDB 10.2.13 on Windows 7.=C2=A0 All tables are using the Ar=
ia
>>> 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-=
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


--===============7407937496919889056==--


From Andy.Ling@grassvalley.com Mon Aug 13 10:22:39 2018
From: "Ling, Andy" <Andy.Ling@grassvalley.com>
To: discuss@lists.mariadb.org
Subject: Re: [Maria-discuss] [EXTERNAL] Re:  Copying database files
Date: Mon, 13 Aug 2018 10:22:37 +0000
Message-ID: <f4c438cb2fb741f091aff9aee38e94e2@DCRIC1EXC05PA.mcp.local>
In-Reply-To: <b4804796-fe4b-04bc-02ed-d9e88281f785@thelounge.net>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============8726536014491327422=="

--===============8726536014491327422==
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable

> >>> I'm using MariaDB 10.2.13 on Windows 7.=C2=A0 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
> >>
>=20
> no idea why you repsond off-list
>=20
I forgot to use reply all. Most lists, just reply works. Sorry

> > Does that mean this is not valid then...
>=20
> did you read and understand it
>=20

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"
>=20
> "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
>=20

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-threade=
d server? Tests suggest otherwise.

> "Warning: If you do the above live copy, you are doing this on your own
> risk" should be clear
>=20
> in short: nobody right in his mind copies database tables hot
>=20

The code doing this stops everything talking to the database (Yes, I know the=
re 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 serv=
er?

Thanks for the help

Regards

Andy Ling


--===============8726536014491327422==--


From serg@mariadb.org Mon Aug 13 10:27:12 2018
From: Sergei Golubchik <serg@mariadb.org>
To: discuss@lists.mariadb.org
Subject: Re: [Maria-discuss] [EXTERNAL] Re:  Copying database files
Date: Mon, 13 Aug 2018 12:27:05 +0200
Message-ID: <20180813102705.GA13670@meddwl.fritz.box>
In-Reply-To: <f4c438cb2fb741f091aff9aee38e94e2@DCRIC1EXC05PA.mcp.local>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============5082686162752364815=="

--===============5082686162752364815==
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: 7bit

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(a)mariadb.org


--===============5082686162752364815==--


From h.reindl@thelounge.net Mon Aug 13 10:28:28 2018
From: Reindl Harald <h.reindl@thelounge.net>
To: discuss@lists.mariadb.org
Subject: Re: [Maria-discuss] [EXTERNAL] Re: Copying database files
Date: Mon, 13 Aug 2018 12:28:27 +0200
Message-ID: <38cb0489-0f8d-ef85-a408-47644ae3719a@thelounge.net>
In-Reply-To: <f4c438cb2fb741f091aff9aee38e94e2@DCRIC1EXC05PA.mcp.local>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============8899913720699121774=="

--===============8899913720699121774==
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable



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
>=20
> Surely not. If you've locked a table for read only nothing should be able t=
o write to it from any connection otherwise what's the point of a multi-threa=
ded 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


--===============8899913720699121774==--


From h.reindl@thelounge.net Mon Aug 13 10:29:55 2018
From: Reindl Harald <h.reindl@thelounge.net>
To: discuss@lists.mariadb.org
Subject: Re: [Maria-discuss] [EXTERNAL] Re: Copying database files
Date: Mon, 13 Aug 2018 12:29:54 +0200
Message-ID: <ba96af2a-44f7-efd1-f545-3a673f2336ad@thelounge.net>
In-Reply-To: <20180813102705.GA13670@meddwl.fritz.box>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============4857568424905908679=="

--===============4857568424905908679==
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: 7bit


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


--===============4857568424905908679==--


From Andy.Ling@grassvalley.com Mon Aug 13 10:30:44 2018
From: "Ling, Andy" <Andy.Ling@grassvalley.com>
To: discuss@lists.mariadb.org
Subject: Re: [Maria-discuss] Copying database files
Date: Mon, 13 Aug 2018 10:30:42 +0000
Message-ID: <5f51bb263d0d4148b0ddd08cd356a05c@DCRIC1EXC05PA.mcp.local>
In-Reply-To: <20180813102705.GA13670@meddwl.fritz.box>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============4777927619776030342=="

--===============4777927619776030342==
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable

> 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?
>=20
> I'd expect FLUSH TABLES ... FOR EXPORT to work.
>=20
> What exactly are you getting? Can you perhaps create a test case? Or
> just report a bug at jira.mariadb.org?
>=20

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 b=
e 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 delive=
r this message to anyone. In such event, you should destroy the message and k=
indly notify the sender by reply e-mail. It is understood that opinions or co=
nclusions that do not relate to the official business of the company are neit=
her given nor endorsed by the company. Thank You.


--===============4777927619776030342==--


From Andy.Ling@grassvalley.com Mon Aug 13 10:40:36 2018
From: "Ling, Andy" <Andy.Ling@grassvalley.com>
To: discuss@lists.mariadb.org
Subject: Re: [Maria-discuss] Copying database files
Date: Mon, 13 Aug 2018 10:40:34 +0000
Message-ID: <f12472d120154ecc87b5c7b2445a9601@DCRIC1EXC05PA.mcp.local>
In-Reply-To: <38cb0489-0f8d-ef85-a408-47644ae3719a@thelounge.net>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============5580815743635826569=="

--===============5580815743635826569==
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable

> 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.
>=20
> "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
>=20
> and how do you make a script that locks the table and continues without
> closing the session to mysqld?
>=20

This is being driven by a Java program. It opens a connection using the maria=
db-java-client. That connection isn't released until the end of the copying p=
rocess when the tables are unlocked and then it only goes back into a pool, s=
o 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 delive=
r this message to anyone. In such event, you should destroy the message and k=
indly notify the sender by reply e-mail. It is understood that opinions or co=
nclusions that do not relate to the official business of the company are neit=
her given nor endorsed by the company. Thank You.


--===============5580815743635826569==--


From serg@mariadb.org Mon Aug 13 12:33:22 2018
From: Sergei Golubchik <serg@mariadb.org>
To: discuss@lists.mariadb.org
Subject: Re: [Maria-discuss] [EXTERNAL] Re: Copying database files
Date: Mon, 13 Aug 2018 14:33:16 +0200
Message-ID: <20180813123316.GB21854@meddwl.fritz.box>
In-Reply-To: <38cb0489-0f8d-ef85-a408-47644ae3719a@thelounge.net>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============0460700604973667530=="

--===============0460700604973667530==
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: 7bit

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(a)mariadb.org


--===============0460700604973667530==--


From h.reindl@thelounge.net Mon Aug 13 12:52:03 2018
From: Reindl Harald <h.reindl@thelounge.net>
To: discuss@lists.mariadb.org
Subject: Re: [Maria-discuss] [EXTERNAL] Re: Copying database files
Date: Mon, 13 Aug 2018 14:52:02 +0200
Message-ID: <f3adef76-c45e-fcc8-5242-840e7d859aa9@thelounge.net>
In-Reply-To: <20180813123316.GB21854@meddwl.fritz.box>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============2613927715857481850=="

--===============2613927715857481850==
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: 7bit



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



--===============2613927715857481850==--


From Andy.Ling@grassvalley.com Tue Aug 14 09:13:39 2018
From: "Ling, Andy" <Andy.Ling@grassvalley.com>
To: discuss@lists.mariadb.org
Subject: Re: [Maria-discuss] Copying database files
Date: Tue, 14 Aug 2018 09:13:36 +0000
Message-ID: <3ff8e49a36144e00be60aa40ea221113@DCRIC1EXC05PA.mcp.local>
In-Reply-To: <ba96af2a-44f7-efd1-f545-3a673f2336ad@thelounge.net>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============5752519886663045071=="

--===============5752519886663045071==
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable

> >> 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?
>=20

It looks like I need to run aria_chk -z every time before I do the copy. Is t=
hat 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 car=
e, 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 delive=
r this message to anyone. In such event, you should destroy the message and k=
indly notify the sender by reply e-mail. It is understood that opinions or co=
nclusions that do not relate to the official business of the company are neit=
her given nor endorsed by the company. Thank You.


--===============5752519886663045071==--