>Description:
A website we recently built contains a highscore table. People play a game, and can
then submit their name and score to the table.
When viewing the table, I then need to rank the scores using an "order by"... However
since I want to be able to only select arbitrary intervals in the ranks (I want to ask
the question "show me position 200-300), I figured I needed to use a temporary table.
All my tables are innodb tables.
I'm doing this in Java using the mm.mysql-2.0.14 driver.
I'm using MySQL 3.23.53.
The SQL I then got is:
1. create temporary table if not exists
Highscores_result ( position int, name varchar(255),
country varchar(255), score int )
type=innodb;
2. delete from Highscores_result;
3. set @n=0;
4. insert into Highscores_result
select @n:=@n+1 as rownum, name, country, score
from Highscores order by score desc;
I grab a connection, set autocommit to false, create a statement (not
prepared) and then do stmt.addBatch() for each of the statements, execute, and then
commit().
And then I can read out say position 200-300 from the temporary table.
The MySQL instance that runs this have got two replication slaves. One which is
constantly connected, and one which connects every night for nightly backups. The one
which is constantly connected works fine and keeps replicating.
However the one connecting every night always stops the slave thread with a:
ERROR: 1146 Table 'foo.Highscores_result' doesn't exist
021127 20:00:03 Slave: error running query 'delete from Highscores_result' 021127
20:00:03 Error running query, slave aborted. Fix the problem, and re-start the slave
thread with "mysqladmin start-slave". We stopped at log 'master-bin.018' position
566235 021127 20:00:03 Slave thread exiting, replication stopped in log
'master-bin.018' at position 566235
Hypothesis:
Could this be down to that temporary tables are connection unique and I'm killing my
connection every now and then, and that the slave does not actually create a table on
"create table if not exists" if the master didn't create it. E.g. master log looks
something like:
- CREATE TABLE IF NOT EXISTS (Creates table on master since it doesn't
exist)
- DELETE FROM
- SET
- INSERT
------ I disconnect my slave
------ I reconnect my slave
- CREATE TABLE IF NOT EXISTS (Doesn' create on slave since it existed on the master)
- DELETE FROM
- SET
- INSERT
>How-To-Repeat:
Set up a replication slave that disconnects and reconnects using cron. Run the
above kind of SQL.
>Fix:
If the hypothesis is correct, perhaps the slave must honor "create table if not
exists" regardless if this was a success on the master or not.
>Submitter-Id: Martin Algesten
>Originator:
>Organization:
Taglab Limited
>MySQL support: none
>Synopsis: replication and if not exists temporary tables
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.53 (Source distribution)
>Environment:
System: Linux 2.4.19-taglab-2002-10-29 #2 Tue Oct 29 17:16:43 GMT 2002 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/local/gcc-2.95.3/bin/gcc
GCC: Reading specs from
/usr/local/gcc-2.95.3/lib/gcc-lib/i686-pc-linux-gnu/2.95.3/specs
gcc version 2.95.3 20010315 (release)
Compilation info: CC='/usr/local/gcc-2.95.3/bin/gcc' CFLAGS=''
CXX='/usr/local/gcc-2.95.3/bin/g++' CXXFLAGS='' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Jan 8 2002 /lib/libc.so.6 -> libc-2.2.4.so
-rwxr-xr-x 2 root root 1282588 Sep 4 2001 /lib/libc-2.2.4.so
-rw-r--r-- 1 root root 27304836 Sep 4 2001 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Sep 4 2001 /usr/lib/libc.so
Configure command: ./configure --prefix=/usr/local/mysql-3.23.53/ --with-gnu-ld
--without-berkeley-db --with-innodb --with-charset=latin1
--with-extra-charsets=complex --with-mysqld-user=mysql --enable-assembler
CC=/usr/local/gcc-2.95.3/bin/gcc CPP=/usr/local/gcc-2.95.3/bin/cpp
CXX=/usr/local/gcc-2.95.3/bin/g++
---------------------------------------------------------------------
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 <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
