Description: ------------ Hi there, Why MDB2_Schema insists on defining a default value (should a default not be supplied) for columns that are marked NOT NULL? Text columns are treated of having a default value of ' ', and integers of having a default value of 0. For example, I have the following table definition: <table> <name>shop_cat_products_attrib</name> <declaration> <field> <name>product_id</name> <type>integer</type> <unsigned>true</unsigned> <length>4</length> <notnull>true</notnull> </field> <field> <name>locale</name> <type>text</type> <length>5</length> <notnull>true</notnull> </field> <field> <name>attrib_key</name> <type>text</type> <length>255</length> <notnull>true</notnull> </field> <field> <name>attrib_value</name> <type>text</type> <notnull>true</notnull> </field> <index> <name>primary</name> <primary>true</primary> <field> <name>product_id</name> </field> <field> <name>locale</name> </field> <field> <name>attrib_key</name> </field> </index> <index> <name>values_idx</name> <field> <name>attrib_value</name> </field> </index> </declaration> </table> which creates the following DDL using the MySQLi driver: CREATE TABLE shop_cat_categories_attrib ( category_id INT UNSIGNED DEFAULT 0 NOT NULL, locale VARCHAR(5) DEFAULT ' ' NOT NULL, attrib_key VARCHAR(255) DEFAULT ' ' NOT NULL, attrib_value TEXT DEFAULT ' ' NOT NULL ) ENGINE = INNODB This fails because a LOB cannot have a default value with MySQL (Native message: BLOB/TEXT column 'attrib_value' can't have a default value). Why not treat the absence of default values just as that? CREATE TABLE shop_cat_categories_attrib ( category_id INT UNSIGNED NOT NULL, locale VARCHAR(5) NOT NULL, attrib_key VARCHAR(255) NOT NULL, attrib_value TEXT NOT NULL ) ENGINE = INNODB

The problem is that you are defining the column as text without a length. You should rather define it as clob or with a length.

Okay, thanks for the tip re: clobs. I still don't get why, lobs aside, a not null column without a defined default gets a default assigned. Using the same DB definition, with the change made to the attrib_value column as suggested, I'm still getting defaults defined for all other NOT NULL columns: CREATE TABLE `shop_cat_categories_attrib` ( `category_id` int(10) unsigned NOT NULL default '0', `locale` varchar(5) NOT NULL default ' ', `attrib_key` varchar(255) NOT NULL default ' ', `attrib_value` longtext NOT NULL, PRIMARY KEY (`category_id`,`locale`,`attrib_key`), KEY `values_idx_idx` (`attrib_value`(767)) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

a NOT NULL colum needs a default .. MDB2_Schema also tries to be portable and so it requires that things are a bit more explicit. as to the ' ' this is due to the fact that you have the default portability setting enabled that converts empty strings to NULL. disable it and you will get ''.

Hey Lukas, Thanks for the further elaboration. As for NOT NULL columns needing a default, that may be the case for certain DBs, but isn't the case with all (MySQL, for example). Providing a default value to a NOT NULL column (unless the default is "legitimate") defeats the purpose of having a NOT NULL setting. Should not an INSERT statement fail if I neglected to include all required column data, or should the database stuff the record with '' or 0? Am I missing something here?

The problem is that one RDBMS will use an implict default some will not. That implicit default will be differnt etc. Since the aim is portability MDB2_Schema chooses to enforce explicit definition. The only thing where they are pretty consistent is that a NULLable column implicitly means a default of NULL unless a default is explcitly defined. What exactly is the problem for you in terms of functionality? Why do you feel that its better to leave out the default clause?

Gotcha. thanks for the clarification. The issue I see with setting unwarranted defaults is data integrity. If I define a NOT NULL column, I expect that each record has valid data set for that column. A '' or 0 may not constitute validate data. For that matter, a default value of any description may not consititurte valid data, as it should be explicitly set by the end user with a valid INSERT statement. Having the INSERT statement die when a NOT NULL column is not set would be a desirable outcome indicating that not all the data requirements have been met. DB portability wise, I really have no clue how this figures into the grand universe. What DBs use an implicit default for NOT NULL columns?

Well actually the main source of trouble is MySQL in non strict mode. Maybe now that MySQL 5 does have a strict mode which does not force an implicit value for NOT NULL columns without a default its time to make this behaviour optional. The vast number of RDBMS simply use NULL if no default is set explicitly, though I would need to research this some more. Its probably going to be a fair bit of work to change this. Patches welcome.

This bug has been fixed in CVS. If this was a documentation problem, the fix will appear on pear.php.net by the end of next Sunday (CET). If this was a problem with the pear.php.net website, the change should be live shortly. Otherwise, the fix will appear in the package's next release. Thank you for the report and for helping us make PEAR better.

column text is define without a length and have the default portability setting enabled (i will change this settings) During tests, i've not read this bug and produce test over multiple environment : PHP4/5 Mysql4/5 mysql/mysqli after reading comments, i understand why my table import would not work ... except with php5 mysql4 mysqli ??? CREATE TABLE `table_test` ( `field1` text NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; test script : <?php require_once 'MDB2/Schema.php'; $cfg['dsn'] = array( 'phptype' => 'mysql', 'port' => '3306', 'username' => 'XXXXX', 'password' => 'XXXXX', 'hostspec' => 'localhost', 'database' => 'test_db', ); $phptypes = array ( '80' => array ('mysql'), '82' => array ('mysql','mysqli') ); $ports = array ('3306','3307'); foreach ($ports as $port) { foreach ($phptypes[$_SERVER['SERVER_PORT']] as $phptype) { $cfg['dsn']['phptype'] = $phptype; $cfg['dsn']['port'] = $port; $schema =& MDB2_Schema::factory($cfg['dsn']); echo 'PHP version : '.phpversion().'<br />'; if ($phptype == 'mysqli') echo 'Mysql Server version : '.mysqli_get_server_info($schema->db->getConnection()).'<br />'; else echo 'Mysql Server version : '.mysql_get_server_info($schema->db->getConnection()).'<br />'; echo 'Mysql Client version : '.mysql_get_client_info().'<br />'; echo 'phptype used : '.$cfg['dsn']['phptype'].'<br />'; $dumpExtension = $port.'_'.$phptype.'_'.$_SERVER['SERVER_PORT']; $dump_config = array( 'output_mode' => 'file', 'output' => 'dump'.$dumpExtension.'.xml', ); $definition = $schema->getDefinitionFromDatabase(); $operation = $schema->dumpDatabase($definition, $dump_config, MDB2_SCHEMA_DUMP_ALL); $definition = $schema->parseDatabaseDefinition($dump_config['output']); $schema->db->setDatabase($cfg['dsn']['database']); $operation = $schema->createTable('table_test_copy'.$dumpExtension,$definition['tables']['table_test']); echo '<h3>DEFINITION</h3><pre>';var_dump($definition);echo '</pre>'; echo '<h3>COPY TABLE</h3>'; if (PEAR::isError($operation)) { echo '<pre>'.$operation->toString().'</pre>'; } else { echo 'OK'; $schema->db->manager->dropTable('table_test_copy'.$dumpExtension); } echo '<hr />'; } } ?> you can test PHP4 Mysql4/5 : http://emmanuel.dyndns.org/test PHP5 Mysql4/5 mysql/mysqli : http://emmanuel.dyndns.org:82/test

Reverse engineering is not a 1:1 mapping unfortunately. This is why in the reverse engineering code in some cases multiple possible definitions are returned. Part of the problem results from an open todo item, which relates to determining a length for CLOB/BLOB fields. I will reopen the bug until all of this is sorted out.

could you try current CVS. I just made the following change to the reverse modules: - do not set a default if type is text and no length is set or if type is a LOB (Request #8074)

mysql and mysqli reverse CVS imported force_defaults => false, tests ok force_defaults => true, tests !ok all seem good for me

ok i have changed the fix in CVS: default values are dumped for type 'text' even of no length is defined. however when creating a table the mysql/mysqli driver will not use TEXT when no length is defined with a default value and instead will create the largest possible varchar. finally by disabling "force_defaults" you can prevent MDB2_Schema from forcing a default value for all non LOB types.