Same MySQL Query Returning Different Results on Same Server

Question

I am running the following query. Sometimes it returns around 100 records, as expected, other times is returns only one record with NULL for all of the values of the joined fields. Also, explain returns a different result almost everytime.

Here is the query:

EXPLAIN
SELECT
`fieldreps`.`user_id` AS `fieldreps.user_id`,
 `fieldreps`.`availability_id` AS `fieldreps.availability_id`,
 `fieldreps`.`applicant_type` AS `fieldreps.applicant_type`,
 `fieldreps`.`license_number` AS `fieldreps.license_number`,
 `fieldreps`.`license_exp` AS `fieldreps.license_exp`,
 `fieldreps`.`license_state` AS `fieldreps.license_state`,
 `fieldreps`.`car` AS `fieldreps.car`,
 `fieldreps`.`authorized_worker` AS `fieldreps.authorized_worker`,
 `fieldreps`.`restrictions` AS `fieldreps.restrictions`,
 `fieldreps`.`night` AS `fieldreps.night`,
 `fieldreps`.`day` AS `fieldreps.day`,
 `fieldreps`.`longer` AS `fieldreps.longer`,
 `fieldreps`.`commitment` AS `fieldreps.commitment`,
 `fieldreps`.`travel_metro` AS `fieldreps.travel_metro`,
 `fieldreps`.`travel_states` AS `fieldreps.travel_states`,
 `fieldreps`.`big_employee` AS `fieldreps.big_employee`,
 `fieldreps`.`employed` AS `fieldreps.employed`,
 `fieldreps`.`retail` AS `fieldreps.retail`,
 `fieldreps`.`status` AS `fieldreps.status`,
 `fieldreps`.`start` AS `fieldreps.start`,
 `fieldreps`.`sales` AS `fieldreps.sales`,
 `fieldreps`.`study` AS `fieldreps.study`,
 `fieldreps`.`relevant` AS `fieldreps.relevant`,
 `fieldreps`.`experience` AS `fieldreps.experience`,
 `fieldreps`.`claims` AS `fieldreps.claims`,
 `fieldreps`.`education` AS `fieldreps.education`,
 `fieldreps`.`degree_details` AS `fieldreps.degree_details`,
 `fieldreps`.`degree_institution` AS `fieldreps.degree_institution`,
 `fieldreps`.`other_training` AS `fieldreps.other_training`,
 `fieldreps`.`jobs` AS `fieldreps.jobs`,
 `fieldreps`.`current_training` AS `fieldreps.current_training`,
 `fieldreps`.`interested` AS `fieldreps.interested`,
 `fieldreps`.`achievements` AS `fieldreps.achievements`,
 `fieldreps`.`passions` AS `fieldreps.passions`,
 `fieldreps`.`ambitions` AS `fieldreps.ambitions`,
 `fieldreps`.`max_travel_time` AS `fieldreps.max_travel_time`,
 `fieldreps`.`creation_time` AS `fieldreps.creation_time`,
 `fieldreps`.`resume` AS `fieldreps.resume`,
 `users`.`user_id` AS `users.user_id`,
 `users`.`email` AS `users.email`,
 `users`.`name` AS `users.name`,
 `users`.`password` AS `users.password`,
 `users`.`given_name` AS `users.given_name`,
 `users`.`title` AS `users.title`,
 `users`.`nationality` AS `users.nationality`,
 `tasks`.`task_id` AS `tasks.task_id`,
 `tasks`.`recurrence_id` AS `tasks.recurrence_id`,
 `tasks`.`parent_id` AS `tasks.parent_id`,
 `tasks`.`user_id` AS `tasks.user_id`,
 `tasks`.`campaign_id` AS `tasks.campaign_id`,
 `tasks`.`location_id` AS `tasks.location_id`,
 `tasks`.`title` AS `tasks.title`,
 `tasks`.`description` AS `tasks.description`,
 `tasks`.`earliest_start` AS `tasks.earliest_start`,
 `tasks`.`deadline` AS `tasks.deadline`,
 `tasks`.`km_estimate` AS `tasks.km_estimate`,
 `tasks`.`estimate` AS `tasks.estimate`,
 `datapoints`.`datapoint_id` AS `datapoints.datapoint_id`,
 `datapoints`.`task_id` AS `datapoints.task_id`,
 `datapoints`.`datatype_id` AS `datapoints.datatype_id`,
 `datapoints`.`value` AS `datapoints.value`,
 `datapoints`.`product_id` AS `datapoints.product_id`,
 `datapoints`.`recorded` AS `datapoints.recorded`,
 `datatypes`.`datatype_id` AS `datatypes.datatype_id`,
 `datatypes`.`datatype_template_id` AS `datatypes.datatype_template_id`,
 `datatypes`.`client_id` AS `datatypes.client_id`,
 `datatypes`.`name` AS `datatypes.name`,
 `datatypes`.`description` AS `datatypes.description`,
 `datatypes`.`options` AS `datatypes.options`,
 `locations`.`location_id` AS `locations.location_id`,
 `locations`.`retailer_group_id` AS `locations.retailer_group_id`,
 `locations`.`latitude` AS `locations.latitude`,
 `locations`.`longitude` AS `locations.longitude`,
 `locations`.`name` AS `locations.name`,
 `locations`.`address` AS `locations.address`,
 `locations`.`city` AS `locations.city`,
 `locations`.`state` AS `locations.state`,
 `locations`.`postal` AS `locations.postal`,
 `locations`.`phone` AS `locations.phone`,
 `locations`.`suburb` AS `locations.suburb`,
 `locations`.`country` AS `locations.country`,
 `locations`.`region` AS `locations.region`,
 `locations`.`coverage` AS `locations.coverage`,
 `locations`.`store_number` AS `locations.store_number`,
 `locations`.`store_type` AS `locations.store_type`

 FROM `fieldreps` AS `fieldreps`

 LEFT JOIN `users` ON (`fieldreps`.`user_id` = `users`.`user_id`)
 LEFT JOIN `tasks` ON (`fieldreps`.`user_id` = `tasks`.`user_id`)
 LEFT JOIN `datapoints` ON (`tasks`.`task_id` = `datapoints`.`task_id`)
 LEFT JOIN `datatypes` ON (`datapoints`.`datatype_id` = `datatypes`.`datatype_id`)
 LEFT JOIN `locations` ON (`tasks`.`location_id` = `locations`.`location_id`)

 WHERE `fieldreps`.`user_id` LIKE '5f5dc29c-832e-5646-9dc2-46d774bfb1ec'

One Result Second Result

No worky worked

I am running the query in phpMyAdmin. Database is hosted on xeround.com(Could it be a bug in their configuration?)

I am stumped. I am not sure where to go from here.

Thanks in advance!

UPDATE:

Here is the dump from phpMyAdmin:

-- phpMyAdmin SQL Dump
-- version 3.4.3.2
-- http://www.phpmyadmin.net
--
-- Generation Time: Jan 07, 2012 at 08:20 PM
-- Server version: 5.1.42
-- PHP Version: 5.3.1

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `big_development`
--

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

--
-- Table structure for table `datapoints`
--

CREATE TABLE IF NOT EXISTS `datapoints` (
  `datapoint_id` varchar(36) COLLATE utf8_bin NOT NULL,
  `task_id` varchar(36) COLLATE utf8_bin NOT NULL,
  `datatype_id` varchar(36) COLLATE utf8_bin NOT NULL,
  `value` text COLLATE utf8_bin,
  `product_id` varchar(36) COLLATE utf8_bin DEFAULT NULL,
  `recorded` text COLLATE utf8_bin,
  PRIMARY KEY (`datapoint_id`),
  KEY `task_id` (`task_id`),
  KEY `datatype_id` (`datatype_id`),
  KEY `product_id` (`product_id`)
) ENGINE=Xeround DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

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

--
-- Table structure for table `datatypes`
--

CREATE TABLE IF NOT EXISTS `datatypes` (
  `datatype_id` varchar(36) COLLATE utf8_bin NOT NULL,
  `datatype_template_id` varchar(36) COLLATE utf8_bin NOT NULL,
  `client_id` varchar(36) COLLATE utf8_bin NOT NULL,
  `name` text COLLATE utf8_bin,
  `description` text COLLATE utf8_bin,
  `options` text COLLATE utf8_bin,
  PRIMARY KEY (`datatype_id`),
  KEY `datatype_template_id` (`datatype_template_id`),
  KEY `client_id` (`client_id`)
) ENGINE=Xeround DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

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

--
-- Table structure for table `fieldreps`
--

CREATE TABLE IF NOT EXISTS `fieldreps` (
  `user_id` varchar(36) COLLATE utf8_bin NOT NULL,
  `availability_id` varchar(36) COLLATE utf8_bin NOT NULL,
  `applicant_type` text COLLATE utf8_bin NOT NULL,
  `license_number` text COLLATE utf8_bin NOT NULL,
  `license_exp` text COLLATE utf8_bin,
  `license_state` text COLLATE utf8_bin,
  `car` text COLLATE utf8_bin NOT NULL,
  `authorized_worker` text COLLATE utf8_bin NOT NULL,
  `restrictions` text COLLATE utf8_bin NOT NULL,
  `night` text COLLATE utf8_bin NOT NULL,
  `day` text COLLATE utf8_bin NOT NULL,
  `longer` text COLLATE utf8_bin NOT NULL,
  `commitment` text COLLATE utf8_bin NOT NULL,
  `travel_metro` text COLLATE utf8_bin NOT NULL,
  `travel_states` text COLLATE utf8_bin NOT NULL,
  `big_employee` text COLLATE utf8_bin NOT NULL,
  `employed` text COLLATE utf8_bin NOT NULL,
  `retail` text COLLATE utf8_bin NOT NULL,
  `status` text COLLATE utf8_bin NOT NULL,
  `start` text COLLATE utf8_bin,
  `sales` text COLLATE utf8_bin NOT NULL,
  `study` text COLLATE utf8_bin NOT NULL,
  `relevant` text COLLATE utf8_bin NOT NULL,
  `experience` text COLLATE utf8_bin NOT NULL,
  `claims` text COLLATE utf8_bin NOT NULL,
  `education` text COLLATE utf8_bin NOT NULL,
  `degree_details` text COLLATE utf8_bin NOT NULL,
  `degree_institution` text COLLATE utf8_bin NOT NULL,
  `other_training` text COLLATE utf8_bin NOT NULL,
  `jobs` text COLLATE utf8_bin NOT NULL,
      `current_training` text COLLATE utf8_bin NOT NULL,
  `interested` text COLLATE utf8_bin NOT NULL,
  `achievements` text COLLATE utf8_bin NOT NULL,
  `passions` text COLLATE utf8_bin NOT NULL,
  `ambitions` text COLLATE utf8_bin NOT NULL,
  `max_travel_time` text COLLATE utf8_bin NOT NULL,
  `creation_time` text COLLATE utf8_bin,
  `resume` text COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`user_id`),
  KEY `availability_id` (`availability_id`)
) ENGINE=Xeround DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

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

--
-- Table structure for table `locations`
--

CREATE TABLE IF NOT EXISTS `locations` (
  `location_id` varchar(36) COLLATE utf8_bin NOT NULL,
  `retailer_group_id` varchar(36) COLLATE utf8_bin NOT NULL,
  `latitude` text COLLATE utf8_bin,
  `longitude` text COLLATE utf8_bin,
  `name` text COLLATE utf8_bin,
  `address` text COLLATE utf8_bin,
  `city` text COLLATE utf8_bin,
  `state` text COLLATE utf8_bin,
  `postal` text COLLATE utf8_bin,
  `phone` text COLLATE utf8_bin,
  `suburb` text COLLATE utf8_bin,
  `country` text COLLATE utf8_bin,
  `region` text COLLATE utf8_bin,
  `coverage` tinyint(1) DEFAULT NULL,
  `store_number` text COLLATE utf8_bin,
  `store_type` text COLLATE utf8_bin,
  PRIMARY KEY (`location_id`),
  KEY `retailer_group_id` (`retailer_group_id`)
) ENGINE=Xeround DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

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

--
-- Table structure for table `tasks`
--

CREATE TABLE IF NOT EXISTS `tasks` (
  `task_id` varchar(36) COLLATE utf8_bin NOT NULL,
  `recurrence_id` varchar(36) COLLATE utf8_bin NOT NULL,
  `parent_id` varchar(36) COLLATE utf8_bin DEFAULT NULL,
  `user_id` varchar(36) COLLATE utf8_bin NOT NULL,
  `campaign_id` varchar(36) COLLATE utf8_bin NOT NULL,
  `location_id` varchar(36) COLLATE utf8_bin NOT NULL,
  `title` text COLLATE utf8_bin,
  `description` text COLLATE utf8_bin,
  `earliest_start` text COLLATE utf8_bin,
  `deadline` text COLLATE utf8_bin,
  `km_estimate` text COLLATE utf8_bin,
  `estimate` int(11) DEFAULT NULL,
  PRIMARY KEY (`task_id`),
  KEY `campaign_id` (`campaign_id`),
  KEY `location_id` (`location_id`),
  KEY `user_id` (`user_id`),
  KEY `parent_id` (`parent_id`),
  KEY `recurrence_id` (`recurrence_id`)
) ENGINE=Xeround DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

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

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `user_id` varchar(36) COLLATE utf8_bin NOT NULL,
  `email` varchar(255) COLLATE utf8_bin NOT NULL,
  `name` text COLLATE utf8_bin,
  `password` varchar(255) COLLATE utf8_bin NOT NULL,
  `given_name` text COLLATE utf8_bin,
  `title` text COLLATE utf8_bin NOT NULL,
  `nationality` text COLLATE utf8_bin,
  PRIMARY KEY (`user_id`),
  KEY `user_id` (`user_id`)
) ENGINE=Xeround DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

EDIT:

If I remove the join on all tables but user, the query works everytime. However, as soon as I add the join on tasks, it breaks. For example, this query breaks it in the same way as the first query I posted:

SELECT
`fieldreps`.`user_id` AS `fieldreps.user_id`,
 `fieldreps`.`availability_id` AS `fieldreps.availability_id`,
 `fieldreps`.`applicant_type` AS `fieldreps.applicant_type`,
 `fieldreps`.`license_number` AS `fieldreps.license_number`,
 `fieldreps`.`license_exp` AS `fieldreps.license_exp`,
 `fieldreps`.`license_state` AS `fieldreps.license_state`,
 `fieldreps`.`car` AS `fieldreps.car`,
 `fieldreps`.`authorized_worker` AS `fieldreps.authorized_worker`,
 `fieldreps`.`restrictions` AS `fieldreps.restrictions`,
 `fieldreps`.`night` AS `fieldreps.night`,
 `fieldreps`.`day` AS `fieldreps.day`,
 `fieldreps`.`longer` AS `fieldreps.longer`,
 `fieldreps`.`commitment` AS `fieldreps.commitment`,
 `fieldreps`.`travel_metro` AS `fieldreps.travel_metro`,
 `fieldreps`.`travel_states` AS `fieldreps.travel_states`,
 `fieldreps`.`big_employee` AS `fieldreps.big_employee`,
 `fieldreps`.`employed` AS `fieldreps.employed`,
 `fieldreps`.`retail` AS `fieldreps.retail`,
 `fieldreps`.`status` AS `fieldreps.status`,
 `fieldreps`.`start` AS `fieldreps.start`,
 `fieldreps`.`sales` AS `fieldreps.sales`,
 `fieldreps`.`study` AS `fieldreps.study`,
 `fieldreps`.`relevant` AS `fieldreps.relevant`,
 `fieldreps`.`experience` AS `fieldreps.experience`,
 `fieldreps`.`claims` AS `fieldreps.claims`,
 `fieldreps`.`education` AS `fieldreps.education`,
 `fieldreps`.`degree_details` AS `fieldreps.degree_details`,
 `fieldreps`.`degree_institution` AS `fieldreps.degree_institution`,
 `fieldreps`.`other_training` AS `fieldreps.other_training`,
 `fieldreps`.`jobs` AS `fieldreps.jobs`,
 `fieldreps`.`current_training` AS `fieldreps.current_training`,
 `fieldreps`.`interested` AS `fieldreps.interested`,
 `fieldreps`.`achievements` AS `fieldreps.achievements`,
 `fieldreps`.`passions` AS `fieldreps.passions`,
 `fieldreps`.`ambitions` AS `fieldreps.ambitions`,
 `fieldreps`.`max_travel_time` AS `fieldreps.max_travel_time`,
 `fieldreps`.`creation_time` AS `fieldreps.creation_time`,
 `fieldreps`.`resume` AS `fieldreps.resume`,
 `users`.`user_id` AS `users.user_id`,
     `users`.`email` AS `users.email`,
 `users`.`name` AS `users.name`,
 `users`.`password` AS `users.password`,
 `users`.`given_name` AS `users.given_name`,
 `users`.`title` AS `users.title`,
 `users`.`nationality` AS `users.nationality`,
 `tasks`.`task_id` AS `tasks.task_id`,
 `tasks`.`recurrence_id` AS `tasks.recurrence_id`,
 `tasks`.`parent_id` AS `tasks.parent_id`,
 `tasks`.`user_id` AS `tasks.user_id`,
 `tasks`.`campaign_id` AS `tasks.campaign_id`,
 `tasks`.`location_id` AS `tasks.location_id`,
 `tasks`.`title` AS `tasks.title`,
 `tasks`.`description` AS `tasks.description`,
 `tasks`.`earliest_start` AS `tasks.earliest_start`,
 `tasks`.`deadline` AS `tasks.deadline`,
 `tasks`.`km_estimate` AS `tasks.km_estimate`,
 `tasks`.`estimate` AS `tasks.estimate`

 FROM `fieldreps` AS `fieldreps`

 LEFT JOIN `users` ON (`fieldreps`.`user_id` = `users`.`user_id`)
 LEFT JOIN `tasks` ON (`fieldreps`.`user_id` = `tasks`.`user_id`)

 WHERE `fieldreps`.`user_id` LIKE '5f5dc29c-832e-5646-9dc2-46d774bfb1ec'

@redmoon7777 Yes I do. Adding the SQL to the question so you can see. — Jonathan
– Jonathan, Commented Jan 7, 2012 at 20:21
have you tried adding an ORDER BY clause just to see what happens? — redmoon7777
– redmoon7777, Commented Jan 7, 2012 at 21:02
@redmoon7777 I just tried ORDER BY fieldreps.user_id and that did not make a difference. I also tried LIMIT 0, 100 and that did not make a difference. What am I missing? :/ — Jonathan
– Jonathan, Commented Jan 7, 2012 at 21:47
Does the SELECT * FROM fieldreps WHERE fieldreps.user_id LIKE '5f5dc29c-832e-5646-9dc2-46d774bfb1ec' always return a row? — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jan 7, 2012 at 21:47

Jonathan · Accepted Answer · 2012-01-23 01:41:11Z

2

The connection and database collation differed.

answered Jan 23, 2012 at 1:41

Jonathan

5,5574 gold badges40 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Same MySQL Query Returning Different Results on Same Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related