[phpOpenTracker-devel] [Bug #4624] POT Creates Month Merge Tables Incorrectly

Bug #4624, was updated on 2005-Jul-20 17:43
Here is a current snapshot of the bug.

Project: phpOpenTracker
Category: None
Status: Open
Resolution: None
Bug Group: None
Priority: 5
Submitted by: matthius
Assigned to : none
Summary: POT Creates Month Merge Tables Incorrectly

Details: When using POT in mysql_merge mode it was unable to correctly create the merge tables/monthly tables. The result was I always got error no:2 file not found errors. I did some research and discovered that the merge tables (pot_accesslog and pot_visitors) were not identical to the monthly tables (i.e. pot_acceslog_200507) the biggest problem I saw was that the monthly tables were InnoDB instead of MyISAM. If i dropped all the monthly tables POT would recreate them incorrectly.

My workaround:
I exported the monthly tables (SQL format) modified their table type, dropped the old tables and re created them.

Old structure:
DROP TABLE IF EXISTS `pot_visitors_200507`;
CREATE TABLE `pot_visitors_200507` (
  `accesslog_id` int(11) NOT NULL default '0',
  `visitor_id` int(11) NOT NULL default '0',
  `client_id` int(10) unsigned NOT NULL default '0',
  `operating_system_id` int(11) NOT NULL default '0',
  `user_agent_id` int(11) NOT NULL default '0',
  `host_id` int(11) NOT NULL default '0',
  `referer_id` int(11) NOT NULL default '0',
  `timestamp` int(10) unsigned NOT NULL default '0',
  `weekday` tinyint(1) unsigned NOT NULL default '0',
  `hour` tinyint(2) unsigned NOT NULL default '0',
  `returning_visitor` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`accesslog_id`),
  KEY `client_time` (`client_id`,`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1;

New structure:
DROP TABLE IF EXISTS `pot_visitors_200507`;
CREATE TABLE `pot_visitors_200507` (
  `accesslog_id` int(11) NOT NULL default '0',
  `visitor_id` int(11) NOT NULL default '0',
  `client_id` int(10) unsigned NOT NULL default '0',
  `operating_system_id` int(11) NOT NULL default '0',
  `user_agent_id` int(11) NOT NULL default '0',
  `host_id` int(11) NOT NULL default '0',
  `referer_id` int(11) NOT NULL default '0',
  `timestamp` int(10) unsigned NOT NULL default '0',
  `weekday` tinyint(1) unsigned NOT NULL default '0',
  `hour` tinyint(2) unsigned NOT NULL default '0',
  `returning_visitor` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`accesslog_id`),
  KEY `client_time` (`client_id`,`timestamp`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1;


Then I dropped the merge table and re created it based on the monthly table definition: i.e.

DROP TABLE IF EXISTS `pot_visitors`;
CREATE TABLE `pot_visitors` (
  `accesslog_id` int(11) NOT NULL default '0',
  `visitor_id` int(11) NOT NULL default '0',
  `client_id` int(10) unsigned NOT NULL default '0',
  `operating_system_id` int(11) NOT NULL default '0',
  `user_agent_id` int(11) NOT NULL default '0',
  `host_id` int(11) NOT NULL default '0',
  `referer_id` int(11) NOT NULL default '0',
  `timestamp` int(10) unsigned NOT NULL default '0',
  `weekday` tinyint(1) unsigned NOT NULL default '0',
  `hour` tinyint(2) unsigned NOT NULL default '0',
  `returning_visitor` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`accesslog_id`),
  KEY `client_time` (`client_id`,`timestamp`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 
UNION=(pot_visitors_200507, pot_visitors_200508, pot_visitors_200509, 
	pot_visitors_200510, pot_visitors_200511, pot_visitors_200512);

After doing this, everything worked. But the POT table creation code is clearly creating the wrong kind of table.

I'm running POT v1.5.1 - Apache2 - PHP 4.4.0RC2 on Win2k3. Feel free to contact me with any questions.


Keep up the good work!
 - Matt

Follow-Ups:

Date: 2005-Jul-21 12:38
By: matthius

Comment:
s_bergmann,

Perfect! Yes my DB does create InnoDB tables by default and the patch does fix the problem. 



Take care, 
 - Matt


-------------------------------------------------------

Date: 2005-Jul-20 21:35
By: s_bergmann

Comment:
Is your MySQL configured to create InnoDB tables by default? Does the following patch work for you?

Index: phpOpenTracker/DB/mysql_merge.php
===================================================================
--- phpOpenTracker/DB/mysql_merge.php   (Revision 51)
+++ phpOpenTracker/DB/mysql_merge.php   (Arbeitskopie)
@@ -317,7 +317,7 @@
         '',
         'IF NOT EXISTS',
         $name,
-        ($type == 'accesslog') ? 'DELAY_KEY_WRITE=1 PACK_KEYS=1' : 'DELAY_KEY_WRITE=1'
+        ($type == 'accesslog') ? 'TYPE=MyISAM DELAY_KEY_WRITE=1 PACK_KEYS=1' : 'TYPE=MyISAM DELAY_KEY_WRITE=1'
       )
     );
   }
-------------------------------------------------------

For detailed info, follow this link:
http://developer.berlios.de/bugs/?func=detailbug&bug_id=4624&group_id=2025