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

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Jump to navigation Jump to search
 
(9 intermediate revisions by the same user not shown)
Line 2: Line 2:
 
|-
 
|-
 
| valign=top | __TOC__
 
| valign=top | __TOC__
−
|
+
| width=90% |
−
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, and additional notes in blockquote-blocks; the original raw output is available on the [https://htyp.org/mw/index.php?title=User:Woozle/Friendica/2019_upgrade&oldid=25345 first version] of the page.
+
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 [https://htyp.org/mw/index.php?title=User:Woozle/Friendica/2019_upgrade&oldid=25345 first version] of the page.
  
 
==Command==
 
==Command==
Line 21: Line 21:
 
Error 1064 occurred during database update:
 
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
 
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
−
<blockquote>'''Note''': This ran fine in MySQL Workbench.</blockquote>
 
 
===attach===
 
===attach===
 
<source lang=mysql>
 
<source lang=mysql>
Line 28: Line 27:
 
Error 1064 occurred during database update:
 
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
 
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
−
<blockquote>'''Note''': This ran fine in MySQL Workbench.</blockquote>
 
 
===auth_codes===
 
===auth_codes===
 
<source lang=mysql>
 
<source lang=mysql>
Line 35: Line 33:
 
Error 1064 occurred during database update:
 
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
 
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
−
<blockquote>'''Note''': This ran fine in MySQL Workbench.</blockquote>
 
  
 
===challenge===
 
===challenge===
Line 43: Line 40:
 
Error 1064 occurred during database update:
 
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
 
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
−
<blockquote>'''Note''': This ran fine in MySQL Workbench.</blockquote>
 
−
 
 
===clients===
 
===clients===
 
<source lang=mysql>
 
<source lang=mysql>
Line 51: Line 46:
 
Error 1064 occurred during database update:
 
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
 
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
−
<blockquote>'''Note''': This ran fine in MySQL Workbench.</blockquote>
 
−
 
 
===config===
 
===config===
 
<source lang=mysql>
 
<source lang=mysql>
 
DROP TABLE IF EXISTS `temp-config`;
 
DROP TABLE IF EXISTS `temp-config`;
 
CREATE TABLE `temp-config` LIKE `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 '';
+
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`;
 
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`;
 
DROP TABLE `config`;
Line 64: Line 58:
 
Error 1064 occurred during database update:
 
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
 
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
 +
<blockquote>
 +
'''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:<source lang=mysql>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 '';</source>
 +
This was successful.
 +
</blockquote>
 +
 
===contact===
 
===contact===
 
<source lang=mysql>
 
<source lang=mysql>
−
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';</source>
+
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';
 +
</source>
  
 
Error 1265 occurred during database update:
 
Error 1265 occurred during database update:
 
Data truncated for column 'last-update' at row 1
 
Data truncated for column 'last-update' at row 1
 +
<blockquote>
 +
'''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 [https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html MySQL 8.0] and even in [https://dev.mysql.com/doc/refman/5.7/en/date-and-time-types.html 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:<source lang=mysql>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';</source>
 +
</blockquote>
 +
This was successful.
 +
 
===conv===
 
===conv===
 
<source lang=mysql>
 
<source lang=mysql>
Line 86: Line 189:
 
DROP TABLE IF EXISTS `temp-fcontact`;
 
DROP TABLE IF EXISTS `temp-fcontact`;
 
CREATE TABLE `temp-fcontact` LIKE `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 '';
+
ALTER TABLE `temp-fcontact`
−
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 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`;
 
DROP TABLE `fcontact`;
 
RENAME TABLE `temp-fcontact` TO `fcontact`;</source>
 
RENAME TABLE `temp-fcontact` TO `fcontact`;</source>
Line 93: Line 250:
 
Error 1064 occurred during database update:
 
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
 
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
 +
<blockquote>
 +
'''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:<source lang=mysql>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`;
 +
</source>
 +
</blockquote>
 +
 
===fsuggest===
 
===fsuggest===
 
<source lang=mysql>
 
<source lang=mysql>
Line 120: Line 303:
 
DROP TABLE IF EXISTS `temp-gserver`;
 
DROP TABLE IF EXISTS `temp-gserver`;
 
CREATE TABLE `temp-gserver` LIKE `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';  
+
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`;
 
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`;
 
DROP TABLE `gserver`;
Line 127: Line 319:
 
Error 1136 occurred during database update:
 
Error 1136 occurred during database update:
 
Column count doesn't match value count at row 1
 
Column count doesn't match value count at row 1
 +
<blockquote>'''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:<source lang=mysql>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`;</source>
 +
This was successful.
 
|}
 
|}
 +
 
==Output (2/2)==
 
==Output (2/2)==
 +
<blockquote>'''Note''': These seem to be nothing more than a list of the specific queries which failed. I only checked the first two, but the SQL is identical to the corresponding statements in [[#Output (1/2)]]. All of the same tables are listed.</blockquote>
 
===addon===
 
===addon===
 
Errors encountered performing database changes: <source lang=mysql>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';</source>
 
Errors encountered performing database changes: <source lang=mysql>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';</source>
Line 157: Line 376:
 
===gserver===
 
===gserver===
 
Errors encountered performing database changes: <source lang=mysql>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';</source>
 
Errors encountered performing database changes: <source lang=mysql>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';</source>
 +
 
==2019-10-13==
 
==2019-10-13==
−
Running the first set of SQL (for <code>addon</code>) via [[MySQL Workbench]] seems to work fine -- so I suspect the problem may be unescaped quotes in the PHP code or something.
+
Using [[MySQL Workbench]] to run many of these queries seems to work just fine; I suspect unescaped quotes in the PHP code may be causing problems, although there are issues with the actual SQL statement which had to be resolved as well.
 +
 
 +
These all ran successfully in MySQL Workbench, without modification except where noted:
 +
* addon
 +
* attach
 +
* auth_codes
 +
* challenge
 +
* clients (modified)
 +
* contact (heavily modified)
 +
* conv
 +
* event
 +
* fcontact (one field added to INSERT)
 +
* fsuggest
 +
* gcontact
 +
* group
 +
* gserver (3 fields added to INSERT)

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.

Output (2/2)

Note: These seem to be nothing more than a list of the specific queries which failed. I only checked the first two, but the SQL is identical to the corresponding statements in #Output (1/2). All of the same tables are listed.

addon

Errors encountered performing database changes: 

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';

attach

Errors encountered performing database changes: 

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';

auth_codes

Errors encountered performing database changes: 

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 '';

challenge

Errors encountered performing database changes: 

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 '';

clients

Errors encountered performing database changes: 

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 '';

config

Errors encountered performing database changes: 

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 '';

contact

Errors encountered performing database changes: 

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';

conv

Errors encountered performing database changes: 

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';

event

Errors encountered performing database changes: 

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';

fcontact

Errors encountered performing database changes: 

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 '';

fsuggest

Errors encountered performing database changes: 

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 '';

gcontact

Errors encountered performing database changes: 

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';

group

Errors encountered performing database changes: 

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';

gserver

Errors encountered performing database changes: 

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';

2019-10-13

Using MySQL Workbench to run many of these queries seems to work just fine; I suspect unescaped quotes in the PHP code may be causing problems, although there are issues with the actual SQL statement which had to be resolved as well.

These all ran successfully in MySQL Workbench, without modification except where noted:

  • addon
  • attach
  • auth_codes
  • challenge
  • clients (modified)
  • contact (heavily modified)
  • conv
  • event
  • fcontact (one field added to INSERT)
  • fsuggest
  • gcontact
  • group
  • gserver (3 fields added to INSERT)
Retrieved from "https://htyp.org/mw/index.php?title=User:Woozle/Friendica/2019_upgrade&oldid=25363"