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