#380 Log.inc repair (invalid NULL uses quotes 'NULL')

Development_Release
open
nobody
None
2
2014-08-28
2014-06-18
Barry R. Dick
No

I am repairing the sql.inc/adodb, gacl, calendar to use mysqli, and working my way through the rest of the files. One minor (Fatal Error)

$srcdir/log.inc (line 10)

$sql = "insert into log ( date, event, user, groupname, success, comments, crt_user, patient_id) " .
        "values ( NOW(), " . $adodb->qstr($event) . "," . $adodb->qstr($user) .
        "," . $adodb->qstr($groupname) . "," . $adodb->qstr($success) . "," .
        $adodb->qstr($comments) ."," .
        $adodb->qstr($crt_user) ."," . $adodb->qstr($patient_id) . ")";

ERROR: insert failed: insert into log ( date, event, user, groupname, success, comments, crt_user, patient_id) values ( NOW(), 'login','admin','Default','1','success: ::1',NULL,'NULL')

Error: Incorrect integer value: 'NULL' for column 'patient_id' at row 1

I'm using str_replace for the last item ($adodb->qstr($patient_id)) str_replace("'", "", $adodb->qstr($patient_id)) to temporarily get passed. The Attachment is proof openemr running on a localhost with the following system details:

C:\Users\Barryd>httpd -v
Server version: Apache/2.4.9 (Win32)
Apache Lounge VC11 Server built: Mar 16 2014 12:13:13

C:\Users\Barryd>php -v
PHP 5.5.11 (cli) (built: Apr 12 2014 06:35:50)
Copyright (c) 1997-2014 The PHP Group
Zend Engine v2.5.0, Copyright (c) 1998-2014 Zend Technologies

[PHP Modules]
...
mysqli
mysqlnd

C:\MySQL\MySQL Server 5.5\bin>mysqld --version
mysqld Ver 5.5.28 for Win64 on x86 (MySQL Community Server (GPL))

1 Attachments

Discussion

  • Barry R. Dick
    Barry R. Dick
    2014-06-18

    /openemr/interface/main/calendar/main_screen.php (line 165)

    8:00

    The 'providerid' needs a default or 0 value and not a <empty> or the newEvt() fails. (Uncaught SyntaxError: Unexpected token ,)

    function newEvt(startampm, starttimeh, starttimem, eventdate, providerid, catid) {
    dlgopen('add_edit_event.php?startampm=' + startampm +
    '&starttimeh=' + starttimeh + '&userid=' + providerid + '&starttimem=' + starttimem +
    '&date=' + eventdate + '&catid=' + catid ,'_blank', 575, 375);
    }

     
  • Barry R. Dick
    Barry R. Dick
    2014-06-18

    I am using OpenEMR v4.1.2 (6) with recently applied file patches (from OpenEMR v4.1.2 (3)), and what I thought were DB updates/patches, but am finding other "problems". But if only the VERSION file is being compared, ill have to double check the db to be sure majority/all patches have been applied appropriate. Some problems include errors returning from deprecated functions, or missing parameters in functions.

    procedure_providers is not getting created on my db and what other tables are missing, ill have to take a look at when I get back. That could have some influence on why my missing value happened in my comment above. When I went to create the table myself on Windows, it creates this error

    "#1101 - BLOB/TEXT column 'notes' can't have a default value"

    I'm not sure how many other tables are missing, but I will continue with install/drop openEMR to fix as many errors before I continue trying to use openemr, and would have no issue with mentioning whatever problem I come across if its of any use.

    "On MS Windows the "no DEFAULT" rule is an error, while on other platforms it is often a warning. While not a bug, it's possible to get trapped by this if you write code on a lenient platform, and later run it on a strict platform."

    http://dev.mysql.com/doc/refman/5.5/en/blob.html

    Do I have to fill in all the values in order to create a patient, or can I simply fill in the who values to create the patient?

     
    Last edit: Barry R. Dick 2014-06-18
  • Barry R. Dick
    Barry R. Dick
    2014-06-19

    history_data , lang_custom , rule_action_item , transactions, procedure_providers , procedure_questions , procedure_order , procedure_order_code , procedure_report , procedure_result ,

    The following tables are not created due to a similar error mentioned in the previous comment. "#1101 - BLOB/TEXT column 'notes' can't have a default value"

    This affects text / mediumtext / longtext and all the blobs as well. Are these pretty relevant tables and is this considered a bug and a useable solution.

     
  • Barry R. Dick
    Barry R. Dick
    2014-06-20

    I discovered that I have to press the 'Confirm Create New Patient' button twice, as doing it once opens up a 'New Window', the second time actually posts the data, but I have yet to create a patient after mysql errors.

    http://localhost/openemr/interface/new/new_search_popup.php?mf_fname=Barry&mf_lname=Dick&mf_DOB=----&mf_financial_review=2014-06-19&mf_deceased_date=2014-06-19

    Also upon creating the patient, there is allowance or chance that a second patient could be created upon accidental repost / reload, which does require authorization, but again with accidental pressing of buttons, there could be multiple patient records. Maybe create a list of searches before creating a new patient if multiple results exist.

    After creating the initial patient, and after realizing that the window that does popup is in fact that search function I mentioned above, but there are still ways around that.

    The last error I get has to do with a missing table previously mentioned:

    Notice: Undefined variable: set in C:\Apache24\htdocs\openemr\library\patient.inc on line 919
    ERROR: insert failed: insert into history_data set pid = ?, date = NOW()

    Error: Table 'openemr.history_data' doesn't exist

    But what is also interesting is that the table codes appears to crash once this result is made, and the error appears, every time.

    "#144 - Table '.\openemr\codes' is marked as crashed and last (automatic?) repair failed"

    ERROR: insert failed: INSERT INTO patient_data SET pid = '1', date = NOW(), title = 'Mr.', fname = 'Barry', mname = 'R', lname = 'Dick', pubpid = '1', DOB = '----', sex = 'Male', ss = '', drivers_license = '', status = 'single', genericname1 = '', genericval1 = '', genericname2 = '', genericval2 = '', street = '---', city = '----', state = '', postal_code = '---', country_code = 'Canada', mothersname = '', guardiansname = '', contact_relationship = '', phone_contact = '----', phone_home = '----', phone_biz = '------', phone_cell = '', email = '-----', providerID = '1', ref_providerID = '1', 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 = '', occupation = 'Technician', language = 'English', ethnicity = '', race = 'white', financial_review = '2014-06-19', family_size = '', monthly_income = '', homeless = '', interpretter = '', migrantseasonal = '', referral_source = '', vfc = '', deceased_date = '', deceased_reason = ''

    Error: Incorrect datetime value: '' for column 'providerID' at row 1
    Error: Incorrect integer value: '' for column 'ref_providerID' at row 1
    Error: Incorrect datetime value: '' for column 'financial_review' at row 1
    Error: Incorrect datetime value: '' for column 'deceased_date' at row 1

    /library/log.php

    line 126 - mysqli_insert_id($GLOBALS['dbh']);

    /library/calendar.inc

    line 45 - mysqli_real_escape_string($GLOBALS['dbh'], $name)

     
    Last edit: Barry R. Dick 2014-06-20
  • Barry R. Dick
    Barry R. Dick
    2014-06-20

    These are some more recent and optional fixes to have OpenEMR 4.1.2 (7) to work with php5 and mysqli.

    http://pastebin.com/n7UWFgNL

     
  • Brady Miller
    Brady Miller
    2014-06-21

    Hi Barry,

    Sounds like two issues here:

    First is dealing with the BLOB/TEXT column 'notes' can't have a default value error, which has been discussed here:
    https://sourceforge.net/p/openemr/discussion/202504/thread/b3f74c7a/#f5b6
    As discussed there, will be a good idea to clean the database.sql file to rid this error since newer versions of mysql will break by default.

    Second is migrating away from mysql, which is briefly outlined here:
    http://www.open-emr.org/wiki/index.php/Active_Projects#Convert_to_MySQLi
    (There has also been some discussion to instead switch to the Zend database library since adodb seems to no longer be updated; but at this point anything will suffice since mysql in php will be going away in the future)

    For each of these, recommend working on separate branches in git(one for the BLOB/TEXT and other for mysqli conversion), so developers can review/test this stuff and provide guidance. Note that the sql/inc.php has pulled in a bunch of adodb mysql library to deal with a nasty bug that happens in OpenEMR's logging engine and pulling out insert id's.

    Check out this tutorial on setting up an OpenEMR git/github repo:
    http://www.open-emr.org/wiki/index.php/Git_for_dummies

    -brady
    OpenEMR

     
  • Barry R. Dick
    Barry R. Dick
    2014-06-22

    What about how the supposed change shown here ?

    https://github.com/openemr/openemr/commit/12dc27efc299a7c5ee62e6a29e373bee3f4c6df3

    Is not shown / affected in the recent update v4.1.2 (7) (except when shown in the code (online))

    Or how I can't create a patient without the (Choices) Provider:,
    (Choices) Referring Provider:, (Stats) Financial Review Date:, and (Misc) Date Deceased: being filled in because it doesn't like the data type selected for the particular column

    Are there any other changes that are not impacted?
    Are any of the notice messages of interest to you in order to have fixed?
    Are there further ways to reduce any of the numerous use of files through out the project?
    Can any of the code be reused?

    "#144 - Table '.\openemr\codes' is marked as crashed and last (automatic?) repair failed" -- This is constantly occurring.

    I read another forum where you had thought thats all it would take in order to update OpenEMR, was to change a few lines, which I did in order for it to support my environment. I wanted to see anything special that you had in your Dashboard / Manager, and how you have written your code. I have only gotten through 2 days, and I still haven't even got it fully functioning. I will see if there are any further solutions and further see what your code libraries, or will have move on to something else.

    I just thought that code could assist you in some way.

     
    Last edit: Barry R. Dick 2014-06-22
  • Kevin Yeh
    Kevin Yeh
    2014-06-22

    Running with "strict" mode is probably more of the issue than mysqli vs mysql.