From 65164295778caa2c4e623e7c8e195f2aa3560121 Mon Sep 17 00:00:00 2001
From: Marius Burkard <m.burkard@pixcept.de>
Date: Thu, 07 Apr 2016 13:19:58 -0400
Subject: [PATCH] - Changed 0000-00-00 date and datetime values to NULL (mySQL compatibility), Fixes: #3690
---
interface/web/tools/import_vpopmail.php | 4 +-
interface/lib/classes/tform_tpl_generator.inc.php | 2
install/sql/ispconfig3.sql | 20 +++++-----
install/sql/incremental/upd_dev_collection.sql | 23 +++++++++++
interface/lib/classes/remote.d/openvz.inc.php | 2
server/plugins-available/maildrop_plugin.inc.php | 2
install/tpl/pureftpd_mysql.conf.master | 20 +++++-----
interface/lib/classes/tform_base.inc.php | 12 +++---
server/plugins-available/maildeliver_plugin.inc.php | 2
interface/lib/classes/listform.inc.php | 2
server/lib/classes/cron.d/400-openvz.inc.php | 2
11 files changed, 57 insertions(+), 34 deletions(-)
diff --git a/install/sql/incremental/upd_dev_collection.sql b/install/sql/incremental/upd_dev_collection.sql
index 4333ab7..4f67ccc 100644
--- a/install/sql/incremental/upd_dev_collection.sql
+++ b/install/sql/incremental/upd_dev_collection.sql
@@ -227,3 +227,26 @@
ALTER TABLE `client_template` ADD COLUMN `limit_database_user` int(11) NOT NULL DEFAULT '-1' after limit_database;
ALTER TABLE `client` CHANGE `customer_no_template` `customer_no_template` VARCHAR(255) NULL DEFAULT 'R[CLIENTID]C[CUSTOMER_NO]';
+ALTER TABLE `client` CHANGE `added_date` `added_date` DATE NULL DEFAULT NULL;
+ALTER TABLE `ftp_user` CHANGE `expires` `expires` DATETIME NULL DEFAULT NULL;
+ALTER TABLE `mail_user` CHANGE `autoresponder_start_date` `autoresponder_start_date` DATETIME NULL DEFAULT NULL;
+ALTER TABLE `mail_user` CHANGE `autoresponder_end_date` `autoresponder_end_date` DATETIME NULL DEFAULT NULL;
+ALTER TABLE `openvz_traffic` CHANGE `traffic_date` `traffic_date` DATE NULL DEFAULT NULL;
+ALTER TABLE `openvz_vm` CHANGE `active_until_date` `active_until_date` DATE NULL DEFAULT NULL;
+ALTER TABLE `sys_session` CHANGE `date_created` `date_created` DATETIME NULL DEFAULT NULL;
+ALTER TABLE `sys_session` CHANGE `last_updated` `last_updated` DATETIME NULL DEFAULT NULL;
+ALTER TABLE `web_domain` CHANGE `added_date` `added_date` DATE NULL DEFAULT NULL;
+ALTER TABLE `web_traffic` CHANGE `traffic_date` `traffic_date` DATE NULL DEFAULT NULL;
+
+UPDATE `client` SET `added_date` = NULL WHERE `added_date` = '0000-00-00';
+UPDATE `ftp_user` SET `expires` = NULL WHERE `expires` = '0000-00-00 00:00:00';
+UPDATE `mail_user` SET `autoresponder_start_date` = NULL WHERE `autoresponder_start_date` = '0000-00-00 00:00:00';
+UPDATE `mail_user` SET `autoresponder_end_date` = NULL WHERE `autoresponder_end_date` = '0000-00-00 00:00:00';
+UPDATE `openvz_traffic` SET `traffic_date` = NULL WHERE `traffic_date` = '0000-00-00';
+UPDATE `openvz_vm` SET `active_until_date` = NULL WHERE `active_until_date` = '0000-00-00';
+UPDATE `sys_session` SET `date_created` = NULL WHERE `date_created` = '0000-00-00 00:00:00';
+UPDATE `sys_session` SET `last_updated` = NULL WHERE `last_updated` = '0000-00-00 00:00:00';
+UPDATE `web_domain` SET `added_date` = NULL WHERE `added_date` = '0000-00-00';
+UPDATE `web_traffic` SET `traffic_date` = NULL WHERE `traffic_date` = '0000-00-00';
+
+
diff --git a/install/sql/ispconfig3.sql b/install/sql/ispconfig3.sql
index 6eed418..17b08b6 100644
--- a/install/sql/ispconfig3.sql
+++ b/install/sql/ispconfig3.sql
@@ -258,7 +258,7 @@
`customer_no_template` varchar(255) DEFAULT 'R[CLIENTID]C[CUSTOMER_NO]',
`customer_no_start` int(11) NOT NULL DEFAULT '1',
`customer_no_counter` int(11) NOT NULL DEFAULT '0',
- `added_date` date NOT NULL DEFAULT '0000-00-00',
+ `added_date` date NULL DEFAULT NULL,
`added_by` varchar(255) DEFAULT NULL,
`validation_status` enum('accept','review','reject') NOT NULL DEFAULT 'accept',
`risk_score` int(10) unsigned NOT NULL DEFAULT '0',
@@ -638,7 +638,7 @@
`dl_ratio` int(11) NOT NULL default '-1',
`ul_bandwidth` int(11) NOT NULL default '-1',
`dl_bandwidth` int(11) NOT NULL default '-1',
- `expires` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+ `expires` datetime NULL DEFAULT NULL,
PRIMARY KEY (`ftp_user_id`),
KEY `active` (`active`),
KEY `server_id` (`server_id`),
@@ -962,8 +962,8 @@
`sender_cc` varchar(255) NOT NULL default '',
`homedir` varchar(255) NOT NULL default '',
`autoresponder` enum('n','y') NOT NULL default 'n',
- `autoresponder_start_date` datetime NOT NULL default '0000-00-00 00:00:00',
- `autoresponder_end_date` datetime NOT NULL default '0000-00-00 00:00:00',
+ `autoresponder_start_date` datetime NULL default NULL,
+ `autoresponder_end_date` datetime NULL default NULL,
`autoresponder_subject` varchar(255) NOT NULL default 'Out of office reply',
`autoresponder_text` mediumtext NULL,
`move_junk` enum('n','y') NOT NULL default 'n',
@@ -1151,7 +1151,7 @@
CREATE TABLE IF NOT EXISTS `openvz_traffic` (
`veid` int(11) NOT NULL DEFAULT '0',
- `traffic_date` date NOT NULL DEFAULT '0000-00-00',
+ `traffic_date` date NULL DEFAULT NULL,
`traffic_bytes` bigint(32) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`veid`,`traffic_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
@@ -1184,7 +1184,7 @@
`start_boot` varchar(255) NOT NULL DEFAULT 'y',
`bootorder` int(11) NOT NULL DEFAULT '1',
`active` varchar(255) NOT NULL DEFAULT 'y',
- `active_until_date` date NOT NULL DEFAULT '0000-00-00',
+ `active_until_date` date NULL DEFAULT NULL,
`description` text,
`diskspace` int(11) NOT NULL DEFAULT '0',
`traffic` int(11) NOT NULL DEFAULT '-1',
@@ -1739,8 +1739,8 @@
CREATE TABLE `sys_session` (
`session_id` varchar(64) NOT NULL DEFAULT '',
- `date_created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
- `last_updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+ `date_created` datetime NULL DEFAULT NULL,
+ `last_updated` datetime NULL DEFAULT NULL,
`permanent` enum('n','y') NOT NULL DEFAULT 'n',
`session_data` longtext,
PRIMARY KEY (`session_id`),
@@ -1972,7 +1972,7 @@
`enable_spdy` ENUM('y','n') NULL DEFAULT 'n',
`last_quota_notification` date NULL default NULL,
`rewrite_rules` mediumtext,
- `added_date` date NOT NULL DEFAULT '0000-00-00',
+ `added_date` date NULL DEFAULT NULL,
`added_by` varchar(255) DEFAULT NULL,
`directive_snippets_id` int(11) unsigned NOT NULL default '0',
`enable_pagespeed` ENUM('y','n') NOT NULL DEFAULT 'n',
@@ -2041,7 +2041,7 @@
CREATE TABLE `web_traffic` (
`hostname` varchar(255) NOT NULL DEFAULT '',
- `traffic_date` date NOT NULL DEFAULT '0000-00-00',
+ `traffic_date` date NULL DEFAULT NULL,
`traffic_bytes` bigint(32) unsigned NOT NULL default '0',
PRIMARY KEY (`hostname`,`traffic_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
diff --git a/install/tpl/pureftpd_mysql.conf.master b/install/tpl/pureftpd_mysql.conf.master
index fcf79bb..32d2159 100644
--- a/install/tpl/pureftpd_mysql.conf.master
+++ b/install/tpl/pureftpd_mysql.conf.master
@@ -59,12 +59,12 @@
# Query to execute in order to fetch the password
-MYSQLGetPW SELECT password FROM ftp_user WHERE active = 'y' AND server_id = '{server_id}' AND username="\L" AND (expires = "0000-00-00 00:00:00" OR expires > NOW())
+MYSQLGetPW SELECT password FROM ftp_user WHERE active = 'y' AND server_id = '{server_id}' AND username="\L" AND (expires IS NULL OR expires > NOW())
# Query to execute in order to fetch the system user name or uid
-MYSQLGetUID SELECT uid FROM ftp_user WHERE active = 'y' AND server_id = '{server_id}' AND username="\L" AND (expires = "0000-00-00 00:00:00" OR expires > NOW())
+MYSQLGetUID SELECT uid FROM ftp_user WHERE active = 'y' AND server_id = '{server_id}' AND username="\L" AND (expires IS NULL OR expires > NOW())
# Optional : default UID - if set this overrides MYSQLGetUID
@@ -74,7 +74,7 @@
# Query to execute in order to fetch the system user group or gid
-MYSQLGetGID SELECT gid FROM ftp_user WHERE active = 'y' AND server_id = '{server_id}' AND username="\L" AND (expires = "0000-00-00 00:00:00" OR expires > NOW())
+MYSQLGetGID SELECT gid FROM ftp_user WHERE active = 'y' AND server_id = '{server_id}' AND username="\L" AND (expires IS NULL OR expires > NOW())
# Optional : default GID - if set this overrides MYSQLGetGID
@@ -84,34 +84,34 @@
# Query to execute in order to fetch the home directory
-MYSQLGetDir SELECT dir FROM ftp_user WHERE active = 'y' AND server_id = '{server_id}' AND username="\L" AND (expires = "0000-00-00 00:00:00" OR expires > NOW())
+MYSQLGetDir SELECT dir FROM ftp_user WHERE active = 'y' AND server_id = '{server_id}' AND username="\L" AND (expires IS NULL OR expires > NOW())
# Optional : query to get the maximal number of files
# Pure-FTPd must have been compiled with virtual quotas support.
-MySQLGetQTAFS SELECT quota_files FROM ftp_user WHERE active = 'y' AND server_id = '{server_id}' AND quota_files != '-1' AND username="\L" AND (expires = "0000-00-00 00:00:00" OR expires > NOW())
+MySQLGetQTAFS SELECT quota_files FROM ftp_user WHERE active = 'y' AND server_id = '{server_id}' AND quota_files != '-1' AND username="\L" AND (expires IS NULL OR expires > NOW())
# Optional : query to get the maximal disk usage (virtual quotas)
# The number should be in Megabytes.
# Pure-FTPd must have been compiled with virtual quotas support.
-MySQLGetQTASZ SELECT quota_size FROM ftp_user WHERE active = 'y' AND server_id = '{server_id}' AND quota_size != '-1' AND username="\L" AND (expires = "0000-00-00 00:00:00" OR expires > NOW())
+MySQLGetQTASZ SELECT quota_size FROM ftp_user WHERE active = 'y' AND server_id = '{server_id}' AND quota_size != '-1' AND username="\L" AND (expires IS NULL OR expires > NOW())
# Optional : ratios. The server has to be compiled with ratio support.
-MySQLGetRatioUL SELECT ul_ratio FROM ftp_user WHERE active = 'y' AND server_id = '{server_id}' AND ul_ratio != '-1' AND username="\L" AND (expires = "0000-00-00 00:00:00" OR expires > NOW())
-MySQLGetRatioDL SELECT dl_ratio FROM ftp_user WHERE active = 'y' AND server_id = '{server_id}' AND dl_ratio != '-1' AND username="\L" AND (expires = "0000-00-00 00:00:00" OR expires > NOW())
+MySQLGetRatioUL SELECT ul_ratio FROM ftp_user WHERE active = 'y' AND server_id = '{server_id}' AND ul_ratio != '-1' AND username="\L" AND (expires IS NULL OR expires > NOW())
+MySQLGetRatioDL SELECT dl_ratio FROM ftp_user WHERE active = 'y' AND server_id = '{server_id}' AND dl_ratio != '-1' AND username="\L" AND (expires IS NULL OR expires > NOW())
# Optional : bandwidth throttling.
# The server has to be compiled with throttling support.
# Values are in KB/s .
-MySQLGetBandwidthUL SELECT ul_bandwidth FROM ftp_user WHERE active = 'y' AND server_id = '{server_id}' AND ul_bandwidth != '-1' AND username="\L" AND (expires = "0000-00-00 00:00:00" OR expires > NOW())
-MySQLGetBandwidthDL SELECT dl_bandwidth FROM ftp_user WHERE active = 'y' AND server_id = '{server_id}' AND dl_bandwidth != '-1' AND username="\L" AND (expires = "0000-00-00 00:00:00" OR expires > NOW())
+MySQLGetBandwidthUL SELECT ul_bandwidth FROM ftp_user WHERE active = 'y' AND server_id = '{server_id}' AND ul_bandwidth != '-1' AND username="\L" AND (expires IS NULL OR expires > NOW())
+MySQLGetBandwidthDL SELECT dl_bandwidth FROM ftp_user WHERE active = 'y' AND server_id = '{server_id}' AND dl_bandwidth != '-1' AND username="\L" AND (expires IS NULL OR expires > NOW())
# Enable ~ expansion. NEVER ENABLE THIS BLINDLY UNLESS :
# 1) You know what you are doing.
diff --git a/interface/lib/classes/listform.inc.php b/interface/lib/classes/listform.inc.php
index 120e652..af6c3ff 100644
--- a/interface/lib/classes/listform.inc.php
+++ b/interface/lib/classes/listform.inc.php
@@ -563,7 +563,7 @@
break;
case 'DATE':
- if($record[$key] != '' && $record[$key] != '0000-00-00') {
+ if($record[$key] != '' && !is_null($record[$key]) && $record[$key] != '0000-00-00') {
$record[$key] = $record[$key];
}
break;
diff --git a/interface/lib/classes/remote.d/openvz.inc.php b/interface/lib/classes/remote.d/openvz.inc.php
index c427a1f..a4ef38a 100644
--- a/interface/lib/classes/remote.d/openvz.inc.php
+++ b/interface/lib/classes/remote.d/openvz.inc.php
@@ -307,7 +307,7 @@
$params['vm_password'] = (isset($override_params['vm_password']))?$override_params['vm_password']:$app->auth->get_random_password(10);
$params['start_boot'] = (isset($override_params['start_boot']))?$override_params['start_boot']:'y';
$params['active'] = (isset($override_params['active']))?$override_params['active']:'y';
- $params['active_until_date'] = (isset($override_params['active_until_date']))?$override_params['active_until_date']:'0000-00-00';
+ $params['active_until_date'] = (isset($override_params['active_until_date']))?$override_params['active_until_date']:null;
$params['description'] = (isset($override_params['description']))?$override_params['description']:'';
//* The next params get filled with pseudo values, as the get replaced
diff --git a/interface/lib/classes/tform_base.inc.php b/interface/lib/classes/tform_base.inc.php
index 4405de4..1739c1f 100644
--- a/interface/lib/classes/tform_base.inc.php
+++ b/interface/lib/classes/tform_base.inc.php
@@ -206,7 +206,7 @@
break;
case 'DATE':
- if($record[$key] != '' && $record[$key] != '0000-00-00') {
+ if($record[$key] != '' && !is_null($record[$key]) && $record[$key] != '0000-00-00') {
$tmp = explode('-', $record[$key]);
$new_record[$key] = date($this->dateformat, mktime(0, 0, 0, $tmp[1] , $tmp[2], $tmp[0]));
}
@@ -770,7 +770,7 @@
}
break;
case 'DATE':
- if($record[$key] != '' && $record[$key] != '0000-00-00') {
+ if($record[$key] != '' && !is_null($record[$key]) && $record[$key] != '0000-00-00') {
if(function_exists('date_parse_from_format')) {
$date_parts = date_parse_from_format($this->dateformat, $record[$key]);
$new_record[$key] = $date_parts['year'].'-'.str_pad($date_parts['month'], 2, "0", STR_PAD_LEFT).'-'.str_pad($date_parts['day'], 2, "0", STR_PAD_LEFT);
@@ -779,7 +779,7 @@
$new_record[$key] = date('Y-m-d', $tmp);
}
} else {
- $new_record[$key] = '0000-00-00';
+ $new_record[$key] = null;
}
break;
case 'INTEGER':
@@ -802,19 +802,19 @@
$new_record[$key] = date( 'Y-m-d H:i:s', mktime($_dt_hour, $_dt_minute, $_dt_second, $_dt_month, $_dt_day, $_dt_year) );
}
} else {*/
- if($record[$key] != '' && $record[$key] != '0000-00-00 00:00:00') {
+ if($record[$key] != '' && !is_null($record[$key]) && $record[$key] != '0000-00-00 00:00:00') {
//$tmp = strtotime($record[$key]);
//$new_record[$key] = date($this->datetimeformat, $tmp);
$parsed_date = date_parse_from_format($this->datetimeformat,$record[$key]);
if($parsed_date['error_count'] > 0 || ($parsed_date['year'] == 1899 && $parsed_date['month'] == 12 && $parsed_date['day'] == 31)) {
// There was an error, set the date to 0
- $new_record[$key] = '0000-00-00 00:00:00';
+ $new_record[$key] = null;
} else {
// Date parsed successfully. Convert it to database format
$new_record[$key] = date( 'Y-m-d H:i:s', mktime($parsed_date['hour'], $parsed_date['minute'], $parsed_date['second'], $parsed_date['month'], $parsed_date['day'], $parsed_date['year']) );
}
} else {
- $new_record[$key] = '0000-00-00 00:00:00';
+ $new_record[$key] = null;
}
/*}*/
break;
diff --git a/interface/lib/classes/tform_tpl_generator.inc.php b/interface/lib/classes/tform_tpl_generator.inc.php
index b759e51..348ac52 100644
--- a/interface/lib/classes/tform_tpl_generator.inc.php
+++ b/interface/lib/classes/tform_tpl_generator.inc.php
@@ -273,7 +273,7 @@
case 'DATE':
$type = 'date';
$typevalue = '';
- $defaultValue = ($field["default"] != '')?$field["default"]:'0000-00-00';
+ $defaultValue = ($field["default"] != '')?$field["default"]:null;
break;
}
diff --git a/interface/web/tools/import_vpopmail.php b/interface/web/tools/import_vpopmail.php
index 3c8db20..242ea5f 100644
--- a/interface/web/tools/import_vpopmail.php
+++ b/interface/web/tools/import_vpopmail.php
@@ -231,8 +231,8 @@
"cc" => '',
"homedir" => '/var/vmail',
"autoresponder" => 'n',
- "autoresponder_start_date" => '0000-00-00 00:00:00',
- "autoresponder_end_date" => '0000-00-00 00:00:00',
+ "autoresponder_start_date" => null,
+ "autoresponder_end_date" => null,
"autoresponder_subject" => 'Out of office reply',
"autoresponder_text" => '',
"move_junk" => 'n',
diff --git a/server/lib/classes/cron.d/400-openvz.inc.php b/server/lib/classes/cron.d/400-openvz.inc.php
index 5eba8d2..c88e0e6 100644
--- a/server/lib/classes/cron.d/400-openvz.inc.php
+++ b/server/lib/classes/cron.d/400-openvz.inc.php
@@ -56,7 +56,7 @@
if ($app->dbmaster == $app->db) {
//* Check which virtual machines have to be deactivated
- $sql = "SELECT * FROM openvz_vm WHERE active = 'y' AND active_until_date != '0000-00-00' AND active_until_date < CURDATE()";
+ $sql = "SELECT * FROM openvz_vm WHERE active = 'y' AND active_until_date IS NOT NULL AND active_until_date < CURDATE()";
$records = $app->db->queryAllRecords($sql);
if(is_array($records)) {
foreach($records as $rec) {
diff --git a/server/plugins-available/maildeliver_plugin.inc.php b/server/plugins-available/maildeliver_plugin.inc.php
index a6f9ae5..35001a4 100644
--- a/server/plugins-available/maildeliver_plugin.inc.php
+++ b/server/plugins-available/maildeliver_plugin.inc.php
@@ -138,7 +138,7 @@
$tpl->setVar('move_junk', $data["new"]["move_junk"]);
// Check autoresponder dates
- if($data["new"]["autoresponder_start_date"] == '0000-00-00 00:00:00' && $data["new"]["autoresponder_end_date"] == '0000-00-00 00:00:00') {
+ if((!$data['new']['autoresponder_start_date'] || $data["new"]["autoresponder_start_date"] == '0000-00-00 00:00:00') && (!$data['new']['autoresponder_end_date'] || $data["new"]["autoresponder_end_date"] == '0000-00-00 00:00:00')) {
$tpl->setVar('autoresponder_date_limit', 0);
} else {
$tpl->setVar('autoresponder_date_limit', 1);
diff --git a/server/plugins-available/maildrop_plugin.inc.php b/server/plugins-available/maildrop_plugin.inc.php
index 5e05bc3..1ebb677 100644
--- a/server/plugins-available/maildrop_plugin.inc.php
+++ b/server/plugins-available/maildrop_plugin.inc.php
@@ -144,7 +144,7 @@
}
$tpl = str_replace('{vmail_mailbox_base}', $mail_config["homedir_path"], $tpl);
- if ($data["new"]["autoresponder_start_date"] != '0000-00-00 00:00:00') { // Dates have been set
+ if ($data['new']['autoresponder_start_date'] && $data["new"]["autoresponder_start_date"] != '0000-00-00 00:00:00') { // Dates have been set
$tpl = str_replace('{start_date}', strtotime($data["new"]["autoresponder_start_date"]), $tpl);
$tpl = str_replace('{end_date}', strtotime($data["new"]["autoresponder_end_date"]), $tpl);
} else {
--
Gitblit v1.9.1