Manually created database error

Issues related to the CMS tools of WYSIWYG Web Builder.
Forum rules
PLEASE READ THE FORUM RULES BEFORE YOU POST:
viewtopic.php?f=12&t=1901

MUST READ:
http://www.wysiwygwebbuilder.com/cms_tools.html
A lot of information about the Content Manager System can be found in the help/manual. Please read this first before posting any questions! Also check out the demo template that is include with the software.

CMS trouble shooting / FAQ:
viewtopic.php?f=10&t=43245
Post Reply
Phillip911
 
 
Posts: 39
Joined: Fri Jan 21, 2022 12:44 pm

Manually created database error

Post by Phillip911 »

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

Image --- Screenshot


Any idea how to fix it ?
User avatar
Pablo
 
Posts: 21578
Joined: Sun Mar 28, 2004 12:00 pm
Location: Europe
Contact:

Re: Manually created database error

Post by Pablo »

Maybe there is no space between

Code: Select all

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

Code: Select all

PRIMARY KEY  (`id`));
Phillip911
 
 
Posts: 39
Joined: Fri Jan 21, 2022 12:44 pm

Re: Manually created database error

Post by Phillip911 »

Pablo wrote: Thu Mar 10, 2022 7:39 am 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
 
 
Posts: 916
Joined: Fri Jan 08, 2010 6:10 pm

Re: Manually created database error

Post by WWBman »

Shouldn't there be a comma after DEFAULT '1'
User avatar
Pablo
 
Posts: 21578
Joined: Sun Mar 28, 2004 12:00 pm
Location: Europe
Contact:

Re: Manually created database error

Post by 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
 
 
Posts: 39
Joined: Fri Jan 21, 2022 12:44 pm

Re: Manually created database error

Post by Phillip911 »

I have try the new code, but still get error :cry:
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
User avatar
Pablo
 
Posts: 21578
Joined: Sun Mar 28, 2004 12:00 pm
Location: Europe
Contact:

Re: Manually created database error

Post by Pablo »

Based on the error it does not look like you have used the 'new code'
Phillip911
 
 
Posts: 39
Joined: Fri Jan 21, 2022 12:44 pm

Re: Manually created database error

Post by 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
 
 
Posts: 39
Joined: Fri Jan 21, 2022 12:44 pm

Re: Manually created database error

Post by Phillip911 »

-
Last edited by Phillip911 on Thu Mar 10, 2022 10:54 am, edited 1 time in total.
User avatar
Pablo
 
Posts: 21578
Joined: Sun Mar 28, 2004 12:00 pm
Location: Europe
Contact:

Re: Manually created database error

Post by Pablo »

You will need to select a database first in PHP MyAdmin.
Phillip911
 
 
Posts: 39
Joined: Fri Jan 21, 2022 12:44 pm

Re: Manually created database error

Post by Phillip911 »

Is it like this ?

Image


Sorry I'm just new with the software.
User avatar
Pablo
 
Posts: 21578
Joined: Sun Mar 28, 2004 12:00 pm
Location: Europe
Contact:

Re: Manually created database error

Post by 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
 
 
Posts: 39
Joined: Fri Jan 21, 2022 12:44 pm

Re: Manually created database error

Post by Phillip911 »

Yea, i try try before. It same, not working.
User avatar
Pablo
 
Posts: 21578
Joined: Sun Mar 28, 2004 12:00 pm
Location: Europe
Contact:

Re: Manually created database error

Post by Pablo »

Then there may be an issue on the server side.
WWBman
 
 
Posts: 916
Joined: Fri Jan 08, 2010 6:10 pm

Re: Manually created database error

Post by 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
 
 
Posts: 39
Joined: Fri Jan 21, 2022 12:44 pm

Re: Manually created database error

Post by 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
 
 
Posts: 916
Joined: Fri Jan 08, 2010 6:10 pm

Re: Manually created database error

Post by 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
 
 
Posts: 39
Joined: Fri Jan 21, 2022 12:44 pm

Re: Manually created database error

Post by 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
 
 
Posts: 916
Joined: Fri Jan 08, 2010 6:10 pm

Re: Manually created database error

Post by WWBman »

Phillip911 wrote: Thu Mar 10, 2022 2:02 pm 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.
User avatar
Pablo
 
Posts: 21578
Joined: Sun Mar 28, 2004 12:00 pm
Location: Europe
Contact:

Re: Manually created database error

Post by Pablo »

You will need to figure out what the default date format for this server is.
WWBman
 
 
Posts: 916
Joined: Fri Jan 08, 2010 6:10 pm

Re: Manually created database error

Post by 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
 
 
Posts: 39
Joined: Fri Jan 21, 2022 12:44 pm

Re: Manually created database error

Post by 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
 
 
Posts: 916
Joined: Fri Jan 08, 2010 6:10 pm

Re: Manually created database error

Post by WWBman »

Phillip911 wrote: Thu Mar 10, 2022 4:33 pm 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
 
 
Posts: 39
Joined: Fri Jan 21, 2022 12:44 pm

Re: Manually created database error

Post by Phillip911 »

It said
The default date format in this server is same as Singapore time and date
Any idea about it ?
User avatar
Pablo
 
Posts: 21578
Joined: Sun Mar 28, 2004 12:00 pm
Location: Europe
Contact:

Re: Manually created database error

Post by Pablo »

Did you try to remove the default date?
Phillip911
 
 
Posts: 39
Joined: Fri Jan 21, 2022 12:44 pm

Re: Manually created database error

Post by Phillip911 »

Pablo wrote: Fri Mar 11, 2022 7:01 am Did you try to remove the default date?
No, I haven't try yet. But how to remove ?
User avatar
Pablo
 
Posts: 21578
Joined: Sun Mar 28, 2004 12:00 pm
Location: Europe
Contact:

Re: Manually created database error

Post by 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
 
 
Posts: 39
Joined: Fri Jan 21, 2022 12:44 pm

Re: Manually created database error

Post by Phillip911 »

Pablo wrote: Fri Mar 11, 2022 7:20 am

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
User avatar
Pablo
 
Posts: 21578
Joined: Sun Mar 28, 2004 12:00 pm
Location: Europe
Contact:

Re: Manually created database error

Post by Pablo »

How can you have the same error?
There is no default date in the query anymore...
Phillip911
 
 
Posts: 39
Joined: Fri Jan 21, 2022 12:44 pm

Re: Manually created database error

Post by Phillip911 »

Pablo wrote: Fri Mar 11, 2022 7:36 am How can you have the same error?
There is no default date in the query anymore...

Image


Like this right ?
User avatar
Pablo
 
Posts: 21578
Joined: Sun Mar 28, 2004 12:00 pm
Location: Europe
Contact:

Re: Manually created database error

Post by Pablo »

Note that this has nothing to do with the software.

Did you get a response from your webhost?
Phillip911
 
 
Posts: 39
Joined: Fri Jan 21, 2022 12:44 pm

Re: Manually created database error

Post by 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 :cry:
User avatar
Pablo
 
Posts: 21578
Joined: Sun Mar 28, 2004 12:00 pm
Location: Europe
Contact:

Re: Manually created database error

Post by Pablo »

I have no idea about it
Maybe they can tell you?
Phillip911
 
 
Posts: 39
Joined: Fri Jan 21, 2022 12:44 pm

Re: Manually created database error

Post by Phillip911 »

Pablo wrote: Fri Mar 11, 2022 10:47 am 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
 
 
Posts: 39
Joined: Fri Jan 21, 2022 12:44 pm

Re: Manually created database error

Post by Phillip911 »

Pablo, Did you have any suggesting on web hosting provider that 100% combability with WWB without configure this kind of issue ?
User avatar
Pablo
 
Posts: 21578
Joined: Sun Mar 28, 2004 12:00 pm
Location: Europe
Contact:

Re: Manually created database error

Post by 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
 
 
Posts: 39
Joined: Fri Jan 21, 2022 12:44 pm

Re: Manually created database error

Post by 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 ?
User avatar
Pablo
 
Posts: 21578
Joined: Sun Mar 28, 2004 12:00 pm
Location: Europe
Contact:

Re: Manually created database error

Post by Pablo »

What is the error?
What is your configuration?
Can you please be more specific?
Phillip911
 
 
Posts: 39
Joined: Fri Jan 21, 2022 12:44 pm

Re: Manually created database error

Post by 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
User avatar
Pablo
 
Posts: 21578
Joined: Sun Mar 28, 2004 12:00 pm
Location: Europe
Contact:

Re: Manually created database error

Post by Pablo »

It looks like it's still not configured correctly.
This is a server related issue.
wwonderfull
 
 
Posts: 1255
Joined: Fri Aug 21, 2020 8:27 am

Re: Manually created database error

Post by 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
 
 
Posts: 39
Joined: Fri Jan 21, 2022 12:44 pm

Re: Manually created database error

Post by Phillip911 »

wwonderfull wrote: Sat Mar 12, 2022 10:47 am 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
 
 
Posts: 916
Joined: Fri Jan 08, 2010 6:10 pm

Re: Manually created database error

Post by 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
 
 
Posts: 39
Joined: Fri Jan 21, 2022 12:44 pm

Re: Manually created database error

Post by Phillip911 »

WWBman wrote: Sat Mar 12, 2022 12:51 pm 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
Post Reply