Mysql Workbench Could Not Read Contents of

Database

My Journeying to Connect MariaDB to MySQL Workbench

My boss asked me for an ER diagram from a schema saved in MariaDB.

Photo by Hilde Demeester on Unsplash

I happened to have to extract the Entity-Human relationship diagram starting from the SQL schema. I usually employ XAMPP to work in SQL so I told myself to try using the Designer tool provided by PhpMyAdmin. In truth, the outcome was non great. My database had over twenty tables and the Designer showed them all stuck on top of each other. Furthermore, the PhpMyAdmin Designer is a very bones tool, which does non allow you to automatically align the tables to brand them more readable.

However, since thou y boss had asked me to produce this blessed ER diagram, and not have the want to manually motility the tables one by 1 in the graph, I idea nearly using MySQL Workbench. I remembered from distant reminiscences that MySQL Workbench provides a very powerful tool for the automated conversion of SQL schemas into ER diagrams. The tool in question is chosen Reverse Engineering science.

And here the problems arose. Getting MariaDB (database provided by new versions of XAMPP) to talk with MySQL Workbench was not a very simple functioning. But I want to eventually make information technology.

And I want to tell yous how I succeeded.

Kickoff of all, I had already pre-installed on my computer (a Mac Os) the latest version of XAMPP (7.3.33 at the time of writing this article), so I had no desire to downgrade to a previous version with MySQL DB installed, as suggested in some posts on StackOverflow.

Kickoff Attempt 😱

I downloaded the latest version of MySQL Workbench (viii.0.27 at the fourth dimension of writing this article) and installed it.

My first endeavour was to have MariaDB speak direct to MySQL Workbench. I started MariaDB from the XAMPP command panel:

Image by Author

When the server is agile, the light adjacent to its name turns green. Then I started MySQL Workbench and clicked on the + sign to create a new connectedness:

Epitome by Author

I added the connectedness parameters, simply when I tried to press the Connect button, miserably the connection failed, every bit it required configuring SSL.

Second Attempt 😢

So I googled a flake and institute how to configure SSL in Maria DB. I found a fairly complex procedure described in this article. But to tell the truth, I got a little scared. And so I gave up.

Wandering around the connection configuration tabs of MySQL Workbench, however, I discovered the existence of a magician (under the SSL tab) that allows you to automatically generate keys and certificates.

Image by Author

By following the wizard procedure, it is possible to generate the various certificates. I take put the checkmark in the Use Default Parameters item.

Once the procedure is completed, you lot can access the files by clicking the Files push, located immediately beneath the Sorcerer button. I copied the files to my local folder so moved on to the Maria DB server configuration.

From the XAMPP menu, I selected the MySQL server, and so I clicked on Configure, and so on Open Conf File, and at the bottom, I pasted the text that is inside the my.conf.sample file, generated together with the certificates. The text in question is this:

          [client]
ssl-ca=<directory>/ca-cert.pem
ssl-cert=<directory>/customer-cert.pem
ssl-key=<directory>/client-fundamental.pem          [mysqld]
ssl-ca=<directory>/ca-cert.pem
ssl-cert=<directory>/server-cert.pem
ssl-primal=<directory>/server-key.pem        

I replaced <directory> with the path to my directory. I restarted the SQL server, which started quietly.

I and so went back to MySQL Workbench and created a new connectedness. This time I have configured all the required SSL parameters, in the appropriate spaces indicated in the SSL tab.

This time I endeavour the connection and … the MySQL Workbench gives me a Warning (come across side). I click on Continue Anyway and everything seems to work fine.

Exactly it seems.

I tin can meet all databases stored on my SQL server, but when I attempt to access individual tables, it tells me that it is unable to load them:

Tables could not be fetched — Error loading schema content

Sigh, even the 2d attempt, unfortunately, failed miserably …

3rd Attempt 😌

But I don't lose centre. I try to google once once again. And this time I observe another StackOverflow thread where it strangely says that MySQL needs to be updated.

Having nix to lose, and always having in heed my dominate who wanted that ER diagram at all costs, I open up a last and run this command, in the hope that it doesn't destroy everything:

          sudo /Applications/XAMPP/bin/mysql_upgrade        

Something is moving. Information technology looks like something is being installed. Ok, it's done. I restart the SQL server and everything works fine.
Then I become back to MySQL Workbench, I reconnect to the SQL server and now, I can finally encounter the tables! Wow! I'm happy!

I just accept to launch Contrary Engineering and that'southward information technology!
But no. The system crashes.

Nooo! It looks like an old odyssey. Simply I don't give upwards, I can practise information technology. I have to do.

I always call back well-nigh that ER diagram that I have to bring to my boss.
In truth a chip of demotivation comes to me … perchance, I recall to myself, I could reuse the PhpMyAdmin Designer.

So, notwithstanding, I imagine myself moving all the tables by hand… no-no. I try to search again.

Fourth Attempt 😄

I keep googling non-end until I encounter this StackOverflow article, where information technology says that the Contrary Engineering crash effect is a known bug on Mac OS of the latest versions of MySQL Workbench. And so if you lot install an older version, 8.0.21 at the latest, the trouble should be solved.

Then I try. Uninstall the latest version, download viii.0.21, and install it. I try Reverse Engineering science and everything works correctly! The tables are all perfectly aligned and require no manual effort to arrange them 😎

That's all folks!

For the record, I tin can't put a screenshot of the ER diagram produced here, for privacy reasons. Instead, I put the screenshot of a uncomplicated ER diagram that produced my Opposite Engineering:

Image past Author

Summary

In this article, I described my risk in trying to connect Maria DB with MySQL Workbench, to have advantage of the Opposite Technology tool provided past MySQL Workbench.

Information technology was a long process, merely I finally managed to produce my ER diagram. Simply this question remains:

Is it worth spending the time to make an automatic tool working or is information technology improve to carry out a transmission operation?

I honestly almost always prefer to invest some time to make an automatic procedure piece of work. What do y'all think of it? If you want, experience gratis to get out a comment.

If you take come this far to read, for me it is already a lot for today. Thank you! Y'all can read more about me in this commodity.

Related Manufactures

garnettuport1953.blogspot.com

Source: https://towardsdatascience.com/my-journey-to-connect-mariadb-to-mysql-workbench-2d7e599a8a26

Share this post