Bug #47770

installtool "compare" fails on ADD KEY sha1 (sha1(40))

Added by d.ros no-lastname-given over 6 years ago. Updated over 4 years ago.

Status:
Closed
Priority:
Must have
Assignee:
-
Category:
-
Target version:
-
Start date:
2013-05-01
Due date:
% Done:

0%

TYPO3 Version:
6.1
PHP Version:
5.4
Tags:
Complexity:
Is Regression:
Sprint Focus:

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

History

#1 Updated by d.ros no-lastname-given over 6 years ago

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

#2 Updated by Maximilian Anton Grimm over 6 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.

#3 Updated by Chris topher over 6 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?

#4 Updated by d.ros no-lastname-given over 6 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 :-)

#5 Updated by Alexander Opitz over 6 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?

#6 Updated by Chris topher over 6 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.

#7 Updated by d.ros no-lastname-given over 6 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

#8 Updated by d.ros no-lastname-given over 6 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

#9 Updated by Alexander Opitz over 6 years ago

  • Status changed from Needs Feedback to Closed

Closing as requested.

#10 Updated by Andreas Becker (Andi) about 6 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

#11 Updated by Sabine Weiß about 6 years ago

This issue also exists in TYPO3 6.2 alpha 2.

#12 Updated by Andreas Groth almost 6 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.

#13 Updated by Christian Sonntag over 5 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.

#14 Updated by Andreas about 5 years ago

Update from 4.5.x to 6.2.5 - same problem. Bugs, bugs, bugs!!

#15 Updated by Andreas Allacher almost 5 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.

#16 Updated by Guillermo Oramas over 4 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.

Also available in: Atom PDF