1

Similar questions have been asked, but I have had issues in the past by using

ALTER TABLE tablename MODIFY columnname SMALLINT

I had a server crash and had to recover my table when I ran this the last time. Is it safe to use this command when there is that much data in the table? What if there are other queries that may be running on the table in parallel? Should I copy the table and run the query on the new table? Should I copy the column and move the data to the new column?

Please let me know if there are any best or "safest" practices when doing this.

Also, I know this depends on a lot of factors, but does anyone know how long the query should take on an InnoDB table with ~5.5 million rows (rough estimate)? The column in question is a TINYINT and has data in it. I want to upgrade to a SMALLINT to handle larger values.

Thanks!

Justin B
  • 110
  • 1
  • 8
  • 1
    You can't get an estimate of time taken because no one knows what hard drive you're running. It can take between nanosecond to infinity. It doesn't help at all, but that's about the right estimate. Safest practice is to **never** alter tables. Alternative is to create another table and copy the data from one table to another, then drop the old table. – Mjh Sep 13 '16 at 14:26
  • Thanks for the information. I knew the time estimate question was a stretch. My main concern is doing this without risk. If the safest way to do this is to create another table and copy the data over, then I will do that. – Justin B Sep 13 '16 at 14:30
  • Consider something like https://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html – ceejayoz Sep 13 '16 at 14:52
  • "crash" and "recover my table" -- would you care to elaborate? – Rick James Sep 13 '16 at 21:21
  • InnoDB? file_per_table? – Rick James Sep 13 '16 at 21:23
  • Consider changing to `SMALLINT UNSIGNED`, if appropriate. – Rick James Sep 27 '16 at 05:21

5 Answers5

2

On a slow disk, and with lots of columns in the table, it could take hours to finish.

The ALTER is "safe" because it used to do the following:

  1. Lock the table
  2. Create a similar table, but with SMALLINT instead of TINYINT.
  3. Copy all the rows over to the new table.
  4. Rename the tables and drop the old one.
  5. Unlock

Step 3 is the slow part. The only vulnerability is in step 4, which is very fast.

A server crash during steps 1-3 should have left the old table intact, but possibly left behind a partially created tmp table named something like #sql....

Percona's pt-online-schema-change has the advantage of being virtually lockless.

Rick James
  • 135,179
  • 13
  • 127
  • 222
1

This cannot be easily answered.

It depends on things like

  • Has the table its own file, or is it shared with others?
  • How big is the table in terms of bytes?

etc.

It can last from some minutes to, indeed, some hours and can involve copying over the whole content of the table, so you have quite big needs of disk space.

glglgl
  • 89,107
  • 13
  • 149
  • 217
  • "Its own file" -- mostly irrelevant in InnoDB. Sure, the details are different, but the speed is not much affected. "Shared with others" -- Mostly says that "others will be delayed or even killed". – Rick James Feb 24 '23 at 16:55
0

You can add a new SMALLINT column to the table:

ALTER TABLE tablename ADD columnname_new SMALLINT AFTER columnname;

then copy the data from old column to new one:

UPDATE tablename SET columnname_new = columnname WHERE columnname_new IS NULL LIMIT 100000

repeat above until all records done

then you can drop old column:

ALTER TABLE tablename DROP COLUMN columnname

and finally rename new column:

ALTER TABLE tablename CHANGE columnname_new columnname SMALLINT

you could do the copy of values from old column to new column in batch of 100000 rows, just to be sure not to have any issue

MtwStark
  • 3,866
  • 1
  • 18
  • 32
  • That's more work than altering the old column. It doesn't solve the problem or implement anything reliability-wise. – Mjh Sep 13 '16 at 14:41
  • 1
    the problem was "how to avoid any risk during operation" not "how to do less work" – MtwStark Sep 13 '16 at 14:43
  • Your solution doesn't avoid risks either (as I mentioned). I'm sorry, but this answer is wrong. – Mjh Sep 13 '16 at 14:48
  • Why? adding a column should not be risky, updating a small batch of records should not be risky, dropping a column should not be risky, changing column name should not be risky. what you mean? – MtwStark Sep 13 '16 at 14:55
  • Plus1 - The only thing I would change/remove is AFTER columnname. The field order should not matter. Besides, adding a column is faster than injecting a column – John Cappelletti Sep 13 '16 at 14:57
  • @JohnCappelletti you are right, but my target was to get the final table the same of the original one. if this is not a problem, adding to the tail is the best solution – MtwStark Sep 13 '16 at 14:58
  • Adding a column issues a write with a default value for every record. This places a lock on every row that gets altered. Altering can fail (insufficient disk space, memory allocation failure etc.). Updating issues a write for every value that's different than current one - you have to traverse the table again. Update can fail. Altering a table issues a write for every row. So, what's exactly "safe" here? If you go down this route, it makes more sense to simply alter the column than to do 3x more work with same possible outcome. Does that clear it up? You are not avoiding any risks. – Mjh Sep 13 '16 at 15:02
  • @Mjh once I had a similar problem with a years old log table with over 13 millions record inside.. every operation on the table locked down the entire database.. I used a procedure to delete only 1000 recs at a time and it worked for me with database always online. – MtwStark Sep 13 '16 at 15:04
  • @Mjh divide et impera, 3 x work but each work is 1/3 of the total.. we are adding a column with nulls not values, we update a batch of rows, not all.. – MtwStark Sep 13 '16 at 15:06
  • Well, you and your problems aren't any sort of referential points. No one knows who you are, what your servers were, what your setup is, what your skill level is etc. I'm not trying to insult you, but these are the facts. Just because this solution worked for you, it **still** doesn't mean it's any safer than altering the table. What I wrote can be verified. What you wrote - cannot. I'll repeat once more - **your solution is as unsafe as altering the column**, and it **will** produce more work. I don't plan to debate on this any more, whoever finds this should read my comments as well. – Mjh Sep 13 '16 at 15:07
  • Your solution is _more_ unsafe -- because it takes so many steps. – Rick James Sep 13 '16 at 21:11
  • @RickJames not true, many low-risk operations are safer than a single massive (HUGE) operation at high potential risk. baby steps.. pardon the colorful language but, when I'm in the bathroom I prefer to drop 10 pieces of poo, 100 grams each rather than just a one kilogram **BIG ONE** – MtwStark Sep 14 '16 at 07:55
  • That `UPDATE` will be less invasive, but it will take a lot longer. There are two reasons. Update must write undo records. Each iteration will be slower and slower due to stepping over the already-done rows. – Rick James Feb 24 '23 at 16:57
0

I would add a new column, change the code to check if a value exists in the new column and to read/write it if it does. Also change the code to read from the old column and write to the new column. At this point you can migrate the data at will, copying over values from the old column into the new column where a value does not exist in the new column.

Once all of the data has been migrated you can drop the old column.

Brian Leeming
  • 11,540
  • 8
  • 32
  • 52
0

I modified 1 column in the table has ~6 millions form int to double in 18 minutes

My server has 8 core, 16GB ram, use SSD.

6,192,515 rows affected in 17 m 50 s 81 ms
Loc Nguyen
  • 1
  • 1
  • 1
    As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Apr 17 '23 at 03:31