forum.vmspython.org Forum Index forum.vmspython.org
Forum system
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

MySQL V5.1.23 install 'mysql' database table types/problem

 
Post new topic   Reply to topic    forum.vmspython.org Forum Index -> MySQL for OpenVMS
View previous topic :: View next topic  
Author Message
duodec



Joined: 05 Oct 2005
Posts: 15

PostPosted: Sun Aug 31, 2008 2:18 am    Post subject: MySQL V5.1.23 install 'mysql' database table types/problem Reply with quote

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
View user's profile Send private message
jfp



Joined: 12 Jul 2004
Posts: 618

PostPosted: Sun Aug 31, 2008 10:17 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail Visit poster's website
duodec



Joined: 05 Oct 2005
Posts: 15

PostPosted: Sun Aug 31, 2008 5:24 pm    Post subject: Reply with quote

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
View user's profile Send private message
duodec



Joined: 05 Oct 2005
Posts: 15

PostPosted: Mon Sep 01, 2008 1:51 am    Post subject: Reply with quote

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
View user's profile Send private message
duodec



Joined: 05 Oct 2005
Posts: 15

PostPosted: Tue Sep 02, 2008 5:03 pm    Post subject: Reply with quote

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
View user's profile Send private message
jfp



Joined: 12 Jul 2004
Posts: 618

PostPosted: Tue Sep 02, 2008 8:33 pm    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail Visit poster's website
duodec



Joined: 05 Oct 2005
Posts: 15

PostPosted: Wed Sep 03, 2008 12:06 am    Post subject: Reply with quote

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
View user's profile Send private message
duodec



Joined: 05 Oct 2005
Posts: 15

PostPosted: Wed Sep 03, 2008 4:17 am    Post subject: Reply with quote

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
View user's profile Send private message
WillemGrooters



Joined: 15 Apr 2006
Posts: 32

PostPosted: Tue Sep 09, 2008 8:53 am    Post subject: Reply with quote

I've seen the same behaviour on MySQL4.1. It is absolutely required you do
Code:
flush privileges
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 Razz
Back to top
View user's profile Send private message Visit poster's website
icl



Joined: 26 Oct 2007
Posts: 29
Location: London, UK

PostPosted: Thu Jul 29, 2010 3:16 am    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    forum.vmspython.org Forum Index -> MySQL for OpenVMS All times are GMT + 2 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group