0

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?

wolmi
  • 1,659
  • 12
  • 25
Eboubaker
  • 618
  • 7
  • 15
  • 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. – Akina Jan 31 '20 at 10:25
  • Please check my updated answer, you can find solution using variable and prepare statement. – Pradeep Darjee Jan 31 '20 at 11:33
  • @Akina thanks but i have problems synchronizing the time of php and the mysql time. – Eboubaker Jan 31 '20 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. – Akina Jan 31 '20 at 14:00

3 Answers3

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);
Ali Ghaini
  • 882
  • 6
  • 13
  • If InnoDB is locked then I'm afraid that triggers creation is locked too... – Akina Jan 31 '20 at 09:37
  • 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. – Eboubaker Jan 31 '20 at 10:00
-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;
Pradeep Darjee
  • 192
  • 6
  • Does not match OP's task. – Akina Jan 31 '20 at 09:20
  • 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 – Pradeep Darjee Jan 31 '20 at 09:23
  • 1
    Subject != question. Subject even have no question mark in it... – Akina Jan 31 '20 at 09:25
  • 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 – Pradeep Darjee Jan 31 '20 at 09:27
  • Please address your words to the question author. – Akina Jan 31 '20 at 09:34
  • @Akina, Than why are you getting into this if I'm answering something. Rather you should do your own business. And by the way according to subject, my answer is definitely correct. https://stackoverflow.com/a/168832/7388520 – Pradeep Darjee Jan 31 '20 at 09:39
  • I don't mind your answer being published. Moreover, the information in it corresponds to reality. However, the author does not just need CURRENT_TIMESTAMP, but also plus five minutes - that is, not what is done in your answer. That's what I said. – Akina Jan 31 '20 at 09:42
  • @PradeepDarjee i can't write the whole question in the title!, i give a short description in the title and i specify in the description.the subject is about default time, i was correct – Eboubaker Jan 31 '20 at 10:02
  • 1
    @ZOLDIK Yes, It's okay – Pradeep Darjee Jan 31 '20 at 10:10
  • considering your edit `SET @def_timestamp = DATE_ADD(NOW(), INTERVAL 1 DAY);` will set @def_timestamp to a fixed time (the time when the variable was created + interval ), i don't want that i want it to be ( the time where i add the new record + interval ), you can see that time with `select @def_timestamp` – Eboubaker Jan 31 '20 at 13:29
-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.

Akina
  • 39,301
  • 5
  • 14
  • 25