Hot answers tagged

3

Do you want to optimize MariaDB tables or do you want to run OPTIMIZE TABLE on MariaDB? Because those are 2 things that have nothing to do with each other. Optimizing, in general, is not a process as easy as running a command- you need to analyze your server, profile your queries, change your schema, check your configuration; select, dimension and configure ...


3

You can really benefit from an index on hosts (hostname, scan_id) for this query, and possibly another one including status (especially for the second query below). Your query may also benefit from transferring some joins to per-row totals: CREATE INDEX ON hosts (hostname, scan_id); CREATE INDEX ON hosts (hostname, status, scan_id); SELECT hosts.hostname, ...


2

The MariaDB doku claims for NOW(): Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context. So this might be related to the fact that '2016-04-14 15:06:30' may look like a timestamp, but is actually a Varchar. Try using timestamp ...


2

The datadir = /var/lib/mysql/data was 100% full. After extending the volume systemctl start mariadb worked very well.


2

Do you have 4 or more cores/processors machine? Current MySQL query cache implementation does not scale well and there is a mutex contention problem on systems with more thread (the query might spend more time waiting to check the query cache than it would need to be executed fresh if the relevant data are already in other buffers which allow parallel ...


1

We can find with subquery each conversation_id for user_id from table conversations_users and use left join for get one reply for each conversation_id from conversation_reply table (see sqlfiddle example and code below) select cp1.* from conversations_reply cp1 left join conversations_reply cp2 on (cp1.conversation_id = cp2.conversation_id AND cp1.id ...


1

Really peculiar behaviour, unfortunate this limitation still exists atleast in my version of MariaDB: MariaDB [test]> select @@version; +-----------------+ | @@version | +-----------------+ | 10.0.23-MariaDB | +-----------------+ 1 row in set (0.00 sec) MariaDB [test]> create table t ( x int not null ); Query OK, 0 rows affected (0.05 sec) ...


1

What you did applies to tables one at a time. Instead... Plan A: Change datadir when installing. (See documentation for details and caveats.) Plan B: Symlink .../mysql/data to some place in the other partition. (And remove DATA_DIRECTORY, etc from CREATE TABLE.)


1

The nodes are not registered by their IP addresses, but by a UUID and server ID. Just ensure that wsrep_cluster_address reflects the nodes properly, and do a rolling restart.


1

I have now found the source of the issue. It is because my ri_email_messages table was using the Aria engine. I had done this because InnoDB did not support full text search at the time. In MariaDB > 10.0.5, you can now do fulltext search in InnoDB, so I have switched back. The way to tell in the EXPLAIN statement is that the PRIMARY query was using ...


1

Found a solution that works MariaDB [(none)]> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES WHERE PATH LIKE '%5377%'; +---------+---------------------------------+ | SPACE | PATH | +---------+---------------------------------+ | 2432544 | ./db5377/menu_router.ibd | +---------+---------------------------------+ 1 row ...


1

The InnoDB/XtraDB is a table engine, it has no say in parsing SQL. Thats another layer, which should be mostly engine independent. MariaDB 5.5 is supposed to be 99.99% SQL compatible with MySQL 5.5 (there were some minor hiccups) and MariaDB 10.x is backward compatible with 5.5. With MariaDB 10 and MySQL 5.6 (and now 5.7) the paths diverged somehow - some ...



Only top voted, non community-wiki answers of a minimum length are eligible