Installation error for Nextcloud 20.0.2

xgorex commented 3 years ago

Steps to reproduce

Standard Nextcloud 20.0.2 installation on Ubuntu 20.04.
Install Phone Sync using server Nextcloud App interface.
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 commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

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

xgorex commented 3 years ago

[SOLVED] with database.xml . THX.

politas commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

thank you @drdetergent !

david-a-hillman commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

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 commented 2 years ago

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

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
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);
Start ocsms app from WebUI
Followed workaround steps given in https://github.com/nextcloud/ocsms/issues/329#issuecomment-776354545 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
Nextcloud will prompt for app update, go-ahead and do update
Start ocsms app from WebUI again even though Nextcloud shows incompatible, still it works for latest Nextcloud 22.2.3.0
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 https://github.com/nerzhul/ncsms-android/issues/184#issuecomment-457127596 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 commented 2 years ago

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 https://github.com/nextcloud/ocsms/pull/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 https://github.com/nerzhul/ncsms-android/issues/232#issuecomment-643650754)

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 commented 2 years ago

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 commented 2 years ago

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

nextcloud / ocsms