Authentication problem while using MySQL Version 4.X

Thursday, November 11th, 2004

Authentication problem while using MySQL Version 4.X

Created 2004-11-11 by Hamid Ali Raja

I am facing problem while writing to MySQL 4.X database using Write to Database action. What should I do?

This issue is related to MySQL authentication protocol built in MySQL 4.1 and above versions. This protocol is based on password hashing algorithm that is not compatible with the one used by older clients and stores passwords differentally as compared to older versions. Therefore, if you upgrade your server to 4.X and try to connect with the older client, you may end in a failure.

You can do one of the following options to solve this problem.

1. You can upgrade your all client programs to use 4.1.1 or newer client library.

2. Use pre-4.1 style password to use a pre-4.1 client program.

You can use SET PASSWORD statement and the OLD_PASSWORD() function to reset the password for user who wants to use pre-4.1 client program.

mysql> SET PASSWORD FOR-> ‘someuser_abc’@’somehost_xyz’ = OLD_PASSWORD(‘somenewpwd’);

You can also use UPDATE and FLUSH privileges to reset the password:

mysql> UPDATE mysql.user SET Password = OLD_PASSWORD(‘somenewpwd’) -> WHERE Host = ‘somehost_xyz’ AND User = ‘someuser_abc’;
mysql> FLUSH PRIVILEGES;

Specify the password you want to use for “somenewpwd”. You can not get your old password from MySQL, so select the new one.

3. Configure your server to use older password hashing algorithm:

a) Start mysqld with the –old-passwords option.
b) You can identify accounts those had updated their passwords to longer 4.1 format using the following query:

mysql> SELECT host, user, password FROM mysql.user -> WHERE LENGTH(Password) >16;

Now you can reset the password for records displayed by the query using the host and user values and assign a password using the OLD_PASSWORD() function. You can either use SET PASSWORD or UPDATE, as discussed above.

Records are not displayed on the Web when MYSQL was the underline database ?

Wednesday, November 19th, 2003

Records are not displayed on the Web when MYSQL was the underline database ?

Created 2003-11-19 by Tamsila-Q-Siddique

Records are not displayed on the web when I use MySQL ? Records aren’t displayed on the web when I use MySQL. The data isn’t retrieved even though the database is being populated and logs are being generated.

Note that MySQL under Linux requires "case sensitive" in the most cases. MySQL database tables and, field names should match with the ones defined in your "Data Base Logging Action". That’s why records aren’t displayed on the web when MySQL is the underline database. For doing this do the following:
* Ruleset – > Actions – > Data Base Logging.
* MySQL -> (Databases)… -> Database -> YourDataBase (where YourDatabase could be any name).
* Check that the table names and the field names defined in the Data Base Logging and YourDataBase should match exactly.

How to setup MonitorWare Products to use MySQL as database?

Friday, August 9th, 2002

How to setup MonitorWare Products to use MySQL as database?

Created on 2002-08-09 by Andre Lorbach.

To use a MySQL Database with WinSyslog, EventReporter or MonitorWare Agent, you need to install some components (If you haven’t) first. Go to http://www.mysql.com/downloads/index.html. If you don’t have any MySQL Server, download MySQL-3.23.5 for Windows for example (Or a newer version if there is one).

Most important, you need to download an install the ODBC Drivers (myodbc-2.50 for example) for MySql. This is needed, because WinSyslog will use a ODBC-Driver for MySQL to access the database.

Note: If you are upgrading from MonitorWare Agent 1.x to 2.x, you would need to creat the SystemEventsProperties table. If you are upgrading from 4.x to 5.x or higher you would need to create the SystemEventsProperties table. Similarly if you are upgrading from 5.x (or any other previous version) to 6.x or higher you would need to create the SystemEventsProperties table.

1. On your MySQL Server, create a new database. The script below shows you an example:

CREATE database MyWinSyslog;
CREATE table SystemEvents
(
ID int IDENTITY (1, 1) NOT NULL,
SystemID int,
ReceivedAt datetime,
DeviceReportedTime datetime,
Facility int,
Priority int,
FromHost nvarchar (60),
Message text,
NTSeverity int,
Importance int,
EventSource nvarchar (60),
EventUser nvarchar (60),
EventCategory int,
EventID int,
EventBinaryData text,
CurrUsage int,
MinUsage int,
MaxUsage int,
MaxAvailable int,
InfoUnitID int,
SysLogTag varchar(60),
EventLogType varchar(60),
GenericFileName VarChar(60),
Checksum int NULL,
CustomerID int
);

CREATE table SystemEventsProperties
(
ID int IDENTITY (1, 1) NOT NULL ,
SystemEventID int NULL ,
ParamName varchar (255) NULL ,
ParamValue text NULL
);

GRANT ALL MyWinSyslog.* To "database username@YourDNS or machine ip" IDENTIFIED BY"YourPassword";

2. After you install the ODBC components, you will need to add a new ODBC System DSN. See the screenshot below which shows you the first step of the Wizard:

3.The screenshot below shows how all values could be filled in. But this depends on your configuration and names:

4. Now configure a Database Logging action in WinSyslog. In the Screenshot below, I used the values I specified before.