Skip to main content
Share Your Experience: Take the 2024 Developer Survey
52 votes

MySQL: How to create Column if not exists?

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);
Paroofkey's user avatar
  • 621
23 votes
Accepted

MySQL: How to create Column if not exists?

MySQL ALTER TABLE does not have the IF EXISTS option. You can do the following in a stored procedure or a program if this is something that you'll need to do on a regular basis: Pseudocode: Find if ...
thatsaru's user avatar
  • 688
15 votes

MySQL: How to create Column if not exists?

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 = ...
Anto Jose's user avatar
  • 151
7 votes
Accepted

MySQL and AUTO_INCREMENT columns in multiple-column index for InnoDB tables: support status?

Under normal circumstances, I would have closed this question as a duplicate. There are already close votes. In this particular instance, since you put a bounty on this question, I'll try to answer it ...
RolandoMySQLDBA's user avatar
4 votes

Can I make sure two columns dont have the same value

Good News!! Mysql supports CHECK CONSTRAINT https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html
Jorge Wander Santana Ureña's user avatar
4 votes

Why use WHERE 1 or WHERE 1=1?

Using "where 1=1" reduces the complexity of the code needed in dynamic sql 'where' clause generation. Otherwise, when creating the 'where' clause you would need to check if this is the first component ...
Niall's user avatar
  • 41
4 votes

Does MySQL have a version of Change Data Capture?

I recommend you take a look at Debezium an open-source CDC platform which, amongst others, also supports MySQL. You can use Debezium to stream changes into Apache Kafka, but by means of its embedded ...
Gunnar's user avatar
  • 141
4 votes

Does MySQL have a version of Change Data Capture?

Recently I had an academic project where I needed to use CDC with MySQL. There are a few tools available in the market. I was able to create a POC by using Maxwell CDC. A unique feature of Maxwell is ...
Anurag Kale's user avatar
4 votes

Will a mysql db import be interrupted if my ssh session times out?

If the SSH drops without putting the mysql session into the background (along with nohup), then the DB connection is at the SSH session's mercy. So, yes, mysql would care about the lifecycle of the ...
RolandoMySQLDBA's user avatar
4 votes

MySQL: How to create Column if not exists?

This below worked for me: SELECT count(*) INTO @exist FROM information_schema.columns WHERE table_schema = 'mydatabase' and COLUMN_NAME = 'mycolumn' AND table_name = 'mytable' ...
Tejpal Sharma's user avatar
4 votes
Accepted

Is there a "soft" ORDER BY / GROUP BY?

SELECT s.* FROM stackoverflow s, ( SELECT reference, MIN(time) time FROM stackoverflow GROUP BY reference ) o WHERE s.reference =...
Akina's user avatar
  • 20k
3 votes
Accepted

Will hacking away ibdata1 destroy databases with only MyISAM tables?

Since you are using MySQL 5.1.73, you can blow away the ibdata1 file without worries. First, let's look inside ibdata1 (picture created by Percona CTO Vadim Tkachenko) STEP 01 : Make sure there is ...
RolandoMySQLDBA's user avatar
3 votes

Should MySQL database be in dedicated server?

The argument against a dedicated server is invalid, if the microseconds that account for the delay when talking to a different server matter you would not be asking about MySQL. But there is one ...
Twinkles's user avatar
  • 2,361
3 votes

count null values of table from information_schema tables

No, you cannot use information schema for this. Information_schema.TABLES only stores an (approximate) count of the number of rows in a table. Not counts of values or nulls. You should have an index ...
ypercubeᵀᴹ's user avatar
3 votes

Why is a 'Select distinct limit 100' using tmp tables?

There are multiple ways that SELECT DISTINCT col FROM ... LIMIT 100 can be performed: Case 1: Create a temp table with all rows of col, sort it, then peel off the first 100 (de-dupping as it goes). ...
Rick James's user avatar
  • 78.4k
3 votes

Repeatable Read: Which one is correct?

While many database systems will actually behave like your first version, for MySQL, version 2 is the expected behaviour, see the documentation about repeatable read: This is the default isolation ...
Solarflare's user avatar
  • 1,251
3 votes
Accepted

Host 'host_name' is blocked because of many connection errors. Unblock with 'mysqladmin flush-hosts'

I was able to resolve my issue with following: # mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 216305 Server version: 5.1.73-log Source distribution ...
alexus's user avatar
  • 625
2 votes

Is there a progress indicator for OPTIMIZE TABLE progress?

There is a simpler way, if you have access to the file system. MySQL generates a .TMD file; you can watch the size of the file growing; it should reach more or less the size of your existing MYD when ...
Pierre-Olivier Benoit's user avatar
2 votes

MySQL: fetch multiple global status and system variable in a single query

Remember that in MySQL 8 the global_status table has been moved to the performance_schema, therefore you should query: SELECT * FROM performance_schema.global_status WHERE VARIABLE_NAME like '...
Diego T's user avatar
  • 81
2 votes

How to restore MySQL database from Files

install the new MySQL create the db you want, let's say DB_NAME_RESTORE go to /var/lib/DB_NAME_RESTORE and copy the data files there chmod on the files if you have to
ninjabber's user avatar
  • 263
2 votes

Will a mysql db import be interrupted if my ssh session times out?

Little bit late for the party, But myself struggled with this issue and found a solution and wanted to share. Solution 1 Using screen is ideal for this situation as your processes will run even ...
Sachi.Dila's user avatar
2 votes

MySQL replication error 1594

In my case, it seems I had the same kind of problem: mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to ...
knocte's user avatar
  • 251
2 votes
Accepted

MySQL Front - Workbench

After reseting root password, I could connect with workbench. I still can't connect with old users (which were already created), but connecting with root and creating new users with workbench allows ...
Alisson Reinaldo Silva's user avatar
2 votes

adding a column with default value to slow in mysq 5.1

That's true. In 5.1 (and 5.5), adding a column (of any type) to a table required locking the table and copying the entire table over, plus rebuilding all the indexes. 5.6 improves significantly on ...
Rick James's user avatar
  • 78.4k
2 votes
Accepted

Increasing column from VARCHAR(255) in master/slave setup

In a replication environment, for an ALTER that takes a long time, I recommend this: On one slave: Take out of rotation (keep clients from using it); perform the `ALTER; put back into rotation. ...
Rick James's user avatar
  • 78.4k
2 votes
Accepted

MYSQL parallel query performance issue

Upgrade to 5.5, then 5.6, then 5.7. You will be able to run more simultaneous queries at each upgrade. Do you really mean "5.1.1"? That was a pre-release for 5.1 from about 2005. Use summary tables ...
Rick James's user avatar
  • 78.4k
2 votes

Is there a "soft" ORDER BY / GROUP BY?

Starting from version 8.0, MySQL supports window functions, including window aggregate functions. If you can afford upgrading your MySQL instance to version 8.0, you can solve your problem with a ...
Andriy M's user avatar
  • 23k
2 votes
Accepted

MySQL "bad handshake" after entering password

There is old Bug Report about MySQL Workbench connecting to older databases. Bug #91828 : Bad handshake since 8.0.12 Others have come across this Can't connect to MySQL server on MySQLCC ERROR 1043 ...
RolandoMySQLDBA's user avatar
2 votes
Accepted

Latest version to support MySQL 5.1.69 ib* files

You should be able to load it with 5.5. As a general rule, next version up should always work. You should be able to run mysqldump from there, or run mysql_upgrade to keep it as is for 5.5. Then if ...
Gordan Bobić's user avatar
1 vote

Skipping chunk X of Y.Z because MySQL chose no index instead of the word_sid_typeindex

The message is both expected and disturbing. WHY EXPECTED ? I don't find this message disturbing at all. I would expect pt-table-checksum to bypass chunks since there is no defined uniqueness. Granted,...
RolandoMySQLDBA's user avatar

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