Social: Upgrade to 0.2.0 fails due to invalid time/date format on SQL Insert

[toolstack]

Describe the bug
Upgrading to 0.2.0 failes to insert values in to the oc_social_a2_actors table due to the creation field being added is blank:

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '' for column 'creation' at row 1

To Reproduce
Steps to reproduce the behavior:

1. Upgrade the plugin to 0.2.0 from 0.1.4

Expected behavior
Successful upgrade.

Screenshots
N/A

Client details:

• OS: Ubuntu 19.04
• Browser: Vivalidi (but also fails from the occ command line)
• Version: 2.4.1488.38
• Device: Laptop

Server details

Social app version:
0.1.4 ->0.2.0
Operating system:
Ubuntu 18.04
Web server:
Apache
Database:
MySQL
PHP version:
7.2.17
Nextcloud version: (see Nextcloud admin page)
16.01

Logs

Nextcloud log (data/nextcloud.log)

Insert your Nextcloud log here

Browser log

Insert your browser log here, this could for example include:

a) The javascript console log
b) The network log
c) ...

[toolstack]

P.S. I disabled social through occ to get my server back up and running. I can re-enable it and do any testing you need though.

[Doc-Saintly]

P.S. I disabled social through occ to get my server back up and running. I can re-enable it and do any testing you need though.

Can you provide the command you used to disable it? I'm not as familiar with OCC and my server is currently unusable because of this.

[ArtificialOwl]

Do you have logs ?

Also, which was the version of your first install of the social app ?

[ArtificialOwl]

Could you try this patch ?

[ArtificialOwl]

@Doc-Saintly :

 ./occ app:disable social

[toolstack]

@Doc-Saintly I also had to disable maintenance mode:

./occ maintenance:mode --off

@daita I'll try the patch now.

[toolstack]

@daita cleared the first error but threw the same incorrect date/time error for the oc_social_a2_cache_documts table.

[ArtificialOwl]

the exact same error message ?

[toolstack]

See below, I've removed a couple of the identifing values and replaced them with "removed":

An exception occurred while executing 'INSERT INTO oc_social_a2_cache_documts (id, id_prim, type, parent_id, media_type, mime_type, url, local_copy, public, error, creation, caching) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params ["removed", "removed", "Image", "", "", "", "removed", "avatar", "0", "0", "2018-12-26 23:45:11", ""]: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '' for column 'caching' at row 1

[ArtificialOwl]

ok, I pushed a new commit, should be fine now !

new patch: https://github.com/nextcloud/social/pull/553/files#diff-1e5010c6976d84782166771e56d48b43

or full file to replace: https://raw.githubusercontent.com/nextcloud/social/a68c35e01f3e7fb99e6f2d77e43ce6715d694f40/lib/Migration/Version0002Date20190506000001.php

[toolstack]

Ok, no more date/time format errors but now I get an error on the oc_social_a2_actors for the id_prim value (not sure if I need to remove the value for this report, but I did just in case):

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'removed' for key 'PRIMARY'

[ArtificialOwl]

can you drop data from all oc_social_a2_* tables ?

[toolstack]

Drop the tables or delete the data?

[toolstack]

Ok, I truncated the tables and re-enabled the app, seems to be working now.

[ArtificialOwl]

both would work.

But if you prefer, you also try the last commit that should catch the duplicate entry exception and keep with the migration: https://github.com/nextcloud/social/pull/553/commits/b13ade6938701ae7a8c38be9984651991a953109

[ArtificialOwl]

thanks for your report and support

[toolstack]

No problem, thanks for the quick response.

[Doc-Saintly]

I applied the patch and then tried the web updater again. There was an error (but I couldn't find the details), and then it was stuck in maintenance mode. After disabling maintenance mode the social app does not load. I there a way to reset all configurations and DB data back to the "original", or do I need to remove the plugin and manually drop the tables?

[Doc-Saintly]

Also, my initial error was different:
Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'INSERT INTOoc_social_a2_actors(id,id_prim,user_id,preferred_username,name,summary,public_key,private_key,avatar_version,creation) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params ["https:\/\/my.nextcloud.com\/apps\/social\/@user", "id_prim", "user", "user", "", "", "publickey", "privatekey", "0", ""]: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '' for columnnextcloud_db.oc_social_a2_actors.creationat row 1

[ArtificialOwl]

you should not need to drop the tables, upgrade the app to 0.2.1 and enable it.

If you have issues, drop the tables named 'oc_social_a2_*' and enable the app.

[Doc-Saintly]

This results in a server error. Shall I drop all oc_social_* tables and give it another try? I noticed the oc_social_a2_* tables were not re-created after enabling the app.

[ArtificialOwl]

can you provide some log ?

If the tables are not re-created, it means the migration process were successful at the one time.

Run this SQL request:

DELETE FROM oc_migrations WHERE app='social' AND version='0002Date20190506000001';

And try enabling the Social app again (v0.2.1).
Refresh your page on Nextcloud.
If your the migration process is not triggered, and the app is enabled:

 ./occ config:app:set --value '0.1.4' social installed_version

Refresh your page on Nextcloud.

[Doc-Saintly]

After deleting the migration and re-enabling, the tables show up. But it's the same white page issue.

Here's the logs. Perhaps the smbclient is something different, but the only thing I changed today was the Social app

{"reqId":"qLJlxFUPrYfBYTVq0KdH","level":0,"time":"2019-05-31T17:37:27+08:00","remoteAddr":"45.32.35.77","user":"user","app":"no app in context","method":"GET","url":"\/apps\/social\/","message":"No cache entry found for \/appdata_oc34rlv2m70p\/avatar\/user\/avatar.jpg (storage: local::\/var\/ncdata\/, internalPath: appdata_oc34rlv2m70p\/avatar\/user\/avatar.jpg)","userAgent":"Mozilla\/5.0 (Windows NT 10.0; Win64; x64; rv:67.0) Gecko\/20100101 Firefox\/67.0","version":"15.0.8.1"}
{"reqId":"qLJlxFUPrYfBYTVq0KdH","level":0,"time":"2019-05-31T17:37:27+08:00","remoteAddr":"45.32.35.77","user":"user","app":"no app in context","method":"GET","url":"\/apps\/social\/","message":"No cache entry found for \/appdata_oc34rlv2m70p\/theming\/images\/background (storage: local::\/var\/ncdata\/, internalPath: appdata_oc34rlv2m70p\/theming\/images\/background)","userAgent":"Mozilla\/5.0 (Windows NT 10.0; Win64; x64; rv:67.0) Gecko\/20100101 Firefox\/67.0","version":"15.0.8.1"}
{"reqId":"Vxr5B68QqkRQ5mmzok0M","level":0,"time":"2019-05-31T17:37:30+08:00","remoteAddr":"45.32.35.77","user":"user","app":"no app in context","method":"GET","url":"\/avatar\/user\/32?v=0","message":"No cache entry found for \/appdata_oc34rlv2m70p\/avatar\/user\/avatar.jpg (storage: local::\/var\/ncdata\/, internalPath: appdata_oc34rlv2m70p\/avatar\/user\/avatar.jpg)","userAgent":"Mozilla\/5.0 (Windows NT 10.0; Win64; x64; rv:67.0) Gecko\/20100101 Firefox\/67.0","version":"15.0.8.1"}
{"reqId":"77B0inwPA25OdwC4VxaW","level":3,"time":"2019-05-31T17:37:31+08:00","remoteAddr":"45.32.35.77","user":"user","app":"PHP","method":"GET","url":"\/ocs\/v2.php\/apps\/notifications\/api\/v2\/notifications","message":"PHP Startup: Unable to load dynamic library '\/usr\/lib\/php\/20151012\/smbclient.so' - \/usr\/lib\/php\/20151012\/smbclient.so: cannot open shared object file: No such file or directory at Unknown#0","userAgent":"Mozilla\/5.0 (Windows NT 10.0; Win64; x64; rv:67.0) Gecko\/20100101 Firefox\/67.0","version":"15.0.8.1"}

[lachmanfrantisek]

I got another error on update:

Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 
'INSERT INTO "oc_social_a2_follows" ("id", "id_prim", "type", "actor_id", "object_id", "follow_id", "accepted", "creation") VALUES(?, ?, ?, ?, ?, ?, ?, ?)'
["https:\/\my.nextcloud\/ce8TRUNKATEDdf",
 "73e978b2502TRUNKATED2ddc52532b549d492",
 "Follow", "https:\/\/my.nextcloud\/apps\/social\/@franta",
 "https:\/\/mastodon.technology\/users\/fdroidorg",
 "https:\/\/mastodon.technology\/users\/fdroidorg\/followers", "", "2019-02-09 21:04:30"]:

SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type boolean: 

(Using postgres as a database.)

---

edit: seems like the same problem as the newly created issue: #558

[lachmanfrantisek]

Btw, is there some way to go back to the old version after the broken update? It allows me only to enable the 2.1 version...

Thanks!

[Ceralor]

I got another error on update:

Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 
'INSERT INTO "oc_social_a2_follows" ("id", "id_prim", "type", "actor_id", "object_id", "follow_id", "accepted", "creation") VALUES(?, ?, ?, ?, ?, ?, ?, ?)'
["https:\/\my.nextcloud\/ce8TRUNKATEDdf",
 "73e978b2502TRUNKATED2ddc52532b549d492",
 "Follow", "https:\/\/my.nextcloud\/apps\/social\/@franta",
 "https:\/\/mastodon.technology\/users\/fdroidorg",
 "https:\/\/mastodon.technology\/users\/fdroidorg\/followers", "", "2019-02-09 21:04:30"]:

SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type boolean: 

(Using postgres as a database.)

edit: seems like the same problem as the newly created issue: #558

Yeah, this got closed because it was the same error but it's not resolved. Having to manually delete records from the database feels really sketchy.

[lachmanfrantisek]

Yeah, this got closed because it was the same error but it's not resolved. Having to manually delete records from the database feels really sketchy.

I think that this issue is a little bit different.

My problem and the one described in #558 seems more related to postgres compatibility....

[ArtificialOwl]

@lachmanfrantisek exactly. I have updated #558 with procedure to downgrade to 0.1.4.

Thanks for your report, I will do some test on pgsql later tonight.