[CWB] bugs caused by sql-mode issues.

Hardie, Andrew a.hardie at lancaster.ac.uk
Mon Feb 8 13:56:04 CET 2016 

There are two bugs here, one is an issue in the error reporting system for that script in 3.2.6, which I've now fixed in that branch (you should svn up); and the other is a more general one stemming from your MySQL setup.

The latter was this message: " Invalid default value for 'last_seen_time' ".

What this usually indicates is that you have the NO_ZERO_DATE option switched on in the MySQL server (which disallows the use of "0000-00-00" to indicate "never happened"... a convention that CQPweb uses).

A quick look at the MySQL manual (see here:  http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html ) suggests that NO_ZERO_DATE was switched on by default in MySQL v 5.7 (it was previously not enabled by default).

Are you using v 5.7? I can't check this myself because my server and dev boxes all have older versions.

If you are, then the solution is to disable NO_ZERO_DATE (or, in some MySQL versions, STRICT; see here: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-changes ) 

Here's what the manual says about setting the sql mode (of which NO_ZERO_DATE is part)

>>
To set the SQL mode at server startup, use the --sql-mode="modes" option on the command line, or sql-mode="modes" in an option file such as my.cnf (Unix operating systems) or my.ini (Windows). modes is a list of different modes separated by commas.
<<

The default sql-mode in 5.5/5.6. is empty. However, in 5.7 the default sql-mode adds a whole lot of "strictness": including NO_ZERO_DATE.

So, if you are running v5.7, the mystery is solved.

It looks as if MySQL are going in the direction of having strict mode on by default, and having strict mode include or imply NO_ZERO_DATE.

I can see 3 longterm possibilities:

- Add to the install manual an explanation that NO_ZERO_DATE must be turned off.
- Add code to CQPweb to turn off strictness as the first thing it does when it connects to mysql
- Take out anything in CQPweb which requires strictness to be turned off.

I'm not wholly sure, yet, which of these would make the most sense. I'll have to think about it.

best

Andrew.

-----Original Message-----
From: cwb-bounces at sslmit.unibo.it [mailto:cwb-bounces at sslmit.unibo.it] On Behalf Of José Antonio M M
Sent: 08 February 2016 11:27
To: cwb at sslmit.unibo.it
Subject: Re: [CWB] CWB Digest, Vol 109, Issue 1

Good morning, Andrew:

I first installed trunk and later 3.2.6. With the latter I get these errors:

Now finalising setup for this installation of CQPweb....
PHP Deprecated:  mysql_connect(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead in /www/sites/cqpweb/wwwroot/lib/library.inc.php on line 156

Deprecated: mysql_connect(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead in /www/sites/cqpweb/wwwroot/lib/library.inc.php on line 156

Installing database structure; please wait.
PHP Notice:  Undefined property: stdClass::$debug_messages_textonly in /www/sites/cqpweb/wwwroot/lib/exiterror.inc.php on line 88

Notice: Undefined property: stdClass::$debug_messages_textonly in /www/sites/cqpweb/wwwroot/lib/exiterror.inc.php on line 88
PHP Notice:  Undefined property: stdClass::$debug_messages_textonly in /www/sites/cqpweb/wwwroot/lib/exiterror.inc.php on line 89

Notice: Undefined property: stdClass::$debug_messages_textonly in /www/sites/cqpweb/wwwroot/lib/exiterror.inc.php on line 89
PHP Notice:  Undefined property: stdClass::$debug_messages_textonly in /www/sites/cqpweb/wwwroot/lib/exiterror.inc.php on line 91

Notice: Undefined property: stdClass::$debug_messages_textonly in /www/sites/cqpweb/wwwroot/lib/exiterror.inc.php on line 91
<tr><td class="concorderror"><p>&nbsp;</p><p class="errormessage">A mySQL query did not run successfully!</p><p>&nbsp;</p></td></tr>

<tr><td class="concorderror"><p>&nbsp;</p><p class="errormessage">Original query:

CREATE TABLE `user_info` (
			`id` int NOT NULL AUTO_INCREMENT,
			`username` varchar(64) NOT NULL,
			`realname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci default NULL,
			`email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci default NULL,
			`affiliation` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci default NULL,
			`country` char(2) default '00',
			`passhash` char(61),
			`acct_status` tinyint(1) NOT NULL default 0,
			`verify_key` varchar(32) default NULL,
			`expiry_time` int UNSIGNED NOT NULL default 0,
			`password_expiry_time` int UNSIGNED NOT NULL default 0,
			`last_seen_time` timestamp NOT NULL default 0,
			`acct_create_time` timestamp NOT NULL default CURRENT_TIMESTAMP,
			`conc_kwicview` tinyint(1),
			`conc_corpus_order` tinyint(1),
			`cqp_syntax` tinyint(1),
			`context_with_tags` tinyint(1),
			`use_tooltips` tinyint(1),
			`css_monochrome` tinyint(1) NOT NULL default 0,
			`thin_default_reproducible` tinyint(1),
			`coll_statistic` tinyint,
			`coll_freqtogether` int,
			`coll_freqalone` int,
			`coll_from` tinyint,
			`coll_to` tinyint,
			`max_dbsize` int(10) unsigned default NULL,
			`linefeed` char(2) default NULL,
			unique key(`username`),
			primary key (`id`)
		) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin
	/* from User: ??? | Function: cqpweb_mysql_total_reset() | 2016-02-08 12:22:41 */

</p><p>&nbsp;</p></td></tr>

<tr><td class="concorderror"><p>&nbsp;</p><p class="errormessage">Error # 1067: Invalid default value for 'last_seen_time' </p><p>&nbsp;</p></td></tr>

PHP Notice:  Undefined property: stdClass::$all_users_see_backtrace in /www/sites/cqpweb/wwwroot/lib/exiterror.inc.php on line 113

Notice: Undefined property: stdClass::$all_users_see_backtrace in /www/sites/cqpweb/wwwroot/lib/exiterror.inc.php on line 113
PHP Notice:  Undefined property: stdClass::$debug_messages_textonly in /www/sites/cqpweb/wwwroot/lib/exiterror.inc.php on line 140

Notice: Undefined property: stdClass::$debug_messages_textonly in /www/sites/cqpweb/wwwroot/lib/exiterror.inc.php on line 140
		</table>
		PHP Fatal error:  Call to undefined function print_html_footer() in /www/sites/cqpweb/wwwroot/lib/exiterror.inc.php on line 159

Fatal error: Call to undefined function print_html_footer() in /www/sites/cqpweb/wwwroot/lib/exiterror.inc.php on line 159

Thank you so much for your help,

José
_______________________________________________
CWB mailing list
CWB at sslmit.unibo.it
http://devel.sslmit.unibo.it/mailman/listinfo/cwb

More information about the CWB mailing list