Fun and games with MySQL

[Retro]

Today, I installed the latest MySQL 8.0.30 on my home PC for testing and development purposes.

I changed the default table case setting (variable "lower_case_table_names") from lower case, to Preserve Given Case as in the screenshot below, since I figured it would be better to allow mixed case table names. However, when I connected to my database using Workbench, I got a pop-up error that my computer doesn't fully meet the requirements of this setting, which will generate errors. This is no good as I'm going to be installing XenForo and other forum software on it, so I wanted to set it back to default without having to uninstall and reinstall the entire MySQL product.

Cue lots of reading up and frantic Googling on how to access these MySQL system variables and change their values. Finally, I figured it out (really complex product). Ran the command... variable is read only!

Turns out that some time back around version 8.0.11 apparently, MySQL no longer allows one to change this setting without uninstalling and reinstalling the whole product! It's all to do with data integrity apparently somehow, I dunno. What a PITA, I'm sure it's not so critical, especially on a brand new, unused database and they could allow the database admin who can be assumed to know what they're doing to take the risk with an informed decision. I couldn't find a physical configuration file for it, although it's apparently there and encrypted according to the manual, which is likely where it lives.

Sod it, that's enough hassle, so I've uninstalled it and am reinstalling it as I write this. Quite unsatisfying to jump through all these hoops only to be forced into doing the thing I was trying to avoid in the first place. Don'tcha just love design decisions that are a real pain when one false move is made? Answers on a postcard to the usual address. At least I learned a bit more about MySQL, so the time and effort is not completely wasted.

 

[Arantor]

This shows how well I'm keeping up with MySQL; I didn't realise this was an option. I have never made a database in MySQL where either the database or its tables had mixed case.

I used to be of a school of thought where, back before MySQL could do foreign keys, I'd mimic this with my ID columns having uppercase names, e.g. ID_MEMBER, ID_MSG, ID_BOARD and all other columns be either camelCase or snake_case depending on what others in the project favoured. But it's been many years since that was a thing.

Actually it's funny, the very reason I didn't adopt a mixed-case naming for tables is because I figured this would be messy between Windows and Linux where the former pretend that MyTable.frm and mytable.frm as files in the same folder would be the same thing. (You can, if you are unlucky or twisted, produce files with different cases. Windows is not impressed in this situation if you manage to actually produce this, but then again Windows gets very upset about the *darnedest* things, e.g. where it has issues with specifically named files that predate MS-DOS for conventions.)

 

[Retro]

I've never liked that case sensitivity in Linux. It makes commands difficult to type and can cause confusion. Much better to ignore case as Windows does.

 

[Arantor]

It’s something of a nightmare for non English languages though because the rules of what is folded for caselessness vary between environments.

Note that in other languages the Windows folders can be translated, e.g. C:\Program Files is C:\Archivos de programa in Spanish but I’m not sure if they accent some of the letters in other languages because that truly would be a logistical nightmare.

 

[Retro]

I'm surprised that system folders would get renamed for a different language and have never heard of it. I'd have thought it would cause chaos.

 

[Arantor]

If you write things properly it’s fine. Spoiler: surprisingly much software is not written properly…

 

[Retro]

While it's clearly possible, I don't think it's a good idea for system folders which are supposed to be be constants.

 

[Arantor]

But they’re not constant. They haven’t been actually constant since the 90s. They weren’t even perfectly constant *in* the 1990s.

The correct approach was *always* to ask Windows for the correct path rather than treat it as a constant because it can and has changed over time. E.g. what is now C:\Users in Win10 used to be C:\Documents and Settings. And especially if you're in a networked environment it’s very likely some of these folders won’t even be local but maybe local-mirrored.

Honestly, third party devs have been doing a lot of this stuff wrong since Windows for Workgroups 3.11.

 

[Retro]

Yes, I know about the system variables, but that doesn't weaken the case. The fact that devolopers get lazy and use direct paths instead actually makes for a stronger case.

Consider the scenario where the app is no longer supported and the company out of business. It's a really important app for the user, but because the folder name has changed in a future version of Windows or they've changed language, the app now breaks, becoming unusable and the user is left high and dry.

I'm actually one of those users, but thankfully the app still works.

 

[Arantor]

Which is why the canonical way for the last at-least-20 years has been an API that is still maintained for an app to ask Windows where these things are. Which is also explicitly not the system variables.

If you use the API it honestly doesn’t matter if things get moved out from under you as Windows changes/adds new things/moves things around because the API just tells you the new location. My own stuff written on Win98 works fine on Win10 for this reason - the Windows API understands how to manage this and if you use the tools given to you, things tend not to break.

It‘s precisely the hardcoding and assuming that is the problem.

 

[Retro]

I know, which is why I referred to lazy developers. Yes, they should follow the rules, but many times they don't, unfortunately. It's therefore still prudent to try and change these things as little as possible to help with edge cases like this. Where a change must be made, then so be it. Changing the language of a system folder for someone's convenience isn't one of them.

 

[Arantor]

But that’s just it - that same argument broke down with the *English* version of C:\Program Files as well. If you drop an old application in there it will not work correctly even if the path was historically that, because of the 64 bit vs 32 bit editioning. See also the system32 folder vs the WoW64 folder (which is the *new* home of the *32-bit* versions, with system32 holding the 64-bit versions)

C:\Program Files stopped behaving the same way as of XP compared to pre-XP but most people didn’t actually get bitten by it until Vista which is why Vista got a lot of the rep it did…

The whole point here is that developers should *never* assume anything, but ask the OS - then it doesn’t *matter* if it’s a different language or if it’s moved for other reasons. A properly written app *doesn’t have to care*, that’s the point.

 

[Retro]

I was thinking of using that very example about the shift to 64-bit to support my argument, quite ironic really.

If you carefully read what I said, it's ok to change the name where it's unavoidable. This is one such case. Again, changing the name for someone's convenience isn't justified because of these problems. Put another way, changing system folder names requires a really strong business case.

The argument isn't whether those developers should be playing by the rules or not, we know they should. But this isn't a perfect world, so certain mitigations are reasonable. Even if they did do everything perfectly, it's bad practice to change a system folder name without reason.

 

[Arantor]

Which is why Microsoft put in the APIs decades ago so that they could just do whatever

Honestly, though, if you want to see staggeringly bad design decisions for renaming things, see Excel formulas in non-English…

 

[Retro]

@Geffers this is my rant thread that I was telling you about.