>Description:
Documentation section 6.5.7 regarding create index states: "For CHAR and
VARCHAR columns, indexes can be created that use only part of a column, using
col_name(length) syntax. (On BLOB and TEXT columns the length is required.)"
It's still possible in the mysql client to request the creation of an index
with indexlength NULL on TEXT fields. This apparently works okay on creation of the
index, insert and select operations, but breaks horribly (marks table as crashed with
corrupted indexes) on update or delete operations.
Same problem occurs with fulltext indexes -- indeed, I had an index created as
a concatenation of two text fields (Name,Description). On running check-table and then
show index, this fulltext index had a null cardinality; and, when dropped, it dropped
immediately (too quickly to have data in it).
We're using mysql4 in production since we need the fast fulltext indexing.
http://ftp.avsim.com/library/ is the site running your backend.
>How-To-Repeat:
Create table with field-type of TEXT.
CREATE INDEX AnyName ON Table(TextField);
Issue an update to a pre-existing record for the incorrectly indexed TextField.
>Fix:
mysql should throw an error to the client if request is made to index a text
field without arity; or assume 255 (which works okay).
>Submitter-Id: n/a
>Originator: Matt Johnson
>Organization: AVSIM Online <http://www.avsim.com/>
>MySQL support: none
>Synopsis: mysql must enforce arity/length requirement when indexing TEXT fields.
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-4.0.0-alpha (Official MySQL RPM)
>Environment:
System: Linux avsim.colo.cais.net 2.2.14-5.0 #1 Tue Mar 7 21:07:39 EST 2000 i686
unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/egcs-2.91.66/specs
gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release)
Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='gcc'
CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors
-fno-exceptions -fno-rtti -mpentium' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Aug 26 2000 /lib/libc.so.6 -> libc-2.1.3.so
-rwxr-xr-x 1 root root 4101836 Jan 15 2001 /lib/libc-2.1.3.so
-rw-r--r-- 1 root root 20273324 Jan 15 2001 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Jan 15 2001 /usr/lib/libc.so
Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static
--with-client-ldflags=-all-static --without-berkeley-db --with-innodb
--enable-assembler --with-mysqld-user=mysql
--with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/
--with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin
--sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql
--infodir=/usr/info --includedir=/usr/include --mandir=/usr/man --with-embedded-server
'--with-comment=Official MySQL RPM'
Perl: This is perl, version 5.005_03 built for i386-linux
---------------------------------------------------------------------
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
