Problem inserting UTF8 Currency symbol

Thuthuka · July 31, 2024, 7:34pm

Hi All
I have the following configuration
System windows 10
java openjdk 17.0.11
liquibase 4.5.0
Database 10.4.32-MariaDB

I am getting the following error :

Reason: liquibase.exception.DatabaseException: (conn=160) Data too long for column ‘SYMBOL’ at row 59 [Failed SQL: (1406) INSERT INTO invoice.CURRENCY
(PK_CURRENCY_ID, CURRENCY, CODE_A, SYMBOL, UNICODE_MS, CODE_DECIMAL, CODE_HEX,FK_CURRENCY_FORMAT)

I get this when running release Java war file and when running in Eclipse , in debug mode, in an Apache Tomcat 9.0.60 server.

The SQL works when I run the same application in Eclipse in debug as a Java application file.
I can also run the SQL via the command line withouty errors
mysql -u adminuser -p invoice < changelog-1.4.sql

I think that some how it is not loading the SQL as UTF8 when inside a Tomcat Server and not taking the encoding=“utf8” for some reason.

Please advise me here.
Thank you

my master_changelog .xml is as follows.

<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.0.xsd
http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">
<include relativeToChangelogFile="true"
file="change_sets\v1_0\changelog-1.1.xml" />
<include relativeToChangelogFile="true"
file="change_sets\v1_0\changelog-1.4.xml" />
</databaseChangeLog>

my changelog-1.4.xml is as follows


<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
	xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
	xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.0.xsd
        http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

	<changeSet id="update version for v2-040 currency"
		author="Peter">
		<comment>
            big tidy up and update of currency table
        </comment>
		<addColumn schemaName="invoice" tableName="CURRENCY">
			<column name="CODE_A" type="varchar(5)">
			</column>
		</addColumn>
		<modifySql>
			<append value=" AFTER CURRENCY ;" />
		</modifySql>
	</changeSet>


	<changeSet id="update version for v2-041 currency"
		author="Peter">
		<addColumn schemaName="invoice" tableName="CURRENCY">
			<column name="UNICODE_MS" type="VARCHAR(5)">
				<constraints nullable="true" />
			</column>
		</addColumn>
		<addColumn schemaName="invoice" tableName="CURRENCY">
			<column name="CODE_DECIMAL" type="varchar(50)">
			</column>
		</addColumn>
		<addColumn schemaName="invoice" tableName="CURRENCY">
			<column name="CODE_HEX" type="varchar(50)">
			</column>
		</addColumn>
	</changeSet>

	<changeSet id="update existing currency v2-042 currency"
		author="Peter">
		<update tableName="CURRENCY">
			<column name="CODE_A" value="ZAR" />
			<column name="UNICODE_MS" value="R" />
			<column name="CODE_DECIMAL" value="82" />
			<column name="CODE_HEX" value="52" />
			<where> CURRENCY = "South Africa Rand" </where>
		</update>

		<update tableName="CURRENCY">
			<column name="CODE_A" value="USD" />
			<column name="UNICODE_MS" value="$" />
			<column name="CODE_DECIMAL" value="36" />
			<column name="CODE_HEX" value="24" />
			<where> CURRENCY = "United States Dollar" </where>
		</update>

		<update tableName="CURRENCY">
			<column name="CODE_A" value="EUR" />
			<column name="UNICODE_MS" value="€" />
			<column name="CODE_DECIMAL" value="8364" />
			<column name="CODE_HEX" value="20ac" />
			<where> CURRENCY = "Euro" </where>
		</update>

		<update tableName="CURRENCY">
			<column name="CODE_A" value="NGN" />
			<column name="UNICODE_MS" value="₦" />
			<column name="CODE_DECIMAL" value="8358" />
			<column name="CODE_HEX" value="20a6" />
			<where> CURRENCY = "Nigeria Naira" </where>
		</update>
	</changeSet>

    <changeSet id="Add currency with SQL v2-043 currency"
		author="Peter">
		<comment>
			liquibase formatted sql
			changeset Peter:Update_the_currency_tablev2-043_load_data splitStatements:true endDelimiter:;		
		</comment>
        <sqlFile encoding="utf8" path="liquibase/change_sets/v1_0/changelog-1.4.sql"/>
    </changeSet>

</databaseChangeLog>

my SQL is as follows:


INSERT INTO invoice.CURRENCY
    (PK_CURRENCY_ID, CURRENCY, CODE_A, SYMBOL, UNICODE_MS, CODE_DECIMAL, CODE_HEX,FK_CURRENCY_FORMAT)
VALUES
('5','Albania Lek','ALL','Lek','Lek','76: 101: 107','4c: 65: 6b','1'),
('6','Afghanistan Afghani','AFN','A','A','1547','60b','1'),
('7','Argentina Peso','ARS','$','$','36','24','1'),
('8','Aruba Guilder','AWG','ƒ','ƒ','402','192','1'),
('9','Australia Dollar','AUD','$','$','36','24','1'),
('10','Azerbaijan Manat','AZN','₼','₼','8380','20bc','1'),
('11','Bahamas Dollar','BSD','$','$','36','24','1'),
('12','Barbados Dollar','BBD','$','$','36','24','1'),
('13','Belarus Ruble','BYN','Br','Br','66: 114','42: 72','1'),
('14','Belize Dollar','BZD','BZ$','BZ$','66: 90: 36','42: 5a: 24','1'),
('15','Bermuda Dollar','BMD','$','$','36','24','1'),
('16','Bolivia Bolíviano','BOB','$b','$b','36: 98','24: 62','1'),
('17','Bosnia and Herzegovina Convertible Mark','BAM','KM','KM','75: 77','4b: 4d','1'),
('18','Botswana Pula','BWP','P','P','80','50','1'),
('19','Bulgaria Lev','BGN','лв','лв','1083: 1074','43b: 432','1'),
('20','Brazil Real','BRL','R$','R$','82: 36','52: 24','1'),
('21','Brunei Darussalam Dollar','BND','$','$','36','24','1'),
('22','Cambodia Riel','KHR','៛','៛','6107','17db','1'),
('23','Canada Dollar','CAD','$','$','36','24','1'),
('24','Cayman Islands Dollar','KYD','$','$','36','24','1'),
('25','Chile Peso','CLP','$','$','36','24','1'),
('26','China Yuan Renminbi','CNY','¥','¥','165','a5','1'),
('27','Colombia Peso','COP','$','$','36','24','1'),
('28','Costa Rica Colon','CRC','₡','₡','8353','20a1','1'),
('29','Croatia Kuna','HRK','kn','kn','107: 110','6b: 6e','1'),
('30','Cuba Peso','CUP','₱','₱','8369','20b1','1'),
('31','Czech Republic Koruna','CZK','Kč','Kč','75: 269','4b: 10d','1'),
('32','Denmark Krone','DKK','kr','kr','107: 114','6b: 72','1'),
('33','Dominican Republic Peso','DOP','RD$','RD$','82: 68: 36','52: 44: 24','1'),
('34','East Caribbean Dollar','XCD','$','$','36','24','1'),
('35','Egypt Pound','EGP','£','£','163','a3','1'),
('36','El Salvador Colon','SVC','$','$','36','24','1'),
('37','Falkland Islands Pound','FKP','£','£','163','a3','1'),
('38','Fiji Dollar','FJD','$','$','36','24','1'),
('39','Ghana Cedi','GHS','¢','¢','162','a2','1'),
('40','Gibraltar Pound','GIP','£','£','163','a3','1'),
('41','Guatemala Quetzal','GTQ','Q','Q','81','51','1'),
('42','Guernsey Pound','GGP','£','£','163','a3','1'),
('43','Guyana Dollar','GYD','$','$','36','24','1'),
('44','Honduras Lempira','HNL','L','L','76','4c','1'),
('45','Hong Kong Dollar','HKD','$','$','36','24','1'),
('46','Hungary Forint','HUF','Ft','Ft','70: 116','46: 74','1'),
('47','Iceland Krona','ISK','kr','kr','107: 114','6b: 72','1'),
('48','India Rupee','IR','IR','INR','8377','20b9','1'),
('49','Indonesia Rupiah','IDR','Rp','Rp','82: 112','52: 70','1'),
('50','Iran Rial','IRR','﷼','﷼','65020','fdfc','1'),
('51','Isle of Man Pound','IMP','£','£','163','a3','1'),
('52','Israel Shekel','ILS','₪','₪','8362','20aa','1'),
('53','Jamaica Dollar','JMD','J$','J$','74: 36','4a: 24','1'),
('54','Japan Yen','JPY','¥','¥','165','a5','1'),
('55','Jersey Pound','JEP','£','£','163','a3','1'),
('56','Kazakhstan Tenge','KZT','лв','лв','1083: 1074','43b: 432','1'),
('57','Korea (North) Won','KPW','₩','₩','8361','20a9','1'),
('58','Korea (South) Won','KRW','₩','₩','8361','20a9','1'),
('59','Kyrgyzstan Som','KGS','лв','лв','1083: 1074','43b: 432','1'),
('60','Laos Kip','LAK','₭','₭','8365','20ad','1'),
('61','Lebanon Pound','LBP','£','£','163','a3','1'),
('62','Liberia Dollar','LRD','$','$','36','24','1'),
('63','Macedonia Denar','MKD','ден','ден','1076: 1077: 1085','434: 435: 43d','1'),
('64','Malaysia Ringgit','MYR','RM','RM','82: 77','52: 4d','1'),
('65','Mauritius Rupee','MUR','₨','₨','8360','20a8','1'),
('66','Mexico Peso','MXN','$','$','36','24','1'),
('67','Mongolia Tughrik','MNT','₮','₮','8366','20ae','1'),
('68','Mozambique Metical','MZN','MT','MT','77: 84','4d: 54','1'),
('69','Namibia Dollar','NAD','$','$','36','24','1'),
('70','Nepal Rupee','NPR','₨','₨','8360','20a8','1'),
('71','Netherlands Antilles Guilder','ANG','ƒ','ƒ','402','192','1'),
('72','New Zealand Dollar','NZD','$','$','36','24','1'),
('73','Nicaragua Cordoba','NIO','C$','C$','67: 36','43: 24','1'),
('74','Norway Krone','NOK','kr','kr','107: 114','6b: 72','1'),
('75','Oman Rial','OMR','﷼','﷼','65020','fdfc','1'),
('76','Pakistan Rupee','PKR','₨','₨','8360','20a8','1'),
('77','Panama Balboa','PAB','B/.','B/.','66: 47: 46','42: 2f: 2e','1'),
('78','Paraguay Guarani','PYG','Gs','Gs','71: 115','47: 73','1'),
('79','Peru Sol','PEN','S/.','S/.','83: 47: 46','53: 2f: 2e','1'),
('80','Philippines Peso','PHP','₱','₱','8369','20b1','1'),
('81','Poland Zloty','PLN','zł','zł','122: 322','7a: 142','1'),
('82','Qatar Riyal','QAR','﷼','﷼','65020','fdfc','1'),
('83','Romania Leu','RON','lei','lei','108: 101: 105','6c: 65: 69','1'),
('84','Russia Ruble','RUB','₽','₽','8381','20bd','1'),
('85','Saint Helena Pound','SHP','£','£','163','a3','1'),
('86','Saudi Arabia Riyal','SAR','﷼','﷼','65020','fdfc','1'),
('87','Serbia Dinar','RSD','Дин.','Дин.','1044: 1080: 1085: 46','414: 438: 43d: 2e','1'),
('88','Seychelles Rupee','SCR','₨','₨','8360','20a8','1'),
('89','Singapore Dollar','SGD','$','$','36','24','1'),
('90','Solomon Islands Dollar','SBD','$','$','36','24','1'),
('91','Somalia Shilling','SOS','S','S','83','53','1'),
('92','Sri Lanka Rupee','LKR','₨','₨','8360','20a8','1'),
('93','Sweden Krona','SEK','kr','kr','107: 114','6b: 72','1'),
('94','Switzerland Franc','CHF','CHF','CHF','67: 72: 70','43: 48: 46','1'),
('95','Suriname Dollar','SRD','$','$','36','24','1'),
('96','Syria Pound','SYP','£','£','163','a3','1'),
('97','Taiwan New Dollar','TWD','NT$','NT$','78: 84: 36','4e: 54: 24','1'),
('98','Thailand Baht','THB','฿','฿','3647','e3f','1'),
('99','Trinidad and Tobago Dollar','TTD','TT$','TT$','84: 84: 36','54: 54: 24','1'),
('100','Turkey Lira','TRY','TL','TL','8378','20ba','1'),
('101','Tuvalu Dollar','TVD','$','$','36','24','1'),
('102','Ukraine Hryvnia','UAH','₴','₴','8372','20b4','1'),
('103','United Kingdom Pound','GBP','£','£','163','a3','1'),
('104','Uruguay Peso','UYU','$U','$U','36: 85','24: 55','1'),
('105','Uzbekistan Som','UZS','лв','лв','1083: 1074','43b: 432','1'),
('106','Venezuela Bolívar','VEF','Bs','Bs','66: 115','42: 73','1'),
('107','Viet Nam Dong','VND','₫','₫','8363','20ab','1'),
('108','Yemen Rial','YER','﷼','﷼','65020','fdfc','1'),
('109','Zimbabwe Dollar','ZWD','Z$','Z$','90: 36','5a: 24','1'),
('110','Zambian Kwacha','ZMK','K','ZK','90: 36','5a: 24','1'),
('111','Malawian Kwacha','MWK','K','MK','90: 36','5a: 24','1');

Topic		Replies	Views
utf8 in a SQL tag encoding properly General Discussion	3	1979	October 24, 2013
Fails to create DB objects having specific symbols like Yen, plus sign and Japanese character's on the table Liquibase Development	6	1986	December 6, 2021
Liquibase 4 UTF-8 not recognized General Discussion	2	782	March 10, 2022
Character encoding problem when using sqlFile General Discussion	2	2365	June 6, 2013
character encoding and sql output General Discussion	7	3676	May 4, 2010

Problem inserting UTF8 Currency symbol

Related topics