Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Installation error for Nextcloud 20.0.2 #329

Closed
xgorex opened this issue Dec 28, 2020 · 19 comments
Closed

Installation error for Nextcloud 20.0.2 #329

xgorex opened this issue Dec 28, 2020 · 19 comments

Comments

@xgorex
Copy link

xgorex commented Dec 28, 2020

Steps to reproduce

  1. Standard Nextcloud 20.0.2 installation on Ubuntu 20.04.
  2. Install Phone Sync using server Nextcloud App interface.
  3. Try access the Phone Sync tool as regular user .

Expected behaviour

I have never seen the app running.
I am expecting an empty message view, as there was no sync so far.

Actual behaviour

When accessing the Phone Sync app as a regular user I do see "Internal Server Error".
Logs shows errors, below.

Next as an admin, I disabled and removed the Phone Sync and then was trying to install it again. Now, it was impossible. I have got:
"
An error occured during the request. Unable to proceed.
An exception occurred while executing 'SELECT user_id, datakey, datavalue FROM oc_ocsms_user_datas WHERE datakey LIKE ?' with params ["lastReadDate-%"]: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'nextcloud.oc_ocsms_user_datas' doesn't exist
"

Server configuration

Nextcloud/ownCloud version:
20.0.2
PHP version:
7.4.3
HTTPd server:
apache2 2.4.41-4ubuntu3.1
Database engine & version:
ubuntu package: mysql-common 5.8+1.0.5ubuntu2
mysql --version: mysql Ver 15.1 Distrib 10.3.25-MariaDB

Client configuration

Android version:

Phone:

Nextcloud SMS app version:

Logs

Insert your log here
`
Doctrine\DBAL\Exception\TableNotFoundException: An exception occurred while executing 'SELECT `sms_address` FROM `oc_ocsms_smsdatas` WHERE (`user_id` = ?) AND (`sms_mailbox` IN (?, ?, ?))' with params ["gore", 0, 1, 3]: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'nextcloud.oc_ocsms_smsdatas' doesn't exist

    /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php - line 169:

    Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException()

    /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php - line 149:

    Doctrine\DBAL\DBALException::wrapException()

    /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php - line 914:

    Doctrine\DBAL\DBALException::driverExceptionDuringQuery()

    /var/www/html/nextcloud/lib/private/DB/Connection.php - line 202:

    Doctrine\DBAL\Connection->executeQuery()

    /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php - line 206:

    OC\DB\Connection->executeQuery()

    /var/www/html/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php - line 217:

    Doctrine\DBAL\Query\QueryBuilder->execute()

    /var/www/html/nextcloud/apps/ocsms/db/smsmapper.php - line 94:

    OC\DB\QueryBuilder\QueryBuilder->execute()

    /var/www/html/nextcloud/apps/ocsms/controller/smscontroller.php - line 70:

    OCA\OcSms\Db\SmsMapper->getAllPhoneNumbers()

    /var/www/html/nextcloud/lib/private/AppFramework/Http/Dispatcher.php - line 169:

    OCA\OcSms\Controller\SmsController->index()

    /var/www/html/nextcloud/lib/private/AppFramework/Http/Dispatcher.php - line 100:

    OC\AppFramework\Http\Dispatcher->executeController()

    /var/www/html/nextcloud/lib/private/AppFramework/App.php - line 152:

    OC\AppFramework\Http\Dispatcher->dispatch()

    /var/www/html/nextcloud/lib/private/Route/Router.php - line 308:

    OC\AppFramework\App::main()

    /var/www/html/nextcloud/lib/base.php - line 1008:

    OC\Route\Router->match()

    /var/www/html/nextcloud/index.php - line 37:

    OC::handleRequest()

Caused by Doctrine\DBAL\Driver\PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'nextcloud.oc_ocsms_smsdatas' doesn't exist

    /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php - line 906:

    Doctrine\DBAL\Driver\PDOStatement->execute()

    /var/www/html/nextcloud/lib/private/DB/Connection.php - line 202:

    Doctrine\DBAL\Connection->executeQuery()

    /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php - line 206:

    OC\DB\Connection->executeQuery()

    /var/www/html/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php - line 217:

    Doctrine\DBAL\Query\QueryBuilder->execute()

    /var/www/html/nextcloud/apps/ocsms/db/smsmapper.php - line 94:

    OC\DB\QueryBuilder\QueryBuilder->execute()

    /var/www/html/nextcloud/apps/ocsms/controller/smscontroller.php - line 70:

    OCA\OcSms\Db\SmsMapper->getAllPhoneNumbers()

    /var/www/html/nextcloud/lib/private/AppFramework/Http/Dispatcher.php - line 169:

    OCA\OcSms\Controller\SmsController->index()

    /var/www/html/nextcloud/lib/private/AppFramework/Http/Dispatcher.php - line 100:

    OC\AppFramework\Http\Dispatcher->executeController()

    /var/www/html/nextcloud/lib/private/AppFramework/App.php - line 152:

    OC\AppFramework\Http\Dispatcher->dispatch()

    /var/www/html/nextcloud/lib/private/Route/Router.php - line 308:

    OC\AppFramework\App::main()

    /var/www/html/nextcloud/lib/base.php - line 1008:

    OC\Route\Router->match()

    /var/www/html/nextcloud/index.php - line 37:

    OC::handleRequest()

Caused by PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'nextcloud.oc_ocsms_smsdatas' doesn't exist

    /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php - line 127:

    PDOStatement->execute()

    /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php - line 906:

    Doctrine\DBAL\Driver\PDOStatement->execute()

    /var/www/html/nextcloud/lib/private/DB/Connection.php - line 202:

    Doctrine\DBAL\Connection->executeQuery()

    /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php - line 206:

    OC\DB\Connection->executeQuery()

    /var/www/html/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php - line 217:

    Doctrine\DBAL\Query\QueryBuilder->execute()

    /var/www/html/nextcloud/apps/ocsms/db/smsmapper.php - line 94:

    OC\DB\QueryBuilder\QueryBuilder->execute()

    /var/www/html/nextcloud/apps/ocsms/controller/smscontroller.php - line 70:

    OCA\OcSms\Db\SmsMapper->getAllPhoneNumbers()

    /var/www/html/nextcloud/lib/private/AppFramework/Http/Dispatcher.php - line 169:

    OCA\OcSms\Controller\SmsController->index()

    /var/www/html/nextcloud/lib/private/AppFramework/Http/Dispatcher.php - line 100:

    OC\AppFramework\Http\Dispatcher->executeController()

    /var/www/html/nextcloud/lib/private/AppFramework/App.php - line 152:

    OC\AppFramework\Http\Dispatcher->dispatch()

    /var/www/html/nextcloud/lib/private/Route/Router.php - line 308:

    OC\AppFramework\App::main()

    /var/www/html/nextcloud/lib/base.php - line 1008:

    OC\Route\Router->match()

    /var/www/html/nextcloud/index.php - line 37:

    OC::handleRequest()
`

@troffasky
Copy link

I am having exactly the same symptoms. This looks like it might be responsible for creating the DB:
https://github.com/nextcloud/ocsms/blob/master/lib/migration/Version020109Date20201216203338.php
but apparently I can't just run this manually to fix it :-)

@troffasky
Copy link

I have tricked this into working by saving https://github.com/nextcloud/ocsms/raw/2.0.0/appinfo/database.xml to apps/ocsms/appinfo then enabling the app. This created the database.

@xgorex
Copy link
Author

xgorex commented Jan 2, 2021

[SOLVED] Thank you @troffasky . The database.xml solved this issue. Everything now works perfectly.

@xgorex xgorex closed this as completed Jan 2, 2021
@xgorex
Copy link
Author

xgorex commented Jan 2, 2021

[SOLVED] with database.xml . THX.

@politas
Copy link

politas commented Jan 11, 2021

Just like to point out that that is a workaround, not a solution. I'm getting the same problem with NextCloud 20.0.4 running on Ubuntu 20.0.4

Could we maybe get a change to the installer that verifies the download of that file?

@e-alfred
Copy link
Collaborator

The database.xml file was deprecated and replaced by the new Database Migrations required for newer Nextcloud releases. But Nextcloud also enforces a limit of table and index name lengths which requires shortening of these names. This is no problem for new installations, but older legacy installations need to rename the affected tables. Currently it is not implemented to be done automatically by this app.

  • If you want to install on a fresh system: You can clone this repository and install the app that way until a new release is made
  • If you want to upgrade a legacy system: You have to change the affected table and index names manually for now until a fix is made and a new version is released to the app store.

@politas
Copy link

politas commented Jan 11, 2021

Ah, so this app is broken for installation on current releases on NextCloud due to this issue until a new release of this app is put out? I'm new to the NextCloud infrastructure.

How would I go about installing from a local clone of the repo?

@drdetergent
Copy link

newest release of ocsms 2.2.0 was supposed to "Migrate from database.xml to the new database migration" but somehow I am getting the same error of this thread

so if you download the database.xml file (as proposed here) as workaround it will not work on this specific version 2.2.0 because it was supposed to get rid of this file

one quick and dirty workaround I found was to

  1. download the latest version from the app store of ocsms 2.2.0
  2. enabling will fail and raise the known error
  3. download the ocsms version 2.1.9 by using command
    curl -L https://github.com/nextcloud/ocsms/releases/download/2.1.9/ocsms-2.1.9.tar.gz --output ocsms-2.1.9.tar.gz
  4. extract downloaded file with
    tar -xf ocsms-2.1.9.tar.gz
  5. copy and replace the 2.2.0 version with command
    cp -r ocsms/ /var/www/nextcloud/apps/
  6. enable now the app and it will work

I have not tried to update to latest version 2.2.0 at this stage, anyone brave to test it and report here?

@drdetergent
Copy link

I have not tried to update to latest version 2.2.0 at this stage, anyone brave to test it and report here?

Update done to 2.2.0 and everything works fine after the update.

I confirm the workaround above to first install 2.1.9 and then upgrade to 2.2.0 is acceptable.

@TobjasR
Copy link

TobjasR commented Mar 25, 2021

thank you @drdetergent !

@david-a-hillman
Copy link

Thanks @drdetergent but unfortunately, this workaround does not always work around.

I just followed your procedure on a fresh install, but Nextcloud insisted on upgrading the app afterwards, and then pronounced it incompatible. Screenshots follow.

update-required

incompatible

@drdetergent
Copy link

I just followed your procedure on a fresh install, but Nextcloud insisted on upgrading the app afterwards, and then pronounced it incompatible. Screenshots follow.

That is interesting, but from what I see you are upgrading to NextCloud 21 and ocsms blocks the upgrade because it is not officially compatible with the target version 21.

Try this:

  • Nextcloud version 20> Uninstall ocsms (or take a backup copy if you want to save old synchronized sms)
  • Nextcloud version 20> Launch the update to Nextcloud 21
  • Nextcloud version 21> follow the workaround from step 1 to 6

Good luck and keep us updated, we will help you : )

@david-a-hillman
Copy link

As a matter of fact, I was not doing an upgrade. I have no explanation for why Nextcloud did what it appeared to do here. This is all occurring on a fresh install of NC 21.

Thanks.

@drdetergent
Copy link

drdetergent commented Apr 10, 2021

I see the most recent update of Nextcloud is 21.0.1, try to

  • disable (not remove) Phone Sync (ocsms) from the installed apps
  • upgrade to Nextcloud 21.0.1
  • once completed the upgrade, enable Phone Sync app

@drdetergent
Copy link

confirmed that there is no version of ocsms available for nextcloud 21 so everything is fine (unfortunately), no way to use it with your version
see here

@aarakh
Copy link

aarakh commented Jan 5, 2022

For the sake of posterity: I followed below steps in latest 22.3.0 version:

  1. downloaded and installed app version 1.13.1 from https://github.com/nextcloud/ocsms/releases/download/1.13.1/ocsms-1.13.1.tar.gz

  2. Prepare nextcloud mysql DB for first installation
    Tables:

    use nextcloud
    create table oc_ocsms_smsdatas(id int(10) auto_increment primary key,user_id varchar(64) not null, added timestamp not null default 0,lastmodified timestamp not null default 0, sms_id int(5) not null,sms_address varchar(64) not null,sms_msg mediumtext not null,sms_date BIGINT not null,sms_flags varchar(2) not null default '00',sms_mailbox int(1) not null,sms_type int(1) not null);
    create table oc_ocsms_sendmessage_queue(id int(10) auto_increment primary key,user_id varchar(64) not null,sms_address varchar(64) not null,sms_msg mediumtext not null);
    create table oc_ocsms_config(user varchar(255) not null,key varchar(255) not null,value varchar(255) not null);
    create table oc_ocsms_user_datas(user_id varchar(64) not null,datakey varchar(64) not null,datavalue varchar(64) not null);
    create table oc_ocsms_conversation_read_states(user_id varchar(64) not null,phone_number varchar(64) not null,int_date int(32) not null);

    Indexes:

    create index user_datas_user_datakey on oc_ocsms_user_datas(user_id,datakey);
    create index smsdata_user_mailbox on oc_ocsms_smsdatas(user_id,sms_mailbox);
    create index smsdata_user_mailbox_date on oc_ocsms_smsdatas(user_id,sms_mailbox,sms_date);
    create index smsdata_user_mailbox_address on oc_ocsms_smsdatas(user_id,sms_mailbox,sms_address);
    create index smsdata_user_mailbox_address_date on oc_ocsms_smsdatas(user_id,sms_mailbox,sms_address,sms_date);
    create index config_user_key on oc_ocsms_config(user,key);
    create index smsdata_user_smsid on oc_ocsms_smsdatas(user_id,sms_id);
    create index sms_conversation_rs_pkey on oc_ocsms_conversation_read_states(user_id,phone_number);

  3. Start ocsms app from WebUI

  4. Followed workaround steps given in Installation error for Nextcloud 20.0.2 #329 (comment)
    a. download the latest version from the app store of ocsms 2.2.0
    b. enabling will fail and raise the known error
    c. download the ocsms version 2.1.9 by using command
    curl -L https://github.com/nextcloud/ocsms/releases/download/2.1.9/ocsms-2.1.9.tar.gz --output ocsms-2.1.9.tar.gz
    d. extract downloaded file with
    tar -xf ocsms-2.1.9.tar.gz
    e.copy and replace the 2.2.0 version with command
    cp -r ocsms/ /var/www/nextcloud/apps/
    f. enable now the app and it will work

  5. Nextcloud will prompt for app update, go-ahead and do update

  6. Start ocsms app from WebUI again even though Nextcloud shows incompatible, still it works for latest Nextcloud 22.2.3.0

  7. in order to work ocsms android App, for now, I had followed steps in
    a. Downloaded latest source from https://gitlab.com/cpu20/ownCloud-SMS-App-Fork, gradle built it, depending upon Android Version modified "Project Structure" in Android Studio to suite app for Android10/11/12.
    b. Also merged changes suggested as Certificate signed by unknow authority nerzhul/ncsms-android#184 (comment)
    c. Able to build apk successfully, and tested on Android10/11/12.
    d. In case of TOTP, generate password for ocsms app using https://docs.nextcloud.com/server/stable/user_manual/en/session_management.html#managing-devices

p.s. - I apologize to add comments on this already closed issue, however if these steps helpeds some-one, it worth my time spent :)

@erdoukki
Copy link

erdoukki commented Apr 14, 2022

Thanks all !

Great and working (today) with NextCloud 23.0.3...

Here some quick notes of what I have done on my docker install of NextCloud to enable and use OCSMS 2.2.0 !

from NC root folder of NextCloud container
root@STARGATE:/srv/NC# docker-compose exec db mysql -u root -p nextcloud

MYSQL DATABASES

use nextcloud
create table oc_ocsms_smsdatas(id int(10) auto_increment primary key,user_id varchar(64) not null, added timestamp not null default 0,lastmodified timestamp not null default 0, sms_id int(5) not null,sms_address varchar(64) not null,sms_msg mediumtext not null,sms_date BIGINT not null,sms_flags varchar(2) not null default '00',sms_mailbox int(1) not null,sms_type int(1) not null);
create table oc_ocsms_sendmessage_queue(id int(10) auto_increment primary key,user_id varchar(64) not null,sms_address varchar(64) not null,sms_msg mediumtext not null);
create table oc_ocsms_config(`user` varchar(255) not null,`key` varchar(255) not null,`value` varchar(255) not null);
create table oc_ocsms_user_datas(user_id varchar(64) not null,datakey varchar(64) not null,datavalue varchar(64) not null);
create table oc_ocsms_conversation_read_states(user_id varchar(64) not null,phone_number varchar(64) not null,int_date int(32) not null);

MYSQL INDEXES

create index user_datas_user_datakey on oc_ocsms_user_datas(user_id,datakey);
create index smsdata_user_mailbox on oc_ocsms_smsdatas(user_id,sms_mailbox);
create index smsdata_user_mailbox_date on oc_ocsms_smsdatas(user_id,sms_mailbox,sms_date);
create index smsdata_user_mailbox_address on oc_ocsms_smsdatas(user_id,sms_mailbox,sms_address);
create index smsdata_user_mailbox_address_date on oc_ocsms_smsdatas(user_id,sms_mailbox,sms_address,sms_date);
create index config_user_key on oc_ocsms_config(`user`,`key`);
create index smsdata_user_smsid on oc_ocsms_smsdatas(user_id,sms_id);
create index sms_conversation_rs_pkey on oc_ocsms_conversation_read_states(user_id,phone_number);

INSTALL OCSMS APP

cd /srv/NC/nextcloud/custom_apps/

curl -L https://github.com/nextcloud/ocsms/releases/download/2.1.9/ocsms-2.1.9.tar.gz --output ocsms-2.1.9.tar.gz

tar -zxf ocsms-2.1.9.tar.gz

chown 33:33 ocsms -R

ACTIVATE OCSMS APP

cd /srv/NC/

docker exec -it nextcloud occ app:enable ocsms

INSTALL OCSMS APP


cd /srv/NC/nextcloud/custom_apps/

curl -L https://github.com/nextcloud/ocsms/releases/download/2.2.0/ocsms-2.2.0.tar.gz --output ocsms-2.2.0.tar.gz

tar -zxf ocsms-2.2.0.tar.gz

PATCH OCSMS APP from #340


wget https://raw.githubusercontent.com/nextcloud/ocsms/f2070b29443c5a62d8a2ebf9d4ce26fc66d757f9/lib/Migration/Version020300Date20210926000100.php

cp Version020300Date20210926000100.php ocsms/lib/Migration/

chown 33:33 ocsms -R

NAVIGATE TO NEXTCLOUD/SMS

INSTALL APK from FDROID with disabling APK INCOMPATIBILITY mode (from nerzhul/ncsms-android#232 (comment))

Add a ocsms account

Try to synchronise and accept necessary privileges

ENJOY...

EDITED:
TAKE CARE WITH COPY/PASTE FROM (here):
the lost of ` chars may issue some errors:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'varchar(255) not null,value varchar(255) not null)' at line 1

Doctrine\DBAL\Exception\TableNotFoundException: An exception occurred while executing a query: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'nextcloud.oc_ocsms_config' doesn't exist

These are correct!

create table oc_ocsms_config(`user` varchar(255) not null,`key` varchar(255) not null,`value` varchar(255) not null);
create index config_user_key on oc_ocsms_config(`user`,`key`);

ADDED a topic to here : https://help.nextcloud.com/t/nc-23-android-11/137488

@LeifAndersen
Copy link

Would it be possible for you to translate the mysql commands to postgresql?

(The only difference I see is that the datatypes need to be spelled in all caps, but I'm not super familiar with sql at this level, hence why I'm asking.)

Either way, thanks for putting all this together. :)

@raiemaille
Copy link

Hi,
Thanks for this app.

Here are the changes I made for PostgreSQL (13) on my setup : Nextcloud 24.0.2, ocsms 2.2.0

I removed default value on timestamp fields, I can't say if it's a good idea or not.

PostgreSQL DATABASES

create table oc_ocsms_smsdatas(id serial primary key, user_id varchar(64) not null, added timestamp not null, lastmodified timestamp not null, sms_id int not null, sms_address varchar(64) not null, sms_msg text not null, sms_date BIGINT not null, sms_flags varchar(2) not null default '00', sms_mailbox int not null, sms_type int not null);
create table oc_ocsms_sendmessage_queue(id serial primary key, user_id varchar(64) not null, sms_address varchar(64) not null, sms_msg text not null);
create table oc_ocsms_config("user" varchar(255) not null, "key" varchar(255) not null, "value" varchar(255) not null);
create table oc_ocsms_user_datas(user_id varchar(64) not null, datakey varchar(64) not null, datavalue varchar(64) not null);
create table oc_ocsms_conversation_read_states(user_id varchar(64) not null, phone_number varchar(64) not null, int_date int not null);

PostgreSQL INDEXES

create index user_datas_user_datakey on oc_ocsms_user_datas(user_id, datakey);
create index smsdata_user_mailbox on oc_ocsms_smsdatas(user_id, sms_mailbox);
create index smsdata_user_mailbox_date on oc_ocsms_smsdatas(user_id, sms_mailbox, sms_date);
create index smsdata_user_mailbox_address on oc_ocsms_smsdatas(user_id, sms_mailbox, sms_address);
create index smsdata_user_mailbox_address_date on oc_ocsms_smsdatas(user_id, sms_mailbox, sms_address, sms_date);
create index config_user_key on oc_ocsms_config("user", "key");
create index smsdata_user_smsid on oc_ocsms_smsdatas(user_id, sms_id);
create index sms_conversation_rs_pkey on oc_ocsms_conversation_read_states(user_id, phone_number);

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests