Related issues:
https://github.com/3scale/system/issues/8471
https://issues.jboss.org/browse/THREESCALE-604 – fixed in 2.2 GA
One case
My Japanese customer has been trying 3scale AMP On-Premises for this month.
And they said they got below error when they input Japanese characters in the form "description" at service create on the web console.
ActiveRecord::StatementInvalid (Mysql2::Error: Incorrect string value: '\xE7\x92\xB0\xE5\xA2\x83...' for column 'description' at row 1: INSERT INTO `services` (`account_id`, `state`, `name`, `system_name`, `description`, `created_at`, `updated_at`) VALUES (2, 'incomplete', 'M3-APP-ST0', 'M3_APP_ST0', 'M3-APP-ST0環境。', '2017-05-30 06:28:31', '2017-05-30 06:28:31')):
app/controllers/api/services_controller.rb:43:in `create'
lib/three_scale/middleware/multitenant.rb:116:in `_call'
lib/three_scale/middleware/multitenant.rb:111:in `call'
lib/action_dispatch/rails5_ssl.rb:86:in `call'
Is it a bug? Any workaround?
Dev notes
Mysql only (postgres and oracle are fine) does not support utf8 mb4 chars by default.
So Hebrew, japanese and basically utf8 mb4 needing language cannot be saved in MySQL.
A possible workaround would be to support ut8 mb4 chars for the DBs that support it, and have a validation (ruby/rails, before reaching the DB) for MySQL to return a proper error message instead of crashing.
Another possibility is migrating mysql to postgres both for saas and for on-prem and not letting the customers useit on-prem for future releases, but that should be Product who decided it.
Real fix: we should migrate the whole database to utfmb4 on mysql. We can follow advices from Percona blog https://www.percona.com/blog/2018/04/10/migrating-database-charsets-to-utf8mb4/
Mysql8 supports by default utf8mb4
Postgres and Oracle databases do not have this issue.
That means we need to do a migration script for SaaS and for customers on-premises, which means taking care of no data loss:
- indices must be reworked
| Notice I’ve enabled the variable innodb_large_prefix. This is important because InnoDB limits index prefixes to 768 bytes by default. If you have an index based in a varchar(255) data type, you will get an error because the new charset exceeds this limit (up to four bytes per character goes beyond 1000 bytes) unless you limit the index prefix. To avoid issues during data load, we enable this variable to extend the limit to 3072 bytes. |
- text fields size must be ajusted
- it will need a maintenance window
Simple idea would be to have a maintenance window:
1. stop all services
2. mysqldump the old database
3. Changing characterset in the dumpfile to utfmb4 everywhere
4. load the dump into a new database
5. start all services
- is duplicated by
-
THREESCALE-4135 Proxy Update endpoint returns 500 Internal error when service has hebrew chars in description
-
- Closed
-
- is related to
-
THREESCALE-2012 Make mysql 8.0 work and support it
-
- Closed
-
- relates to
-
-
- Closed
-
