#21578
#21583

Again Joomla is more restrictive than mysql states here https://dev.mysql.com/doc/refman/8.0/en/identifiers.html :-(

Identifiers may begin with a digit but unless quoted may not consist solely of digits.

where were the changes. liquid web does the same thing with customer numbers. This has to be fixed.

found it and got it to install

@nikosdion With your experience, Was it you who said that numbers at the start of database names causes some issues?

AFAIK this is an artificial requirement added by Joomla! because of the way automating quotation of identifiers works inside the query classes of the Database package. I'd say do not touch it. The impact of automatic identifier quotation failing is far more serious than the mild annoyance of not being able to use a digit as the first character of the database name or the table name prefix.

mild annoyance

The reporter is saying that webhosts like "liquid web" create and provide databases for their customers which ONLY start with integers ... which means Joomla is now distributing a product that, because it doesn't handle MySQL standard identifiers correctly, means that Joomla cannot be used on whole web hosts :(

If we could identify the automatic identifier quotation failing bugs maybe we could take a look at those instead of just saying "Hey Liquid Web, and 1000s of other webhosts - although your table names meet the mysql standards for identifiers, change your ways, or else Joomla cannot be used on your webhost" :-)

OK, I see. I don't have the time to look at the code right now but I can at least tell you how you can approach this.

Run a few manual tests with a database whose name starts with numbers and a prefix whose name starts with numbers. If it works (which is likely, the problem I remember is from way too many years ago) then the obvious fix is removing the limitation, next paragraph notwithstanding. If it doesn't work, fix the Query class which does the automatic quotation.

Another thing I'd check is whether the installer applied this limitation because of a PostgreSQL and/or Microsoft SQL Server limitation. In this case we might want to modify the check depending on which database server is being used.

This should be about 2 hours of work and you're more than competent enough to write such a patch. Ping me on your PR so I can take a look.

I was hoping to find some documented issues of people having an issue with this already as the underlying reason for this change being made unilaterally in Joomla, I'll take a poke and see what I can find.

Thanks for your experience and time @nikosdion

This requirement pre-dates the history available on github as it is at least 10 years old

This "Joomla" requirement though is in direct opposition to the documentation from MySQL which clearly allows database names starting with an integer. Therefore maybe Joomla could ease up and relax the restrictions.. unless we can find out WHY the restriction is there.

This requirement pre-dates the history available on github as it is at least 10 years old

Not sure what "this" you re referring too, so lets assume this = "The requirement that a db name doesnt start with an integer"

I'm sorry @brianteeman but, the evidence contradicts your statement

You (@brianteeman) added this restriction unilaterally in 4.0-dev with commit d8246ac#diff-b87e556aecb5fcc183e14fa970cb19a5

Before then Joomla did not restrict the ability to create a database table starting with an integer.

This (the inability to have a database starting with an integer) is a new restriction in Joomla 4.

Like @elerion64 states, there was no issue in Joomla 2 or 3 series which only checked if the name was empty, check the length of the name, Check for asc(00) Null in name, Check for spaces in beginning or end of name, forbid . / or \ or NULL. etc...

see: #15583

I need more coffee - I read it as referring to the database table prefix not the table name

Because I am old and have a good memory I can say that the reason for that restriction was to

1. overcome a limitation in mysql that you can not/could not have a database name made up exclusively of numbers
2. postgresql databases cannot begin with a number

ok so the solution then is to remove this restriction for mysql and make it a postgres only restriction in the next code block to check for integers at the start . ?

And maybe add an additional check in the mysql section to see if the db name is an integer (ie. all numbers)

That would fix this and the #15583 in one sweep.

Because I am old and have a good memory I can say that the reason for that restriction was to

1. overcome a limitation in mysql that you can not/could not have a database name made up exclusively of numbers
2. postgresql databases cannot begin with a number

yes, but
You can overcome both of the above restrictions, if
the database name is name-quoted properly in the queries that use it

i did not know if quoting will not work in older DB versions, will it not ?

It seems that this check was indeed added for PostgreSQL. See their documentation.

Similar but different rules apply for SQL Server -- see under "Rules for Regular Identifiers".

The correct solution is to have different checks per database connection type and enforce them server-side.

You can overcome both of the above restrictions if
the database name is name-quoted properly in the queries that use it

I dont think that is correct - postgresql databases cannot begin with a number regardless of quoting :)

The correct solution is to have different checks per database connection type and enforce them server-side.

Like I said in #23041 (comment) :-)

@ggppdk No amount of escaping can overcome hard restrictions imposed by the database server. See the linked articles.

@PhilETaylor Well, that was my second reply on this issue which predates yours by an hour and a half so, yeah, I would say we're in full agreement here :)

Actually, guys, we're all too busy thinking "how" that we forgot to ask "why". Joomla's installation requires the database to be already created. Clearly, the user cannot create a database with an invalid name. Therefore the check for the database name in Joomla's installer is pointless. We can simply remove it.

The only check we need is for the prefix which actually requires a different algorithm for each database.

Joomla's installation requires the database to be already created.

Are you 100% sure? ;-)

I often install Joomla locally with root mysql user and Joomla creates the database for me :)

Its a bug. I created the bug when trying to fix the issue that @PhilETaylor reported of no validation. I confused the prefix requirements with the db requirements when I wrote it.

Of course that wouldnt have happened if @PhilETaylor had written the fix for the issue he reported or tested the fix for the issue he reported.

Of course that wouldnt have happened if @PhilETaylor had written the fix for the issue he reported or tested the fix for the issue he reported.

Once again with the personal attacks. If you dont appreciate reporting of issues, without code, by developers then I suggest you go elsewhere...

@PhilETaylor If you are using root to install Joomla! on a live site you are suicidal at best. For local tests, sure, if you KNOW what you're doing you can use root creds for a temp site. However, if you know how to do that you already know how not to use invalid identifiers. Even if you do use an invalid identifier MySQL will spit an error. So no need to check anything.

The prefix is a different issue because failure to use a valid identifier there will lead to error messages which can be misleading to the user. That's the only thing that needs a validation.

As a side note to the dispute whether developers should write code for the issues they report: yes, we should. After all, who the heck is developing Joomla or FOSS in general if not the people who are scratching their own itch?

Once again with the personal attacks. If you dont appreciate reporting of issues, without code, by developers then I suggest you go elsewhere...

I already have - thanks to you

As a side note to the dispute whether developers should write code for the issues they report: yes, we should. After all, who the heck is developing Joomla or FOSS in general if not the people who are scratching their own itch?

Yes but different hats for different days. When you are busy doing something else and an issue arises, its best to log it there and then rather than ignoring it until you have time to debug, develop and PR yourself.

When you are "testing" - you are testing! Thats what you are doing - testing, and recording the issues.

When you are coding - you are coding...

This also gives other developers the opportunity to comment, or code on the issue - I don't believe I have all the answers (or all the time to fix everything)

I contribute PRs where and as I can.

I already have - thanks to you

Seriously though I've had enough of the personal attacks.

You were reported for continued, repetitive, personal attacks using the Code of conduct policies within the project and your conduct was found to be in breach of them by the board of directors of OSM, you were issued with a final warning by them. That's not my fault.

@PhilETaylor As it happens, today I am trying to actually write code to make releases for my software -- the same software the clients of your paid service are using. I apologize for taking time off writing code to help you only to be misunderstood as personally attacking you. ☮️ 👋

only to be misunderstood as personally attacking you.

I was not insinuating that you were - sorry if it read that way. Bad formatting of the comment. I'll edit it :) ☮️ 👋

Seems that some hosters configure database names with leading numbers since many years and Joomla V4 cannot be installed there.

This restriction is a real no-go.

For hosting, the database is created before the installer is called because a PHP script can not create a database in a typical hosting environment.
In a local environment, it may look different when working as a root user, but that's not the norm.

Please do not make any restrictions that further complicate the handling of Joomla in many hostels. Leave the database name and database user name unchanged.

Set to "closed" on behalf of @Quy by The JTracker Application at issues.joomla.org/joomla-cms/23041

Please test PR #23704

Please test PR #23704