try catch not working

[Jake Bunce]

This is my install code for my addon:

	public static function install()
	{
		$db = XenForo_Application::get('db');

		$db->query("
			CREATE TABLE IF NOT EXISTS `nat_options` (
				`node_id` int(10) unsigned NOT NULL,
				`nat_display_tab` tinyint(3) unsigned NOT NULL,
				`nat_display_tabperms` tinyint(3) unsigned NOT NULL,
				`nat_display_order` int(10) unsigned NOT NULL,
				`nat_position` enum('home','middle','end') NOT NULL,
				`nat_childlinks` tinyint(3) unsigned NOT NULL,
				`nat_childlinksperms` tinyint(3) unsigned NOT NULL,
				`nat_markread` tinyint(3) unsigned NOT NULL,
				`nat_linkstemplate` varchar(50) NOT NULL,
				`nat_popup` tinyint(3) unsigned NOT NULL,
				`nat_childnodes` text NOT NULL,
				`nat_firstchildnodes` mediumblob NOT NULL,
				UNIQUE KEY `node_id` (`node_id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='NodesAsTabs addon'
		");

		try
		{
			$db->query("
				ALTER TABLE `nat_options` ADD `nat_markread` tinyint(3) unsigned NOT NULL AFTER `nat_childlinksperms`
			");
		}
		catch (Zend_Db_Exception $e)
		{
			
		}

		try
		{
			$db->query("
				ALTER TABLE `nat_options` ADD `nat_popup` tinyint(3) unsigned NOT NULL AFTER `nat_linkstemplate`
			");
		}
		catch (Zend_Db_Exception $e)
		{
			
		}

		$optionsModel = XenForo_Model::create('NodesAsTabs_Model_Options');

		$optionsModel->deleteOrphans();
		$optionsModel->rebuildCache();
	}

The red part is supposed to add those columns if they are missing. The try / catch is supposed to let those queries fail gracefully. But one person reported this error on the first try block:

Problem here version of XF 1.2

Server Error

mysqli_stmt::execute(): (42S21/1060): Duplicate column name 'nat_markread'

1. XenForo_Application::handlePhpError()
2. mysqli_stmt->execute() in Zend/Db/Statement/Mysqli.php at line 208
3. Zend_Db_Statement_Mysqli->_execute() in Zend/Db/Statement.php at line 297
4. Zend_Db_Statement->execute() in Zend/Db/Adapter/Abstract.php at line 479
5. Zend_Db_Adapter_Abstract->query() in NodesAsTabs/Install.php at line 31
6. NodesAsTabs_Install::install()
7. call_user_func() in XenForo/Model/AddOn.php at line 214
8. XenForo_Model_AddOn->installAddOnXml() in XenForo/Model/AddOn.php at line 169
9. XenForo_Model_AddOn->installAddOnXmlFromFile() in XenForo/ControllerAdmin/AddOn.php at line 178
10. XenForo_ControllerAdmin_AddOn->actionInstall() in XenForo/FrontController.php at line 310
11. XenForo_FrontController->dispatch() in XenForo/FrontController.php at line 132
12. XenForo_FrontController->run() in /var/www/xxxxxxxxx.net/htdocs/forum/admin.php at line 13

I can't reproduce this error myself and I can't figure out what might be causing it. Any ideas?

Because I can't reproduce this error I am inclined to blame his PHP configuration, but I want to check with you guys first.

 

[EasyTarget]

The try / catch block is working as designed.
The Zend_Db_Exception extends the php exception handler. When the object is created it is going to spit out the trace route.

In your try / catch block you should just use

catch (Exception $e)​

{​

//ignore the error​

}​

I

Although, it is normally a bad idea to rely on the try / catch block to allow for your code to run smoothly. You should be checking for the error condition before causing it. My suggestion, if you want to make the code more bullet proof I would use a check query before altering the table.

SELECT *
FROM information_schema.COLUMNS
WHERE table_schema =  'database_name_from_cache'
AND table_name =  'nat_options'
AND column_name =  'nat_markread'
LIMIT 0 , 50

And if the result set is not returned then you know, most likely, it is safe to add the column.

Then if an error condition is generated, most likely, the user that is accessing the database doesn't have permissions to alter the table.

 

[Jake Bunce]

Why can't I reproduce the error? The query fails gracefully in all of my tests where there is a duplicate column.

 

[EasyTarget]

Why can't I reproduce the error? The query fails gracefully in all of my tests where there is a duplicate column.

No idea. Different versions of php? different versions of mysql? Too many things to try and narrow it down.

Still the preferred method, is to test for the possible error condition. It really is bad practice to have an exception handler be part of the code logic. It kind of is in the top 10 list of things not to do. There are very few exceptions to the rule, like in vba sometimes you have to catch err 5 and use a resume statement.

I think somewhere in the zend framework they have this code logic already built, but I could be wrong.

 

[Jeremy P]

public static function addColumnIfNotExist($db, $table, $field, $attr)
{
if ($db->fetchRow('SHOW COLUMNS FROM ' . $table . ' WHERE Field = ?', $field))
{
return;
}
 
return $db->query('ALTER TABLE ' . $table . ' ADD ' . $field . ' ' . $attr);
}