CURRENT_TIMESTAMP: different mysql-behavior phpmyadmin vs. CI

CURRENT_TIMESTAMP: different mysql-behavior phpmyadmin vs. CI - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: CURRENT_TIMESTAMP: different mysql-behavior phpmyadmin vs. CI (/showthread.php?tid=72275)

CURRENT_TIMESTAMP: different mysql-behavior phpmyadmin vs. CI - kbs170 - 11-29-2018

Hi,

We have a problem with different behavior between executing mysql-query in phpmyadmin and Codeigniter. In the same UPDATE-Query we would like do both save an old timestamp (means: "move" to an other field) and set the new timestamp. That doesn't work with Codeigniter. Codeigniter sets both fields with the new value.

Why that?

One thing we recognize: It works with Codeigniter, If we stop using the mysql-function "ON UPDATE CURRENT_TIMESTAMP". Actual "ON UPDATE CURRENT_TIMESTAMP" is a "core-function" of our system, because we do not implement a modified=NOW() statement at all.

Code:
CREATE TABLE `test` (

  `id` int(6) NOT NULL,

  `name` char(255) COLLATE utf8_unicode_ci DEFAULT NULL,

  `created` datetime DEFAULT NULL,

  `modified2` datetime DEFAULT NULL,

  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `test` (`id`, `name`, `created`, `modified2`, `modified`) VALUES

(1, 'Test1', NULL, NULL, '2018-11-29 11:50:39'),

(2, 'Test2', NULL, NULL, '2018-11-29 11:50:39');

in phpmyadmin

Code:
UPDATE

  test

SET

  modified2 = modified,

  modified = NOW()

WHERE

  id = 1

results (as expected):
(1, 'Test1', NULL, '2018-11-29 12:50:39', '2018-11-29 11:52:44')

in Codeigniter

PHP Code:
$this->db->query('
UPDATE
  test
SET
  modified2 = modified,
  modified = NOW()
WHERE
  id = 1
'); 

results (not expected):
(1, 'Test1', NULL, '2018-11-29 11:52:44', '2018-11-29 11:52:44')

It should

set "modified2" with the old value of "modified" (2018-11-29 12:50:39)
set "modified" with the new value

How could that be?

Best,
kbs170

RE: CURRENT_TIMESTAMP: different mysql-behavior phpmyadmin vs. CI - Pertti - 11-29-2018

If you are keeping your queries, you can do

PHP Code:
echo $this->db->last_query(); 

That should give you exact SQL query CI was trying to execute.

RE: CURRENT_TIMESTAMP: different mysql-behavior phpmyadmin vs. CI - kbs170 - 11-29-2018

(11-29-2018, 06:09 AM)Pertti Wrote: If you are keeping your queries, you can do

PHP Code:
echo $this->db->last_query();

That should give you exact SQL query CI was trying to execute.

That gives:

Code:
UPDATE test SET modified2 = modified, modified = NOW() WHERE id = 1

as well. Unfortunately no new insight. Thank you for the hint.

Best

RE: CURRENT_TIMESTAMP: different mysql-behavior phpmyadmin vs. CI - Pertti - 11-29-2018

Does it work if you split it in two different queries, one for modified2 = modified and another for modified = NOW()?

RE: CURRENT_TIMESTAMP: different mysql-behavior phpmyadmin vs. CI - kbs170 - 11-29-2018

I found the mistake. There is a bundle of UPDATEs before that. All without modified=NOW() but - of course - mysql set "modified" automatically (because of ON UPDATE CURRENT_TIMESTAMP).

Sorry, and thank you for your support.

Best,

RE: CURRENT_TIMESTAMP: different mysql-behavior phpmyadmin vs. CI - InsiteFX - 11-29-2018

This is the correct way of doing it.

Code:
 `created_at` DATETIME  DEFAULT CURRENT_TIMESTAMP,

  `updated_at` DATETIME  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

Works with DATETIME and TIMESTAMP

Hope that helps.