select distinct doesn't coalesce NULL rows
Description: select distinct FOO from BAR reports multiple NULL rows How-To-Repeat: Unknown. It's a large table (1 entries or so). The problem didn't show with a simple test table. The table: -- MySQL dump 10.0 -- -- Host: localhostDatabase: pop - -- Server version 4.1.0-alpha-debug-log -- -- Table structure for table 'person' -- DROP TABLE IF EXISTS person; CREATE TABLE person ( descr int(11) default NULL, id int(11) NOT NULL default '0', kunde int(11) NOT NULL default '0', nameid int(11) default NULL, abtid int(11) default NULL, mailid int(11) default NULL, foneid int(11) default NULL, faxid int(11) default NULL, pagerid int(11) default NULL, isdnid int(11) default NULL, dest varchar(4) default NULL, adrid int(11) default NULL, ausweis int(11) default NULL, zusatz int(11) default NULL, suche int(11) default NULL, username int(11) default NULL, passwort int(11) default NULL, uid int(11) NOT NULL default '0', pwsubdir int(11) default NULL, pwuse bigint(20) NOT NULL default '0', udomain int(11) default NULL, uip int(11) default NULL, proto smallint(6) default NULL, maxconn tinyint(4) default NULL, ulocip int(11) default NULL, uremip int(11) default NULL, prefcall smallint(6) default NULL, tarif int(11) default NULL, satz tinyint(4) default NULL, mperson int(11) default NULL, gebtag smallint(6) default NULL, gebjahr smallint(6) default NULL, funktion int(11) default NULL, ustid varchar(10) default NULL, timestamp timestamp NOT NULL, PRIMARY KEY (id), KEY suche (suche), KEY username (username), KEY uid (uid), KEY mailid (mailid), KEY mperson (mperson), KEY timestamp (timestamp) ) TYPE=InnoDB CHARSET=latin1; The statement: (explain select distinct mperson from person): id select_type table typepossible_keys key key_len ref rows Extra 1 SIMPLE person index NULLmperson 5 NULL8125Using index Fix: None known. Submitter-Id: submitter ID Originator:Matthias Urlichs Organization: noris network AG, Nuernberg, Germany MySQL support: licence Synopsis: select distinct reports multiple NULL rows Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-4.1.0-alpha (Up-to-date Bitkeeper distribution) C compiler:gcc.real (GCC) 3.2.3 20030228 (Debian prerelease) C++ compiler: g++.real (GCC) 3.2.3 20030228 (Debian prerelease) Environment: System: Linux play.smurf.noris.de 2.4.19-586tsc #1 Sun Oct 6 18:00:21 EST 2002 i686 unknown unknown GNU/Linux 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.2.3/specs Configured with: ../src/configure -v --enable-languages=c,c++,java,f77,proto,pascal,objc,ada --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-gxx-include-dir=/usr/include/c++/3.2 --enable-shared --with-system-zlib --enable-nls --without-included-gettext --enable-__cxa_atexit --enable-clocale=gnu --enable-java-gc=boehm --enable-objc-gc i386-linux Thread model: posix gcc version 3.2.3 20030308 (Debian prerelease) Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 2003-02-28 17:59 /lib/libc.so.6 - libc-2.3.1.so -rwxr-xr-x1 root root 1104072 2003-02-25 14:46 /lib/libc-2.3.1.so -rw-r--r--1 root root 2337976 2003-02-25 14:47 /usr/lib/libc.a -rw-r--r--1 root root 178 2003-02-25 14:47 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--datadir=/usr/share' '--sysconfdir=/etc/mysql' '--localstatedir=/var/lib/mysql' '--includedir=/usr/include' '--infodir=/usr/share/info' '--mandir=/usr/share/man' '--enable-shared' '--enable-static' '--enable-thread-safe-client' '--enable-assembler' '--enable-local-infile' '--with-raid' '--with-unix-socket-path=/var/run/mysqld/mysqld.sock' '--with-mysqld-user=mysql' '--with-libwrap' '--with-client-ldflags=-lstdc++' '--with-embedded-server' '--with-vio' '--with-openssl' '--without-docs' '--without-bench' '--without-readline' '--with-extra-charsets=all' '--with-debug' '--with-innodb' - 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
Constraint violations don't show which constraint fails
Description: A constraint violation doesn't show which constraint was violated, which is bad for fixing bugs. How-To-Repeat: create table a(b int, index(b)) type=innodb; create table c(d int, index(d)) type=innodb; alter table c add constraint foo foreign key(`d`) references `a` (`b`); insert into c values(123); = ERROR 1216: Cannot add or update a child row: a foreign key constraint fails Fix: Tell me which constraint fails, please. Submitter-Id: submitter ID Originator:Matthias Urlichs Organization: noris network AG, Nuernberg, Germany MySQL support: icense Synopsis: Constraint violations don't show which constraint fails Severity: serious Priority: low Category: mysql Class: sw-bug Release: mysql-4.1.0-alpha (current Bitkeeper source) C compiler:gcc.real (GCC) 3.2.3 20030308 (Debian prerelease) C++ compiler: g++.real (GCC) 3.2.3 20030228 (Debian prerelease) Environment: System: Linux play.smurf.noris.de 2.4.19-586tsc #1 Sun Oct 6 18:00:21 EST 2002 i686 unknown unknown GNU/Linux 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.2.3/specs Configured with: ../src/configure -v --enable-languages=c,c++,java,f77,proto,pascal,objc,ada --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-gxx-include-dir=/usr/include/c++/3.2 --enable-shared --with-system-zlib --enable-nls --without-included-gettext --enable-__cxa_atexit --enable-clocale=gnu --enable-java-gc=boehm --enable-objc-gc i386-linux Thread model: posix gcc version 3.2.3 20030308 (Debian prerelease) Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 2003-02-28 17:59 /lib/libc.so.6 - libc-2.3.1.so -rwxr-xr-x1 root root 1104072 2003-02-25 14:46 /lib/libc-2.3.1.so -rw-r--r--1 root root 2337976 2003-02-25 14:47 /usr/lib/libc.a -rw-r--r--1 root root 178 2003-02-25 14:47 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--datadir=/usr/share' '--sysconfdir=/etc/mysql' '--localstatedir=/var/lib/mysql' '--includedir=/usr/include' '--infodir=/usr/share/info' '--mandir=/usr/share/man' '--enable-shared' '--enable-static' '--enable-thread-safe-client' '--enable-assembler' '--enable-local-infile' '--with-raid' '--with-unix-socket-path=/var/run/mysqld/mysqld.sock' '--with-mysqld-user=mysql' '--with-libwrap' '--with-client-ldflags=-lstdc++' '--with-embedded-server' '--with-vio' '--with-openssl' '--without-docs' '--without-bench' '--without-readline' '--with-extra-charsets=all' '--with-debug' '--with-innodb' - 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
mysqldump doesn't quote table names
Description: How-To-Repeat: mysql -ecreate table `FIELDS` (x int) test mysqldump test Fix: Teach mysqldump to use backquotes. Submitter-Id: submitter ID Originator:Matthias Urlichs Organization: noris network AG, Nuernberg, Germany MySQL support: licence Synopsis: mysqldump doesn't quote table names Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-4.1.0-alpha (Bitkeeper), _old_ bug C compiler:gcc.real (GCC) 3.2.3 20030228 (Debian prerelease) C++ compiler: g++.real (GCC) 3.2.3 20030228 (Debian prerelease) Environment: System: Linux play.smurf.noris.de 2.4.19-586tsc #1 Sun Oct 6 18:00:21 EST 2002 i686 unknown unknown GNU/Linux 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.2.3/specs Configured with: ../src/configure -v --enable-languages=c,c++,java,f77,proto,pascal,objc,ada --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-gxx-include-dir=/usr/include/c++/3.2 --enable-shared --with-system-zlib --enable-nls --without-included-gettext --enable-__cxa_atexit --enable-clocale=gnu --enable-java-gc=boehm --enable-objc-gc i386-linux Thread model: posix gcc version 3.2.3 20030228 (Debian prerelease) Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 2003-02-28 17:59 /lib/libc.so.6 - libc-2.3.1.so -rwxr-xr-x1 root root 1104072 2003-02-25 14:46 /lib/libc-2.3.1.so -rw-r--r--1 root root 2337976 2003-02-25 14:47 /usr/lib/libc.a -rw-r--r--1 root root 178 2003-02-25 14:47 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--datadir=/usr/share' '--sysconfdir=/etc/mysql' '--localstatedir=/var/lib/mysql' '--includedir=/usr/include' '--infodir=/usr/share/info' '--mandir=/usr/share/man' '--enable-shared' '--enable-static' '--enable-thread-safe-client' '--enable-assembler' '--enable-local-infile' '--with-raid' '--with-unix-socket-path=/var/run/mysqld/mysqld.sock' '--with-mysqld-user=mysql' '--with-libwrap' '--with-client-ldflags=-lstdc++' '--with-embedded-server' '--with-vio' '--with-openssl' '--without-docs' '--without-bench' '--without-readline' '--with-extra-charsets=all' '--with-debug' '--with-innodb' - 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
Update with sub-select from same table FAILS
Description: MySQL doesn't allow me to use a sub-select in an update if the subselect happens to be from the same table. How-To-Repeat: mysql use test; mysql create table T(lfd int, foo int); mysql insert into T (lfd, foo) values ( ( select max( lfd ) + 1 from T), 123); ERROR 1093: You can't specify target table 'T' for update in FROM clause Fix: Use PostgreSQL or Oracle or ..., it works with them ?!? Submitter-Id: submitter ID Originator:Matthias Urlichs Organization: noris network AG, Nuernberg, Germany MySQL support: licence Synopsis: Update with sub-select from same table fails Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-4.1.0-alpha (from BitKeeper, as of 2003-03-01) C compiler:gcc.real (GCC) 3.2.3 20030221 (Debian prerelease) C++ compiler: g++.real (GCC) 3.2.3 20030221 (Debian prerelease) Environment: System: Linux play.smurf.noris.de 2.4.19-586tsc #1 Sun Oct 6 18:00:21 EST 2002 i686 unknown unknown GNU/Linux 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.2.3/specs Configured with: ../src/configure -v --enable-languages=c,c++,java,f77,proto,pascal,objc,ada --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-gxx-include-dir=/usr/include/c++/3.2 --enable-shared --with-system-zlib --enable-nls --without-included-gettext --enable-__cxa_atexit --enable-clocale=gnu --enable-java-gc=boehm --enable-objc-gc i386-linux Thread model: posix gcc version 3.2.3 20030221 (Debian prerelease) Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 2003-02-28 17:59 /lib/libc.so.6 - libc-2.3.1.so -rwxr-xr-x1 root root 1104072 2003-02-25 14:46 /lib/libc-2.3.1.so -rw-r--r--1 root root 2337976 2003-02-25 14:47 /usr/lib/libc.a -rw-r--r--1 root root 178 2003-02-25 14:47 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--datadir=/usr/share' '--sysconfdir=/etc/mysql' '--localstatedir=/var/lib/mysql' '--includedir=/usr/include' '--infodir=/usr/share/info' '--mandir=/usr/share/man' '--enable-shared' '--enable-static' '--enable-thread-safe-client' '--enable-assembler' '--enable-local-infile' '--with-raid' '--with-unix-socket-path=/var/run/mysqld/mysqld.sock' '--with-mysqld-user=mysql' '--with-libwrap' '--with-client-ldflags=-lstdc++' '--with-embedded-server' '--with-vio' '--with-openssl' '--without-docs' '--without-bench' '--without-readline' '--with-extra-charsets=all' '--with-debug' '--with-innodb' - 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
innodb deadlock leads to server crash
Description: A deadlock within innodb leads to a server crash. I have a large table which I need to update in-place. So one mysql connection does a SELECT, and another updates the data. I thought that, since innodb supports transactions and multiversioning, the two should not block each other. Apparently, this is wrong and leads to a server crash. The relevant output from mysqld's server log is this: -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 13947, signal count 13945 --Thread 13326 has waited at btr0sea.c line 448 for 499.00 seconds the semaphore: X-lock on RW-latch at 40172668 created in file btr0sea.c line 128 a writer (thread id 13326) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 683 Last time write locked in file btr0sea.c line 1117 --Thread 7176 has waited at btr0sea.c line 863 for 490.00 seconds the semaphore: S-lock on RW-latch at 40172668 created in file btr0sea.c line 128 a writer (thread id 13326) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 683 Last time write locked in file btr0sea.c line 1117 Mutex spin waits 662, rounds 8840, OS waits 94 RW-shared spins 31791, OS waits 13822; RW-excl spins 34, OS waits 31 TRANSACTIONS Trx id counter 0 2108142 Purge done for trx's n:o 0 2108136 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 2107229, not started, OS thread id 10251 MySQL thread id 28, query id 881 gemini.office.noris.de 10.2.0.132 updater ---TRANSACTION 0 2107136, not started, OS thread id 9226 MySQL thread id 23, query id 815 gemini.office.noris.de 10.2.0.132 updater ---TRANSACTION 0 2108141, ACTIVE 499 sec, OS thread id 13326 starting index read MySQL thread id 30, query id 1484 gemini.office.noris.de 10.2.0.132 updater preparing select timestamp,seq,wann from ticketlast where ticket = '1823' and person = '3 406' ---TRANSACTION 0 2108137, ACTIVE 507 sec, OS thread id 11276 , holds adaptive hash latch MySQL thread id 29, query id 1481 gemini.office.noris.de 10.2.0.132 updater Sending data select timestamp,ticket,person from ticketlast where timestamp = FROM_UNIXTIME (916760612) order by ticket,person Trx read view will not see trx with id = 0 2108138, sees 0 2108138 How-To-Repeat: Create a table with suitably many records. mysql -q -e select id from FOO order by id | program The program would do a loop with select * from FOO where id=$ID, and do an occasional UPDATE. Fix: the two processes should not block each other. Dropping the -q is not a solution; the table is too large. Submitter-Id: submitter ID Originator: Organization: noris network AG, Nuernberg, Germany MySQL support: license Synopsis: innodb deadlock Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-3.23.56 Environment: System: Linux dev1.dev.noris.de 2.4.18-3 #1 Thu Apr 18 07:37:53 EDT 2002 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/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.0) Compilation info: CC='gcc' CFLAGS='-g -O2' CXX='g++' CXXFLAGS='-DTHREAD_SAFE_CLIENT -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Jun 6 2002 /lib/libc.so.6 - libc-2.2.5.so -rwxr-xr-x 1 root root 1260480 Apr 15 2002 /lib/libc-2.2.5.so -rw-r--r-- 1 root root 2310808 Apr 15 2002 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Apr 15 2002 /usr/lib/libc.so -rwxr-xr-x 1 root root 2194520 Feb 6 12:32 /usr/lib/libc-client.a Configure command: ./configure '--prefix=/usr' '--without-debug' '--enable-shared' '--without-mit-threads' '--libexecdir=/usr/sbin' '--localstatedir=/var/mysql' '--enable-thread-safe-client' '--sysconfdir=/etc' '--datadir=/usr/share' '--enable-large-files' '--without-readline' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/run/mysql.socket' '--enable-strcoll' '--with-comment=noris network MySQL' '--with-docs' '--with-bench' '--without-berkeley-db' '--without-bench' '--with-innodb' 'CPPFLAGS=-DTHREAD_SAFE_CLIENT' 'CXXFLAGS=-DTHREAD_SAFE_CLIENT -felide-constructors -fno-exceptions -fno-rtti' - Before posting, please check:
Innodb table with auto-increment column doesn't create (err 1005)
Description: Table not creatable How-To-Repeat: mysql create table foo (id int auto_increment,unique key (id)) type=innodb; ERROR 1005: Can't create table './test_smurf/stundenliste.frm' (errno: 121) mysql create table stundenliste (id int auto_increment) type=innodb; ERROR 1075: Incorrect table definition; There can only be one auto column and it must be defined as a key mysql create table stundenliste (id int auto_increment) type=myisam; *SUCCESS* Fix: Unknown. Submitter-Id: [EMAIL PROTECTED] Originator:Matthias Urlichs Organization: noris network AG MySQL support: licence Synopsis: auto_increment innodb tables don't Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-4.0.6-gamma (Bitkeeper source) Environment: System: Linux play.smurf.noris.de 2.4.19-586tsc #1 Sun Oct 6 18:00:21 EST 2002 i686 unknown unknown GNU/Linux 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.2.2/specs Configured with: ../src/configure -v --enable-languages=c,c++,java,f77,proto,pascal,objc,ada --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-gxx-include-dir=/usr/include/c++/3.2 --enable-shared --with-system-zlib --enable-nls --without-included-gettext --enable-__cxa_atexit --enable-clocale=gnu --enable-java-gc=boehm --enable-objc-gc i386-linux Thread model: posix gcc version 3.2.2 20030124 (Debian prerelease) Compilation info: CC='gcc' CFLAGS='-O2 -fomit-frame-pointer -g ' CXX='g++' CXXFLAGS='-O2 -fomit-frame-pointer -g -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 2003-01-30 01:26 /lib/libc.so.6 - libc-2.3.1.so -rwxr-xr-x1 root root 1102984 2003-01-21 23:15 /lib/libc-2.3.1.so -rw-r--r--1 root root 2337952 2003-01-21 23:15 /usr/lib/libc.a -rw-r--r--1 root root 178 2003-01-21 23:15 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--datadir=/usr/share' '--sysconfdir=/etc/mysql' '--localstatedir=/var/lib/mysql' '--includedir=/usr/include' '--infodir=/usr/share/info' '--mandir=/usr/share/man' '--enable-shared' '--with-libwrap' '--enable-assembler' '--with-innodb' '--enable-static' '--enable-shared' '--enable-local-infile' '--with-raid' '--enable-thread-safe-client' '--without-readline' '--with-unix-socket-path=/var/run/mysqld/mysqld.sock' '--with-mysqld-user=mysql' '--without-bench' '--with-client-ldflags=-lstdc++' '--with-extra-charsets=all' 'CC=gcc' 'CFLAGS=-O2 -fomit-frame-pointer -g ' 'CXXFLAGS=-O2 -fomit-frame-pointer -g -felide-constructors -fno-exceptions -fno-rtti' 'CXX=g++' - 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
Sending data, but no data sent?
Description: Selecting for a number in a char table is REALLY slow. How-To-Repeat: Large table, main index on column 'id' varchar(100). mysql select id from ticketid where id = '15473'; Empty set (0.00 sec) mysql select id from ticketid where id = 15473; +--+ | id | +--+ | [EMAIL PROTECTED] | | [EMAIL PROTECTED] | | [EMAIL PROTECTED] | +--+ 3 rows in set (3 min 49.81 sec) mysql select id from ticketid where id = 15473 and id like '15473%'; +--+ | id | +--+ | [EMAIL PROTECTED] | | [EMAIL PROTECTED] | | [EMAIL PROTECTED] | +--+ 3 rows in set (4 min 6.83 sec) Say what ??? mysql explain select id,ticket,seq from ticketid where id = 15473 and id like '15473%'; +--+--+---+--+-+--++--+ | table| type | possible_keys | key | key_len | ref | rows | Extra| +--+--+---+--+-+--++--+ | ticketid | ALL | PRIMARY | NULL |NULL | NULL | 406867 | where used +--+--+---+--+-+--++--+ 1 row in set (0.09 sec) mysql explain select id,ticket,seq from ticketid where id like '15473%'; +--+---+---+-+-+--+--++ | table| type | possible_keys | key | key_len | ref | rows | Extra | +--+---+---+-+-+--+--++ | ticketid | range | PRIMARY | PRIMARY | 100 | NULL |2 | where used +--+---+---+-+-+--+--++ 1 row in set (0.06 sec) Fix: Does the SQL standard really require that you go through the table and evaluate its contents numerically? The 'like' operator should be able to use the index. Why doesn't it? Submitter-Id: submitter ID Originator:Matthias Urlichs Organization: noris network AG MySQL support: licence Synopsis: Bad use of index Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.34 (noris network MySQL) Environment: System: Linux play.smurf.noris.de 2.4.0s-noris-t5-2 #58 SMP Wed Jul 19 10:24:19 CEST 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/i686-pc-linux-gnu/2.95.3/specs gcc version 2.95.3 19991030 (prerelease) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='-DTHREAD_SAFE_CLIENT -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Jun 8 2000 /lib/libc.so.6 - libc-2.1.3.so -rwxr-xr-x 1 root root 4118299 Sep 20 1999 /lib/libc-2.1.2.so -rwxr-xr-x 1 root root 4123003 Jun 8 2000 /lib/libc-2.1.3.so -rw-r--r-- 1 root root 19203634 Jun 8 2000 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Jun 8 2000 /usr/lib/libc.so -rwxr-xr-x 1 root root 2042654 Oct 27 17:09 /usr/lib/libc-client.a Configure command: ./configure --prefix=/usr --with-debug --enable-shared --without-mit-threads --libexecdir=/usr/sbin --localstatedir=/var/mysql --enable-thread-safe-client --sysconfdir=/etc --datadir=/usr/share --enable-large-files --without-readline --with-mysqld-user=mysql --with-unix-socket-path=/var/run/mysql.socket --enable-strcoll '--with-comment=noris network MySQL' --with-docs --with-bench --without-berkeley-db --without-bench - 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
After a crash, a bogus log entry doesn't appear on the slave
Description: I had a system crash today. The master log didn't get written after some point (zeroed-out data). The slave's log stated: 010302 12:59:47 Slave: Failed reading log event, reconnecting to retry, log 'mysql.138449' position 86713 010302 12:59:47 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'mysql.138449' at position 86713 010302 12:59:47 Error reading packet from server: bogus data in log event (read_errno 0,server_errno=65535) Unfortunately, the slave status still showed "running" and no error. How-To-Repeat: See above. Fix: Your task. ;-) Submitter-Id: submitter ID Originator:Matthias Urlichs Organization: MySQL support: licence Synopsis: Bogus log entry doesn't show in the slave state Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.34 (noris network MySQL) Environment: System: Linux play.smurf.noris.de 2.4.0s-noris-t5-2 #58 SMP Wed Jul 19 10:24:19 CEST 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/i686-pc-linux-gnu/2.95.3/specs gcc version 2.95.3 19991030 (prerelease) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='-DTHREAD_SAFE_CLIENT -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Jun 8 2000 /lib/libc.so.6 - libc-2.1.3.so -rwxr-xr-x 1 root root 4118299 Sep 20 1999 /lib/libc-2.1.2.so -rwxr-xr-x 1 root root 4123003 Jun 8 2000 /lib/libc-2.1.3.so -rw-r--r-- 1 root root 19203634 Jun 8 2000 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Jun 8 2000 /usr/lib/libc.so -rwxr-xr-x 1 root root 2042654 Oct 27 17:09 /usr/lib/libc-client.a Configure command: ./configure --prefix=/usr --with-debug --enable-shared --without-mit-threads --libexecdir=/usr/sbin --localstatedir=/var/mysql --enable-thread-safe-client --sysconfdir=/etc --datadir=/usr/share --enable-large-files --without-readline --with-mysqld-user=mysql --with-unix-socket-path=/var/run/mysql.socket --enable-strcoll '--with-comment=noris network MySQL' --with-docs --with-bench --without-berkeley-db --without-bench - 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
Large updates don't travel through the binary log
Description: Updates 4 MBytes cause the binary replication to stall. How-To-Repeat: Try to replicate an UPDATE command with 4MB of data Fix: log_event.h, line 45: delete MAX_EVENT_LEN log_event.cc, line 88: replace MAX_EVENT_LEN with max_allowed_packet Submitter-Id: submitter ID Originator:Matthias Urlichs Organization: noris network AG MySQL support: license Synopsis: Updates 4 MBytes stall the binary replication Severity: critical Priority: high Category: mysql Class: sw-bug Release: mysql-3.23.34 (current BK archive) Server: /usr/bin/mysqladmin Ver 8.16 Distrib 3.23.34, for pc-linux-gnu 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 3.23.34-debug-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/run/mysql.socket Uptime: 8 hours 14 min 35 sec Threads: 1 Questions: 38318 Slow queries: 0 Opens: 21 Flush tables: 1 Open tables: 15 Queries per second avg: 1.291 Environment: System: Linux play.smurf.noris.de 2.4.0s-noris-t5-2 #58 SMP Wed Jul 19 10:24:19 CEST 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/i686-pc-linux-gnu/2.95.3/specs gcc version 2.95.3 19991030 (prerelease) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='-DTHREAD_SAFE_CLIENT -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Jun 8 2000 /lib/libc.so.6 - libc-2.1.3.so -rwxr-xr-x 1 root root 4118299 Sep 20 1999 /lib/libc-2.1.2.so -rwxr-xr-x 1 root root 4123003 Jun 8 2000 /lib/libc-2.1.3.so -rw-r--r-- 1 root root 19203634 Jun 8 2000 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Jun 8 2000 /usr/lib/libc.so -rwxr-xr-x 1 root root 2042654 Oct 27 17:09 /usr/lib/libc-client.a Configure command: ./configure --prefix=/usr --with-debug --enable-shared --without-mit-threads --libexecdir=/usr/sbin --localstatedir=/var/mysql --enable-thread-safe-client --sysconfdir=/etc --datadir=/usr/share --enable-large-files --without-readline --with-mysqld-user=mysql --with-unix-socket-path=/var/run/mysql.socket --enable-strcoll '--with-comment=noris network MySQL' --with-docs --with-bench --without-berkeley-db --without-bench - 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
Restarting a slave didn't work
Description: Restarting doesn't connect to the master. How-To-Repeat: mysql restart. show slave status. Read server log. Fix: The master's file name still had a newline. Index: noris.38/sql/slave.cc --- noris.38/sql/slave.cc Thu, 25 Jan 2001 07:35:47 +0100 smurf (database_mysql/O/b/39_slave.cc 1.9.1.2.1.1.1.1.1.2 664) +++ noris.38(w)/sql/slave.cc Mon, 05 Feb 2001 21:16:57 +0100 smurf +(database_mysql/O/b/39_slave.cc 1.9.1.2.1.1.1.1.1.2 664) @@ -513,7 +513,7 @@ goto error; } -mi-log_file_name[length]= 0; // kill \n +mi-log_file_name[length-1]= 0; // kill \n char buf[FN_REFLEN]; if(!my_b_gets(mi-file, buf, sizeof(buf))) { Submitter-Id: submitter ID Originator:Matthias Urlichs Organization: MySQL support: licence Synopsis: Restarting a slave didn't work Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-3.23.32 (noris network MySQL) Environment: System: Linux play.smurf.noris.de 2.4.0s-noris-t5-2 #58 SMP Wed Jul 19 10:24:19 CEST 2000 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/i686-pc-linux-gnu/2.95.3/specs gcc version 2.95.3 19991030 (prerelease) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='-DTHREAD_SAFE_CLIENT -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Jun 8 2000 /lib/libc.so.6 - libc-2.1.3.so -rwxr-xr-x 1 root root 4118299 Sep 20 1999 /lib/libc-2.1.2.so -rwxr-xr-x 1 root root 4123003 Jun 8 2000 /lib/libc-2.1.3.so -rw-r--r-- 1 root root 19203634 Jun 8 2000 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Jun 8 2000 /usr/lib/libc.so -rwxr-xr-x 1 root root 2042654 Oct 27 17:09 /usr/lib/libc-client.a Configure command: ./configure --prefix=/usr --with-debug --enable-shared --without-mit-threads --libexecdir=/usr/sbin --localstatedir=/var/mysql --enable-thread-safe-client --sysconfdir=/etc --datadir=/usr/share --enable-large-files --without-readline --with-mysqld-user=mysql --with-unix-socket-path=/var/run/mysql.socket --enable-strcoll '--with-comment=noris network MySQL' --with-docs --with-bench --without-berkeley-db --without-bench - 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
Adding the offset to mysqlbinlog
Description: Sometimes, when you get a resync error, you need to skip a particular update and restart with the next bit. How-To-Repeat: Insert conflicting records and try to re-sync without deleting one. Fix: Apply this patch. Index: noris.38/sql/mysqlbinlog.cc --- noris.38/sql/mysqlbinlog.cc Wed, 24 Jan 2001 21:35:51 +0100 smurf (database_mysql/N/b/20_mysqlbinlo 1.12 664) +++ noris.39/sql/mysqlbinlog.cc Mon, 05 Feb 2001 22:00:12 +0100 smurf +(database_mysql/N/b/20_mysqlbinlo 1.13 664) @@ -355,18 +355,25 @@ while(1) { char llbuff[21]; +my_off_t old_off = my_b_tell(file); + Log_event* ev = Log_event::read_log_event(file, 0); if (!ev) { if (file-error) die("\ Could not read entry at offset %s : Error in log format or read error", - llstr(my_b_tell(file),llbuff)); + llstr(old_off,llbuff)); // file-error == 0 means EOF, that's OK, we break in this case break; } if (rec_count = offset) +{ + if (!short_form) +printf("# at %s\n",llstr(old_off,llbuff)); + ev-print(stdout, short_form); +} rec_count++; delete ev; } Submitter-Id: submitter ID Originator:Matthias Urlichs Organization: MySQL support: licence Synopsis: mysqlbinlog doesn't print the offset Severity: non-critical Priority: medium Category: mysql Class: change-request Release: mysql-3.23.32 (noris network MySQL) Environment: System: Linux play.smurf.noris.de 2.4.0s-noris-t5-2 #58 SMP Wed Jul 19 10:24:19 CEST 2000 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/i686-pc-linux-gnu/2.95.3/specs gcc version 2.95.3 19991030 (prerelease) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='-DTHREAD_SAFE_CLIENT -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Jun 8 2000 /lib/libc.so.6 - libc-2.1.3.so -rwxr-xr-x 1 root root 4118299 Sep 20 1999 /lib/libc-2.1.2.so -rwxr-xr-x 1 root root 4123003 Jun 8 2000 /lib/libc-2.1.3.so -rw-r--r-- 1 root root 19203634 Jun 8 2000 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Jun 8 2000 /usr/lib/libc.so -rwxr-xr-x 1 root root 2042654 Oct 27 17:09 /usr/lib/libc-client.a Configure command: ./configure --prefix=/usr --with-debug --enable-shared --without-mit-threads --libexecdir=/usr/sbin --localstatedir=/var/mysql --enable-thread-safe-client --sysconfdir=/etc --datadir=/usr/share --enable-large-files --without-readline --with-mysqld-user=mysql --with-unix-socket-path=/var/run/mysql.socket --enable-strcoll '--with-comment=noris network MySQL' --with-docs --with-bench --without-berkeley-db --without-bench - 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