Difference between revisions of "User:Woozle/Friendica/2017 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
(updated syntax highlighting tags)
 
(6 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
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.
 
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==
+
==Table Modifications==
−
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.
+
* [[/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
−
|
+
* [[/gcontact]]
−
| valign=top |
+
* [[/notify]]
−
===create table===
+
==Other Changes==
−
<mysql>CREATE TABLE IF NOT EXISTS `contact` (
+
===acl_selectors===
−
`id` int(11) NOT NULL auto_increment,
+
This SQL:<source lang=mysql>SELECT `group`.`id`, `group`.`name`, GROUP_CONCAT(DISTINCT `group_member`.`contact-id` SEPARATOR ',') AS uids
−
`uid` int(11) NOT NULL DEFAULT 0,
+
FROM `group`
−
`created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
+
INNER JOIN `group_member` ON `group_member`.`gid`=`group`.`id` AND `group_member`.`uid` = `group`.`uid`
−
`self` tinyint(1) NOT NULL DEFAULT 0,
+
WHERE NOT `group`.`deleted` AND `group`.`uid` = 1
−
`remote_self` tinyint(1) NOT NULL DEFAULT 0,
+
−
`rel` tinyint(1) NOT NULL DEFAULT 0,
+
GROUP BY `group`.`name`
−
`duplex` tinyint(1) NOT NULL DEFAULT 0,
+
ORDER BY `group`.`name`
−
`network` varchar(255) NOT NULL DEFAULT '',
+
LIMIT 0,100</source>
−
`name` varchar(255) NOT NULL DEFAULT '',
+
...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'''.
−
`nick` varchar(255) NOT NULL DEFAULT '',
+
 
−
`location` varchar(255) NOT NULL DEFAULT '',
+
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>
−
`about` text,
+
==Sticking Point==
−
`keywords` text,
+
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):
−
`gender` varchar(32) NOT NULL DEFAULT '',
+
<pre>
−
`xmpp` varchar(255) NOT NULL DEFAULT '',
+
2017-04-08 19:33:18@wrk58e93afe0cd759.83335256 [NORMAL]:dba.php:289:q DB Error (Connected) 1050: Table 'temp-addon' already exists
−
`attag` varchar(255) NOT NULL DEFAULT '',
+
2017-04-08 19:33:18@wrk58e93afe0cd759.83335256 [NORMAL]:dba.php:328:q dba: CREATE TABLE `temp-addon` LIKE `addon`; returned false.
−
`avatar` varchar(255) NOT NULL DEFAULT '',
+
Table 'temp-addon' already exists
−
`photo` text,
+
2017-04-08 19:33:18@wrk58e93afe0cd759.83335256 []:dbstructure.php:25:update_fail
−
`thumb` text,
+
Cannot notify administrators about update_id=1216, error_message=Errors encountered performing database changes.
−
`micro` text,
+
ALTER TABLE `temp-addon` MODIFY `name` varchar(190) NOT NULL DEFAULT '', MODIFY `version` varchar(255) NOT NULL DEFAULT '', ADD UNIQUE INDEX `name` (`name`);<br />
−
`site-pubkey` text,
+
</pre>
−
`issued-id` varchar(255) NOT NULL DEFAULT '',
+
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''.
−
`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').
 
−
|}
 

Latest revision as of 18:29, 12 October 2019

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.

Table Modifications

  • /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
  • /gcontact
  • /notify

Other Changes

acl_selectors

This SQL:

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

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

grep -r "GROUP BY \`group\`.\`name\`" *.php

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:

GROUP BY `group`.`name`, `group`.`id`

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): 

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 />

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.

Retrieved from "https://htyp.org/mw/index.php?title=User:Woozle/Friendica/2017_upgrade&oldid=25335"