0

In somewhat related questions I notice the discusions of Data Engines. I have some tables set to MyISAM and some set to InnoDB but not sure if this could be causing the problem.

I used to connect to my old database like this (using MySQL ODBC 5.1 Driver)

Connection_STRING = "PROVIDER=MSDASQL;" & _
                    "DRIVER={MySQL ODBC 5.1 Driver};" & _
                    "port=3306;" & _
                    "SERVER=localhost;" & _
                    "UID=user;" & _
                    "PWD=password;" & _
                    "database=database;" & _
                    "Option=3"

Now I am using the MySQL ODBC 5.3 ANSI Driver, and connect like this:

Connection_STRING = "PROVIDER=MSDASQL;" & _
                    "DRIVER={MySQL ODBC 5.3 ANSI Driver};" & _
                    "port=3306;" & _
                    "SERVER=localhost;" & _
                    "UID=user;" & _
                    "PWD=password;" & _
                    "database=database;" & _
                    "Option=3"

I also tried the MySQL ODBC 5.3 Unicode Driver, but no matter what, these Drivers are causing a problems, or so I think.

Windows Logs Application Warnings from MariaDB:

Aborted connection 850 to db: 'database' user: 'user' host: 'localhost' (Got an error reading communication packets)

Countless (C0000005) Errors

Error: File /home.asp Unexpected error. A trappable error (C0000005) occurred in an external object. The script cannot continue running..

Windows Logs System Warning:

A process serving application pool 'my_pool' suffered a fatal communication error with the Windows Process Activation Service. The process id was '5896'. The data field contains the error number.

My MariaDB my.ini config file:

[mysqld]
datadir=C:/Program Files/MariaDB 10.6/data
plugin-dir=C:/Program Files/MariaDB 10.6/lib/plugin
port=3306
sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
default_storage_engine=InnoDB
innodb_buffer_pool_size=2G  
innodb_log_file_size=50M
character-set-server=utf8
max_connections = 600

# https://mariadb.com/kb/en/mariadb/server-system-variables/#socket
socket = MySQL

# https://mariadb.com/kb/en/mariadb/server-system-variables/#skip_external_locking
skip-external-locking

# https://mariadb.com/kb/en/mariadb/optimizing-key_buffer_size/
key_buffer_size = 384M

# https://mariadb.com/kb/en/mariadb/server-system-variables/#max_allowed_packet
max_allowed_packet = 128M

# https://mariadb.com/kb/en/mariadb/server-system-variables/#table_open_cache
table_open_cache = 1024M

# https://mariadb.com/kb/en/mariadb/server-system-variables/#sort_buffer_size
sort_buffer_size = 2M

# https://mariadb.com/kb/en/mariadb/server-system-variables/#read_buffer_size
read_buffer_size = 2M

# https://mariadb.com/kb/en/mariadb/server-system-variables/#read_rnd_buffer_size
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M

# https://mathiasbynens.be/notes/mysql-utf8mb4
character-set-client-handshake = false 
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci

[mysql]
default-character-set = utf8mb4

[client]
port=3306
socket = MySQL
default-character-set = utf8mb4

[mysqldump]
quick
max_allowed_packet = 1024M

I also tried adding LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0; to the Connection String; tried also connecting via a System DSN connection configured in Microsoft OBDC Administrator tool, but nothing worked. The application pool keeps restarting; and the website has lots of new errors, because of wrong returns. For instance this code used to work by returning a record. Now there is no record:

Dim rsOrders__MMColParam
rsOrders__MMColParam = "1"
If (Request.QueryString("orderID") <> "") Then
  rsOrders__MMColParam = Request.QueryString("orderID")
End If
Dim rsOrders
Dim rsOrders_cmd
Dim rsOrders_numRows
Set rsOrders_cmd = Server.CreateObject ("ADODB.Command")
rsOrders_cmd.ActiveConnection = Connection_STRING 
rsOrders_cmd.CommandText = "SELECT * FROM orders WHERE orderID = ?"
rsOrders_cmd.Prepared = true
rsOrders_cmd.Parameters.Append rsOrders_cmd.CreateParameter("param1", 5, 1, -1, sOrders__MMColParam) 
Set rsOrders = rsOrders_cmd.Execute

I had to change it to this to get it to work with the new connection string:

Dim rsOrders__MMColParam
rsOrders__MMColParam = "1"
If isNumeric(Request.QueryString("orderID")) Then
  rsOrders__MMColParam = Cint(Request.QueryString("orderID"))
End If
Dim rsOrders
Dim rsOrders_cmd
Dim rsOrders_numRows
Set rsOrders_cmd = Server.CreateObject ("ADODB.Command")
rsOrders_cmd.ActiveConnection = MM_Connection01_STRING
rsOrders_cmd.CommandText = "SELECT * FROM orders WHERE orderID = " & rsOrders__MMColParam
rsOrders_cmd.Prepared = true
Set rsOrders = rsOrders_cmd.Execute

What could be going on? Sorry for making it so long.

3
  • Is "provider" a valid parameter? dev.mysql.com/doc/connector-odbc/en/… Nov 24 at 1:26
  • "this code used to work" with a non-existent parameter "sOrders__MMColParam" ? The new code "rsOrders_cmd.Prepared = true" but no parameters? Nov 24 at 1:30
  • @GerardH.Pille I was fearing having to re-code a bunch; but was glad to have found that Maria ODBC 3.1.15 worked.
    – MeSo2
    Nov 24 at 2:08
0

I got my site back by using the Maria ODBC 3.1.15 connector

I am using default settings and all is good.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.