Jump to content
TNG Community
Bill Herndon

MariaDB Issues - TNG 13.0.4 Install Stalls During Table Creation

Recommended Posts

Bill Herndon

I've successfully run MariaDB (the 'drop-in' successor to MySQL, created by the original team that authored MySQL many years ago) on my TNG live site for years.  Now, with Apple making it harder and harder for software developers to install and manage development tools, I've had to switch to Brew for maintaining software that my TNG test installations require.  One good positive development, though, was the possibility of leaving MySQL (and Oracle the owner of MySQL) behind forever with a move to MariaDB.

All good...or so I thought.  My first attempt to create a fresh TNG 13.0.4 test installation stalled at database table creation.  The script just never finishes.  Approximately half of the tables get created, and then...nothing.  Process inspection shows that MariaDB does not appear to be doing any work (so...no live-lock though perhaps a deadlock, I suppose).  PHP does not appear to be working either.  A second attempt stalled as well, but after getting not quite as far through the tables as the first.

My system:  MariaDB 10.6.4 / PHP 7.4.2 / macOS 11.5.2 (otherwise known as "Big Suck" ... oh, sorry... "Big Sur")

Any insights would be appreciated.

Bill Herndon

Lindell-Herndon Genealogy

Share this post


Link to post
Share on other sites
Ken Roy

Bill,

Maybe MariaDB has the same issue as MySQL in creating new tables.  Since the MySQL folk chose to default the collation sequence to latin1 when they migrated from MySQL 4 to MySQL5 even though the underlying database is UTF-8, some parameter still defaults to latin1 when creating new tables and as a result you have incompatible collation sequences. 

I know nothing about MacIntosh systems but maybe another possibility is that you need to specify a port to use MariaDB

1 hour ago, Bill Herndon said:

Now, with Apple making it harder and harder for software developers to install and manage development tools

Apple is not the only one.  Microsoft is also enforcing rules that make it harder to install 32-bit apps on 64-bit systems. 

 

Share this post


Link to post
Share on other sites
Bill Herndon

Ken... I've tried both of the UTF8 Unicode collations supported by MariaDB, utf8mb3 (3-byte) and utf8mb4 (4-byte).  (These are, BTW, also used by modern versions of MySQL.)  Wasn't successful with either one.  The table creation stalls after creating tng_repositories.

I'm able to import my older test databases (MySQL 5.7) or my live database (MariaDB) into a new MariaDB database with either collation.  So...not dead in the water, but with more and more hosting services switching with MariaDB, I see trouble on the horizon.

Bill

Lindell-Herndon Genealogy

Share this post


Link to post
Share on other sites
Ken Roy

Bill,

I am not a database expert, but according to Brent Hemphill who provided me the code to create new tables with the correct collation sequence used for the other tables in the database,  utf8mb4 requires INNODB instead of ISAM for the database handler.

I may not have yet posted the changes required to create the tables correctly for the Census Plus International mod since there were a whole lot of other changes needed to eliminate PHP 8 warnings.   My Same Person Link mod does contain the correct way to create new tables in an existing database as provided by Brent Hemphill.

Did you create your database before doing the TNG full install?  or are you trying to create it as part of the readme?

While I did a full TNG 13.0.4 install on my WampServer, I used a previous database.

Share this post


Link to post
Share on other sites
bhemph

Ken,

Since Bill has tried the utf8mb3 (former collation label utf8), which would have used the distributed TNG database table code and ISAM without an error, there must be something new with MariaDB.  Or is it something on his installation?  Also since Bill says that it doesn't stop at the same point in the table creation, it is kind of strange.  Importing working is even more strange, as the mention of reserved word usage would break there as well.  If I remember correctly though, MariaDB has tried to make sure there are very few reserved words by doing some things differently from MySQL but still compatible such that the MariaDB reserved words are a subset of the MySQL reserved words.  So that would mean it should be less likely to have problems with MariaDB.  I am curious now what is going on and think I will look into testing my install this weekend.

Brent

Share this post


Link to post
Share on other sites
Bill Herndon

Ken, Brent... Thanks for the additional analysis.  A couple of comments:

InnoDB and MyISAM are storage engines and not the only ones supported by MySQL and MariaDB.  The main difference between the two is that InnoDB supports transactions, rollback, and atomicity.  (That last one has to do with an "all or nothing" view of how database operations are executed.)  ISAM supports none of these features.  Consequently, the way you write code to interact with an ISAM database is very different from the code you write to interact with an InnoDB database.

The ability to store utf8mb3 (3 byte Unicode) and utf8mb4 (4 byte Unicode) should be independent of the storage engine, unless Oracle (for MySQL) or the MariaDB Foundation actually built in something to prevent the usage of one encoding (or the other).  Both Oracle and MariaDB have stated that utfmb3 is supported but deprecated and that developers need to be moving to utfmb4. Finally..."encoding" applies to the database itself and is chosen when the database is created.  "collation" is a switch selected by TNG code when you create/import tables and refers to how fields are ordered when selected by queries.  (It's probably not a good idea, but you can select utfmb4 encoding and use utfmb3_general_ci collation...likely with strange results.)

Here are the cases I tried: 

  1. MariaDB 10.6.4 / utfmb4 encoding and collation / new installation - Stalled after table tng_repositories once; stalled after table tng_mediatypes once.
  2. MariaDB 10.6.4 / utfmb3 encoding and collation / new installation - Stalled after table tng_repositories once;
  3. MariaDB 10.6.4 / utfmb4 encoding, utfmb3_general_ci collation / upgrade with a TNG 13.0.3 database import - Seems to be working OK (despite the fact that this was actually a mistake on my part)
  4. MariaDB 10.6.4 / utfmb3 encoding and collation / upgrade with a TNG 13.0.3 database import - Works fine.

Just trying to fill in the gaps.

Thanks,

Bill Herndon

 

Share this post


Link to post
Share on other sites
Ken Roy

Thanks Bill,

You are using a different environment than I am since I am on Windows and using WampServer.  Databases were only to test my operating system when I supported and modified the IBM mainframe operating system  :)  so I may not know all the correct terminology. 

Share this post


Link to post
Share on other sites
bhemph

Bill,

I found that there is a problem with the column named "offset" in the save_import table.  That is where the table creation was failing.  I will respond with the same information to the mail list.

Brent

Share this post


Link to post
Share on other sites
Bill Herndon

Brent,

So...a reserved word problem?  If MariaDB's reserved word list is truly a subset of MySQL's then this is going to be an issue for MySQL as well..at some point.

Great find regardless!  I'd be very interested in how you discovered the issue.  MariaDB's logs?  I'm still getting used to Brew and merely trying to figure out where all the log files are--too much deviation from a garden variety CentOS or Ubuntu distribution for me.

k/r

Bill Herndon

Share this post


Link to post
Share on other sites
bhemph

Bill,

Not quite so simple as being in a log or anywhere that a normal user would think to look.  I used xdebug to find that the statement was throwing an error end exiting by putting a breakpoint in tngconnect.  Then to find out that it was repeatedly failing on the same statement in reality, I had to open up the developer tools in the browser and go to the network tab.  Then I had to check the response from each click of the button to see the unexpected ajax response which contained the error.  Maybe I should have known to check the server response before going to the debugging step, but I expected the error handling should have sent a response to the caller that could at least be parsed by the caller.

Brent

Share this post


Link to post
Share on other sites
Bill Herndon

Brent,

Different paths to the same goal.  I'd have started with the server.  Adjust the my.cnf to increase the logging level (...assuming, of course, I can figure out where Brew has stashed the MariaDB logs for the schema in question, and then go combing through the log for the most recent entries post failure.  I've never been able to get interactive debugging for PHP working on macOS.  The Zend engine seems to completely ignore any attempt to connect to set a breakpoint even when it's run in debugging mode.  [sigh]

Thanks,

Bill Herndon

Share this post


Link to post
Share on other sites
bhemph

Bill,

I see now, the SQL error logging plugin must not be installed by default or at least not enabled in my environment.  So the logging level even up to 9 in my.cnf/my.ini and query logging set gave nothing more than "Quit" or "[Warning] Aborted connection 3 to db: 'testtngfamilytree' user: 'root' host: 'localhost' (This connection closed normally)"  Neither of those though are clear enough to understand the query failure though.  I'll just assume that the plugin when installed and enabled would have something more detailed as shown in the example at this point.

I've done the interactive PHP debugging in both Windows and Linux using Visual Studio Code.  I do know that xdebug 3 took a little while to figure out the new settings in order to get it to interact with the VS Code extension properly and stop on the breakpoint.

 

Brent

Share this post


Link to post
Share on other sites
KolbjornBlix
On 8/28/2021 at 5:32 PM, bhemph said:

Bill,

I found that there is a problem with the column named "offset" in the save_import table.  That is where the table creation was failing.  I will respond with the same information to the mail list.

Brent

Brent,

Thinking back I also noticed this "offset" thing while basking with my own TNG 13.0.1 and MySQL 8 / MariaDB 10.6 problems posted here

I didn't see it from any logs (neither mysql og php), but it showed up only when I selected to use the old GEDCOM import method. using the new method the import just stopped silently.

-Kolbjørn

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×