Very Big Mysql problem !

Anon

hi all,
iam using php and mysql
actually i have a table caleld report with 3 fields:
1.date stored as varchar(30)
2.username varchar(30)
3.task text
none of them is s a primary key also

i wanted to change the date column to something other name such as repdate and also the column type of date field to 'date'

i executed the following command;

alter table report change date repdate varchar(30);
alter table report modify repdate date;

it was succesful and now the date coulmn became repdate and stored as date

but whatevr existing date was there are changed to 0000-00-00
iam shocked,all my dates are gone
nearly 200 rows i had and all the dates are now zero zero

how do i get back my dates back?
please help me

Regards
roopa

[deleted]

"how do i get back my dates back?"

You can't, you'll have to restore from a backup. You did make a backup before doing something as dangerous as changing the database structure right? evil grin

A forum, a FAQ, what else do you need?

Time to YAPF!: http://www.hvt-automation.nl/yapf/

mwilson

Erm, I would say you could rollback the change, but it sounds like you have already commited the changes to the database. In short, without a backup of the data you won't be able to get it back.

I hope for your sakes that i'm wrong, i've had mistakes like this before and they're not pretty.

Matt

[deleted]

Rollback a database structure change? uhm... is that even possible?

mwilson

I believe in Postgresql (possibly MySQL too, although i've never used it) there is a function called COMMIT() and ROLLBACK(), once a query (of any type, such as INSERT of UPDATE) is executed, you can either COMMIT() it to storage or ROLLBACK() the query and get back to the original data.

Matt

Anon

hi all,
I tried ROLLBACK but i got a warning saying taht
cannot rollback non-transcational tables
so there is no way i guess.
Regards
roopa

Anon

Does mysql have a syntax like postgresql does where you can do this:

'select * into newtable from oldtable'

that will create a new table layed out just like the old one with the data in it, but no contraints or indexes.

It's a quick and dirty way to make sure you've got a backup of your stuff before doing dangerous things like changing data format etc...

[deleted]

MySQL only supports transactions when using InnoDB tables.

But my point is more: is it possible to rollback a query that changed the database structure?

PostgreSQL does not have ALTER TABLE statements that can change the datatype of a column, so it can't roll that back.

On the other hand, MySQL is such a complete piece of crap that it probably can roll it back.

But there is one reason why it wouldn't work anyway: a rollback can only be done if a transaction was started. And if Roopa started a transaction I would have known that he could do a rollback to undo the changes. And even if he did, he most likely would have done a commit before checking the results.

If he had not done a commit, the changes would have been rolled back automatically when the connection was closed.

Anon

Vincent, be nice! :-) MySQL is written by people who love it. They may or may not understand database theory like the core team working on Postgresql or Oracle, but they're trying.

That said, I find it useful as a mostly read, little write, content management data store type thing. Not a database meant to handle large financial transactions or put a man on the moon.

But anyway, this brings up a vital skill a DBA must aquire quickly in his career, preservation of data. Any time you're gonna do something a little bit dangerous, what do you do? You back up your data. You shouldn't expect your database to save you from your own mistakes, because often, like this situation, it just doesn't happen.

[deleted]

"Vincent, be nice! :-)"

It's hard to be nice about MySQL. :-) my pet peeve...

"MySQL is written by people who love it. They may or may not understand database theory like the core team working on Postgresql or Oracle, but they're trying."

... trying, and with the help of InnoDB they are even getting somewhere.

I just wish that they would stop focussing on speed and start putting some usefull functionality in there.