Max pid = 10000

OpenEMR Development
1

juggernautsei wrote on Thursday, August 25, 2016:

This is funny. I imported 21644 patients into the patient_data table. When we try to add a new record, we get this error.

"ERROR: insert failed: INSERT INTO patient_data SET pid = ‘10000’, date = NOW(),
title = ‘Mr.’, fname = ‘Patient’, mname = ‘’, lname = ‘Demo’, pubpid =
‘10000’, DOB = ‘2016-08-25’, sex = ‘Male’, ss = ‘’, drivers_license =
‘’, status = ‘’, genericname1 = ‘’, genericval1 = ‘’, genericname2 = ‘’,
genericval2 = ‘’, billing_note = ‘’, street = ‘’, city = ‘’, state =
‘’, postal_code = ‘’, country_code = ‘’, county = ‘’, mothersname = ‘’,
guardiansname = ‘’, contact_relationship = ‘’, phone_contact = ‘’,
phone_home = ‘’, phone_biz = ‘’, phone_cell = ‘’, email = ‘’,
email_direct = ‘’, providerID = ‘’, ref_providerID = ‘’, pharmacy_id =
‘0’, hipaa_notice = ‘’, hipaa_voice = ‘’, hipaa_message = ‘’, hipaa_mail
= ‘’, hipaa_allowsms = ‘’, hipaa_allowemail = ‘’, allow_imm_reg_use = ‘’,
allow_imm_info_share = ‘’, allow_health_info_ex = ‘’, allow_patient_portal
= ‘’, care_team = ‘’, cmsportal_login = ‘’, occupation = ‘’, industry =
‘’, language = ‘’, ethnicity = ‘’, race = ‘’, financial_review = ‘’,
family_size = ‘’, monthly_income = ‘’, homeless = ‘’, interpretter = ‘’,
migrantseasonal = ‘’, referral_source = ‘’, vfc = ‘’, religion = ‘’,
deceased_date = ‘’, deceased_reason = ‘’

I chased it down to new_comprehensive_save.php

$result = sqlQuery("SELECT MAX(pid)+1 AS pid FROM patient_data");

When I export the variable the number is 10000.
It seems that the MAX(pid) is 10000.

I’ll update with a fix once I find one.

2

mdsupport wrote on Thursday, August 25, 2016:

Standard tables allow (extremely) large integer. So MAX(pid) is not the error unless you changed table definition. You should try running the statement in sql command to see the complete error - could be related to mysql innodb issue or resource limit.

3

juggernautsei wrote on Thursday, August 25, 2016:

This works:

$result = sqlQuery(“SELECT id AS pid FROM patient_data ORDER BY id DESC LIMIT 1”);
$newpid = 1;

if ($result[‘pid’] > 1) $newpid = $result[‘pid’] + 1;

4

juggernautsei wrote on Thursday, August 25, 2016:

My question is why is the code stopping at 10000? I entered a record form the phpmyadmin Insert and the record went into the table fine. So that meant I needed to find in the code where the 10000 was coming from. This where I traced it down to.

5

visolveemr wrote on Thursday, August 25, 2016:

Sherwin,

10000 also should be accepted.

Did you get the 10000 th entry? If not can you directly try this in your database and tell us the result?

INSERT INTO patient_data SET pid = ‘10000’;

Thanks
OpenEMR Customization/Support Team,
Visolve Inc

6

robertdown wrote on Thursday, August 25, 2016:

This could be written more efficiently:

$result = sqlQuery("SELECT id AS pid FROM patient_data ORDER BY id DESC LIMIT 1");
$newpid = ($result['pid'] > 1) ? $result['pid'] + 1 : 1;

This condenses the if statement down to 1 line without using irregular if statements and unnecessary variable declarations.

7

sunsetsystems wrote on Thursday, August 25, 2016:

Not sure I get this solution. There’s no guaranteed relationship between id and pid.

I’d suggest reviewing the pid column definition (e.g. with phpmyadmin) and looking for table/index corruption (see mysqlcheck).

It’s also a mystery to me why the id column exists and why pid is not the primary index with an auto_increment attribute. It’s been weird like this since day 1.

Rod
http://www.sunsetsystems.com/

8

mdsupport wrote on Thursday, August 25, 2016:

Your solution is definitely not addressing root cause. If it worked without making any database changes, your hiccup was most likely related to a mysql problem. If you are still getting the same error with old code, as stated earlier, you have to look at raw mySQL error code & whatever description it provides.

9

juggernautsei wrote on Friday, September 02, 2016:

Ok, thanks for all the suggestions. To restate the root issue. I imported 21644 records into the patient data table. I did not change the table in anyway (that I can remember).

We went to add a new patient and the system arbitrally choose 10000 as the next PID to be assigned. 10000 is already being used so we would get the sql error above.

I could go to the database itself and add a patient from the GUI and it went in just fine. So I thought that ruled out a database error.

I tracked down the code that was assigning the PID. I changed it and the new patient went in with the next signed PID

Yes, I know there is no corrilation between the PID and ID. It was just a scheme to assign a unique number.

Thanks for the short hand code. I need to take that class to learn that.

You know that once you get past a issue, sometimes you don’t look back you just keep it moving. This was one of those times I looked back.

10

juggernautsei wrote on Friday, September 02, 2016:

Yes there were 21644 entries in the database already so 10000 was being occupied already.

11

mdsupport wrote on Friday, September 02, 2016:

At this stage of mySQL database, it is extremely unlikely that its MAX intrinsic function has a limit of 10000. Since you state that your patient_data was added without emr interface, can you post output of
SELECT pid, id FROM patient_data ORDER BY pid DESC LIMIT 10

12

sunsetsystems wrote on Friday, September 02, 2016:

Yeah… I think it’s important for you to figure out what the problem is. Covering it up invites more trouble.

Rod
http://www.sunsetsystems.com/

13

The answer to the problem is when the database was originally created. The pid column was set as a varchar(20). So, MAX() will only return 10000 for a varchar column. The solution was to this command.

 ALTER TABLE `patient_data` CHANGE `pid` `pid` BIGINT(50) NULL DEFAULT NULL;

Now, I have reverted the code back to the original and it works as intended.