CODEWITHSUNDEEP
mysql
user3065376
user3065376

Reputation: 25

mysql - Invalid default value

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?

Upvotes: 1

Views: 15082

Answers (2)

Daniel W.
Daniel W.

Reputation: 32350

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)
);

Upvotes: 7

fancyPants
fancyPants

Reputation: 51928

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)
);

Upvotes: 2

Related Questions