View previous topic :: View next topic |
Author |
Message |
duodec
Joined: 05 Oct 2005 Posts: 15
|
Posted: Sun Aug 31, 2008 2:18 am Post subject: MySQL V5.1.23 install 'mysql' database table types/problem |
|
|
OpenVMS Alpha V8.2, all current ECOs (including the recent MUP) and ZLIB V1.2-3 per the docs. Alpha DS10 6/466, 1GB RAM
I installed MySQL on this server to use with VAMP software; once I got those software packages to use InnoDB tables they worked pretty well. This is a test box, no production. The root password was set using the 'oldpassword' option to work with the factory MOD_PHP from HP. The server was sitting idle since last weekend, then we had a long powerfail and it was not shut down properly.
After the restart, the MySQL root password was gone; trying to log on to MySQL or use the mysql* utilities using a password failed, but not using a password worked, including from the webserver. The web package databases were apparently fine but the 'mysql' database reported various corruptions. After running the repairs which claimed to be successful, it still wouldn't allow connection with the password, but worked with no password.
Then I checked and found all the 'mysql' database tables were of type MyISAM which I thought was not supposed to be used on VMS.
I just blew away the installation (completely) and reinstalled through the point of running @[.MYSQL]FIRST_START_MYSQLD. Although the log file shows numerous InnoDB messages, the actual tables are MyISAM again, except for general_log and slow_log which are type CSV.
The first_run_mysqld.com command procedure is specifically setting the engine to myisam.
Is this correct? Before I do any more work on reloading the web apps, I want to be sure this isn't a problem. I didn't see a mention elsewhere here on the boards, and the VAMP boards are down, so if this has been covered before, sorry. |
|
Back to top |
|
 |
jfp
Joined: 12 Jul 2004 Posts: 636
|
Posted: Sun Aug 31, 2008 10:17 am Post subject: |
|
|
You're correct only InnoDB is reliable on OpenVMS, but the mysql schema need to use MyISAM so it's an exception.
All others tables need to use InnoDB which seem to work corectly, I know a site with billions (yes billions...) records in a table without any problem.
Depend on the VMS and CRTL version MyISAM work more or less correctly.
A previous note give a pointer to a demonstation video (flash).
JF |
|
Back to top |
|
 |
duodec
Joined: 05 Oct 2005 Posts: 15
|
Posted: Sun Aug 31, 2008 5:24 pm Post subject: |
|
|
JFP,
thanks for taking time to respond.
I'll redefine the root password and rebuiild the apps again, and try a few crashes to see if I can replicate the behavior (of the root password simply disappearing). If the video can be viewed on my wife's Mac I'll watch it as well.
I don't believe I made any errors in installation or setup though. Just followed the directions on the vmsmysql page. The only variant was setting the 'oldpassword' to work with mod_php, and if that proves to be the issue I will attempt the relink procedure documented here.
Thanks again!
Rich |
|
Back to top |
|
 |
duodec
Joined: 05 Oct 2005 Posts: 15
|
Posted: Mon Sep 01, 2008 1:51 am Post subject: |
|
|
I defined a new root password on the test box, then 'redefined' it using OLD_PASSWORD after my php test failed (I forgot about it). The select shows the shorted hashed password as expected with OLD_PASSWORD. The 'flush privileges;' command was used each time.
I installed Joomla on the test box; it was done using instructions from here and VAMP, specifically JAR was used to unpack it, and the SQL files were modified to create InnoDB tables before it was set up. Ths install and setup were error free.
I then rebooted the Alpha normally. When it came up the root password was gone again. I can enter the mysql client, or the various others, with no password. The select statement in the instructions shows empty fields for the password for user root. A mysqlcheck run against both the mysql and joomla databases shows no errors (just the informationals on the two mysql log tables).
After manually resetting the root password using OLD_PASSWORD, the joomla site is working again.
What would cause the root password to get removed during a reboot?
Thanks!
Rich |
|
Back to top |
|
 |
duodec
Joined: 05 Oct 2005 Posts: 15
|
Posted: Tue Sep 02, 2008 5:03 pm Post subject: |
|
|
Confirmed. After running fine all day yesterday and into this morning, I rebooted the Alpha again. When it came up, the MySQL root password had been cleared again and I had to reset it (still using OLD_PASSWORD due to original HP MOD_PHP modules) before the websites came back up. |
|
Back to top |
|
 |
jfp
Joined: 12 Jul 2004 Posts: 636
|
Posted: Tue Sep 02, 2008 8:33 pm Post subject: |
|
|
Very strange,
can you try to shutdown MySQL, restart it then try to connect and let me know if the new password has evaporated... |
|
Back to top |
|
 |
duodec
Joined: 05 Oct 2005 Posts: 15
|
Posted: Wed Sep 03, 2008 12:06 am Post subject: |
|
|
JFP,
its behaving differently now.
Stopped MySQL using the shutdown command 3 times, and verifying the process was gone. Waited a few minutes then restarted. The password was present and correct.
I repeated that with the same results.
I then rebooted the system (with a 'normal' shutdown of MySQL before doing so). Same results, password was OK.
I then rebooted without shutting down MySQL (there is no MySQL shutdown in the system shutdown procedures either). Same result, password OK.
In all cases the 'old_password' type password was still in place and correct, because Joomla was able to access the database. I also ran MYSQLCHECK against both the mysql and the joomla database after the last shutdown, and no problems were reported.
So now I can't duplicate the behavior, but since its working I'm not going to be too unhappy about it. I will try tonight, perhaps hard crashing instead of shutting down, to see what happens (after I make a backup!)
Thanks for following up again. |
|
Back to top |
|
 |
duodec
Joined: 05 Oct 2005 Posts: 15
|
Posted: Wed Sep 03, 2008 4:17 am Post subject: |
|
|
Now it won't reccur. I crashed the alpha and rebooted it, without shutting down anything. The MySQL root password didn't change or disappear, the joomla set and test scripts worked just fine.
If it repeats I'll try to collect as much info as possible, but for now...
Thanks again.
Rich |
|
Back to top |
|
 |
WillemGrooters
Joined: 15 Apr 2006 Posts: 36
|
Posted: Tue Sep 09, 2008 8:53 am Post subject: |
|
|
I've seen the same behaviour on MySQL4.1. It is absolutely required you do after changing the root password (any password, in fact). A shutdown the database normally and start it up again assures the dataabse and cashed data in memory are in symch again.
If you forget this, and MySQL crashes, your changes are gone; the root password is empty and the only way to proceed, I found, was just reinstall it. In this respect theer is no difference between 4.1 and 5.1.
It didn't mean, however, the problem was gone competely. I had to rebuild the 4.1 database a few times after a power failure because the root password - even the full acoount - was gone. With 5.1 this hasn't happend yet (had no power fail) but it might happen. A frequent backup of your database (actually: dump of the contents) will come handy  |
|
Back to top |
|
 |
icl
Joined: 26 Oct 2007 Posts: 29 Location: London, UK
|
Posted: Thu Jul 29, 2010 3:16 am Post subject: |
|
|
I have been having fun with this myself....
I managed to identify the cause and a workaround.
When your MySQL server crashes and is restarted - it goes into a Crash Recovery mode. This crash recovery mode is meant to involve the system being updated to the latest state prior to the crash from the binary log files: mysql-bin.xxxxxxx.
This crash recovery phase doesn't work and consequently user accounts that haven't been flushed (flush tables) will not persist over such a crash.
To avoid the need to always perform a "flush tables" command to prevent such loss - I have found a workaround.
If you add the keyword "flush" to the my.cnf file under the [mysqld] section then it will force all changes to be flushed to disk immediately. |
|
Back to top |
|
 |
|