SchemaValidator and SchemaUpdate Mismatch Issue with AutoMapping

[bbsuuo]

I encountered an issue with the SchemaValidator and SchemaUpdate functionalities in NHibernate while using AutoMapping to construct tables. Here's a breakdown of the problem along with relevant code and error logs:

using System;
using System.Collections.Generic;
using MySqlConnector;
using Microsoft.Extensions.Logging;
using ProtobufRuntime.OBJ;
using NHibernate.Cfg;
using NHibernate;
using ILoggerFactory = Microsoft.Extensions.Logging.ILoggerFactory;
using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using NHibernate.Tool.hbm2ddl;
using FluentNHibernate.Automapping;

namespace Server.DB.MySQL
{
    // 实现 MySQL 服务的类
    public class MySQLService : IMySQLService
    {
 
        private readonly ILoggerFactory? _loggerFactory;
        private readonly ILogger? _logger;
        private ISessionFactory? _sessionFactory;

        private static Dictionary<string, ISession> _sessionContext = new Dictionary<string, ISession>();


        public MySQLService(IServiceProvider serviceProvider) 
        {
            _loggerFactory = serviceProvider.GetService(typeof(ILoggerFactory)) as ILoggerFactory;
            _logger = _loggerFactory?.CreateLogger("MySQL Service");
        }
        public void Start(DatabaseBuilder builder)
        {
            _logger?.LogInformation($"Start");

            var configuration = new Configuration();
            configuration.Configure("DBConfig/database-config.xml");
 
            var fconfigure = Fluently.Configure(configuration);
            var mysqlConfig = MySQLConfiguration.Standard;
            fconfigure.Database(mysqlConfig);
            if (builder.Mappings != null)
            {
                fconfigure.Mappings(builder.Mappings);
            }
            else 
            {
                _logger?.LogWarning($"Not Mapping Set");
            }

            fconfigure.ExposeConfiguration(SchemaCheckerCreate);
            _sessionFactory = fconfigure.BuildSessionFactory();

            _logger?.LogInformation($"Start Service Completed! {_sessionFactory == null}");
        }

        public void Stop() 
        {
            if (_sessionFactory != null)
            {
                _sessionFactory.Close();
            }

            _logger?.LogInformation($"Stop");
        }

        public ISession? GetSession(string sessionKey)
        {
            if (_sessionFactory == null)
            {
                throw new InvalidOperationException("Session factory is not initialized. Make sure to initialize the session factory before accessing sessions.");
            }

            var context = _sessionContext;
            ISession? currentSession = null;
            if (context.ContainsKey(sessionKey))
            {
                currentSession = context[sessionKey] as ISession;
            }     
            if (currentSession == null)
            {
                currentSession = _sessionFactory.OpenSession();
                context[sessionKey] = currentSession;
            }

            return currentSession;
        }
        public void CloseSession(string sessionKey)
        {
            if (_sessionFactory == null)
            {
                throw new InvalidOperationException("Session factory is not initialized. Make sure to initialize the session factory before accessing sessions.");
            }
            var context = _sessionContext;
            if (!context.ContainsKey(sessionKey))
            {
                // Session with the given key does not exist, no action needed
                return;
            }
            var currentSession = context[sessionKey] as ISession;
            if (currentSession == null)
            {
                return;
            }
            currentSession.Close();
            context.Remove(sessionKey);
        }
        private void SchemaCheckerCreate(Configuration config) 
        {
            _logger?.LogInformation($"Start create schema");

             // already something: validate
            bool validatorError = false; 
            SchemaValidator validator = new SchemaValidator(config);
            try
            {
                validator.Validate();
            }
            catch (SchemaValidationException schemeError) 
            {
                validatorError = true;
                _logger?.LogError(String.Format("Error while valid database: {0}", schemeError));
                foreach (var error in schemeError.ValidationErrors) 
                {
                    _logger?.LogError(String.Format("Scheme error {0}", error));
                }

            }
            catch (Exception validError)
            {
                validatorError = true;
                _logger?.LogError(String.Format("Error while valid database: {0}", validError));
 
            }

            if (validatorError) 
            {
                try
                {
                    SchemaUpdate update = new SchemaUpdate(config);
                    update.Execute(false, true);
                }
                catch (HibernateException e)
                {
                    _logger?.LogError(String.Format("Error while updating database: {0}", e));
                }
            }

        }
 
    }
}

and

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Server.Protobuf.PipelineFilter;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using Server.DB.DBServices;
using Server.DB.MySQL;
using SuperSocket;
using SuperSocket.Connection;
using SuperSocket.Server;
using SuperSocket.Server.Abstractions;
using SuperSocket.Server.Abstractions.Session;
using Server.DB;
using HubServer.Data;
using HubServer.Data.DB.Entities;
using HubServer.Data.DB.Components;
using HubServer.Services.UserServices;
using FluentNHibernate.Automapping;

namespace HubServer.Server
{
    internal class HUBService : SuperSocketService<ProtobufPackageInfo>
    {
        IUserService _userService;
        IDatabaseService _databaseService;
        IMySQLService _mysqlService;
        public HUBService(IServiceProvider serviceProvider, IOptions<ServerOptions> serverOptions) : base(serviceProvider, serverOptions)
        {
            _userService = serviceProvider.GetService<IUserService>();
            _databaseService = serviceProvider.GetService<IDatabaseService>();
            _mysqlService = serviceProvider.GetService<IMySQLService>();
        }

        protected override async ValueTask OnStartedAsync()
        {
            DatabaseBuilder builder = new DatabaseBuilder();
            var storeConfig = new SQLStoreConfiguration();
            builder.Mappings = (x) => {
                x.AutoMappings.Add(AutoMap.Assembly(typeof(UserInfoEntity).Assembly, storeConfig).Conventions.Add<PrimaryKeyConvention>());
            } ;
            await _databaseService.StartService(builder);
            await base.OnStartedAsync();
            await Task.Delay(5000);
            Test();
        }
        void Test()
        {
            var session = _mysqlService.GetSession("test");
            session.Save(new UserInfoEntity()
            {
                UserName = "test",
                Gender = 0,
                Id = 1,
                PhoneNumber = "1234567890",
                TimeInfo = new UserTimeComponent()
                {
                    LastLoginTime = DateTime.Now,
                    RegisteTime = DateTime.Now,
                }
            });
            _mysqlService.CloseSession("test");
        }


        protected override ValueTask OnStopAsync()
        {
            _databaseService.StopService();
            return base.OnStopAsync();
        }

        protected override ValueTask OnSessionConnectedAsync(IAppSession session)
        {
            return base.OnSessionConnectedAsync(session);
        }

        protected override ValueTask OnSessionClosedAsync(IAppSession session, CloseEventArgs e)
        {
            return base.OnSessionClosedAsync(session, e);
        }

        protected override ValueTask<bool> OnSessionErrorAsync(IAppSession session, PackageHandlingException<ProtobufPackageInfo> exception)
        {
            return base.OnSessionErrorAsync(session, exception);
        }

        protected override ValueTask OnNewConnectionAccept(ListenOptions listenOptions, IConnection connection)
        {
            return base.OnNewConnectionAccept(listenOptions, connection);
        }
    }
}

1.Upon the first run, the Validator throws an error:
2024-05-26 00:35:43,945 [1] ERROR NHibernate.Tool.hbm2ddl.SchemaValidator [(null)] - could not complete schema validation
NHibernate.SchemaValidationException: Schema validation failed: see list of validation errors
at NHibernate.Cfg.Configuration.ValidateSchema(Dialect dialect, IDatabaseMetadata databaseMetadata)
at NHibernate.Tool.hbm2ddl.SchemaValidator.Validate()
2024-05-26 00:35:43,970 [1] DEBUG NHibernate.Connection.ConnectionProvider [(null)] - Closing connection
2024-05-26 00:35:43,974 [1] ERROR MySQL Service [(null)] - Error while valid database: NHibernate.SchemaValidationException: Schema validation failed: see list of validation errors
at NHibernate.Cfg.Configuration.ValidateSchema(Dialect dialect, IDatabaseMetadata databaseMetadata)
at NHibernate.Tool.hbm2ddl.SchemaValidator.Validate()
at Server.DB.MySQL.MySQLService.SchemaCheckerCreate(Configuration config) in C:\Library\UnityLibrary\xblstudio\Server\GameServer\Server.DB\MySQL\MySQLService.cs:line 117
2024-05-26 00:35:43,977 [1] ERROR MySQL Service [(null)] - Scheme error Missing table: PayInfoEntity
2024-05-26 00:35:43,978 [1] ERROR MySQL Service [(null)] - Scheme error Missing table: PayOrderEntity
2024-05-26 00:35:43,979 [1] ERROR MySQL Service [(null)] - Scheme error Missing table: UserInfoEntity

It's fine, This error is expected since the tables have not been constructed yet. the SchemaUpdate successfully constructs all table structures without any errors.

2.Upon the second run, I receive errors from both SchemaValidator and SchemaUpdate:

2024-05-26 00:39:03,834 [1] ERROR NHibernate.Tool.hbm2ddl.SchemaValidator [(null)] - could not complete schema validation
NHibernate.SchemaValidationException: Schema validation failed: see list of validation errors
at NHibernate.Cfg.Configuration.ValidateSchema(Dialect dialect, IDatabaseMetadata databaseMetadata)
at NHibernate.Tool.hbm2ddl.SchemaValidator.Validate()
2024-05-26 00:39:03,865 [1] DEBUG NHibernate.Connection.ConnectionProvider [(null)] - Closing connection
2024-05-26 00:39:03,868 [1] ERROR MySQL Service [(null)] - Error while valid database: NHibernate.SchemaValidationException: Schema validation failed: see list of validation errors
at NHibernate.Cfg.Configuration.ValidateSchema(Dialect dialect, IDatabaseMetadata databaseMetadata)
at NHibernate.Tool.hbm2ddl.SchemaValidator.Validate()
at Server.DB.MySQL.MySQLService.SchemaCheckerCreate(Configuration config) in C:\Library\UnityLibrary\xblstudio\Server\GameServer\Server.DB\MySQL\MySQLService.cs:line 117
2024-05-26 00:39:03,870 [1] ERROR MySQL Service [(null)] - Scheme error Wrong column type in def.hub.userinfoentity for column UserName. Found: char, Expected VARCHAR(255)

and

2024-05-26 00:39:03,912 [1] ERROR NHibernate.Tool.hbm2ddl.SchemaUpdate [(null)] - could not complete schema update
System.ArgumentException: Invalid collection name. (Parameter 'collectionName')
at MySqlConnector.Core.SchemaProvider.GetSchemaAsync(IOBehavior ioBehavior, String collectionName, CancellationToken cancellationToken) in //src/MySqlConnector/Core/SchemaProvider.cs:line 59
at MySqlConnector.MySqlConnection.GetSchema(String collectionName, String[] restrictions) in //src/MySqlConnector/MySqlConnection.cs:line 535
at NHibernate.Dialect.Schema.AbstractDataBaseSchema.GetForeignKeys(String catalog, String schema, String table)
at NHibernate.Dialect.Schema.AbstractTableMetadata.InitForeignKeys(IDataBaseSchema meta)
at NHibernate.Dialect.Schema.AbstractTableMetadata..ctor(DataRow rs, IDataBaseSchema meta, Boolean extras)
at NHibernate.Dialect.Schema.MySQLTableMetadata..ctor(DataRow rs, IDataBaseSchema meta, Boolean extras)
at NHibernate.Dialect.Schema.MySQLDataBaseSchema.GetTableMetadata(DataRow rs, Boolean extras)
at NHibernate.Tool.hbm2ddl.DatabaseMetadata.GetTableMetadata(String name, String schema, String catalog, Boolean isQuoted)
at NHibernate.Cfg.Configuration.GenerateSchemaUpdateScript(Dialect dialect, IDatabaseMetadata databaseMetadata)
at NHibernate.Tool.hbm2ddl.SchemaUpdate.Execute(Action`1 scriptAction, Boolean doUpdate)

On the second run, both SchemaValidator and SchemaUpdate encountered errors. This is somewhat unexpected because the current database was entirely generated by the SchemaUpdate executed during the first run. Moreover, based on the error message 2024-05-26 00:39:03,870 [1] ERROR MySQL Service [(null)] - Scheme error Wrong column type in def.hub.userinfoentity for column UserName. Found: char, Expected VARCHAR(255), I meticulously examined the table structure and found that the type of UserName is VARCHAR(255) rather than char. Why would such an error occur? Furthermore, the error from SchemaUpdate is perplexing because my types haven't undergone any changes, so SchemaUpdate shouldn't encounter any errors; it shouldn't execute any operations, right?

[bbsuuo]

After downloading the source code and recompiling it myself, I managed to resolve this issue. I found significant differences in the Configuration script within the nhibernate-core library between the code on NuGet and the code on the 5.5.x branch. I'm unsure when these changes will be pushed to NuGet.

[hazzik]

Glad you've found the issue, @bbsuuo. However, it seems that you were using old NHibernate 5.3.3 that is referenced by FluentNHibernate. It seems that instead of recompiling anything yourself you needed to install the NHibernate 5.5.1 explicitly.