MySql 8.x rcmcarddav

[CyrilMM]

Hello,
and already a big thank you for your distributions and your work!
I encounter a problem with RoundCubeMail CardDAV on MySql 8.x database the webmail crashes with the error message impossible connection to the database by activating the plugin
on previous version of MySql it works without worry.
Do you have an idea of your problem and possibly a solution for me?
Thank you

[mstilkerich]

Hello,

MySQL 8 should work fine, it is also included in the test environment. Rcmcarddav uses roundcube's database interface to access the database, so I have no idea why it would work for roundcube itself, but not for rcmccarddav. Since the error message "impossible connection" doesn't tell very much, can you check the files in the logs/ folder inside roundcube root for a more specific error message? Also, in which situation do you encounter the error?

[CyrilMM]

Hello and thank you for your feedback, here is the log file: From: Michael Stilkerich Sent: Friday, March 18, 2022 11:39 AM To: mstilkerich/rcmcarddav Cc: CyrilMM ; Author Subject: Re: [mstilkerich/rcmcarddav] MySql 8.x rcmcarddav (Discussion #379) Hello, MySQL 8 should work fine, it is also included in the test environment. Rcmcarddav uses roundcube's database interface to access the database, so I have no idea why it would work for roundcube itself, but not for rcmccarddav. Since the error message "impossible connection" doesn't tell very much, can you check the files in the logs/ folder inside roundcube root for a more specific error message? Also, in which situation do you encounter the error? — Reply to this email directly, view it on GitHub, or unsubscribe. Triage notifications on the go with GitHub Mobile for iOS or Android. You are receiving this because you authored the thread.Message ID: ***@***.***>

[mstilkerich]

I don’t see any logs :-)

[CyrilMM]

Sorry : [18-Mar-2022 16:39:29 +0100]: <3s7h2ej6> [5 ERR] Database::get (SELECT `filename` FROM carddav_migrations) ERROR: [1146] Table 'round.carddav_migrations' doesn't exist [18-Mar-2022 16:39:29 +0100]: <3s7h2ej6> [5 ERR] Migration query (-- table to store the configured address books CREATE TABLE IF NOT EXISTS carddav_addressbooks ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(64) NOT NULL, username VARCHAR(64) NOT NULL, password VARCHAR(255) NOT NULL, url VARCHAR(255) NOT NULL, active TINYINT UNSIGNED NOT NULL DEFAULT 1, user_id INT(10) UNSIGNED NOT NULL, last_updated TIMESTAMP NOT NULL DEFAULT '2000-01-01 00:00:01', -- time stamp of the last update of the local database refresh_time TIME NOT NULL DEFAULT '01:00:00', -- time span after that the local database will be refreshed, default 1h sync_token VARCHAR(255) NOT NULL DEFAULT '', -- sync-token the server sent us for the last sync authentication_scheme VARCHAR(64) NOT NULL DEFAULT "auto", -- the HTTP authentication scheme to use, auto will be overwritten presetname VARCHAR(64), -- presetname PRIMARY KEY(id), FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE ) ROW_FORMAT=DYNAMIC ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;) failed: [1824] Failed to open the referenced table 'users' [18-Mar-2022 16:39:29 +0100]: <3s7h2ej6> [5 ERR] Database::get (SELECT * FROM carddav_addressbooks WHERE ((`user_id` = '80'))) ERROR: [1146] Table 'round.carddav_addressbooks' doesn't exist [18-Mar-2022 16:39:29 +0100]: <3s7h2ej6> [5 ERR] Error initializing preconfigured addressbooks: [1146] Table 'round.carddav_addressbooks' doesn't exist [18-Mar-2022 16:39:29 +0100]: <3s7h2ej6> [5 ERR] Database::get (SELECT * FROM carddav_addressbooks WHERE ((`user_id` = '80'))) ERROR: [1146] Table 'round.carddav_addressbooks' doesn't exist [18-Mar-2022 16:39:29 +0100]: <3s7h2ej6> [5 ERR] Could not init rcmcarddav: [1146] Table 'round.carddav_addressbooks' doesn't exist From: Michael Stilkerich Sent: Friday, March 18, 2022 6:27 PM To: mstilkerich/rcmcarddav Cc: CyrilMM ; Author Subject: Re: [mstilkerich/rcmcarddav] MySql 8.x rcmcarddav (Discussion #379) I don’t see any logs :-) — Reply to this email directly, view it on GitHub, or unsubscribe. Triage notifications on the go with GitHub Mobile for iOS or Android. You are receiving this because you authored the thread.Message ID: ***@***.***>

[mstilkerich]

hmm you are using a table prefix, and for some reason the prefix is substituted for an empty string in the queries. I’ll check this myself on my test system

[CyrilMM]

I don't use table prefix for information look : Piece jointe picture attachment From: Michael Stilkerich Sent: Friday, March 18, 2022 7:36 PM To: mstilkerich/rcmcarddav Cc: CyrilMM ; Author Subject: Re: [mstilkerich/rcmcarddav] MySql 8.x rcmcarddav (Discussion #379) hmm you are using a table prefix, and for some reason the prefix is substituted for an empty string in the queries. I’ll check this myself on my test system — Reply to this email directly, view it on GitHub, or unsubscribe. Triage notifications on the go with GitHub Mobile for iOS or Android. You are receiving this because you authored the thread.Message ID: ***@***.***>

[mstilkerich]

the file attachments don’t seem to work when you respond by email. Anyway, looking at the logs roundcube uses the table prefix “round” for the tables. Can you please double check your db_prefix option? On the other hand, rcmcarddav thinks the prefix is empty, which confuses me.

[CyrilMM]

I confirm that my dbpreffix is empty moreover when I switch to MySql <8 your plugin works directly as soon as I switch to 8.x it blocks ALL roundcube webmail From: Michael Stilkerich Sent: Friday, March 18, 2022 7:51 PM To: mstilkerich/rcmcarddav Cc: CyrilMM ; Author Subject: Re: [mstilkerich/rcmcarddav] MySql 8.x rcmcarddav (Discussion #379) the file attachments don’t seem to work when you respond by email. Anyway, looking at the logs roundcube uses the table prefix “round” for the tables. Can you please double check your db_prefix option? On the other hand, rcmcarddav thinks the prefix is empty, which confuses me. — Reply to this email directly, view it on GitHub, or unsubscribe. Triage notifications on the go with GitHub Mobile for iOS or Android. You are receiving this because you authored the thread.Message ID: ***@***.***>

[CyrilMM]

cache Parcourir Structure Rechercher Insérer Vider Supprimer 0 MyISAM utf8_unicode_ci 2,0 kio - cache_index Parcourir Structure Rechercher Insérer Vider Supprimer 0 MyISAM utf8_unicode_ci 4,0 kio - cache_messages Parcourir Structure Rechercher Insérer Vider Supprimer 0 MyISAM utf8_unicode_ci 4,0 kio - cache_shared Parcourir Structure Rechercher Insérer Vider Supprimer 0 MyISAM utf8_unicode_ci 4,0 kio - cache_thread Parcourir Structure Rechercher Insérer Vider Supprimer 0 MyISAM utf8_unicode_ci 4,0 kio - collected_addresses Parcourir Structure Rechercher Insérer Vider Supprimer 0 MyISAM utf8_unicode_ci 4,0 kio - contactgroupmembers Parcourir Structure Rechercher Insérer Vider Supprimer 0 MyISAM utf8_general_ci 1,0 kio - contactgroups Parcourir Structure Rechercher Insérer Vider Supprimer 0 MyISAM utf8_unicode_ci 1,0 kio - contacts Parcourir Structure Rechercher Insérer Vider Supprimer 0 MyISAM utf8_unicode_ci 1,0 kio - dictionary Parcourir Structure Rechercher Insérer Vider Supprimer 0 MyISAM utf8_unicode_ci 1,0 kio - filestore Parcourir Structure Rechercher Insérer Vider Supprimer 0 MyISAM utf8_unicode_ci 2,0 kio - identities Parcourir Structure Rechercher Insérer Vider Supprimer 0 MyISAM utf8_unicode_ci 2,0 kio - searches Parcourir Structure Rechercher Insérer Vider Supprimer 0 MyISAM utf8_unicode_ci 2,0 kio - session Parcourir Structure Rechercher Insérer Vider Supprimer 0 MyISAM utf8_unicode_ci 2,0 kio - system Parcourir Structure Rechercher Insérer Vider Supprimer 1 MyISAM utf8_unicode_ci 2,0 kio - users My structure dans PhpMyadmin From: Michael Stilkerich Sent: Friday, March 18, 2022 7:51 PM To: mstilkerich/rcmcarddav Cc: CyrilMM ; Author Subject: Re: [mstilkerich/rcmcarddav] MySql 8.x rcmcarddav (Discussion #379) the file attachments don’t seem to work when you respond by email. Anyway, looking at the logs roundcube uses the table prefix “round” for the tables. Can you please double check your db_prefix option? On the other hand, rcmcarddav thinks the prefix is empty, which confuses me. — Reply to this email directly, view it on GitHub, or unsubscribe. Triage notifications on the go with GitHub Mobile for iOS or Android. You are receiving this because you authored the thread.Message ID: ***@***.***>

[mstilkerich]

Right, I misinterpreted the “round.” as a prefix but it is actually the database name. I see that your roundcube tables use the MyIsam storage engine. Rcmcarddav uses innodb, and ai believe there can be no foreign key constraints between the two which might be why rcmcarddav fails to create the carddav_addressbooks table. The error message is a bit confusing as it says there is no table users, but your table list contains that table, albeit with MyIsam engine. You could try converting those tables to innodb. Roundcube also has been using innodb for as long as I can think, so I wonder how you ended up with MyIsam. Anyway, converting those tables to innodb might be worth a try. Did you always properly upgrade roundcube using the install script that takes care of database schema upgrades?

[CyrilMM]

in fact I'm from the old school;) I've always used MyIsam innodb having arrived later on MySql, what I like about MyIsam is the simplicity of saving tables as simple files whereas InnoDB you have to restructure the MySql ibdata1 file for table migration if you don't want to bother doing DumpSql on large tables. In fact I will test if on your side you have a simple solution to save Innodb tables automatically I am interested. Anyway, thank you for your feedback and I'll keep you posted. From: Michael Stilkerich Sent: Friday, March 18, 2022 10:48 PM To: mstilkerich/rcmcarddav Cc: CyrilMM ; Author Subject: Re: [mstilkerich/rcmcarddav] MySql 8.x rcmcarddav (Discussion #379) Right, I misinterpreted the “round.” as a prefix but it is actually the database name. I see that your roundcube tables use the MyIsam storage engine. Rcmcarddav uses innodb, and ai believe there can be no foreign key constraints between the two which might be why rcmcarddav fails to create the carddav_addressbooks table. The error message is a bit confusing as it says there is no table users, but your table list contains that table, albeit with MyIsam engine. You could try converting those tables to innodb. Roundcube also has been using innodb for as long as I can think, so I wonder how you ended up with MyIsam. Anyway, converting those tables to innodb might be worth a try. Did you always properly upgrade roundcube using the install script that takes care of database schema upgrades? — Reply to this email directly, view it on GitHub, or unsubscribe. Triage notifications on the go with GitHub Mobile for iOS or Android. You are receiving this because you authored the thread.Message ID: ***@***.***>

[CyrilMM]

Hello, I just changed the chema and that's the problem, it works there. Thank you ! From: Michael Stilkerich Sent: Friday, March 18, 2022 10:48 PM To: mstilkerich/rcmcarddav Cc: CyrilMM ; Author Subject: Re: [mstilkerich/rcmcarddav] MySql 8.x rcmcarddav (Discussion #379) Right, I misinterpreted the “round.” as a prefix but it is actually the database name. I see that your roundcube tables use the MyIsam storage engine. Rcmcarddav uses innodb, and ai believe there can be no foreign key constraints between the two which might be why rcmcarddav fails to create the carddav_addressbooks table. The error message is a bit confusing as it says there is no table users, but your table list contains that table, albeit with MyIsam engine. You could try converting those tables to innodb. Roundcube also has been using innodb for as long as I can think, so I wonder how you ended up with MyIsam. Anyway, converting those tables to innodb might be worth a try. Did you always properly upgrade roundcube using the install script that takes care of database schema upgrades? — Reply to this email directly, view it on GitHub, or unsubscribe. Triage notifications on the go with GitHub Mobile for iOS or Android. You are receiving this because you authored the thread.Message ID: ***@***.***>

[CyrilMM]

Hello, I come back to you for a concern, already to start, we agree that your plugin and a CardDav client? in which case impossible to make it work with NexCloud or Synology which have CardDav servers I have the following error message: contacts: No address book found While on another CardDav client; Android and Apple everything works fine. Do you know where this problem comes from? Thanks again Cyril From: Michael Stilkerich Sent: Friday, March 18, 2022 10:48 PM To: mstilkerich/rcmcarddav Cc: CyrilMM ; Author Subject: Re: [mstilkerich/rcmcarddav] MySql 8.x rcmcarddav (Discussion #379) Right, I misinterpreted the “round.” as a prefix but it is actually the database name. I see that your roundcube tables use the MyIsam storage engine. Rcmcarddav uses innodb, and ai believe there can be no foreign key constraints between the two which might be why rcmcarddav fails to create the carddav_addressbooks table. The error message is a bit confusing as it says there is no table users, but your table list contains that table, albeit with MyIsam engine. You could try converting those tables to innodb. Roundcube also has been using innodb for as long as I can think, so I wonder how you ended up with MyIsam. Anyway, converting those tables to innodb might be worth a try. Did you always properly upgrade roundcube using the install script that takes care of database schema upgrades? — Reply to this email directly, view it on GitHub, or unsubscribe. Triage notifications on the go with GitHub Mobile for iOS or Android. You are receiving this because you authored the thread.Message ID: ***@***.***>

[mstilkerich]

Hello, can you show the log messages please?

[CyrilMM]

Hello, [20-Mar-2022 08:50:45 +0300]: <dfsnalb4> [1 DBG] internalGet query: SELECT * FROM carddav_addressbooks WHERE ((`user_id` = '108')) [20-Mar-2022 08:50:45 +0300]: <dfsnalb4> [1 DBG] carddav::savePreferences [20-Mar-2022 08:50:45 +0300]: <dfsnalb4> [1 DBG] Starting discovery with input https://nextcloud.alphablend.fr/remote.php/dav/addressbooks/users/Celine/contacts/ [20-Mar-2022 08:50:45 +0300]: <dfsnalb4> [1 DBG] Try context path /.well-known/carddav [20-Mar-2022 08:50:45 +0300]: <dfsnalb4> [2 NFO] Exception while querying current-user-principal: cURL error 60: SSL certificate problem: unable to get local issuer certificate (see https://curl.haxx.se/libcurl/c/libcurl-errors.html) [20-Mar-2022 08:50:45 +0300]: <dfsnalb4> [1 DBG] Try context path / [20-Mar-2022 08:50:45 +0300]: <dfsnalb4> [2 NFO] Exception while querying current-user-principal: cURL error 60: SSL certificate problem: unable to get local issuer certificate (see https://curl.haxx.se/libcurl/c/libcurl-errors.html) [20-Mar-2022 08:50:45 +0300]: <dfsnalb4> [1 DBG] Try context path /co [20-Mar-2022 08:50:45 +0300]: <dfsnalb4> [2 NFO] Exception while querying current-user-principal: cURL error 60: SSL certificate problem: unable to get local issuer certificate (see https://curl.haxx.se/libcurl/c/libcurl-errors.html) [20-Mar-2022 08:50:45 +0300]: <dfsnalb4> [1 DBG] Try context path /remote.php/dav/addressbooks/users/Celine/contacts/ [20-Mar-2022 08:50:46 +0300]: <dfsnalb4> [2 NFO] Exception while querying current-user-principal: cURL error 60: SSL certificate problem: unable to get local issuer certificate (see https://curl.haxx.se/libcurl/c/libcurl-errors.html) [20-Mar-2022 08:50:46 +0300]: <dfsnalb4> [1 DBG] carddav::addPreferencesSection [20-Mar-2022 08:50:46 +0300]: <dfsnalb4> [1 DBG] carddav::buildPreferencesPage Thanks Cyril From: Michael Stilkerich Sent: Sunday, March 20, 2022 6:24 AM To: mstilkerich/rcmcarddav Cc: CyrilMM ; Author Subject: Re: [mstilkerich/rcmcarddav] MySql 8.x rcmcarddav (Discussion #379) Hello, can you show the log messages please? — Reply to this email directly, view it on GitHub, or unsubscribe. Triage notifications on the go with GitHub Mobile for iOS or Android. You are receiving this because you authored the thread.Message ID: ***@***.***>

[mstilkerich]

Hello,
the logs show that the SSL certificate provided by the server is not signed by a certification authority that is trusted by the client. If you want to use HTTPS, your server needs to show a certificate that is trusted by the client. It appears you use letsencrypt, so your certificate looks good. Therefore, I would check if the client (the web server running roundcube) has the root certificates of lets encrypt in its certificate store. On most linux systems, you would have to install a package, e.g. ca-certificates on Ubuntu.

[CyrilMM]

Hello, did you receive my error logs this morning? Thank you. Cyril From: Michael Stilkerich Sent: Sunday, March 20, 2022 6:24 AM To: mstilkerich/rcmcarddav Cc: CyrilMM ; Author Subject: Re: [mstilkerich/rcmcarddav] MySql 8.x rcmcarddav (Discussion #379) Hello, can you show the log messages please? — Reply to this email directly, view it on GitHub, or unsubscribe. Triage notifications on the go with GitHub Mobile for iOS or Android. You are receiving this because you authored the thread.Message ID: ***@***.***>

[CyrilMM]

Good evening ;) that was the worry bravo and thank you again! now everything is working. I'm still abusing a little, do you know a way to save MySql tables in innodb (.frm) to easily remount them in case of concern that I can no longer save them as I did with the MyIsam schemas in simple copy if you have a tip I'm a taker? Cyril From: Michael Stilkerich Sent: Sunday, March 20, 2022 8:15 PM To: mstilkerich/rcmcarddav Cc: CyrilMM ; Author Subject: Re: [mstilkerich/rcmcarddav] MySql 8.x rcmcarddav (Discussion #379) Hello, the logs show that the SSL certificate provided by the server is not signed by a certification authority that is trusted by the client. If you want to use HTTPS, your server needs to show a certificate that is trusted by the client. It appears you use letsencrypt, so your certificate looks good. Therefore, I would check if the client (the web server running roundcube) has the root certificates of lets encrypt in its certificate store. On most linux systems, you would have to install a package, e.g. ca-certificates on Ubuntu. — Reply to this email directly, view it on GitHub, or unsubscribe. Triage notifications on the go with GitHub Mobile for iOS or Android. You are receiving this because you authored the thread.Message ID: ***@***.***>