Related articles and resources
- KB-LJW-DB-101 : Impact of ADO cursor types on an ODBC driver in ASP
- KB-LJW-DB-104 : Handle "DATE" datatypes with MySQL and MariaDB
MariaDB Drivers installed on our servers Classic ASP
For reasons of reliability and long-term license sustainability, the MariaDB database engine is installed on our Classic ASP shared hosting plans. The MySQL database engine remains available and installed on request on our dedicated servers, in version 8.x or 5.x.
The MariaDB engine can import and run your MySQL databases. The ODBC Drivers available on our shared servers to access your MariaDB databases are as follows:
- {MariaDB ODBC 3.2 Driver}
- {MySQL ODBC 5.3 Unicode Driver}
- {MySQL ODBC 3.51 Driver}
What is MariaDB
MariaDB is an open-source relational database management system, compatible with MySQL, from which it is derived. This engine is designed to store, query, and manipulate structured data using the SQL language. Born from a fork (source code split) of MySQL in April 2009, MariaDB aims to provide technical continuity: it uses the same fundamental concepts (tables, indexes, storage engines, transactions), the same network protocols, and very strong compatibility at the query and schema level.
MySQL was owned and sponsored by the Swedish company MySQL AB, which was acquired in February 2008 by Sun Microsystems, now Oracle Corporation. In 2009-2010, when Oracle acquired Sun, a fork (a separate branch) from the open-source MySQL project was created: MariaDB. This separate branch was created due to concerns about Oracle's product management and its commercial and pricing policy, which could evolve arbitrarily and threaten the free availability or even the very availability of MySQL in the medium term.
Both share the same engine and very strong technical compatibility, because MySQL and MariaDB databases can be converted from one engine to the other and operate in the same way, sometimes with minor adjustments. As a result, MariaDB is considered a drop-in replacement for MySQL.
MariaDB has established itself as the most dynamic and most regularly updated project. MariaDB has been adopted by more and more companies and products, such as Google, Mozilla, and Plesk, which has now integrated it as standard since 2022 as a replacement for MySQL, the latter no longer being installed by Plesk by default. The main advantages of MariaDB are its free and open-source nature, as well as the dynamism and regularity of its updates, innovations, and optimizations.
MariaDB is a solid and mature alternative widely adopted in production by many companies and players. Its community development, open governance, and pace of innovation make it a relevant choice for using an equivalent to MySQL, but without dependence on a single vendor (Oracle).
Importing MySQL databases to MariaDB
Because of the common root of the MySQL and MariaDB engines, data import is generally very simple. Make sure you have a native backup of your MySQL database, namely a backup created by the MySQL engine itself, or a reliable tool such as Plesk DUMP, NaviCat, or phpMyAdmin. Backups of your MySQL database made with less reliable software (e.g. EMS SQL Manager for MySQL) or older versions may prove less compatible, and may be more complex to import into MariaDB.
Our customers for example frequently perform imports of this type:
- From: MySQL 5.x (MyISAM storage engine) with "utf8_general_ci" encoding
- To: MariaDB 11.4 (InnoDB storage engine) with "utf8mb4_unicode_ci" encoding
Once your database has been converted to the MariaDB engine, we advise against restoring or importing old MySQL backups of your database, as this may cause data corruption. Such an import is possible with precautions and prior data checks. We can assist you if you want to import data from MySQL to MariaDB.
How to manage and administer MariaDB databases
The MariaDB databases available in your hosting plans provided on our servers are accessible in the same way as your MySQL databases, namely from the PHPMyAdmin interface provided from Plesk, and from any other compatible remote database management software.
Regarding this compatibility, the database management client software you use to manage your databases must be able to reliably support connections to a MariaDB 11.4+ server, the InnoDB storage engine, and the utf8mb4_unicode_ci encoding/collation. As examples, we recommend HeidiSQL (free), Navicat (paid), or DbVisualizer (paid), this list being non-exhaustive. Other programs exist depending on your needs and preferences, but make sure they are reliable and favor recent and up-to-date versions.
How to access MariaDB databases from Classic ASP
Connecting to your MariaDB databases from your Classic ASP site is done in the same way as you do with MySQL. Only the name of the ODBC Driver changes, and possibly the OPTION (Flags) that you indicate in your connection string.
Access to your MariaDB databases from your ASP pages via ADO must be done using the {MariaDB ODBC 3.2 Driver} or {MariaDB ODBC 3.1 Driver} Driver depending on the one installed on the Windows server.
Because the MariaDB and MySQL engines are close, you can even access your MariaDB databases via the {MySQL ODBC 3.51 Driver} Driver or the {MySQL ODBC 5.3 Unicode Driver}. However, this method should only be used in very specific cases and if you fully understand what you are doing. In general, it is recommended to use the ODBC Driver provided and intended for this purpose, {MariaDB ODBC 3.2 Driver}.
Establishing connections to other data sources, such as Microsoft Access or Excel, may include the Provider= parameter in the connection string. In the absence of this parameter, ADO uses the default provider for ODBC Drivers, namely MSDASQL (Microsoft OLE DB provider for ODBC).
When you establish a connection to your MariaDB database via an ODBC Driver from your ASP code, you establish a so-called "DSN-less" connection. In this case, you do not need to specify Provider=MSDASQL in your connection string: it is implicitly used by ADO. For this purpose, see our article about the impact of ADO cursor types on access to your MariaDB databases.
One exception to note: you will probably need to add Provider=MSDASQL; DSN=MonDSN; to your connection string if you establish your connection via a DSN.
Using a Driver designed for MySQL may in some cases allow you to avoid certain bugs that may occur if you access your Recordsets too permissively. Conversely, using a Driver designed for MySQL should not be necessary if you have coded your Classic ASP by following the best practices related to explicit declaration of the cursor type via the rs.CursorType property.
The following code shows you how to establish a DSN-less connection to your MariaDB database, and how to specify certain Flags in the OPTION parameter.
<%
'Declare MariaDB connection options/flags that may be useful with ADO + ASP Classic (non-exhaustive list)
'References & Lists (incomplete):
'https://mariadb.com/docs/connectors/mariadb-connector-odbc/mariadb-connector-odbc-guide#general-connection-parameters
'https://docs.skysql.com/Connecting%20to%20SkySQL%20DBs/Connect%20using%20ODBC/#options-bitmask
'Some connection options/flags exist and are inherited from the MySQL engine:
'https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-connection-parameters.html#codbc-dsn-option-flags
Dim CONST_DB_MARIADB__FOUND_ROWS : CONST_DB_MARIADB__FOUND_ROWS = 2
Dim CONST_DB_MARIADB__NO_PROMPT : CONST_DB_MARIADB__NO_PROMPT = 16
Dim CONST_DB_MARIADB__DYNAMIC_CURSOR : CONST_DB_MARIADB__DYNAMIC_CURSOR = 32
Dim CONST_DB_MARIADB__NO_SCHEMA : CONST_DB_MARIADB__NO_SCHEMA = 64
Dim CONST_DB_MARIADB__COMPRESSED_PROTO : CONST_DB_MARIADB__COMPRESSED_PROTO = 2048
Dim CONST_DB_MARIADB__NO_CACHE : CONST_DB_MARIADB__NO_CACHE = 1048576
Dim CONST_DB_MARIADB__FORWARD_CURSOR : CONST_DB_MARIADB__FORWARD_CURSOR = 2097152
Dim CONST_DB_MARIADB__MULTI_STATEMENTS : CONST_DB_MARIADB__MULTI_STATEMENTS = 67108864
'Declare the value (sum) of the options to be supplied to the MariaDB ODBC Driver
'(recommended list, to be customized according to your needs)
Dim dbConnOptions
dbConnOptions = CONST_DB_MARIADB__FOUND_ROWS + CONST_DB_MARIADB__NO_PROMPT
'Declare the database connection string via the MariaDB Driver
Dim dbConnString : dbConnString = "Driver={MariaDB ODBC 3.2 Driver}; Server=127.0.0.1; Charset=utf8; Port=3306; Database=dbName_abcdef; User=dbUser_test_abc123; Password=dbPw_abcdef12345; Option=" & dbConnOptions & ";"
'If you are using tables encoded in utf8mb4, add this to your connection string:
dbConnString = dbConnString & "InitStmt={SET NAMES 'utf8mb4';}"
''ALTERNATIVE: The collation type is usually already included in the table definition.
''However, you can force it for this connection if you wish:
'dbConnString = dbConnString & "InitStmt={SET NAMES 'utf8mb4' COLLATE utf8mb4_unicode_ci;}"
'Open the database connection
Set dbConn = Server.CreateObject("ADODB.Connection")
dbConn.Open dbConnString
%>You also have the INITSTMT parameter, which allows SQL statements to be executed when the connection starts. If you want to execute several SQL statements when your connection starts, you must protect these different SQL statements with braces {} in order to group them. Otherwise, the semicolons separating each SQL statement will be incorrectly interpreted by the ODBC parser. This is an important nuance because the semicolon is used as a separator by both the SQL parser and the ODBC parser! The correct form is therefore: InitStmt={SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE'; SET time_zone='+00:00'; SET NAMES utf8;}. You must have added the value of the MULTI_STATEMENTS Flag (67108864) to be able to execute several statements. Finally, note that some ODBC Drivers execute only the first SQL statement and ignore the following ones.
MyISAM and InnoDB storage engines
MySQL and MariaDB database servers support the two main engine types, MyISAM and InnoDB. However, for the reasons discussed below in the paragraphs to come, we recommend that you favor the InnoDB storage engine, and migrate the tables of your database from the MyISAM engine to the InnoDB engine. You can perform this conversion gradually, table by table. We are available to assist you with this operation if you wish.
The MyISAM storage engine is old and was historically used by default by MySQL. It is still supported by MySQL and MariaDB. For several years now, the InnoDB storage engine has become the standard, because of its greater reliability and better overall performance both in reading and writing. The InnoDB engine supports scaling much better in terms of data volume and traffic than the MyISAM engine, which can cause slowdowns when large-scale volumes are reached. One of the main reasons to use InnoDB rather than MyISAM is the absence of full table-level locking, which helps speed up query processing.
The main differences between InnoDB and MyISAM are as follows:
- InnoDB has granular locking. MyISAM only has full locking at the entire table level.
- InnoDB has what is called referential integrity, which implies support for foreign keys (RDBMS) and relational constraints, which MyISAM does not do (DBMS).
- InnoDB supports transactions, which means you can commit or roll them back. This is not the case with MyISAM, which de facto applies every query.
- InnoDB is more reliable because it uses transaction logs for automatic recovery, which is not implemented in MyISAM.
Encoding and collation
Character encoding (charset)
In the MariaDB engine, encoding (charset / character set) defines how characters are represented in memory and stored on disk. Encoding answers the question: “which symbols can I store and how are they encoded as bytes?”. For example, the utf8mb4 encoding can represent the entire Unicode character set (including emojis), while the latin1 encoding can store a much more limited number of characters.
Character collation
Collation does not concern character storage itself, but the way character strings are compared and sorted. It defines linguistic rules: case sensitivity, accent sensitivity, alphabetical order, and equivalences between characters. In summary, encoding describes what can be stored, and collation indicates how this stored data is compared. The reference collation for the utf8mb4 encoding is the utf8mb4_unicode_ci collation.
Several collations exist for one and the same encoding (for example utf8mb4_general_ci, utf8mb4_unicode_ci, utf8mb4_bin), and its choice will impact how your data is compared, sorted, and classified. If necessary, the collation type can be forced when executing your SQL query via COLLATE, as shown in this example:
SELECT * FROM myTable ORDER BY myTable.champ COLLATE utf8mb4_unicode_ci;Relationships between encoding and collation
Encoding and collation are closely linked, because each collation is defined for a given encoding. You therefore cannot apply a utf8mb4_* collation to a column whose character set is defined as latin1. MariaDB therefore always uses the encoding–collation pair to handle text. In practice, this has direct consequences: an ORDER BY clause, a GROUP BY, or a comparison (= , LIKE) will depend on the collation, while corrupted character issues (� symbols) almost always stem from incorrect encoding.
It may sometimes be tempting to modify the collation to “fix” a display problem, but this very rarely works. The sound rule is simple: choose a modern and universal encoding (we recommend utf8mb4), then select a collation suited to the expected business behavior (strict comparison, case-insensitive, linguistic compliance, etc.) (we recommend utf8mb4_unicode_ci).
Official MariaDB documentation regarding available charsets and collations:
- Overview and general information about charsets and collations on MariaDB
- List of available charsets and collations on MariaDB
Converting the encoding to "utf8mb4_unicode_ci"
This section presents the benefits of migrating tables with a utf8_general_ci or latin1_swedish_ci encoding to utf8mb4_unicode_ci.
First of all, be aware that MariaDB supports all charsets and collations historically used by MySQL. You are therefore under no obligation to perform this migration. However, if your tables are intended to contain data with accented characters, information entered by users, or advanced characters (such as Emojis), then reading this section will probably be the best decision you have made today!
UTF-8 is an encoding method that translates any Unicode-based character into a string. UTF-8 is one of the most frequently used encoding systems for Unicode. UTF means “Unicode Translation Format”: its role is to translate Unicode characters into corresponding binary strings and vice versa.
The utf8mb4 encoding offers full Unicode support and supports a wider range of characters, making it possible to use symbols requiring up to 4 bytes per character. This character range includes new Emojis, smileys such as ☺, 😍, 😐, etc.
However, contrary to its name, the utf8 encoding in MySQL does not comply with the UTF-8 standard, because it does not support the correct number of bytes per character. MySQL corrected this error only from MySQL 8 onward, now using the utf8mb4 character set by default instead of the incomplete utf8, used until then in previous versions, and in particular MySQL 5.x. As a result, to ensure complete and correct support for the UTF-8 standard, it is advisable to use the utf8mb4 character encoding.
The latin1_swedish_ci encoding was, for its part, the default collation of MySQL 5.x (before MySQL 8) for the latin1 (ISO-8859-1) encoding, historically chosen for reasons of simplicity and performance, with no real connection to the Swedish language. It was long accepted and used by inertia, but it handles non-Western languages poorly, complex accented characters, and any modern Unicode compatibility, which caused countless silent encoding errors. Corrupted data stored with the wrong encoding in your tables is very difficult to recover. In its time, it was fast, lightweight, and sufficient for simple Western applications. Today it is deprecated and discouraged, in favor of migration to utf8mb4.
MariaDB, for its part, is more standards-compliant because it is more recent. It natively manages the utf8mb4 encoding reliably. The incomplete handling of UTF-8 in MySQL 5.x makes it impossible to store characters requiring 3 or 4 bytes included in the UTF-8 standard, such as Emojis, now commonly used. Attempting to insert such characters into a table using utf8 (not using utf8mb4) causes runtime errors such as: Error 1366 (HY000): Incorrect string value: « \x77\xD0 » for column "column_name" at row 1. This is the main reason why some data displays incorrectly for certain MySQL users using this historical utf8 encoding.
It is useful to note that two types of encoding are available for utf8mb4:
- utf8mb4_unicode_ci, which is perfectly aligned with the actual UTF-8 standard, and is therefore recommended and used by default by MariaDB and recent versions of MySQL (8.x)
- utf8mb4_general_ci, which is intended for general use cases. Although this character set is called utf8mb4, it is also implemented incompletely in MySQL. It is a partial version of utf8mb4 designed to take shortcuts in order to improve database performance.
Special cases with MariaDB
Because MariaDB is more recent than MySQL, it adheres more closely to the ODBC 3.8 standard, and proves less permissive in some very specific cases.
In the vast majority of cases, importing your existing MySQL databases will work immediately on the MariaDB engine, often with performance gains.
Some very specific cases may occur: consult our knowledge base, or contact our team for assistance if you encounter any difficulty.
Managing DATE-type data
You may encounter a few special cases concerning the management of Date-type fields, notably with empty values, or if you want to store them in a format different from the ISO standard YYYY-MM-DD HH:MM:SS.
Refer to the tips and best practices in our article KB-LJW-DB-104 on this topic.
Default cursor
The {MariaDB ODBC 3.2 Driver} Driver uses a Forward-Only cursor (SQL_CURSOR_FORWARD_ONLY) by default if the cursor type is not provided in the OPTION of your connection string. This differs from the behavior previously observed with the {MariaDB ODBC 3.1 Driver} Driver, which used a static cursor (SQL_CURSOR_STATIC) as the default cursor.
ADO never hands control to the default cursor of the ODBC Driver, and specifies at least 0=adOpenForwardOnly. Therefore, this change at the ODBC Driver level should in practice have no impact on your ASP code.
Refer to our article KB-LJW-DB-101 on this topic.
We help you efficiently with IIS and your Classic ASP code.
Just get in touch with our team
