MySQL Bugs: #12475: schema corruption, catastrophic failure

Bug #12475	schema corruption, catastrophic failure
Submitted:	9 Aug 2005 23:12	Modified:	11 Aug 2005 2:15
Reporter:	Daniel Kasak (Candidate Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.10	OS:	Linux (Gentoo Linux PPC)
Assigned to:	Timothy Smith	CPU Architecture:	Any

Description:
The output speaks for itself:

skitz ~ # /etc/init.d/mysql start
* Starting mysqld (/etc/mysql/my.cnf) ... [ ok ]
skitz ~ # mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.10-beta-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
ERROR 1052 (23000): Column 'SCHEMA_NAME' in field list is ambiguous
mysql> use entropy;
Database changed
mysql> show tables;
ERROR 1052 (23000): Column 'TABLE_NAME' in order clause is ambiguous
mysql> quit
Bye
skitz ~ # mysqldump --opt --all-databases > full_dump -p
Enter password:
mysqldump: mysqldump: Couldn't execute 'SHOW DATABASES': Column 'SCHEMA_NAME' in field list is ambiguous (1052)
skitz ~ #

I *am* able to select from tables from the command-line client ... as long as I know the name of the table already.

I am *not* able to use any GUI tools to connect to mysql. I assume they all want to inspect the database schema, and give up when they hit something like the above errors.

I'm not after assistance in getting my data back - I have backups. There is at least one other user, however, who may want assistance - he responded to my initial post in the mysql list, and apparently wants his data back :)

How to repeat:
Don't know. It 'just happened'. It *could* have been after an upgrade from mysql-5.0.9 to mysql-5.0.10, but I can't be certain that's what caused it - I didn't pay close enough attention to say that the problem started immediately after an upgrade.

I'm uploading a tar.bz2'd copy of my /var/lib/mysql folder *minus* one particular folder ( incidents ), which has a client's data. They consider it confidential, and I'm pretty sure are legally bound to keep it so. The data itself is particularly uninteresting, but still, I've been cautioned that it's confidential so the data itself it out. Also left out are the logs which detail the data being loaded. Hopefully this will not be an issue. The database was working fine until well after this point anyway.

If other logs or the 'incidents' folder are required, I can upload them to some sort of secure area, but I don't really want to post them to a public bug-tracking system.

Change of plans.
The file is larger than the maximum allowed for http uploads, so I've ftp'd it to your server ... in the 'secret' directory. Since this file won't be publicly accessible, I've included the whole folder.

The file is named after the issue number:
issue_12475.tar.bz2

Daniel,

Thanks for providing all the info.  Unfortunately, I still can't repeat this with the data you sent:

[ts@production 10]$ tar jxf /supportftp/pub/mysql/upload/issue_12475.tar.bz2
[ts@production 10]$ mv mysql data
[ts@production 10]$ ./runserver -- --skip-grant-tables                     Running [ ./bin/mysqld_safe --no-defaults --basedir=/users/ts/m/50/10 --datadir=/users/ts/m/50/10/data --tmpdir=/users/ts/m/50/10/tmp --log-error=/users/ts/m/50/10/data/log.err --socket=mysql.sock --port=33500 --server-id=33500 --log-bin --skip-grant-tables & ]
Starting mysqld daemon with databases from /users/ts/m/50/10/data
[ts@production 10]$ mysql -A
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.10-beta-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| datasheet          |
| dreams             |
| entropy            |
| incidents          |
| mysql              |
| test               |
+--------------------+
7 rows in set (0.04 sec)

mysql> use entropy
Database changed
mysql> show tables;
+-------------------+
| Tables_in_entropy |
+-------------------+
| Clients           |
| Invoices          |
| Projects          |
| Tasks             |
+-------------------+
4 rows in set (0.00 sec)

mysql> Bye
[ts@production 10]$ uname -a
Linux production 2.6.11-1.14_FC3smp #1 SMP Thu Apr 7 19:38:19 EDT 2005 i686 i686 i386 GNU/Linux

I tried setting a different root password (the only change I made to your data), and restarting mysqld without --skip-grant-tables, and still had no troubles.

Perhaps there is some startup option which affects this - could you attach your my.cnf file to this bug report?

Could you try starting up a mysqld on a separate copy of your data, using the same method I used above for starting the server?  It won't interfere with your running server, since it uses a separate data directory, port and socket.

I tried both the mysql-debug-5.0.10-beta-linux-i686-glibc23 and mysql-standard-5.0.10-beta-linux-i686 binaries from our downloads page.

What exact Linux version are you using (uname -a)?  What glibc version (rpm -qa | grep glibc)?

Thanks,

Timothy

Same problem when started with above options.

System info:

skitz ~ # uname -a
Linux skitz 2.6.12-gentoo-r7 #4 Fri Aug 5 11:59:32 EST 2005 ppc 7455, altivec supported PowerBook3,5 GNU/Linux
skitz ~ # 

skitz ~ # emerge --info
Portage 2.0.51.22-r2 (default-linux/ppc/2005.0, gcc-3.4.4, glibc-2.3.5-r0, 2.6.12-gentoo-r7 ppc)
=================================================================
System uname: 2.6.12-gentoo-r7 ppc 7455, altivec supported
Gentoo Base System version 1.6.13
dev-lang/python:     2.3.5-r1, 2.4.1-r1
sys-apps/sandbox:    1.2.12
sys-devel/autoconf:  2.13, 2.59-r7
sys-devel/automake:  1.4_p6, 1.5, 1.6.3, 1.7.9-r1, 1.8.5-r3, 1.9.6
sys-devel/binutils:  2.16.1
sys-devel/libtool:   1.5.18-r1
virtual/os-headers:  2.6.11-r2
ACCEPT_KEYWORDS="ppc ~ppc"
AUTOCLEAN="yes"
CBUILD="powerpc-unknown-linux-gnu"
CFLAGS="-O2 -mtune=7450 -mcpu=7450 -maltivec -mabi=altivec -pipe"
CHOST="powerpc-unknown-linux-gnu"
CONFIG_PROTECT="/etc /usr/kde/2/share/config /usr/kde/3/share/config /usr/lib/X11/xkb /usr/lib/mozilla/defaults/pref /usr/share/config /var/qmail/control"
CONFIG_PROTECT_MASK="/etc/gconf /etc/splash /etc/terminfo /etc/env.d"
CXXFLAGS="-O2 -mtune=7450 -mcpu=7450 -maltivec -mabi=altivec -pipe"
DISTDIR="/usr/portage/distfiles"
FEATURES="autoconfig distlocks sandbox sfperms strict"
GENTOO_MIRRORS="http://distfiles.gentoo.org http://distro.ibiblio.org/pub/Linux/distributions/gentoo"
PKGDIR="/usr/portage/packages"
PORTAGE_TMPDIR="/var/tmp"
PORTDIR="/usr/portage"
PORTDIR_OVERLAY="/usr/local/portage"
SYNC="rsync://rsync.gentoo.org/gentoo-portage"
USE="ppc X alsa altivec berkdb bitmap-fonts cairo cdr composite crypt cups curl dv dvd dvdread eds emboss encode esd fam fame fbcon firefox fortran freetds gd gdbm gif glitz gnome gnutls gpm gstreamer gtk gtk2 hal hfs imagemagick imlib ipv6 jack java jpeg junit libwww lzo mad mjpeg motif mozilla mp3 mpeg mysql ncurses network nls nptl nptlonly odbc ogg oggvorbis opengl pam pcmcia pdflib perl php pmud png pvm python quicktime readline ruby samba sdl spell sqlite ssl tcltk tcpd theora tiff truetype truetype-fonts type1-fonts unicode vorbis xine xml2 xmms xprint xv xvid zlib userland_GNU kernel_linux elibc_glibc"
Unset:  ASFLAGS, CTARGET, LANG, LC_ALL, LDFLAGS, LINGUAS, MAKEOPTS

skitz ~ # 

The ebuild that was responsible for this version has the following in it:

        #the compiler flags are as per their "official" spec ;)
        #CFLAGS="${CFLAGS/-O?/} -O3" \
        export CXXFLAGS="${CXXFLAGS} -fno-implicit-templates -felide-constructors -fno-exceptions -fno-rtti"

        econf \
                --libexecdir=/usr/sbin \
                --sysconfdir=/etc/mysql \
                --localstatedir=/var/lib/mysql \
                --with-low-memory \
                --enable-assembler \
                --enable-local-infile \
                --with-mysqld-user=mysql \
                --with-client-ldflags=-lstdc++ \
                --enable-thread-safe-client \
                --with-comment="Gentoo Linux ${PF}" \
                --with-unix-socket-path=/var/run/mysqld/mysqld.sock \
                --with-zlib-dir=/usr \
                --with-lib-ccflags="-fPIC" \
                ${myconf} || die "bad ./configure"

I'm almost certain that I compiled mysql with gcc-3.4.4.

I will try recompiling, then if that doesn't improve things, I'll back going back to 5.0.9.

I'm also attaching my /etc/mysql/my.cnf ( too large to post into web form ).

/etc/mysql/my.cnf

Attachment: my.cnf (application/octet-stream, text), 4.00 KiB.

Daniel,

Thanks.  Please also try using our binary (no need to install it, you can just unpack it and run it in place).  That should eliminate most of the variables.

You can grab it from:

http://dev.mysql.com/get/Downloads/MySQL-5.0/mysql-standard-5.0.10-beta-linux-i686.tar.gz/...

Timothy

Daniel,

Sorry,ignore that last comment.  Of course you can't run the i686 binary, sorry.

Try building with no optimizations.  Add --with-debug=full to the configure options of your ebuild.  If it works without optimizations, but fails with them, it may point to a bug in the compiler, or ... who knows, but it'll be extra info.

Thanks,

Timothy

Yeah not a problem :)

I found a mysql-standard-5.0.10-beta-linux-powerpc-glibc23.tar.gz and I'm downloading that. I prefer to build from source because it integrates everything very well on Gentoo ( init scripts, dependancies and dependants, etc ), but of course I can use a binary for testing stuff like this.

I'll keep you posted.

MySQL PPC binary fixes it.
Sorry 'bout that. I've never had any issues with compiling MySQL on a Gentoo system, but admittedly this is a bit of an uncommon setup.

I will submit a bug report to the ebuild maintainer.

Thanks for your help.

Daniel,

OK, thanks for following up on that.

Regards,

Timothy