http://stackoverflow.com/questions/8550789/where-to-change-the-value-of-lower-case-table-names-2-on-windows-xampp

Ahhh!! Fantastic, thank-you! Will try at once and let know if that fixes things up...

(BTW, clicking on the link you posted goes to GitHub and gives a 404. I had to copy-paste the link to have it work. No big deal, but I think you would like to know...)

I'm so glad I could help! :)

I too think we should discuss about enforcing lowercase prefixes, because some hosting services have mysql configured to enforce lower case in table names.

@EmilMassey

So, what happens is that after applying to my.ini the fix you suggested, i.e.:

[mysqld]
lower_case_table_names = 2

a new Joomla installation with an uppercase prefix does work (at first sight and having checked only the com_postinstall related issue). I still have issues restoring the Akeeba Backup containing the uppercase prefix, but this is out of scope here.

I anyway agree with you that an official stance on this problem should be taken, so I'm going to leave this issue open, for the time being.

Personally, and not having thought too much about it, I think it would be fine to enforce lowercase prefixes (at least for new installations), but I'm unsure if there could be any BC related issue or even some danger (unlikely, but it could happen that someone have the bad idea of overlaying two Joomla installations on the same DB, differing only by the prefix case...)

Thanks again!

Sorry, no issues even with the restored site: I just used a wrong password, silly me!

Joomla's policy should be that it works with whatever features the supported database engines make available. There should be minimal if any restrictions on that, therefore a decision to enforce lowercase table names should be voted ?

i use always lowercase (linux habit) but i agree with @mbabker

Normally I too use lowercase, and the few times I didn't, I've been bitten:

• this time, with the restoring issues I faced
• and apparently on a previous occasion when I opened an issue about being unable to hide the post install messages and nobody else could replicate, not even me on following tests (I really don't know how today I had the enlightenment that the the two things could be related... just luck, I guess)

I'm too against any kind of enforcement and thus I tend to agree with @mbabker, but if we leave things as they are, I think we should make sure that everything works independently of the prefix case and the state of the involved my.ini setting.

Most components/extensions do work even with the uppercase prefix and the my.ini setting missing, so I think we should make sure that others too do work in those conditions. I'm thinking of com_postinstall and Akeeba Backup (and hence I'm evoking @nikosdion in here...), but there might be other problematic extensions.

The least we can do, I think, is to put a warning message under/over the prefix inbox, advising against the usage of uppercase prefixes that could lead to unexpected behavior under some circumstances.

It should only be an issue if the underlying database server is configured a certain way, which I'm not sure could be reliably detected in the Joomla install application, so I don't think a warning here is necessarily warranted. It should definitely be documented on a best practices page though.

I get the feeling this is a very specific thing to specific platforms/environments. Per the MySQL documentation the default behavior is to store tables as specified and that references are case sensitive. Therefore I'd argue any code that enforces rules such as forced lower case table names (to include the prefix since that is part of the table name) UNLESS the MySQL lower_case_table_names system variable is set to 1 includes an arbitrary limitation.

Interestingly enough, per the MySQL documentation apparently MySQL builds default the lower_case_table_names variable to 1 for Windows and 2 for OS X.

It might very well be for the benefit of the user that this behavior is happening, but at a high level it's inconsistent with at least MySQL's configuration (haven't looked at others since we aren't talking about them and apparently few if any care about supporting them anyway).

Sergio,

Stop spreading FUD about my software. You do not even understand the issue. Akeeba Backups works PERFECTLY FINE on uppercase prefixes as long as you have a properly configured database server.

Akeeba Backup IS NOT liable for the wrong results of SHOW TABLES and/or SHOW CREATE TABLES returned by MySQL servers on case incentive file systems for MyISAM tables. This is a MySQL issue.

Furthermore, my software uses Joomla's own JDatabaseDriver to get the list of tables in the database and check whether a particular table already exists or not. The fact that Joomla returns the wrong results is not a bug in my software, or even Joomla. It is a MySQL bug.

Finally note that making the table search case insensitive in my code would not only have a dramatic performance impact but is also WRONG AND A BUG OF ITSELF. Properly configured servers would allow tables with prefixes foo_, Foo_ and FOO_ to co-exist. By treating them the same we end up considering a set of tables not used by the current site as a valid set of tables for said site. On everything except your broken server this is A GLARING BUG. If you still cannot see why it is a bug I ask you to never submit another issue on Joomla or elsewhere until you get a grip on database management.

To sum it up, there is no bug. There is a misconfigured server of yours. Fix your server and stop spreading FUD about my software.

FWIW other extensions are affected to. Typically you'd only bump into that if you tried installing an extension when one or more of its tables already exist. The CREATE TABLE IF NOT EXISTS would fail on your broken server because of MySQL's bug (assuming a MyISAM table). You don't bump into that because you are using Akeeba Backup to save yourself from these situations. You're welcome.

Sorry if I'm repeating myself with this, but I want to be sure I made my point clear (because of my linguistic uncertainties):

If we do not enforce lowercase, then we need to decide what Joomla behavior should be in a - let's call it this way - ill configured environment (uppercase prefix and "lower_case_table_names = 1", which apparently is the default under Windows):

• must work
• will not work
• undefined

@nikosdion
Spreading FUD about your software is the last thing I want do.

I just observed that issue in your software and in some core components too.

That does not affect at the minimum level my judgment of your software, which is simply: excelent

Joomla can't behave differently based on this environmental difference. The fix is to ensure the database server is configured correctly.

The configuration value, lower_case_table_names, is one set at the server bootup and cannot be changed after the fact.

At best, all Joomla can do is check after you have inserted your database credentials in the install app if it is a MySQL connection, query if lower_case_table_names === 1 and raise a warning at that point. That is the ONLY thing Joomla can do.

Any changes in the Joomla database API opens a pandora's box of issues Nic already pointed out.

@joomla/cms-maintainers please close this issue. There is no action that can feasibly be taken to address this server configuration issue within the scope of a Joomla application.

If you think it is better to close this, no problem: I'll do it myself, especially as I really don't want this to be taken as something against Akeeba, which is something that can't be more afar from my intents.

@nikosdion
Disregarding your offensive words, which I think are caused by a huge misunderstanding, it is absolutely true what you say, that in a properly configured environment everything is working just fine, but you must consider that unhappily that's not the default under Windows.

Sergio, I'd really appreciate it if you had let me replied to your email before posting something public. I understood you don't understand the issue – I just wanted to rest for a day after three odd months of working seven days a week. So much for that :(

When you have a MySQL server on a case insensitive filesystem where lower_case_table_names is 0 you will end up with severe problems, per MySQL's documentation.

If it's 1 we have a major issue: MySQL reports all tables with a lowercase prefix, e.g. foo_bar, instead of with the actual prefix e.g. FOO_bar. Why is this bad? Several reasons. Let me outline some for your use case:

• Components need to know if a table already exists or not e.g. to detect whether old data is still present and do a migration instead of a clean install. The only way to do it is ask Joomla to list all the tables and check if the table in question, e.g. FOO_bar, exists. Since MySQL reports to Joomla that the table name is foo_bar the extension thinks the table is not there. If it tries to create the "missing" table MySQL will say that the table does exist and throw an error. Joomla makes it an exception which bubbles up. In the case of Akeeba Backup we report a 403 Forbidden upon detecting the exception because Some Shit We Didn't Expect Has Happened so clearly you shouldn't have access to the component.
• When you're backing up your site we need to replace the prefix with #__ so you can change the prefix on restoration. If, however, MySQL reports the prefix as foo_ and Joomla is configured with prefix FOO_ you end up with a backup where you can never change the prefix. Even worse, restoring that backup on a case sensitive server (e.g. Linux) will result in a broken site: Joomla looks for FOO_bar but the restored table is foo_bar.
• It gets worse if you have foreign key relations. Since the detected relations reference a table that doesn't exist (FOO_bar doesn't exist) you will get an unrestorable backup.

The last two issues also affect backups with phpMyAdmin and mysqldump. Even MySQL's manual (see above) has a warning about that when they say:

Use lower_case_table_names=1 on all systems. The main disadvantage with this is that when you use SHOW TABLES or SHOW DATABASES, you do not see the names in their original lettercase.
Yup, that's the issue. SHOW TABLES is borked on your server exactly because of the wrong setting in MySQL.

Then it says that you have another alternative:

Use lower_case_table_names=0 on Unix and lower_case_table_names=2 on Windows. This preserves the lettercase of database and table names. The disadvantage of this is that you must ensure that your statements always refer to your database and table names with the correct lettercase on Windows.
HOWEVER, it goes on to say that this won't work on InnoDB tables. Joomla uses InnoDB tables and my understanding is that all core tables will move to InnoDB.

This poses a major conundrum. There is absolutely no MySQL configuration which allows you to use uppercase letters in table names, including table name prefixes, which will allow you to transfer a site between Linux/UNIX and Windows or back. That's right, you can't use it. It's the official MySQL documentation stating that.

Hence the advice I gave you in my email:

do not use uppercase table prefixes

Hence the advice I gave you in my email:

do not use uppercase table prefixes

... and hence my questioning here if, by chance, we should, closing our nose, enforce lowercase table names. Nothing more than that.

@nikosdion
... and I'm really sorry having disrupted your Saturday, Nic, sincerely, but I honestly don't feel guilty of having personally attacked you, your company or your software. That wasn't in my intentions and I really don't understand how you can have taken this way.

When someone writes "I'm thinking of com_postinstall and Akeeba Backup (and hence I'm evoking @nikosdion in here...), but there might be other problematic extensions." the implication is that my software is "problematic". That's the part I took offense with. I had already explained to you that the problem is not with my software, it's with MySQL itself. Hopefully my super extra long explanation on why MySQL itself says that using an uppercase prefix won't fly well when you're transferring your site between Linux and Windows will satisfy you more than the troubleshooting documentation of my software having the same advice in more concise form.

Yes, I understand, but problematic doesn't means buggy in any way, in my dictionary.

I may have a wrong interpretation of "problematic", but as you perfectly say in your signature in the Akeeba ticketing system:

"Please keep in mind our timezone and cultural differences when reading my replies. Thank you!"

... and hence my questioning here if, by chance, we should, closing our nose, enforce lowercase table names

No. There are workarounds to an extent in Joomla to deal with a variety of server configurations, but this is something that should not be enforced by Joomla. There is a fine line between workarounds that deal with server related issues and workarounds that are arbitrarily enforced which impose limitations. This one is an arbitrary workaround that imposes limitations.

A best practice is to always use lowercase table names (to include the prefix which is part of the table name). The reasoning here only discusses behaviors with the MySQL engine. Remember also that Joomla (the application) claims support for MySQL, PostgreSQL, and SQL Server database engines and has a DBAL that provides support for the above plus Oracle and SQLite (and possibly other PDO supported drivers if someone were to create the appropriate subclass of JDatabaseDriverPdo).

So any change that would be made would affect ALL database engines; can you validate the behaviors regarding character casing against all of those database engines and ensure that the enforcement of all lowercase table names does NOT cause unexpected behavior in ALL use cases? Until you answer yes, there is no code change to be made and IMO no further discussion to be had.

@smz https://www.dropbox.com/s/1vwflw633ugg3fp/Screenshot%202016-06-25%2018.36.19.png?dl=0

I could go in detail about the etymology of "problem" but it suffices to say that the word is Greek (πρόβλημα) and exists unchanged in several languages including English (problem), French (problème), German (Problem) and even your native language Italian (problema). In all languages it has exactly the same bad connotation of something bad that has to be overcome. "Problematic software" is, therefore, "software that causes a bad situation that has to be overcome". That's the definition of buggy.

Considering that the Italian word "problematico" and the English word "problematic" are so close together even to the level of individual phonemes please do not attempt to put it down to cultural differences. You know I'm smart enough to see through this :p

You made an unfortunate choice of words and I defended myself and my software against them. You didn't mean what you wrote, it's fine, let's move on. As far as I'm concerned the truth is restored and I can continue enjoying my otherwise lazy Saturday. Ciao :)

@mbabker
Michael, I may be thick, but wouldn't a strtolower() in the installation form, possibly together with some (JS?) enforcing that the field is also displayed lowercase, give a reasonable guarantee that all new installations use a lowercase prefix?

That's an arbitrary rule being imposed by Joomla. And it's inconsistent. How do you deal with existing installations? Or are you claiming that it is acceptable that different 3.x releases of Joomla has different database schema rules regarding table names?

You either enforce the lowercase for the entire Joomla API, or you don't do it anywhere. There is no middle ground unless you want to write all of the documentation explaining why Joomla 3.x and earlier allow table names matching X regex while Joomla 3.y and later allows only table names matching Y regex but the core platform did not migrate existing installations to match the new rules.

This is borderline as asinine as trying to enforce / separators needlessly.

You can certainly warn the users about prefixes with uppercase letters but you cannot (and should not) enforce a lowercase form. On case sensitive filesystems uppercase prefixes are just fine.

Also, it's futile. In the end of the day they could simply edit configuration.php or just not use Joomla's installer: there are hosting-level installers like Fantastico, things like MAMP Pro and Web Platform Installer or even Akeeba Backup.

Also what @mbabker said. It makes no sense. Don't break people's sites for no reason. The ONLY problem you'll ever have is trying to transfer a site from Linux to Windows and keep the uppercase prefix. Since most people happily use whatever the Joomla! default is (predictably lowercase) your problem is not widespread at all. You are a subset of a corner case. Everyone fitting this narrow descriptions ends up asking me for support – and I've already documented the commandment: Thou Shalt Not Use Uppercase Prefixes On Windows.

@nikosdion

No, Nic, have al look at http://www.treccani.it/vocabolario/problematico/, the most authoritative Italian dictionary, says that the main meaning is "Che costituisce un problema", "which is/has a problem", and that's irrefutable: some extensions have a problem under those circumstances, other don't. And that's said without any implied judgement of where the problem lies.

OK, I get it. You are problematic.

What? You took offense because I said that you have a problem (implying your head doesn't work right)? You shouldn't. You just told me that saying that something is or has a problem is not offensive.

See how this kind of sophistry is pathetic? Anyone with an IQ above room temperature can see right through it.

Anyway, this is a non issue and I'll stop wasting my time with it now.

Indeed. "Problematico" may also mean "dense", "difficult", like a marvellous piece of music/literature of difficult execution/reading.

How would you say that a software X has a problem (something doesn't work) under conditions Y, without implying that's software's X fault?

Why continue offending? Have I used bad words against you or anybody else?

This issue is closed. Stop feeding the Italian? the troll. He has never
contributed anything more than the xxxxxxxxxxxxxxx
On 25 Jun 2016 5:17 p.m., "Sergio Manzi" notifications@github.com wrote:

Indeed. "Problematico" may also mean "dense", "difficult", like a
marvellous piece of music/literature of difficult execution/reading.

How would you say that a software X has a problem (something doesn't work)
under conditions Y, without implying that's software's X fault?

Why continue offending? Have I used bad words against you or anybody else?

—
You are receiving this because you are on a team that was mentioned.
Reply to this email directly, view it on GitHub
#10928 (comment),
or mute the thread
https://github.com/notifications/unsubscribe/ABPH8bIi6h1xBtVFz4yLwPeahRAWNHwmks5qPVSLgaJpZM4I-Uug
.