# Convert database to UTF-8

## <span class="mw-headline" id="bkmrk-what-it-does-1">What it does</span>

<span class="mw-headline" id="bkmrk-special-characters-c-1">Special characters causing errors</span><span class="mw-headline" id="bkmrk-"></span>

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
```

## <span class="mw-headline" id="bkmrk-how-to-invoke-1">How to invoke</span>

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.  
Remember to reaplce `[LIVE DB]` with the name of the live database of your instance!

```mysql
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 DB]'
```

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:

```mysql
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.

### <span class="mw-headline">Options</span>

<span class="mw-headline">None.</span>

## <span class="mw-headline" id="bkmrk-configuration-1">Configuration</span>

<span class="mw-headline">None.</span>

## <span class="mw-headline" id="bkmrk-developer-info-1">Developer info</span>

- <span class="mw-headline">Type: [CodeunitPagecontent](https://docs.tsnocode.com/books/codeunit-reference/chapter/pagecontent)</span>
- <span class="mw-headline">Security: Requires administrator</span>
- <span class="mw-headline">Classpath: dk.p2e.blanket.codeunit.common.PageConvertDatabaseToUTF8</span>