July 2022 - discuss - lists.mariadb.org

[Maria-discuss] Repos empty?
by William Edwards 15 Aug '22

15 Aug '22

Hello, The Debian repos @ https://mariadb.org/download/?t=repo-config don't seem to contain any packages and no Release file anymore. Last updated yesterday at 18:41 (IDK which timezone). E.g. http://ams2.mirrors.digitalocean.com/mariadb/repo/10.3/debian/dists/buster/… (the issue isn't with this specific mirror). When choosing a repo on mariadb.org, the error "An error occured. The mirrors could not be loaded." is shown. -- With kind regards, William Edwards

2 4

[Maria-discuss] Major upgrade instructions missing for Galera
by William Edwards 10 Aug '22

10 Aug '22

Hi, The documentation at https://mariadb.com/kb/en/upgrading-galera-cluster/ is missing major upgrade instructions > MariaDB 10.4. Someone else caught this as well: https://mariadb.com/kb/en/upgrading-from-104-to-105-galera-changes/ Is this intentional? -- With kind regards, William Edwards

3 6

[Maria-discuss] MariaDB server horribly slow on start
by Cédric Counotte 01 Aug '22

01 Aug '22

Hello all. I hope I'm at the right place to ask this question. I opened a bug here: https://jira.mariadb.org/browse/MDEV-28969, however I was told to use this mailing list. We have 4 MariaDB servers in a Galera Cluster and it happens that a server has to be restarted (be it for a crash which I have to open a bug for) or maintenance. When that happens, the restarted server is causing huge slow down on the whole cluster, and it lasts for 10 to 30 minutes at the very least! And by huge, I mean huge, we end up with 500 to 800 pending queries on all servers as you can see on attached screenshots I've attached the configuration of any server for reference in case this is the source of the issue. Any way to solve this would be greatly appreciated. Regards, 3C.

8 45

[Maria-discuss] Looking for advice where to start looking at a slow query case
by ngtech1ltd＠gmail.com 26 Jul '22

26 Jul '22

Hey, I have tried to run a local service which claims to be a YouTube alike which is named AVideo(used to be PHPTube). I loaded the service with 220k+ videos from YouTube and have used MariaDB as the DB backend. The service moved slowly as the size of the videos DB growed from 100k to 150k. I got a recommendation from the developer to use MySQL which didn’t made any sense to me. I tracked the issue to the SQL queries that the service runs. Currently I am upgrading my local servers so the DB cannot be downloaded for testing however I have seen a very interesting thing. I will first put the query here: ```sql SELECT u.*, v.*, c.iconClass, c.name as category, c.clean_name as clean_category,c.description as category_description, v.created as videoCreation, v.modified as videoModified FROM videos as v LEFT JOIN categories c ON categories_id = c.id LEFT JOIN users u ON v.users_id = u.id WHERE 2=2 AND u.status = 'a' AND v.status IN ('a','k','f') ORDER BY likes DESC LIMIT 36, 12; ``` The ANALYZE FORMAT=JSON for that query is: ```json { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 9643.807074, "filesort": { "sort_key": "v.likes desc", "r_loops": 1, "r_total_time_ms": 756.223544, "r_limit": 48, "r_used_priority_queue": true, "r_output_rows": 49, "r_sort_mode": "sort_key,rowid", "temporary_table": { "table": { "table_name": "u", "access_type": "ref", "possible_keys": ["PRIMARY", "users_status_IDX"], "key": "users_status_IDX", "key_length": "1", "used_key_parts": ["status"], "ref": ["const"], "r_loops": 1, "rows": 1, "r_rows": 1, "r_table_time_ms": 0.034852389, "r_other_time_ms": 0.020715696, "filtered": 100, "r_filtered": 100, "index_condition": "u.`status` = 'a'" }, "table": { "table_name": "v", "access_type": "ref", "possible_keys": [ "fk_videos_users_idx", "video_status_idx", "videos_status_index" ], "key": "fk_videos_users_idx", "key_length": "4", "used_key_parts": ["users_id"], "ref": ["AVideo.u.id"], "r_loops": 1, "rows": 391299, "r_rows": 990067, "r_table_time_ms": 2691.791742, "r_other_time_ms": 6129.887127, "filtered": 50.00025558, "r_filtered": 100, "attached_condition": "v.`status` in ('a','k','f')" }, "table": { "table_name": "c", "access_type": "eq_ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["id"], "ref": ["AVideo.v.categories_id"], "r_loops": 990067, "rows": 1, "r_rows": 1, "r_table_time_ms": 0.00546745, "r_other_time_ms": 0.569425897, "filtered": 100, "r_filtered": 100 } } } } } ``` While for the next: ```sql SELECT * FROM (SELECT u.id as uid , u.user as uuser , u.name as uname , u.email as uemail , u.password as upassword , u.created as ucreated , u.modified as umodified , u.isAdmin as uisAdmin , u.status as ustatus , u.photoURL as uphotoURL , u.lastLogin as ulastLogin , u.recoverPass as urecoverPass , u.backgroundURL as ubackgroundURL , u.canStream as ucanStream , u.canUpload as ucanUpload , u.canCreateMeet as ucanCreateMeet , u.canViewChart as ucanViewChart , u.about as uabout , u.channelName as uchannelName , u.emailVerified as uemailVerified , u.analyticsCode as uanalyticsCode , u.externalOptions as uexternalOptions , u.first_name as ufirst_name , u.last_name as ulast_name , u.address as uaddress , u.zip_code as uzip_code , u.country as ucountry , u.region as uregion , u.city as ucity , u.donationLink as udonationLink , u.extra_info as uextra_info , u.phone as uphone , u.is_company as uis_company , v.*, c.iconClass, c.name as category, c.clean_name as clean_category,c.description as category_description, v.created as videoCreation, v.modified as videoModified FROM videos as v LEFT JOIN categories c ON categories_id = c.id LEFT JOIN users u ON v.users_id = u.id WHERE 2=2 AND v.status IN ('a','k','f') ORDER BY likes DESC ) a WHERE a.ustatus IN ('a') LIMIT 1000, 900; ``` The ANALYZE FORMAT=JSON is the next: ```json { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 8.047324959, "table": { "table_name": "u", "access_type": "ref", "possible_keys": ["PRIMARY", "users_status_IDX"], "key": "users_status_IDX", "key_length": "1", "used_key_parts": ["status"], "ref": ["const"], "r_loops": 1, "rows": 1, "r_rows": 1, "r_table_time_ms": 0.045761237, "r_other_time_ms": 0.0201165, "filtered": 100, "r_filtered": 100, "index_condition": "u.`status` = 'a'" }, "table": { "table_name": "v", "access_type": "ref", "possible_keys": [ "fk_videos_users_idx", "video_status_idx", "videos_status_index" ], "key": "fk_videos_users_idx", "key_length": "4", "used_key_parts": ["users_id"], "ref": ["AVideo.u.id"], "r_loops": 1, "rows": 391299, "r_rows": 1900, "r_table_time_ms": 6.939235389, "r_other_time_ms": 1.010973728, "filtered": 50.00025558, "r_filtered": 100, "attached_condition": "v.`status` in ('a','k','f')" }, "table": { "table_name": "c", "access_type": "eq_ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["id"], "ref": ["AVideo.v.categories_id"], "r_loops": 1900, "rows": 1, "r_rows": 1, "r_table_time_ms": 0.007669714, "r_other_time_ms": 0.001959112, "filtered": 100, "r_filtered": 100 } } } ``` And what I am trying to understand is, what's the difference? Why would the same exact query will result with this issue? Just to notice that the next query will not run at all due to fields naming conflict between u and v: ```sql SELECT * FROM (SELECT u.* , v.*, c.iconClass, c.name as category, c.clean_name as clean_category,c.description as category_description, v.created as videoCreation, v.modified as videoModified FROM videos as v LEFT JOIN categories c ON categories_id = c.id LEFT JOIN users u ON v.users_id = u.id WHERE 2=2 AND v.status IN ('a','k','f') ORDER BY likes DESC ) a WHERE a.ustatus IN ('a') LIMIT 1000, 900; ``` I am not new to the DB world that much but it's the first time I am getting my hands dirty about such an issue. I will share the full DB later on today so it could be used as a learning material. Also, are there any big public databases that can be used for learning and testing purposes? Thanks, Eliezer ---- Eliezer Croitoru NgTech, Tech Support Mobile: +972-5-28704261 Email: ngtech1ltd(a)gmail.com Web: https://ngtech.co.il/ My-Tube: https://tube.ngtech.co.il/

3 10

[Maria-discuss] mariadb-connector-cpp and multiple threads
by jason＠youtorque.com 25 Jul '22

25 Jul '22

Using the [ MariaDB C++ Connector ]( https://github.com/mariadb-corporation/mariadb-connector-cpp ) on ubuntu 20.04, and gcc compiler. During load test of a webserver application it was discovered that anytime there was more than a single concurrent connection the server would crash. This was narrowed down to the connect member of the mariadb driver. The errors that were being reported were always one of the following two: terminate called after throwing an instance of 'std::logic_error' what(): basic_string::_M_construct null not validAborted (core dumped)terminate called after throwing an instance of 'std::invalid_argument' what(): stoiAborted (core dumped) As it wouldn't be feasible to post the source of the server, I have reproduced a minimal working example that uses only standard library and mariadb/conncpp.hpp: ``` #include <mariadb/conncpp.hpp>#include <iostream>#include <thread>#include <string>sql::Driver* driver = sql::mariadb::get_driver_instance();sql::SQLString connectionUrl = "jdbc:mariadb://localhost:3306/the_database_name";sql::Properties properties = {{"user", "the_username"}, {"password", "the_password"}};int main(){ // simulate 2 concurrent connections each making 500 requests std::vector<std::function<void()>> t1cbs; std::vector<std::function<void()>> t2cbs; for(int i = 0; i < 500; i++) // if does not fail for you at 500 up the number until it does { t1cbs.push_back([&]{ std::unique_ptr<sql::Connection> conn(driver->connect(connectionUrl, properties)); std::cout << "t1:" << conn->getHostname().c_str() << std::endl; }); // comment out this block to keep the second thread from executing, and you will see // that no errors occur t2cbs.push_back([&]{ std::unique_ptr<sql::Connection> conn(driver->connect(connectionUrl, properties)); std::cout << "t2:" << conn->getHostname().c_str() << std::endl; }); } std::thread t1([&]{ for(auto& cb : t1cbs) cb(); }); std::thread t2([&]{ for(auto& cb : t2cbs) cb(); }); t1.join(); t2.join(); return 0;} ``` I'm guessing the issue is on my end as I've just started working with this particular library and have probably missed or misunderstood something. Would someone be able to point me in the right direction as far as how I might go about creating database connections from within separate threads?

2 3

Re: [Maria-discuss] mysql_upgrade fails
by Majed Zouhairy 21 Jul '22

21 Jul '22

on centos 8, upgrade form mraidb 10.3 to 10.6 fails with: sudo mysql_upgrade -u root -p Enter password: Major version upgrade detected from 10.3.17-MariaDB to 10.6.8-MariaDB. Check required! Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK mysql.columns_priv OK mysql.db OK mysql.event OK mysql.func OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK mysql.index_stats OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.roles_mapping OK mysql.servers OK mysql.table_stats OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK Phase 2/7: Installing used storage engines... Skipped Phase 3/7: Fixing views Phase 4/7: Running 'mysql_fix_privilege_tables' ERROR 1005 (HY000) at line 694: Can't create table `mysql`.`innodb_table_stats` (errno: 184 "Tablespace already exists") ERROR 1005 (HY000) at line 698: Can't create table `mysql`.`innodb_index_stats` (errno: 184 "Tablespace already exists") ERROR 1005 (HY000) at line 702: Can't create table `mysql`.`transaction_registry` (errno: 184 "Tablespace already exists") ERROR 1005 (HY000) at line 774: Can't create table `mysql`.`gtid_slave_pos` (errno: 184 "Tablespace already exists") FATAL ERROR: Upgrade failed ________________________________ From: Vicențiu Ciorbaru <cvicentiu(a)gmail.com> Sent: Wednesday, April 21, 2021 3:51 PM To: Majed Zouhairy <majedzouhairy(a)hotmail.com> Cc: maria-discuss(a)lists.launchpad.net <maria-discuss(a)lists.launchpad.net> Subject: Re: [Maria-discuss] mysql_upgrade fails Hi Majed, What MariaDB version is this? What is the previous version and what is the current version you are upgrading to? Is this from a linux distro, or where did you get the MariaDB binary from? If it's a distro, which one? Vicențiu On Wed, 21 Apr 2021 at 15:31, Majed Zouhairy <majedzouhairy(a)hotmail.com<mailto:majedzouhairy@hotmail.com>> wrote: in last time's upgrade and today i got: sudo mysql_upgrade -u root -p Enter password: Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK mysql.columns_priv OK mysql.db OK mysql.event OK mysql.func OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK mysql.index_stats OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.roles_mapping OK mysql.servers OK mysql.table_stats OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK Phase 2/7: Installing used storage engines... Skipped Phase 3/7: Fixing views Phase 4/7: Running 'mysql_fix_privilege_tables' ERROR 1813 (HY000) at line 77: Tablespace for table '`mysql`.`innodb_table_stats`' exists. Please DISCARD the tablespace before IMPORT ERROR 1813 (HY000) at line 81: Tablespace for table '`mysql`.`innodb_index_stats`' exists. Please DISCARD the tablespace before IMPORT ERROR 1813 (HY000) at line 85: Tablespace for table '`mysql`.`transaction_registry`' exists. Please DISCARD the tablespace before IMPORT ERROR 1813 (HY000) at line 153: Tablespace for table '`mysql`.`gtid_slave_pos`' exists. Please DISCARD the tablespace before IMPORT ERROR 1146 (42S02) at line 642: Table 'mysql.innodb_index_stats' doesn't exist ERROR 1243 (HY000) at line 643: Unknown prepared statement handler (stmt) given to EXECUTE ERROR 1146 (42S02) at line 645: Table 'mysql.innodb_table_stats' doesn't exist ERROR 1243 (HY000) at line 646: Unknown prepared statement handler (stmt) given to EXECUTE ERROR 1146 (42S02) at line 650: Table 'mysql.innodb_index_stats' doesn't exist ERROR 1146 (42S02) at line 654: Table 'mysql.innodb_table_stats' doesn't exist ERROR 1146 (42S02) at line 657: Table 'mysql.innodb_table_stats' doesn't exist FATAL ERROR: Upgrade failed what to do? _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss(a)lists.launchpad.net<mailto:maria-discuss@lists.launchpad.net> Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

3 8