Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Finishing site setup SQLSTATE[42000] #224

Closed
marcalbet opened this issue Nov 19, 2018 · 6 comments

Comments

@marcalbet
Copy link

commented Nov 19, 2018

Expected behavior:
Finish TastyIgniter setup installation.

Actual behavior:
It is not possible to finish with the installation.

SQLSTATE[42000]: Syntax error or access violation

Reproduce steps:
Setup Installation

Version:
TastyIgniter-3.0.4-beta.6

Additional Information:

SQLSTATE[42000]: Syntax error or access violation: 1064 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 'json null, priority int unsigned null, created_at timestamp null, updated_a' at line 1 (SQL: create table ti_media_attachments (idint unsigned not null auto_increment primary key,diskvarchar(128) not null,namevarchar(128) not null,file_namevarchar(128) not null,mime_typevarchar(128) not null,sizeint unsigned not null,tagvarchar(128) null,attachment_idint unsigned null,attachment_typevarchar(128) null,is_publictinyint(1) not null default '1',custom_propertiesjson null,priorityint unsigned null,created_attimestamp null,updated_at` timestamp null) default character set utf8mb4 collate utf8mb4_unicode_ci)

@sampoyigi

This comment has been minimized.

Copy link
Member

commented Nov 19, 2018

Your MySQL version does not support json column type, you need MySQL 5.7.8+.

There's a workaround if you can't upgrade your MySQL, let me know.

@marcalbet

This comment has been minimized.

Copy link
Author

commented Nov 19, 2018

Thank you for your feedback. I am using MySQL 5.5.27 and maybe it is not a good idea to change my version now because current projects...

Is it possible to fix with my MySQL version?

Thanks.

@sampoyigi

This comment has been minimized.

Copy link
Member

commented Nov 19, 2018

Since you can't upgrade your MySQL, you'll need to make some changes to the migration file then complete setup manually using the command line, using the setup wizard will overwrite your changes.

Open app/system/database/migrations/2018_10_19_000300_create_media_attachments_table.php

Line 29 should look like this

$table->text('custom_properties')->nullable();

Then complete setup by running the following command

php artisan igniter:install
@marcalbet

This comment has been minimized.

Copy link
Author

commented Nov 19, 2018

I did it but without successful.

I've modified this file and I0've executed manually this command, but I have these errors:

Migrating application and extensions...

In Connection.php line 664:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error i
n your SQL syntax; check the manual that corresponds to your MySQL server v
ersion for the right syntax to use near 'json null, priority int unsigned
null, created_at timestamp null, updated_a' at line 1 (SQL: create tabl e ti_media_attachments (idint unsigned not null auto_increment primary key,diskvarchar(128) not null,namevarchar(128) not null,file_nam
evarchar(128) not null,mime_typevarchar(128) not null,sizeint uns igned not null,tagvarchar(128) null,attachment_idint unsigned null,attachment_typevarchar(128) null,is_publictinyint(1) not null defau lt '1',custom_propertiesjson null,priorityint unsigned null,creat
ed_attimestamp null,updated_at` timestamp null) default character set u
tf8mb4 collate utf8mb4_unicode_ci)

In PDOConnection.php line 79:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error i
n your SQL syntax; check the manual that corresponds to your MySQL server v
ersion for the right syntax to use near 'json null, priority int unsigned
null, created_at timestamp null, `updated_a' at line 1

In PDOConnection.php line 77:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error i
n your SQL syntax; check the manual that corresponds to your MySQL server v
ersion for the right syntax to use near 'json null, priority int unsigned
null, created_at timestamp null, `updated_a' at line 1

In the previous version, TastyIngniter v3_beta5 I installed it successfully without errors, but without seeing the store.

Any idea to fix the above errors?

Thanks in advance.

@xDefcon

This comment has been minimized.

Copy link

commented Jun 27, 2019

@sampoyigi Still happening in the latest beta version.
Debian 9
MariaDB 10.4 (also tried on 10.3)
Latest beta version.

I tested it on a different machine with MariaDB 10.1 and it worked. What's going on with this error?

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1.0.0'' COLLATE utf8mb4_unicode_ci' at line 1 (SQL: ALTER TABLE extensions CHANGE version version VARCHAR(32) DEFAULT ''1.0.0'' COLLATE utf8mb4_unicode_ci)

The file causing the error is the following:

app/system/database/migrations/2018_03_30_000300_create_extension_settings_table.php(16):

Don't ask me why but in the SQL query the quotes in ''1.0.0'' are not the single quote ' or double quotes ", but two single quotes characters at the beginning and at the end of the string (this seems to be the syntax error).

@sampoyigi

This comment has been minimized.

Copy link
Member

commented Jun 27, 2019

Seems the word 'version' is a keyword (reserved) in MariaDB and cannot be used as a column name without quoting it.

I've not tested this but open the file:
app/system/database/migrations/2018_03_30_000300_create_extension_settings_table.php

and replace the single quotes to double quotes on line 16 to look like this:

$table->string("version", 32)->nullable()->change();

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
3 participants
You can’t perform that action at this time.