>Description This may be by design, but I figured I'd bring it up because it caused less-than-desirable behaviour for me. I executed an alter table query, designed to change the length of a fixed width char field to 128 from 255. The alter occured fine, with no problems, except that there were apparently some insert delayed queries that I guess were queued for execution before the alter, and tried to execute after. Since I did not remove any columns, and none of the char data that was being inserted was over 20 chars, I do not understand why the inserts failed...
I noticed the problem thanks to my monitoring system telling me that the slave had stopped. >How-To-Repeat: CREATE TABLE test ( id int(10) unsigned AUTO_INCREMENT, test char(255) NOT NULL default '', PRIMARY KEY(id) ); INSERT DELAYED INTO test VALUES (0, 'This is a test'); INSERT DELAYED INTO test VALUES (0, 'This is a test'); INSERT DELAYED INTO test VALUES (0, 'This is a test'); INSERT DELAYED INTO test VALUES (0, 'This is a test'); INSERT DELAYED INTO test VALUES (0, 'This is a test'); INSERT DELAYED INTO test VALUES (0, 'This is a test'); INSERT DELAYED INTO test VALUES (0, 'This is a test'); INSERT DELAYED INTO test VALUES (0, 'This is a test'); INSERT DELAYED INTO test VALUES (0, 'This is a test'); INSERT DELAYED INTO test VALUES (0, 'This is a test'); INSERT DELAYED INTO test VALUES (0, 'This is a test'); ALTER TABLE test change column text text char(128) NOT NULL default ''; If you do it all quickly enough, the problem will occur. It may not be evident unless you have a slave to fail. The slave will stop with the following error: Slave: query 'INSERT DELAYED INTO searches ( terms,category,date_created,id,modifiers ) VALUES ( 'jacob and co',01040000000000000000,NOW(),0,'cat:1-4' )' partially completed on the master and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; SLAVE START; >Fix: Either: 1) Execute all insert delayed queries before executing the alter table command, or 2) allow the query to execute normally after the alter IF the tables structure is not fundamentally changed. By fundamentally changed, I mean that columns that are necessary for the insert were not removed, and they didn't change type. In the case of my insert, I consider that there was no fundamental change because in a char field if I try to insert greater than char(128) characters, it is truncated and the query completes. >Submitter-Id: <submitter ID> >Originator: root >Organization: >MySQL support: none >Synopsis: >Severity: >Priority: >Category: mysql >Class: >Release: mysql-4.0.11a-gamma (Source distribution) >Server: /usr/local/bin/mysqladmin Ver 8.40 Distrib 4.0.11a-gamma, for pc-linux on >i686 Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult 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 4.0.11a-gamma-log Protocol version 10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 18 days 19 hours 30 min 14 sec Threads: 101 Questions: 9558361 Slow queries: 8453 Opens: 22074 Flush tables: 1 Open tables: 262 Queries per second avg: 5.881 >C compiler: gcc (GCC) 3.1.1 >C++ compiler: gcc (GCC) 3.1.1 >Environment: IBM Netfinity 4500, Debian Potato System: Linux 2.4.19pre1 #2 SMP Sun Jun 30 03:54:52 CDT 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/3.1.1/specs Configured with: /mnt/data/gcc-3.1/gcc-3.1-3.1.1ds3/src/configure -v --enable-languages=c,c++,java,f77,proto,objc,ada --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-gxx-include-dir=/usr/include/c++/3.1 --enable-shared --with-system-zlib --enable-long-long --enable-nls --without-included-gettext --enable-clocale=gnu --enable-__cxa_atexit --enable-threads=posix --enable-java-gc=boehm --enable-objc-gc i386-linux Thread model: posix gcc version 3.1.1 Compilation info: CC='gcc' CFLAGS='-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' CXX='gcc' CXXFLAGS='-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Nov 24 15:27 /lib/libc.so.6 -> libc-2.3.1.so -rwxr-xr-x 1 root root 1109068 Nov 19 12:13 /lib/libc-2.3.1.so -rw-r--r-- 1 root root 2344038 Nov 19 12:14 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Nov 19 12:14 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/local/mysql' '--enable-assembler' '--with-extra-charsets=none' '--enable-thread-safe-client' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--enable-local-infile' '--with-other-libc=/usr/local/mysql-glibc-2.2.5' '--prefix=/usr/local' '--without-innodb' '--without-isam' 'CFLAGS=-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' 'CXXFLAGS=-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' 'CXX=gcc' --------------------------------------------------------------------- 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
