Tag Info

Hot answers tagged

2

Use the information stored in INFORMATION_SCHEMA: SELECT table_schema, table_name FROM INFORMATION_SCHEMA.tables ORDER BY table_schema, table_name; table_schema is the database name. To show also the databases without any tables: SELECT s.schema_name, t.table_name FROM INFORMATION_SCHEMA.schemata AS s LEFT JOIN INFORMATION_SCHEMA.tables AS t ON ...


2

There is actually a far more useful application for the SYSTEM command than simply executing ls to see the current directory contents: As mentioned in the docs (and elaborated on by a comment), this is way to make backups: echo "FLUSH TABLES WITH READ LOCK; SYSTEM snapshot.sh; UNLOCK TABLES;" | mysql snapshot.sh contains code to make an atomic snapshot ...


2

I thought I'd add something interesting. Based on some testing I found that the engine is NULL for MyISAM tables if they are corrupted: mysql> repair table TEST.test_myisam; ^CCtrl-C -- sending "KILL QUERY 10" to server ... Ctrl-C -- query aborted. +---------------------+--------+----------+------------------------------+ | Table | Op | ...


2

Your suppositions are correct, MySQL versions up to 5.5 only can execute those kind of subqueries (semijoins) in that particular way. The subquery is not correlated in reality (there is no real dependency), but the DEPENDENT SUBQUERY that you may get from explain means that it is executing it as if it was one. Please note that in some scenarios, that ...


2

So, I will answer the question "why it is creating problem with whole query": In MySQL's REPEATABLE-READ transaction isolation mode, no phantom rows appear. In order to do that, MySQL locks, not only the rows, but also the gaps between them (also know as next-key locking). As MySQL happens to be executing the read on the first table using an index scan, ...


2

Yes you can. Or no you can't. It doesn't really matter. If you think about how DBMS1 is going to have to deal with a commit and/or rollback after it has, via your UDF function in which you have hidden an update to another, external, DBMS2, then you should realize that this is a very bad idea no matter how you toss or turn it, because somwewhere somehow it ...


2

You need to specify whether you want an left, right or full outer join. A left outer join is the same as left join. Assuming this is what you want: [...] FROM (SELECT subfactor.score, dateID, EmployeeID FROM employeescore LEFT JOIN subfactor ON employeescore.SubFactorID = subfactor.SubFactorID LEFT JOIN factor ON ...


1

In a way, it does not matter since you should purge the binlogs. But first, there are two questions -- why do you have the binlog, and are you using SBR or RBR? If the binlog is for incremental backups, then you need it (them) to be big enough to last until you take the next backup. This might be a few GB per day for a "busy" system. If you have Slave(s) ...


1

Unfortunately, MySQL does not allow for filtered indexes (otherwise you can just filter on open tickets only). The next best thing is to do something similar to the two table approach you describe: Partitioned tables.


1

Below is what you need CREATE TABLE customers ([id 1] int, [status] varchar(4), [id 2] int, [name] varchar(4), [category] varchar(6)) ; INSERT INTO customers ([id 1], [status], [id 2], [name], [category]) VALUES (001, 'open', 011, 'john', 'person'), (001, 'open', 011, 'john', 'male') ; select * from dbo.customers ---- You have to use ...


1

The general log should never be enabled on a production server (under normal circumstances), as it logs absolutely all queries (reads and writes) performed on the server, taking a lot of disk space and query performance with it. The general log is only useful for debugging issues on the server. You should turn it off on a live server by executing: SET ...


1

All statements that hit the server are stored inside the general log. So general log is duplicating your dump. The general log should be used only for debugging only, and it should be activated for the time required to spot a problem. Try to disactivate general log.


1

I've realised that using an alternative query structure helps to speed this up. UPDATE table1 SET is_active = 1 WHERE id IN (1,2,3,4,...) Works much quicker and reduces the script file size to 11kb from 114kb and also reduces running time from 40 secs to just under 2 secs


1

You can set it to 1 with this command: mysql> ALTER TABLE tbl AUTO_INCREMENT = 1; More info in the mysql manuals, here: https://dev.mysql.com/doc/refman/5.6/en/example-auto-increment.html


1

Lennart's answer explains why your query fails and how to fix it so it is syntactically correct. About the additional problem that you "want to display the output regardless if one of the joined sub queries doesn't have any rows", you could rewrite the query and simplify it: SELECT AVG(CASE WHEN es.DateID = 'Jan2015' THEN sf.score ELSE NULL END) AS ...


1

QUERY #1 (Separate Columns) SET @db = ''; SELECT db,tb FROM (SELECT @tb := IF(@db=table_schema,table_name,'') tb, IF(@db=table_schema,'',table_schema) db, (@db := table_schema) unused FROM information_schema.tables) A; QUERY #2 (One Column) SET @db = ''; SELECT CONCAT(db,' ',tb) DBTB FROM (SELECT @tb := IF(@db=table_schema,table_name,'') tb, ...


1

PXC (and other Galera solutions) require one network hop at COMMIT time. If your application can combine writes into a single transaction, that will help at some level. However, still each COMMIT will take 300ms (or whatever it is). Regular MySQL can do Master-Master and have both writable. That replication is asynchronous. However, there are a number ...


1

A version of JD Schmidt's answer, but without the awkwardness of an extra column: CREATE TABLE foo ( FieldA INT, FieldB INT ); DELIMITER // CREATE TRIGGER InsertFieldABNotNull BEFORE INSERT ON foo FOR EACH ROW BEGIN IF (NEW.FieldA IS NULL AND NEW.FieldB IS NULL) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '\'FieldA\' and \'FieldB\' cannot ...


1

I've seen it done both ways. If you want to bulk up your database size just start using BLOBS. Pro's for storing documents in your database you're already storing your data there so why not a little more? (Only it's a lot more) a certain simplicity of access. Any request to read only has to go one place to access the document or thing Cons for storing ...


1

Perhaps doing a COUNT is better since it must be a number always DELIMITER $$ DROP PROCEDURE IF EXISTS `USER_EXISTS` $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `USER_EXISTS` (IN `GIVEN_USERNAME` VARCHAR(64) CHARSET utf8mb4) BEGIN SET @User_exists = 0; SELECT COUNT(1) INTO @found FROM `dbname`.`tablename` WHERE `username` = ...


1

You can try to use replace function: mysql> select replace("A.B C", ".", "") = "AB C"; +------------------------------------+ | replace("A.B C", ".", "") = "AB C" | +------------------------------------+ | 1 | +------------------------------------+ 1 row in set (0.00 sec)


1

If you have a logical backup generated with mysqldump, you should load it into mysql 5.5 without issues. You could check that logical backup was generated with the options --triggers --events --comments --routines to be more complete.


1

I am adding an additional answer because Giovanni's one is completely right, but it only defines why scaling, how to do HA on the master and on the slaves, and how to split read queries among the slaves from an architectural point of view. I think OP question was more about how to do the read-write split itself. Then answer depends on the software stack ...


1

A good solution is to define two datasources for the applications. One datasource pointing to master is for writing. One datasouce pointing to slave node is for reading. The applications must be aware that data can be delayed with respect to the master. There are applications that could tolerate a slight delay on data, and other applications that must be ...


1

1) Yes 2) it's not going to be a problem but of course it depends on the problem itself. Lets use the term - incident. Problem is already a problem by definition. Solve incidents, analyze the cause , prevent the problem!!! ;) 3) in case of an error, try to think what caused it. Nothing to worry about specifically in my opinion Try to strictly separate ...


1

"Is it possible to do so? The trigger will insert some rows into a unique table that exists only on the slave." yes, you can A lot of warehousing architectures are based on the slave only. The title of your question is wrong. Change replica DB with SLAVE ONLY


1

If you have a good reason to have multiple tables (i.e. the data has different columns), then of course multiple tables is best. But if the data all belongs in the one table and you're just debating splitting it up due to its size, don't.


1

I am not 100% sure, but 5.1 documentation says: PARTITION_DESCRIPTION: This column is used for RANGE and LIST partitions. For a RANGE partition, it contains the value set in the partition's VALUES LESS THAN clause, which can be either an integer or MAXVALUE. For a LIST partition, this column contains the values defined in the partition's VALUES ...


1

In earlier versions of MySQL, you would run this on the Master STOP SLAVE; CHANGE MASTER TO master_host = ''; and restart mysqld. For MySQL 5.5/5.6, you would run this on the Master STOP SLAVE; RESET SLAVE ALL; MySQL restart not required For more details on this, please read Disconnecting a replication slave is easier with MySQL 5.5+ (RESET SLAVE vs. ...



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