User:Woozle/Friendica/2017 upgrade - Revision history

Woozle: updated syntax highlighting tags

2019-10-12T18:29:01Z

updated syntax highlighting tags

← Older revision		Revision as of 18:29, 12 October 2019
Line 8:		Line 8:
	==Other Changes==		==Other Changes==
	===acl_selectors===		===acl_selectors===
	This SQL:<mysql>SELECT `group`.`id`, `group`.`name`, GROUP_CONCAT(DISTINCT `group_member`.`contact-id` SEPARATOR ',') AS uids		This SQL:<source lang=mysql>SELECT `group`.`id`, `group`.`name`, GROUP_CONCAT(DISTINCT `group_member`.`contact-id` SEPARATOR ',') AS uids
	FROM `group`		FROM `group`
	INNER JOIN `group_member` ON `group_member`.`gid`=`group`.`id` AND `group_member`.`uid` = `group`.`uid`		INNER JOIN `group_member` ON `group_member`.`gid`=`group`.`id` AND `group_member`.`uid` = `group`.`uid`
Line 15:		Line 15:
	GROUP BY `group`.`name`		GROUP BY `group`.`name`
	ORDER BY `group`.`name`		ORDER BY `group`.`name`
	LIMIT 0,100</~~mysql~~>		LIMIT 0,100</source>
	...returned the following error: '''Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'icms-fka.group.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by'''.		...returned the following error: '''Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'icms-fka.group.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by'''.

	Finding where this was being generated required the use of [[grep]]:<bash>grep -r "GROUP BY \`group\`.\`name\`" *.php</~~bash~~>Note that the -r option doesn't actually seem to work; I had to run this inside the "includes" folder to identify the file -- which turned out to be '''acl_selectors.php''' line 505, which I have modified thusly:<mysql>GROUP BY `group`.`name`, `group`.`id`</~~mysql~~>		Finding where this was being generated required the use of [[grep]]:<source lang=bash>grep -r "GROUP BY \`group\`.\`name\`" *.php</source>Note that the -r option doesn't actually seem to work; I had to run this inside the "includes" folder to identify the file -- which turned out to be '''acl_selectors.php''' line 505, which I have modified thusly:<source lang=mysql>GROUP BY `group`.`name`, `group`.`id`</source>
	==Sticking Point==		==Sticking Point==
	I finally got stuck -- or, perhaps I should say, decided/realized that the next step was too big of a time-investment -- when trying to figure out where and why this error was happening (~~Reformatted~~ slightly for readability):		I finally got stuck -- or, perhaps I should say, decided/realized that the next step was too big of a time-investment -- when trying to figure out where and why this error was happening (reformatted slightly for readability):
	<pre>		<pre>
	2017-04-08 19:33:18@wrk58e93afe0cd759.83335256 [NORMAL]:dba.php:289:q DB Error (Connected) 1050: Table 'temp-addon' already exists		2017-04-08 19:33:18@wrk58e93afe0cd759.83335256 [NORMAL]:dba.php:289:q DB Error (Connected) 1050: Table 'temp-addon' already exists

Woozle: sticking point

2017-04-08T21:40:59Z

sticking point

← Older revision		Revision as of 21:40, 8 April 2017
Line 19:		Line 19:

	Finding where this was being generated required the use of [[grep]]:<bash>grep -r "GROUP BY \`group\`.\`name\`" *.php</bash>Note that the -r option doesn't actually seem to work; I had to run this inside the "includes" folder to identify the file -- which turned out to be '''acl_selectors.php''' line 505, which I have modified thusly:<mysql>GROUP BY `group`.`name`, `group`.`id`</mysql>		Finding where this was being generated required the use of [[grep]]:<bash>grep -r "GROUP BY \`group\`.\`name\`" *.php</bash>Note that the -r option doesn't actually seem to work; I had to run this inside the "includes" folder to identify the file -- which turned out to be '''acl_selectors.php''' line 505, which I have modified thusly:<mysql>GROUP BY `group`.`name`, `group`.`id`</mysql>
			==Sticking Point==
			I finally got stuck -- or, perhaps I should say, decided/realized that the next step was too big of a time-investment -- when trying to figure out where and why this error was happening (Reformatted slightly for readability):
			<pre>
			2017-04-08 19:33:18@wrk58e93afe0cd759.83335256 [NORMAL]:dba.php:289:q DB Error (Connected) 1050: Table 'temp-addon' already exists
			2017-04-08 19:33:18@wrk58e93afe0cd759.83335256 [NORMAL]:dba.php:328:q dba: CREATE TABLE `temp-addon` LIKE `addon`; returned false.
			Table 'temp-addon' already exists
			2017-04-08 19:33:18@wrk58e93afe0cd759.83335256 []:dbstructure.php:25:update_fail
			Cannot notify administrators about update_id=1216, error_message=Errors encountered performing database changes.
			ALTER TABLE `temp-addon` MODIFY `name` varchar(190) NOT NULL DEFAULT '', MODIFY `version` varchar(255) NOT NULL DEFAULT '', ADD UNIQUE INDEX `name` (`name`);<br />
			</pre>
			I found where the error was being thrown, but would have to do some significant backtracing just to figure out what was executing the problematic SQL, and/or what was generating it... and, most importantly, ''why''.

Woozle: /notify

2017-04-08T19:32:52Z

/notify

← Older revision		Revision as of 19:32, 8 April 2017
Line 5:		Line 5:
	* [[/item]] - this was a large table, and took a lot of time to do the copy		* [[/item]] - this was a large table, and took a lot of time to do the copy
	* [[/gcontact]]		* [[/gcontact]]
			* [[/notify]]
	==Other Changes==		==Other Changes==
	===acl_selectors===		===acl_selectors===

Woozle at 19:06, 8 April 2017

2017-04-08T19:06:56Z

← Older revision		Revision as of 19:06, 8 April 2017
Line 4:		Line 4:
	* [[/contact]] - this is the first one I did, so notes are more explanatory		* [[/contact]] - this is the first one I did, so notes are more explanatory
	* [[/item]] - this was a large table, and took a lot of time to do the copy		* [[/item]] - this was a large table, and took a lot of time to do the copy
			* [[/gcontact]]
	==Other Changes==		==Other Changes==
	===acl_selectors===		===acl_selectors===

Woozle: acl_selectors

2017-04-08T15:22:28Z

acl_selectors

← Older revision		Revision as of 15:22, 8 April 2017
Line 4:		Line 4:
	* [[/contact]] - this is the first one I did, so notes are more explanatory		* [[/contact]] - this is the first one I did, so notes are more explanatory
	* [[/item]] - this was a large table, and took a lot of time to do the copy		* [[/item]] - this was a large table, and took a lot of time to do the copy
			==Other Changes==
			===acl_selectors===
			This SQL:<mysql>SELECT `group`.`id`, `group`.`name`, GROUP_CONCAT(DISTINCT `group_member`.`contact-id` SEPARATOR ',') AS uids
			FROM `group`
			INNER JOIN `group_member` ON `group_member`.`gid`=`group`.`id` AND `group_member`.`uid` = `group`.`uid`
			WHERE NOT `group`.`deleted` AND `group`.`uid` = 1

			GROUP BY `group`.`name`
			ORDER BY `group`.`name`
			LIMIT 0,100</mysql>
			...returned the following error: '''Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'icms-fka.group.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by'''.

			Finding where this was being generated required the use of [[grep]]:<bash>grep -r "GROUP BY \`group\`.\`name\`" *.php</bash>Note that the -r option doesn't actually seem to work; I had to run this inside the "includes" folder to identify the file -- which turned out to be '''acl_selectors.php''' line 505, which I have modified thusly:<mysql>GROUP BY `group`.`name`, `group`.`id`</mysql>

Woozle at 14:33, 8 April 2017

2017-04-08T14:33:57Z

← Older revision		Revision as of 14:33, 8 April 2017
Line 3:		Line 3:
	==Table Modifications==		==Table Modifications==
	* [[/contact]] - this is the first one I did, so notes are more explanatory		* [[/contact]] - this is the first one I did, so notes are more explanatory
	* [[/item]]		* [[/item]] - this was a large table, and took a lot of time to do the copy

Woozle: extracted `contact` to subpage; added `item`

2017-04-08T14:33:26Z

extracted `contact` to subpage; added `item`

Show changes

Woozle: /* modify Contacts */

2017-04-08T13:20:15Z

modify Contacts

← Older revision		Revision as of 13:20, 8 April 2017
Line 2:		Line 2:

	==modify Contacts==		==modify Contacts==
	~~To modify~~ the `contacts` table, I had to rename the old table, create the new one (modified from the official spec), then copy the old data into the new table.		It proved impossible to simply alter the `contacts` table in place because of invalid DATETIME VALUES. Instead I had to rename the old table, create the new one (modified from the official spec), then copy the old data into the new table.
	{\|		{\|
	\|		\|

Woozle: Created page with "This isn't a complete description of everything I had to do in order to get the latest Friendica working with my existing data, but it shows how I solved some of the more..."

2017-04-08T13:18:04Z

Created page with "This isn't a complete description of everything I had to do in order to get the latest Friendica working with my existing data, but it shows how I solved some of the more..."

New page

This isn't a complete description of everything I had to do in order to get the latest [[Friendica]] working with my existing data, but it shows how I solved some of the more thorny problems.

==modify Contacts==
To modify the `contacts` table, I had to rename the old table, create the new one (modified from the official spec), then copy the old data into the new table.
{|
|
| valign=top |
===create table===
<mysql>CREATE TABLE IF NOT EXISTS `contact` (
`id` int(11) NOT NULL auto_increment,
`uid` int(11) NOT NULL DEFAULT 0,
`created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`self` tinyint(1) NOT NULL DEFAULT 0,
`remote_self` tinyint(1) NOT NULL DEFAULT 0,
`rel` tinyint(1) NOT NULL DEFAULT 0,
`duplex` tinyint(1) NOT NULL DEFAULT 0,
`network` varchar(255) NOT NULL DEFAULT '',
`name` varchar(255) NOT NULL DEFAULT '',
`nick` varchar(255) NOT NULL DEFAULT '',
`location` varchar(255) NOT NULL DEFAULT '',
`about` text,
`keywords` text,
`gender` varchar(32) NOT NULL DEFAULT '',
`xmpp` varchar(255) NOT NULL DEFAULT '',
`attag` varchar(255) NOT NULL DEFAULT '',
`avatar` varchar(255) NOT NULL DEFAULT '',
`photo` text,
`thumb` text,
`micro` text,
`site-pubkey` text,
`issued-id` varchar(255) NOT NULL DEFAULT '',
`dfrn-id` varchar(255) NOT NULL DEFAULT '',
`url` varchar(255) NOT NULL DEFAULT '',
`nurl` varchar(255) NOT NULL DEFAULT '',
`addr` varchar(255) NOT NULL DEFAULT '',
`alias` varchar(255) NOT NULL DEFAULT '',
`pubkey` text,
`prvkey` text,
`batch` varchar(255) NOT NULL DEFAULT '',
`request` text,
`notify` text,
`poll` text,
`confirm` text,
`poco` text,
`aes_allow` tinyint(1) NOT NULL DEFAULT 0,
`ret-aes` tinyint(1) NOT NULL DEFAULT 0,
`usehub` tinyint(1) NOT NULL DEFAULT 0,
`subhub` tinyint(1) NOT NULL DEFAULT 0,
`hub-verify` varchar(255) NOT NULL DEFAULT '',
`last-update` datetime DEFAULT NULL,
`success_update` datetime DEFAULT NULL,
`failure_update` datetime DEFAULT NULL,
`name-date` datetime DEFAULT NULL,
`uri-date` datetime DEFAULT NULL,
`avatar-date` datetime DEFAULT NULL,
`term-date` datetime DEFAULT NULL,
`last-item` datetime DEFAULT NULL,
`priority` tinyint(3) NOT NULL DEFAULT 0,
`blocked` tinyint(1) NOT NULL DEFAULT 1,
`readonly` tinyint(1) NOT NULL DEFAULT 0,
`writable` tinyint(1) NOT NULL DEFAULT 0,
`forum` tinyint(1) NOT NULL DEFAULT 0,
`prv` tinyint(1) NOT NULL DEFAULT 0,
`contact-type` int(11) unsigned NOT NULL DEFAULT 0,
`hidden` tinyint(1) NOT NULL DEFAULT 0,
`archive` tinyint(1) NOT NULL DEFAULT 0,
`pending` tinyint(1) NOT NULL DEFAULT 1,
`rating` tinyint(1) NOT NULL DEFAULT 0,
`reason` text,
`closeness` tinyint(2) NOT NULL DEFAULT 99,
`info` mediumtext,
`profile-id` int(11) NOT NULL DEFAULT 0,
`bdyear` varchar(4) NOT NULL DEFAULT '',
`bd` date DEFAULT NULL,
`notify_new_posts` tinyint(1) NOT NULL DEFAULT 0,
`fetch_further_information` tinyint(1) NOT NULL DEFAULT 0,
`ffi_keyword_blacklist` text,
PRIMARY KEY(`id`),
INDEX `uid_name` (`uid`,`name`),
INDEX `self_uid` (`self`,`uid`),
INDEX `alias_uid` (`alias`(32),`uid`),
INDEX `pending_uid` (`pending`,`uid`),
INDEX `blocked_uid` (`blocked`,`uid`),
INDEX `uid_rel_network_poll` (`uid`,`rel`,`network`,`poll`(64),`archive`),
INDEX `uid_network_batch` (`uid`,`network`,`batch`(64)),
INDEX `addr_uid` (`addr`(32),`uid`),
INDEX `nurl_uid` (`nurl`(32),`uid`),
INDEX `nick_uid` (`nick`(32),`uid`),
INDEX `dfrn-id` (`dfrn-id`),
INDEX `issued-id` (`issued-id`)
) DEFAULT CHARSET=utf8mb4;</mysql>
All but one of the DATETIME fields are now DEFAULT NULL. I decided `create` should be DEFAULT CURRENT_TIMESTAMP, because how far wrong can you go with that in a creation-timestamp field? I guess we'll find out...
|
===copy data===
<mysql>INSERT INTO `contact`(
`id`,
`uid`,
`created`,
`self`,
`remote_self`,
`rel`,
`duplex`,
`network`,
`name`,
`nick`,
`attag`,
`photo`,
`thumb`,
`micro`,
`site-pubkey`,
`issued-id`,
`dfrn-id`,
`url`,
`nurl`,
`addr`,
`alias`,
`pubkey`,
`prvkey`,
`batch`,
`request`,
`notify`,
`poll`,
`confirm`,
`poco`,
`aes_allow`,
`ret-aes`,
`usehub`,
`subhub`,
`hub-verify`,
`last-update`,
`success_update`,
`name-date`,
`uri-date`,
`avatar-date`,
`term-date`,
`priority`,
`blocked`,
`readonly`,
`writable`,
`forum`,
`prv`,
`hidden`,
`archive`,
`pending`,
`rating`,
`reason`,
`closeness`,
`info`,
`profile-id`,
`bdyear`,
`bd`,
`notify_new_posts`,
`fetch_further_information`,
`ffi_keyword_blacklist`
)
SELECT
`id`,
`uid`,
`created`,
`self`,
`remote_self`,
`rel`,
`duplex`,
`network`,
`name`,
`nick`,
`attag`,
`photo`,
`thumb`,
`micro`,
`site-pubkey`,
`issued-id`,
`dfrn-id`,
`url`,
`nurl`,
`addr`,
`alias`,
`pubkey`,
`prvkey`,
`batch`,
`request`,
`notify`,
`poll`,
`confirm`,
`poco`,
`aes_allow`,
`ret-aes`,
`usehub`,
`subhub`,
`hub-verify`,
IF(UNIX_TIMESTAMP(`last-update`)=0,NULL,`last-update`),
IF(UNIX_TIMESTAMP(`success_update`)=0,NULL,`success_update`),
IF(UNIX_TIMESTAMP(`name-date`)=0,NULL,`name-date`),
IF(UNIX_TIMESTAMP(`uri-date`)=0,NULL,`uri-date`),
IF(UNIX_TIMESTAMP(`avatar-date`)=0,NULL,`avatar-date`),
IF(UNIX_TIMESTAMP(`term-date`)=0,NULL,`term-date`),
`priority`,
`blocked`,
`readonly`,
`writable`,
`forum`,
`prv`,
`hidden`,
`archive`,
`pending`,
`rating`,
`reason`,
`closeness`,
`info`,
`profile-id`,
`bdyear`,
IF(UNIX_TIMESTAMP(`bd`)=0,NULL,`bd`),
`notify_new_posts`,
`fetch_further_information`,
`ffi_keyword_blacklist`
FROM `icms-fka`.`contact-original`;</mysql>
The "UNIX_TIMESTAMP()" function was necessary because I would get errors even when comparing the field to '0000-00-00 00:00:00'. If there were any illegal values other than that, I couldn't find them even by doing a SELECT which explicitly filtered for them (SELECT UNIX_TIMESTAMP(`last-update`), `last-update` FROM `contact-original` WHERE UNIX_TIMESTAMP(`last-update`)=0 AND `last-update` != '0000-00-00 00:00:00').
|}