unusable binlog when 2 connections use the same temporary table names

Description:
This may be already known, but it seems to be a long-lasting bug. In 3.23.47 linux and
few-days-old 4.1 linux, if 2 connections create temporary tables with the same name, the binlog
gets unusable.

How-To-Repeat:
In thread 1 do :
create table test.t (a int);
keep this thread open (keep the mysql client open)
In thread 2 do :
create table test.t (b char(1));
flush logs;
And examine the binlog :
it contains, in short :
create table test.t (a int);
create table test.t (b char(1));
Which will result in errors when piping the output of mysqlbinlog into mysql ("table t already
exists") and make the binlog unusable (and recovery impossible).

Fix:
?


___________________________________________________________
Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français !
Yahoo! Mail : http://fr.mail.yahoo.com

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12804@lists.mysql.com
To unsubscribe, e-mail

Re: unusable binlog when 2 connections use the same temporary table names

On Sunday 20 October 2002 17:51, Guilhem Bichot wrote:
> Description:
> This may be already known, but it seems to be a long-lasting bug. In 3.=
23.47=20
linux and
> few-days-old 4.1 linux, if 2 connections create temporary tables with t=
he=20
same name, the binlog
> gets unusable.
> =20

Sorry I I'm not sure about your example:
You do not have tables as "temporary" below so it is obvious it shall not=
work=20
- it just should fail upon second table creation.

Also I'm not sure what do you mean by "unusable binlog" does replication=20
breaks for you or you're trying to dump the log to text format and see th=
is=20
text in it ?

Replication is known to work with temporary tables, in case you do not sh=
ut=20
down slave while the it has temporary tables open, while you can't dump i=
t to=20
text file and use properly. =20

> How-To-Repeat:
> In thread 1 do :
> create table test.t (a int);
> keep this thread open (keep the mysql client open)
> In thread 2 do :
> create table test.t (b char(1));
> flush logs;
> And examine the binlog :
> it contains, in short :
> create table test.t (a int);
> create table test.t (b char(1));
> Which will result in errors when piping the output of mysqlbinlog into =
mysql=20
("table t already
> exists") and make the binlog unusable (and recovery impossible).
>=20
> Fix:
> ?
>=20
>=20
> ___________________________________________________________
> Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en fran=E7ais !
> Yahoo! Mail : http://fr.mail.yahoo.com
>=20
> ------------------------------------------------------------ ---------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>=20
> To request this thread, e-mail bugs-thread12804@lists.mysql.com
> To unsubscribe, e-mail
>=20
>=20

--=20
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Peter Zaitsev
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Moscow, Russia
<___/ www.mysql.com M: +7 095 725 4955


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12805@lists.mysql.com
To unsubscribe, e-mail

unusable binlog when 2 connections use the same temporary table names

Hi!

>>>>> "Guilhem" == Guilhem Bichot writes:

Guilhem> Description:
Guilhem> This may be already known, but it seems to be a long-lasting bug. In 3.23.47 linux and
Guilhem> few-days-old 4.1 linux, if 2 connections create temporary tables with the same name, the binlog
Guilhem> gets unusable.

Guilhem> How-To-Repeat:
Guilhem> In thread 1 do :
Guilhem> create table test.t (a int);
Guilhem> keep this thread open (keep the mysql client open)
Guilhem> In thread 2 do :
Guilhem> create table test.t (b char(1));
Guilhem> flush logs;
Guilhem> And examine the binlog :
Guilhem> it contains, in short :
Guilhem> create table test.t (a int);
Guilhem> create table test.t (b char(1));
Guilhem> Which will result in errors when piping the output of mysqlbinlog into mysql ("table t already
Guilhem> exists") and make the binlog unusable (and recovery impossible).

As Peter pointed out, you are not using CREATE TEMPORARY here, but I
understand what you are hinting at.

This is actually a bug in how mysqlbinlog works when piping data to a
server, not in the binary log.

This doesn't affect replication that works as follows:

The binary log contains information about which thread created a
temporary file. On the slave this information is automaticly appended
to the internal temporary file name on usage, which avoids conflicts
between different threads.

We have plans to fix this by allowing mysqlbinlog to connect directly
to a server and feed the binary log to it in 'raw' format.
(This will take care of the temporary table problem)

I just come up with another solution that will make it easy to also
use 'pipe' in the future. (We still want to implement the above
extension to mysqlbinlog as this will be faster and more efficient).

- Add a status byte in the binary log for all normal queries.
- In this status byte, define one bit to mean 'using temporary table
for this query'
- If mysqlbinlong notices that this bit is set, it should generate an
extra command before the query:

SET @temporary_file_suffix=####

The above number would be used as 'slave_proxy_id' is used now
in open_table.

To Nick (responsible for replication and binary log):

Note that we have to change slave_proxy_id in 4.0 as it at this
point doesn't work when we create temporary tables in many servers
(It only includes the thread_id, when it should be server_id:thread_id)

Regards,
Monty

--
For technical support contracts, goto https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Michael Widenius
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ www.mysql.com

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12814@lists.mysql.com
To unsubscribe, e-mail