Manually created database error

[Phillip911]

Hi, I try create a CMS database manually but i get this error. The code is copy from the help in WYSIWYG software

--- Screenshot


Any idea how to fix it ?

[Pablo]

Maybe there is no space between

Code: Select all

`search_index` int(1) DEFAULT '1'

and

Code: Select all

PRIMARY KEY  (`id`));

[Phillip911]

Maybe there is no space between

Code: Select all

`search_index` int(1) DEFAULT '1'

and

Code: Select all

PRIMARY KEY  (`id`));

Nop, still same. Can you help me test ? I send you my c-penal account personally

[WWBman]

Shouldn't there be a comma after DEFAULT '1'

[Pablo]

You can also try this:

Code: Select all

CREATE TABLE `CMS_PAGES` (
  `id` int(10) UNSIGNED NOT NULL,
  `category_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `home` tinyint(1) NOT NULL,
  `visible` tinyint(1) NOT NULL,
  `create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_by` varchar(255) NOT NULL,
  `last_update_date` timestamp NOT NULL DEFAULT '1970-01-01 00:00:01',
  `last_update_by` varchar(255) NOT NULL,
  `views` int(11) NOT NULL,
  `menu_index` int(11) NOT NULL,
  `url` varchar(255) DEFAULT NULL,
  `extra_data` varchar(255) DEFAULT NULL,
  `title` varchar(100) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `keywords` varchar(255) DEFAULT NULL,
  `seo_friendly_url` varchar(100) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `search_index` tinyint(1) DEFAULT '1'
);

ALTER TABLE `CMS_PAGES`
  ADD PRIMARY KEY (`id`);

[Phillip911]

I have try the new code, but still get error

Static analysis:

2 errors were found during analysis.

A comma or a closing bracket was expected. (near "(" at position 842)
Unexpected beginning of statement. (near "`id`" at position 843)
SQL query:

-- -- Table structure for table `CMS_PAGES` -- CREATE TABLE `CMS_PAGES` ( `id` int(10) unsigned NOT NULL auto_increment, `category_id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `content` text NOT NULL, `home` tinyint(1) NOT NULL default '0', `visible` tinyint(1) NOT NULL, `create_date` timestamp NOT NULL, `created_by` varchar(255) NOT NULL, `last_update_date` timestamp NOT NULL default '1970-01-01 00:00:01', `last_update_by` varchar(255) NOT NULL, `views` int(11) NOT NULL, `menu_index` smallint(4) NOT NULL, `url` varchar(255) NULL, `extra_data` varchar(255) NULL, `title` VARCHAR(100), `description` VARCHAR(255), `keywords` VARCHAR(255), `seo_friendly_url` VARCHAR(100), `parent_id` int(11) DEFAULT NULL, `search_index` int(1) DEFAULT '1' PRIMARY KEY (`id`))

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(`id`))' at line 49

[Pablo]

Based on the error it does not look like you have used the 'new code'

[Phillip911]

Opps, sorry. I got the wrong paste.

It said this

Error
SQL query:


CREATE TABLE `CMS_PAGES` (
`id` int(10) UNSIGNED NOT NULL,
`category_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`content` text NOT NULL,
`home` tinyint(1) NOT NULL,
`visible` tinyint(1) NOT NULL,
`create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`created_by` varchar(255) NOT NULL,
`last_update_date` timestamp NOT NULL DEFAULT '1970-01-01 00:00:01',
`last_update_by` varchar(255) NOT NULL,
`views` int(11) NOT NULL,
`menu_index` int(11) NOT NULL,
`url` varchar(255) DEFAULT NULL,
`extra_data` varchar(255) DEFAULT NULL,
`title` varchar(100) DEFAULT NULL,
`description` varchar(255) DEFAULT NULL,
`keywords` varchar(255) DEFAULT NULL,
`seo_friendly_url` varchar(100) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
`search_index` tinyint(1) DEFAULT '1'
)
MySQL said: Documentation

#1046 - No database selected

[Phillip911]

-

[Pablo]

You will need to select a database first in PHP MyAdmin.

[Phillip911]

Is it like this ?




Sorry I'm just new with the software.

[Pablo]

It looks like your server expects another default date, you can try '0000-00-00 00:00:00' instead.

Note that this is not specific to the software.

It is assumed that if you want to work with MySQL that you know what you are doing.
I cannot teach you how to use PHP myAdmin.

[Phillip911]

Yea, i try try before. It same, not working.

[Pablo]

Then there may be an issue on the server side.

[WWBman]

If you are using the SQL documented in CMS Admin Help then the penultimate line should have a comma at the end.
Did you try this (after creating the Database)?

CREATE TABLE `CMS_PAGES` (
`id` int(10) unsigned NOT NULL auto_increment,
`category_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`content` text NOT NULL,
`home` tinyint(1) NOT NULL default '0',
`visible` tinyint(1) NOT NULL,
`create_date` timestamp NOT NULL,
`created_by` varchar(255) NOT NULL,
`last_update_date` timestamp NOT NULL default '1970-01-01 00:00:01',
`last_update_by` varchar(255) NOT NULL,
`views` int(11) NOT NULL,
`menu_index` smallint(4) NOT NULL,
`url` varchar(255) NULL,
`extra_data` varchar(255) NULL,
`title` VARCHAR(100),
`description` VARCHAR(255),
`keywords` VARCHAR(255),
`seo_friendly_url` VARCHAR(100),
`parent_id` int(11) DEFAULT NULL,
`search_index` int(1) DEFAULT '1',
PRIMARY KEY (`id`));

[Phillip911]

Thanks WWBman. I do not notice need to put comma at penultimate line. Now only have this error

#1067 - Invalid default value for 'last_update_date'

Did you know how to fix ? I have try "1970-01-01 00:00:01" and "0000-00-00 00:00:00". Both not work

Thanks again

[WWBman]

My CMS uses '1970-01-01 00:00:01' as stated in the Help.
Why are you creating the table manually?
The documentation states:
The CMS will automatically create the required tables if they do not exist, so you do not have to create the tables manually.
The following tables will be created (note that the table prefix 'CMS_' is configurable):
CMS_PAGES
This table contains the page information like page title, content, created/updated date, view count etc.
CMS_SEARCH_WORDS
This table is part of the search index and contains all unique words.
CMS_SEARCH_WORDMATCH
This table is part of the search index and contains the word to page relationship.

[Phillip911]

The file don't create it automatically, every time i login to the admin it always show this error

Invalid query: Invalid default value for 'last_update_date'

That's why i do it manually

[WWBman]

The file don't create it automatically, every time i login to the admin it always show this error

Invalid query: Invalid default value for 'last_update_date'

That's why i do it manually

Then as Pablo said, there may be an issue on the server side.

[Pablo]

You will need to figure out what the default date format for this server is.

[WWBman]

Just a thought.
Are you using a local server e.g. XAMPP?
I just tried it on my XAMPP server and it gave the error "Invalid query: Invalid default value for 'last_update_date'".

I then used Admin to create the tables on my 'normal' Linux host and it worked.
It created all the tables with no error message.

[Phillip911]

Nop, I'm using c-panel server. My server is Linux system. Now i'm waiting my hosting provider to answer me about the default time

[WWBman]

Nop, I'm using c-panel server. My server is Linux system. Now i'm waiting my hosting provider to answer me about the default time

OK.

[Phillip911]

It said

The default date format in this server is same as Singapore time and date

Any idea about it ?

[Pablo]

Did you try to remove the default date?

[Phillip911]

Did you try to remove the default date?

No, I haven't try yet. But how to remove ?

[Pablo]

Code: Select all

CREATE TABLE `CMS_PAGES` (
  `id` int(10) UNSIGNED NOT NULL,
  `category_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `home` tinyint(1) NOT NULL,
  `visible` tinyint(1) NOT NULL,
  `create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_by` varchar(255) NOT NULL,
  `last_update_date` timestamp NOT NULL,
  `last_update_by` varchar(255) NOT NULL,
  `views` int(11) NOT NULL,
  `menu_index` int(11) NOT NULL,
  `url` varchar(255) DEFAULT NULL,
  `extra_data` varchar(255) DEFAULT NULL,
  `title` varchar(100) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `keywords` varchar(255) DEFAULT NULL,
  `seo_friendly_url` varchar(100) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `search_index` tinyint(1) DEFAULT '1'
);

ALTER TABLE `CMS_PAGES`
  ADD PRIMARY KEY (`id`);

[Phillip911]

Code: Select all

CREATE TABLE `CMS_PAGES` (
  `id` int(10) UNSIGNED NOT NULL,
  `category_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `home` tinyint(1) NOT NULL,
  `visible` tinyint(1) NOT NULL,
  `create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_by` varchar(255) NOT NULL,
  `last_update_date` timestamp NOT NULL,
  `last_update_by` varchar(255) NOT NULL,
  `views` int(11) NOT NULL,
  `menu_index` int(11) NOT NULL,
  `url` varchar(255) DEFAULT NULL,
  `extra_data` varchar(255) DEFAULT NULL,
  `title` varchar(100) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `keywords` varchar(255) DEFAULT NULL,
  `seo_friendly_url` varchar(100) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `search_index` tinyint(1) DEFAULT '1'
);

ALTER TABLE `CMS_PAGES`
  ADD PRIMARY KEY (`id`);

Tried, still same error

[Pablo]

How can you have the same error?
There is no default date in the query anymore...

[Phillip911]

How can you have the same error?
There is no default date in the query anymore...

Like this right ?

[Pablo]

Note that this has nothing to do with the software.

Did you get a response from your webhost?

[Phillip911]

Yea, I know the error not related with the software. I just want find who have experience to solve this issue.

They replied

The default date format in this server is same as Singapore time and date

I have no idea about it

[Pablo]

I have no idea about it

Maybe they can tell you?

[Phillip911]

Maybe they can tell you?

I think they also don't know what i'm asking and what is it..... RIP

A lesson for me, DON'T BUY CHEAP SERVER

[Phillip911]

Pablo, Did you have any suggesting on web hosting provider that 100% combability with WWB without configure this kind of issue ?

[Pablo]

This is not a matter of compatibility.
You should be able to create the databases with any web host that supports MYSQL.

However, you will need to figure out what the default date format for the server is.
Or ask someone who has more knowledge about MySQL.

[Phillip911]

A Good news is, i success create the SQL manually

Code: Select all

-- 

-- Table structure for table `CMS_PAGES`

-- 

CREATE TABLE `CMS_PAGES` (

`id` int(10) unsigned NOT NULL auto_increment,

`category_id` int(11) NOT NULL,

`name` varchar(255) NOT NULL,

`content` text NOT NULL,

`home` tinyint(1) NOT NULL default '0',

`visible` tinyint(1) NOT NULL,

`create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

`created_by` varchar(255) NOT NULL,

`last_update_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

`last_update_by` varchar(255) NOT NULL,

`views` int(11) NOT NULL,

`menu_index` smallint(4) NOT NULL,

`url` varchar(255) NULL,

`extra_data` varchar(255) NULL,

`title` VARCHAR(100), 

`description` VARCHAR(255), 

`keywords` VARCHAR(255), 

`seo_friendly_url` VARCHAR(100),

`parent_id` int(11) DEFAULT NULL,

`search_index` int(1) DEFAULT '1',

PRIMARY KEY  (`id`));
 

 
-- 

-- Table structure for table `CMS_SEARCH_WORDMATCH`

-- 

CREATE TABLE `CMS_SEARCH_WORDMATCH` (

`page_id` int(10) unsigned NOT NULL,

`word_id` int(10) unsigned NOT NULL,

PRIMARY KEY  (`page_id`,`word_id`));

 

 

-- 

-- Table structure for table `CMS_SEARCH_WORDS`

-- 

CREATE TABLE `CMS_SEARCH_WORDS` (

`id` int(10) unsigned NOT NULL auto_increment,

`word` varchar(50) NOT NULL,

PRIMARY KEY  (`id`)) ;

BUT, The bad news is. I still getting the same error while i login with the admin.......
anymore idea ?

[Pablo]

What is the error?
What is your configuration?
Can you please be more specific?

[Phillip911]

This is the error

Invalid query: Invalid default value for 'last_update_date'

I have done created the SQL manually, while i try login to the cms admin. Get this error.
The code i use for create the SQL have send above

[Pablo]

It looks like it's still not configured correctly.
This is a server related issue.

[wwonderfull]

just dropping an opinion if you want you can get a webhost from hostinger(dot)com or whois(dot)com or even bluehost(dot)com all of them have a decent price with good and compatible service.

[Phillip911]

just dropping an opinion if you want you can get a webhost from hostinger(dot)com or whois(dot)com or even bluehost(dot)com all of them have a decent price with good and compatible service.

Thanks for suggestion

[WWBman]

The sql generated by wwb contains:
last_update_date TIMESTAMP NOT NULL,
so the issue could be the sql mode NO_ZERO_DATE.
I got the same error when using XAMPP.
In the SQL file my.ini there is:
sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION
When I changed this to:
sql_mode=NO_ZERO_IN_DATE,NO_ENGINE_SUBSTITUTION
the table was created successfully with no error message.

If you can find the equivalent file on your server and remove the NO_ZERO_DATE then this might solve the problem.

BUT, I am no expert in SQL options and although this seems to work on my XAMPP system, it may not be applicable on a 'live' server.
On my Linux server I do not get the error message.

[Phillip911]

The sql generated by wwb contains:
last_update_date TIMESTAMP NOT NULL,
so the issue could be the sql mode NO_ZERO_DATE.
I got the same error when using XAMPP.
In the SQL file my.ini there is:
sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION
When I changed this to:
sql_mode=NO_ZERO_IN_DATE,NO_ENGINE_SUBSTITUTION
the table was created successfully with no error message.

If you can find the equivalent file on your server and remove the NO_ZERO_DATE then this might solve the problem.

BUT, I am no expert in SQL options and although this seems to work on my XAMPP system, it may not be applicable on a 'live' server.
On my Linux server I do not get the error message.

Sorry bro, i tried. Still not from me. But i found a solution.

Use Hostinger... All my issue manage to solve, no even need to create the database manually.

Thanks for help