Support

Admin Tools

#30529 Database Issues

Posted in ‘Admin Tools for Joomla! 4 & 5’
This is a public ticket

Everybody will be able to see its contents. Do not include usernames, passwords or any other sensitive information.

Environment Information

Joomla! version
n/a
PHP version
n/a
Admin Tools version
n/a

Latest post by on Thursday, 10 January 2019 17:17 CST

webdevtim
I see both MyISAM and InnoDB database engines referenced in the database and I see both utf8_general_ci and utf8mb4_unicode_ci collation methods listed.

I was going to change the collation to utf8mb4_unicode_ci, and the database engine to InnoDB.

I am aware that there can be problems with JOINS when different tables are using different collations. I am also aware that extensions can break if the collation for that extension's tables is changed.

What would you recommend? It seems to me that if I run into a problem with extensions when changing the collation method, that I can always change the collation back to the original for that extension, or reinstall the extension.

tampe125
Akeeba Staff
Hello,

I can suggest you to change the collation to your tables, to avoid issues about mixed collations (pro tip: Admin Tools can do that for you).
Please be aware that in some cases you could get a failure during the conversion, since the field is too long for the new collation; I'd strongly suggest you to take a full database backup before continuing.
Regarding the engine, unless you have a site with a lot of traffic, that shouldn't concern you. The main difference between MyISAM and InnoDB is how they actually store the data on the filesystem: the first one uses actual files, the latter writes everything in a reserved area of your filesystem. InnoDB supports transactions, but I doubt that any Joomla extension is actually using them.
On the other hand MyISAM supports the Full Index search index, that could be used by the extensions.

I'd suggest you to change the collation only, and leave the engine as it is.

Davide Tampellini

Developer and Support Staff

๐Ÿ‡ฎ๐Ÿ‡นItalian: native ๐Ÿ‡ฌ๐Ÿ‡งEnglish: good โ€ข ๐Ÿ• My time zone is Europe / Rome (UTC +1)
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

webdevtim
This is a test to see if I can post something

webdevtim
After running Admin Tools Change Database Collation, I still see a mixed bag of collations among the tables, like utf8_general_ci, utf8_unicode_ci and utf8mb4_unicode_ci.

I plan to run the following query to create a list of commands that can be subsequently run to change the collations on a table by table basis.

SQL code should go here, but I can't post if I include it.


I As you can see, I want to change the collation for every table to utf8mb4_unicode_ci and the character set to utf8mb4.

According to phpMyAdmin, the database collation is already utf8mb4_unicode_ci and the character set is "MySQL charset: UTF-8 Unicode (utf8) " which is phpMyAdmin speak for utf8mb4, as they define it as "utf8mb4 (UTF-8 Unicode)."

Question 1? Does this look like a good approach for changing the collation on each of the tables? I have to get the SQL query to you somehow

Ouestion 2? Why doesn't "Admin Tools" -> "Change Database Collation" change the collation for every table?

Question 3? Why doesn't "Admin Tools" -> "Change Database Collation" have a drop down to allow me to choose any of the utf8mb4 collations?

tampe125
Akeeba Staff
Admin Tools offers you three options when you change the database collation:

  1. Custom (you have to provide your own collation)
  2. UTF-8, which is utf8_general_ci
  3. UTF-8 multibyte, which is utf8mb4_unicode_ci


Admin Tools will change the collation of everything. It will first try to change the database charset (so you won't have to do this trick again. Usually it fails because you don't have enough permissions).
Then changes the charset of the table and finally of every single column.

If anything fails, it means that it doesn't have enough permissions or an error occurred. Usually it's a problem with the index length, since the index (in UTF8 multibyte) will take more space than allowed to. In that case there's anything we (you and I) can do, since you would have to check if it's possible to shrink the column and then physically change its size.
For the records, Admin Tools will change only the tables of the current Joomla installation.

Davide Tampellini

Developer and Support Staff

๐Ÿ‡ฎ๐Ÿ‡นItalian: native ๐Ÿ‡ฌ๐Ÿ‡งEnglish: good โ€ข ๐Ÿ• My time zone is Europe / Rome (UTC +1)
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

webdevtim
Thanks Davide, I am still working on this, but don't know when I will have time to plunge into this again. The people who manage the organization, that this website represents, do not understand the value of Open Source development. They complain about oligarchy and yet rely on Google to be their champion against oligarchy. I try to explain that Open Source is the best defense against Oligarchy, because it represents a collaborative effort of thousands of developers across the globe, but my words seem to fall on deaf ears. They have decided to use a proprietary CRM for their database management and expect that I will somehow be able to integrate that solution with the Joomla website. The solution they decided to use lives on the Amazon.com EC Cloud and they don't seem to understand that I have no direct hooks into the Amazon database to allow me to update content on the website in real time. The only mechanism that is available to sync data on that proprietary database with the Joomla website's database is import/export and that means that someone needs to be doing that at least once a week and preferably every day.

So I guess you can close this ticket, because I am not sure when I will be able to get back to this, sorry to say.

tampe125
Akeeba Staff
I'm sorry to hear that, but thank you for the update!

Davide Tampellini

Developer and Support Staff

๐Ÿ‡ฎ๐Ÿ‡นItalian: native ๐Ÿ‡ฌ๐Ÿ‡งEnglish: good โ€ข ๐Ÿ• My time zone is Europe / Rome (UTC +1)
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

webdevtim
Thanks Davide, I think the work you guys are doing if far more important than the work Larry Page at Google is doing. I will quote Chamath Palihapitiya, one of the founders of Facebook, who said that Facebook is destroying community, which is why he left in 2011 and formed the company Social Capital. While I laud his decision, I still think that Open Source is the better way to build community, and if people like Chamath would would embrace Open Source, we would be making much greater strides in solving the myriad of problems that face all of us today.

I got some money from this organization to keep struggling some more. Unless I can get them to adopt CiviCRM as their user management platform, that I can install in an instance of Joomla, Drupal or WordPress, I am not sure what I can do for them. In order to provide custom solutions I have to have access to the code, and I have to have access to the database.

Do you have any arguments for Open Source that I am not getting here?

tampe125
Akeeba Staff
There are a lot of useful and interesting thoughts on the community point of view, but when we're talking about business, at the end of the day it only comes down to the same question: What's the best for me?
Because management is not interested if the tool you're going to use is eco-friendly-locally-developed-gluten-free-zero-emissions, they only care about price and quality: if the perceived quality justifies the price, then they're good.
So the ear we have to talk to is the one about reducing operating costs and easing everyday operations.

First of all, they will state that if is the software is free, how are they still in business?
Let's clear a common misconception: Open Source Software doesn't mean you don't have to pay for that (you just paid for Akeeba software that is open source). That's a linguistic problem coming from the word free, that's why you usually found the statement free as freedom of speak or free as free beer to eliminate any doubts.
Side fact, Linux is free, but the server edition is not free of charge, ask that to the hosting companies...

Then they're going to object about business continuity: I want to go corporate because I know they will be there in the future, I don't trust a bunch of volunteers.
This is a moot point, because we all obey the laws of business. If what you're spending (time, money...) doesn't return enough value back, there's no point in continuing. This happens for corporate (they're in for the money) and for volunteers (they're in for the satisfaction to see your product being used). There's plenty of stories about famous products being discontinued because the holding company was sold or simply went bankrupt (fun website, all the projects Google killed)
However, Open Source Software has a nice thing: the code is completely open. This means that if Company X stops selling it, Company Z can get the code and continue from where it stopped. If you're really desperate, you can keep using the same product and maintain it by yourself. It's terrible, but you can do that if and only if you have access to the source code.

Finally security, because nowadays everything is about security.
Inspecting and debugging closed applications is possible but it's hard. This limits the amount of eyes that could take a look at it and find potential bugs and security threats. On the other hand, when you have the source code, you can run a lot more of tests and inspections and it's way easier to find bugs.

As closing note, for what is worth, the global trend is to move to Open Source. Even the Mother Of All Corporates (also known Microsoft) is moving with an impressive pace in that direction. They started to release their own code (Visual Studio went Open Source), they started to publish debugging info to help inspecting the closed source, they started to contribute to Open Source projects (they submitted a lot of changes to the control version tool named Git); finally they are going to ditch their browser engine to adopt Chromium (Open Source browser engine).

I hope I was able to give you some bullets to spend :)

Davide Tampellini

Developer and Support Staff

๐Ÿ‡ฎ๐Ÿ‡นItalian: native ๐Ÿ‡ฌ๐Ÿ‡งEnglish: good โ€ข ๐Ÿ• My time zone is Europe / Rome (UTC +1)
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

System Task
system
This ticket has been automatically closed. All tickets which have been inactive for a long time are automatically closed. If you believe that this ticket was closed in error, please contact us.

Support Information

Working hours: We are open Monday to Friday, 9am to 7pm Cyprus timezone (EET / EEST). Support is provided by the same developers writing the software, all of which live in Europe. You can still file tickets outside of our working hours, but we cannot respond to them until we're back at the office.

Support policy: We would like to kindly inform you that when using our support you have already agreed to the Support Policy which is part of our Terms of Service. Thank you for your understanding and for helping us help you!