select distinct doesn't coalesce NULL rows

2003-03-14 Thread Smurf 
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

2003-03-14 Thread Smurf 
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

2003-03-07 Thread Smurf 
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

2003-03-04 Thread smurf 
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

2003-03-02 Thread smurf 
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)

2003-01-30 Thread Smurf 
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?

2001-03-07 Thread smurf 

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

2001-03-02 Thread smurf 

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

2001-02-18 Thread smurf 

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

2001-02-05 Thread smurf 

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

2001-02-05 Thread smurf 

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