I noticed that my users couldn't access their tables from within phpMyAdmin, which lead to some debugging and finally to the origin of the problem. When I try to SELECT tables from databases I didn't activate by USEing them before, MySQL returns weird errors (on two different systems): mysql> SELECT * FROM mysql.users; ERROR 1146 (42S02): Table '
I noticed that my users couldn't access their tables from within phpMyAdmin, which lead to some debugging and finally to the origin of the problem. When I try to SELECT tables from databases I didn't activate by USEing them before, MySQL returns weird errors (on two different systems): mysql> SELECT * FROM mysql.users; ERROR 1146 (42S02): Table '¼ .users' doesn't exist mysql> USE mysql; Database changed mysql> SHOW TABLES; ERROR 1146 (42S02): Table 'mysql.TABLE_NAMES' doesn't exist After I first upgraded to 5.0.22 (from 4.1.20) I got many problems with mysql_install_db. The mysqld-process spawned by that script just froze. After killing it, I had pointless logfiles in /var/lib/mysql/, telling me nothing, a mysql/ and a test/-directory, both empty. It finally worked out when I copied the mysql-database-folder from another (working) installation. Now it's getting even more weird: The same errors than with 4.1.20 are occuring and now a SELECT from a USEd table doesn't work, too. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 to server version: 5.0.22-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> USE mysql; SELECT * FROM users; Database changed ERROR 1146 (42S02): Table 'mysql.users' doesn't exist That table has to exist as I wouldn't be able to login (with password) if it didn't. Everything works when using the binaries provided by mysql.com. zakx@pepsi ~ $ sudo emerge --info Portage 2.0.54-r2 (default-linux/x86/2006.0, gcc-3.4.6, glibc-2.3.6-r3, 2.6.14-gentoo-r2 i686) ================================================================= System uname: 2.6.14-gentoo-r2 i686 Pentium III (Coppermine) Gentoo Base System version 1.6.14 dev-lang/python: 2.3.5-r2, 2.4.2 dev-python/pycrypto: [Not Present] dev-util/ccache: [Not Present] dev-util/confcache: [Not Present] sys-apps/sandbox: 1.2.17 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-r1 sys-devel/binutils: 2.16.1-r2 sys-devel/gcc-config: 1.3.13-r2 sys-devel/libtool: 1.5.22 virtual/os-headers: 2.6.11-r2 ACCEPT_KEYWORDS="x86" AUTOCLEAN="yes" CBUILD="i386-pc-linux-gnu" CFLAGS="-O2 -mcpu=i686 -fomit-frame-pointer -fforce-addr" CHOST="i386-pc-linux-gnu" CONFIG_PROTECT="/etc" CONFIG_PROTECT_MASK="/etc/gconf /etc/env.d" CXXFLAGS="-O1 -mcpu=i686 -fomit-frame-pointer -fforce-addr" DISTDIR="/usr/portage/distfiles" FEATURES="autoconfig distlocks sandbox sfperms strict" GENTOO_MIRRORS="http://distfiles.gentoo.org http://distro.ibiblio.org/pub/linux/distributions/gentoo" MAKEOPTS="-j2" PKGDIR="/usr/portage/packages" PORTAGE_TMPDIR="/var/tmp" PORTDIR="/usr/portage" SYNC="rsync://rsync.gentoo.org/gentoo-portage" USE="x86 apm avi bash-completion berkdb bitmap-fonts bzip2 cli crypt cups curl dri eds emboss encode expat fast cgi foomaticdb fortran ftp gd gdbm gif gmp gpm gstreamer hardened idn imagemagick imlib ipv6 isdnlog jabber jpe g libg++ libwww mad mhash mikmod motif mp3 mpeg mysql ncurses nls nptl offensive ogg oss pam pcre pdflib perl p ng pppd python readline recode reflection ruby session sockets spell spl sqlite ssl tcpd truetype truetype-font s type1-fonts udev vhosts vorbis xml xml2 xmms xorg xv zlib userland_GNU kernel_linux elibc_glibc" Unset: CTARGET, INSTALL_MASK, LANG, LC_ALL, LDFLAGS, LINGUAS, PORTAGE_RSYNC_EXTRA_OPTS, PORTAGE_RSYNC_OPTS, PO RTDIR_OVERLAY
So... what you are telling us here is that: - you essentially nuked your previous db by running mysql_install_db over an existing DB install - additionally, you killed mysql_install_db in the middle of its work - you didn't run emerge --config as the ebuild told you (well, you've essentially nuked your DB in step one, so it doesn't matter now that it's upgrade and it's not needed there) - then you did copy over a DB from another machine, probably also running a different major version of MySQL, containing different users, etc. and after you've done everything above, you go to file a BLOCKER bug and complain that mysql doesn't work. Right? Well, I guess you must be joking. How about that you start over from fresh, not getting every single install step completely wrong on the way?
Actually, no. I left out some details for the original bugs.mysql.com-Description. I ran emerge --config several times, telling me that the datadir is already existant. After I removed it, it also failed with the following error: //usr/bin/mysql_install_db: line 218: 17810 Aborted /usr/sbin/mysqld --bootstrap --skip-grant-tables --basedir=/usr --datadir=/var/lib/mysql --skip-innodb --skip-bdb --skip-ndbcluster --user=mysql --max_allowed_packet=8M --net_buffer_length=16K !!! ERROR: dev-db/mysql-5.0.22 failed. After that, I ran mysql_install_db, which exited with the same error. Running the failing command just results in a non-responding mysqld background process. I tried to fix that on my own ending up copying a working datadir (from the same mysql version, but different OS), the mysqld finally started (via the init script) but had the same errors 4.1 had (see initial bug description).
emerge --config is for the first install, not for an upgrade. The "mysql" database is broken. Try a clean upgrade, take http://dev.mysql.com/doc/refman/5.0/en/mysql-upgrade.html as a start point.
Maybe you didn't understand what I did. The last thing I did was removing mysql with "emerge -C mysql" and reemerging it, so it was a clean install. I also deleted /var/lib/mysql manually. Are there any files that are not removed when unmerging mysql which could cause my problems?
(In reply to comment #4) > Maybe you didn't understand what I did. The last thing I did was removing mysql > with "emerge -C mysql" and reemerging it, so it was a clean install. I also > deleted /var/lib/mysql manually. > > Are there any files that are not removed when unmerging mysql which could cause > my problems? > Not usually, at the time of slotted mysql many users had problem of an unclean unmerge. when mysql the directory that remain are: /etc/mysql /var/log/mysql plus files /etc/conf,init.d/mysql, the ones that should disappear are: /usr/share/mysql /usr/lib/mysql /usr/include/mysql and files /usr/lib/libmysqlclient* if there are still problems probably af full debug session, with backtraces and all will be needed A suggestion, emerge with "--buildpkg" , it will be faster to make other try
Can't reproduce this at all with current MySQL 5.0.26-r2, SELECTs work perfectly from the CLI client, even on non-USEd databases. Please be sure to cleanup everything like Francesco said in the last comment and try out 5.0.26-r2 and/or 5.0.32, reopen if needed. Best regards, CHTEKK.
