sql error

[Dwintersun]

Warning: odbc_connect(): SQL error: [MySQL][ODBC 3.51 Driver]Can't connect to MySQL server on 'localhost' (10061), SQL state S1T00 in SQLConnect in c:\program files\firstspot\cfgmgr\user_status.php on line 59
connection failed!
is the message with updating SQL driver 3.51

[alan]

Please make sure:

1) You are using the version of MySQL and ODBC specified in readme.rtf

2) You follow the instruction in chapter 8 of firstspot_guide.pdf. Note that we only support File DSN created within ODBC Data Source Administrator.
_________________
~ Patronsoft Limited ~

[sdwedemeyer]

There is a problem with your answer. The firstspot guide example shows the odbc driver version as 3.51.06. The readme.rtf specifies 3.51.04. The problem is that the first windows driver that was released for this series was 3.51.07. So what version of the driver are we really supposed to use?
_________________
Stephen D Wedemeyer

[sdwedemeyer]

This gets even better - MySQL 4.0 is no longer in the Archives at the MySQL web site. That means that FirstSpot no longer is compatible with MYSQL - a major problem for us as we had intended to to use MySQL when we planned for this solution.

As mentioned above, the earliest ODBC driver is 3.51.07 - and that is from 2004. And the MySQL 4.1.1 archive is 2003. So requiring MySQL 4.0 is requiring software that is more than 4 years old.

Since the only compatible version of MySQL is no longer available, you need to either update the software or remove the support comments for MySQL.

As for Microsoft SQL 2000 - also quite old, but also an expenses, we are left with no choices except the included DBase - software I was developing with in the 80's!!!!!!!! The tools and utilities for manipulating DBase tables is very limited. We need to generate reports that are not available in your code.

I have one site on line and 3 more that we were about to purchase licenses for but needed to workout the database issue before doing so. Now everything is on hold unless I can find a MySQL 4.0 for Windows and then I must be able to use the ODBS driver 3.51.07.
_________________
Stephen D Wedemeyer

[alan]

You can download ODBC 3.51.04 at http://downloads.mysql.com/archives/myodbc-3.51/MyODBC-3.51.04.exe .

You should be able to use MySQL 4.1.x with FirstSpot. Just note the password hashing problem stated in http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html .

Part of the difficulties for MySQL support is compatibility between different MySQL versions is a bit shaky. The reason we use an old version of ODBC is due to a bug in File DSN support. We will definitely improve the certification process in the future though.
_________________
~ Patronsoft Limited ~

[sdwedemeyer]

Thanks for the link to 3.51.04 - it was not labeled as the windows package on the mysql site.

Your readme.rtf file specifically says not to use 4.1

"MySQL 4.0.x using ODBC 3.51.04 (MySQL 4.1.x is not supported),"

Meanwhile, I have found mysql 4.0.27 and loaded it with 3.51.07, setup the tables and configured firstspot to use it. Other than checking that firstspot was able to access the file, I have not tested the configuration.

You are going to have to elaborate on the 4.1 password issue. FirstSpot would be the client - but a client of what version? And FirstSpot uses PHP, but again, what version. You cannot make the statement you made - "You should be able" and "Just note the ...problem". Either you support the config or you don't. If you do, then what configuration of the password should be used.
_________________
Stephen D Wedemeyer

[alan]

In that case, we still recommend you to use 4.0.x since we did most of the testing using this MySQL version.

Regarding the 4.1.x password problem, it is due to MySQL change the password hashing algorithm starting from 4.1.x. Note that this is the password for MySQL user (not FirstSpot user). I am not sure about your question : "but a client of what version?" FirstSpot includes all the PHP files within the setup, and only the ODBC driver and MySQL requires you to run another setup and thus needs to match the version. Architecturally, FirstSpot acts as "client" which access the MySQL "server". So FirstSpot will needs to authenticate against a MySQL user in order to gain access.
_________________
~ Patronsoft Limited ~

[sdwedemeyer]

You do not understand my question because you have not read the article. The FirstSpot software acts as a client to the MySQL server. Quote:

"The password hashing mechanism was updated in MySQL 4.1 to provide better security and to reduce the risk of passwords being intercepted. However, this new mechanism is understood only by MySQL 4.1 (and newer) servers and clients, which can result in some compatibility problems. A 4.1 or newer client can connect to a pre-4.1 server, because the client understands both the old and new password hashing mechanisms. However, a pre-4.1 client that attempts to connect to a 4.1 or newer server may run into difficulties."

Since you state that all testing was done with 4.0 - there is a good chance that FirstSpot is considered a 4.0, or earlier, client.

However, the article does goes on to speak of work arounds and options which is why I asked you to elaborate and explain what must be done to ensure the configuration would work.

I still do not understand why you chose to only support an obsolete version of MySQL. Your answer on this point is weak. It is clear that development on this product stopped a long time ago. Don't get me wrong, the software as is works, but there is room for improvement and enhancements are needed to stay competitive - but that would be another thread. My concern is whether any work is being done to further this product's future.
_________________
Stephen D Wedemeyer

[alan]

I can assure you that the future of FirstSpot is alive and well. In fact, we had been working with v5 immediately after v4 final was released. We just don't want to talk about v5 until it is almost ready ;-)

To give you more background on our MySQL support:

1) We haven't change MySQL support version from FirstSpot v3 to v4, so it is natural that you feel that the MySQL support is out-dated. At least at the time of v4 release (around end of 2005), we felt that it was okay (though not ideal) to keep supporting ODBC 3.51.04 and MySQL 4.0.x. The other reason of course is due to MySQL 4.1.x change in password hashing algorithm I mentioned earlier.

2) Recently, we re-evaluate the MySQL issue. You might wonder why don't we upgrade everything (i.e. ODBC and MySQL). The reason is that the latest ODBC 3.51.x driver has problem with File DSN which FirstSpot relies on. For some strange reason, the latest ODBC 3.51.x only works with System DSN, even though there is no official preference statement from Microsoft regarding this issue. At this moment, we are hesitate to switch to System DSN since MySQL keep promising that this problem will be fixed. In fact, officially they claim that the latest ODBC 3.51.x driver will work with File DSN ( http://bugs.mysql.com/bug.php?id=12019 , http://bugs.mysql.com/bug.php?id=23428 )!

3) So we are back to square one. Upon further investigation, it seems that the only major difference between MySQL 4.0.x and 4.1.x is the password hashing algorithm change. Note that this MySQL password is only used when FirstSpot initializes an ODBC connection. We did some testing using MySQL 4.1.x with FirstSpot (with the password hashing workaround) and it looks like it works okay.

4) Actually, I did read the above paragraph, though I didn't completely finish the article and should elaborate more. Here are the steps on using the password hashing workaround:
i) create a MySQL user and use this user to create the FirstSpot tables
ii) use the workaround stated in the article to change MySQL user password back to MySQL 4.0.x format (so that it will be compatible with ODBC 3.51.04)
iii) use Windows ODBC Data Source Administrator to create File DSN (chapter 8 of firstspot_guide.pdf)

Hope this helps.

BTW, MS SQL 2005 was released in Nov 2005 so we didn't have enough time to complete the certification test for FirstSpot v4.
_________________
~ Patronsoft Limited ~

[sdwedemeyer]

That is much better, thanks. I will give that a try.

Thanks Alan.
_________________
Stephen D Wedemeyer