Lost Data with delayed insert / mysqlhotcopy
>Description: mysqlhotcopy (but possible any other software) can cause delayed handlers to terminate with "Delayed insert thread couldn't get requested lock for " I havent worked out the exact conditions under wich mysqlhotcopy fails, but it seems that if mysqlhotcopy tries to get a lock (waiting for a query to finish) and you start another delayed insert (maybe involving several tables?), then it will fail the server The description below, extracts the fault without mysqlhotcopy and failed always on my system >How-To-Repeat: you need two mysql client connection: (assuming a table tb with a column a int) 1> LOCK TABLES tb READ; 2> insert delayed into tb values(1); 1> flush tables; # the delayed handler dies here, the insert is not in the table 1> unlock tables >Fix: >Submitter-Id: [EMAIL PROTECTED] >Originator: >Organization: >MySQL support: none >Synopsis: data loss with delayed insert >Severity: serious >Priority: low >Category: mysql >Class: >Release: mysql-4.0.15 (FreeBSD port: mysql-server-4.0.15) >Server: /usr/local/bin/mysqladmin Ver 8.40 Distrib 4.0.13, for portbld-freebsd4.8 on i386 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.15 Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 8 days 23 hours 34 min 32 sec Threads: 6 Questions: 138727 Slow queries: 1 Opens: 209 Flush tables: 4 Open tables: 15 Queries per second avg: 0.179 >C compiler:2.95.4 >C++ compiler: 2.95.4 >Environment: System: FreeBSD dionysus.london.hybyte.com 4.8-STABLE FreeBSD 4.8-STABLE #0: Tue Aug 12 12:52:01 BST 2003 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/DIONYSUS i386 Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using builtin specs. gcc version 2.95.4 20020320 [FreeBSD] Compilation info: CC='cc' CFLAGS='-O1 -pipe -mcpu=i686 -D__USE_UNIX98 -D_REENTRANT -D_THREAD_SAFE -I/usr/local/include/pthread/linuxthreads -O3 -fno-omit-frame-pointer' CXX='cc' CXXFLAGS='-O1 -pipe -mcpu=i686 -D__USE_UNIX98 -D_REENTRANT -D_THREAD_SAFE -I/usr/local/include/pthread/linuxthreads -O3 -fno-omit-frame-pointer -felide-constructors -fno-rtti -fno-exceptions' LDFLAGS='' ASFLAGS='' LIBC: -r--r--r-- 1 root wheel 1224554 Aug 12 12:52 /usr/lib/libc.a lrwxr-xr-x 1 root wheel 9 Aug 12 12:52 /usr/lib/libc.so -> libc.so.4 -r--r--r-- 1 root wheel 578880 Aug 12 12:52 /usr/lib/libc.so.4 Configure command: ./configure '--localstatedir=/var/db/mysql' '--without-debug' '--without-readline' '--without-bench' '--without-extra-tools' '--with-libwrap' '--with-mysqlfs' '--with-vio' '--with-low-memory' '--with-comment=FreeBSD port: mysql-server-4.0.15' '--enable-thread-safe-client' '--enable-assembler' '--with-berkeley-db' '--with-openssl' '--with-named-thread-libs=-DHAVE_GLIBC2_STYLE_GETHOSTBYNAME_R -D_THREAD_SAFE -I/usr/local/include/pthread/linuxthreads -L/usr/local/lib -llthread -llgcc_r' '--prefix=/usr/local' '--build=i386-portbld-freebsd4.8' 'CFLAGS=-O1 -pipe -mcpu=i686 -D__USE_UNIX98 -D_REENTRANT -D_THREAD_SAFE -I/usr/local/include/pthread/linuxthreads -O3 -fno-omit-frame-pointer' 'CXX=cc' 'build_alias=i386-portbld-freebsd4.8' 'CC=cc' 'CXXFLAGS=-O1 -pipe -mcpu=i686 -D__USE_UNIX98 -D_REENTRANT -D_THREAD_SAFE -I/usr/local/include/pthread/linuxthreads -O3 -fno-omit-frame-pointer -felide-constructors -fno-rtti -fno-exceptions' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bug with UNIQUE Key on multi-row containing NULL values
SEND-PR: -*- send-pr -*- SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as SEND-PR: will all comments (text enclosed in `<' and `>'). SEND-PR: From: root To: [EMAIL PROTECTED] Subject: [50 character or so descriptive subject here (for reference)] >Description: first thing i am not sure, if this is a bug or I am missunderstanding something. It is possible to insert multiple records in a database, having the same unique key, if the unique key or one of its fields contain NULL. I know that NULL compares to itself as not equal, but I dont know if this should happen on a unique Key. The documentation did not seem to say something about this >How-To-Repeat: create the following table (MyISAM) create table times ( id int(11) not null, start integer unsigned not null, stop integer unsigned, # NULL is allowed flags integer not null default 0, unique (start, stop ,id) ); insert into times (id,start,stop,flags) values (1,1000,null,0); Repeat this insert statement. you can insert the same dataset as often as you want. This works only if one of the fields in the UNIQUE index is NULL. This also happens under mysql 3.23.38 >Fix: >Submitter-Id: >Originator:root >Organization: >MySQL support: [none | licence | email support | extended email support ] >Synopsis: >Severity: <[ non-critical | serious | critical ] (one line)> >Priority: <[ low | medium | high ] (one line)> >Category: mysql >Class: <[ sw-bug | doc-bug | change-request | support ] (one line)> >Release: mysql-3.23.34 (Official MySQL RPM) >Server: /usr/bin/mysqladmin Ver 8.17 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 Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 18 days 1 hour 24 min 42 sec Threads: 1 Questions: 57091 Slow queries: 0 Opens: 450 Flush tables: 1 Open tables: 64 Queries per second avg: 0.037 >Environment: System: Linux mfriebe1 2.2.14-5.0 #1 Tue Mar 7 20:53:41 EST 2000 i586 unknown Architecture: i586 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='egcs' CFLAGS='-O6 -fomit-frame-pointer -mpentium' CXX='egcs' CXXFLAGS='-O6 -fomit-frame-pointer-felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Mar 5 02:40 /lib/libc.so.6 -> libc-2.1.3.so -rwxr-xr-x1 root root 4101324 Feb 29 2000 /lib/libc-2.1.3.so -rw-r--r--1 root root 20272704 Feb 29 2000 /usr/lib/libc.a -rw-r--r--1 root root 178 Feb 29 2000 /usr/lib/libc.so Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --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 --without-berkeley-db --without-innobase '--with-comment=Official MySQL RPM' -- Martin Friebe - 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