puckman,
Thursday, November 28, 2002, 12:46:15 PM, you wrote:
patdc> A website we recently built contains a highscore table. People play a game, and
can then submit their name and score to the table.
patdc> 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
patdc> me position 200-300), I figured I needed to use a temporary table.
patdc> All my tables are innodb tables.
patdc> I'm doing this in Java using the mm.mysql-2.0.14 driver.
patdc> I'm using MySQL 3.23.53.
patdc> The SQL I then got is:
patdc> 1. create temporary table if not exists
patdc> Highscores_result ( position int, name varchar(255),
patdc> country varchar(255), score int )
patdc> type=innodb;
patdc> 2. delete from Highscores_result;
patdc> 3. set @n=0;
patdc> 4. insert into Highscores_result
patdc> select @n:=@n+1 as rownum, name, country, score
patdc> from Highscores order by score desc;
patdc> I grab a connection, set autocommit to false, create a statement (not
patdc> prepared) and then do stmt.addBatch() for each of the statements, execute, and
then commit().
patdc> And then I can read out say position 200-300 from the temporary table.
patdc> 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
patdc> connected works fine and keeps replicating.
patdc> However the one connecting every night always stops the slave thread with a:
patdc> ERROR: 1146 Table 'foo.Highscores_result' doesn't exist
patdc> 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
patdc> "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
patdc> Hypothesis:
patdc> 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
patdc> exists" if the master didn't create it. E.g. master log looks something like:
patdc> - CREATE TABLE IF NOT EXISTS (Creates table on master since it doesn't
patdc> exist)
patdc> - DELETE FROM
patdc> - SET
patdc> - INSERT
patdc> ------ I disconnect my slave
patdc> ------ I reconnect my slave
patdc> - CREATE TABLE IF NOT EXISTS (Doesn' create on slave since it existed on the
master)
patdc> - DELETE FROM
patdc> - SET
patdc> - INSERT
>How-To-Repeat:
patdc> Set up a replication slave that disconnects and reconnects using cron. Run the
patdc> above kind of SQL.
Sorry, but I was unable to repeat it as you describe.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ 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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
