ODBC 5.1 Driver

Snitz™ Forums 2000
https://forum.snitz.com/forumTopic/Posts/71242?pagenum=1
04 November 2025, 21:12

Topic

Webbo
ODBC 5.1 Driver
31 October 2017, 08:40


I'm in the process of moving my MySQL databases over to a new server and the Snitz database is proving problematic and I'm getting a HTTP:500 error after pointing the connection string to the new server's IP

The host has said..
it looks like because your previous MYSQL version was 5.1 and the new one is 5.5 that the connection information you are using is not supported

Is there an easy fix for this editing the current connection string or is it a case of loading the ODBC 5.1 driver into the new server?
Here is my current connection string (important stuff edited out)

Code:
strConnString = "driver={MySQL ODBC 5.1 Driver};option=16387;server=000.000.00.000;user=edit;password=edit;DATABASE=edit;" '##MySQL w/ MyODBC v5.1

Thanks

 

Replies ...

HuwR
31 October 2017, 17:40


first thing you need to ask them is what odbc version do they have available, AFAIK 5.5 is the MySQL version not the ODBC version, I think the latest ODBC is 5.3

ODBC 5.1 should connect to MySQL 5.5 so it is possible they are using another later odbc version
Webbo
01 November 2017, 07:14


Do you ever get the feeling that you're going around in circles when talking to host tech support?

After asking the question twice now and them looking into the connection issue for seemingly hours, they have come back with ..
Thank you for your patience, I have looked into your query and the new VPS currently uses MariaDB ->

+-------------------------+---------------------+
| Variable_name | Value |
+-------------------------+---------------------+
| innodb_version | 5.5.52-MariaDB-38.3 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.5.56-MariaDB |
| version_comment | MariaDB Server |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+---------------------+

We wouldn't be able to install the depreciated ODBC driver 5.1 however we may be able to install the MariaDB driver ->

Yet MySQL ODBC 5.1 is stil available to use: https://dev.mysql.com/downloads/connector/odbc/5.1.html

Am I right in thinking that the following connection string willwork with the MariaDB ODBC 3.0 driver (if it is installed)...
Code:
strConnString = "driver={MariaDB ODBC 3.0 Driver};option=16387;server=00.00.00.00;user=edit;password=edit;DATABASE=db_name;" '##MySQL w/ MariaDB ODBC 3.0 Driver

Thanks

Edit: just corrected a typo
HuwR
01 November 2017, 07:48


params are slightly different, I believe the following should work

"Driver={MariaDB ODBC 3.0 Driver};Server=localhost;UID=odbc_user;PWD=odbc_pw;DB=odbc_test;Port=3306" (port is optional I believe)
Webbo
01 November 2017, 08:15


Thanks Huw, I've just corrected a typo in the string (2.0 should have read 3.0) but it still doesn't work which suggests that the MariaDB driver isn't installed or is incorrectly installed

Running setup.asp it is returning the following error:
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

I've put it back in the hands of our hosts so will await their response.

(The forums are still working ok as they're still running off the existing servers not the new one we're trying to move over to)
Webbo
01 November 2017, 20:31


This is still ongoing, I have managed to get a connection string to connect to a test database on the new server but when trying to create tables it manages to create 8 tables and then errors out.
Using the same connection string to an existing db on the new server results in the setup.asp page saying that the database needs to be installed (it already is)

Is Snitz forum compatible with MySQL Server 5.5 ?
HuwR
01 November 2017, 22:06


yes, I have a local copy running against MySQL 5.5
I use the following connection string (I have odbc 5.3 installed)
strConnString = "driver={MySQL ODBC 5.3 ANSI Driver};option=16387;server=XXXXX;user=XXX;password=XXX;DATABASE=snitz2000;"
Webbo
01 November 2017, 22:26


Just on the off chance I just tried that and got the following error:

New Server:
+-------------------------+---------------------+
| Variable_name | Value |
+-------------------------+---------------------+
| innodb_version | 5.5.52-MariaDB-38.3 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.5.56-MariaDB |
| version_comment | MariaDB Server |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+---------------------+
7 rows in set (0.00 sec)

Old Server

+-------------------------+---------------------+
| Variable_name | Value |
+-------------------------+---------------------+
| protocol_version | 10 |
| version | 5.1.73-log |
| version_comment | Source distribution |
| version_compile_machine | x86_64 |
| version_compile_os | redhat-linux-gnu |
+-------------------------+---------------------+
5 rows in set (0.00 sec)

with a suggestion that I may have to consider re writing the connection script

Any thoughts would be appreciated where to go on this

Regards,
HuwR
02 November 2017, 14:08


As far as I can tell, they need to install an odbc driver for MariaDB (NOT MySQL) https://downloads.mariadb.org/connector-odbc/ You should then be able to connect the forum to the database.
It is not the database version that is the issue here, classic asp does not connect directly to the server it must use a connector (in this case ODBC). Ask your host if they have installed a MariaDB ODBC driver on your webserver because without one you won't be able to connect the forum to the new database.
Webbo
02 November 2017, 14:17


Just to rule out any coding issues I've tried to install a fresh version of Snitz on the server with an new empty MySQL db and despite trying multiple connection strings I can't get past the HTTP500 error. I've asked the host to install ODBC 5.3 to try the above connection string, or cancel the server order as I've lot too much time on this as it is
Webbo
02 November 2017, 14:19


Post overlap

Will do Huw
HuwR
02 November 2017, 18:44


Originally posted by Webbo
Post overlap

Will do Huw
thumbsUp
Webbo
02 November 2017, 20:54


Well I asked the question, quoting your reply here Huw, and received the following message back, eventually..
Unfortunately, we are unable to install additional software onto the managed VPS platform and can only use what software components already exist on the system. An un-managed VPS may be more suitable as you will have full root access to the system and you can install any components you wish to suit your needs.

Part of my response was "I fail to see why you cannot install a driver if it resolves this issue, please explain" , I won't quote the rest - am I missing something angry question
HuwR
03 November 2017, 07:43


I totally agree, if they are provinding the Maria DB for your use then they really should be providing a means to connect to it
Webbo
10 November 2017, 17:27


Well it's still not connecting and I'm lost here smile
The databases are in place on the VPS and I've been told several times that a MySQL ODBC Connector is installed and it is doing the same job as the requested MariaDB ODBC Connector that Huw suggested should be used.
Yet despite all this and using countless variations of connection strings I still cannot get the Snitz Forum to read the database tables.
Apparently there is MySQL 3.51 driver available on the VPS and using that in a connection string and running setup.asp brings up the following...
I've double checked everything and the ODBC driver is installer and external MySQL connections are enabled.
It's in the MySQL configuration file (/etc/my.cnf) ->

#bind-address = ::ffff:127.0.0.1
bind-address=0.0.0.0

Enabled in Plesk ->

====
Tools & Settings > Database Servers > Settings

"Allow remote connections from any host" is selected. ====

And we can log in externally from another server with that MySQL user ("myUID") to that database, and see tables and data there ->

=====
$ mysql -h server_IP -u myUID -p myPWD
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 90676
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql [db_name]> show tables;
+--------------------------+
| Tables_in_db_name |
+--------------------------+
| list of tables
+--------------------------+
80 rows in set (0.00 sec)

mysql [db_name]>
=====


I'm afraid I don't know why that forum software has that issue, because server side here everything looks fine.
Are you able to speak to the developers of the software for any guidance or tips why this might be occurring? They may have come across this before.


So any suggestions would be appreciated, and Huw if you would like to look at the full transcript of the support ticket let me know and I'll arrange temporary access

Thanks,
HuwR
11 November 2017, 10:35


If the drivers are installed then I can't think of any reason why it wouldn't connect, but then I have never tried to connect to mariadb only MySQL. however just because they can connect to MySQL from another server does not prove that you can connect using the odbc driver, have they tried connecting using ODBC?
Webbo
11 November 2017, 13:46


I'll ask the question Huw
Webbo
11 November 2017, 17:58


They have come back to me with the following:

There has been an error !!

The database could not be opened !! Check your config.asp file and set the
strConnString so it points to the database. Also check if strDBType is set to the right databasetype.


Code : 80004005

Error Description :
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified



Is ...
strDBType = "mysql"

compatible with MariaDB , ie could this be the issue ?
Webbo
11 November 2017, 21:33


If I change the connection string to:

strConnString = "Driver={MySQL ODBC 5.1 Driver};Server=IPAddress;
UID=odbc_user;PWD=odbc_pw;DB=odbc_test;Port=3306,option=3;"

(without line breaks)

it returns:

The database needs to be installed !!

You need to create all the tables in the database before you can start using the forum.

Click here to create the tables in the database.
Click here to retry.

Yet the database and tables, etc are on the server


Likewise with...
strConnString = "driver={MySQL ODBC 5.1 Driver};option=16387;server=IPAddress;user=odbd_user;
password=odbc_pwd;DATABASE=obdb_test;Port=3306;" '##MySQL w/ MyODBC v5.1

(without line breaks)
Webbo
11 November 2017, 23:44


I found an article on the net that said that migrating MySQL db to MariaDB could create binary incompatibilities and suggested migrating data by dumping it and importing it in the new database.
So having created a dump I'm giving that a try assuming the file will upload and not time out due to the size
Webbo
12 November 2017, 00:36


Well that didn't work either.
Using {MySQL ODBC 3.51 Driver} or {MySQL ODBC 5.1 Driver} in the connection string returns...

The database needs to be installed !!

You need to create all the tables in the database before you can start using the forum.

Click here to create the tables in the database.
Click here to retry.

And when I click to create the tables in an already created database the one thing I noticed was there was a connection and 8 tables were created before the script displayed a load of errors similar to:

Error: -2147467259 [MySQL][ODBC 5.1 Driver][mysqld-5.5.56-MariaDB] Invalid default value for 'CAT_ID'


When viewing the tables in the database the existing tables are all in lower case, the new ones that were duplicated were in upper case.
Does it make a difference if the original tables are in lower case in the database ?
Offline now as I'm knackered dead
Webbo
12 November 2017, 08:14


Okay, donations in lieu of 'I'm a Genius' please forward to...


SOLVED IT

The issue was multiple,

Firstly the migration process from MySQL to MariaDB didn't work as expected so the resolve was to dump the database from the old server and import it into the new.
Then, for some reason Snitz software, MariaDB or the Linux OS did not like the fact that the table names were in lowercase so running multiple SQL commands to convert them from lowercase to upper case seemed to do the trick

The connection string I used is...
strConnString = "driver={MySQL ODBC 5.1 Driver};option=16387;server=IPAddress;user=odbd_user;
password=odbc_pwd;DATABASE=obdb_test;Port=3306;" '##MySQL w/ MyODBC v5.1



All other databases that were migrated with the exception of one and the Snitz db worked first time, so perhaps it is something to do with the code within both softwares making the db tables case sensitive, possibly
Webbo
12 November 2017, 21:04


Well I spoke too soon, not so much with the above but regarding it all working ok, for some reason topic.asp post.asp post_info.asp threw up HTTP500 errors but these files are modified from the originals. Loading the originals fixed the error but then caused problems with various mods installed including our site supporters... not good smile

Two ways around it I think, one is to go through a batch of fresh files, ie a new install, and add mods as required until I find out which one(s) don't work - very time consuming
OR, see if it's possible to get my hosts to modify the OS system files so that the Linux version accepts lower and upper case in MySQL. If that's possible then I can re-import a dump of the database and see if it works without further modification - the easiest route I think

Any thoughts?
HuwR
13 November 2017, 10:03


by default the snitz code uses UPPERCASE table names when creating the database and in all it's queries, if yours were lower case then they were possibly created outside of the snitz setup code, plus your previous MySQL db must have been set to be case insensitive otherwise it would not have worked.
It my be laborious but you are probably best fixing whatever code/tables needs to be fixed to get the queries to work
Webbo
13 November 2017, 21:16


Our Snitz was first installed about 15/16 years ago Huw and over time it has been upgraded and modified.
The hosts are adamant they will not/can not make it case insensitive for what ever reasons and to be honest I don't have the time to go through the files to see what's what or not, I looked through topic.asp yesterday, all 1500+ lines within ours, and found a couple of bits but not sufficient to resolve the problem.
Likewise to start with a fresh copy of Snitz then add the mods and changes one at a time to find out which or what is causing the issue is again a big task and one I don't have time for at present.

Bearing in mind the future I'm looking at other hosting options as I think the present ones will not be the best option
Webbo
21 November 2017, 22:17


Just an update - I'm in the process of moving hosts smile
© 2000-2021 Snitz™ Communications