41

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 ...


33

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 ...


29

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 ...


26

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 C:/...


15

My main use is that it makes it easier to comment out stuff during development of queries. I lead with ,'s and and's: SELECT A -- ,B ,C ,D -- ,E FROM TABLE WHERE 1=1 -- and B='This' and C='That' -- and D is not null Also makes it easier to programmatically tack stuff unto the end. this = "SELECT * " this += "FROM TABLE " this += "...


14

I usually use mysqlcheck with --optimize and --databases combination. mysqlcheck is a command-line interface for administrators to check, optimize and repair tables. mysqlcheck -uroot -p --optimize --databases myDatabase Here is the MySQL doc : http://dev.mysql.com/doc/refman/5.5/en/mysqlcheck.html Max.


12

According to the docs if you ommit the @'hostname' (that is CREATE USER 'name') MySQL will interpret it as it had a @'%'. The error message you provided suggests that there is already a user 'name'@'%' in the system: mysql> CREATE USER 'name'@'%' IDENTIFIED BY 'test'; Query OK, 0 rows affected (0.04 sec) mysql> CREATE USER 'name'@'%' IDENTIFIED BY '...


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 ...


11

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 ...


10

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 ...


10

There is nothing MySQL has that will indicate the progress of OPTIMIZE TABLE;. You may have to go the OS using Windows Explorer and look for a growing tmp table. Hint: Temp table are MyISAM tables that do not have a .frm file with the name #sql-9999.MYD and #sql-9999.MYI. While these two fle exist, the query would be considered in progress. To know how far, ...


10

Why we do so? Dynamic code generation written by not too competent programmers comes to my mind. Generates SELECT.... WHERE and then you have to have SOMETHING.... so instead of adding the WHERE only when needed, they add a non-limiting condition when none is there. Seen that - responsible for firing the "specialist". Or the guy just things WHERE is ...


10

Actually exists now for Maria DB 10.219 ALTER TABLE test ADD COLUMN IF NOT EXISTS column_a VARCHAR(255); Bonus, it works for MODIFY as well ALTER TABLE test MODIFY IF EXISTS column_a VARCHAR(255);


9

Just off the bat, newer versions of MySQL actually improve innodb performance (especially 5.5). I would highly recommend updating to this version if you're going to run InnoDB. One method you could use to hunt down why it's taking so much longer is using MySQL Profiles mysql> SET PROFILING=1; mysql> SHOW TABLES; mysql> SELECT * FROM foo; mysql> ...


9

You may find this surprising, but MySQL 5.1 may outperform MySQL 5.5 under certain circumstances. Percona performed a bake-off among multiple releases of MySQL MySQL 4.1 MySQL 5.0 MySQL 5.1 (with built-in InnoDB) MySQL 5.1 with InnoDB-plugin MySQL 5.5 MySQL 5.6 All tests were performed with MySQL unconfigured (In other words, no my.cnf was made). The ...


8

Using MySQL 5.5 out-of-the-box without proper configuration is like getting a Lamborghini and expecting topnotch performance on a gallon of regular gasoline (87 Octane). You should expect better performance with high octane gasoline in a Lamborghini. As with any database product, it is only as performance-enhanced as you actually configure it. Just like ...


8

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 Mb,...


8

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 ...


8

No, you can't. In most DBMS (Postgres, SQL-Server, Oracle, DB2 and many others), you can just add a CHECK constraint: ALTER TABLE foo ADD CONSTRAINT aa_cannot_be_equal_to_bb_CHK CHECK (aa <> bb) ; I don't see any way to have this in MySQL, using only referential constraints. Besides triggers, you could allow the two columns to have equal ...


7

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 ...


7

Back on January 21, 2009, Peter Zaitsev stated the following on mysqlperformanceblog.com As the call for action – I would surely like someone to see if EXT3 can be fixed in this regard as it is by far the most common file system for Linux. It is also worth to investigate if something can be done on MySQL side – may be opening binlog with O_DSYNC flag if ...


7

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 ...


6

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 ...


6

The maximum value for your auto_increment 'seq_number' field is 16777215. You should make this field use an int or a bigint if you need more that 16,777,215 records. See the MySQL Documentation for info about the mediumint(9) format. https://dev.mysql.com/doc/refman/5.0/en/integer-types.html


6

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. http://mike.kruckenberg.com/...


6

ISSUE #1 : Upgrade Path Your grant tables can get screwed up because you are leaping two versions instead of one. I just helped someone a week ago because they did just that (MySQL service stops after trying to grant privileges to a user). The solution for that question was to manually fix the mysql.user. Rather than going through that heavy-handed route, ...


6

You can use this solution, already mentioned on another StackOverFlow post: (Ref.: https://stackoverflow.com/a/31989541/) MySQL - ALTER TABLE to add a column if it does not exist: SET @dbname = DATABASE(); SET @tablename = "tableName"; SET @columnname = "colName"; SET @preparedStatement = (SELECT IF( ( SELECT COUNT(*) FROM INFORMATION_SCHEMA....


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

You can try to use the REPAIR TABLE myTable USE_FRM "Probably" your .MYI file is missing or have corrupted headers This option tells MySQL not to trust the information in the .MYI file header and to re-create it using information from the .frm file However before proceed check the documentation of your MySQL 5.1 version and make a backup copy of your ....


5

This is a known and well documented issue: You cannot refer to a TEMPORARY table more than once in the same query. For example, the following does not work: mysql> SELECT * FROM temp_table, temp_table AS t2; ERROR 1137: Can't reopen table: 'temp_table' This error also occurs if you refer to a temporary table multiple times in a stored ...


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