Ticket Options

Question Details

TICKET ARCHIVE -> mySQL misbehaving

moisheweiss - May 25, 2006 - 12:08 am

I am trying to do a database project for college. I don't remember which mySQL version I had to begin with but the Query Browser and Administrator kept quitting whenever I tried to add a foreign key. Somewhere in there I tried solving the problem by saying to change the tables to InnoDB. When that didn't work, i downloaded mySQL 5 and then the new Beta version. I now have two 'erased' version of mySQL (I hope) and all my schemas, user accounts and the like didn't carry forward. Most of the work I care about is from the mySQL 5 install, because that's where I did most of my project. But I also have data from a previous class in the first install.
I still can't get mySQL to behave with foreign keys, even though I am using InnoDB and on top of that I lost my project and logins. Now, my root account seems to be corrupted or something, so I can't add other accounts and schemas (to redo the project from scratch).

Before I screw myself into the ground further by installing a different mySQL, I hope someone can help unbury me. Thanks Moishe Weiss

BjarneDM - May 26, 2006 - 4:42 pm

I'm assuming that you are using downloads from MySQL.

To me it seems as if you've more or less hosed your install and a lot of data. My guess is that you've re-installed each time instead of upgrading.

You might be able to salvage data.

Use NetinfoManager to enable the root account. This is necessary, as the files and folders we are going to mess around with are owned completely by mysql and thus only accessible to mysql and root.

Login as root.
In Finder, use the 'Go to folder...' menu option to access this directory: /usr/local/mysql/data and see if any of your data is still present. Each database is completely contained i a folder. A fresh install only has a mysql and a test folder/database. If you see any more folders, they'll belong to old databases that haven't been registered with the new install.

Report your results of the above, and we'll take it from there.

moisheweiss - May 26, 2006 - 8:21 pm

1. Hi, I very well might have reinstalled vs upgrading as I didn't even know there were two ways. Usually, you (or rather, I) download the app, click on the dmg and then copy the .app file over the original. I take it there's a different method?

2. Found NetInfo Manager, opened it, clicked on the lock icon, entered my password. (Also tried using 'root' and 'su' as the user but neither took, so I used my regular account name.) Under security, I did 'Authenticate', then 'Enable Root User.' It told me I didn't have a password set up (which shows that it is not the same as su in terminal) so I gave it one (the same one that I use for su in terminal and for my regular account, and the one I was using for mysql before the upgrade/overwrite erased it). Went to finder->Go->Go to Folder...->'/usr/local/mysql/data'. Got response: folder cannot be found.

2b. Opened Terminal:
cd /
cd usr
cd local
cd mysql
cd data -> "access denied"
sudo bash -> password...

cd /usr/local/mysql/data
ls -l
-------cut and paste----------
total 40984
drwx------ 6 mysql wheel 204 May 25 11:09 GlassSlipper
-rw-rw---- 1 mysql wheel 7332 May 25 21:04 MW-iMac.local.err
-rw-rw---- 1 mysql wheel 4 May 25 21:04 MW-iMac.local.pid
drwx------ 6 mysql wheel 204 May 23 17:54 cluster_replication
-rw-rw---- 1 mysql wheel 5242880 May 25 21:04 ib_logfile0
-rw-rw---- 1 mysql wheel 5242880 May 23 17:58 ib_logfile1
-rw-rw---- 1 mysql wheel 10485760 May 25 11:09 ibdata1
drwxr-x--- 63 mysql wheel 2142 May 23 17:54 mysql
drwxr-x--- 2 mysql wheel 68 Feb 27 16:16 test
MW-iMac:/usr/local/mysql/data root#
------end cut and paste-----------------------

2c. Went back to NetInfo Manager, disabled root user and deauthenticated. Quit NetInfo Manager - but not before noticing a statement on the dock next to it. I think it was something like "local @ localhost - /"

3. By the way, that Terminal output is about half of everything I know how to do in Unix.

Thanks for helping me out, I was thinking I was going to have to read a few fat books about mac Unix to solve this. (I should do that anyway but that is SO a different project.

moisheweiss - May 26, 2006 - 8:30 pm

Update: I just realized that I did something in Terminal yesterday:

MW-iMac:/Library/MySQL/data root# ls
GlassSlipper cis330 ib_logfile0 moishe-weiss-imac-g5.local.err
MW-iMac.local.err comp370 ib_logfile1 mysql
MW-iMac.local.pid ib_arch_log_0000000000 ibdata1 test
MW-iMac:/Library/MySQL/data root#

Now THOSE are the old files! Or at least the names of some of them. The GlassSlipper thing is the one I am working on, so It should be in three different places for three different 'takes' unless two of the takes ended up in the same place. I seem to feel there is at least one other database account or schema missing though (not that I am sure which, or which the above are - I just feel like I am not seeing everthing).

BjarneDM - May 28, 2006 - 6:43 am

1) the reason why you didn't get it to work in Finder was that you didn't login as root. I didn't know how proficient you were using Terminal, so I thought the safest option was to use the Apple GUI.

2) now, what has happened is, that the mysql database has been created from scratch each time you installed a new version of MySQL, thus loosing the contact with the databases you had created. Your data didn't get deleted - the database engine just lost contact with the databases you had created. It ought to be somewhat simple to get the connection re-established.

Do the following:

In Terminal login at the root account in MySQL:
mysql --user="root" --password=""

in MySQL do the following:
create user '' identified by '' ;
http://dev.mysql.com/doc/refman/5.1/en/create-user.html
grant all on .* to ''@'' ;
http://dev.mysql.com/doc/refman/5.1/en/grant.html
flush privileges ;
http://dev.mysql.com/doc/refman/5.1/en/flush.html
exit ;

BjarneDM - May 28, 2006 - 6:49 am

Do the following:

In Terminal login at the root account in MySQL:
mysql --user="root" --password="password"

in MySQL do the following:
create user 'user' identified by 'password' ;
http://dev.mysql.com/doc/refman/5.1/en/create-user.html
grant all on db_name.* to 'user'@'host' ;
http://dev.mysql.com/doc/refman/5.1/en/grant.html
flush privileges ;
http://dev.mysql.com/doc/refman/5.1/en/flush.html
exit ;

moisheweiss - May 28, 2006 - 12:37 pm

MW-iMac:/ Moishe$ mysql --user="root" --password=""
-bash: mysql: command not found
MW-iMac:/ Moishe$ whereis mysql
MW-iMac:/ Moishe$ apropos mysql
mysql_table(5) - Postfix MySQL client configuration
MW-iMac:/ Moishe$

moisheweiss - May 28, 2006 - 1:17 pm

It's not in my path but here it is:

MW-iMac:/Library/MYSQL/bin Moishe$ ./mysql --user="root" --password=""
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.1.7-beta

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

mysql>
granted all on *.* to TheMan@localhost

moisheweiss - May 28, 2006 - 1:26 pm

logging in using 'TheMan', no password took. (Using the password gives access denied. w/o password gets entry.)

moisheweiss - May 28, 2006 - 11:15 pm

Oops, I see I left out some information - after finding the mysql path, I went to that directory and typed in the mysql command like this:
./mysql --user="root" password=""
I then did:
create user 'TheMan' identified by 'dontmess4me';
grant all on *.* to TheMan@localhost;
flush priviliges;
exit;

after that I did:
./mysql --user='TheMan' --password='dontmess4me'
that returnded access denied. But this got me in:
./mysql --user='TheMan' --password=''

moisheweiss - May 30, 2006 - 8:24 pm

I'm afraid I've made matters worse...

Having decided that I had found the files, I also decided to move them myself, which meant changing file permissions in a lot of places. I'm not sure what I did but now I can't start the server.

moisheweiss - May 30, 2006 - 11:05 pm

Okay, better.
I saved the directories in the data directory which I recongnized to my desktop. Then I deleted every reference to mysql I could find (including the entire /Library/MySQL directory and the /usr/local/mysql[...] directories. Then I reinstalled the General Release - Standard 5.0.whatever for Mac 10.4 (PowerPC). I then moved the files on my desktop to the data directory in /usr/local/mysql/data and used chmod and chown (copied from a book on unix) to make those directories and contents look like the 'mysql' directory in the data directory. Again using the book I even added the path to mysql in the .bashrc file! I THINK things are looking better. I was able to add accounts in mysql administrator and the databases I moved also show up. However, it looks like the accounts I added have universal privileges, although thats not what I 'said' when I created them. I'm going to see if I can recreate the database I was beginning when all this @$# happened, by cutting and pasting the queries into a query window. Crossed fingers.