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.
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)
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.
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
---------------------------------------------------------------------
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
