Tag Info

New answers tagged

0

If you want number and kilograms to appear as a single column (comma separated), do this: SELECT GROUP_CONCAT(uom_name) UnitsOfMeasure FROM unit_of_measure WHERE uom_id IN (1,2); If you want number and kilograms to appear as a single column (space separated), do this: SELECT GROUP_CONCAT(uom_name SEPARATOR ' ') UnitsOfMeasure FROM unit_of_measure WHERE ...


0

Let's say you made the table like this CREATE TABLE TrainEvents ( id INT NOT NULL AUTO_INCREMENT, station_arr_id INT NOT NULL, station_dep_id INT NOT NULL, arr_dt DATETIME NOT NULL, dep_dt DATETIME NOT NULL, ... PRIMARY KEY (id), KEY station_arr_index (station_arr_id,arr_dt), KEY station_dep_index (station_dep_id,dep_dt) ...


2

You don't. You restore the backup as a new DB, drop the unwanted table from it, then take a new backup of this working DB.


0

You should store all the events(*) in a dedicated table A with a date column for time of the train departure (to re use your example). This will allow you to deal with exceptions when (to reuse your example) a train departure is cancelled even if it was initially supposed to leave the station this day. Quering the table will then consist in using 2 ...


0

Note that if you are in a transaction it will take about as long to roll back the inserts as it did to do them initially.


0

Echoing the earlier comments here, I would agree that indexes in MongoDB are similar in general terms to MySQL. Proper indexing is probably the single easiest factor you can adjust to improve your query performance. Similar to most MySQL storage engines, MongoDB also uses B-tree based indexes. There are some other differences in terms of index types or ...


2

Do you mean by "kill / stop the MySQL instance" the linux command kill -9 ...? Yes, this might lead to corrupt data. Which instead should not leave corrupt data is the build-in kill command of MySQL. See the lower part of the page, which states that it might take the thread some time to actually notice the presence of the kill flag. The part which states it ...


1

You're using po in the WHERE clause, and in effect, saying that it can't be null. Maybe use: IFNULL(po.tot2,0) instead of po.tot2


2

If the master crashes, then it doesn't really matter what you do as far as stopping the slave, as long as you observe the slave once the master is back online, to verify that it has successfully started reading and executing events from the master again. If you don't stop it, the slave should still be fine, and will sit and continually try to reconnect to ...


1

It's easier for the storage engine to load in smaller chunks. The reason is that a big data load fills up the InnoDB rollback segment, and that takes time to purge. For this reason, tools like pt-fifo-split exist. This helps to process a huge file in modestly-sized pieces, without copying the pieces to disk.


1

There are no differences between all the SQL statements, except the third one you're using a different name for the unique index, that's all. All of them creates a unique key/index, constraint md5_constraint unique (query_md5) has the same effect. Just pick the name you prefer for the unique key and feel free to use any of your queries. :)


1

THIS IS THE QUERY YOU NEED SET @GivenDate = '2014-04-15'; SELECT CONCAT(@GivenDate,' ',MAX(time)) INTO @LastDateTime FROM airport_taxi WHERE date=@GivenDate; SET @nexttime = 0; SELECT TIME(FROM_UNIXTIME(StartTime)) "start", TIME(FROM_UNIXTIME(EndTime)) "end", FLOOR((EndTime - StartTime)/60) diff_min FROM (SELECT (@prevtime:=@nexttime) ...


1

There is no solution for fan-in with traditional MySQL replication. Another option is Tungsten Replicator. Here are a couple of posts on how it supports fan-in replication: http://narmitag.wordpress.com/2012/09/27/setting-up-fan-in-using-tungsten-replicator/ https://code.google.com/p/tungsten-replicator/wiki/TRCMultiMasterInstallation Another option is ...


0

Try to get rid of function calls in your where condition. Most of the time it'll result in a full table scan. If you can't avoid look if you can create a function index.


1

The comment from @ChristianAmmer is the key to answering your question. Why ??? First look the the WHERE clause DATE_FORMAT(t1.when, '%Y-%m-%d') <= LAST_DAY(now() - interval 1 month ) You are forcing mysqld to evaluate DATE_FORMAT(t1.when, '%Y-%m-%d') for every row and comparing each result to LAST_DAY(now() - interval 1 month ). That quickly adds ...


1

When possible, you should run slave stop on the slave after you have stopped traffic to the master. That said, in most cases the salve will reconnect on its own as long as there isn't an re-attempt limit and the binlog file/position hasn't changed.


1

You want to use a TRANSLATIONS table, that looks something like: CREATE TABLE TRANSLATIONS ( translation_id INTEGER, language_id INTEGER, translation VARCHAR(255), PRIMARY KEY (translation_id, language_id) ); Then edit your current schema and/or code to reference the TRANSLATIONS table, rather than any hard-coded values (using ...


0

The answer relies on what kind of data you store and how. I would never suggest making separate database. Depending on the data you can either: Create separate rows in same tables and add "language" column to specify which language the content is in. Create separate table per langauge to completely separate content. Which way to go depends on your data, ...


1

I like this presentation by Giuseppe Bianchi. Starting on page 5 it contains a desctiption of the TLS protocol - segment size, header size, HMAC overhead. As for the handshake, the impact on replication should be negligible. It will only occur on connection, and there may be a key exchange going on every hour, depending on the configuration. As for the ...


-1

Alternatively you may consider setting up replications from the main server to your dev machines. It would require to have a direct connection from your master to the slaves (the dev machines), but it doesn't need to be permanent. If you connect it once a month then the master will replicate only the changes, which should still be much faster. I doubt any ...


2

The replication filter you have in place Replicate_Wild_Do_Table: zo_dev_matrix.* is actually a little misleading. In the MySQL Documentation, the legal characters for wildcards are %, _ and \_. (If you want to interpret a literal underscore). The asterisk character is not listed. The above filter is actually looking for a table called zo_dev_matrix.*. ...


0

It appears that you have a successful connection from the slave to the master, and the SQL thread is running. But I notice this: Replicate_Wild_Do_Table: zo_dev_matrix.* This indicates you have configured a replication filter. It means that even though the relay logs contain all changes, only changes applying to tables in the zo_dev_matrix database will ...


1

You can take a backup from the live MySQL with Xtrabackup. Percona provides deb repository for Ubuntu. To install the repo for Ubuntu 12.04 follow instructions: Install the key: # apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A Add the source file: # cat /etc/apt/sources.list.d/percona.list deb http://repo.percona.com/apt precise ...


0

This will depend a bit on your table type. With MyISAM tables, you can generally just copy the folder and files and everything will work. With other table types, it sometimes doesn't work as well. The first thing you can try is to completely replace your MySQL data directory with the one from the backup. I would move it out of the way (mv /var/lib/mysql ...


0

Create an index on the [date] column, for table [glpi_followups] and run the query again. CREATE INDEX idx_1_glpi_followups ON glpi_followups (`date`)


3

You need a compound unique index. Suppose your table is called photos. You can do this: CREATE TABLE photos_new LIKE photos; ALTER TABLE photos_new ADD UNIQUE INDEX pid_tid_index (pid,tid); INSERT IGNORE INTO photos_new SELECT * FROM photos; ALTER TABLE photos RENAME photos_old; ALTER TABLE photos_new RENAME photos; If it works out, then run DROP TABLE ...


1

If you dropped a 4GB MyISAM table and still have 100% usage of the disk, then something else is clogging up the disk. This is especially true if /var/lib/mysql is on the same mount and /root. Here is what you can do to start investigating: Go to the OS and do this: cd /var/lib/mysql df -h . If the root partition is 100% despite dropping 4GB, your ...


1

You have to understand that the underlying protocol that you're using to send dta(http://en.wikipedia.org/wiki/Transmission_Control_Protocol). The TC-Protocol is designed to deal with network breaks. Ultimately if you send a transmission to the remote server saying, for instance "insert into table (id, name) values (1, 'test');" then the TC-Protocol will ...


1

On Apri 18, 2012, I addressed a similar question : MySQL : Does 'bytes_sent' and 'bytes_received' include mysqldump data? I mentioned the following Bytes_sent : IO Thread requesting binlog entries from the Master Bytes_received : IO Thread reading binlogs entries from its Master Bytes_received : SQL thread reading its own relay logs You ...


0

I managed to find a workaround for this but I am not very happy about it. I installed my previuos setup which was EasyPHP and copied my old MySQL *data* dir into the EasyPHP *MySQL* data directory. This time around the database was able to read the .ibd / .frm files correctly.(even though these files did not change at all) I then used phpMyAdmin to export ...


1

If you execute frequently those kind of queries: SELECT C1 FROM tbl WHERE source = 'x' and state = 'y' Then it might be advisable to build an additional index on both source and state. Maybe this question Should I create an index for non key columns? might help you to understand this concept.


3

Cardinality Even with high cardinality, the tipping point used by the MySQL Query Optimizer is either the key distribution or the storage engine. Back on November 13, 2012, I discussed how lopsided keys can make the Query Optimizer choose different indexes (sometime not choose and index at all) : Must an index cover all selected columns for it to be used ...


1

Export all the views of the database <DB>: mysql -BNe "SELECT TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA = '<DB>' AND TABLE_TYPE = 'VIEW'" \ information_schema | xargs mysqldump --single-transaction --no-data <DB> >views.sql or: mysql -BNe "SELECT TABLE_NAME FROM VIEWS WHERE TABLE_SCHEMA = '<DB>'" \ information_schema | ...


2

Export all the views of the database <DB>: mysql -BNe "SELECT TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA = '<DB>' AND TABLE_TYPE = 'VIEW'" \ information_schema | xargs mysqldump --single-transaction --no-data <DB> >views.sql or: mysql -BNe "SELECT TABLE_NAME FROM VIEWS WHERE TABLE_SCHEMA = '<DB>'" \ information_schema | ...


2

You need to be aware of query results and query behavior with replication running. While there are a minimum of two threads for MySQL Replication, it is the SQL thread that can get in the way of SELECT queries. Why? MyISAM Each time an INSERT, an UPDATE, or a DELETE is executed, a full table locked is issued. That can block SELECTs. The only exception is ...


3

If you have binary logging enabled, you can replay changes that went into creating the stats table, up to the point where you did the TRUNCATE. But you may not have binary logs for all changes to that table from the beginning of time. It's common to have binary logs only back a few days. Any data that was created in that table prior to your oldest binary ...


1

InnoDB Architecture Percona created this picture a long time ago. As you can see, ibdata1 contains many classes of data structures. There are as follows: Table Data Pages (if innodb_file_per_table disabled) Table Index Pages (if innodb_file_per_table disabled) Data Dictionary (Tablespace IDs, Logical-to-Physical Mapping to Tables) Double Write Buffer ...


3

The only reliable way for you to find out yourself is to import a subset and extrapolate. The answer to your question depends on too many parameters how powerful is your machine? does the data have to go through a bottleneck (connection speed, etc.)? how large is your data in each row? how big is the overhead from writing indices? what method will you use ...


1

No, you can't. An index, in MySQL, will either be a BTREE single or multi-column index. a HASH index (available only for MEMORY tables), that can span several columns. a SPATIAL index (available only for MyISAM tables). a FULLTEXT index (available for MyISAM and 5.6.4+ InnoDB tables), that can span several columns of CHAR, VARCHAR or TEXT type. You ...


3

I have something similar with using SELECT IF() statement in MySQL if you are trying not to have procedures. select if ( exists( select distinct index_name from information_schema.statistics where table_schema = 'schema_db_name' and table_name = 'tab_name' and index_name like 'index_1' ) ,'select ''index index_1 ...


0

The only way to create an index is to use CREATE INDEX statement. There are only 2 ways to apply this statement: 1. Empty table and copy data into it 2. To use it on populated table. In Case 2 you need to consider availabe DB space, temp space, log space as well as memory and CPU time. If this table is large for your hardware, then there is a high chance ...


0

If you just wish to create a single index, CREATE INDEX would be the way to go. However, if you wish to setup your table with multiple indices, setting up a new empty table and then copying from your old to your new table is IMO a more practical solution, since you can get everything ready and then simply run the insert when it's convenient.


0

You can deploy JIRA on a existing RDS instance itself you just need to create a DB called "jiradb". I guess you might have to change few DB parameters in RDS instance as JURA needs to be run in "STRICT" type for "SQL_mode" You can refer this documentation link on how to Configure your JIRA server to connect to your MySQL database. ...


0

You can use set profiling=1; and then, later, show profiles; which will give a list of commands and times. See http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html h/t http://ronaldbradford.com/blog/timing-your-sql-queries-2010-07-07/ Please refer these links on how to optimize subqueries: ...


3

I don't see any option in pt-show-grants to do this kind of restriction. There are options for --only and --ignore specific users, but not tables. Nor is there an automatic option to ignore grants on nonexistant tables. I also glanced in the code, and it doesn't seem to break out the tables to scan through them for any reason. For what it's worth, I don't ...


2

ASPECT #1 This sounds like you are suffering from a classic case of bulk insert buffering. (Forgive me if I sound like a doctor). LOAD DATA INFILE takes advantage of a tree-structured bulk insert buffer. The size is set by the option bulk_insert_buffer_size. Please note what that part of the Documentation says: MyISAM uses a special tree-like cache to ...


2

You might need to LOCK the tables before you insert the data into the database and then you unlock after the LOAD statement. There are some tips on MySQL manual as well, check it out: http://dev.mysql.com/doc/refman/5.6/en/optimizing-myisam-bulk-data-loading.html


1

Your question already has the key to your answer. It does depend on row based or statement based replication. STATEMENT-BASED REPLICATION If you run DELETE FROM tblname WHERE blahblahblah; and the rows matching blahblahblah do not exist, no big deal. Replication will just carry on. It just take you a lot longer to realize your data draft on the Slave (or ...


2

I'm assuming because you have SELECT * above, that you are actually using SELECT *. 1. If MySQL were to use the index_promotion_codes_on_t_id_and_gu_id index when ordering on id, then MySQL would have to do the following: Retrieve the primary keys for all 33088 records that match the WHERE clause Search for each of the 33088 records using the primary key ...


1

We hit the same problem on deploying 5.6 as part of our standards we always install from .rpm and relocate the data dir after the first set up . 5.5 and lower we would change our settings in the /etc/my.cnf stop mysql and then tar up the database directories in the default data dir and untar to our new one . As we change all our innodb settings we then got ...



Top 50 recent answers are included