New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Database errors in MySQL strict mode #433

Closed
willydee opened this Issue Apr 22, 2017 · 4 comments

Comments

Projects
None yet
3 participants
@willydee

willydee commented Apr 22, 2017

Problem

On a new Froxlor installation (Debian stretch), I've encountered error messages:

A database error occurred
SQLSTATE[HY000]: General error: 1364 Field 'destination' doesn't have a default value

Steps to reproduce

  • Set up a pristine host based on a recent Debian or Ubuntu distribution
  • Verify that MySQL is running in strict mode
  • Create a customer and domain
  • As customer, try to create a new email address

Possible cause

MySQL is operating in strict mode by default (in Debian jessie or newer), which causes errors if a text type column declared NOT NULL is omitted in an INSERT operation. See https://dev.mysql.com/doc/refman/5.7/en/data-type-defaults.html.

Suggestions

  • Declare text type columns without NOT NULL constraint, or
  • Use sufficiently large varchar type columns instead of text, or
  • Supply empty string values for the affected columns when inserting

RFC. I'll supply a pull request once it is decided which solution should be applied.

Best regards, willydee

@d00p

This comment has been minimized.

Show comment
Hide comment
@d00p

d00p Apr 24, 2017

Member

Being strict-mode compatible would be the best choice as also ubuntu is using this mode by default now; a pull request - tested! - would be most helpful

Member

d00p commented Apr 24, 2017

Being strict-mode compatible would be the best choice as also ubuntu is using this mode by default now; a pull request - tested! - would be most helpful

@d00p d00p added the enhancement label Apr 24, 2017

@d00p d00p added this to the 0.9.39 milestone Apr 24, 2017

@Starcalc

This comment has been minimized.

Show comment
Hide comment
@Starcalc

Starcalc May 11, 2017

During installation on a new ubuntu (17.04) with mysql 5.7, this fails even during installation. Yet worse, the installation sql-script does not check whether the imported sql-statements have failed. This results in various problems, e.g. the "mail_virtual"-table is never created due to an error. The installation website results with everything green although an error has occured.

When I try to create the mail_virtual-table in CLI-mode of mysql, I get a more explanatory error which pointed me to this issue.

mysql> CREATE TABLE `mail_virtual` (
    ->   `id` int(11) NOT NULL auto_increment,
    ->   `email` varchar(255) NOT NULL default '',
    ->   `email_full` varchar(255) NOT NULL default '',
    ->   `destination` text NOT NULL default '',
    ->   `domainid` int(11) NOT NULL default '0',
    ->   `customerid` int(11) NOT NULL default '0',
    ->   `popaccountid` int(11) NOT NULL default '0',
    ->   `iscatchall` tinyint(1) unsigned NOT NULL default '0',
    ->   PRIMARY KEY  (`id`),
    ->   KEY `email` (`email`)
    -> ) ENGINE=MyISAM CHARSET=utf8 COLLATE=utf8_general_ci;
ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'destination' can't have a default value

Starcalc commented May 11, 2017

During installation on a new ubuntu (17.04) with mysql 5.7, this fails even during installation. Yet worse, the installation sql-script does not check whether the imported sql-statements have failed. This results in various problems, e.g. the "mail_virtual"-table is never created due to an error. The installation website results with everything green although an error has occured.

When I try to create the mail_virtual-table in CLI-mode of mysql, I get a more explanatory error which pointed me to this issue.

mysql> CREATE TABLE `mail_virtual` (
    ->   `id` int(11) NOT NULL auto_increment,
    ->   `email` varchar(255) NOT NULL default '',
    ->   `email_full` varchar(255) NOT NULL default '',
    ->   `destination` text NOT NULL default '',
    ->   `domainid` int(11) NOT NULL default '0',
    ->   `customerid` int(11) NOT NULL default '0',
    ->   `popaccountid` int(11) NOT NULL default '0',
    ->   `iscatchall` tinyint(1) unsigned NOT NULL default '0',
    ->   PRIMARY KEY  (`id`),
    ->   KEY `email` (`email`)
    -> ) ENGINE=MyISAM CHARSET=utf8 COLLATE=utf8_general_ci;
ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'destination' can't have a default value

@d00p

This comment has been minimized.

Show comment
Hide comment
@d00p

d00p May 11, 2017

Member

@Starcalc known, as always: froxlor is not yet compatible with strict-mode, also does not yet officially support 16.06/17.04...

Member

d00p commented May 11, 2017

@Starcalc known, as always: froxlor is not yet compatible with strict-mode, also does not yet officially support 16.06/17.04...

@d00p

This comment has been minimized.

Show comment
Hide comment
@d00p

d00p Nov 27, 2017

Member

Might be fixed due to 1e03946

Member

d00p commented Nov 27, 2017

Might be fixed due to 1e03946

@d00p d00p closed this Nov 27, 2017

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment