The schema definitions in hosting define a bunch of LONGTEXT fields with default values of ''. A LONGTEXT field cannot have a default value on MariaDB (and possibly on newer versions of MySQL either). I can roll a patch for this, but ...

The schema as-is also has (for instance) the short_name field defined as a LONGTEXT, but uses it in a WHERE clause in various queries. Because a LONGTEXT can't have an index on it, performance will degrade badly once the tables being queried for such fields start getting larger.

I suggest undoing the hosting_package_update_3 change and setting such a field back to a VARCHAR. A VARCHAR field can be 16Kb in size and can have an index on the first 255 characters, which will make selects with a WHERE clause run much faster.

Realistically, do any users need a short_name that is larger than 16Kb?

Comments

cafuego’s picture

cafuego CreditAttribution: cafuego commented

Just FYI, the errors generated when I run drush hostmaster-install --version=HEAD

Created myaegirdatabase database                                                                        [success]
WD php: BLOB/TEXT column 'error' can't have a default value                                          [error]
query: CREATE TABLE hosting_task_log (
`lid` INT NOT NULL auto_increment, 
`vid` INT NOT NULL DEFAULT 0, 
`type` VARCHAR(16) NOT NULL DEFAULT '', 
`message` LONGTEXT NOT NULL, 
`error` LONGTEXT NOT NULL DEFAULT '', 
`timestamp` INT NOT NULL DEFAULT 0, 
PRIMARY KEY (lid), 
INDEX type (type)
) /*!40100 DEFAULT CHARACTER SET UTF8 */ in /var/aegir/hostmaster-HEAD/includes/database.inc on line
555.
WD php: BLOB/TEXT column 'short_name' can't have a default value                                     [error]
query: CREATE TABLE hosting_package (
`vid` INT NOT NULL DEFAULT 0, 
`nid` INT unsigned NOT NULL DEFAULT 0, 
`package_type` VARCHAR(16) NOT NULL DEFAULT '', 
`short_name` LONGTEXT NOT NULL DEFAULT '', 
`old_short_name` LONGTEXT NOT NULL DEFAULT '', 
`description` LONGTEXT NOT NULL DEFAULT '', 
PRIMARY KEY (vid)
) /*!40100 DEFAULT CHARACTER SET UTF8 */ in /var/aegir/hostmaster-HEAD/includes/database.inc on line
555.
cafuego’s picture

cafuego CreditAttribution: cafuego commented
Status: Active » Needs review

I have a patch to remove the DEFAULT values from the longtext fields at https://github.com/cafuego/hostmaster/commit/6466df73380af83cd509d791633...

anarcat’s picture

anarcat CreditAttribution: anarcat commented
Status: Needs review » Needs work

I suspect this is also the cause of the performance problems I see in #934864: platform verification tasks take forever, and then some more.

I agree that the profile names should be varchar, in fact they are limited within Drupal to 255 characters, so we should stick with that. So let's land a patch that does that too.

anarcat’s picture

anarcat CreditAttribution: anarcat commented
Assigned: Unassigned » anarcat

testing a patch.

anarcat’s picture

anarcat CreditAttribution: anarcat commented
Status: Needs work » Fixed

Fix committed to git, inspired by your patch: http://git.aegirproject.org/?p=hostmaster.git;a=commitdiff;h=1cb033c434e...

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

  • Commit fbc83f4 on 6.x-2.x, 7.x-3.x, dev-ssl-ip-allocation-refactor, dev-sni, dev-helmo-3.x by anarcat: 
    Fix up schema for MariaDB and performance

We drop the defaults on...

  • Commit fbc83f4 on 6.x-2.x, 7.x-3.x, dev-ssl-ip-allocation-refactor, dev-sni, dev-helmo-3.x by anarcat: 
    Fix up schema for MariaDB and performance

We drop the defaults on...