My-Little-Forum / mylittleforum

A simple PHP and MySQL based internet forum that displays the messages in classical threaded view (tree structure)
GNU General Public License v3.0
122 stars 48 forks source link

Sql error while installing mylittleforum 20220509.1 #601

Closed prbt2016 closed 7 months ago

prbt2016 commented 2 years ago

Hello @auge8472 ,

I was in the process of installing mylittleforum release 20220509.1 on Centos7 with PHP 7.3, MYSQL 5.5.62 and Apache 2.2.

However, I encountered the following error after I enter all the admin details and database details and click on 'Install Forum'. The following error is thrown i.e :

'SQL error' (MySQL: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci' at line 1)

'SQL error' (MySQL: Table '{{DB-NAME}}.{{DATABASE-PREFIX}}userdata' doesn't exist)

Following is the screenshot of the same :

image

Also tested the release with MYSQL 5.6 and MYSQL 5.7 on another servers. Getting the same issue.

What could be causing the issue?. Could you please replicate this at your end and fix this? .

auge8472 commented 2 years ago

Hmm, no notification about this new issue in my browser. Anyway. 🤔

It's clear, what happens. The placeholder for the table name was not properly replaced by the real table name. I will look into the code.

Beside from that I don't know, why this happens. I updated three instances yesterday with not a single error. On the other hand, the yesterdays update does not change a single bit beside your bugfix and a few bits in HTML, CSS and language files.

… Ahem, now I see that you tried to install the forum script, not to upgrade it. That makes me a bit nervous, because I installed the version 20220508.1 (the last with changes in the installation script) without any issue.

auge8472 commented 2 years ago

I have a question about this error message.

'SQL error' (MySQL: Table '{{DB-NAME}}.{{DATABASE-PREFIX}}userdata' doesn't exist)

The words {{DB-NAME}} and {{DATABASE-PREFIX}} are your own replacements of the real values in the error message for hiding them in the published message?

prbt2016 commented 2 years ago

Hello @auge8472 ,

Kindly let me know if you were able to replicate this. and regarding this :

The words {{DB-NAME}} and {{DATABASE-PREFIX}} are your own replacements of the real values in the error message for hiding them in the published message?

Yea {{DB-NAME}} and {{DATABASE-PREFIX}} are the replacements of the real values.

auge8472 commented 2 years ago

O.k. I got it. Once again a closing bracket got lost. I decluttered the CREATE-query for the userdata table.

The broken code:

CREATE TABLE mlf2_userdata (
  user_id int(11) NOT NULL AUTO_INCREMENT,
  /* many column and index definitions */
  UNIQUE KEY key_user_email (user_email)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

… and the corrected code:

CREATE TABLE mlf2_userdata (
  user_id int(11) NOT NULL AUTO_INCREMENT,
  /* many column and index definitions */
  UNIQUE KEY key_user_email (user_email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

… or, cluttered and corrected as it has to be in install/install.sql:

CREATE TABLE mlf2_userdata (/*many column and index definitions*/ UNIQUE KEY key_user_email (user_email)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

And never ever ask me why my testing installation with version 20220508.1 succeeded!

prbt2016 commented 2 years ago

Hello @auge8472 ,

After correcting the code as above this error occurs on MYSQL 5.5 and 5.6 i.e :


'SQL error' (MySQL: Specified key was too long; max key length is 767 bytes)
'SQL error' (MySQL: Table '{{DB-NAME}}.{{DATABASE-PREFIX}}userdata' doesn't exist)

Following is the screenshot :

lf2

The code works fine on MYSQL 5.7.

Since max prefix length in MYSQL 5.5 and 5.6 supported is upto 767 bytes, by InnoDB the above error occurs.

https://dev.mysql.com/doc/refman/5.6/en/innodb-limits.html

Could you please either make the code compatible to work with MYSQL 5.5 and 5.6 or raise the minimum requirements here to MYSQL 5.7 :

https://github.com/ilosuna/mylittleforum#system-requirements

So that users using MYSQL < 5.7 wont face the above issue.

auge8472 commented 2 years ago

Hach, Mist!

'SQL error' (MySQL: Specified key was too long; max key length is 767 bytes)

… means that the defined length of a column exceeds the maximal index length because of the charset utf8mb4 (a value of this column, using only four-byte-chars would need a theoretical index length of 1024 bytes. Because of that we once cut the user name lenght from 256 to 128 bytes what made the maximal lenght of a user name fitting in the maximal size of an index. So the column user_name isn't the breaking point.

The only new created key is key_user_email which matches onto the column user_email. That column has a maximal size of 256 Bytes what exceeds the maximal index size. Here it is no option to cut the possible length of the column because e-mail-addresses can get longer than 128 characters. Here we need the full possible length of 256 characters.

It would be the simplest solution to raise the minimal MySQL-version to 5.7. In Europe many hosting companies, that offers hosting service for a low fee offers MySQL in an appropriate version (>=5.7) or MariaDB as alternative. I have absolutely no clue about the situation in other regions of our planet.

So the one solution I can think of is to define the column user_email in the user data table with the charset utf8 without the suffix mb4.

/* current definition for the column user_email in the query to create the user data table */
user_email varchar(255) NOT NULL UNIQUE

/* fixed definition for the column user_email */
user_email varchar(255) character set utf8 NOT NULL UNIQUE

I am at work so I beg you for testing the (hopefully) corrected code above. Please replace the definition for user_email in line 8 with the last line of the code example above and start a new installation attempt.

auge8472 commented 2 years ago

Meanwhile I was able to test my own proposal under not ideal circumstances. I have no possibility to install the forum anywhere with a MySQL-version below 5.7. Best I can do is to install it with a MySQL-server of version 5.7 in a 5.5-compatibility mode.

I was able to install the forum with the two fixes (the closing bracket and the redefined column user_email). The affected user data table was created, no error message arised and the definition of the column user_email with character set utf8 (without mb4 and therefore with a maximal character size of 3 Bytes) led the index creation succeed.

Even my own test succeeded, I am interested in a test with a real MySQL-server of an affected version.

I hope, this does not break anything else like e-mai-addresses that contains emojis or something similar.

auge8472 commented 2 years ago

@prbt2016 Was you able to install the forum on a server with MySQL in a version 5.5 or 5.6 with the proposed fixes? I myself am not able to test this because I don't have access to such a configuration. Your feedback would help me a lot.

prbt2016 commented 2 years ago

Hello @auge8472 ,

I checked with the proposed changes i.e :


/* current definition for the column user_email in the query to create the user data table */
user_email varchar(255) NOT NULL UNIQUE

/* fixed definition for the column user_email */
user_email varchar(255) character set utf8 NOT NULL UNIQUE

on two servers with MYSQL 5.5 and MYSQL 5.6 but there's a strange error on entering the correct db details and admin details , following error is thrown on clicking 'Install forum' i.e script redirects to the root URL and following is thrown :

my little forum Database error

Could not connect to the MySQL database. The forum is probably not installed yet.

Following is the screenshot of the same :

image

Also i checked the config/db_settings.php file , all details are correct and database tables are also populated.

After refreshing this error page, the login page is shown correctly !.

Seems that something is breaking the install script. Could you please figure it out?.

auge8472 commented 2 years ago

@prbt2016 I will check the install.php as soon as possible.

Such problems are a bit disturbing. I tested the installation procedure and I encounterd no error myself. 🤔

prbt2016 commented 2 years ago

Hello @auge8472 ,

Thanks for your kind support. Kindly let me know if you discover anything.

Regards.

auge8472 commented 2 years ago

Hello @prbt2016 I can't find anything in the code. that would cause such an error with exception of a real error in the communication with the database. The message Could not connect to the MySQL database. tells us, what happened. But I can't see anything, that could have caused this event.

Please add the following code to the script install/index.php between the heading comment block and the first code define('IN_INDEX', TRUE); in line 23:

ini_set('display_errors', 1);
error_reporting(E_ALL);

When running the installation script, all errors, the PHP interpreter will become aware of, will get displayed in the browser window.

streaps commented 2 years ago

Maybe php could just not connect to the MySQL database, like the error message says? @prbt2016 are you sure the settings are correct and mysqld is reachable?

auge8472 commented 2 years ago

Maybe php could just not connect to the MySQL database, like the error message says? @prbt2016 are you sure the settings are correct and mysqld is reachable?

Yes, maybe. Because of that, I proposed to add the code shown above to make the script more verbose in an error case. I myself can only state, that all my attempts to install the forum with the SQL-fixes succeeded. But I don't have a MySQL-installation with one of the versions that causes the installation script to fail. :-/

auge8472 commented 2 years ago

With no feedback I must assume that the issue is solved with the fixes in #602. So I'm going to close this issue. If it lasts with the main branch or the next release, that will contain the fixes, feel free to reopen the issue. In that case we have to decide if we put further work into solving this issue in old/outdated MySQL version or if we declare the MySQL version 5.7, that works verifiable, as the minimal version to run the forum.

prbt2016 commented 2 years ago

Hello @auge8472 ,

I checked even with MYSQL 5.7 setup with #602 proposed changes . Same above issue as on MYSQL 5.7 as well now.

my little forum Database error

Could not connect to the MySQL database. The forum is probably not installed yet.

Seems something breaks the script . Also adding

 ini_set('display_errors', 1);
error_reporting(E_ALL); 

in install/index.php isn't throwing any error .

auge8472 commented 2 years ago

Once again, the script in itself works. I performed several successful installations of the up-to-date code with the SQL-fixes on two webservers connecting to all in all three database servers.

The error message says, that the script can not establish a connection to the database. It's possible, that the database server is not reachable or that the provided data for the connection are (partially) wrong.

I will do a last test on another webserver with it's own database.

prbt2016 commented 2 years ago

Hello @auge8472 ,

Thanks for the support. Kindly let me know if you were able to replicate.

Regards.

prbt2016 commented 2 years ago

Hello @auge8472 ,

I was able to successfully install version 20220517.1 on MYSQL 5.5 with checking the option of 'Create specified database' without any issues.

However, the above error occurs on specifying existing blank database, even when all of the database details are correct. Even Database gets populated on specifying blank db , redirecting to the error page above . That's something strange.

Could you perform an installation with existing blank database and check ?.

auge8472 commented 2 years ago

@prbt2016 I tried to install the forum on another server, I have access to and encountered the database-unavailable-error for the first time. The database on this server runs with MySQL 5.7.

I looked into phpMyAdmin and saw, that all the tables are there. I looked into the file config/db_settings.php and saw, that it was populated the right way (database connection information, table names). Then I spotted the URL in the browser and saw, that it was https://example.org/forum, so it is not the installation that failed but the first request of the forum after the final redirect in the installation script. Knowing, that the installation in itself was successful, I reloaded the page in the browser and 🎺 .oO(tada) 🎶, the forum gets displayed.

Are you able to confirm, that the forum can be "enabled" with a page reload after the installation?

That said, I have to confirm your observation and the obviously every when and then occuring bug. Because of that I reopen the issue.

prbt2016 commented 2 years ago

Hello @auge8472,

Are you able to confirm , that the forum can be "enabled" with a page reload after the installation?

Yes after a page reload forum works fine after that Database error page . I have checked that.

So it is not the installation that failed but the first request of the forum after the final redirect in the installation script

Yes exactly. This is what needs to be fixed. The final redirect goes to the database error page instead of the forum page, when I try to install with an existing database and specify it and later after reloading it works. This is not the case while checking the option of 'Create specified database', the final redirect works absolutely fine.

auge8472 commented 2 years ago

So it is not the installation that failed but the first request of the forum after the final redirect in the installation script

This is what needs to be fixed.

There is nothing like an "error page". The redirect goes to index.php in every case but there, while executing the PHP-code, something unrecognised happens. I'll dive into it.

prbt2016 commented 2 years ago

Hello @auge8472 ,

Thanks for checking and deep diving into the issue. Really appreciate it. Kindly let me know once you discover something.

Regards.

auge8472 commented 8 months ago

I am assuming that the follow-up error discussed last is identical to the one in #681 and #695.

auge8472 commented 7 months ago

I'll close this issue for now because the current code contains the fix for the cause I suspected (see the comment above this one).