SQLERROR 1067 fires after installing Anchor for some users

[TheBrenny]

Summary

After installing Anchor CMS, if the server doesn't have MySQL 5.6 or higher, any visitor to the page will encounter an error, claiming:

Database Error: SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'updated'
SQL: ALTER TABLE `anchor_posts` ADD COLUMN `updated` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `created`
The error has been logged in /anchor/errors.log

Expected Behaviour

Visitors should be able to access the page with no issues, whatsoever.

Actual Behaviour

Visitors hit this brick wall with a stack trace:

Database Error: SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'updated'
SQL: ALTER TABLE `anchor_posts` ADD COLUMN `updated` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `created`
The error has been logged in /anchor/errors.log

Uncaught Exception

Database Error: SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'updated'

SQL: ALTER TABLE `anchor_posts` ADD COLUMN `updated` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `created`

Origin

system/database/connector.php on line 53

Trace

#0 [internal function]: System\database\connector->ask('ALTER TABLE `an...')
#1 /home/ubuntu/workspace/system/database.php(86): call_user_func_array(Array, Array)
#2 /home/ubuntu/workspace/anchor/migrations/213_add_updated_fields_to_tables.php(13): System\database::__callStatic('ask', Array)
#3 /home/ubuntu/workspace/anchor/migrations/213_add_updated_fields_to_tables.php(13): System\database::ask('ALTER TABLE `an...')
#4 /home/ubuntu/workspace/anchor/libraries/migrations.php(63): Migration_add_updated_fields_to_tables->up()
#5 /home/ubuntu/workspace/anchor/libraries/anchor.php(112): migrations->up(220)
#6 /home/ubuntu/workspace/anchor/libraries/anchor.php(27): anchor::migrations()
#7 /home/ubuntu/workspace/anchor/run.php(41): anchor::setup()
#8 /home/ubuntu/workspace/system/start.php(26): require('/home/ubuntu/wo...')
#9 /home/ubuntu/workspace/index.php(35): require('/home/ubuntu/wo...')
#10 {main}

Context details (if applicable)

• Anchor version: 0.12.1
• Server setup: LAMP. Specifically MySQL 5.3

Other information

Potential fixes include altering the migrations file so that it is more inclusive to all servers; especially those where MySQL cannot be upgraded. However, the best (and hence easiest) solution would be to bump up the version of the MySQL requirement. Rather than doing it willy-nilly, I've decided to open this discussion to allow all members to have input on whether on not that the proposed solution really is the best.

[corkyluu]

please let me know when this is fixed...

[evansharp]

Personally I'd support simply making MySQL 5.3 unsupported.

Since CURRENT_TIMESTAMP did not exist prior to MySQL 5.5, we'd have to have multiple pre-insert triggers dealing with different versions AND operating systems of 5.3 and 5.4 for this to be patched.

5.5 was first released in 2009, it's been long enough that I vote we drop support for prior versions.

[misterunknown]

I get exactly the same error, with the following setup:

• Apache 2.4.10
• MySQL 5.5.57
• PHP 5.6.30

It's a Debian Jessie system.

Edit: Sorry, just noticed that MySQL 5.6 is mandatory.

[jackeryjam]

In my membery, this problem might cause by sql syntax is too strict, another reason maybe is you haven’t set mod_rewrite on.Follow these links you may solve it.Good luck. https://github.com/anchorcms/anchor-cms/wiki/mod_rewrite https://stackoverflow.com/questions/18762308/how-to-get-rid-of-strict-sql-mode-in-mysql 发件人: Marco Dickert 发送时间: 2017年10月11日 18:02 收件人: anchorcms/anchor-cms 抄送: Subscribed 主题: Re: [anchorcms/anchor-cms] SQLERROR 1067 fires after installingAnchor for some users (#1205) I get exactly the same error, with the following setup: • Apache 2.4.10 • MySQL 5.5.57 • PHP 5.6.30 It's a Debian Jessie system. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or mute the thread.

[misterunknown]

@jackeryjam Thanks for the info. I'll try to fix it by disabling the strict mode in MySQL, but otherwise simply install MySQL 5.7 or MariaDB. At first, I didn't recognize that MySQL 5.6 and higher is required.

[TheBrenny]

So with the correct information displayed on Anchor's Readme page on GH, I think it's fairly safe to say that this issue can be closed. The fix involved is that the MySQL version required must be greater than or equal to 5.6 (>=5.6 or 5.6+, whichever way suits you).

I guess a feature (not a fix) would be to include a "pre-requisite" page on the first page of the installation phase. Hopefully there we can define the rules, test the system and provide tips on how to better prep the system before the user goes ahead.

[ccheath]

just installed anchor 0.12.7 and at the end of the install going to the admin page it error'd

my setup: centos 7.4, apache, php7, mariadb (all updated to current versions)

is this the same 1067 rearing its head again?
or a new issue?

"message":"Database Error: SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'date'</code></p>

SQL: ALTER TABLE anchor_comments CHANGE date date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER status","trace":[{"function":"ask","class":"System\database\connector","type":"->","args":["ALTER TABLE anchor_comments CHANGE date date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER status"]}

(darn markdown is messing up the exact syntax of the error but you get it, right?)