View Issue Details

IDProjectCategoryView StatusLast Update
0003997SymmetricDSBugpublic2020-12-29 14:39
ReporterFrederic Assigned To 
Prioritynormal 
Status newResolutionopen 
Product Version3.10.2 
Summary0003997: Failure to create all SYM_tables in utf8MB4_General_ci collation
DescriptionI have been experimenting with SymmetricDS for the past few days and found that it wouldn't create all of the 48 SYM_**** tables it needs when the database collation is set to UTF8MB4_General_ci.

When setting the database collation to UTF8_General_ci it works fine.

System Information:
- MYSQL (source) to MYSQL(target) both on the same host.
- MYSQL Server version: 10.1.25-MariaDB - mariadb.org binary distribution
- Running on a XAMPP instance on Windows10

The following tables are created:
- sym_channel
- sym_conflict
- sym_context
- sym_data
- sym_data_event
- sym_data_gap
- sym_extension
- sym_extract_request
Steps To Reproduce1) create 2 MYSQL database accessible by root on your localhost.
1.a first MYSQL database should be called 'base' in collation utf8MB4_general_ci
1.b second MYSQL database should be called 'clone' in collation utf8_general_ci

2) Create the following properties file and put them in /engines/
2.a (for the DB created in step 1.a):
engine.name=first
group.id=000
external.id=Main
sync.url=http://127.0.0.1:31415/sync/first
registration.url=
db.driver=com.mysql.jdbc.Driver
db.url=jdbc:mysql://127.0.0.1:3306/base?tinyInt1isBit=false&zeroDateTimeBehavior=convertToNull&characterEncoding=utf8&useUnicode=true&character_set_server=utf8mb4
db.user=root
db.password=
auto.start.engine=true
auto.registration=true

2.b (For the database created in step 1.B):
engine.name=target
group.id=001
external.id=mirA
sync.url=http://127.0.0.1:31415/sync/target
registration.url=http://127.0.0.1:31415/sync/first
db.driver=com.mysql.jdbc.Driver
db.url=jdbc:mysql://127.0.0.1:3306/clone?tinyInt1isBit=false&zeroDateTimeBehavior=convertToNull&characterEncoding=utf8&useUnicode=true&character_set_server=utf8mb4
db.user=root
db.password=
db.read.strings.as.bytes=true

3) Open the PowerShell on Windows and cd to the root folder of symmetrics; then type: 'bin/sym' wait for it to complete, then kill the batch job.
4) Go to your PHPMyAdminpage and compare the two databases.

5) as an extra... empty both databases,
6) change the collation of the MYSQL-database 'base' from UTF8MB4_General_ci to UTF8_general_ci
7) retype bin/sym
Additional InformationIn MYSQL utf8mb4_general_ci is needed for characters outside the basic multillingual plan (0X000 -0XFFFF)

The message in the PowerShell after failing to create all tables in a UTF8MB4_general_ci-database is:
[first] - SymmetricServlet - Requests for engine first are being rejected because it is not started
[target] - RegistrationService - Unable to register with server because the service is not available. It may be starting up.
[target] - RegistrationService - Could not register. Sleeping for 10000ms before attempting again.
[target] - RegistrationService - This node is unregistered. It will attempt to register using the registration.url

==> the node related to that database simply doesn't start.
Tagscharacter encoding, ddl/schema, install

Relationships

related to 0002044 acknowledgedchenson Startup failure with MySQL UTF8 database 

Activities

pmarzullo

2020-12-29 14:39

developer   ~0001873

You can set a InnoDB parameter that will allow 3072 bytes for the key in the index:

innodb-large-prefix=ON (or 1)

Issue History

Date Modified Username Field Change
2019-06-12 12:29 Frederic New Issue
2019-06-12 12:29 Frederic Tag Attached: character encoding
2019-06-12 12:29 Frederic Tag Attached: install
2019-06-19 16:42 elong Tag Attached: ddl/schema
2019-06-19 16:44 elong Relationship added related to 0002044
2020-12-29 14:39 pmarzullo Note Added: 0001873