MySQL 5 problems

[guychi]

Hi

I've been trying to get mysql5 up and running.

First I tried using darwinports, hoping for smooth integration with the rest of my darwinports packages, but ran into all kinds of error messages I haven't recorded.

Then I gave up and tried using the official installer. Again no luck:

$ mysql5
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/opt/local/var/run/mysql5/mysqld.sock' (2)

The mysql processes were:

root      142  0.0 -0.0    27724    420  ??  S    Sat10AM  0:00.02 /bin/sh ./bin/mysqld_safe --datadir=/usr/loc
mysql      174  0.0 -0.1    64312  1744  ??  S    Sat10AM  0:41.52 /usr/local/mysql/bin/mysqld --basedir=/usr/l

I guessed there is some conflict with the builtin mysql4 of the personal web sharing, so I killed these processes and tried to start mysqld myself.

$ mysqld_safe5 
touch: /opt/local/var/db/mysql5/guys-computer.local.err: No such file or directory
chown: /opt/local/var/db/mysql5/guys-computer.local.err: No such file or directory
Starting mysqld daemon with databases from /opt/local/var/db/mysql5
/opt/local/bin/mysqld_safe5: line 379: /opt/local/var/db/mysql5/guys-computer.local.err: No such file or directory
/opt/local/bin/mysqld_safe5: line 385: /opt/local/var/db/mysql5/guys-computer.local.err: No such file or directory
STOPPING server from pid file /opt/local/var/db/mysql5/guys-computer.local.pid
tee: /opt/local/var/db/mysql5/guys-computer.local.err: No such file or directory
061120 06:47:24  mysqld ended
tee: /opt/local/var/db/mysql5/guys-computer.local.err: No such file or directory

I manually created the non-existant mysql5 dir above and chowned it to mysql:mysql and got:

$ mysqld_safe5 --user=mysql
touch: /opt/local/var/db/mysql5/guys-computer.local.err: Permission denied
Starting mysqld daemon with databases from /opt/local/var/db/mysql5
/opt/local/bin/mysqld_safe5: line 379: /opt/local/var/db/mysql5/guys-computer.local.err: Permission denied
/opt/local/bin/mysqld_safe5: line 385: /opt/local/var/db/mysql5/guys-computer.local.err: Permission denied
STOPPING server from pid file /opt/local/var/db/mysql5/guys-computer.local.pid
tee: /opt/local/var/db/mysql5/guys-computer.local.err: Permission denied
061120 06:51:07  mysqld ended
tee: /opt/local/var/db/mysql5/guys-computer.local.err: Permission denied

Even with sudo I get an enigmatic exit:

$ sudo mysqld_safe5 --user=mysql
Starting mysqld daemon with databases from /opt/local/var/db/mysql5
STOPPING server from pid file /opt/local/var/db/mysql5/guys-computer.local.pid
061120 06:51:22  mysqld ended

I'm not even sure where is the relevant error log to look at...

Any help would be greatly appreciated...

[Newbish]

The first error is because MySQL was not running.

The second error was because you tried to start MySQL was simply because you tried to start it as a non-root user, and your regular user does not have permission to use the various mysql files.

The third error -- even though you correctly started mysqld_safe using sudo -- is because the core databases for MySQL haven't been initialized yet. I am not sure how or where the Darwin Ports installation organizes things, so it is best I not comment on how to initialize the core databases. I could really mess you up.

In all honesty, I have had much better luck building MySQL from the source code than I ever have trying to install a binary package of mysql. The instructions for initializing the core databases is in the text file name INSTALL-SOURCE, which I highly recommend you read first before proceeding.

You can get the source code from the downloads page at mysql.com. (The source code is at the bottom of the page, grab the "tarball").

You can ignore the part in INSTALL-SOURCE about creating a new user for MySQL, as OS X already has the mysql user set up.

Follow the instructions starting from ./configure --prefix=/usr/local/mysql and you should be up and running in less than 30 minutes. (Or sooner -- it depends on how fast your computer can compile the source code.)

One More Thing...

I like to do this before I actually start MySQL:

mv /usr/local/mysql /usr/local/mysql-version_number_here
ln -s /usr/local/mysql-version_number_here /usr/local/mysql

That creates this type of situation:

lrwxr-xr-x    1 root  mysql   24 Nov  7 09:45 mysql -> /usr/local/mysql-5.0.27/
drwxr-xr-x   12 root  mysql  408 Sep 26 11:22 mysql-5.0.19
drwxr-xr-x   11 root  mysql  374 Nov  7 09:55 mysql-5.0.27
lrwxr-xr-x    1 root  wheel   20 Nov  7 09:33 php5 -> /usr/local/php5.2.0/
drwxr-xr-x    7 root  wheel  238 Nov  2 12:25 php5.1.6
drwxr-xr-x    7 root  wheel  238 Nov  7 09:32 php5.2.0

I create a logical link pointing at the version of mysql I want to use because if a newer version of MySQL turns out to not work, all I have to do is redo the logical link to a version of MySQL that does work. (Notice I do the same thing with PHP)

Remember to delete the logical link before each time you install a newer version, or else you will overwrite the previous installation. (Yep! I've done that waaaay too many times...! )

[guychi]

Should I use all the options recommended in the INSTALL-SOURCE? I.e.:

CFLAGS="-O3" CXX=gcc CXXFLAGS="-O3 -felide-constructors \
-fno-exceptions -fno-rtti" ./configure \
--prefix=/usr/local/mysql --enable-assembler \
--with-mysqld-ldflags=-all-static

I'm on a MacBook...

And by the way, the current situation in my /usr/local is:

drwxr-xr-x   16 root  wheel  544 Nov 19 01:59 bin
drwxr-xr-x    3 root  wheel  102 Nov 19 01:59 man
lrwxr-xr-x    1 root  wheel   29 Nov 11 20:01 mysql -> mysql-max-5.0.27-osx10.4-i686
drwxr-xr-x   19 root  wheel  646 Oct 21 02:57 mysql-max-5.0.27-osx10.4-i686
drwxr-xr-x    3 root  wheel  102 Nov 19 01:59 share

Could it be something to do with the difference in group from your mysql? (root:wheel vs. root:mysql)

[Newbish]

As far as the extra compiler flags are concerned, I built 5.0.27 on a Mac Mini Core Duo without them, and it seems to be running fine. As to just what those flags are setting, I will defer to better qualified folks (Hayne, you reading this? ) to explain their significance. But, there's nothing wrong with trying them out. If things don't work, just delete the /usr/local/mysql-xx-xx-xx directory and rebuild it without those flags. (Maybe I'll try them myself.)

Before you actually try building anything, check to see if /usr/local/mysql/bin/mysql_install_db is there. If it is, run it with the flag:

/usr/local/mysql/bin/mysql_install_db --user=mysql

Some of the binary installs put this program into the ../scripts directory in the mysql directory.

That might get things up and running for you, without having to go through the compile.

The reason you are getting those last set of errors trying to run the current install is because the MySQL database hasn't been initialized. mysql_install_db creates the databases MySQL needs to run.

A warning: After running that program, mysql is running with no passwords needed. If you try to enter the MySQL shell with mysql -p, you will get an error. Just sign in (as root) without the -p flag, and immediately set passwords for mysql users.