#58 Problem with DB-MySQL.sql from CSV

[Anonymous]

The last table fails to create due to definition error.
According to the MYSQL manual on creating tables

http://dev.mysql.com/doc/refman/5.0/en/create-table.html

The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column. See Section 10.3.1.1, “TIMESTAMP Properties”.

So the table definition in DB-MySQL.sql in the install directory is incorrect.

CREATE TABLE tbl_Wrktimer (
issue int4 DEFAULT '0' NOT NULL,
userid int4 DEFAULT '0' NOT NULL,
running ENUM('f','t') DEFAULT 'f' NOT NULL,
`begin` DATETIME DEFAULT 'now()' NOT NULL,
`end` DATETIME DEFAULT 'now()' NOT NULL,
primary key (issue,userid)
);

This maybe to due a bug in MYSQLDUMP its self or PHPMYADMIN

Regards
Louie Athanasiadis
la@uow.edu.au

[Ryan Coleman]

Logged In: YES
user_id=2108670
Originator: NO

Use this, I fixed the SQL, it's the SQL code itself.

::::Copy from here on out::::

-- phpMyAdmin SQL Dump
-- version 2.7.0-pl1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Feb 01, 2006 at 02:32 AM
-- Server version: 4.1.14
-- PHP Version: 4.4.0-pl1-gentoo
--
-- Database: `helpdeskdb`
--

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

--
-- Table structure for table `tbl_default_preferences`
--

CREATE TABLE `tbl_default_preferences` (
`identifier` varchar(20) NOT NULL default '',
`value` varchar(100) NOT NULL default '',
PRIMARY KEY (`identifier`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_default_preferences`
--

INSERT INTO `tbl_default_preferences` (`identifier`, `value`) VALUES ('verify-email', 'true'),
('pub-hide-su', 'true'),
('pub-hide-remarks', 'true'),
('language', 'en_UK'),
('email-remark', 'false'),
('email-resolve', 'false'),
('newissue-email', ''),
('wrk-timer', 'true');

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

--
-- Table structure for table `tbl_domain_preferences`
--

CREATE TABLE `tbl_domain_preferences` (
`identifier` varchar(20) NOT NULL default '',
`domain` int(4) NOT NULL default '0',
`value` varchar(100) NOT NULL default '',
PRIMARY KEY (`identifier`,`domain`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_domain_preferences`
--

INSERT INTO `tbl_domain_preferences` (`identifier`, `domain`, `value`) VALUES ('pub-hide-remarks', 1, 'true'),
('pub-hide-su', 1, 'true');

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

--
-- Table structure for table `tbl_domains`
--

CREATE TABLE `tbl_domains` (
`id` int(4) unsigned NOT NULL auto_increment,
`domain` varchar(200) NOT NULL default '',
`active` int(4) NOT NULL default '1',
`css` varchar(100) NOT NULL default '',
`notice` varchar(255) NOT NULL default '',
`defaultdomain` int(4) NOT NULL default '0',
`defaultpriority` int(4) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `tbl_domains`
--

INSERT INTO `tbl_domains` (`id`, `domain`, `active`, `css`, `notice`, `defaultdomain`, `defaultpriority`) VALUES (1, 'System', 0, '', '', 0, 0),
(2, 'Demo', 1, '', '', 0, 0);

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

--
-- Table structure for table `tbl_emailconfirmation`
--

CREATE TABLE `tbl_emailconfirmation` (
`hash` varchar(100) NOT NULL default '',
`user_id` int(4) NOT NULL default '0',
`email` varchar(200) NOT NULL default '',
`date` int(11) NOT NULL default '0',
PRIMARY KEY (`hash`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_emailconfirmation`
--

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

--
-- Table structure for table `tbl_issues`
--

CREATE TABLE `tbl_issues` (
`id` int(4) unsigned NOT NULL auto_increment,
`reportedby` int(4) NOT NULL default '0',
`createdby` int(4) NOT NULL default '0',
`closedby` int(4) NOT NULL default '0',
`assignedto` int(4) NOT NULL default '0',
`status` int(4) NOT NULL default '0',
`level` int(4) NOT NULL default '0',
`priority` int(4) NOT NULL default '0',
`site` int(4) NOT NULL default '0',
`detail` int(4) NOT NULL default '0',
`category` int(4) NOT NULL default '0',
`location` varchar(200) NOT NULL default '',
`contact` varchar(100) NOT NULL default '',
`createdon` int(8) NOT NULL default '0',
`closedon` int(8) NOT NULL default '0',
`summary` text NOT NULL,
`description` text NOT NULL,
`solution` text NOT NULL,
`published` int(2) NOT NULL default '0',
`publishedby` int(4) NOT NULL default '0',
`publishedon` int(8) NOT NULL default '0',
`domain` int(4) NOT NULL default '0',
`recalled` int(2) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `tbl_issues`
--

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

--
-- Table structure for table `tbl_levels`
--

CREATE TABLE `tbl_levels` (
`id` int(4) unsigned NOT NULL auto_increment,
`level` varchar(100) NOT NULL default '',
`sortorder` int(4) NOT NULL default '0',
`domain` int(4) NOT NULL default '0',
`active` int(4) NOT NULL default '1',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `tbl_levels`
--

INSERT INTO `tbl_levels` (`id`, `level`, `sortorder`, `domain`, `active`) VALUES (1, 'Easy', 0, 2, 1),
(2, 'Medium', 1, 2, 1),
(3, 'Difficult', 2, 2, 1);

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

--
-- Table structure for table `tbl_link_categories`
--

CREATE TABLE `tbl_link_categories` (
`id` int(4) unsigned NOT NULL auto_increment,
`name` varchar(100) NOT NULL default '',
`sortorder` int(4) NOT NULL default '0',
`lang` varchar(5) NOT NULL default 'en_UK',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `tbl_link_categories`
--

INSERT INTO `tbl_link_categories` (`id`, `name`, `sortorder`, `lang`) VALUES (1, 'Helpdesk Issue Manager', 0, 'en_UK'),
(2, 'Sponsors', 1, 'en_UK');

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

--
-- Table structure for table `tbl_links`
--

CREATE TABLE `tbl_links` (
`id` int(4) unsigned NOT NULL auto_increment,
`text` varchar(100) NOT NULL default '',
`link` varchar(200) NOT NULL default '',
`category` int(4) NOT NULL default '0',
`sortorder` int(4) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

--
-- Dumping data for table `tbl_links`
--

INSERT INTO `tbl_links` (`id`, `text`, `link`, `category`, `sortorder`) VALUES (1, 'Home', 'index.php', 1, 0),(2, 'About', 'about.php', 1, 1),(3, 'News', 'news.php', 1, 2),(4, 'Documentation', 'docs/index.html', 1, 3),(5, 'F.A.Q.', 'faq.php', 1, 4),(6, 'Download', 'download.php', 1, 5),(7, 'Features', 'features.php', 1, 6),(8, 'Change Log', 'changelog.php', 1, 7),(9, 'Central Mcr. CLC', 'http://helpdesk.centralmanclc.com/', 2, 0),(10, 'Oxford Archeology', 'http://www.thehumanjourney.net/', 2, 1),(11, 'European Electronique', 'http://www.euroele.co.uk/', 2, 2);

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

--
-- Table structure for table `tbl_logs`
--

CREATE TABLE `tbl_logs` (
`id` int(4) unsigned NOT NULL auto_increment,
`userid` int(4) NOT NULL default '0',
`domain` int(4) NOT NULL default '0',
`timestamp` int(8) default '0',
`code` varchar(50) NOT NULL default '',
`log` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `tbl_logs`
--

INSERT INTO `tbl_logs` (`id`, `userid`, `domain`, `timestamp`, `code`, `log`) VALUES (1, 2, 2, 1138757551, 'LOGIN', 'Project Admin has successfully logged in.');

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

--
-- Table structure for table `tbl_news`
--

CREATE TABLE `tbl_news` (
`id` int(4) unsigned NOT NULL auto_increment,
`date` date NOT NULL default '0000-00-00',
`content` text NOT NULL,
`frontpagepara` int(4) NOT NULL default '0',
`lang` varchar(5) NOT NULL default 'en_UK',
`domain_id` int(4) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `tbl_news`
--

INSERT INTO `tbl_news` (`id`, `date`, `content`, `frontpagepara`, `lang`, `domain_id`) VALUES (1, '2005-03-05', ' <h2 style="text-align:right;">[ PHP / Pear::DB ]</h2>\r\n <p>The Helpdesk Issue Manager is a robust and generic helpdesk management system for all kinds of helpdesks.</p>\r\n\r\n<p>Look on the <a href="about.php">about the system</a> page to find out more.</p>\r\n', 1, 'en_UK', NULL),
(2, '2005-03-19', '<h2>This is in development - beta testing!</h2>\r\n\r\n <h3>If you would like to contribute to the development of this, please email me: helpdesk@<em>handyandy.org.uk</em></h3>\r\n<p>Enjoy!</p>', 2, 'en_UK', NULL),
(3, '2005-04-02', '</p><h2>Move over to MySQL almost comlete!</h2>\r\n<p>Welcome to this site. Things have been moving very fast, and I am glad to say that the move over to mySQL is complete.', 0, 'en_UK', NULL),
(4, '0000-00-00', '', 3, 'en_UK', 1),
(5, '0000-00-00', '', 3, 'en_UK', 2);

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

--
-- Table structure for table `tbl_preference_descriptions`
--

CREATE TABLE `tbl_preference_descriptions` (
`identifier` varchar(20) NOT NULL default '',
`type` varchar(20) NOT NULL default '',
`description` varchar(100) NOT NULL default '',
`overridable` int(2) NOT NULL default '0',
PRIMARY KEY (`identifier`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_preference_descriptions`
--

INSERT INTO `tbl_preference_descriptions` (`identifier`, `type`, `description`, `overridable`) VALUES ('verify-email', 'boolean', 'Turn on email verification', 0),
('pub-hide-su', 'boolean', 'Hide site and user names in published issues', 0),
('pub-hide-remarks', 'boolean', 'Hide all remarks in published issues', 0),
('language', 'langlist', 'The language in which to display all text', 1),
('email-remark', 'boolean', 'Email notification of issue changes/remarks.', 1),
('email-resolve', 'boolean', 'Email notification of issue resolutions.', 1),
('newissue-email', 'text', 'Email address to receive new unassigned issues (or leave blank)', 0),
('wrk-timer', 'boolean', 'Turn on usage of Timer', 0);

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

--
-- Table structure for table `tbl_priorities`
--

CREATE TABLE `tbl_priorities` (
`id` int(4) unsigned NOT NULL auto_increment,
`priority` varchar(100) NOT NULL default '',
`class` int(4) NOT NULL default '0',
`sortorder` int(4) NOT NULL default '0',
`domain` int(4) NOT NULL default '0',
`active` int(4) NOT NULL default '1',
`notify` int(2) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `tbl_priorities`
--

INSERT INTO `tbl_priorities` (`id`, `priority`, `class`, `sortorder`, `domain`, `active`, `notify`) VALUES (1, 'Low', 0, 0, 2, 1, 0),
(2, 'Medium', 1, 1, 2, 1, 0),
(3, 'High', 2, 2, 2, 1, 0);

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

--
-- Table structure for table `tbl_problemcategories`
--

CREATE TABLE `tbl_problemcategories` (
`id` int(4) unsigned NOT NULL auto_increment,
`description` varchar(100) NOT NULL default '',
`active` int(2) NOT NULL default '1',
`domain` int(4) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `tbl_problemcategories`
--

INSERT INTO `tbl_problemcategories` (`id`, `description`, `active`, `domain`) VALUES (1, 'Software', 1, 2),
(2, 'Hardware', 1, 2),
(3, 'Website', 1, 2);

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

--
-- Table structure for table `tbl_problemdetails`
--

CREATE TABLE `tbl_problemdetails` (
`id` int(4) unsigned NOT NULL auto_increment,
`category` int(4) NOT NULL default '0',
`description` varchar(100) NOT NULL default '',
`active` int(2) NOT NULL default '1',
`domain` int(4) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

--
-- Dumping data for table `tbl_problemdetails`
--

INSERT INTO `tbl_problemdetails` (`id`, `category`, `description`, `active`, `domain`) VALUES (1, 1, 'Not installed properly', 1, 2),
(2, 1, 'Crashing', 1, 2),
(3, 1, 'Will not load', 1, 2),
(4, 2, 'Printer', 1, 2),
(5, 2, 'Scanner', 1, 2),
(6, 2, 'Monitor', 1, 2),
(7, 2, 'Base Unit', 1, 2),
(8, 3, 'Updating', 1, 2),
(9, 3, 'Error messages', 1, 2),
(10, 3, 'Gone offline', 1, 2);

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

--
-- Table structure for table `tbl_remarks`
--

CREATE TABLE `tbl_remarks` (
`id` int(4) unsigned NOT NULL auto_increment,
`issue` int(4) NOT NULL default '0',
`reportedby` int(4) default '0',
`reportedon` int(8) default '0',
`remark` text NOT NULL,
`confidential` int(4) NOT NULL default '1',
`time` time default '00:00:00',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `tbl_remarks`
--

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

--
-- Table structure for table `tbl_sites`
--

CREATE TABLE `tbl_sites` (
`id` int(4) unsigned NOT NULL auto_increment,
`site` varchar(100) NOT NULL default '',
`domain` int(4) NOT NULL default '0',
`active` int(4) NOT NULL default '1',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `tbl_sites`
--

INSERT INTO `tbl_sites` (`id`, `site`, `domain`, `active`) VALUES (1, 'Test Site', 2, 1);

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

--
-- Table structure for table `tbl_statuses`
--

CREATE TABLE `tbl_statuses` (
`id` int(4) unsigned NOT NULL auto_increment,
`status` varchar(100) NOT NULL default '',
`sortorder` int(4) NOT NULL default '0',
`domain` int(4) NOT NULL default '0',
`active` int(4) NOT NULL default '1',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `tbl_statuses`
--

INSERT INTO `tbl_statuses` (`id`, `status`, `sortorder`, `domain`, `active`) VALUES (1, 'New', 0, 2, 1),
(2, 'Open', 1, 2, 1),
(3, 'Closed', 2, 2, 1);

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

--
-- Table structure for table `tbl_system_preferences`
--

CREATE TABLE `tbl_system_preferences` (
`id` int(4) unsigned NOT NULL auto_increment,
`identifier` varchar(20) NOT NULL default '',
`value` varchar(100) NOT NULL default '',
`comment` varchar(100) NOT NULL default '',
`system` int(2) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `tbl_system_preferences`
--

INSERT INTO `tbl_system_preferences` (`id`, `identifier`, `value`, `comment`, `system`) VALUES (1, 'version', '0.9.3', 'This is the current version number for the system', 1),
(2, 'pagesize', '30', 'Page size for paging of issues', 0),
(3, 'closedpagesize', '10', 'Page size for restriction of closed issues', 0),
(4, 'maxupload', '2000', 'Maximum file upload size in bytes', 0),
(5, 'excessivehours', '10', 'Number of hours spent on a problem that is considered excessive and should be confirmed', 0);

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

--
-- Table structure for table `tbl_thesaurus`
--

CREATE TABLE `tbl_thesaurus` (
`id` int(4) unsigned NOT NULL auto_increment,
`term` varchar(40) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `tbl_thesaurus`
--

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

--
-- Table structure for table `tbl_thesaurus_mappings`
--

CREATE TABLE `tbl_thesaurus_mappings` (
`term` int(4) NOT NULL default '0',
`issue` int(4) NOT NULL default '0',
PRIMARY KEY (`term`,`issue`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_thesaurus_mappings`
--

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

--
-- Table structure for table `tbl_times`
--

CREATE TABLE `tbl_times` (
`issue` int(4) NOT NULL default '0',
`userid` int(4) NOT NULL default '0',
`time` int(14) NOT NULL default '0',
PRIMARY KEY (`issue`,`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_times`
--

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

--
-- Table structure for table `tbl_userdomains`
--

CREATE TABLE `tbl_userdomains` (
`id` int(4) unsigned NOT NULL auto_increment,
`userid` int(4) NOT NULL default '0',
`domain` int(4) NOT NULL default '0',
`defaultflag` int(2) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `tbl_userdomains`
--

INSERT INTO `tbl_userdomains` (`id`, `userid`, `domain`, `defaultflag`) VALUES (1, 1, 1, 1);

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

--
-- Table structure for table `tbl_usersites`
--

CREATE TABLE `tbl_usersites` (
`userid` int(4) NOT NULL default '0',
`site` int(4) NOT NULL default '0',
`usertype` int(4) NOT NULL default '0',
`defaultflag` int(2) NOT NULL default '1',
PRIMARY KEY (`userid`,`site`,`usertype`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_usersites`
--

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

--
-- Table structure for table `tbl_usertypes`
--

CREATE TABLE `tbl_usertypes` (
`id` int(4) unsigned NOT NULL auto_increment,
`type` varchar(100) NOT NULL default '',
`sortorder` int(4) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `tbl_usertypes`
--

INSERT INTO `tbl_usertypes` (`id`, `type`, `sortorder`) VALUES (1, 'Client', 0),
(2, 'Site Contact', 1),
(3, 'Support Agent', 2),
(4, 'Administrator', 3),
(5, 'Domain Administrator', 4),
(6, 'Root', 999);

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

--
-- Table structure for table `tbl_user_preferences`
--

CREATE TABLE `tbl_user_preferences` (
`identifier` varchar(20) NOT NULL default '',
`userid` int(4) NOT NULL default '0',
`domain` int(4) NOT NULL default '0',
`value` varchar(100) NOT NULL default '',
PRIMARY KEY (`identifier`,`userid`,`domain`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_user_preferences`
--

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

--
-- Table structure for table `tbl_users`
--

CREATE TABLE `tbl_users` (
`id` int(4) unsigned NOT NULL auto_increment,
`username` varchar(20) NOT NULL default '',
`pass` varchar(50) NOT NULL default '',
`name` varchar(100) NOT NULL default '',
`email` varchar(100) NOT NULL default '',
`available` int(2) NOT NULL default '0',
`active` int(2) NOT NULL default '1',
`root` int(2) NOT NULL default '0',
`restricted` int(2) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `tbl_users`
--

INSERT INTO `tbl_users` (`id`, `username`, `pass`, `name`, `email`, `available`, `active`, `root`, `restricted`) VALUES (1, '', '', '* unassigned *', '* unassigned *', 0, 0, 0, 1),
(2, 'root', '63a9f0ea7bb98050796b649e85481845', 'Project Admin', '', 1, 1, 1, 0);

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

--
-- Table structure for table `tbl_wkrtimer`
--

CREATE TABLE tbl_wrktimer (
issue int4 DEFAULT '0' NOT NULL,
userid int4 DEFAULT '0' NOT NULL,
running ENUM('f','t') DEFAULT 'f' NOT NULL,
`begin` DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL,
`end` DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL,
primary key (issue,userid)
);