Hi there! I'm having a little Problem with the mysql binary log which I
think could actually be a bug of some sort, though I wasn't able to try it
on a new Version of MySQL.
My system configuration is:
MySQL 3.23.41´, Win32 binary Release
WindowsNT 4.0 Service Pack 6
PII, 256 MB Ram
I am using the binary log to back up a databse called 'www' to files like
c:/server/mysql/data/www-bin.???
In this database there is a table 'satzungen', create Syntax is like this:
CREATE TABLE satzungen (
rowid mediumint(8) unsigned zerofill NOT NULL auto_increment,
titel tinytext NOT NULL,
inhalt mediumtext NOT NULL,
data longblob NOT NULL,
content_type varchar(255) NOT NULL default '',
gruppe enum('Allgemeine Verwaltung','Öffentliche Sicherheit und
Ordnung','Schulen') NOT NULL default 'Allgemeine Verwaltung',
kuerzel tinytext NOT NULL,
kennung varchar(10) NOT NULL default '',
last_change date NOT NULL default '0000-00-00',
PRIMARY KEY (rowid),
FULLTEXT KEY titel (titel),
FULLTEXT KEY inhalt (inhalt),
FULLTEXT KEY kuerzel (kuerzel),
KEY last_changed (last_change)
) TYPE=MyISAM;
due to some specific design reasons I use the following SQL-Statement to
write data into the file
INSERT INTO satzungen 'titel' VALUES('');
SET @rowid = LAST_INSERT_ID();
#...let's conser that @rowid now has a value of 125
#...some other statements, not concerning this table...
UPDATE satzungen SET titel='title', inhalt = 'context',
content_type='text/plain', data='data',kuerzel='kuerzel', kennung='kennung',
last_change = NOW() WHERE rowid = @rowid;
this is logged into c:/server/mysql/data/www-bin.007
what happens is that when I use "mysqlbinlog --result-file=c:/log.sql
c:/server/mysql/data/www-bin.007" it gets me a mysql dump which looks like
this:
#...other statements
SET INSERT_ID = 125;
SET TIMESTAMP=1017998537;
INSERT INTO satzungen (titel) values('');
SET TIMESTAMP=1017998537;
UPDATE
satzungen
SET
titel = 'title',
inhalt = 'context',
content_type = 'text/plain',
data = 'data',
kuerzel = 'kuerzel',
kennung='kennung'
last_change = NOW(),
WHERE
rowid = @rowid;
############################################################################
#######################
#^^^^^^^^^^^ THIS IS I THINK WHERE THE PROBLEM IS - THE LOG REMEBERS MY
USAGE OF @rowid #BUT IT NEVER REMBERS WHAT I SET @rowid to (the line SET
@rowid = LAST_INSERT_ID()
############################################################################
#######################
#...other statements
when I then try to read this dump on another server (using "mysql -u
username -p < dumpfile.sql") which has an empty table "satzungen" with the
same definition as above I get exactly empty row.
So my question is: is it a design feature that variables are not dumped into
the binary log? Was this problem solved in a later version of MySQL? Am I
just blind and stupid-and is there maybe an easy workaround (w/o using
update....where rowid = LAST_INSERT_ID()-this won#t work for me!)
Thanks!
Falk Eilenberger
---------------------------------------------------------------------
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
