MySQL/MariaDB connection in asp code encodes wrongly charset utf8 at insert or update in data base

mariadb-10.1MySQLutf-8vba

I can't get a simple update or insert to save special äöü characters. Instead they are being stored as Ã¤Ã¶Ã¼.

The website displays äöü characters correctly if they are already stored as äöü in the database using phpMyAdmin (or some other tool like HeidiSQL), but for some reason I can't use my code/connection to the database to store them properly. This is where I stand:

I added charset=utf8 to my connection string:

MM_Connection_STRING = "PROVIDER=MSDASQL;DRIVER={MySQL ODBC 5.1 Driver};port=3306;SERVER=localhost;UID=xxx;PWD=xxx;database=xxx;Option=3;charset=utf8"

I added accept-charset="UTF-8" to all my forms.

I am running MariaDB and IIS 10.

Server version:         10.1.13-MariaDB mariadb.org binary distribution
Protocol version:       10

The page gets proxied using Apache 2.4. I am running asp (classic) code.

Calling the MySQL server for character and collation, I get this:

MariaDB> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR
  Variable_name LIKE 'collation%';

+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_general_ci |
| collation_database       | utf8mb4_general_ci |
| collation_server         | utf8mb4_general_ci |
+--------------------------+--------------------+

The code worked when I was using iso-8859-1 as charset.

Any help will be very much appropriated. Thank you.

Best Answer

I had <%@language="VBSCRIPT" CODEPAGE="1252" LCID=1033%> and needed <%@language="VBSCRIPT" CODEPAGE="65001" LCID=1033%> for it to work. Also, after doing this I needed to drop charset=utf8 from the connection string.

UPDATE 2013-04-15 18:04 EDT

I just noticed you have innodb_file_per_table OFF. What gives ?

You currently have all the InnoDB data and the corresponding index sitting in a single file.
Any CREATE TABLE statement must make data dictionary updates and look for space (small but annoying in this instance)
Internal Fragmentation of ibdata1
Dropping a table means scanning the table and its indexes for availability to lock. With data and index pages possibly fragmented, this takes spindles, seek time, and latency.
See Pictorial Representation of ibdata1 to see everything that goes into ibdata1

Recommendation : Remove all Data and Index Pages from ibdata1

This will give ibdata1 a breather to handle just data dictionary and MVCC management. In addition, ibdata1 will stay rather lean and mean and can be read more quickly.

You will need to perform the InnoDB Infrastructure Cleanup. I wrote out all the steps back on October 29, 2010 in StackOverflow.

UPDATE 2013-04-22 08:10 EDT

Three suggestions

SUGGESTION 1 : I just noticed something else. You are using an ancient version of MySQL (5.0.45). You should think about upgrading to MySQL 5.6.11 as it performs significantly faster that MySQL 5.5 and way faster than MySQL 5.0.

SUGGESTION 2 : You should also go ahead and implement the InnoDB Infrastructure Cleanup.

SUGGESTION 3 : You should also check the disk itself. If the data is sitting on a RAID10 set, one of the disks may have an issues. Check the disk controller's battery as well because it can slow down disk caching and affect read performance.

Mysql – Changing Charset to utf8 for all the fields and tables in thesql

First backup the database. Sometimes character conversions can give unexpected results.

I have had good success using these instructions:

SELECT CONCAT('ALTER TABLE ',  table_name, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') INTO OUTFILE '/tmp/altertablesstatements.sql'
FROM
information_schema.tables
WHERE
    table_schema='<database-name>'
AND table_collation != 'utf8' 
GROUP BY table_name;

Add disable foreign_key_checks to first line of sql.

echo 'SET foreign_key_checks = 0;' >> /tmp/altertablesstatements.sql

Import the changes

mysql DATABASE  < /tmp/altertablesstatements.sql

Converting Columns

SELECT CONCAT(  'ALTER TABLE <DATABASE>.', table_name,  ' CHANGE ',COLUMN_NAME,'  ',COLUMN_NAME,' ',COLUMN_TYPE,' CHARACTER SET utf8 COLLATE utf8_general_ci',
IF(IS_NULLABLE='NO',' NOT NULL;',' NULL;')) INTO OUTFILE '/tmp/altercolumnsstatements.sql'
FROM information_schema.COLUMNS AS tb
WHERE TABLE_SCHEMA =  '<DATABASE>'
AND character_set_name != 'utf8';

Add disable foreign_key_checks to first line of sql.

echo 'SET foreign_key_checks = 0;' >> /tmp/altercolumnsstatements.sql

Import the changes

mysql DATABASE  < /tmp/altercolumnsstatements.sql

https://confluence.atlassian.com/jirakb/how-to-change-all-columns-and-tables-collation-to-utf8_bin-in-mysql-601456761.html

Best Answer

Related Solutions

MySQL database drop insanely slow

UPDATE 2013-04-15 18:04 EDT

Recommendation : Remove all Data and Index Pages from ibdata1

UPDATE 2013-04-22 08:10 EDT

Mysql – Changing Charset to utf8 for all the fields and tables in thesql

Related Question