Re: [Maria-discuss] Nosql language with plugins
Hi mailing list moderators, Should I unsubscribe from MariaDB mailing lists now, or will you unsubscribe this person? This language is totally unacceptable and in violation the MariaDB CoC. Kind regards, Erik > Den 14. maj 2020 kl. 19.49 skrev Reindl Harald : > > if you once again are responding to a private mail on a public list i will > care of that you wish you woldn't been born at all - and now for tahe sake of > god just shut up ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Re: [Maria-discuss] ERROR 1071 with mysql_upgrade
> Den 17. jul. 2019 kl. 11.48 skrev Erik Cederstrand : > > Thanks, Jocelyn! > >> Den 17. jul. 2019 kl. 11.28 skrev jocelyn fournier >> : >> It seems this table should be in InnoDB format, not MyISAM (same for >> innodb_table_stats). > > I tried altering the engine to InnoDB but was not allowed. Hmm. > > Then I decided to just drop the table to have mysql_upgrade create it again. > Now mysql_upgrade complains that the tablespace for that table exists and I > need to drop it. But: > > MariaDB [mysql]> ALTER TABLE innodb_index_stats DROP TABLESPACE; > ERROR 1146 (42S02): Table 'mysql.innodb_index_stats' doesn't exist > [mysql]> DROP TABLE innodb_index_stats; > ERROR 1051 (42S02): Unknown table 'mysql.innodb_index_stats' > MariaDB [mysql]> CREATE TABLE `innodb_index_stats` ( >-> `database_name` varchar(64) COLLATE utf8_bin NOT NULL, >-> `table_name` varchar(199) COLLATE utf8_bin NOT NULL, >-> `index_name` varchar(64) COLLATE utf8_bin NOT NULL, >-> `last_update` timestamp NOT NULL DEFAULT current_timestamp() ON > UPDATE current_timestamp(), >-> `stat_name` varchar(64) COLLATE utf8_bin NOT NULL, >-> `stat_value` bigint(20) unsigned NOT NULL, >-> `sample_size` bigint(20) unsigned DEFAULT NULL, >-> `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL, >-> PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`) >-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin > STATS_PERSISTENT=0; > ERROR 1050 (42S01): Table '`mysql`.`innodb_index_stats`' already exists > > > I tried stopping the server, deleting all files in > /var/db/mysql/mysql/innodb_index_stats.* and restarting, but I still see the > same behaviour above. Answering my own question: I ended up solving my problem with the following: * stop the server * copy in /var/db/mysql/mysql/innodb_* files from a different 10.4 server where these tables were already InnoDB * start the server * drop the tables * re-run mysql_upgrade Thanks for the hints, everyone! Kind regards, Erik ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Re: [Maria-discuss] ERROR 1071 with mysql_upgrade
Thanks, Jocelyn! > Den 17. jul. 2019 kl. 11.28 skrev jocelyn fournier > : > It seems this table should be in InnoDB format, not MyISAM (same for > innodb_table_stats). I tried altering the engine to InnoDB but was not allowed. Hmm. Then I decided to just drop the table to have mysql_upgrade create it again. Now mysql_upgrade complains that the tablespace for that table exists and I need to drop it. But: MariaDB [mysql]> ALTER TABLE innodb_index_stats DROP TABLESPACE; ERROR 1146 (42S02): Table 'mysql.innodb_index_stats' doesn't exist [mysql]> DROP TABLE innodb_index_stats; ERROR 1051 (42S02): Unknown table 'mysql.innodb_index_stats' MariaDB [mysql]> CREATE TABLE `innodb_index_stats` ( -> `database_name` varchar(64) COLLATE utf8_bin NOT NULL, -> `table_name` varchar(199) COLLATE utf8_bin NOT NULL, -> `index_name` varchar(64) COLLATE utf8_bin NOT NULL, -> `last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), -> `stat_name` varchar(64) COLLATE utf8_bin NOT NULL, -> `stat_value` bigint(20) unsigned NOT NULL, -> `sample_size` bigint(20) unsigned DEFAULT NULL, -> `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL, -> PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0; ERROR 1050 (42S01): Table '`mysql`.`innodb_index_stats`' already exists I tried stopping the server, deleting all files in /var/db/mysql/mysql/innodb_index_stats.* and restarting, but I still see the same behaviour above. Kind regards, Erik ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Re: [Maria-discuss] ERROR 1071 with mysql_upgrade
Thanks! I couldn't find the mysql_fix_privilege_tables.sql installed anywhere on my system, so I ended up patching mysql_upgrade to print the SQL commands it's executing. There are ca. 200 commands executed at once, so it took a while to pinpoint the failing statement. It would be cool if triple-verbose mysql_upgrade could print the exact statement that matches the error that it prints in https://github.com/MariaDB/server/blob/9a7d96e8326377b92406c09fdcb8bd60c45f901c/client/mysql_upgrade.c#L1063 Anyway, the failing query is: alter table innodb_index_stats modify last_update timestamp not null default current_timestamp on update current_timestamp, modify table_name varchar(199); which throws "Specified key was too long; max key length is 1000 bytes". Currently, my innodb_index_stats table is defined as: CREATE TABLE `innodb_index_stats` ( `database_name` varchar(64) COLLATE utf8_bin NOT NULL, `table_name` varchar(64) COLLATE utf8_bin NOT NULL, `index_name` varchar(64) COLLATE utf8_bin NOT NULL, `last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `stat_name` varchar(64) COLLATE utf8_bin NOT NULL, `stat_value` bigint(20) unsigned NOT NULL, `sample_size` bigint(20) unsigned DEFAULT NULL, `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL, PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0; Does that ring a bell? Kind regards, Erik > Den 16. jul. 2019 kl. 21.13 skrev Sergei Golubchik : > > Hi, Erik! > > you can run mysql_fix_privilege_tables.sql manually. Like with > mysql -uroot -p -vvv < mysql_fix_privilege_tables.sql > > Presuming FreeBSD installs this file somewhere. If it doesn't, you can > extract it from mysql_upgrade with strings, I suppose. > > On Jul 16, Erik Cederstrand wrote: >> Hi, >> >> I'm trying to upgrade a MariaDB 10.2 server to 10.4.6 on FreeBSD. >> >> When running the mysql_update command, it dies with: >> >> $ mysql_upgrade >> Phase 1/7: Checking and upgrading mysql database >> Processing databases >> mysql > ... >> mysql.transaction_registry OK >> Phase 2/7: Installing used storage engines... Skipped >> Phase 3/7: Fixing views >> mysql.user OK >> Phase 4/7: Running 'mysql_fix_privilege_tables' >> ERROR 1071 (42000) at line 437: Specified key was too long; max key length >> is 1000 bytes >> FATAL ERROR: Upgrade failed >> >> Adding verbose option does not give more hints, and the failing SQL >> statement doesn't seem to be logged with full query logging turned on. >> >> How do I debug this? I can't even see which table or column it's >> complaining about. I don't remember fiddling with collation or >> character sets on system tables. >> >> Kind regards, >> Erik > > Regards, > Sergei > VP of MariaDB Server Engineering > and secur...@mariadb.org > > ___ > Mailing list: https://launchpad.net/~maria-discuss > Post to : maria-discuss@lists.launchpad.net > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
[Maria-discuss] ERROR 1071 with mysql_upgrade
Hi, I'm trying to upgrade a MariaDB 10.2 server to 10.4.6 on FreeBSD. When running the mysql_update command, it dies with: $ mysql_upgrade Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK mysql.columns_priv OK mysql.db OK mysql.eventOK mysql.func OK mysql.global_priv OK mysql.gtid_slave_pos OK mysql.help_categoryOK mysql.help_keyword OK mysql.help_relationOK mysql.help_topic OK mysql.index_stats OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.roles_mappingOK mysql.servers OK mysql.table_stats OK mysql.tables_priv OK mysql.time_zoneOK mysql.time_zone_leap_secondOK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_typeOK mysql.transaction_registry OK Phase 2/7: Installing used storage engines... Skipped Phase 3/7: Fixing views mysql.user OK Phase 4/7: Running 'mysql_fix_privilege_tables' ERROR 1071 (42000) at line 437: Specified key was too long; max key length is 1000 bytes FATAL ERROR: Upgrade failed Adding verbose option does not give more hints, and the failing SQL statement doesn't seem to be logged with full query logging turned on. How do I debug this? I can't even see which table or column it's complaining about. I don't remember fiddling with collation or character sets on system tables. Kind regards, Erik ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Re: [Maria-discuss] Installing MariaDB Alongside MySQL
Hi Reindl, Den 5. maj 2018 kl. 12.42 skrev Reindl Harald : > > get rid of that damned /etc/init.d/mariadb crap > WTF is "ExecStart=/etc/init.d/mariadb start" > > put your service in /etc/systemd/system/mariadb.service and you are done > > no idea what you did not understand in the ExecStart from my sample above This is a public mailing list, and as such goes out to a lot of people. Can you please leave out the profanities and arrogance in your future posts? It's unnecessary, it scares off beginners, it reflects badly on the project and quite frankly makes following this list much less enjoyable than it should be. Thanks, Erik signature.asc Description: Message signed with OpenPGP ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Re: [Maria-discuss] Dynamically rewrite query
> Den 20. apr. 2016 kl. 20.15 skrev Sergei Golubchik : > > On Apr 20, Erik Cederstrand wrote: >> >> CREATE TABLE `t1` ( `i` int(11) NOT NULL, `j` int(11) NOT NULL, `l` >> int(11) NOT NULL, `m` int(11) DEFAULT NULL, PRIMARY KEY >> (`i`,`j`,`l`)); >> >> CREATE TABLE `t2` ( `i` int(11) NOT NULL, `k` int(11) DEFAULT NULL, >> PRIMARY KEY (`i`)) ENGINE=InnoDB DEFAULT CHARSET=utf8; >> >> The problematic query is this: >> SELECT DISTINCT t2.i, t2.k, t1.j, t1.l, t1.m FROM t2 LEFT JOIN t1 >> ON t2.i=t1.i WHERE t2.k < 123; >> >> The query is slow because the DISTINCT clause forces the query to use >> a temporary table. Removing DISTINCT makes the query 100x faster. >> ... >> it could also be argued that MariaDB should be smart enough to detect >> that the DISTINCT is unnecessary and ignore it (should I create a bug >> report for that?). > > Yes, please, do report it! On the first glance this looks like a rather > simple optimization. Thanks. Reported as https://jira.mariadb.org/browse/MDEV-9964 Erik signature.asc Description: Message signed with OpenPGP using GPGMail ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Re: [Maria-discuss] Dynamically rewrite query
> Den 20. apr. 2016 kl. 17.07 skrev Guillaume Lefranc : > > You can use MaxScale : > https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale/maxscale-regex-filter-overview/ Ok, thanks. I'll have a look at it. Erik signature.asc Description: Message signed with OpenPGP using GPGMail ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
[Maria-discuss] Dynamically rewrite query
Hello list, I'm administering a MariaDB (10.0.23) backend used by some proprietary software. I'm having trouble with one specific slow query. The tables look like this (simplified for clarity): CREATE TABLE `t1` ( `i` int(11) NOT NULL, `j` int(11) NOT NULL, `l` int(11) NOT NULL, `m` int(11) DEFAULT NULL, PRIMARY KEY (`i`,`j`,`l`) ); CREATE TABLE `t2` ( `i` int(11) NOT NULL, `k` int(11) DEFAULT NULL, PRIMARY KEY (`i`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; The problematic query is this: SELECT DISTINCT t2.i, t2.k, t1.j, t1.l, t1.m FROM t2 LEFT JOIN t1 ON t2.i=t1.i WHERE t2.k < 123; The query is slow because the DISTINCT clause forces the query to use a temporary table. Removing DISTINCT makes the query 100x faster. Now, since (t1.i, t1.j, t1.l, t1.m) are guaranteed to be unique (and also (t2.i, t2.k) on their own, if the left join doesn't match), I cannot see how "SELECT ..." and "SELECT DISTINCT ..." can possibly produce different output. So I mention that to the developers of said software, and they reply that they forgot why they added the DISTINCT but that they decided it's too risky to remove it (thereby admitting that they don't comment their code, and don't write unit tests for bugs they find, but I knew that already). I'm aware of "Garbage in, garbage out", but it could also be argued that MariaDB should be smart enough to detect that the DISTINCT is unnecessary and ignore it (should I create a bug report for that?). In the meantime, is there any way I can rewrite queries like this dynamically to remove the DISTINCT clause? MySQL 5.7 documentation mentions query rewrite plugins, but I don't see that mentioned in MariaDB documentation. Any other suggestions are welcome. Thanks, Erik signature.asc Description: Message signed with OpenPGP using GPGMail ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Re: [Maria-discuss] Monitoring InnoDB tables?
Hello Rhys, Thanks for your suggestions! > Den 13. apr. 2016 kl. 09.48 skrev rhys.campb...@swisscom.com: > > Triggers? You mean create a custom logging table in the customer database? I guess I could do something like this on each table: CREATE TRIGGER insert_event AFTER INSERT ON t1 FOR EACH ROW INSERT INTO event_log SET table_name = 't1', count=1 ON DUPLICATE KEY UPDATE count = count + 1; > Monitor the binlog, as you state, might be a goer. Yes, but I'd like to know which schema and table changed, not just that *something* changed somewhere on the server. Otherwise I have too much work to do. > You could enable the performance schema and monitor queries there? I tried to enable the performance_schema. Any pointers to where I should look? Poking around, my best bet right now would be: SELECT object_schema, object_name, sum_timer_write_allow_write FROM table_lock_waits_summary_by_table WHERE object_schema='test' AND object_name='t1'; > If you cannot change the source database at all how about an additional slave > that you can change? I think I'd go for this option. I could try that. Erik signature.asc Description: Message signed with OpenPGP using GPGMail ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
[Maria-discuss] Monitoring InnoDB tables?
Hi list, I'm developing a fast data synchronization tool and need to monitor some tables in a customer's MariaDB (10.1) server for changes. I only have access to the server as a MariaDB client (no filesystem access). Ideally, I would keep a connection open and poll every few seconds - AFAIK, it's not possible for e.g. a stored procedure to notify a listener external to MariaDB. The tables have no 'timestamp' or other columns I can do MAX() on to check for updates. The database is used as a backend for some proprietary software, so I can't just ask the customer to add an autoincrement column. With MyISAM, I could look at UPDATE_TIME in information_schema.tables (and live with the full-second resolution) or ask the customer to "alter table t1 checksum=1;" so I can use "checksum table t1 quick;". None of these work with InnoDB, and "checksum table t1;" is too slow for large tables. I've seen suggestions to use NUM_ROWS and MODIFIED_COUNTER in information_schema.innodb_sys_tablestats, but it's unreliable for me in a number of cases, e.g. if the number of rows is the same but contents are different. Here's an example: MariaDB [test]> create table t1 (i int primary key, j int); Query OK, 0 rows affected (0.02 sec) MariaDB [test]> insert into t1 values (1, 2); Query OK, 1 row affected (0.00 sec) MariaDB [test]> select * from information_schema.innodb_sys_tablestats where name = 'test/t1'; +--+-+---+--+--+--+--+-+---+ | TABLE_ID | NAME| STATS_INITIALIZED | NUM_ROWS | CLUST_INDEX_SIZE | OTHER_INDEX_SIZE | MODIFIED_COUNTER | AUTOINC | REF_COUNT | +--+-+---+--+--+--+--+-+---+ |15000 | test/t1 | Initialized |1 |1 | 0 |1 | 0 | 1 | +--+-+---+--+--+--+--+-+---+ 1 row in set (0.00 sec) MariaDB [test]> delete from t1; Query OK, 1 row affected (0.00 sec) MariaDB [test]> insert into t1 values (3, 4); Query OK, 1 row affected (0.00 sec) MariaDB [test]> select * from information_schema.innodb_sys_tablestats where name = 'test/t1'; +--+-+---+--+--+--+--+-+---+ | TABLE_ID | NAME| STATS_INITIALIZED | NUM_ROWS | CLUST_INDEX_SIZE | OTHER_INDEX_SIZE | MODIFIED_COUNTER | AUTOINC | REF_COUNT | +--+-+---+--+--+--+--+-+---+ |15000 | test/t1 | Initialized |1 |1 | 0 |1 | 0 | 1 | +--+-+---+--+--+--+--+-+---+ 1 row in set (0.00 sec) How can I monitor an InnoDB table for changes efficiently? I could *possibly* convince the customer to monitor the binlog or full query log, but then I don't even know which database was changed. The server has hundreds of databases. Erik signature.asc Description: Message signed with OpenPGP using GPGMail ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
