Page 1 of 1
Manually created database error
Posted: Thu Mar 10, 2022 5:12 am
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
--- Screenshot
Any idea how to fix it ?
Re: Manually created database error
Posted: Thu Mar 10, 2022 7:39 am
by Pablo
Maybe there is no space between
and
Re: Manually created database error
Posted: Thu Mar 10, 2022 9:08 am
by Phillip911
Pablo wrote: ↑Thu Mar 10, 2022 7:39 am
Maybe there is no space between
and
Nop, still same. Can you help me test ? I send you my c-penal account personally
Re: Manually created database error
Posted: Thu Mar 10, 2022 9:44 am
by WWBman
Shouldn't there be a comma after DEFAULT '1'
Re: Manually created database error
Posted: Thu Mar 10, 2022 10:11 am
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`);
Re: Manually created database error
Posted: Thu Mar 10, 2022 10:13 am
by 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
Re: Manually created database error
Posted: Thu Mar 10, 2022 10:18 am
by Pablo
Based on the error it does not look like you have used the 'new code'
Re: Manually created database error
Posted: Thu Mar 10, 2022 10:24 am
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
Re: Manually created database error
Posted: Thu Mar 10, 2022 10:29 am
by Phillip911
-
Re: Manually created database error
Posted: Thu Mar 10, 2022 10:29 am
by Pablo
You will need to select a database first in PHP MyAdmin.
Re: Manually created database error
Posted: Thu Mar 10, 2022 10:35 am
by Phillip911
Is it like this ?
Sorry I'm just new with the software.
Re: Manually created database error
Posted: Thu Mar 10, 2022 10:44 am
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.
Re: Manually created database error
Posted: Thu Mar 10, 2022 11:05 am
by Phillip911
Yea, i try try before. It same, not working.
Re: Manually created database error
Posted: Thu Mar 10, 2022 11:10 am
by Pablo
Then there may be an issue on the server side.
Re: Manually created database error
Posted: Thu Mar 10, 2022 12:34 pm
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`));
Re: Manually created database error
Posted: Thu Mar 10, 2022 1:00 pm
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
Re: Manually created database error
Posted: Thu Mar 10, 2022 1:42 pm
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.
Re: Manually created database error
Posted: Thu Mar 10, 2022 2:02 pm
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
Re: Manually created database error
Posted: Thu Mar 10, 2022 2:07 pm
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.
Re: Manually created database error
Posted: Thu Mar 10, 2022 2:32 pm
by Pablo
You will need to figure out what the default date format for this server is.
Re: Manually created database error
Posted: Thu Mar 10, 2022 3:42 pm
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.
Re: Manually created database error
Posted: Thu Mar 10, 2022 4:33 pm
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
Re: Manually created database error
Posted: Thu Mar 10, 2022 4:43 pm
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.
Re: Manually created database error
Posted: Fri Mar 11, 2022 3:19 am
by Phillip911
It said
The default date format in this server is same as Singapore time and date
Any idea about it ?
Re: Manually created database error
Posted: Fri Mar 11, 2022 7:01 am
by Pablo
Did you try to remove the default date?
Re: Manually created database error
Posted: Fri Mar 11, 2022 7:15 am
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 ?
Re: Manually created database error
Posted: Fri Mar 11, 2022 7:20 am
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`);
Re: Manually created database error
Posted: Fri Mar 11, 2022 7:27 am
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
Re: Manually created database error
Posted: Fri Mar 11, 2022 7:36 am
by Pablo
How can you have the same error?
There is no default date in the query anymore...
Re: Manually created database error
Posted: Fri Mar 11, 2022 10:03 am
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...
Like this right ?
Re: Manually created database error
Posted: Fri Mar 11, 2022 10:11 am
by Pablo
Note that this has nothing to do with the software.
Did you get a response from your webhost?
Re: Manually created database error
Posted: Fri Mar 11, 2022 10:27 am
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
Re: Manually created database error
Posted: Fri Mar 11, 2022 10:47 am
by Pablo
I have no idea about it
Maybe they can tell you?
Re: Manually created database error
Posted: Fri Mar 11, 2022 10:55 am
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
Re: Manually created database error
Posted: Fri Mar 11, 2022 3:33 pm
by Phillip911
Pablo, Did you have any suggesting on web hosting provider that 100% combability with WWB without configure this kind of issue ?
Re: Manually created database error
Posted: Fri Mar 11, 2022 4:04 pm
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.
Re: Manually created database error
Posted: Fri Mar 11, 2022 5:00 pm
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 ?
Re: Manually created database error
Posted: Fri Mar 11, 2022 6:07 pm
by Pablo
What is the error?
What is your configuration?
Can you please be more specific?
Re: Manually created database error
Posted: Fri Mar 11, 2022 6:39 pm
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
Re: Manually created database error
Posted: Fri Mar 11, 2022 8:55 pm
by Pablo
It looks like it's still not configured correctly.
This is a server related issue.
Re: Manually created database error
Posted: Sat Mar 12, 2022 10:47 am
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.
Re: Manually created database error
Posted: Sat Mar 12, 2022 11:58 am
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
Re: Manually created database error
Posted: Sat Mar 12, 2022 12:51 pm
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.
Re: Manually created database error
Posted: Sun Mar 13, 2022 4:29 am
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