Description:
Sorry for the HUGE description of my problem, but I'm hoping that with as much information that I can possibly avail to you, my issue can be cleared up for me.
I have four tables being used by an application that I'm evaluating a PHP reporting tool to use.
The version of MySQL that I have on my web hosting service is 4.0.25
The table definitions are as follows:
--
-- Table structure for table `mantis_bug_text_table`
--
CREATE TABLE `mantis_bug_text_table` (
`id` int(10) unsigned NOT NULL auto_increment,
`description` text NOT NULL,
`steps_to_reproduce` text NOT NULL,
`additional_information` text NOT NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=52 ;
-- --------------------------------------------------------
--
-- Table structure for table `mantis_bugnote_table`
--
CREATE TABLE `mantis_bugnote_table` (
`id` int(10) unsigned NOT NULL auto_increment,
`bug_id` int(10) unsigned NOT NULL default '0',
`reporter_id` int(10) unsigned NOT NULL default '0',
`bugnote_text_id` int(10) unsigned NOT NULL default '0',
`view_state` smallint(6) NOT NULL default '10',
`date_submitted` datetime NOT NULL default '1970-01-01 00:00:01',
`last_modified` datetime NOT NULL default '1970-01-01 00:00:01',
`note_type` int(11) default '0',
`note_attr` varchar(250) default '',
PRIMARY KEY (`id`),
KEY `idx_bug` (`bug_id`),
KEY `idx_last_mod` (`last_modified`)
) TYPE=MyISAM AUTO_INCREMENT=113 ;
-- --------------------------------------------------------
--
-- Table structure for table `mantis_bugnote_text_table`
--
CREATE TABLE `mantis_bugnote_text_table` (
`id` int(10) unsigned NOT NULL auto_increment,
`note` text NOT NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=113 ;
-- --------------------------------------------------------
--
-- Table structure for table `mantis_user_table`
--
CREATE TABLE `mantis_user_table` (
`id` int(10) unsigned NOT NULL auto_increment,
`username` varchar(32) NOT NULL default '',
`realname` varchar(64) NOT NULL default '',
`email` varchar(64) NOT NULL default '',
`password` varchar(32) NOT NULL default '',
`date_created` datetime NOT NULL default '1970-01-01 00:00:01',
`last_visit` datetime NOT NULL default '1970-01-01 00:00:01',
`enabled` tinyint(4) NOT NULL default '1',
`protected` tinyint(4) NOT NULL default '0',
`access_level` smallint(6) NOT NULL default '10',
`login_count` int(11) NOT NULL default '0',
`lost_password_request_count` smallint(6) NOT NULL default '0',
`failed_login_count` smallint(6) NOT NULL default '0',
`cookie_string` varchar(64) NOT NULL default '',
PRIMARY KEY (`id`),
UNIQUE KEY `idx_user_cookie_string` (`cookie_string`),
UNIQUE KEY `idx_user_username` (`username`),
KEY `idx_enable` (`enabled`),
KEY `idx_access` (`access_level`)
) TYPE=MyISAM AUTO_INCREMENT=26 ;
---------------------------------------------------------------------------------------
I have the following query that is generated using a tool called PHP ReportMaker (PRM):
---------------------------------------------------------------------------------------
SELECT
mantis_bugnote_table.date_submitted,
mantis_bug_text_table.id,
mantis_bug_text_table.description,
mantis_user_table.username,
mantis_bugnote_text_table.id,
mantis_bugnote_text_table.note
FROM
mantis_bugnote_table
INNER JOIN mantis_bug_text_table ON (mantis_bugnote_table.bug_id = mantis_bug_text_table.id)
INNER JOIN mantis_bugnote_text_table ON (mantis_bugnote_table.bugnote_text_id = mantis_bugnote_text_table.id)
INNER JOIN mantis_user_table ON (mantis_bugnote_table.reporter_id = mantis_user_table.id)
---------------------------------------------------------------------------------------
The PRM package doesn't work with BLOBs (by their design / admission) and, unfortunately, MySQL treats TEXT fields as BLOBs.
The support group for PRM stated that if I cast the fields that are defined as TEXT in the table definition, as CHAR, that this might "cure" my problem.
So, I modified the SQL in the PRM package as follows:
---------------------------------------------------------------------------------------
SELECT
mantis_bugnote_table.date_submitted,
mantis_bug_text_table.id as Bug_ID,
CAST(mantis_bug_text_table.description as CHAR) as `Bug Description`,
mantis_user_table.username,
mantis_bugnote_text_table.id as Note_ID,
CAST(mantis_bugnote_text_table.note as CHAR) as `Note Text`
FROM
mantis_bugnote_table
INNER JOIN mantis_bug_text_table ON (mantis_bugnote_table.bug_id = mantis_bug_text_table.id)
INNER JOIN mantis_bugnote_text_table ON (mantis_bugnote_table.bugnote_text_id = mantis_bugnote_text_table.id)
INNER JOIN mantis_user_table ON (mantis_bugnote_table.reporter_id = mantis_user_table.id)
---------------------------------------------------------------------------------------
Suprisingly, it did remedy my initial problem.
---- BUT ----
If I add an ORDER BY statement to the SQL, MySQL no longer CASTs the TEXT field as CHAR (according to the support group for PRM) and the query returns a BLOB for the two fields that I have CAST'd and the PRM application fails. Their contention is that the problem is with MySQL.
-jP
How to repeat:
SELECT
mantis_bugnote_table.date_submitted,
mantis_bug_text_table.id as Bug_ID,
CAST(mantis_bug_text_table.description as CHAR) as `Bug Description`,
mantis_user_table.username,
mantis_bugnote_text_table.id as Note_ID,
CAST(mantis_bugnote_text_table.note as CHAR) as `Note Text`
FROM
mantis_bugnote_table
INNER JOIN mantis_bug_text_table ON (mantis_bugnote_table.bug_id = mantis_bug_text_table.id)
INNER JOIN mantis_bugnote_text_table ON (mantis_bugnote_table.bugnote_text_id = mantis_bugnote_text_table.id)
INNER JOIN mantis_user_table ON (mantis_bugnote_table.reporter_id = mantis_user_table.id)
ORDERED BY
mantis_bugnote_table.date_submitted
Fields that were CAST returned as BLOB rather than STRING / CHAR
