1

I have MyISAM engine running in my cPanel server ,the server does not support InnoDB Engine, i only have MyISAM engine.

  When i try to create a table column of type timestamp with a default value of (current time + 5 minutes) it gives an error. This worked in my machine with InnoDB but in the server with MyISAM it gives an error You have an error in your SQL syntax; check the manual that... near DEFAULT (NOW() + 300)

CREATE TABLE test( token_death_time TIMESTAMP DEFAULT (NOW() + 300) );

I also tried

CREATE TABLE test( token_death_time TIMESTAMP DEFAULT DATE_ADD(NOW(), INTERVAL 5 MINUTE) );

Can I achieve what I want with MyISAM or I have to do the 'adding' in my PHP script?

Improve this question
4
  • Considering that "on the server i don't have privileges to create triggers" I'd recommend you to take these 5 minutes into account in the query which checks the expiration of the token. Commented Jan 31, 2020 at 10:25
  • Please check my updated answer, you can find solution using variable and prepare statement. Commented Jan 31, 2020 at 11:33
  • @Akina thanks but i have problems synchronizing the time of php and the mysql time. Commented Jan 31, 2020 at 13:19
  • i have problems synchronizing the time of php and the mysql time. Perform all calculations on the server side - and this problem will have no base for to occurr. Commented Jan 31, 2020 at 14:00

3 Answers 3

Reset to default
2

you cant use function for default value (before version 8)

but you can use trigger

CREATE TRIGGER before_insert_test BEFORE INSERT ON test FOR EACH ROW SET new.token_death_time = DATE_ADD(NOW(), INTERVAL 5 MINUTE);
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

If InnoDB is locked then I'm afraid that triggers creation is locked too...
Yes it worked in machine but on the server i don't have privileges to create triggers, and i can't change them. i also tried creating a function and call it on default, can't create functions too.
-1

The DEFAULT value expression which you need is available for MySQL version 8.0.13 or later (fiddle - both queries are successfully executed on 8.0.19 version).

See Data Type Default Values.

And the issue is not relative to the table's engine.

Improve this answer

Comments

-1

You can use CURRENT_TIMESTAMP

CREATE TABLE test( token_death_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

To set desired timestamp as default, you can set it in variable and can use prepare & execute statement, use below code:

SET @def_timestamp = DATE_ADD(NOW(), INTERVAL 1 DAY); SET @query = CONCAT("CREATE TABLE test(token_death_time TIMESTAMP DEFAULT '", @def_timestamp , "')"); PREPARE stmt from @query; EXECUTE stmt; DEALLOCATE PREPARE stmt;
Improve this answer

10 Comments

Does not match OP's task.
Your question was "MySQL MYISAM engine does not accept default timestamp value". So my answer is, it allow CURRENT_TIMESTAMP as default to be set
Subject != question. Subject even have no question mark in it...
You are specifying your problem by subject. It means you specified wrong subject than. MyIsam allow CURRENT_TIMESTAMP to set as a default value, That's it
@ZOLDIK Yes, It's okay
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.