Skip to main content

Convert database to UTF-8

What it does

Special characters causing errors

The default support for UTF8 is 3 byte.

Using 4 byte UTF content in text areas, can cause errors, that looks like

java.sql.SQLException: Incorrect string value: '\xC2\x96 Z. ...' for column 'NOTES' at row 1

How to invoke

Update the database to full UTF8 support using the following command

main?command=dk.p2e.blanket.codeunit.common.PageConvertDatabaseToUTF8

Note an administration profile is needed to complete the operation.

You might also have to update the tomcat server.xml file.
You have to add useBodyEncodingForURI="true" to the connectors.

Note that this codeunit has build in protection against being excuted multiple times at once, and should be executed every time a new field or entity has been added to the database.

Issue before version 11315 on MariaDB

Due to a difference in how MySQL and MariaDB stores "DEFALUT NULL", when executing this codeunit on a MariaDB server, all columns with the default value of null would be updated to have a default value of 'NULL' (the string NULL, not the empty value null).

To find the affected columns and generate all the required sql to fix the issue, run the following query.

SELECT a.TABLE_NAME, a.COLUMN_NAME, CONCAT('ALTER TABLE ', a.TABLE_NAME, ' ALTER ', a.COLUMN_NAME, ' SET DEFAULT NULL;') AS upd
FROM information_schema.columns AS a
INNER JOIN INFORMATION_SCHEMA.TABLES b ON 
	a.TABLE_CATALOG = b.TABLE_CATALOG AND 
	a.TABLE_SCHEMA = b.TABLE_SCHEMA AND 
	a.TABLE_NAME = b.TABLE_NAME AND
	LOWER(b.table_type) != 'view'
WHERE a.COLUMN_DEFAULT = '\'NULL\'' AND 
a.TABLE_SCHEMA = '[LIVE TABLE]'

This should create one row for every column in the database that has a default value of the string 'NULL'.

Take the output of the third column, copy all rows and execute them against the database.
Should look something like this:

ALTER TABLE form ALTER SagNavnI18N SET DEFAULT NULL;
ALTER TABLE form ALTER EntityIcon SET DEFAULT NULL;
ALTER TABLE form ALTER Description SET DEFAULT NULL;
ALTER TABLE form ALTER DescriptionI18N SET DEFAULT NULL;
ALTER TABLE form ALTER MailDomains SET DEFAULT NULL;
ALTER TABLE form ALTER MailSecurity SET DEFAULT NULL;
ALTER TABLE form ALTER Codeunit SET DEFAULT NULL;
ALTER TABLE form ALTER Instructions SET DEFAULT NULL;

Go back and execute the first query, it now should return 0 rows.

Options

[Optional options, that can be set at runtime, eg. url-parameters]None.

Configuration

[All possible configurations and where to edit them]None.

Developer info

  • Type: CodeunitPagecontent
  • Security: Requires administrator
  • Classpath: dk.p2e.blanket.codeunit.common.PageConvertDatabaseToUTF8
Back to top