| T O P I C R E V I E W |
| Webbo |
Posted - 31 October 2017 : 08:40:38
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..
quote:
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)
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 |
| 15 L A T E S T R E P L I E S (Newest First) |
| Webbo |
Posted - 21 November 2017 : 22:17:57
Just an update - I'm in the process of moving hosts  |
| Webbo |
Posted - 13 November 2017 : 21:16:05
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 |
| HuwR |
Posted - 13 November 2017 : 10:03:26
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 |
Posted - 12 November 2017 : 21:04:08
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
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? |
| Webbo |
Posted - 12 November 2017 : 08:14:26
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 |
Posted - 12 November 2017 : 00:36:38
Well that didn't work either.
Using {MySQL ODBC 3.51 Driver} or {MySQL ODBC 5.1 Driver} in the connection string returns...
quote:
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 
|
| Webbo |
Posted - 11 November 2017 : 23:44:10
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 |
Posted - 11 November 2017 : 21:33:06
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:
quote:
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 |
Posted - 11 November 2017 : 17:58:44
They have come back to me with the following:
quote:
Thanks for your response,
I have tested this with an ODBC driver, obtained at:
https://downloads.mariadb.org/connector-odbc/3.0.2/
This was able to establish a successful database connection to the server.
When I change the connection string to:
strConnString = "Driver={MariaDB ODBC 3.0 Driver};Server=IPAddress;
UID=odbc_user;PWD=odbc_pw;DB=odbc_test;Port=3306;option=3;"
(without line breaks)
The forum appears to connect to the server that the database is on but when running setup.asp I get the following error:
quote:
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 |
Posted - 11 November 2017 : 13:46:51
I'll ask the question Huw |
| HuwR |
Posted - 11 November 2017 : 10:35:28
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 |
Posted - 10 November 2017 : 17:27:03
Well it's still not connecting and I'm lost here
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...
quote:
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 is there complete with all the tables, etc.
After several days and hours with this issue one message that came back from the tech support was....
quote:
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 |
Posted - 03 November 2017 : 07:43:21
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 |
Posted - 02 November 2017 : 20:54:07
Well I asked the question, quoting your reply here Huw, and received the following message back, eventually..
quote:
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   |
| HuwR |
Posted - 02 November 2017 : 18:44:48
quote:
Originally posted by Webbo
Post overlap
Will do Huw
[)8] |
