User:Woozle/Friendica/2019 upgrade - Revision history

Woozle: /* 2019-10-13 */

2019-10-13T18:28:00Z

2019-10-13

← Older revision		Revision as of 18:28, 13 October 2019
Line 378:		Line 378:

	==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.

	~~Queries run~~ successfully in MySQL Workbench:		These all ran successfully in MySQL Workbench, without modification except where noted:
	* Part 1		* addon
	** addon		* attach
	** attach		* auth_codes
	** auth_codes		* challenge
	** challenge		* clients (modified)
	** clients (modified)		* contact (heavily modified)
	** contact (heavily modified)		* conv
	** conv		* event
	** event		* fcontact (one field added to INSERT)
	** fcontact (one field added to INSERT)		* fsuggest
			* gcontact
			* group
			* gserver (3 fields added to INSERT)

Woozle: /* Output (2/2) */

2019-10-13T18:18:26Z

Output (2/2)

← Older revision		Revision as of 18:18, 13 October 2019
Line 347:		Line 347:

	==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 375:		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.		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.

Woozle: /* gserver */

2019-10-13T18:12:01Z

gserver

@@ Line 303: / Line 303: @@
 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';
+ALTER TABLE `temp-gserver`
 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`;
@@ Line 310: / Line 319: @@
 Error 1136 occurred during database update:
 Column count doesn't match value count at row 1
 |}
 ==Output (2/2)==
 ===addon===

Woozle: /* 2019-10-13 */ more tables modified

2019-10-13T16:43:46Z

2019-10-13: more tables modified

← Older revision		Revision as of 16:43, 13 October 2019
Line 344:		Line 344:

	Queries run successfully in MySQL Workbench:		Queries run successfully in MySQL Workbench:
	* 1.addon		* Part 1
	* 1.attach		** addon
	* 1.auth_codes		** attach
	* 1.challenge		** auth_codes
	* 1.clients		** challenge
			** clients (modified)
			** contact (heavily modified)
			** conv
			** event
			** fcontact (one field added to INSERT)

Woozle: /* fcontact */

2019-10-13T16:42:21Z

fcontact

@@ Line 189: / Line 189: @@
 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 '';
+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`,
 DROP TABLE `fcontact`;
 RENAME TABLE `temp-fcontact` TO `fcontact`;</source>
@@ Line 196: / Line 250: @@
 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
 ===fsuggest===
 <source lang=mysql>

Woozle at 15:58, 13 October 2019

2019-10-13T15:58:23Z

← Older revision		Revision as of 15:58, 13 October 2019
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; 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.

Woozle: /* contact */

2019-10-13T15:53:00Z

contact

Show changes

Woozle: /* config */

2019-10-13T15:02:52Z

config

← Older revision		Revision as of 15:02, 13 October 2019
Line 50:		Line 50:
	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 57:		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>

Woozle: removing blockquoted bits

2019-10-13T14:52:39Z

removing blockquoted bits

← Older revision		Revision as of 14:52, 13 October 2019
Line 3:		Line 3:
	\| valign=top \| __TOC__		\| valign=top \| __TOC__
	\|		\|
	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>

Woozle: /* 2019-10-13 */

2019-10-13T14:51:08Z

2019-10-13

← Older revision		Revision as of 14:51, 13 October 2019
Line 159:		Line 159:
	==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.		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.

			Queries run successfully in MySQL Workbench:
			* 1.addon
			* 1.attach
			* 1.auth_codes
			* 1.challenge
			* 1.clients