Mysql datetime DEFAULT CURRENT_TIMESTAMP error

Question

1. When I ran this MYSQL syntax on windows it ran properly:

CREATE TABLE New
(
  id bigint NOT NULL AUTO_INCREMENT,
  timeUp datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
)

But when I tried running this code on Linux I got an error:

 #1067 - Invalid default value for 'time'

2. On windows the case is not sensitive eg. New and new both are considered to be same. But on linux the case is sensitive.

Configuration of Linux:

MySQL 5.5.33, phpMyAdmin: 4.0.5, PHP: 5.2.17

Configuration of Windows:

MySql: 5.6.11, phpMyAdmin: 4.0.4.1, PHP: 5.5.0

Is there any way to make them common for both systems? Or any alternative approach?

Answer 1

The DEFAULT CURRENT_TIMESTAMP support for a DATETIME (datatype) was added in MySQL 5.6.

In 5.5 and earlier versions, this applied only to TIMESTAMP (datatype) columns.

It is possible to use a BEFORE INSERT trigger in 5.5 to assign a default value to a column.

 DELIMITER $$

 CREATE TRIGGER ...
 BEFORE INSERT ON mytable
 FOR EACH ROW
 BEGIN
    IF NEW.mycol IS NULL THEN
       SET NEW.mycol = NOW();
    END IF;
 END$$

---

Case sensitivity (of queries against values stored in columns) is due to the collation used for the column. Collations ending in _ci are case insensitive. For example latin1_swedish_ci is case insensitive, but latin1_general_cs is case sensitive.

The output from SHOW CREATE TABLE foo will show the character set and collation for the character type columns. This is specified at a per-column level. The "default" specified at the table level applies to new columns added to the table when the new column definition doesn't specify a characterset.

UPDATE

Kaii pointed out that my answer regarding "case sensitivity" deals with values stored within columns, and whether queries will return a value from a column containing a value of "New" will be returned with a predicate like "t.col = 'new'".

See Kaii's answer regarding identifiers (e.g. table names) being handled differently (by default) on Windows than on Linux.

Answer 2

As the DEFAULT CURRENT_TIMESTAMP question is already answered, i will only respond to the case-sensitivity mismatch in table names between windows and linux.

On Windows, file systems are by default case-insensitive.
But on Linux and other *NIX like Operating Systems, they are case-sensitive by default.

The reason why you get a mismatch in behaviour here is the file system, as each table is created as a separate file and the filesystem handles case-sensitivity for you.

MySQL has a parameter to override this behaviour:

For example, on Unix, you can have two different tables named my_table and MY_TABLE, but on Windows these two names are considered identical. To avoid data transfer problems arising from lettercase of database or table names, you have two options:

• Use lower_case_table_names=1 on all systems. The main disadvantage with this is that when you use SHOW TABLES or SHOW DATABASES, you do not see the names in their original lettercase.

• Use lower_case_table_names=0 on Unix and lower_case_table_names=2 on Windows. This preserves the lettercase of database and table names. The disadvantage of this is that you must ensure that your statements always refer to your database and table names with the correct lettercase on Windows. If you transfer your statements to Unix, where lettercase is significant, they do not work if the lettercase is incorrect.
  
  Exception: If you are using InnoDB tables and you are trying to avoid these data transfer problems, you should set lower_case_table_names=1 on all platforms to force names to be converted to lowercase.

[...]
To avoid problems caused by such differences, it is best to adopt a consistent convention, such as always creating and referring to databases and tables using lowercase names. This convention is recommended for maximum portability and ease of use.

This is an excerpt from the MySQL manual on the case sensitivity of identifiers