Tag Info

New answers tagged

0

I performed the DROP command and it was instantaneous. I did not attempt to wrap the DROP and ALTER PROC in a transaction however.


6

No sane DBA will ever allow such a procedure. This is a SQL injection privilege escalation vector. I can pass in the tablename 'x; exec sp_myfoo;' and voila. There are basic issue: table names are NVARCHAR, not VARCHAR table names are length 128, not 60 sysname is a handy type to represent object names, is an alias for NVARCHAR(128) tables are qualified ...


2

No, it is executing TRUNCATE TABLE in the context of the current owner of the module. So, as long as the owner of stored procedure has the ALTER TABLE permission, it will work. Anyone who has CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created, can create the procedure. Any member who has ...


0

If I understand it correctly, A transactional snapshot is taken of the chunk (~0 time) The copy of that snapshot (old data) takes place (500ms) Meanwhile, the trigger is hit by your UPDATE, and takes care of updating both the old and new tables. (This is where I get fuzzy.) The bottom line is "trust it".


1

pt-online-schema-change takes advantage of the consistency nature of relational databases at table level. Let's see what happens when we write to the original table, one of three cases: The chunk has already been copied. No problem, the trigger will overwrite the values of the accessory table within the transaction The chunk has not yet been copied. The ...


2

This is exactly what ROW_NUMBER() window function can do: select id, version_date, row_number() over (partition by id order by version_date) as version from your_table ; I don't see a reason to have these version numbers stored in a column of the table. It would be fairly easy to insert the values the first time but quite complicated to keep ...


0

ALTER TABLE .. ADD COLUMN must copy the table over and rebuild the indexes. This means that it needs about as much extra space as the table requires. It may not seem like the disk ever got full, but it may have, then got freed up when the error occurred. Also, depending on the setting of 'tmpdir', it may be running out of room in the temp directory. ...


0

While the CREATE TABLE is a strange error, and should be investigated further and/or reported to see if the failback operation should be done for Workbench, your real problem is the first one: ERROR 1114: The table 'Analysis' is full This suggests that the engine cannot alter the table because it has run out of space. This doesn't necessarily mean that ...


0

If you have a lot of records in your table, take a look at my answer to a related question here. With respect to MySQL Workbench, I have always found the tool to be excellent. Having said that, what MySQL Workbench 'may' be doing in the background is creating an empty temporary table with the extra field, copying over all the records, the deleting your ...


0

So, if I've understood your log file correctly, you attempt to add a new column to your 20 million row table, the system crashes and then is rebooted by the mysqld_safe daemon? Please tell me that you're not really going to call it "newColum" in production. What's strange (to me at least) is that your crash doesn't appear to be related to a time-out - you ...


0

Check your memory usage or, more probably, your disk usage (is there enough free space during the process?). Altering a table may require either a large amount of memory or a copy on disk of your table. Changing the alter algorithm from INPLACE to COPY can be even faster in your particular case. You may also be hitting the innodb_online_alter_log_max_size ...


3

My guess is that your trigger has a "insert into ... select * from myTable" kind of structure, and now it's broken because there's one more column in the table. Change your trigger so that it lists the columns instead of using *.


2

To elaborate on Josh's correct and excellent answer: Is it safe for us to outright cancel our ALTER TABLE query? Yes. It would be safe even if it was in the middle of rewriting the table. If you wanted to you could just shut down the whole PostgreSQL server, or in fact the machine it runs on, restart it, and everything would be fine. DDL in ...


4

As far as I understand, the fact that our query is waiting for a lock means it has always been waiting for a lock, and it has never changed anything. Right -- if you see that pg_stat_activity.waiting is "true" for an ALTER TABLE, that almost certainly means that it's patiently waiting for the ACCESS EXCLUSIVE lock on its target table, and its real work ...


0

If I inherit a database and try to hunt down where DML is coming from, I usually right click the database in SSMS and use the 'Generate Scripts...' menu option and then select Stored procedures and UDFs to be scripted. You can save it in a file or into a new query window and then do a string search for your table. You should also check any link server ...



Top 50 recent answers are included