Hello.
Use such alter statement:
alter table mytable change a a int default '1';
Every thing works fine on the MySQL 5.0.3 (from the latest bk source):
mysql> CREATE TABLE `mytable` (
->
-> `a` int(11) NOT NULL,
->
-> `b` int(11) NOT NULL,
->
-> `c` int(11) default NULL
->
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
-> ;
Query OK, 0 rows affected (0.19 sec)
mysql> insert into mytable (b, c) values (2, 3);
Query OK, 1 row affected (0.01 sec)
mysql> select * from mytable;
+---+---+------+
| a | b | c |
+---+---+------+
| 0 | 2 | 3 |
+---+---+------+
1 row in set (0.01 sec)
mysql> alter table mytable change a a int default '1';Query OK, 1 row affected
(0.23 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show create table mytable\G;
*************************** 1. row ***************************
Table: mytable
Create Table: CREATE TABLE `mytable` (
`a` int(11) default '1',
`b` int(11) NOT NULL,
`c` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> insert into mytable (b, c) values (2, 3);
Query OK, 1 row affected (0.02 sec)
mysql> select * from mytable;
+------+---+------+
| a | b | c |
+------+---+------+
| 0 | 2 | 3 |
| 1 | 2 | 3 |
+------+---+------+
2 rows in set (0.00 sec)
[snip]
I am using MySQL 5.0.2 on a Redhat 9 box. I am having problems altering
some columns default value and having it show up in the 'show create
table x' and backup files. Here is my repeatable example:
I issue this create statement:
create table mytable (
a int not null,
b int not null,
c int
);
Then I issue this alter table statement:
alter table mytable
alter a set default '1';
Then I issue:
Show create table mytable;
The result is:
CREATE TABLE `mytable` (
`a` int(11) NOT NULL,
`b` int(11) NOT NULL,
`c` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
However, the behavior works properly.
For instance:
insert into mytable (b, c) values (2, 3);
select * from mytable;
produces:
1, 2, 3
I realize of course that I can simply put the default value clause in my
create statement, but I don't want to do that because the entire
database has already been established and is up and running in a
production environment.
Am I doing something wrong or is this a bug? Whatever the case, what is
the fix?
Thank you,
Jason McAffee
The Technology Group
[snip]
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
