MariaDB set up issue

Post Reply
n1mjf
Novice Class
Posts: 6
Joined: 14 Sep 2021, 16:19

MariaDB set up issue

Post by n1mjf »

Hello,

I'm a new user of Log4OM, currently migrating over from HRD now that I hear that Log4OM has TCI support and SQL support.

So far I've gotten everything working under Log4OM2 except for SQL. When I attempt to set up the SQL instance I get the following in the log (I've masked sensitive parts):

2021-09-14 16:32:29.8956 INFO: [DbMysql] : MYSQL deploying database log4om2 START
2021-09-14 16:32:29.8961 INFO: [DbMysql] : Connection string: Server=192.168.10.26;Port=3306;User ID=admin;Password=notreallymypass;Pooling=false;SslMode=None
2021-09-14 16:32:29.8966 INFO: [DbMysql] : Script loaded
2021-09-14 16:32:29.8976 INFO: [DbMysql] : MySQL creation file is saved here: C:\Users\user\AppData\Roaming\Log4OM2\log\createDatabase_log4om2.sql
2021-09-14 16:32:29.8981 INFO: [DbMysql] : Opening connection...
2021-09-14 16:32:29.9066 INFO: [DbMysql] : Running script...
2021-09-14 16:32:29.9323 ERROR: [DbMysql][Deploy] : MYSQL Error while deploying database
[EXCEPTION] The given key was not present in the dictionary. System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary.
at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
at MySql.Data.MySqlClient.MySqlField.SetFieldEncoding()
at MySql.Data.MySqlClient.NativeDriver.GetColumnData(MySqlField field)
at MySql.Data.MySqlClient.NativeDriver.GetColumnsData(MySqlField[] columns)
at MySql.Data.MySqlClient.Driver.GetColumns(Int32 count)
at MySql.Data.MySqlClient.ResultSet.LoadColumns(Int32 numCols)
at MySql.Data.MySqlClient.ResultSet..ctor(Driver d, Int32 statementId, Int32 numCols)
at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlDataReader.Close()
at MySql.Data.MySqlClient.MySqlDataReader.Dispose()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
at MySql.Data.MySqlClient.MySqlScript.Execute()
at L4ONG.DAL.MYSQL.DbMysql.Deploy(DatabaseConfiguration #=bunchofstuff)
n1mjf
Novice Class
Posts: 6
Joined: 14 Sep 2021, 16:19

Re: MariaDB set up issue

Post by n1mjf »

MariaDB version is 10.6.4 and the admin user I'm using to set up the DB has all permissions granted.
User avatar
G4POP
Log4OM Alpha Team
Posts: 8724
Joined: 21 Jan 2013, 14:55
Location: Burnham on Crouch, Essex UK

Re: MariaDB set up issue

Post by G4POP »

Why did you use Maria and not https://www.mysql.com/downloads/
73 Terry G4POP
n1mjf
Novice Class
Posts: 6
Joined: 14 Sep 2021, 16:19

Re: MariaDB set up issue

Post by n1mjf »

It's an existing SQL server that is on my network for a number of tasks. I picked that as it's the default SQL server under Arch Linux distro.
User avatar
G4POP
Log4OM Alpha Team
Posts: 8724
Joined: 21 Jan 2013, 14:55
Location: Burnham on Crouch, Essex UK

Re: MariaDB set up issue

Post by G4POP »

I asked because I recal we had problems some time back with users that migrated from HRD and had a a Maria database but once they changed to the standard one above all worked well.

I have asked Lele to check this out because I only use Mysql and SQLite for testing
73 Terry G4POP
N1FG
Novice Class
Posts: 19
Joined: 23 Jul 2021, 08:47

Re: MariaDB set up issue

Post by N1FG »

@n1mjf - I had that problem and it was due to a mismatch datatype in the default Log4OM2 database configuration which resulted in looking for a key of the wrong type.

In general the issue was the DEFAULT CHARACTER SET statements in the configuration file (memory at work here, so I might be off +/- 3db! hi) - they are likely set to "utf8" but the database will actually have "utf8mb4" set.

So - change the default character set statements in the Log4OM2 configuration file to match your Maria DB setup and I think that was the ticket to success. (I just used Heidi to run modified script and ran each section separately to make sure it all cooked up correctly.)

Good luck and 73,

Larry N1FG
n1mjf
Novice Class
Posts: 6
Joined: 14 Sep 2021, 16:19

Re: MariaDB set up issue

Post by n1mjf »

Thank you. This sounds like the right track but I've looked through both .config files and cannot find any reference to the SQL character map.

I've looked through both, L4ONG.exe.config and L4ong.ConfigManager.config.
N1FG
Novice Class
Posts: 19
Joined: 23 Jul 2021, 08:47

Re: MariaDB set up issue

Post by N1FG »

@n1mjf - the problem is the SQL file which Log4OM2 generates to create the SQL db.

**** note - the below file is a SQL file and should have .sql extension but the attach file routine doesn't allow that extension - so I have added .log extension ****

Hope this helps - and you can see from the SQL file there are four separate sections -

1) create db log4om2
2) create table log4om2.log
3) create user log4om2user
4) grant privileges to log4om2user

When you first ask Log4OM2 to setup a SQL DB it generates a SQL script which is *almost* identical to this - with the exception of the utf8 vs utf8mb4 areas.

If needed, you can cut and paste just one section at a time into Heidi (or whatever SQL window you want), run them and make sure each section does what it needs to do.

Good luck -
Larry N1FG
Attachments
Build Log4OM DB char set utfmb4 vs utf8.sql.log
(8.74 KiB) Downloaded 4 times
Last edited by N1FG on 15 Sep 2021, 11:42, edited 1 time in total.
n1mjf
Novice Class
Posts: 6
Joined: 14 Sep 2021, 16:19

Re: MariaDB set up issue

Post by n1mjf »

Woohoo! That worked. Awesome!

I now have a log that supports everything I use directly!

Thank you all and hopefully this thread will help out others. I know that MariaDB is quite popular on Linux systems.

73!
N1FG
Novice Class
Posts: 19
Joined: 23 Jul 2021, 08:47

Re: MariaDB set up issue

Post by N1FG »

@n1mjf - That's great, glad this helped.

I was just going through my notes from July when I first installed Log4OM2 and found the source of the issue (as it regards Maria 10.6 vs MySQL) is contained in the mysql.sql file (~/AppData/Roaming/Log4OM2/) where it has that character type nailed to the floor. (Which to be clear might be perfect for MySQL.)

Perhaps at some point they will either move to utf8mb4 as a default (which I believe would correct this issue) or allow for a MariaDB.sql file with the utf8mb4 type.

Best 73 from New Hampshire!
Larry N1FG
Post Reply