Problems with MySQL 4.0.9gamma and vBulletin

[TECK]

Problem solved. The fix is here (http://www.vbulletin.com/forum/showthread.php?threadid=63421&postid=404935). :)

OS: WinXP Pro P4 2GIG (1 processor)
PHP Ver: 4.3.0
Server Software: IIS 5.1 (lockdown tool cleaned)
MySQL Ver: 4.0.9gamma

The problem I get is on step 3 of VB install:Attempting to attach to database...

Warning: mysql_connect() [function.mysql-connect]: Access denied for user: 'root@127.0.0.1' (Using password: YES) in c:\inetpub\wwwroot\vbulletin\admin\db_mysql.php on line 40

Warning: mysql_select_db(): supplied argument is not a valid MySQL-Link resource in c:\inetpub\wwwroot\vbulletin\admin\db_mysql.php on line 47

The database has failed to connect because you do not have permission to connect to the server. Please go back to the last step and ensure that you have entered all your login details correctly.

[TECK]

The last 15lines on mysql.err:030118 17:25:57 InnoDB: Setting file .\ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
030118 17:25:57 InnoDB: Log file .\ib_logfile0 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
030118 17:25:57 InnoDB: Log file .\ib_logfile1 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
MySql: ready for connections
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
030118 17:26:02 InnoDB: Started

[TECK]

eva2000? Anyone? There are many SQL experts here... and no post. :(
C:\WINDOWS\system32\inetsrv\mysql\bin>mysql -uroot -ppassword ver
ERROR 1045: Access denied for user: 'root@127.0.0.1' (Using password: YES)

[eva2000]

reasons why 4.09 is still gamma and not stable release

are you using innodb tables at all ? if not add under [mysqld] in your my.cnf file

skip-innodb

and then restart mysql

[TECK]

Originally posted by eva2000
reasons why 4.09 is still gamma and not stable release

are you using innodb tables at all ? if not add under [mysqld] in your my.cnf file

skip-innodb

and then restart mysql Thanks George, will do this...

[Stadler]

Try to login to the database without using a password and to set it later. This has worked for me.

[fastforward]

It's nothing to with MySQL being gamma.

The problem is clearly shown in the error:


Attempting to attach to database...

Warning: mysql_connect() [function.mysql-connect]: Access denied for user: 'root@127.0.0.1' (Using password: YES) in c:\inetpub\wwwroot\vbulletin\admin\db_mysql.php on line 40

Simply connect using a valid user.

MySQL 4.0.9 is more than ready for production on a vBulletin site. You're far more likely to come across a bug in vB than MySQL 4.0.9. MySQL and most other leading database server vendors will always delay a final release until they get the nod from all their leading customers that run real mission critical apps.

[Stadler]

Well, on my local machine MySQL4 removes alle Users Settings and passwords after installing a new version. And it seems to ignore, if you have set password=password in your my.ini.

[fastforward]

Originally posted by Stadler
Well, on my local machine MySQL4 removes alle Users Settings and passwords after installing a new version. And it seems to ignore, if you have set password=password in your my.ini.
I'm assuming you mean MySQL users. This is probably because you didn't run the fix-privileges script to upgrade the user and permission tables.

If you mean vB users then I'm afraid I can't believe MySQL was at fault. I have installed every released version of MySQL plus some other CVS versions since version 4.0.1 and never has it deleted any data without me telling it to. Nor have I heard of anybody else losing data that can't be explained by user actions.

As for the password issue: Where are you trying to enter the password? Are you sure it's supported in the main my.cnf in this version? From the manual:

Store your password in a configuration file. For example, you can list your password in the [client] section of the `.my.cnf' file in your home directory:
[client]
password=your_pass

Note the dot and the 'home' directory location.

[Stadler]

Originally posted by fastforward
I'm assuming you mean MySQL users. This is probably because you didn't run the fix-privileges script to upgrade the user and permission tables. Yes, I meant that. But I have no idea, how to run the fix-privileges script on my Win2k-Machine.

[fastforward]

Originally posted by Stadler
Yes, I meant that. But I have no idea, how to run the fix-privileges script on my Win2k-Machine.
Ah. I should have spotted your reference to my.ini and realized you were on windows.

I'm afraid I haven't had any experience with running it on windows. In the Linux install, it's a shell script mysql_fix_privilege_tables. Have a look to see if there's a file with a similar name in your installation.

[MUG]

Originally posted by fastforward
Ah. I should have spotted your reference to my.ini and realized you were on windows.

I'm afraid I haven't had any experience with running it on windows. In the Linux install, it's a shell script mysql_fix_privilege_tables. Have a look to see if there's a file with a similar name in your installation. I just copied the var directory from my MySQL 3.23.54a installation... not sure if that caused any problems, but I'm not having any. :D

[fastforward]

Originally posted by MUG
I just copied the var directory from my MySQL 3.23.54a installation... not sure if that caused any problems, but I'm not having any. :D
Yeah. It should continue to work without problems. The only difference is that you will be missing the option to grant a few extra privileges.

You can also continue to use older clients with the server. They will work without problems and are fully supported.

I'm guessing the problem Stadler had was caused by the nre installation overwriting the existing mysql system tables. This doesn't happen on Linux if you use the RPM.

[TECK]

Originally posted by fastforward
It's nothing to with MySQL being gamma.

The problem is clearly shown in the error:


Simply connect using a valid user.

MySQL 4.0.9 is more than ready for production on a vBulletin site. You're far more likely to come across a bug in vB than MySQL 4.0.9. MySQL and most other leading database server vendors will always delay a final release until they get the nod from all their leading customers that run real mission critical apps. Paul, I already set the right users, in my case was root/password (you can see this from my config.php file) ...
I uninstalled gamma and went back to 2.x, I have no problems now...
However, I would like to benefit also from the fast queries cache system, available on gamma.
Do you guys recommend a link or site for the gamma 101 install steps?

The funny part is that I never had a problem until 4.0.6 was released.

[fastforward]

Originally posted by TECK
Paul, I already set the right users, in my case was root/password (you can see this from my config.php file) ...
I uninstalled gamma and went back to 2.x, I have no problems now...
However, I would like to benefit also from the fast queries cache system, available on gamma.
Do you guys recommend a link or site for the gamma 101 install steps?

The funny part is that I never had a problem until 4.0.6 was released.
I can't help much I'm afraid. I've not installed MySQL on windows for some time.

I do read the mailing list and I haven't seen anyone mention any problems. But it's only been out a few days so I'll keep an eye on the list for you.

If it's a MySQL problem, you can guarantee you won't be the only one experiencing it.

[TECK]

Originally posted by Stadler
Well, on my local machine MySQL4 removes alle Users Settings and passwords after installing a new version. And it seems to ignore, if you have set password=password in your my.ini. In other words, you set the password to a value different then "password"?
I could try this, maybe it will solve my issues... Please let me know exacly what you did.
Thanks.

[TECK]

Nope, that didn't help.
My.ini file:
[mysqld]
basedir=C:/WINDOWS/system32/inetsrv/mysql
#bind-address=192.168.1.100
datadir=C:/WINDOWS/system32/inetsrv/mysql/data
#language=C:/WINDOWS/system32/inetsrv/mysql/share/your language directory
#slow query log#=
#tmpdir#=
#port=3306
#set-variable=key_buffer=16M
[WinMySQLadmin]
Server=C:/WINDOWS/system32/inetsrv/mysql/bin/mysqld-nt.exe
user=Floren
password=roottest
I extracted the windows binary to a temp folder and Install it (on a fresh clean winXP pro install machine).
I ran the winmysqladmin and prompted me to create a new user/password.
I restarted the SQL service.

Same prob, I cannot connect.
I also noticed those errors, in error file:
InnoDB: The first specified data file .\ibdata1 did not exist:
InnoDB: a new database to be created!
030128 19:57:55 InnoDB: Setting file .\ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
030128 19:57:55 InnoDB: Log file .\ib_logfile0 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
030128 19:57:55 InnoDB: Log file .\ib_logfile1 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
MySql: ready for connections
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
030128 19:58:01 InnoDB: Started
030128 20:00:08 MySql: Normal shutdown

030128 20:00:08 InnoDB: Starting shutdown...
030128 20:00:09 InnoDB: Shutdown completed
030128 20:00:09 MySql: Shutdown Complete

MySql: ready for connections
030128 20:01:25 InnoDB: Started
030128 20:16:21 MySql: Normal shutdown

030128 20:16:21 InnoDB: Starting shutdown...
030128 20:16:22 InnoDB: Shutdown completed
030128 20:16:22 MySql: Shutdown Complete

MySql: ready for connections
030128 20:16:29 InnoDB: Started
030128 20:22:30 MySql: Normal shutdown

030128 20:22:30 InnoDB: Starting shutdown...
030128 20:22:31 InnoDB: Shutdown completed
030128 20:22:31 MySql: Shutdown Complete

MySql: ready for connections
030128 20:24:32 InnoDB: Started

[TECK]

The funny part is that MySQL service is running...

[nuno]

Why are you installing MySQL to c:\WINDOWS\...\mysql in the first place?
Install it to c:\mysql.

030118 17:25:57 InnoDB: Setting file .\ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
030118 17:25:57 InnoDB: Log file .\ib_logfile0 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
030118 17:25:57 InnoDB: Log file .\ib_logfile1 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
MySql: ready for connections
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
030118 17:26:02 InnoDB: Started

I see no errors here.

[mysqld]
basedir=C:/WINDOWS/system32/inetsrv/mysql
#bind-address=192.168.1.100
datadir=C:/WINDOWS/system32/inetsrv/mysql/data
#language=C:/WINDOWS/system32/inetsrv/mysql/share/your language directory
#slow query log#=
#tmpdir#=
#port=3306
#set-variable=key_buffer=16M
[WinMySQLadmin]
Server=C:/WINDOWS/system32/inetsrv/mysql/bin/mysqld-nt.exe
user=Floren
password=roottest

try this

[mysqld]
basedir=C:/WINDOWS/system32/inetsrv/mysql
#bind-address=192.168.1.100
datadir=C:/WINDOWS/system32/inetsrv/mysql/data
#language=C:/WINDOWS/system32/inetsrv/mysql/share/your language directory
#slow query log#=
#tmpdir#=
#port=3306
#set-variable=key_buffer=16M
[WinMySQLadmin]
Server=C:/WINDOWS/system32/inetsrv/mysql/bin/mysqld.exe

Go to C:/WINDOWS/system32/inetsrv/mysql/bin/ and delete mysqld.exe.
Rename mysqld-nt.exe to mysqld.exe.
Restart MySQL.

[TECK]

nuno, thanks for helping.
You cannot do this because you will mess the InnoDB.
And it doesn't matter where you install it as long as you use short directories. :)
I keep everything toghether in the /inetsrv folder... php and mysql, since forever.
Btw, as I mentioned before, installing 3.x will work fine, there is something else I miss...

I wonder what steps people did to get it working?
Do you run 4.0.9-gamma?

Here it is the way it looks as default, without your changes...

[nuno]

I'm running under Windows .NET, PHP 4.3.0, MySQL 4.0.7-gamma-nt with innoDB.
No problems here.
Here goes my my.ini file

[client]
port=3306

[mysqld]
port=3306
skip-locking
default-table-type=InnoDB
set-variable = max_connections=500
set-variable = key_buffer=32M
set-variable = max_allowed_packet=2M
set-variable = table_cache=1024
set-variable = sort_buffer=4M
set-variable = net_buffer_length=8K
set-variable = myisam_sort_buffer_size=128M
server-id = 1

innodb_data_file_path = ibdata1:400M
innodb_data_home_dir = c:\ibdata
innodb_log_group_home_dir = c:\iblogs
innodb_log_arch_dir = c:\iblogs
set-variable = innodb_mirrored_log_groups=1
set-variable = innodb_log_files_in_group=3
set-variable = innodb_log_file_size=5M
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit =1
innodb_log_archive =0
set-variable = innodb_buffer_pool_size=400M
set-variable = innodb_additional_mem_pool_size=2M
set-variable = innodb_file_io_threads=4
set-variable = innodb_lock_wait_timeout=50

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[mysql]
no-auto-rehash

[isamchk]
set-variable = key_buffer=20M
set-variable = sort_buffer=20M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[myisamchk]
set-variable = key_buffer=20M
set-variable = sort_buffer=20M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[mysqlhotcopy]
interactive-timeout
[WinMySQLAdmin]
Server=C:/mysql/bin/mysqld-max-nt.exe

[TECK]

Ok, I changed all settings back to root/password.
Now I try to run a simple command and look at the results...
It pisses me off. :(

[TECK]

nuno, it works for me also, in a lower version... other then 4.0.9...
But other people told me they got it working?

[nuno]

Originally posted by TECK
Ok, I changed all settings back to root/password.
Now I try to run a simple command and look at the results...
It pisses me off. :(

mysql -uroot -ppassword ver? :confused:
shouldn't that be
mysqladmin -uroot -pyour_password ver?
I'll upgrade mine to 4.0.9 then.
Back in a mo. :)

[fastforward]

Try adding the following to your my.cnf, ini or whatever it's called.

skip-name-resolve

[nuno]

Ok, do this:
Paste this into your my.ini file

[mysqld]
basedir=C:/WINDOWS/system32/inetsrv/mysql
#bind-address=192.168.1.100
datadir=C:/WINDOWS/system32/inetsrv/mysql/data
#language=C:/WINDOWS/system32/inetsrv/mysql/share/your language directory
#slow query log#=
#tmpdir#=
#port=3306
#set-variable=key_buffer=16M
[WinMySQLadmin]
Server=C:/WINDOWS/system32/inetsrv/mysql/bin/mysqld.exe
user=root
password=your_password

Restart MySQL.

Paul, nice job you did on Overgrow's forum, i wish you could release it as a hack. :)

[TECK]

Nope, it's not working, I cannot connect.

[eva2000]

reason why nuno has no innodb related errors and teck you do is you're missing what i suggested in posr #4 ;)

also i'd move and rename your my.ini to C:\my.cnf

[eva2000]

Originally posted by TECK
Ok, I changed all settings back to root/password.
Now I try to run a simple command and look at the results...
It pisses me off. :( run it without passing password on same line

mysql -u root -p version

[TECK]

Thanks George. :)
I found a fix on my own... Just do what is posted in the attachment, it will get it working.
Here it is explained for several type of users (hope that will save someone else's white hair):C:\mysql\bin> mysql --user=root mysql
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@localhost
-> IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@"%"
-> IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON *.* TO admin@localhost;
mysql> GRANT USAGE ON *.* TO dummy@localhost;
These GRANT statements set up three new users:

monty
A full superuser who can connect to the server from anywhere,
but who must use a password 'some_pass' to do so.
Note that we must issue GRANT statements for both monty@localhost and monty@"%".
If we don't add the entry with localhost, the anonymous user entry for localhost
that is created by mysql_install_db will take precedence when we connect
from the local host, because it has a more specific Host field value and
thus comes earlier in the user table sort order.

admin
A user who can connect from localhost without a password and who is granted
the RELOAD and PROCESS administrative privileges. This allows the user to execute
the mysqladmin reload, mysqladmin refresh, and mysqladmin flush-* commands,
as well as mysqladmin processlist . No database-related privileges are granted.
(They can be granted later by issuing additional GRANT statements.)

dummy
A user who can connect without a password, but only from the local host.
The global privileges are all set to 'N'—the USAGE privilege type allows you
to create a user with no privileges. It is assumed that you will grant
database-specific privileges later.

[TECK]

I used my.ini file. I'm happy again, just installed vBulletin and it flyes under the new gamma. :D

[nuno]

Dear Lord, after several hours i finally did it. :D
The solution is here -> http://www.mysql.com/doc/en/Access_denied.html
Procedure

shell> mysql -u root mysql
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('%','your_db_name_here',PASSWORD ('your_db_password_here'));
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('localhost','your_db_name_here',PASSWORD ('your_db_password_here'));
mysql> SET PASSWORD FOR your_db_name_here@"%" = PASSWORD('your_db_password_here');
mysql> SET PASSWORD FOR your_db_name_here@"localhost" = PASSWORD('your_db_password_here');
mysql> GRANT USAGE ON *.* TO your_db_name_here@"%" IDENTIFIED BY 'your_db_password_here';
mysql> GRANT USAGE ON *.* TO your_db_name_here@"localhost" IDENTIFIED BY 'your_db_password_here';
mysql> FLUSH PRIVILEGES;

All done.
Damn damn damn, now i'm going to bed. :D

[nuno]

*nuno throws a rock at TECK* :D
My head hurts. :p
And george is right about innoDB BTW. ;)