Bug #47770
closed
installtool "compare" fails on ADD KEY sha1 (sha1(40))
Added by d.ros no-lastname-given over 11 years ago. Updated over 9 years ago.
0%
Description
error in your SQL syntax
Add fields ALTER TABLE sys_file ADD KEY sha1 (sha1 CHAR(40)); 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 'sha1(40))' at line 1
Occours at running the compare after an update from 6.0.4 to 6.1.0 on
cheers David
Updated by d.ros no-lastname-given over 11 years ago
Updated by Maximilian Anton Grimm over 11 years ago
d.ros no-lastname-given wrote:
After changing the Table manually and re-removing all cache files the former errors #47767 and #47768 do not occour anymore. I don´t know why. . .
cheers
Can i ask what you have changed manually?
I have the same issues when making an update and even when i setup a new installation (same error at create table sys_files).
Thanks.
Updated by Chris topher over 11 years ago
- Status changed from New to Needs Feedback
Hi guys,
the query ALTER TABLE sys_file ADD KEY sha1 (sha1 CHAR(40)); obviously should add a key with the name "sha1" - the arguments in the brackets must cause the error. What should they look like, if they were correct?
Updated by d.ros no-lastname-given over 11 years ago
The query itelf is invalid MySQL Syntax. Just drop this line by phpmyadmin and you´ll see.
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 'CHAR(40))' at line 1
Therefore the syntax has to be changed. Don´t know the right syntax from mind. I just altered these points in phpmyadmin by click :-)
Updated by Alexander Opitz over 11 years ago
Correct is ALTER TABLE sys_file ADD KEY sha1 (sha1(40)) and thats what you can find in ext_tables.sql.
So I don't know where you get this "CHAR"? Not on MySQL?
Updated by Chris topher over 11 years ago
Alexander Opitz wrote:
Correct is
ALTER TABLE sys_file ADD KEY sha1 (sha1(40))and thats what you can find in ext_tables.sql.
I have just tested that again and for me the Install Tool does not show this CHAR() in the line. The Install Tool shows me the correct query and it is also executed correctly and successfully.
Sounds like in David's case the Install Tool somehow computes the query before, which leads to a wrong result.
Updated by d.ros no-lastname-given over 11 years ago
If I drop the line
ALTER TABLE sys_file ADD KEY sha1 (sha1(40))
in 6.0.4 Version of T3 @ phpmyadmin I get the error also back.
Additionally if I open the Resources Module in phpmyadmin I get the information
You are using PHP's deprecated 'mysql' extension, which is not capable of handling multi queries. <b>The execution of some stored routines may fail!</b> Please use the improved 'mysqli' extension to avoid any problems.
Is it possible that the switch from mysql to mysqli did not succeed @ update wizard and therefore the syntax is interpreted as wrong ?
Cheers David
Updated by d.ros no-lastname-given over 11 years ago
Here the issue was, that the mysql server was @ 5.0 ... upgraded to 5.1 and th issue was gone.
cheers....
can be closed
Updated by Alexander Opitz over 11 years ago
- Status changed from Needs Feedback to Closed
Closing as requested.
Updated by Andreas Becker (Andi) about 11 years ago
This issue is still there!
We just updated a site from 6.0.10 to 6.1.5 and got stock exactly at the same problem while doing the compare of database.
Fortunately the backend is still wprking and you can use i.e. adminer to clear the cache tables. This helps and the site is loading again.
Before we inserted the sql line mentioned above into adminer and it reported a duplicated key! Indeed the key which should get written is already there with the same values!
Andi
Updated by Sabine Weiß about 11 years ago
This issue also exists in TYPO3 6.2 alpha 2.
Updated by Andreas Groth almost 11 years ago
The installer's CREATE statement contains
KEY sha1 (sha1(40))which is supposed to mean "create a key (a lookup index) named "sha1" for the column "sha1", using the first 40 characters of the columns's entries. Unfortunately, the column name "sha1" is also the name of a MySQL function. In order to tell the parser to resolve it as a column name it must be quoted in backticks like so:
KEY sha1 (`sha1`(40))Inserting a space before the brackets might also work:
KEY sha1 (sha1 (40))See [[http://dev.mysql.com/doc/refman/5.0/en/function-resolution.html]]). The SQL staments suggested above to add it to the table are not correct. Look up the syntax of
{INDEX|KEY} [index_name] [index_type] (index_col_name,...)
[index_type]
in the manual [[http://dev.mysql.com/doc/refman/5.0/en/create-table.html]]. This is for MySQL 5.0, but should be the same for 5.5.
Updated by Christian Sonntag over 10 years ago
This issue also exists in TYPO3 6.2.3 ;-(
Installtool could not create table sys_file.
I had to create the table manually with an additional space mentioned in comment #12.
Updated by Andreas about 10 years ago
Update from 4.5.x to 6.2.5 - same problem. Bugs, bugs, bugs!!
Updated by Andreas Allacher almost 10 years ago
The bug still exist with 6.2.7
adding a space between sha1 and (40) in typo3_src
It does not seem to be an issue on all systems (probably depends on used mysql server and settings)
like
KEY sha1 (sha1 (40))
mentioned by Andreas Groth
fixed the issue.
It also worked on a system where it worked fine also without the space.
Updated by Guillermo Oramas over 9 years ago
The bug is still present in version 6.2.9. (on php 5.4.32 and mysql 5.5.14).
Changing sha1 (sha1(40)) to sha1 (sha1 (40)) (space added) in the query worked correctly.