Hi all,
This was resolved by implementing the strict_fields option for
runall.pl:
--strict_fields: Disable all AI applied to columns defined in
$fields in the gendata file. Allows for very specific column
definitions
See the email below for the original problem.
This basically resolves the issue listed there, since when this
option is enabled, no column AI (like the example shown below) will
be applied.
This allows very specific column definitions without having to think
about how RQG will change the column definitions.
On 27/10/2011 3:01 PM, Roel Van de Paar wrote:
Hi all,
In /randgen/lib/GenTest/App/Gendata.pm:340 this block exists:
if ($field_copy[FIELD_TYPE] =~ m{timestamp}sio ) {
if (defined $self->[GD_NOTNULL]) {
$field->[FIELD_SQL] .= ' NOT NULL';
} else {
$field->[FIELD_SQL] .= ' NULL DEFAULT 0';
}
}
This results in the following behavior:
#1 When a timestamp field is created, and RQG is running with
--notnull, the resulting timestamp column will be created like
this:
`col_timestamp_not_null` timestamp not null NOT NULL,
(the twice not null is likely since --notnull enables
adding 'not null' and the second timestamp check adds another
'NOT NULL')
#2 When a timestamp field is created, and RQG is
running without --notnull, the resulting timestamp column will
be created like this:
`col_timestamp` timestamp NULL DEFAULT 0,
Conclusions:
#A The addition of 'NOT NULL' for timestamp columns when using
--notnull seems excessive/not needed
#B The addition of 'NULL DEFAULT 0' gives two issues:
#B.1 When wanting to use a secondary clause on the timestamp
column in the types field, like this (pseudo code):
$fields = {types => ['timestamp default
NULL'],};
It will result in the DEFAULT value
being overwritten for runs without --notnull:
`col_timestamp` timestamp default NULL
NULL DEFAULT 0,
Proof of concept (the last DEFAULT
clause always overwrite the previous ones): Two different tables
resulting:
mysql> create table time4 (id
timestamp null default 0 default null);
Query OK, 0 rows affected (0.01 sec)
mysql> show create table time4\G
Create Table: CREATE TABLE `time4` (
`id` timestamp NULL DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
mysql> create table time5 (id
timestamp null default null default 0);
Query OK, 0 rows affected (0.01 sec)
mysql> show create table time5\G
Create Table: CREATE TABLE `time5` (
`id` timestamp NULL DEFAULT
'0000-00-00 00:00:00'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
#B.2 This is limiting test coverage.
As per the example above, a timestamp column can be
created as DEFAULT NULL instead of DEFAULT
'0000-00-00 00:00:00' (i.e. DEFAULT 0).
But, since "DEFAULT 0" is always automatically
added when not using --notnull, we would never be able get:
- DEFAULT NULL (when using --notnull) - since it would
always add "DEFAULT 0" at the end resulting in "DEFAULT
'0000-00-00 00:00:00'"
- DEFAULT CURRENT_TIMESTAMP [ON UPDATE
CURRENT_TIMESTAMP] (auto added to the first timestamp field in
any table) (when using -notnull)
for the same reason
etc.
I think I understand however why this block was implemented. It
is like because one can also specify a default clause in the $fields
block.
And, if one specifies a timestamp type, with a wrong
default clause - let's take for instance:
mysql> create table time4 (id timestamp default null);
ERROR 1067 (42000): Invalid default value for 'id'
then table creation would fail.
So - my question is - how can we change this block to make it
more versatile (i.e. allowing one to specify things
like CURRENT_TIMESTAMP as a default, and also various
default clauses even from the types array)
without loosing [much] table creation success?
Thanks for any input you can provide. Changing this will improve
RQG in terms of timestamp handling - which is presumably
something which is often used by customers.
--
Kind regards,
God Bless,
Roel Van de Paar | Senior QA Engineer
Mobile: +61 0400 225 827
Oracle MySQL Server QA
Oracle Australia | NSW 2440
Oracle is committed to developing
practices and products that help protect the environment
|
_______________________________________________
Mailing list: https://launchpad.net/~randgen
Post to : [email protected]
Unsubscribe : https://launchpad.net/~randgen
More help : https://help.launchpad.net/ListHelp
