varchar datatype for Primary Key is treating as BlOB/TEXT data type

[anthtoonaing]

varchar datatype for Primary Key is treating as BlOB/TEXT data type

[anthtoonaing]

In my database, I use Character Set : utf8mb4 and Collation : utf8mb4_general_ci.
When I change Collation to utf8mb4_unicode_ci, the problem is gone.
Now I am able to sync locally or via web server.

[Mimetis]

Hello @anthtoonaing
You are using Mariadb as the server side (if I understand correctly)
What db engine are you using on the client side ?

[anthtoonaing]

I am using MariaDB on client and server side. schema are exactly the same.

[anthtoonaing]

      Dotmim.Sync.SyncException: [InternalProvisionClientAsync].Provision:Table, TrackingTable, StoredProcedures, Triggers.Overwrite:False..[InternalProvisionAsync].Provision:Table, TrackingTable, StoredProcedures, Triggers.Overwrite:False..[InternalCreateTrackingTableAsync].Table:companies..BLOB/TEXT column 'id' used in key specification without a key length
       ---> MySqlConnector.MySqlException (0x80004005): BLOB/TEXT column 'id' used in key specification without a key length
         at MySqlConnector.Core.ServerSession.ReceiveReplyAsyncAwaited(ValueTask`1 task) in /_/src/MySqlConnector/Core/ServerSession.cs:line 964
         at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 175
         at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 133
         at MySqlConnector.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 493
         at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 77
         at MySqlConnector.MySqlCommand.ExecuteNonQueryAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 304
         at Dotmim.Sync.BaseOrchestrator.InternalCreateTrackingTableAsync(ScopeInfo scopeInfo, SyncContext context, DbTableBuilder tableBuilder, DbConnection connection, DbTransaction transaction, CancellationToken cancellationToken, IProgress`1 progress)
         at Dotmim.Sync.BaseOrchestrator.InternalCreateTrackingTableAsync(ScopeInfo scopeInfo, SyncContext context, DbTableBuilder tableBuilder, DbConnection connection, DbTransaction transaction, CancellationToken cancellationToken, IProgress`1 progress)
         --- End of inner exception stack trace ---
         at Dotmim.Sync.SyncAgent.SynchronizeAsync(String scopeName, SyncSetup setup, SyncType syncType, SyncParameters parameters, CancellationToken cancellationToken, IProgress`1 progress)
         at Dotmim.Sync.SyncAgent.SynchronizeAsync(String scopeName, SyncSetup setup, SyncType syncType, SyncParameters parameters, CancellationToken cancellationToken, IProgress`1 progress)
         at IV.DataSyncAgent.Worker.<>c__DisplayClass7_0.<<StartAsync>b__0>d.MoveNext() in D:\projs5\saysaing\apps\DataSync\IV.DataSyncAgent\Workers.cs:line 55

This is exception.

[anthtoonaing]

    internal class Worker: BackgroundService {
        private readonly ILogger<Worker> _logger;
        private readonly AppSettings _settings;
        private System.Timers.Timer? _syncTimer;
        private SyncAgent? _syncAgent;
        private int _syncing = 0;

        public Worker(ILogger<Worker> logger, AppSettings settings) {
            _logger = logger;
            _settings = settings;
        }
        protected override async Task ExecuteAsync(CancellationToken stoppingToken) {

        }

        public override async Task StartAsync(CancellationToken cancellationToken) {
            _logger.Log(LogLevel.Information, "Service Start");

            if (string.IsNullOrWhiteSpace(_settings.Tables)) {
                _logger.Log(LogLevel.Information, "No tables to sync");
                return;
            }

            if (string.IsNullOrWhiteSpace(_settings.ServerConnectionString) && string.IsNullOrWhiteSpace(_settings.RemoteOrchestratorUrl)) {
                _logger.Log(LogLevel.Information, "No server to sync");
                return;
            }

            if (string.IsNullOrWhiteSpace(_settings.ClientConnectionString)) {
                _logger.Log(LogLevel.Information, "No client to sync");
                return;
            }

            var options = new SyncOptions();
            var remoteOrchestrator = CreateRemoteOrchestrator(options);
            var localOrchestrator = CreateLocalOrchestrator(remoteOrchestrator.Options);

                var tables = _settings.Tables.Split(",");
            if (tables.Length > 0 && tables.All(t => t.Length > 0)) {
                
                _syncAgent = new SyncAgent(localOrchestrator, remoteOrchestrator);
                _syncTimer = new System.Timers.Timer(_settings.SyncInterval * 1000);
                _syncTimer.Elapsed += async (s, e) => {
                    if (0 == Interlocked.Exchange(ref _syncing, 1)) {
                        try {
                            _logger.Log(LogLevel.Information, "Sync starts");
                            var setup = new SyncSetup(tables);
                            var result = await _syncAgent.SynchronizeAsync(setup);
                            if (result != null) {
                                _logger.Log(LogLevel.Information, result.ToString());
                            }
                            _logger.Log(LogLevel.Information, "Sync ends");

                        }
                        catch(Exception ex) {
                            _logger.Log(LogLevel.Error, ex.ToString());
                        }
                        finally {
                            Interlocked.Exchange(ref _syncing, 0);
                        }
                    }
                };
                _syncTimer.AutoReset = true;
                _syncTimer.Enabled = true;
            }
            else {
                _logger.Log(LogLevel.Information, "Invalid table names");
            }
        }

        public override async Task StopAsync(CancellationToken cancellationToken) {

            await base.StopAsync(cancellationToken);
            _logger.Log(LogLevel.Information, "Service Stop");
        }

        public override void Dispose() {
            base.Dispose();

            if (_syncTimer != null) {
                _syncTimer.Stop();
                _syncTimer.Dispose();
            }

            if(_syncAgent != null) {
                _syncAgent.Dispose();
            }
        }

        private RemoteOrchestrator CreateRemoteOrchestrator(SyncOptions options) {
            if (!string.IsNullOrEmpty(_settings.ServerConnectionString)) {
                var remoteProvider = new MariaDBSyncProvider(_settings.ServerConnectionString);
                var remoteOrchestrator = new RemoteOrchestrator(remoteProvider, options);
                return remoteOrchestrator;
            }
            else {
                var remoteOrchestrator = new WebRemoteOrchestrator(_settings.RemoteOrchestratorUrl);
                remoteOrchestrator.HttpClient.Timeout = TimeSpan.FromMinutes(15);
                return remoteOrchestrator;
            }
        }

        private LocalOrchestrator CreateLocalOrchestrator(SyncOptions options) {
            var clientProvider = new MariaDBSyncProvider(_settings.ClientConnectionString);
            var localOrchestrator = new LocalOrchestrator(clientProvider, options);
            return localOrchestrator;
        }

    }

This is my code.