CMS admin and database problem

[avent]

Using CMS demo the tables are not created after the first-time login to the admin area.
All appears work fine, no error but tables are not created.
Any idea?

P.S.
Creating tables manually with the script shown in the help, an error occurs in default value of 'last_update_date' field

--

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



--

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

[Pablo]

The table cannot be created by the script likely for the same reason you are unable to create it manually.
What is the error when you try to create the tables manually?

[avent]

---------------- about error messages

No error appears after login, all seem to work fine at this step but the tables are not created and of course content area give error cause table non found


---------------- about the script

`last_update_date` timestamp NOT NULL default '1970-01-01 00:00:01',

....timestamp appears not allow default value on MariaDB in xampp infact in the file cmsadmin.php the function to generate script is correct without default value. of course this is not a problem while is enough delete the default value. This is only to correct the help file.

$sql = "CREATE TABLE IF NOT EXISTS CMS_PAGES (id INT UNSIGNED NOT NULL AUTO_INCREMENT,

category_id INT NOT NULL,

parent_id INT DEFAULT 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,

created_by VARCHAR(255) NOT NULL,

last_update_date TIMESTAMP NOT NULL,

last_update_by VARCHAR(255) NOT NULL,

views INT NOT NULL,

menu_index INT NOT NULL,

url VARCHAR(255),

extra_data VARCHAR(255),

title VARCHAR(100),

description VARCHAR(255),

keywords VARCHAR(255),

seo_friendly_url VARCHAR(100),

search_index tinyint(1) NOT NULL,

PRIMARY KEY(id));";

[Pablo]

The script is for MySQL, not for MariaDB.
So, you may need to adjust the SQL statement.

[avent]

The script shown in the help does not work even with MySql and differs from that of the creation function in cmsadmin.php.

The problem seems to be the TIMESTAMP field that in MariaDb (local server in XAMPP) wants 'DEFAULT CURRENT_TIMESTAMP' while MySql of the online server that I used as a test, accepts it even without default.

Maybe it's just some server settings but I'm not so expert to find the problem, I use the default settings.

Since MariaDB is very popular everywhere as a replacement for MySql, could you, without haste, do some tests and delve into the problem?

Thanks.

[Pablo]

I have tried it with MySQL and it works for me.

At the moment I do not have access to MariaDB.
But can't you try to change the statement and see if that works?

[avent]

With the tables manually created CMS demo works.

This is the script of cms_pages with small modification that works with MariaDB but I think that it works also with MySql (If I can, I'll test it later)
The other two script for other tables work fine.

in the script on Help, errors are in these two statements

...
`last_update_date` timestamp NOT NULL default '1970-01-01 00:00:01', <----- this default is not accepted
...
`search_index` int(1) DEFAULT '1' <----need a comma
...

in the script in content.php the statement
...
create_date TIMESTAMP NOT NULL, <--- need 'DEFAULT current_timestamp()' on my local MariaDB server but works fine on MySql
...


Probably is some setting.


For local test I use
https://sourceforge.net/projects/xampp/ ... 20Windows/


-------------------------------------------------

--
-- Struttura della tabella `cms_pages`
--

CREATE TABLE `cms_pages` (
`id` int(10) UNSIGNED NOT NULL,
`category_id` int(11) NOT NULL,
`parent_id` int(11) DEFAULT 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 current_timestamp(),
`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,
`search_index` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

[Pablo]

Thanks for the tip.
I have tried it and it seems to work correct on MySQL.