Tag Info

Hot answers tagged

13

Basically it is just for programmer convenience since you can just add additional conditions with AND... after that and it has no impact on execution time. Check out these links to Stackoverflow: Why would someone use WHERE 1=1 AND in a SQL clause? “where 1=1” statement Note that WHERE 1 is identical to WHERE 1=1; both mean WHERE TRUE but the former is ...


11

Oracle are obliged to fix security bugs and updates for MySQL 5.1 until the Premium support period has ended - remember that there are paying customers out there using the product in production. See page 13 of this document for support end dates by MySQL version. MySQL 5.5 is more than ready for production use & I'd recommend using that for any new ...


9

I assume you are asking about columns with the AUTO_INCREMENT property. No, deleted rows is not the only reason, there are several others: inserts that did not succeed (due to duplicate unique or primary key violations or other reasons) inserts inside transactions that did not commit and were rolled back. inserts where the id was explicitely defined (and ...


9

FACTS You said you are using ext4. File size limit is 16TB. Thus, Sample.ibd should not be full. You said your innodb_data_file_path is ibdata1:10M:autoextend. Thus, the ibdata1 file itself has no cap to its size except from the OS. Why is this message coming up at all? Notice the message is "The table ... is full", not "The disk ... is full". This table ...


5

To rotate out logs more than 7 days old, add this to my.cnf [mysqld] expire-logs-days=7 then restart mysql. To perform this manually, run this command: mysql> PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 7 DAY) + INTERVAL 0 SECOND; This will erase binary logs back to Midnight 7 days ago. WARNING !!! Do not erase binary logs from the OS level ...


5

Remove the HAVING COUNT(*) > 0. It's useless, no row will have a count of 0 after a group by. Change the GROUP BY to: GROUP BY client_id. Grouping by institution_id is not needed, you already have a WHERE condition that narrows it one value. As @HLGEM suggested, remove the select * and use a list of fields that you need. Right now you are repeating data ...


5

Here are some of my recommendations for InnoDB. In my experience, the buffer pool size is the most important because the more data you can keep in cache, the less time your system will spend using disk IOs. http://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html This is the buffer pool, where data and index are cached innodb_buffer_pool_size=(In ...


5

From your paths, I can safely assume the following: You are running MySQL in Windows You used the MySQL MSI to install What you need to do is establish the file my.ini When installing MySQL for Windows using the MSI, the location of my.ini is expected to be C:/ProgramData/MySQL/MySQL Server 5.1. Please run the following in a DOS Window: cd ...


5

Just altering the lower_case_table_names setting isn't enough. It needs to be done before you import your database(s). The MySQL 5.1 documentation lists a procedure for moving between Windows and Linux/UNIX. This will ensure that your desired rules for enforcing case sensitivity are followed. Take a look and verify that you did these steps in the correct ...


5

It will be handled immediately on mysql startup. You do not have to wait for a mysql restart. First, set expire_logs_days to be 10 in /etc/my.cnf [mysqld] log-bin server-id=1 binlog-do-db=foodb1 binlog-do-db=foodb2 expire_logs_days=10 Next, log in to mysql and run this PURGE BINARY LOGS BEFORE (date(now()) + interval 0 second - interval 10 day); Note ...


4

Without seeing your table structure, I believe part of your problem is you are not currently joining the tables on a particular column. Your current query is generating a Cartesian result which means that if leads has 17 rows and leads_cstm has 10 rows the result could be up to 170 rows because each row in the first table will match to each row in the ...


4

I was programming a bunch of user defined functions in C++/C for PostgreSQL that were used by other people in a large company (10K+ people). My functions have an optional where parameter: if no value was given, the clause was not used. This was explicitly documented. Unfortunately, nobody used this feature and everybody where just supplying where 1=1 clause. ...


4

If you want to return both the total number of logins and the total number of sessions that are less than 10 minutes, then you can get the result using your current query with some minor changes. You can move the where clause filter inside of a CASE expression to get the count of the sessions less than 10 minutes. Then just add a second count() to get the ...


4

In order to perform an upgrade with (almost) no downtime, you need to add some extra infrastructure for high availability. The most common way to perform that is with the help of MySQL built-in replication: Setup a slave with a clone of the data of the original master. It is technically possible to setup a master-slave replication within the same node by ...


3

Seconds_Behind_Master is based on the difference between UNIX_TIMESTAMP() and the timestamp logged for the query within the binary log of the master, or the relay log on the slave. Seconds_Behind_Master actually gets you lost in context of realtime if replication processes a series of long running queries. The lag could grow astronomically until all relay ...


3

innodb_lock_wait_timeout The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction will hang for at most this many seconds before issuing the following error: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction When a lock wait timeout occurs, the current statement is not ...


3

Make sure that you have at least 2 unused API slots ([mysqld] sections) in your configi.ini file (and perform a rolling restart of your Cluster after adding them). These slots will then be used by memcached. Note that you can choose to run mysqld processes on the host as well and have them part of the cluster (can access the same data through SQL and NoSQL) ...


3

The problem is, when you GROUP on user,foo,bar you are not able to get a list of each of those times to compare. Something like this might work, but no idea on the performance on a large table. You might want to only do this on a subset (ie tuples just from the last 30 days) SELECT A.*,B.time FROM reqs A INNER JOIN (SELECT user,foo,bar,time FROM reqs) B ...


3

While I would normally not recommend this To download and install MySQL 5.1.34, run these commands at your Linux prompt AS IS... MYSQL_VERSION=5.1 MYSQL_VERSION_RELNUM=34 MYSQL_VERSION_RELEASE=${MYSQL_VERSION}.${MYSQL_VERSION_RELNUM} REDHAT_RELEASE=5 BINARY_VERSION=x86_64 PERL_LOCATION=ftp://ftp.de.netclusive.de/pub/parallels/Plesk/Expand/2.3.1/packages ...


3

Your grants are not replicating because you have an (unnecessary) replication filter on INFORMATION_SCHEMA. There is no reason to exclude this, as it is not replicated anyhow. When replication filters are in place, you have to USE a database or your statements are not replicated. For example...not replicated Grant all privileges on *.* to foo@localhost ...


3

First of all, thank you posting the DELETE query and the table layout With regard to your question, there is nothing you can really do because a rollback is being done via the UNDO tablespace inside ibdata1 (it should have grown immensely). If you kill the mysqld process and restart it will just pickup where it left off as part of the crash recovery cycle. ...


3

It doesn't come down because it isn't supposed to come down. Max_used_connections is a status variable, and is defined as "the maximum number of connections that have been in use simultaneously since the server started." It turns out, you can also reset the value to the current number of connections using FLUSH STATUS. But that isn't a "partial ...


3

I'm guessing you're getting varying row count in the table_row column? This is expected behaviour for InnoDB-tables. "For InnoDB tables, the row count is only a rough estimate used in SQL optimization. (This is also true if the InnoDB table is partitioned." http://dev.mysql.com/doc/refman/5.1/en/tables-table.html There is more information here: ...


3

Conditions with OR are harder for the optimizer than conditions with AND only. Two or more range conditions (>, >=, <, <=, BETWEEN, LIKE 'search%') are harder than conditions with equality only or with only one range. Your query has both the above difficulties. Noticing that it is equivalent to this rewriting: WHERE ( languageId = 3 AND ...


3

MySQL's sql_mode setting allows you to enforce or relax ANSI standards for SQL processing. In your case, MySQL will regress in behavior and results. In what sense ? MySQL 5.1 version features will stay, but how the SQL is processing changes. Here is proof: MySQL 5.0 first introduced Stored Procedures to the MySQL realm. ...


3

There is most definitely a difference between SHOW STATUS; and SHOW GLOBAL STATUS; SHOW GLOBAL STATUS; will give you status variables that have updated since mysqld started for all sessions that are connected or have ever been connected. SHOW STATUS; will give you status variables that have updated within your session. The command can also be expressed as ...


3

This consider all database names are different on all server. As you said all servers are mysql 5.1, You can take mysqldump on each server and restore it on new server Step 1 : Take dump from all old server like below Everything written in one file: table structures, indexes, triggers, stored procedures, users, encrypted passwords. mysqldump -u... ...


3

You find the explanation of your question in the docs: "If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored." For your purpose it would be sufficient to leave auto_increment_increment and auto_increment_offset on their default values and simply run: ALTER TABLE test ...


2

Two servers are not allowed to have the same server_id. Also, If you omit --server-id from a master, the default ID is 0, in which case a master refuses connections from all slaves, and a slave refuses to connect to a master. For more information, see Section 15.1.1.2, “Setting the Replication Slave Configuration”. Even if both servers have different ...


2

If you cannot free up or add any disk space, you could copy the table_data.* files (MYD,MYI,frm,etc) to another machine with plenty of free disk, run the repair there with myisamchk, then copy the files back to the original machine. If the server needs to stay running, do a FLUSH TABLES WITH READ LOCK table_data before copying the data to the other server ...



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