spurious select ERROR 1191 when insert into ... select * is done on fulltext table
Description:
copying a table with a fulltext index via
insert into ft1 select * from ft2;
into a identical table sometimes leads to select error 1191
when concurrent select's are running.
this happens in an enviroment where the searched table is
periodically created as a temporary table and finally
copied into the searched table.
so the locking time is restricted to copying and the
searches running parallel are not locked while creating
the new search table.
the error 1191 occurs to the search selects after copying, but
only sometimes not always.
without running concurrent selects while copying,
the error seems not to happen.
the mysql database version is fresh fetched from the server:
mysql-5.0.51a-linux-i686-glibc23.tar.gz
but this error could be reproduced with v5.0.45 too.
How-To-Repeat:
script 1:
create a table with a fulltext indexed column,
fill with some data and run selects on this
table in a loop.
in this test the select result is not of interest,
only if there is a problem flagged, or a success exit code.
script t1.sh:
---
#!/bin/bash
##
## create table ft1 with one fulltext column:
##
mysql -B test EOF
drop table if exists ft1;
CREATE TABLE ft1 (
data text NOT NULL,
FULLTEXT KEY data (data)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
EOF
max=1000
##
## fill table with data:
##
i=0
while [ $i -lt $max ] ; do
i=$(($i + 1))
echo insert into ft1 values ('textstring');
done | mysql -B test
##
## loop endless selecting data.
##
## on success (regardless if there is a match) print a . on success
## or print the mysql error message.
##
while true ; do
mysql -B test -e select match (data) against ('something'), data \
from ft1 where match (data) against ('something') /dev/null
if [ $? = 0 ] ; then
echo -n .
fi
done
exit 0
---
script 2:
the second script does the copying forth and back:
- drop the temp table, create and fill it.
- lock
- empty search table and fill from temp table
- unlock
running the second script with a parameter sets the count how
many times this copying should be done.
script t2.sh:
---
#!/bin/bash
if [ $1 != ] ; then
loop=$1
else
loop=9
fi
i=0
while [ $i -lt $loop ] ; do
i=$(($i + 1))
mysql -B test EOF
drop table if exists ft2;
create table ft2 like ft1;
insert into ft2 select * from ft1;
lock table ft1 write, ft2 read;
delete from ft1;
insert into ft1 select * from ft2;
unlock tables;
select count(*) from ft1;
EOF
done
---
testing:
- run t1.sh
should start printing dots for every select:
.
[...]
- run t2.sh
t2 should print the select count(*) output for every loop run:
count(*)
1000
[...]
- as t2.sh starts, the first script starts to output errors between
the successful dots:
ERROR 1191 (HY000) at line 1: Can't find FULLTEXT index matching the
column list
...ERROR 1191 (HY000) at line 1: Can't find FULLTEXT index matching
the column list
ERROR 1191 (HY000) at line 1: Can't find FULLTEXT index matching the
column list
...ERROR 1191 (HY000) at line 1: Can't find FULLTEXT index matching
the column list
[...]
the spurious behavior can be observed by running the t2.sh copy script
manyally one copy after an other:
t2.sh 1
t2.sh 1
[...]
somtimes the output of the t1.sh looping script changes to only
errors, flagging a broken table:
ERROR 1191 (HY000) at line 1: Can't find FULLTEXT index matching the
column list
ERROR 1191 (HY000) at line 1: Can't find FULLTEXT index matching the
column list
ERROR 1191 (HY000) at line 1: Can't find FULLTEXT index matching the
column list
[...]
and then continuing with the copies:
t2.sh 1
t2.sh 1
[...]
the output of the t1.sh looping script changes
back to dots, showing the table is usable.
Fix:
a fix is not known.
a usable workaround seems to be adding a repair table quick:
insert into ft1 select * from ft2;
+ repair table ft1 quick;
unlock tables;
Submitter-Id: [EMAIL PROTECTED]
Originator:Erik Schoenfelder
Organization:
Gaertner Datensysteme GbR, E-Mail: [EMAIL PROTECTED]
38114 Braunschweig, Hamburger Str. 273a, Germany, Gesellschafter
SELECT ERROR
I'm trying to compare 2 tables and keep getting an error. SELECT * from listings where listings.id = fake.id; The error is Error 1109: Unknown table 'fake' in where clause or Error 1109: Unknown table 'listings' in where clause depending on the table order at the end of the query. Both tables do exist and I can select any or all contents from either of them seperately just not using the command above that practically comes straight from the documentation. Any ideas? Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT ERROR
If you want to compare the 2 tables you have to join them: select * from listings, fake where listings.id=fake.id; If you do your query SELECT * from listings where listings.id = fake.id; it simply doesn't know where to get fake.id From: Ed Curtis [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: SELECT ERROR Date: Wed, 23 Feb 2005 08:02:21 -0500 (EST) I'm trying to compare 2 tables and keep getting an error. SELECT * from listings where listings.id = fake.id; The error is Error 1109: Unknown table 'fake' in where clause or Error 1109: Unknown table 'listings' in where clause depending on the table order at the end of the query. Both tables do exist and I can select any or all contents from either of them seperately just not using the command above that practically comes straight from the documentation. Any ideas? Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT ERROR
Thanks, as soon as I seen it I slapped myself really hard :) On Wed, 23 Feb 2005, mel list_php wrote: If you want to compare the 2 tables you have to join them: select * from listings, fake where listings.id=fake.id; If you do your query SELECT * from listings where listings.id = fake.id; it simply doesn't know where to get fake.id From: Ed Curtis [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: SELECT ERROR Date: Wed, 23 Feb 2005 08:02:21 -0500 (EST) I'm trying to compare 2 tables and keep getting an error. SELECT * from listings where listings.id = fake.id; The error is Error 1109: Unknown table 'fake' in where clause or Error 1109: Unknown table 'listings' in where clause depending on the table order at the end of the query. Both tables do exist and I can select any or all contents from either of them seperately just not using the command above that practically comes straight from the documentation. Any ideas? Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert into from select error
Hi David, Can you run the query in the MySQL command line client? -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Zak Greant [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Advocate /_/ /_/\_, /___/\___\_\___/ Calgary, Canada ___/ 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
