mysql_install_db5 works without '--user=_mysql' option, but mysql.user table not populated

[knightcode (Dylan)]

When I try

sudo mysql_install_db5 --user=_mysql 

or

sudo -u _mysql mysql_install_db5

I get this at the beginning of the output:

Installing MySQL system tables...
ERROR: 1004  Can't create file '/var/tmp/#sqlbd1f_1_0.frm' (errno: 9)
110314 10:20:07 [ERROR] Aborting

110314 10:20:07 [Note] /opt/local/libexec/mysqld: Shutdown complete

/var/tmp has the appropriate 0777 permissions for anything to be able to create something there, though.

If I do:

sudo mysql_install_db5

I get:

Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/opt/local/lib/mysql5/bin/mysqladmin -u root password 'new-password'
/opt/local/lib/mysql5/bin/mysqladmin -u root -h dhcp196.cs.pitt.edu password 'new-password'

Alternatively you can run:
/opt/local/lib/mysql5/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /opt/local ; /opt/local/lib/mysql5/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /opt/local/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /opt/local/lib/mysql5/bin/mysqlbug script! 

After that, I can start the server with "sudo mysqld_safe5 &" but I'm unable to log into the server with "mysql -u root" or any of the mysqladmin commands that should be run next. If I restart the server with "--skip-grant-tables", I can log in and see that the 'user' table of the 'mysql' database is empty. I'm not completely certain that it should or shouldn't be, but googling around seems to suggest that I should be able to set a root password in that table. My guess is that mysql_install_db5 is supposed to set up that user and it isn't. Is that right?

[jmroot (Joshua Root)]

Please remember to fill in the Port field and cc the maintainer.

[ryandesign (Ryan Carsten Schmidt)]

Your question doesn't sound MacPorts-specific. Have you asked in a MySQL support forum?

[knightcode (Dylan)]

Replying to ryandesign@…:

Your question doesn't sound MacPorts-specific. Have you asked in a MySQL support forum?

I haven't. My initial thought was that it had something to do with the user account, and I didn't know how that was getting set up on my macbook. But I'll seek out another forum if this isn't the venue for my problem.

[kemar (Marc Hertzog)]

Got same problem. Solved it using this answer on StackOverflow: ​http://stackoverflow.com/questions/4798464/installing-mysql-on-snow-leopard-using-macports/4907071#4907071

[pixilla (Bradley Giesbrecht)]

knightcode1 or marc,
What were the permissions on /opt/local/var/db/mysql5 after "port install mysql"?

$ ls -l /opt/local/var/db

[ryandesign (Ryan Carsten Schmidt)]

Note that "sudo port install mysql5" does not (is not designed to) create or set the permissions or ownership of /opt/local/var/db/mysql5; "sudo port install mysql5-server" is.

[knightcode (Dylan)]

Hi,

I first installed 'mysql' and then realized I really wanted mysql-server, which I installed after. Right now /opt/local/var/db/mysql5 has permissions 755 with user '_mysql' and group 'admin'.

I also downloaded the binary mysql package from mysql.com. I have its server up and running, but I had to run it from my user account and change the ownership of its 'data' directory accordingly. Maybe there's something I'm just not understanding about how to set this up with another user account. Should I just create another account in the System Preferences?

[pixilla (Bradley Giesbrecht)]

Replying to knightcode1@…:

I also downloaded the binary mysql package from mysql.com. I have its server up and running, but I had to run it from my user account and change the ownership of its 'data' directory accordingly.

This could conflict with your MacPorts mysql install. I recommend uninstalling this binary mysql package, delete or move /usr/local if it exists and delete or move /etc/my.cnf if it exists.

Maybe there's something I'm just not understanding about how to set this up with another user account. Should I just create another account in the System Preferences?

No, the "_mysql" user account should already exist.

$ dscl localhost -list /Local/Default/Users
$ dscl localhost -read /Local/Default/Users/_mysql

You should be able to start mysql5-server with:

$ sudo port load mysql5-server

Looks like the mysql root user exists and has a blank password. Add "-p" and hit enter "empty password" when prompted for a password.

$ mysqladmin5 -p -u root password 'new-password'

[kemar (Marc Hertzog)]

Cc Me!

[kemar (Marc Hertzog)]

pixilla, after a sudo port install mysql5 mysql5-server permissions on /opt/local/var/db/mysql5 were root:root

[knightcode (Dylan)]

I have the binary package at /Applications/mysql-5.5.9-osx10.6-x86/, and it seems to be self contained there. The unix socket and data folders are all created in separate folders. I don't have a /etc/my.conf, and /usr/local/ contains some other files whose purpose is definitely unrelated.

I tried the StackOverflow link from the previous comment but using '_mysql' as the user and group instead. mysql_install_db --user=_mysql' didn't complain. I ran the server using 'sudo /opt/local/bin/mysqld_safe5 --user=_mysql &' and got access denied with a subsequent 'sudo /opt/local/bin/mysqladmin5 -u root -p'. Restarting the server with --skip-grant-tables, I found that the mysql.user table is still empty.

[ryandesign (Ryan Carsten Schmidt)]

Replying to marc@…:

pixilla, after a sudo port install mysql5 mysql5-server permissions on /opt/local/var/db/mysql5 were root:root

The ownership should be _mysql:_mysql, which is what you can see the mysql5-server port does. If it's owned by root:root on your system, you must've done that manually, and should manually fix it using chown.

[ryandesign (Ryan Carsten Schmidt)]

Replying to knightcode1@…:

I have the binary package at /Applications/mysql-5.5.9-osx10.6-x86/, and it seems to be self contained there. The unix socket and data folders are all created in separate folders.

You should probably decide whose MySQL you want to use (MacPorts' or mysql.com's) and uninstall the other. It will reduce confusion.

I don't have a /etc/my.conf,

my.cnf, not my.conf

and /usr/local/ contains some other files whose purpose is definitely unrelated.

Having anything installed in /usr/local while using MacPorts is unsupported because of the many and various problems it can cause.

[knightcode (Dylan)]

The entire contents of my /usr/local/ directory:

$ sudo ls -R
Password:
bin	include	juniper	lib

./bin:
ldid	ldid2

./include:
fuse	fuse.h

./include/fuse:
fuse.h			fuse_common_compat.h	fuse_darwin.h		fuse_lowlevel_compat.h
fuse_common.h		fuse_compat.h		fuse_lowlevel.h		fuse_opt.h

./juniper:
nc

./juniper/nc:
6.5.0	install

./juniper/nc/6.5.0:
ncproxyd		nctun.kext		nctun_tiger.kext

./juniper/nc/6.5.0/nctun.kext:
Contents

./juniper/nc/6.5.0/nctun.kext/Contents:
Info.plist	MacOS

./juniper/nc/6.5.0/nctun.kext/Contents/MacOS:
nctun

./juniper/nc/6.5.0/nctun_tiger.kext:
Contents

./juniper/nc/6.5.0/nctun_tiger.kext/Contents:
Info.plist	MacOS

./juniper/nc/6.5.0/nctun_tiger.kext/Contents/MacOS:
nctun_tiger

./juniper/nc/install:
NCJarVerify.jar		installer.common	uninstall_nc.sh
fwk_reference_tool	ncinstallhelper		version

./lib:
libfuse.0.dylib			libfuse.la			libfuse_ino64.la
libfuse.2.7.3.dylib		libfuse_ino64.2.7.3.dylib	pkgconfig
libfuse.2.dylib			libfuse_ino64.2.dylib
libfuse.dylib			libfuse_ino64.dylib

./lib/pkgconfig:
fuse.pc

The juniper networks stuff is necessary for me to be able to VPN into my university's network. I can probably get rid of the FUSE stuff. There's no 'my.cnf' either (that was a typo).

Just to note, the problem predates the installation of the binary mysql. I'd prefer having the macports version.

[smitty1e@…]

I was running:

#sudo mysql_install_db5 --user=_mysql

and getting:

ERROR: 1004  Can't create file '/var/tmp/#sql12d_1_0.frm' (errno: 9)

this was because /var/tmp was root:wheel

so I did:

#sudo chown root:admin /var/tmp

and life improved