At 4:58 PM -0700 7/17/01, Bryan Coon wrote:
>Hi, I am experiencing some really bizzare behaviour in MySQL v3.23.36...
>
>I have a perl script that takes some html form based user input, parses
>it into an array and then loops throught the array inserting each
>element into a table. This table is then left outer joined with others
>for a quick query, and then the data is deleted. During debugging, I
>have noticed some really strange stuff. I have not figured out how to
>consitently reproduce it, but Im still trying...
>
>So heres what happens. If 9 rows are entered into the table, what
>happens normally is this:
>mysql> select * from idsearch;
>+----+---------+--------+
>| id | queryid | userid |
>+----+---------+--------+
>| 1 | 1 | bryan |
>| 2 | 2 | bryan |
>| 3 | 3 | bryan |
>| 4 | 4 | bryan |
>| 5 | 5 | bryan |
>| 6 | 6 | bryan |
>| 7 | 7 | bryan |
>| 8 | 8 | bryan |
>| 9 | 9 | bryan |
>+----+---------+--------+
>9 rows in set (0.00 sec)
>
>Looks fine to me.
Coincidence. You don't have an ORDER BY clause to tell the
server how you want the output rows sorted. It's free to return
them in any order it wants otherwise.
>
>BUT about 50% of the time, the table comes out like this:
>mysql> select * from idsearch;
>+----+---------+--------+
>| id | queryid | userid |
>+----+---------+--------+
>| 25 | 9 | bryan |
>| 24 | 8 | bryan |
>| 23 | 7 | bryan |
>| 22 | 6 | bryan |
>| 21 | 5 | bryan |
>| 20 | 4 | bryan |
>| 19 | 3 | bryan |
>| 18 | 2 | bryan |
>| 17 | 1 | bryan |
>+----+---------+--------+
>9 rows in set (0.00 sec)
Again, no ORDER BY clause. Without it, the server's free to ...
>
>id is defined as auto_increment not null...
>The starting point of my 'auto_decrement' :P is as far as I can tell
>completely random.
>Here is my perl code in a nutshell:
>
>## Insert data
>for (my $i=0; $i < @array; $i++) {
> Data::Table::fromSQL( $dbh, "insert into mytable (queryid, userid) values
>('$array[$i]', 'bryan')");
>}
>
>## Select table
>my $table = Data::Table::fromSQL( $dbh, "select * from idsearch");
>
>## Delete data
>Data::Table::fromSQL( $dbh, "delete from idsearch where userid='bryan'");
>
>** I use the Data::Table module from perl to execute queries. And in this
>example I leave out the left outer join.
>
>Can anyone postulate how this is possible? I suspect it may have something
>to do with the fact that I delete the records immediately after I insert
>them causing some glitch between auto_incrementation and writing of the data
>block... but this is still strange.
You're correct that it has something do with deleting, then inserting.
But what's the glitch in auto-incrementation? The id values are increasing
in perfect correlation to your queryid values. Do you mean that you want
the id sequence to reset back to 1?
>
>I have verified doing various debugging that even in the table that is
>tweaked, each row is added in the correct order (1-9) from the array,
>and the script itself seems to be working just fine. In fact, if I
>comment out the delete statement, and do a manual delete on every reload
>of the page, it works fine. But uncomment that statement and things get
>screwy again.
>
>Can anyone offer suggestions as to what I might be doing wrong or what I
>should look at?
>
>Thanks!
>Bryan
--
Paul DuBois, [EMAIL PROTECTED]
---------------------------------------------------------------------
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
