Difference between revisions of "User:Woozle/Friendica/2019 upgrade"

Latest revision as of 18:28, 13 October 2019

2019-10-12 Here are the errors I got when attempting to upgrade from 3.5.4 to 2018-09. I've added formatting and headers for readability; the original raw output is available on the first version of the page.

Command

icms@cloud5:~/domains/hey.iseeamess.com/site/git/friendica-full-2018.09$ bin/console dbstructure update

Output (1/2)

addon

DROP TABLE IF EXISTS `temp-addon`;
CREATE TABLE `temp-addon` LIKE `addon`;
ALTER TABLE `temp-addon` MODIFY `id` int unsigned NOT NULL auto_increment COMMENT '', MODIFY `name` varchar(50) NOT NULL DEFAULT '' COMMENT 'addon base (file)name', MODIFY `version` varchar(50) NOT NULL DEFAULT '' COMMENT 'currently unused', MODIFY `installed` boolean NOT NULL DEFAULT '0' COMMENT 'currently always 1', MODIFY `hidden` boolean NOT NULL DEFAULT '0' COMMENT 'currently unused', MODIFY `timestamp` int unsigned NOT NULL DEFAULT 0 COMMENT 'file timestamp to check for reloads', MODIFY `plugin_admin` boolean NOT NULL DEFAULT '0' COMMENT '1 = has admin config, 0 = has no admin config', ADD UNIQUE INDEX `name` (`name`), COMMENT = 'registered addons', DEFAULT COLLATE utf8mb4_general_ci; ALTER TABLE `temp-addon` MODIFY `name` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'addon base (file)name', MODIFY `version` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'currently unused';
INSERT INTO `temp-addon` SELECT ANY_VALUE(`id`),ANY_VALUE(`name`),ANY_VALUE(`version`),ANY_VALUE(`installed`),ANY_VALUE(`hidden`),ANY_VALUE(`timestamp`),ANY_VALUE(`plugin_admin`) FROM `addon` GROUP BY `name`;
DROP TABLE `addon`;
RENAME TABLE `temp-addon` TO `addon`;

Error 1064 occurred during database update: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALTER TABLE `temp-addon` MODIFY `name` varchar(50) COLLATE utf8mb4_general_ci NO' at line 1

attach

ALTER TABLE `attach` MODIFY `id` int unsigned NOT NULL auto_increment COMMENT 'generated index', MODIFY `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner User id', MODIFY `hash` varchar(64) NOT NULL DEFAULT '' COMMENT 'hash', MODIFY `filename` varchar(255) NOT NULL DEFAULT '' COMMENT 'filename of original', MODIFY `filetype` varchar(64) NOT NULL DEFAULT '' COMMENT 'mimetype', MODIFY `filesize` int unsigned NOT NULL DEFAULT 0 COMMENT 'size in bytes', MODIFY `data` longblob NOT NULL COMMENT 'file data', MODIFY `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'creation time', MODIFY `edited` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'last edit time', MODIFY `allow_cid` mediumtext COMMENT 'Access Control - list of allowed contact.id \'<19><78>', MODIFY `allow_gid` mediumtext COMMENT 'Access Control - list of allowed groups', MODIFY `deny_cid` mediumtext COMMENT 'Access Control - list of denied contact.id', MODIFY `deny_gid` mediumtext COMMENT 'Access Control - list of denied groups', COMMENT = 'file attachments', DEFAULT COLLATE utf8mb4_general_ci; ALTER TABLE `attach` MODIFY `hash` varchar(64) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'hash', MODIFY `filename` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'filename of original', MODIFY `filetype` varchar(64) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'mimetype', MODIFY `allow_cid` mediumtext COLLATE utf8mb4_general_ci COMMENT 'Access Control - list of allowed contact.id \'<19><78>', MODIFY `allow_gid` mediumtext COLLATE utf8mb4_general_ci COMMENT 'Access Control - list of allowed groups', MODIFY `deny_cid` mediumtext COLLATE utf8mb4_general_ci COMMENT 'Access Control - list of denied contact.id', MODIFY `deny_gid` mediumtext COLLATE utf8mb4_general_ci COMMENT 'Access Control - list of denied groups';

Error 1064 occurred during database update: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALTER TABLE `attach` MODIFY `hash` varchar(64) COLLATE utf8mb4_general_ci NOT NU' at line 1

auth_codes

ALTER TABLE `auth_codes` MODIFY `client_id` varchar(20) NOT NULL DEFAULT '' COMMENT '', MODIFY `redirect_uri` varchar(200) NOT NULL DEFAULT '' COMMENT '', MODIFY `expires` int NOT NULL DEFAULT 0 COMMENT '', MODIFY `scope` varchar(250) NOT NULL DEFAULT '' COMMENT '', COMMENT = 'OAuth usage', DEFAULT COLLATE utf8mb4_general_ci; ALTER TABLE `auth_codes` MODIFY `id` varchar(40) COLLATE utf8mb4_general_ci NOT NULL COMMENT '', MODIFY `client_id` varchar(20) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '', MODIFY `redirect_uri` varchar(200) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '', MODIFY `scope` varchar(250) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '';

Error 1064 occurred during database update: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALTER TABLE `auth_codes` MODIFY `id` varchar(40) COLLATE utf8mb4_general_ci NOT ' at line 1

challenge

ALTER TABLE `challenge` MODIFY `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', MODIFY `challenge` varchar(255) NOT NULL DEFAULT '' COMMENT '', MODIFY `dfrn-id` varchar(255) NOT NULL DEFAULT '' COMMENT '', MODIFY `expire` int unsigned NOT NULL DEFAULT 0 COMMENT '', MODIFY `type` varchar(255) NOT NULL DEFAULT '' COMMENT '', MODIFY `last_update` varchar(255) NOT NULL DEFAULT '' COMMENT '', DEFAULT COLLATE utf8mb4_general_ci; ALTER TABLE `challenge` MODIFY `challenge` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '', MODIFY `dfrn-id` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '', MODIFY `type` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '', MODIFY `last_update` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '';

Error 1064 occurred during database update: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALTER TABLE `challenge` MODIFY `challenge` varchar(255) COLLATE utf8mb4_general_' at line 1

clients

ALTER TABLE `clients` MODIFY `pw` varchar(20) NOT NULL DEFAULT '' COMMENT '', MODIFY `redirect_uri` varchar(200) NOT NULL DEFAULT '' COMMENT '', MODIFY `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id', COMMENT = 'OAuth usage', DEFAULT COLLATE utf8mb4_general_ci; ALTER TABLE `clients` MODIFY `client_id` varchar(20) COLLATE utf8mb4_general_ci NOT NULL COMMENT '', MODIFY `pw` varchar(20) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '', MODIFY `redirect_uri` varchar(200) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '', MODIFY `name` text COLLATE utf8mb4_general_ci COMMENT '', MODIFY `icon` text COLLATE utf8mb4_general_ci COMMENT '';

Error 1064 occurred during database update: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALTER TABLE `clients` MODIFY `client_id` varchar(20) COLLATE utf8mb4_general_ci ' at line 1

config

DROP TABLE IF EXISTS `temp-config`;
CREATE TABLE `temp-config` LIKE `config`;
ALTER TABLE `temp-config` DROP INDEX `cat_k`, MODIFY `cat` varbinary(50) NOT NULL DEFAULT '' COMMENT '', MODIFY `k` varbinary(50) NOT NULL DEFAULT '' COMMENT '', MODIFY `v` mediumtext COMMENT '', ADD UNIQUE INDEX `cat_k` (`cat`,`k`), COMMENT = 'main configuration storage', DEFAULT COLLATE utf8mb4_general_ci;
ALTER TABLE `temp-config` MODIFY `cat` varbinary(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '', MODIFY `k` varbinary(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '', MODIFY `v` mediumtext COLLATE utf8mb4_general_ci COMMENT '';
INSERT INTO `temp-config` SELECT ANY_VALUE(`id`),ANY_VALUE(`cat`),ANY_VALUE(`k`),ANY_VALUE(`v`) FROM `config` GROUP BY `cat`,`k`;
DROP TABLE `config`;
RENAME TABLE `temp-config` TO `config`;

Error 1064 occurred during database update: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALTER TABLE `temp-config` MODIFY `cat` varbinary(50) COLLATE utf8mb4_general_ci ' at line 1

Details: "Error Code: 1253. COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'binary'"
Changed query to remove COLLATE clause from any fields defined as varbinary:
ALTER TABLE `temp-config` MODIFY `cat` varbinary(50) NOT NULL DEFAULT '' COMMENT '', MODIFY `k` varbinary(50) NOT NULL DEFAULT '' COMMENT '', MODIFY `v` mediumtext COLLATE utf8mb4_general_ci COMMENT '';
This was successful.

contact

ALTER TABLE `contact` 
  DROP INDEX `uid_name`,
  DROP INDEX `dfrn-id`,
  DROP INDEX `issued-id`,
  MODIFY `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
  MODIFY `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner User id',
  MODIFY `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '',
  MODIFY `self` boolean NOT NULL DEFAULT '0' COMMENT '1 if the contact is the user him/her self',
  MODIFY `rel` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'The kind of the relation between the user and the contact',
  MODIFY `network` char(4) NOT NULL DEFAULT '' COMMENT 'Network protocol of the contact',
  MODIFY `name` varchar(255) NOT NULL DEFAULT '' COMMENT 'Name that this contact is known by',
  MODIFY `nick` varchar(255) NOT NULL DEFAULT '' COMMENT 'Nick- and user name of the contact',
  MODIFY `keywords` text COMMENT 'public keywords (interests) of the contact',
  MODIFY `photo` varchar(255) DEFAULT '' COMMENT 'Link to the profile photo of the contact',
  MODIFY `thumb` varchar(255) DEFAULT '' COMMENT 'Link to the profile photo (thumb size)',
  MODIFY `micro` varchar(255) DEFAULT '' COMMENT 'Link to the profile photo (micro size)',
  MODIFY `pubkey` text COMMENT 'RSA public key 4096 bit',
  MODIFY `prvkey` text COMMENT 'RSA private key 4096 bit',
  MODIFY `request` varchar(255) COMMENT '',
  MODIFY `notify` varchar(255) COMMENT '',
  MODIFY `poll` varchar(255) COMMENT '',
  MODIFY `confirm` varchar(255) COMMENT '',
  MODIFY `poco` varchar(255) COMMENT '',
  MODIFY `last-update` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last try to update the contact info',
  MODIFY `success_update` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last successful contact update',
  MODIFY `failure_update` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last failed update',
  MODIFY `name-date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '',
  MODIFY `uri-date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '',
  MODIFY `avatar-date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '',
  MODIFY `term-date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '',
  MODIFY `last-item` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'date of the last post',
  MODIFY `priority` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '',
  MODIFY `readonly` boolean NOT NULL DEFAULT '0' COMMENT 'posts of the contact are readonly',
  MODIFY `forum` boolean NOT NULL DEFAULT '0' COMMENT 'contact is a forum',
  MODIFY `prv` boolean NOT NULL DEFAULT '0' COMMENT 'contact is a private group',
  MODIFY `contact-type` tinyint NOT NULL DEFAULT 0 COMMENT '',
  MODIFY `rating` tinyint NOT NULL DEFAULT 0 COMMENT '',
  MODIFY `closeness` tinyint unsigned NOT NULL DEFAULT 99 COMMENT '',
  MODIFY `profile-id` int unsigned NOT NULL DEFAULT 0 COMMENT '',
  MODIFY `bd` date NOT NULL DEFAULT '0001-01-01' COMMENT '',
  MODIFY `fetch_further_information` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '',
  ADD INDEX `uid_name` (`uid`,`name`(190)),
  ADD INDEX `dfrn-id` (`dfrn-id`(64)),
  ADD INDEX `issued-id` (`issued-id`(64)),
  COMMENT = 'contact table';

Error 1265 occurred during database update: Data truncated for column 'last-update' at row 1

Details: "Error Code: 1265. Data truncated for column 'last-update' at row 1"

I first tried changing all the '0001-01-01 00:00:00' values to '0000-00-00 00:00:00', but although that is apparently a valid "zero value" in MySQL 8.0 and even in 5.7, it is still generating an error on my MySQL 5.7.27-0ubuntu0-18.04.1. Perhaps it's not supported as a default value? (It's not clear where the original default value would ever be valid.)

I considered using the '1970-01-01 00:00:00' default value, but couldn't think of a good scenario in which NULL wouldn't work better -- so opted for just removing the NOT NULL restriction. I will be on the lookout for potential problems (failed queries, sorting & filtering anomalies, etc.).

Note that this appears to be how those columns were originally defined. I could just remove those modify statements, but some of them add comments.

The modified statement:

ALTER TABLE `contact` 
  DROP INDEX `uid_name`,
  DROP INDEX `dfrn-id`,
  DROP INDEX `issued-id`,
  MODIFY `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
  MODIFY `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner User id',
  MODIFY `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '',
  MODIFY `self` boolean NOT NULL DEFAULT '0' COMMENT '1 if the contact is the user him/her self',
  MODIFY `rel` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'The kind of the relation between the user and the contact',
  MODIFY `network` char(4) NOT NULL DEFAULT '' COMMENT 'Network protocol of the contact',
  MODIFY `name` varchar(255) NOT NULL DEFAULT '' COMMENT 'Name that this contact is known by',
  MODIFY `nick` varchar(255) NOT NULL DEFAULT '' COMMENT 'Nick- and user name of the contact',
  MODIFY `keywords` text COMMENT 'public keywords (interests) of the contact',
  MODIFY `photo` varchar(255) DEFAULT '' COMMENT 'Link to the profile photo of the contact',
  MODIFY `thumb` varchar(255) DEFAULT '' COMMENT 'Link to the profile photo (thumb size)',
  MODIFY `micro` varchar(255) DEFAULT '' COMMENT 'Link to the profile photo (micro size)',
  MODIFY `pubkey` text COMMENT 'RSA public key 4096 bit',
  MODIFY `prvkey` text COMMENT 'RSA private key 4096 bit',
  MODIFY `request` varchar(255) COMMENT '',
  MODIFY `notify` varchar(255) COMMENT '',
  MODIFY `poll` varchar(255) COMMENT '',
  MODIFY `confirm` varchar(255) COMMENT '',
  MODIFY `poco` varchar(255) COMMENT '',
  MODIFY `last-update` datetime DEFAULT NULL COMMENT 'Date of the last try to update the contact info',
  MODIFY `success_update` datetime DEFAULT NULL COMMENT 'Date of the last successful contact update',
  MODIFY `failure_update` datetime DEFAULT NULL COMMENT 'Date of the last failed update',
  MODIFY `name-date` datetime DEFAULT NULL COMMENT '',
  MODIFY `uri-date` datetime DEFAULT NULL COMMENT '',
  MODIFY `avatar-date` datetime DEFAULT NULL COMMENT '',
  MODIFY `term-date` datetime DEFAULT NULL COMMENT '',
  MODIFY `last-item` datetime DEFAULT NULL COMMENT 'date of the last post',
  MODIFY `priority` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '',
  MODIFY `readonly` boolean NOT NULL DEFAULT '0' COMMENT 'posts of the contact are readonly',
  MODIFY `forum` boolean NOT NULL DEFAULT '0' COMMENT 'contact is a forum',
  MODIFY `prv` boolean NOT NULL DEFAULT '0' COMMENT 'contact is a private group',
  MODIFY `contact-type` tinyint NOT NULL DEFAULT 0 COMMENT '',
  MODIFY `rating` tinyint NOT NULL DEFAULT 0 COMMENT '',
  MODIFY `closeness` tinyint unsigned NOT NULL DEFAULT 99 COMMENT '',
  MODIFY `profile-id` int unsigned NOT NULL DEFAULT 0 COMMENT '',
  MODIFY `bd` date DEFAULT NULL COMMENT '',
  MODIFY `fetch_further_information` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '',
  ADD INDEX `uid_name` (`uid`,`name`(190)),
  ADD INDEX `dfrn-id` (`dfrn-id`(64)),
  ADD INDEX `issued-id` (`issued-id`(64)),
  COMMENT = 'contact table';

This was successful.

conv

ALTER TABLE `conv` MODIFY `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', MODIFY `guid` varchar(255) NOT NULL DEFAULT '' COMMENT 'A unique identifier for this conversation', MODIFY `recips` text COMMENT 'sender_handle;recipient_handle', MODIFY `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner User id', MODIFY `creator` varchar(255) NOT NULL DEFAULT '' COMMENT 'handle of creator', MODIFY `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'creation timestamp', MODIFY `updated` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'edited timestamp', MODIFY `subject` text COMMENT 'subject of initial message', COMMENT = 'private messages', DEFAULT COLLATE utf8mb4_general_ci; ALTER TABLE `conv` MODIFY `guid` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'A unique identifier for this conversation', MODIFY `recips` text COLLATE utf8mb4_general_ci COMMENT 'sender_handle;recipient_handle', MODIFY `creator` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'handle of creator', MODIFY `subject` text COLLATE utf8mb4_general_ci COMMENT 'subject of initial message';

Error 1064 occurred during database update: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALTER TABLE `conv` MODIFY `guid` varchar(255) COLLATE utf8mb4_general_ci NOT NUL' at line 1

event

ALTER TABLE `event` DROP INDEX `uid`, MODIFY `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', ADD `guid` varchar(255) NOT NULL DEFAULT '' COMMENT '', MODIFY `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner User id', MODIFY `cid` int unsigned NOT NULL DEFAULT 0 COMMENT 'contact_id (ID of the contact in contact table)', MODIFY `uri` varchar(255) NOT NULL DEFAULT '' COMMENT '', MODIFY `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'creation time', MODIFY `edited` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'last edit time', MODIFY `start` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'event start time', MODIFY `finish` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'event end time', MODIFY `summary` text COMMENT 'short description or title of the event', MODIFY `desc` text COMMENT 'event description', MODIFY `location` text COMMENT 'event location', MODIFY `type` varchar(20) NOT NULL DEFAULT '' COMMENT 'event or birthday', MODIFY `nofinish` boolean NOT NULL DEFAULT '0' COMMENT 'if event does have no end this is 1', MODIFY `adjust` boolean NOT NULL DEFAULT '1' COMMENT 'adjust to timezone of the recipient (0 or 1)', MODIFY `ignore` boolean NOT NULL DEFAULT '0' COMMENT '0 or 1', MODIFY `allow_cid` mediumtext COMMENT 'Access Control - list of allowed contact.id \'<19><78>\'', MODIFY `allow_gid` mediumtext COMMENT 'Access Control - list of allowed groups', MODIFY `deny_cid` mediumtext COMMENT 'Access Control - list of denied contact.id', MODIFY `deny_gid` mediumtext COMMENT 'Access Control - list of denied groups', ADD INDEX `uid_start` (`uid`,`start`), COMMENT = 'Events', DEFAULT COLLATE utf8mb4_general_ci; ALTER TABLE `event` MODIFY `uri` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '', MODIFY `summary` text COLLATE utf8mb4_general_ci COMMENT 'short description or title of the event', MODIFY `desc` text COLLATE utf8mb4_general_ci COMMENT 'event description', MODIFY `location` text COLLATE utf8mb4_general_ci COMMENT 'event location', MODIFY `type` varchar(20) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'event or birthday', MODIFY `allow_cid` mediumtext COLLATE utf8mb4_general_ci COMMENT 'Access Control - list of allowed contact.id \'<19><78>\'', MODIFY `allow_gid` mediumtext COLLATE utf8mb4_general_ci COMMENT 'Access Control - list of allowed groups', MODIFY `deny_cid` mediumtext COLLATE utf8mb4_general_ci COMMENT 'Access Control - list of denied contact.id', MODIFY `deny_gid` mediumtext COLLATE utf8mb4_general_ci COMMENT 'Access Control - list of denied groups';

Error 1064 occurred during database update: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALTER TABLE `event` MODIFY `uri` varchar(255) COLLATE utf8mb4_general_ci NOT NUL' at line 1

fcontact

DROP TABLE IF EXISTS `temp-fcontact`;
CREATE TABLE `temp-fcontact` LIKE `fcontact`;
ALTER TABLE `temp-fcontact`
 DROP INDEX `addr`,
 MODIFY `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
 ADD `guid` varchar(255) NOT NULL DEFAULT '' COMMENT 'unique id',
 MODIFY `url` varchar(255) NOT NULL DEFAULT '' COMMENT '',
 MODIFY `name` varchar(255) NOT NULL DEFAULT '' COMMENT '',
 MODIFY `photo` varchar(255) NOT NULL DEFAULT '' COMMENT '',
 MODIFY `request` varchar(255) NOT NULL DEFAULT '' COMMENT '',
 MODIFY `nick` varchar(255) NOT NULL DEFAULT '' COMMENT '',
 MODIFY `addr` varchar(255) NOT NULL DEFAULT '' COMMENT '',
 MODIFY `batch` varchar(255) NOT NULL DEFAULT '' COMMENT '',
 MODIFY `notify` varchar(255) NOT NULL DEFAULT '' COMMENT '',
 MODIFY `poll` varchar(255) NOT NULL DEFAULT '' COMMENT '',
 MODIFY `confirm` varchar(255) NOT NULL DEFAULT '' COMMENT '',
 MODIFY `priority` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '',
 MODIFY `network` char(4) NOT NULL DEFAULT '' COMMENT '',
 MODIFY `alias` varchar(255) NOT NULL DEFAULT '' COMMENT '',
 MODIFY `pubkey` text COMMENT '',
 MODIFY `updated` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '',
 ADD INDEX `addr` (`addr`(32)),
 ADD UNIQUE INDEX `url` (`url`(190)),
 COMMENT = 'Diaspora compatible contacts - used in the Diaspora implementation',
 DEFAULT COLLATE utf8mb4_general_ci;
ALTER TABLE `temp-fcontact`
 MODIFY `url` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '',
 MODIFY `name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '',
 MODIFY `photo` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '',
 MODIFY `request` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '',
 MODIFY `nick` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '',
 MODIFY `addr` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '',
 MODIFY `batch` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '',
 MODIFY `notify` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '',
 MODIFY `poll` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '',
 MODIFY `confirm` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '',
 MODIFY `network` char(4) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '',
 MODIFY `alias` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '',
 MODIFY `pubkey` text COLLATE utf8mb4_general_ci COMMENT '';
INSERT INTO `temp-fcontact`
  SELECT
    ANY_VALUE(`id`),
    ANY_VALUE(`url`),
    ANY_VALUE(`name`),
    ANY_VALUE(`photo`),
    ANY_VALUE(`request`),
    ANY_VALUE(`nick`),
    ANY_VALUE(`addr`),
    ANY_VALUE(`batch`),
    ANY_VALUE(`notify`),
    ANY_VALUE(`poll`),
    ANY_VALUE(`confirm`),
    ANY_VALUE(`priority`),
    ANY_VALUE(`network`),
    ANY_VALUE(`alias`),
    ANY_VALUE(`pubkey`),
    ANY_VALUE(`updated`)
  FROM `fcontact` GROUP BY `url`;
DROP TABLE `fcontact`;
RENAME TABLE `temp-fcontact` TO `fcontact`;

Error 1064 occurred during database update: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALTER TABLE `temp-fcontact` MODIFY `url` varchar(255) COLLATE utf8mb4_general_ci' at line 1

Details: for the INSERT statement, "Error Code: 1136. Column count doesn't match value count at row 1"

The problem is that there is no value for the added `guid` field. Modified INSERT:

INSERT INTO `temp-fcontact`
  SELECT
    ANY_VALUE(`id`),
    ANY_VALUE(`url`),
    ANY_VALUE(`name`),
    ANY_VALUE(`photo`),
    ANY_VALUE(`request`),
    ANY_VALUE(`nick`),
    ANY_VALUE(`addr`),
    ANY_VALUE(`batch`),
    ANY_VALUE(`notify`),
    ANY_VALUE(`poll`),
    ANY_VALUE(`confirm`),
    ANY_VALUE(`priority`),
    ANY_VALUE(`network`),
    ANY_VALUE(`alias`),
    ANY_VALUE(`pubkey`),
    ANY_VALUE(`updated`),
    ''
  FROM `fcontact` GROUP BY `url`;

fsuggest

ALTER TABLE `fsuggest` MODIFY `id` int unsigned NOT NULL auto_increment COMMENT '', MODIFY `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id', MODIFY `cid` int unsigned NOT NULL DEFAULT 0 COMMENT '', MODIFY `name` varchar(255) NOT NULL DEFAULT '' COMMENT '', MODIFY `url` varchar(255) NOT NULL DEFAULT '' COMMENT '', MODIFY `request` varchar(255) NOT NULL DEFAULT '' COMMENT '', MODIFY `photo` varchar(255) NOT NULL DEFAULT '' COMMENT '', MODIFY `note` text COMMENT '', MODIFY `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', COMMENT = 'friend suggestion stuff', DEFAULT COLLATE utf8mb4_general_ci; ALTER TABLE `fsuggest` MODIFY `name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '', MODIFY `url` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '', MODIFY `request` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '', MODIFY `photo` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '', MODIFY `note` text COLLATE utf8mb4_general_ci COMMENT '';

Error 1064 occurred during database update: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALTER TABLE `fsuggest` MODIFY `name` varchar(255) COLLATE utf8mb4_general_ci NOT' at line 1

gcontact

DROP TABLE IF EXISTS `temp-gcontact`;
CREATE TABLE `temp-gcontact` LIKE `gcontact`;
ALTER TABLE `temp-gcontact` DROP INDEX `nurl`, MODIFY `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', MODIFY `name` varchar(255) NOT NULL DEFAULT '' COMMENT 'Name that this contact is known by', MODIFY `nick` varchar(255) NOT NULL DEFAULT '' COMMENT 'Nick- and user name of the contact', MODIFY `url` varchar(255) NOT NULL DEFAULT '' COMMENT 'Link to the contacts profile page', MODIFY `nurl` varchar(255) NOT NULL DEFAULT '' COMMENT '', MODIFY `photo` varchar(255) NOT NULL DEFAULT '' COMMENT 'Link to the profile photo', MODIFY `connect` varchar(255) NOT NULL DEFAULT '' COMMENT '', MODIFY `keywords` text COMMENT 'puplic keywords (interests)', MODIFY `birthday` varchar(32) NOT NULL DEFAULT '0001-01-01' COMMENT '', MODIFY `community` boolean NOT NULL DEFAULT '0' COMMENT '1 if contact is forum account', MODIFY `contact-type` tinyint NOT NULL DEFAULT -1 COMMENT '', MODIFY `hide` boolean NOT NULL DEFAULT '0' COMMENT '1 = should be hidden from search', MODIFY `nsfw` boolean NOT NULL DEFAULT '0' COMMENT '1 = contact posts nsfw content', MODIFY `network` char(4) NOT NULL DEFAULT '' COMMENT 'social network protocol', MODIFY `notify` varchar(255) COMMENT '', MODIFY `generation` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', MODIFY `server_url` varchar(255) NOT NULL DEFAULT '' COMMENT 'baseurl of the contacts server', ADD UNIQUE INDEX `nurl` (`nurl`(190)), ADD INDEX `name` (`name`(64)), COMMENT = 'global contacts', DEFAULT COLLATE utf8mb4_general_ci; ALTER TABLE `temp-gcontact` MODIFY `name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'Name that this contact is known by', MODIFY `nick` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'Nick- and user name of the contact', MODIFY `url` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'Link to the contacts profile page', MODIFY `nurl` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '', MODIFY `photo` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'Link to the profile photo', MODIFY `connect` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '', MODIFY `location` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '', MODIFY `about` text COLLATE utf8mb4_general_ci COMMENT '', MODIFY `keywords` text COLLATE utf8mb4_general_ci COMMENT 'puplic keywords (interests)', MODIFY `gender` varchar(32) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '', MODIFY `birthday` varchar(32) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0001-01-01' COMMENT '', MODIFY `network` char(4) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'social network protocol', MODIFY `addr` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '', MODIFY `notify` varchar(255) COLLATE utf8mb4_general_ci COMMENT '', MODIFY `alias` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '', MODIFY `server_url` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'baseurl of the contacts server';
INSERT INTO `temp-gcontact` SELECT ANY_VALUE(`id`),ANY_VALUE(`name`),ANY_VALUE(`nick`),ANY_VALUE(`created`),ANY_VALUE(`updated`),ANY_VALUE(`last_contact`),ANY_VALUE(`last_failure`),ANY_VALUE(`location`),ANY_VALUE(`about`),ANY_VALUE(`keywords`),ANY_VALUE(`gender`),ANY_VALUE(`birthday`),ANY_VALUE(`community`),ANY_VALUE(`contact-type`),ANY_VALUE(`hide`),ANY_VALUE(`nsfw`),ANY_VALUE(`network`),ANY_VALUE(`addr`),ANY_VALUE(`notify`),ANY_VALUE(`alias`),ANY_VALUE(`generation`),ANY_VALUE(`server_url`),ANY_VALUE(`url`),ANY_VALUE(`nurl`),ANY_VALUE(`photo`),ANY_VALUE(`connect`) FROM `gcontact` GROUP BY `nurl`;
DROP TABLE `gcontact`;
RENAME TABLE `temp-gcontact` TO `gcontact`;

Error 1064 occurred during database update: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALTER TABLE `temp-gcontact` MODIFY `name` varchar(255) COLLATE utf8mb4_general_c' at line 1

group

ALTER TABLE `group` MODIFY `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', MODIFY `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner User id', MODIFY `visible` boolean NOT NULL DEFAULT '0' COMMENT '1 indicates the member list is not private', MODIFY `deleted` boolean NOT NULL DEFAULT '0' COMMENT '1 indicates the group has been deleted', MODIFY `name` varchar(255) NOT NULL DEFAULT '' COMMENT 'human readable name of group', COMMENT = 'privacy groups, group info', DEFAULT COLLATE utf8mb4_general_ci; ALTER TABLE `group` MODIFY `name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'human readable name of group';

Error 1064 occurred during database update: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALTER TABLE `group` MODIFY `name` varchar(255) COLLATE utf8mb4_general_ci NOT NU' at line 1

gserver

DROP TABLE IF EXISTS `temp-gserver`;
CREATE TABLE `temp-gserver` LIKE `gserver`;
ALTER TABLE `temp-gserver`
  DROP INDEX `nurl`,
  MODIFY `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
  MODIFY `register_policy` tinyint NOT NULL DEFAULT 0 COMMENT '',
  ADD `registered-users` int unsigned NOT NULL DEFAULT 0 COMMENT 'Number of registered users',
  MODIFY `network` char(4) NOT NULL DEFAULT '' COMMENT '',
  ADD `relay-subscribe` boolean NOT NULL DEFAULT '0' COMMENT 'Has the server subscribed to the relay system',
  ADD `relay-scope` varchar(10) NOT NULL DEFAULT '' COMMENT 'The scope of messages that the server wants to get',
  ADD UNIQUE INDEX `nurl` (`nurl`(190)),
  COMMENT = 'Global servers'; 
INSERT INTO `temp-gserver` SELECT ANY_VALUE(`id`),ANY_VALUE(`url`),ANY_VALUE(`nurl`),ANY_VALUE(`version`),ANY_VALUE(`site_name`),ANY_VALUE(`info`),ANY_VALUE(`register_policy`),ANY_VALUE(`poco`),ANY_VALUE(`noscrape`),ANY_VALUE(`network`),ANY_VALUE(`platform`),ANY_VALUE(`created`),ANY_VALUE(`last_poco_query`),ANY_VALUE(`last_contact`),ANY_VALUE(`last_failure`) FROM `gserver` GROUP BY `nurl`;
DROP TABLE `gserver`;
RENAME TABLE `temp-gserver` TO `gserver`;

Error 1136 occurred during database update: Column count doesn't match value count at row 1

Details: for INSERT statement, "Error Code: 1136. Column count doesn't match value count at row 1" Modified INSERT statement to include the 3 columns which had been added earlier:

INSERT INTO `temp-gserver`
  SELECT
	ANY_VALUE(`id`),
    ANY_VALUE(`url`),
    ANY_VALUE(`nurl`),
    ANY_VALUE(`version`),
    ANY_VALUE(`site_name`),
    ANY_VALUE(`info`),
    ANY_VALUE(`register_policy`),
    ANY_VALUE(`poco`),
    ANY_VALUE(`noscrape`),
    ANY_VALUE(`network`),
    ANY_VALUE(`platform`),
    ANY_VALUE(`created`),
    ANY_VALUE(`last_poco_query`),
    ANY_VALUE(`last_contact`),
    ANY_VALUE(`last_failure`),
    '0',
    '0',
    ''
  FROM `gserver`
  GROUP BY `nurl`;

This was successful.