BUG: Facilities Editor

system · February 22, 2010, 2:53am

yehster wrote on Monday, February 22, 2010:

Using 3.2.0 running Windows 7, MySQL 5.1 IIS 7

If I uncheck any of the 3 fieldss
Billing Location:
Accepts Assignment
Service Location
and hit update, I get the following error when the SQL tries to update. it looks like it’s trying to insert ‘’ (empty string) when it is expecting an integer

ERROR: query failed: update facility set name=‘Your Clinic Name Here’, phone=‘000-000-0000’, fax=‘000-000-0000’, street=’’, city=’’, state=’’, postal_code=’’, country_code=’’, federal_ein=’’, service_location=‘1’, billing_location=’’, accepts_assignment=‘1’, pos_code=‘01’, domain_identifier=’’, facility_npi=’’, attn=’’ where id=‘3’

Error: Incorrect integer value: ‘’ for column ‘billing_location’ at row 1

system · February 22, 2010, 3:02am

yehster wrote on Monday, February 22, 2010:

It looks like I have a more general problem. I tried other forms and am having a similar problem in the calendar: I’m guessing it’s something to do with my DB configuration and it not wanting to accept nulls. I will continue to investigate.

ERROR: insert failed: INSERT INTO openemr_postcalendar_events ( pc_catid, pc_multiple, pc_aid, pc_pid, pc_title, pc_time, pc_hometext, pc_informant, pc_eventDate, pc_endDate, pc_duration, pc_recurrtype, pc_recurrspec, pc_startTime, pc_endTime, pc_alldayevent, pc_apptstatus, pc_prefcatid, pc_location, pc_eventstatus, pc_sharing, pc_facility ) VALUES ( ‘9’, ‘’, ‘2’, ‘’, ‘Established Patient’, NOW(), ‘’, ‘2’, ‘2010-02-22’, ‘0000-00-00’, ‘900’, ‘0’, ‘a:6:{s:17:“event_repeat_freq”;N;s:22:“event_repeat_freq_type”;N;s:19:“event_repeat_on_num”;s:1:“1”;s:19:“event_repeat_on_day”;s:1:“0”;s:20:“event_repeat_on_freq”;s:1:“0”;s:6:“exdate”;s:0:"";}’, ‘9:0:00’, ‘9:15:00’, ‘0’, ‘-’, ‘0’, ‘a:6:{s:14:“event_location”;s:0:"";s:13:“event_street1”;s:0:"";s:13:“event_street2”;s:0:"";s:10:“event_city”;s:0:"";s:11:“event_state”;s:0:"";s:12:“event_postal”;s:0:"";}’, 1, 1, 3 )

Error: Incorrect integer value: ‘’ for column ‘pc_multiple’ at row 1

system · February 22, 2010, 12:43pm

visolveemr wrote on Monday, February 22, 2010:

Hi Kevin,

Can you please share the following?
(i) mysql configuration (my.cnf)
(ii) “show variables” output from mysql,

Thanks
ViCarePlus Team
www.vicareplus.com

system · February 22, 2010, 12:53pm

dlee5400 wrote on Monday, February 22, 2010:

I would also like to see your Environment Variables as well

Thanks
dlee5400

system · February 22, 2010, 5:10pm

yehster wrote on Monday, February 22, 2010:

I am not using a my.cnf file (maybe that is related to my problem)

Here are my show variables results
Environment variables to follow

auto_increment_increment’, ‘1’
‘auto_increment_offset’, ‘1’
‘autocommit’, ‘ON’
‘automatic_sp_privileges’, ‘ON’
‘back_log’, ‘50’
‘basedir’, ‘C:\Program Files\MySQL\MySQL Server 5.1’
‘big_tables’, ‘OFF’
‘binlog_cache_size’, ‘32768’
‘binlog_direct_non_transactional_updates’, ‘OFF’
‘binlog_format’, ‘STATEMENT’
‘bulk_insert_buffer_size’, ‘8388608’
‘character_set_client’, ‘utf8’
‘character_set_connection’, ‘utf8’
‘character_set_database’, ‘utf8’
‘character_set_filesystem’, ‘binary’
‘character_set_results’, ‘utf8’
‘character_set_server’, ‘utf8’
‘character_set_system’, ‘utf8’
‘character_sets_dir’, ‘C:\Program Files\MySQL\MySQL Server 5.1\share\charsets’
‘collation_connection’, ‘utf8_general_ci’
‘collation_database’, ‘utf8_general_ci’
‘collation_server’, ‘utf8_general_ci’
‘completion_type’, ‘0’
‘concurrent_insert’, ‘1’
‘connect_timeout’, ‘10’
‘datadir’, ‘C:\ProgramData\MySQL\MySQL Server 5.1\Data’
‘date_format’, ‘%Y-%m-%d’
‘datetime_format’, ‘%Y-%m-%d %H:%i:%s’
‘default_week_format’, ‘0’
‘delay_key_write’, ‘ON’
‘delayed_insert_limit’, ‘100’
‘delayed_insert_timeout’, ‘300’
‘delayed_queue_size’, ‘1000’
‘div_precision_increment’, ‘4’
‘engine_condition_pushdown’, ‘ON’
‘error_count’, ‘0’
‘event_scheduler’, ‘OFF’
‘expire_logs_days’, ‘0’
‘flush’, ‘OFF’
‘flush_time’, ‘1800’
‘foreign_key_checks’, ‘ON’
‘ft_boolean_syntax’, ‘+ -><()~*:""&|’
‘ft_max_word_len’, ‘84’
‘ft_min_word_len’, ‘4’
‘ft_query_expansion_limit’, ‘20’
‘ft_stopword_file’, ‘(built-in)’
‘general_log’, ‘OFF’
‘general_log_file’, ‘C:\ProgramData\MySQL\MySQL Server 5.1\Data\pinky.log’
‘group_concat_max_len’, ‘1024’
‘have_community_features’, ‘YES’
‘have_compress’, ‘YES’
‘have_crypt’, ‘NO’
‘have_csv’, ‘YES’
‘have_dynamic_loading’, ‘YES’
‘have_geometry’, ‘YES’
‘have_innodb’, ‘YES’
‘have_ndbcluster’, ‘NO’
‘have_openssl’, ‘DISABLED’
‘have_partitioning’, ‘YES’
‘have_query_cache’, ‘YES’
‘have_rtree_keys’, ‘YES’
‘have_ssl’, ‘DISABLED’
‘have_symlink’, ‘YES’
‘hostname’, ‘pinky’
‘identity’, ‘0’
‘ignore_builtin_innodb’, ‘OFF’
‘init_connect’, ‘’
‘init_file’, ‘’
‘init_slave’, ‘’
‘innodb_adaptive_hash_index’, ‘ON’
‘innodb_additional_mem_pool_size’, ‘2097152’
‘innodb_autoextend_increment’, ‘8’
‘innodb_autoinc_lock_mode’, ‘1’
‘innodb_buffer_pool_size’, ‘41943040’
‘innodb_checksums’, ‘ON’
‘innodb_commit_concurrency’, ‘0’
‘innodb_concurrency_tickets’, ‘500’
‘innodb_data_file_path’, ‘ibdata1:10M:autoextend’
‘innodb_data_home_dir’, ‘’
‘innodb_doublewrite’, ‘ON’
‘innodb_fast_shutdown’, ‘1’
‘innodb_file_io_threads’, ‘4’
‘innodb_file_per_table’, ‘OFF’
‘innodb_flush_log_at_trx_commit’, ‘1’
‘innodb_flush_method’, ‘’
‘innodb_force_recovery’, ‘0’
‘innodb_lock_wait_timeout’, ‘50’
‘innodb_locks_unsafe_for_binlog’, ‘OFF’
‘innodb_log_buffer_size’, ‘1048576’
‘innodb_log_file_size’, ‘20971520’
‘innodb_log_files_in_group’, ‘2’
‘innodb_log_group_home_dir’, ‘.’
‘innodb_max_dirty_pages_pct’, ‘90’
‘innodb_max_purge_lag’, ‘0’
‘innodb_mirrored_log_groups’, ‘1’
‘innodb_open_files’, ‘300’
‘innodb_rollback_on_timeout’, ‘OFF’
‘innodb_stats_on_metadata’, ‘ON’
‘innodb_support_xa’, ‘ON’
‘innodb_sync_spin_loops’, ‘20’
‘innodb_table_locks’, ‘ON’
‘innodb_thread_concurrency’, ‘8’
‘innodb_thread_sleep_delay’, ‘10000’
‘innodb_use_legacy_cardinality_algorithm’, ‘ON’
‘insert_id’, ‘0’
‘interactive_timeout’, ‘28800’
‘join_buffer_size’, ‘131072’
‘keep_files_on_create’, ‘OFF’
‘key_buffer_size’, ‘23068672’
‘key_cache_age_threshold’, ‘300’
‘key_cache_block_size’, ‘1024’
‘key_cache_division_limit’, ‘100’
‘language’, ‘C:\Program Files\MySQL\MySQL Server 5.1\share\english’
‘large_files_support’, ‘ON’
‘large_page_size’, ‘0’
‘large_pages’, ‘OFF’
‘last_insert_id’, ‘0’
‘lc_time_names’, ‘en_US’
‘license’, ‘GPL’
‘local_infile’, ‘ON’
‘log’, ‘OFF’
‘log_bin’, ‘OFF’
‘log_bin_trust_function_creators’, ‘OFF’
‘log_bin_trust_routine_creators’, ‘OFF’
‘log_error’, ‘C:\ProgramData\MySQL\MySQL Server 5.1\Data\pinky.err’
‘log_output’, ‘FILE’
‘log_queries_not_using_indexes’, ‘OFF’
‘log_slave_updates’, ‘OFF’
‘log_slow_queries’, ‘OFF’
‘log_warnings’, ‘1’
‘long_query_time’, ‘10.000000’
‘low_priority_updates’, ‘OFF’
‘lower_case_file_system’, ‘ON’
‘lower_case_table_names’, ‘1’
‘max_allowed_packet’, ‘1048576’
‘max_binlog_cache_size’, ‘4294963200’
‘max_binlog_size’, ‘1073741824’
‘max_connect_errors’, ‘10’
‘max_connections’, ‘100’
‘max_delayed_threads’, ‘20’
‘max_error_count’, ‘64’
‘max_heap_table_size’, ‘16777216’
‘max_insert_delayed_threads’, ‘20’
‘max_join_size’, ‘18446744073709551615’
‘max_length_for_sort_data’, ‘1024’
‘max_prepared_stmt_count’, ‘16382’
‘max_relay_log_size’, ‘0’
‘max_seeks_for_key’, ‘4294967295’
‘max_sort_length’, ‘1024’
‘max_sp_recursion_depth’, ‘0’
‘max_tmp_tables’, ‘32’
‘max_user_connections’, ‘0’
‘max_write_lock_count’, ‘4294967295’
‘min_examined_row_limit’, ‘0’
‘multi_range_count’, ‘256’
‘myisam_data_pointer_size’, ‘6’
‘myisam_max_sort_file_size’, ‘107374182400’
‘myisam_mmap_size’, ‘18446744073709551615’
‘myisam_recover_options’, ‘OFF’
‘myisam_repair_threads’, ‘1’
‘myisam_sort_buffer_size’, ‘31457280’
‘myisam_stats_method’, ‘nulls_unequal’
‘myisam_use_mmap’, ‘OFF’
‘named_pipe’, ‘OFF’
‘net_buffer_length’, ‘16384’
‘net_read_timeout’, ‘30’
‘net_retry_count’, ‘10’
‘net_write_timeout’, ‘60’
‘new’, ‘OFF’
‘old’, ‘OFF’
‘old_alter_table’, ‘OFF’
‘old_passwords’, ‘OFF’
‘open_files_limit’, ‘622’
‘optimizer_prune_level’, ‘1’
‘optimizer_search_depth’, ‘62’
‘optimizer_switch’, ‘index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on’
‘pid_file’, ‘C:\ProgramData\MySQL\MySQL Server 5.1\Data\pinky.pid’
‘plugin_dir’, ‘C:\Program Files\MySQL\MySQL Server 5.1\lib/plugin’
‘port’, ‘3306’
‘preload_buffer_size’, ‘32768’
‘profiling’, ‘OFF’
‘profiling_history_size’, ‘15’
‘protocol_version’, ‘10’
‘pseudo_thread_id’, ‘38’
‘query_alloc_block_size’, ‘8192’
‘query_cache_limit’, ‘1048576’
‘query_cache_min_res_unit’, ‘4096’
‘query_cache_size’, ‘0’
‘query_cache_type’, ‘ON’
‘query_cache_wlock_invalidate’, ‘OFF’
‘query_prealloc_size’, ‘8192’
‘rand_seed1’, ‘’
‘rand_seed2’, ‘’
‘range_alloc_block_size’, ‘4096’
‘read_buffer_size’, ‘65536’
‘read_only’, ‘OFF’
‘read_rnd_buffer_size’, ‘262144’
‘relay_log’, ‘’
‘relay_log_index’, ‘’
‘relay_log_info_file’, ‘relay-log.info’
‘relay_log_purge’, ‘ON’
‘relay_log_space_limit’, ‘0’
‘report_host’, ‘’
‘report_password’, ‘’
‘report_port’, ‘3306’
‘report_user’, ‘’
‘rpl_recovery_rank’, ‘0’
‘secure_auth’, ‘OFF’
‘secure_file_priv’, ‘’
‘server_id’, ‘0’
‘shared_memory’, ‘OFF’
‘shared_memory_base_name’, ‘MYSQL’
‘skip_external_locking’, ‘ON’
‘skip_networking’, ‘OFF’
‘skip_show_database’, ‘OFF’
‘slave_compressed_protocol’, ‘OFF’
‘slave_exec_mode’, ‘STRICT’
‘slave_load_tmpdir’, ‘C:\Windows\TEMP’
‘slave_net_timeout’, ‘3600’
‘slave_skip_errors’, ‘OFF’
‘slave_transaction_retries’, ‘10’
‘slow_launch_time’, ‘2’
‘slow_query_log’, ‘OFF’
‘slow_query_log_file’, ‘C:\ProgramData\MySQL\MySQL Server 5.1\Data\pinky-slow.log’
‘socket’, ‘MySQL’
‘sort_buffer_size’, ‘262144’
‘sql_auto_is_null’, ‘ON’
‘sql_big_selects’, ‘ON’
‘sql_big_tables’, ‘OFF’
‘sql_buffer_result’, ‘OFF’
‘sql_log_bin’, ‘ON’
‘sql_log_off’, ‘OFF’
‘sql_log_update’, ‘ON’
‘sql_low_priority_updates’, ‘OFF’
‘sql_max_join_size’, ‘18446744073709551615’
‘sql_mode’, ‘STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’
‘sql_notes’, ‘ON’
‘sql_quote_show_create’, ‘ON’
‘sql_safe_updates’, ‘OFF’
‘sql_select_limit’, ‘18446744073709551615’
‘sql_slave_skip_counter’, ‘’
‘sql_warnings’, ‘OFF’
‘ssl_ca’, ‘’
‘ssl_capath’, ‘’
‘ssl_cert’, ‘’
‘ssl_cipher’, ‘’
‘ssl_key’, ‘’
‘storage_engine’, ‘InnoDB’
‘sync_binlog’, ‘0’
‘sync_frm’, ‘ON’
‘system_time_zone’, ‘Eastern Standard Time’
‘table_definition_cache’, ‘256’
‘table_lock_wait_timeout’, ‘50’
‘table_open_cache’, ‘256’
‘table_type’, ‘InnoDB’
‘thread_cache_size’, ‘8’
‘thread_handling’, ‘one-thread-per-connection’
‘thread_stack’, ‘262144’
‘time_format’, ‘%H:%i:%s’
‘time_zone’, ‘SYSTEM’
‘timed_mutexes’, ‘OFF’
‘timestamp’, ‘1266858130’
‘tmp_table_size’, ‘15728640’
‘tmpdir’, ‘C:\Windows\TEMP’
‘transaction_alloc_block_size’, ‘8192’
‘transaction_prealloc_size’, ‘4096’
‘tx_isolation’, ‘REPEATABLE-READ’
‘unique_checks’, ‘ON’
‘updatable_views_with_limit’, ‘YES’
‘version’, ‘5.1.44-community’
‘version_comment’, ‘MySQL Community Server (GPL)’
‘version_compile_machine’, ‘unknown’
‘version_compile_os’, ‘Win64’
‘wait_timeout’, ‘28800’
‘warning_count’, ‘0’

system · February 22, 2010, 8:11pm

yehster wrote on Monday, February 22, 2010:

MySQL was a clean install, which I then configured using Setup.php.

My environment variables:
ALLUSERSPROFILE=C:\ProgramData
APPDATA=C:\Users\yehster\AppData\Roaming
CommonProgramFiles=C:\Program Files\Common Files
CommonProgramFiles(x86)=C:\Program Files (x86)\Common Files
CommonProgramW6432=C:\Program Files\Common Files
COMPUTERNAME=PINKY
ComSpec=C:\Windows\system32\cmd.exe
FP_NO_HOST_CHECK=NO
HOMEDRIVE=C:
HOMEPATH=\Users\yehster
LOCALAPPDATA=C:\Users\yehster\AppData\Local
LOGONSERVER=\\PINKY
NUMBER_OF_PROCESSORS=2
OS=Windows_NT
Path=C:\Program Files (x86)\PHP;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0;C:\Program Files (x86)\Common Files\Acronis\SnapAPI;c:\Program Files (x86)\Microsoft SQL Server\90\Tools\binn\
PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.MSC
PHPRC=C:\Program Files (x86)\PHP\
PROCESSOR_ARCHITECTURE=AMD64
PROCESSOR_IDENTIFIER=AMD64 Family 16 Model 6 Stepping 2, AuthenticAMD
PROCESSOR_LEVEL=16
PROCESSOR_REVISION=0602
ProgramData=C:\ProgramData
ProgramFiles=C:\Program Files
ProgramFiles(x86)=C:\Program Files (x86)
ProgramW6432=C:\Program Files
PROMPT=$P$G
PSModulePath=C:\Windows\system32\WindowsPowerShell\v1.0\Modules\
PUBLIC=C:\Users\Public
SESSIONNAME=Console
SystemDrive=C:
SystemRoot=C:\Windows
TEMP=C:\Users\yehster\AppData\Local\Temp
TMP=C:\Users\yehster\AppData\Local\Temp
USERDOMAIN=pinky
USERNAME=yehster
USERPROFILE=C:\Users\yehster
VS90COMNTOOLS=c:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\Tools\
windir=C:\Windows

system · February 22, 2010, 8:54pm

dlee5400 wrote on Monday, February 22, 2010:

Ok, in windows i would try adding the following paths to your current paths or thier Equivalent depending on your path structure:

C:\xampp\perl\site\bin;C:\xampp\perl\bin;C:\xampp\perl\bin;c:\xampp\mysql\bin

system · February 22, 2010, 9:01pm

dlee5400 wrote on Monday, February 22, 2010:

Sorry, to your current environment variables under PATH… Don’t change anything just append the locations of PERL BIN, PERL SITE BIN and MYSQL BIN…

Let me know!
dlee5400

system · February 22, 2010, 10:32pm

yehster wrote on Monday, February 22, 2010:

I added perl and mysql bin directories to my PATH in the environment and I still get the same errors.

system · February 22, 2010, 10:51pm

yehster wrote on Monday, February 22, 2010:

I have narrowed down the issue to MySQL configuration. I’m not sure what specifically, but instead of using the instance of MySQL I installed from the package I downloaded from MySQL(5.1.44) and installed as a service, I switched to the mysql version included in the xampp package from 3.1.0 which is MySQL version (5.1.30). This gives me a working system, but it doesn’t identify the configuration issue.

system · February 23, 2010, 1:19am

dlee5400 wrote on Tuesday, February 23, 2010:

This is something to check on, Brady once recommended this to me as I was hunting down the MYSQL stuff:

Check your mysql configuration file (my.cnf). If your sql-mode parameter is not set to ‘’, then do the following:

1) Configure the sql-mode parameter in your mysql configuration file (my.cnf) to be blank: sql-mode=’’
(it will likely have settings which shouldn’t be there- for some reason some mysql windows releases set this)

system · February 23, 2010, 1:23am

dlee5400 wrote on Tuesday, February 23, 2010:

Some other thoughts as well, have you checked the Global.php file for the following settings?:

// Operating system specific settings
//  Currently used in the Adminstration->Backup page within OpenEMR
//  -Note the temporary file directory parameter is only used when
//    php version is < 5.2.1 (otherwise the temporary directory that
//    is set within php is used)
//
// WINDOWS Specific Settings
$GLOBALS['mysql_bin_dir_win'] = "C:/xampp/mysql/bin";
$GLOBALS['perl_bin_dir_win'] = "C:/xampp/perl/bin";
$GLOBALS['temporary_files_dir_win'] = "C:/windows/temp";

system · February 23, 2010, 6:05pm

yehster wrote on Tuesday, February 23, 2010:

Thanks all. I’ve determined the exact nature of the issue:
changing
‘sql_mode’, ‘STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’
and removing STRICT_TRANS_TABLES
using my sql console and issuing:

SET global sql_mode=‘NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;

corrected the issue.
Making sure that STRICT_TRANS_TABLES is not enabled in mysql is something that could be added to the install guide.

jmm72011 · September 11, 2019, 6:25pm

Hi yehster, i am new to openEMR and am trying to insert into the openemr_postcalendar_events table but i don’t understand the format for the recurrspec column. I’ve tried looking through documentation but i haven’t gotten what i need. I figured i’d ask since it seems you were trying to do the same almost a decade ago.

Thanks in advance!