mysql - Invalid default value

Question

MySQL Distrib 5.5.31

I have dropped old DB because of incorrect charset. So I try to create new DB and load correct dump. Also I had an old version mysql before.

While creating a BD and loading a dump I am getting the following errors:

ERROR 1067 (42000) at line 225234: Invalid default value for 'ts_created'
ERROR 1146 (42S02) at line 225243: Table 'ardostore.toc_piwik_site' doesn't exist
ERROR 1067 (42000) at line 225252: Invalid default value for 'date_registered'
ERROR 1146 (42S02) at line 225263: Table 'ardostore.toc_piwik_user' doesn't exist

225234:

create table toc_piwik_site (
  idsite int(10) unsigned not null auto_increment,
  name varchar(90) not null,
  main_url varchar(255) not null,
  ts_created timestamp default 'CURRENT_TIMESTAMP' not null,
  feedburnerName varchar(100),
  PRIMARY KEY (idsite)
);

225243:

insert into toc_piwik_site (idsite, name, main_url, ts_created, feedburnerName) values ('1', 'Store Name', 'http://ardostore.com', '2012-10-18 19:58:49', NULL);
drop table if exists toc_piwik_site_url;
create table toc_piwik_site_url (
  idsite int(10) unsigned not null,
  url varchar(255) not null,
  PRIMARY KEY (idsite, url)
);

225252:

create table toc_piwik_user (
  login varchar(100) not null,
  password char(32) not null,
  alias varchar(45) not null,
  email varchar(100) not null,
  token_auth char(32) not null,
  date_registered timestamp default 'CURRENT_TIMESTAMP' not null,
  PRIMARY KEY (login),
  UNIQUE uniq_keytoken (token_auth)
);

225263

insert into toc_piwik_user (login, password, alias, email, token_auth, date_registered) values ('toc_piwik_view', '5f4dcc3b5aa765d61d8327deb882cf99', 'toc_piwik_view', '[email protected]', 'a674bf3fe5d9c0651ac32b28fcbe74f8', '2012-10-18 19:58:49');
drop table if exists toc_piwik_user_dashboard;
create table toc_piwik_user_dashboard (
  login varchar(100) not null,
  iddashboard int(11) not null,
  layout text not null,
  PRIMARY KEY (login, iddashboard)
);

UPD

ERROR 1170 (42000) at line 225099: BLOB/TEXT column 'query' used in key specification without a ke                                         y length

create table toc_piwik_log_profiling (
  query text not null,
  count int(10) unsigned,
  sum_time_ms float,
  UNIQUE query (query)
);

What's causing the issue? And how to fix?

Thank you, it works. But I forgot about 1 error (look at UPD in post pls) — user3065376
– user3065376, Commented Dec 4, 2013 at 10:42

Daniel W. · Accepted Answer · 2013-12-04 10:36:38Z

7

default value 'CURRENT_TIMESTAMP' is a string. remove quotes and it will work:

create table toc_piwik_site (
  idsite int(10) unsigned not null auto_increment,
  name varchar(90) not null,
  main_url varchar(255) not null,
  ts_created timestamp default CURRENT_TIMESTAMP not null,
  feedburnerName varchar(100),
  PRIMARY KEY (idsite)
);

answered Dec 4, 2013 at 10:36

Daniel W.

32.6k15 gold badges99 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

fancyPants · Accepted Answer · 2013-12-04 10:36:37Z

2

Remove the ' around `CURRENT_TIMESTAMP. Those make it a string.

Write it for example like this:

create table toc_piwik_site (
  idsite int(10) unsigned not null auto_increment,
  name varchar(90) not null,
  main_url varchar(255) not null,
  ts_created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  feedburnerName varchar(100),
  PRIMARY KEY (idsite)
);

answered Dec 4, 2013 at 10:36

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

Collectives™ on Stack Overflow

mysql - Invalid default value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related