13 Replies - 363 Views - Last Post: 14 June 2017 - 11:44 PM

#1 ArtificialSoldier  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 1828
  • View blog
  • Posts: 5,756
  • Joined: 15-January 14

Count returning incorrect number

Posted 12 June 2017 - 02:26 PM

I'm seeing this issue only on a single database on a single server (although I haven't tested every other one, there are around 100 of these databases set up).

Here's the server:
Server: Localhost via UNIX socket
Server type: MySQL
Server version: 5.6.35-log - MySQL Community Server (GPL)
Protocol version: 10
User: [email protected]
Server charset: UTF-8 Unicode (utf8)

cpsrvd 11.64.0.24
Database client version: libmysql - 5.1.73
PHP extension: mysqli curl mbstring
PHP version: 5.6.30


Here's the setup:

CREATE TABLE `content` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `description` text COLLATE utf8_unicode_ci,
  `code` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `type` int(11) UNSIGNED NOT NULL COMMENT '1:online training; 2:classroom training; 3:online test/questionnaire; 4:other online resource; 5:webinar; 6:MultiSCO',
  `parent_id` int(11) UNSIGNED DEFAULT NULL,
  `url` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
  `active` tinyint(1) UNSIGNED NOT NULL DEFAULT '1',
  `disp_order` int(11) UNSIGNED NOT NULL,
  `width` int(11) UNSIGNED NOT NULL,
  `height` int(11) UNSIGNED NOT NULL,
  `toolbar` tinyint(1) UNSIGNED NOT NULL,
  `launchcount` int(11) UNSIGNED NOT NULL,
  `pass_score` int(11) UNSIGNED NOT NULL,
  `credits` float UNSIGNED NOT NULL,
  `launch_limit` int(11) UNSIGNED NOT NULL,
  `instacomplete` tinyint(1) UNSIGNED NOT NULL,
  `time_to_complete` float NOT NULL,
  `keywords` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `target_aud` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `thumbnail` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `post_date` int(11) UNSIGNED NOT NULL,
  `category` int(11) UNSIGNED NOT NULL,
  `auto_archive` tinyint(1) UNSIGNED NOT NULL,
  `mark_cids_complete` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `certificate` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `track_type` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `resource_type` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `prereqs` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `list_in_catalog` tinyint(1) UNSIGNED NOT NULL,
  `list_in_transcript` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  `self_assign` tinyint(1) UNSIGNED NOT NULL,
  `scorm_default_org` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `media_use` tinyint(1) UNSIGNED NOT NULL,
  `media_width` int(11) UNSIGNED NOT NULL,
  `media_height` int(11) UNSIGNED NOT NULL,
  `media_auto` tinyint(1) UNSIGNED NOT NULL,
  `media_show` tinyint(1) UNSIGNED NOT NULL,
  `media_loop` tinyint(1) UNSIGNED NOT NULL,
  `media_note` text COLLATE utf8_unicode_ci,
  `media_type` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `media_download` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  `reuse_win` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  `no_aicc_output` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  `extra_aicc_text` text COLLATE utf8_unicode_ci,
  `session_time` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  `exact_url` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  `ug_assign` tinyint(1) UNSIGNED NOT NULL DEFAULT '1',
  `send_email` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  `email_student` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  `lock_completion_records` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  `lock_completion_status` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  `overwrite` tinyint(1) NOT NULL DEFAULT '0',
  `template_id_s` int(11) UNSIGNED DEFAULT NULL,
  `template_id_a` int(11) UNSIGNED DEFAULT NULL,
  `show_summary` tinyint(1) UNSIGNED DEFAULT '1',
  `show_score` tinyint(1) UNSIGNED DEFAULT '1',
  `show_answer` tinyint(1) UNSIGNED DEFAULT '0',
  `pw_required` tinyint(1) NOT NULL DEFAULT '0',
  `password` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `attach_url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `electronic_signature` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  `ignore_auto_collapse` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  `pass_fail` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  `hidden` tinyint(1) NOT NULL DEFAULT '0',
  `bypass_wrapper` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  `bid` int(11) UNSIGNED DEFAULT NULL,
  `content_start_timestamp` int(11) UNSIGNED NOT NULL DEFAULT '0',
  `content_end_timestamp` int(11) UNSIGNED NOT NULL DEFAULT '4294967295'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `content`
  ADD PRIMARY KEY (`id`),
  ADD KEY `parent_id` (`parent_id`),
  ADD KEY `active` (`active`),
  ADD KEY `bid` (`bid`),
  ADD KEY `template_id_s` (`template_id_s`),
  ADD KEY `template_id_a` (`template_id_a`);
ALTER TABLE `content` ADD FULLTEXT KEY `title` (`title`,`code`,`description`,`keywords`);

ALTER TABLE `content`
  ADD CONSTRAINT `content_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `content` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `content_ibfk_2` FOREIGN KEY (`bid`) REFERENCES `badges` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `content_ibfk_3` FOREIGN KEY (`template_id_s`) REFERENCES `email_templates` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `content_ibfk_4` FOREIGN KEY (`template_id_a`) REFERENCES `email_templates` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;


The problem shows up when running COUNT queries. For example:

SELECT COUNT(*) AS num FROM content WHERE parent_id=3643 AND active=1; // 0 rows
SELECT COUNT(`title`) AS num FROM content WHERE parent_id=3643 AND active=1; // 6 rows


In phpMyAdmin, it will return 0 rows when the parameter to count is *, id, parent_id, active, template_id_s, template_id_a, and bid, for all other columns it will return 6. If I write a PHP script, and include our application initialization file with all objects defined, then using either mysqli or PDO only template_id_s, template_id_a, and bid return 0, everything else returns 6. I get the same results if I include only our config file and create my own PDO object:

$pdo = new PDO("mysql:dbname={$config['db_name']};host={$config['db_host']};charset=utf8", $config['db_user'], $config['db_pass'], [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

$stmt = $pdo->query('SHOW COLUMNS FROM content');
$cols = $stmt->fetchAll();

$batch = '';

echo '<table border=1>';
$sql = 'SELECT COUNT(*) AS num FROM content WHERE ' . $where;
$stmt = $pdo->query($sql);
$num = $stmt->fetchColumn();
echo '<tr><td>' . $sql . '</td><td>' . $num . '</td></tr>';
$initial = $num;
$batch .= $sql . ";\n";

foreach ($cols as $col) {
  $sql = 'SELECT COUNT(`' . $col['Field'] . '`) AS num FROM content WHERE ' . $where;
  $batch .= $sql . ";\n";
  $stmt = $pdo->query($sql);
  $num = $stmt->fetchColumn();
  echo '<tr><td>' . $sql . '</td><td>' . ($num != $initial ? '<b style="color: red;">' : '') . $num . ($num != $initial ? '</b>' : '') . '</td></tr>';
}
echo '</table>';
echo $batch;


I notice that all of the results that return 0 in any case, with the exception of count(*), are part of indexes. I've tried to flush the table, optimize, defrag, etc, and there's no change. This is for the live database for a particular installation, for the test sandbox database on the same server it works fine. I haven't seen this problem on any other servers.

Does anyone know what the issue is? phpMyAdmin reports that the client version and the MySQL version are different, but they're the exact same versions on other working servers as well.

Quote

Your PHP MySQL library version 5.1.73 differs from your MySQL server version 5.6.35. This may cause unpredictable behavior.


Is This A Good Question/Topic? 0
  • +

Replies To: Count returning incorrect number

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13485
  • View blog
  • Posts: 53,857
  • Joined: 12-June 08

Re: Count returning incorrect number

Posted 12 June 2017 - 02:28 PM

Does the count change when you use a 'group by'?
Was This Post Helpful? 0
  • +
  • -

#3 ArtificialSoldier  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 1828
  • View blog
  • Posts: 5,756
  • Joined: 15-January 14

Re: Count returning incorrect number

Posted 12 June 2017 - 02:32 PM

This also returns 0:

SELECT COUNT(`template_id_s`) AS num FROM content WHERE parent_id=3643 AND active=1 GROUP BY template_id_s

Was This Post Helpful? 0
  • +
  • -

#4 ArtificialSoldier  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 1828
  • View blog
  • Posts: 5,756
  • Joined: 15-January 14

Re: Count returning incorrect number

Posted 12 June 2017 - 02:39 PM

It looks like at this point phpMyAdmin is at least now consistent with my own code, only the 3 columns are returning 0. I also notice that those 3 columns are all foreign keys. The other foreign key, parent_id, returns the correct count but it is a foreign key that refers to the content table instead of another table.

Here are the actual records, by the way:

SELECT * FROM content WHERE parent_id=3643

INSERT INTO `content` (`id`, `title`, `description`, `code`, `type`, `parent_id`, `url`, `active`, `disp_order`, `width`, `height`, `toolbar`, `launchcount`, `pass_score`, `credits`, `launch_limit`, `instacomplete`, `time_to_complete`, `keywords`, `target_aud`, `thumbnail`, `post_date`, `category`, `auto_archive`, `mark_cids_complete`, `certificate`, `track_type`, `resource_type`, `prereqs`, `list_in_catalog`, `list_in_transcript`, `self_assign`, `scorm_default_org`, `media_use`, `media_width`, `media_height`, `media_auto`, `media_show`, `media_loop`, `media_note`, `media_type`, `media_download`, `reuse_win`, `no_aicc_output`, `extra_aicc_text`, `session_time`, `exact_url`, `ug_assign`, `send_email`, `email_student`, `lock_completion_records`, `lock_completion_status`, `overwrite`, `template_id_s`, `template_id_a`, `show_summary`, `show_score`, `show_answer`, `pw_required`, `password`, `attach_url`, `electronic_signature`, `ignore_auto_collapse`, `pass_fail`, `hidden`, `bypass_wrapper`, `bid`, `content_start_timestamp`, `content_end_timestamp`) VALUES
(3519, 'Setting Up Alerts', '​desc1', 'US_08_00_N1', 1, 3643, '', 1, 1, 0, 0, 0, 0, 80, 0, 0, 0, 15, 'keywords1', '', '', 1433266059, 0, 0, '', '', 'scorm', '', '', 0, 0, 0, 'rc2ke5_ORG', 0, 0, 0, 0, 0, 0, '', '', 0, 0, 0, '', 0, 0, 0, 0, 0, 1, 0, 0, NULL, NULL, 0, 0, 0, 0, '', '', 0, 0, 0, 0, 0, NULL, 0, 4294967295),
(3520, 'Creating Special Events', '​​desc2', 'US_08_00_N2', 1, 3643, '', 1, 2, 0, 0, 0, 0, 80, 0, 0, 0, 0, 'keywords2', '', '', 1433266275, 0, 0, '', '', 'scorm', '', '', 0, 0, 0, 'rc2ke4_ORG', 0, 0, 0, 0, 0, 0, '', '', 0, 0, 0, '', 0, 0, 0, 0, 0, 1, 0, 0, NULL, NULL, 0, 0, 0, 0, '', '', 0, 0, 0, 0, 0, NULL, 0, 4294967295),
(3521, 'Setting Up Rate Plans', '​​desc3', 'US_08_00_N3', 1, 3643, '', 1, 3, 0, 0, 0, 0, 80, 0, 0, 0, 0, 'keywords3', '', '', 1433266749, 0, 0, '', '', 'scorm', '', '', 0, 0, 0, 'rc2ke3_ORG', 0, 0, 0, 0, 0, 0, '', '', 0, 0, 0, '', 0, 0, 0, 0, 0, 1, 0, 0, NULL, NULL, 0, 0, 0, 0, '', '', 0, 0, 0, 0, 0, NULL, 0, 4294967295),
(3525, 'Setting Up Rate Buckets', '​​desc4', 'US_08_00_N4', 1, 3643, '', 1, 4, 0, 0, 0, 0, 80, 0, 0, 0, 15, 'keywords4', '', '', 1433274500, 0, 0, '', '', 'scorm', '', '', 0, 0, 0, 'rc2ke2_ORG', 0, 0, 0, 0, 0, 0, '', '', 0, 0, 0, '', 0, 0, 0, 0, 0, 1, 0, 0, NULL, NULL, 0, 0, 0, 0, '', '', 0, 0, 0, 0, 0, NULL, 0, 4294967295),
(3526, 'Using Room Type Buckets', '​​desc5', 'US_08_00_N5', 1, 3643, '', 1, 0, 0, 0, 0, 0, 80, 0, 0, 0, 15, 'keywords5', '', '', 1433274618, 0, 0, '', '', 'scorm', '', '', 0, 0, 0, 'rc2ke1_ORG', 0, 0, 0, 0, 0, 0, '', '', 0, 0, 0, '', 0, 0, 0, 0, 0, 1, 0, 0, NULL, NULL, 0, 0, 0, 0, '', '', 0, 0, 0, 0, 0, NULL, 0, 4294967295),
(4604, 'Creating Packages', '​​desc6', 'US_08_00_N6', 1, 3643, '', 1, 5, 0, 0, 0, 0, 0, 0, 0, 0, 30, '', 'keywords6', '', 1496335442, 0, 0, '', '', 'scorm', '', '', 1, 0, 0, 'Creating_Packages_ORG', 0, 0, 0, 0, 0, 0, '', '', 0, 0, 0, '', 0, 0, 0, 0, 0, 1, 0, 0, NULL, NULL, 0, 0, 0, 0, '', '', 0, 0, 1, 0, 0, NULL, 0, 4294967295);



All of those records have the same values for parent_id, template_id_s (null), template_id_a (null), and bid.
Was This Post Helpful? 0
  • +
  • -

#5 ArtificialSoldier  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 1828
  • View blog
  • Posts: 5,756
  • Joined: 15-January 14

Re: Count returning incorrect number

Posted 12 June 2017 - 03:03 PM

So the 3 columns that return 0 at this point are all foreign keys to other tables, and all set to null.

Some more tests:

For one of the records, I set template_id_a to a non-null value. Then, doing count(template_id_a) returned 1. I tried to do a count(*) after that, and it returned 0 again (was 6 before I set that value). I removed the value and set it back to null, and count(template_id_a) returns 0 and count(*) returns 6 again.

This seems like a bug with MySQL, but I have no idea how to report it because I can't duplicate this on other databases.
Was This Post Helpful? 0
  • +
  • -

#6 ArtificialSoldier  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 1828
  • View blog
  • Posts: 5,756
  • Joined: 15-January 14

Re: Count returning incorrect number

Posted 12 June 2017 - 03:13 PM

It doesn't look like this has anything to do with PHP:

mysql> SELECT COUNT(`template_id_s`) AS num FROM content WHERE parent_id=3643 AND active=1;
+-----+
| num |
+-----+
|   0 |
+-----+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) AS num FROM content WHERE parent_id=3643 AND active=1;
+-----+
| num |
+-----+
|   6 |
+-----+
1 row in set (0.00 sec)



I take it back, I ran the template_id_s query on another server and it also returned 0. count(*) worked on that server also. I suppose that when counting rows, you can't specify a foreign key that might be null. Of course, that wouldn't be a problem but it came up as a bug because even the queries for count(*) and count(id) were returning 0, although I guess something that I did fixed that at some point. Maybe flushing or rebuilding the table, or maybe just running all of these queries over and over did something with the cache or something like that. I'm not sure how it got broken or fixed in the first point, but this is the second time this client has reported this bug and when we've gone in to look at it the count(*) and count(id) queries were returning 0 instead of the actual count. If it was restricted to just those 3 columns, fine, we wouldn't count on those columns (and there's no reason to use them when counting rows), but since count(*) wasn't working that's a problem.
Was This Post Helpful? 0
  • +
  • -

#7 ArtificialSoldier  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 1828
  • View blog
  • Posts: 5,756
  • Joined: 15-January 14

Re: Count returning incorrect number

Posted 12 June 2017 - 03:22 PM

I found a bug report:

https://bugs.mysql.c...ug.php?id=81031
Was This Post Helpful? 0
  • +
  • -

#8 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 879
  • View blog
  • Posts: 1,893
  • Joined: 30-January 09

Re: Count returning incorrect number

Posted 12 June 2017 - 06:38 PM

View PostArtificialSoldier, on 13 June 2017 - 09:03 AM, said:

So the 3 columns that return 0 at this point are all foreign keys to other tables, and all set to null.

Some more tests:

For one of the records, I set template_id_a to a non-null value. Then, doing count(template_id_a) returned 1. I tried to do a count(*) after that, and it returned 0 again (was 6 before I set that value). I removed the value and set it back to null, and count(template_id_a) returns 0 and count(*) returns 6 again.

This seems like a bug with MySQL, but I have no idea how to report it because I can't duplicate this on other databases.

This is supposed to be standard behaviour. COUNT(column) only counts non-null values, and is equivalent to the following statement:
SUM(CASE WHEN column IS NULL THEN 0 ELSE 1 END)


If you want a "row count" (i.e. not sensitive to null values), use either COUNT(1) or COUNT(*).
Was This Post Helpful? 0
  • +
  • -

#9 ArtificialSoldier  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 1828
  • View blog
  • Posts: 5,756
  • Joined: 15-January 14

Re: Count returning incorrect number

Posted 13 June 2017 - 10:00 AM

Yeah we were using count(*), but the customer kept reporting that things weren't working right and we noticed that it was because even count(*) was returning 0. In doing our testing we noticed that count(id) was also returning 0 but other columns were returning the correct number so that prompted me to write that script to test counting with each column.

Interestingly enough, I just tried count(1) and that also returned 0. I'm trying to figure out how to safely update MySQL to a more recent point release for 5.6.
Was This Post Helpful? 0
  • +
  • -

#10 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13485
  • View blog
  • Posts: 53,857
  • Joined: 12-June 08

Re: Count returning incorrect number

Posted 13 June 2017 - 10:19 AM

*cough* replace it with Access */cough* :D
Was This Post Helpful? 0
  • +
  • -

#11 ArtificialSoldier  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 1828
  • View blog
  • Posts: 5,756
  • Joined: 15-January 14

Re: Count returning incorrect number

Posted 13 June 2017 - 10:22 AM

Access: When your data isn't really all that important.™
Was This Post Helpful? 1
  • +
  • -

#12 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 879
  • View blog
  • Posts: 1,893
  • Joined: 30-January 09

Re: Count returning incorrect number

Posted 13 June 2017 - 03:45 PM

*cough* replace it with PostgreSQL */cough*
Was This Post Helpful? 0
  • +
  • -

#13 ArtificialSoldier  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 1828
  • View blog
  • Posts: 5,756
  • Joined: 15-January 14

Re: Count returning incorrect number

Posted 13 June 2017 - 04:55 PM

I could update to MariaDB 10.0 or 10.1, but a) I don't know if the bug is fixed there, and 2) I can't switch back to MySQL after that. Or, at least, cPanel doesn't support it.
Was This Post Helpful? 0
  • +
  • -

#14 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 879
  • View blog
  • Posts: 1,893
  • Joined: 30-January 09

Re: Count returning incorrect number

Posted 14 June 2017 - 11:44 PM

I know the pain you're in. I'm currently in a job where I'm building the IT profile for a company, including their internal applications. I've run with PostgreSQL 9.6 and PHP 7.1 on split web/data server in AWS. Unfortunately, I also have to support the existing website, which is invariably pegged to Expression Engine 2, which uses PHP 5.6 and MySQL 5.5. If upgrade beyond any of those versions, the site dies, with dozens of dependencies screaming in pain.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1