List:General Discussion« Previous MessageNext Message »
From:schoenfr Date:April 3 2008 4:27pm
Subject:spurious select ERROR 1191 when insert into ... select * is done on fulltext table
View as plain text  
>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=99999
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:	schoenfr@stripped
>Originator:	Erik Schoenfelder
>Organization:
  Gaertner Datensysteme GbR,   E-Mail: support@stripped   
  38114 Braunschweig,   Hamburger Str. 273a,   Germany,  Gesellschafter:
>MySQL support: none
>Synopsis:	spurious select ERROR 1191 when insert into .. select * is done on fulltext
> table
>Severity:	serious
>Priority:	low
>Category:	mysql
>Class:		sw-bug
>Release:	mysql-5.0.51a (MySQL Community Server (GPL))
>Server: /usr/local/bin/mysqladmin  Ver 8.41 Distrib 5.0.51a, for redhat-linux-gnu on
> i686
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version		5.0.51a-log
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/var/run/mysqld/mysqld-5.sock
Uptime:			23 hours 6 min 53 sec

Threads: 20  Questions: 1404381461  Slow queries: 565  Opens: 4037  Flush tables: 1  Open
tables: 780  Queries per second avg: 16876.948
>C compiler:    gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-52)
>C++ compiler:  gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-52)
>Environment:
System: Linux d4 2.6.24.2 #1 SMP Tue Feb 12 12:38:01 CET 2008 i686 GNU/Linux
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Using built-in specs.
Target: i486-linux-gnu
Configured with: ../src/configure -v
--enable-languages=c,c++,fortran,objc,obj-c++,treelang --prefix=/usr --enable-shared
--with-system-zlib --libexecdir=/usr/lib --without-included-gettext
--enable-threads=posix --enable-nls --program-suffix=-4.1 --enable-__cxa_atexit
--enable-clocale=gnu --enable-libstdcxx-debug --enable-mpfr --with-tune=i686
--enable-checking=release i486-linux-gnu
Thread model: posix
gcc version 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)
Compilation info: CC='ccache gcc'  CFLAGS=''  CXX='ccache gcc'  CXXFLAGS=''  LDFLAGS='' 
ASFLAGS=''
LIBC: 
lrwxrwxrwx 1 root root 13 Feb 18 14:39 /lib/libc.so.6 -> libc-2.3.6.so
-rwxr-xr-x 1 root root 1153784 Apr  8  2003 /lib/libc-2.2.5.so
-rwxr-xr-x 1 root root 1147548 Jan 19 13:14 /lib/libc-2.3.6.so
-rw-r--r-- 1 root root 2602934 Jan 19 13:14 /usr/lib/libc.a
-rwxr-xr-x 1 root root 204 Jan 19 12:53 /usr/lib/libc.so
Configure command: ./configure '--prefix=/usr/local/mysql'
'--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin'
'--with-comment=MySQL Community Server (GPL)' '--with-server-suffix='
'--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--with-pic'
'--with-fast-mutexes' '--with-client-ldflags=-static' '--with-mysqld-ldflags=-static'
'--with-zlib-dir=bundled' '--with-big-tables' '--with-yassl' '--with-readline'
'--with-archive-storage-engine' '--with-blackhole-storage-engine' '--with-ndbcluster'
'--with-csv-storage-engine' '--with-example-storage-engine' '--without-falcon'
'--with-federated-storage-engine' '--with-innodb' '--with-extra-charsets=all' 'CC=ccache
gcc' 'CXX=ccache gcc'

Thread
spurious select ERROR 1191 when insert into ... select * is done on fulltext tableschoenfr3 Apr