Database Critical Error

[pols1337]

Okay, so I've recently had problems trying to upload files to my gallery.  I've tested both front-end and FTP. 

I've also checked my permissions. 

I think I know the problem, although I don't know how to solve:

1.  BEFORE I try to upload, this is the debug code.  Is any of this problematic?

Code Select Expand

/include/inspekt/cage.php
Runtime Notice line 253: Non-static method Inspekt::getMatched() should not be called statically, assuming $this from incompatible context
Runtime Notice line 529: Non-static method Inspekt::isIp() should not be called statically, assuming $this from incompatible context
Runtime Notice line 236: Non-static method Inspekt::getEscaped() should not be called statically, assuming $this from incompatible context
Runtime Notice line 236: Non-static method Inspekt::getEscaped() should not be called statically, assuming $this from incompatible context
Runtime Notice line 140: Non-static method Inspekt::getAlnum() should not be called statically, assuming $this from incompatible context
/include/functions.inc.php
Deprecated line 183: mysql_connect(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead
Runtime Notice line 773: Non-static method CPGPluginAPI::filter() should not be called statically
Runtime Notice line 816: Non-static method CPGPluginAPI::filter() should not be called statically
Runtime Notice line 821: Non-static method CPGPluginAPI::filter() should not be called statically
Runtime Notice line 822: Non-static method CPGPluginAPI::filter() should not be called statically
Runtime Notice line 6157: Non-static method CPGPluginAPI::filter() should not be called statically
Runtime Notice line 6157: Non-static method CPGPluginAPI::filter() should not be called statically
/include/init.inc.php
Runtime Notice line 230: Non-static method CPGPluginAPI::load() should not be called statically
Runtime Notice line 435: Non-static method CPGPluginAPI::action() should not be called statically
/include/plugin_api.inc.php
Runtime Notice line 86: Non-static method CPGPluginAPI::action() should not be called statically
Runtime Notice line 86: Non-static method CPGPluginAPI::action() should not be called statically
Runtime Notice line 86: Non-static method CPGPluginAPI::action() should not be called statically
Runtime Notice line 86: Non-static method CPGPluginAPI::action() should not be called statically
Runtime Notice line 86: Non-static method CPGPluginAPI::action() should not be called statically
Runtime Notice line 86: Non-static method CPGPluginAPI::action() should not be called statically
Runtime Notice line 86: Non-static method CPGPluginAPI::action() should not be called statically
Runtime Notice line 86: Non-static method CPGPluginAPI::action() should not be called statically
/bridge/udb_base.inc.php
Notice line 153: Undefined property: cpg_udb::$multigroups
Deprecated line 353: mysql_escape_string(): This function is deprecated; use mysql_real_escape_string() instead.
/include/themes.inc.php
Runtime Notice line 153: Non-static method CPGPluginAPI::filter() should not be called statically
Runtime Notice line 190: Non-static method CPGPluginAPI::filter() should not be called statically
Runtime Notice line 2439: Non-static method CPGPluginAPI::filter() should not be called statically
Runtime Notice line 1699: Non-static method CPGPluginAPI::filter() should not be called statically
Runtime Notice line 1554: Non-static method CPGPluginAPI::filter() should not be called statically
/upload.php
Runtime Notice line 42: Non-static method CPGPluginAPI::filter() should not be called statically
Runtime Notice line 565: Non-static method CPGPluginAPI::action() should not be called statically

2.  AFTER I try to upload, I get the following "Critical Error:  There was an error while processing a database query."

See screenshot.

Code Select Expand

While executing query 'INSERT INTO cpg15x_pictures (aid, filepath, filename, filesize, total_filesize, pwidth, pheight, ctime, owner_id, title, caption, keywords, approved, user1, user2, user3, user4, pic_raw_ip, pic_hdr_ip, position, guest_token) VALUES ('164', 'userpics/10001/', '07cbffb5dc37a41ccf809106d709af8e~1.jpg', '180302', '207152', '736', '1274', '1419794406', '1', 'Test', 'lorem ipsum', '', 'YES', '', '', '', '', '66.199.97.115', '66.199.97.115', '0', '')' in include/picmgmt.inc.php on line 173

mySQL error: Incorrect datetime value: '0000-00-00 00:00:00' for column 'mtime' at row 1


[pols1337]

By the way, I vaguely remember this problem happening to me before ... but I thought Andre fixed the code so it wouldn't be a problem anymore?  Something about the mtime and how it's not the right default value or something. 

I don't know.  I'm confused  :-\

[Αndré]

That has been fixed in cpg1.5.30, latest stable release is cpg1.5.34:

Code Select Expand

2014-07-04 [B] Fixed default value for columns that use the MySQL datatype "datetime" (thread ID 77646) {eenemeenemuu}

Please run update.php and try again.

[pols1337]

I've upgraded all of my galleries to cpg1.3.34 (in fact, I even said thanks in the announcemens thread), but it's still not working.  I've also been running cpg1.3.30 for awhile now.

I did not overwrite the albums/, docs/, bridge/, or plugins/ folder. 

Also, my database's collation is latin1_swedish_ci.   

Would any of those affect the default value for MySQL "datetime"? 

[Αndré]

No. For this issue make sure the file sql/update. sql is up-to-date and your Coppermine MySQL user has permission to ALTER tables. Alternatively, change the default value via phpMyAdmin (have a look at the last few lines in sql/update.sql to get an idea which fields needs to be updated).

[pols1337]

Okay, I'm excited because I think I found one of the problems. 

1) The prefixes of my table names (if that's the right word) don't begin with "CPG_"; instead they begin with "cpg15x_"

See photo MySQL error 1.

2) I thought I would be smart and manually change the ALTER TABLE queries to "cpg15x_."  However, I still get an error on mtime

Code Select Expand

#1292 - Incorrect datetime value: '0000-00-00 00:00:00' for column 'mtime' at row 3

See photo MySQL error 2.


So, first of all, is there a query I can run to change the prefixes on all of my table entries?  Otherwise the prefix is totally wrong, and the update.sql probably didn't do anything at all when I updated!

Second, how do I fix 'mtime'?

[pols1337]

Also, I can't be the ONLY Coppermine admin with the wrong prefixes, right?  I didn't willingly choose the prefixes; they were default settings during installation, which makes me wonder why I would even have the wrong prefixes with which to begin. 

[Αndré]

The table prefix placeholder "CPG_" get replaced in update.php, so nothing wrong here.

However, your second screenshot tell why it doesn't work as expected. phpMyAdmin complains about the old default value, though we try to set a different one. I currently cannot tell you why this happens. Have you also tried to change the default value via phpMyAdmin's GUI tools (I'm sure there are some)?

[pols1337]

Okay, after many months of trying (and eventually giving up), it turns out the same error of datetime ended up affecting my test forums (IPS 4 Release Candidate 4), and their excellent customer support might have isolated the problem.  I am having my server administrator apply the solution, and will update this topic if this works.

Hello,

I've found the problem, actually. It is a combination of your MySQL Version (5.7) and your sql_mode configuration, which is set to STRICT_TRANS_TABLES.

As of MySQL 5.7, a default value of 0000-00-00 00:00:00 For a "datetime" column is no longer valid, if strict mode is enabled. Your server specifically meets that criteria, so an error is thrown when the calendar_events table is created.

Code Select Expand

CREATE TABLE `calendar_events` (
  `event_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `event_calendar_id` int(10) unsigned NOT NULL DEFAULT '0',
  `event_member_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `event_content` mediumtext COLLATE utf8mb4_unicode_ci,
  `event_title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `event_comments` int(11) NOT NULL DEFAULT '0',
  `event_rsvp` tinyint(1) NOT NULL DEFAULT '0',
  `event_approved` tinyint(1) NOT NULL DEFAULT '0',
  `event_saved` int(10) unsigned NOT NULL DEFAULT '0',
  `event_lastupdated` int(10) unsigned NOT NULL DEFAULT '0',
  `event_recurring` text COLLATE utf8mb4_unicode_ci COMMENT 'Holds an RRULE value to indicate how the event recurs',
  `event_start_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `event_end_date` datetime DEFAULT NULL,
  `event_title_seo` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `event_rating` smallint(5) unsigned NOT NULL DEFAULT '0',
  `event_post_key` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `event_comments_pending` int(11) NOT NULL DEFAULT '0',
  `event_sequence` int(11) NOT NULL DEFAULT '0',
  `event_all_day` tinyint(4) NOT NULL DEFAULT '0',
  `event_reviews` int(10) unsigned NOT NULL DEFAULT '0',
  `event_ip_address` varchar(46) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `event_locked` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Whether the event is locked (for new comments/reviews) or not',
  `event_featured` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Whether the event is featured or not',
  `event_last_comment` int(11) unsigned DEFAULT NULL,
  `event_last_review` int(11) unsigned DEFAULT NULL,
  `event_approved_by` bigint(20) unsigned DEFAULT NULL COMMENT 'Member ID who approved the event',
  `event_approved_on` int(11) unsigned DEFAULT NULL COMMENT 'Date the event was approved on',
  `event_location` text COLLATE utf8mb4_unicode_ci COMMENT 'Stores location data for the event, used to create a map',
  `event_rsvp_limit` int(10) DEFAULT NULL COMMENT 'Maximum number of attendees allowed for the event',
  `event_cover_photo` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Header background image for the event',
  `event_album` mediumint(8) unsigned DEFAULT NULL COMMENT 'Holds album ID associated with this event',
  `event_cover_offset` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`event_id`),
  KEY `approved` (`event_calendar_id`,`event_approved`,`event_start_date`,`event_end_date`),
  KEY `event_member_id` (`event_member_id`,`event_approved`,`event_lastupdated`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
The immediate fix here would be to simply disable strict mode in MySQL