Database.sql errors in 3_2

OpenEMR Development
1

saikensf wrote on Wednesday, June 02, 2010:

I’ve never submitted a patch to an open source project before so I’ll be following the wiki instructions shortly and any pointers
are welcome.  While reverse engineering the setup.php so I could get a sense of where/how things are configured I ran
database.sql by hand.  While reviewing the logs for errors/warnings ( and filtering out all the noise generated by the 1051 thrown
on every delete if exists ) I found 10 legit issues, one of which looks marginally serious.

So the big one.

ERROR 1136 (21S01): Column count doesn’t match value count at row 1

An insert into openemr_postcalendar_categories for 5, Office Visit, is malformed.  It has 6 numbers after the long text blob
instead of 7.  I’m guessing that the fix is to add an extra ‘0’ in the final field. 

Eight of the remaining warnings are for text fields that are being assigned a default value. 

Warning (Code 1101): BLOB/TEXT column ‘exams’ can’t have a default value
Warning (Code 1101): BLOB/TEXT column ‘userarea11’ can’t have a default value
Warning (Code 1101): BLOB/TEXT column ‘userarea12’ can’t have a default value
Warning (Code 1101): BLOB/TEXT column ‘body’ can’t have a default value
Warning (Code 1101): BLOB/TEXT column ‘reply_findings’ can’t have a default value
Warning (Code 1101): BLOB/TEXT column ‘reply_services’ can’t have a default value
Warning (Code 1101): BLOB/TEXT column ‘reply_recommend’ can’t have a default value
Warning (Code 1101): BLOB/TEXT column ‘reply_rx_refer’ can’t have a default value

On the surface, just removing the Default ’ ’ should do it … it will certainly result in tables that are the same structure as are
currently being generated.  However, these columns are all NOT NULL.  And a lot of times when people combine NOT NULL and
Default Value that means that it is valid to insert nothing into it.  In which case some or all of them also need to be made
nullable so people with no data for those columns can insert.

If so, can someone more familiar with the database architecture let me know which columns should be nullable?

The least of the warnings is a comment missing its space ( ------ ).

Thank You,
Simone Aiken

2

saikensf wrote on Wednesday, June 02, 2010:

Also, for diff oriented people:

axe:sql saiken$ svn diff -r 222 database.sql
Index: database.sql

– database.sql        (revision 222)
+++ database.sql        (working copy)
@@ -1311,7 +1311,7 @@
   `name_2` varchar(255) default NULL,
   `value_2` varchar(255) default NULL,
   `additional_history` text,
-  `exams`      text         NOT NULL DEFAULT ‘’,
+  `exams`      text         NOT NULL,
   `usertext11` varchar(255) NOT NULL DEFAULT ‘’,
   `usertext12` varchar(255) NOT NULL DEFAULT ‘’,
   `usertext13` varchar(255) NOT NULL DEFAULT ‘’,
@@ -1337,8 +1337,8 @@
   `userdate13` date DEFAULT NULL,
   `userdate14` date DEFAULT NULL,
   `userdate15` date DEFAULT NULL,
-  `userarea11` text NOT NULL DEFAULT ‘’,
-  `userarea12` text NOT NULL DEFAULT ‘’,
+  `userarea11` text NOT NULL,
+  `userarea12` text NOT NULL,
   PRIMARY KEY  (`id`),
   KEY `pid` (`pid`)
) ENGINE=MyISAM AUTO_INCREMENT=1 ;
@@ -2142,7 +2142,7 @@

  • Dumping data for table `openemr_postcalendar_categories`

-INSERT INTO `openemr_postcalendar_categories` VALUES (5, ‘Office Visit’, ‘#FFFFCC’, ‘Normal Office Visit’, 0, NULL, ‘a:5:{s:17:“event_repeat_freq”;s:1:“0”;s:22:“event_repeat_freq_type”;s:1:“0”;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”;}’, 0, 900, 0, 0, 0, 0);
+INSERT INTO `openemr_postcalendar_categories` VALUES (5, ‘Office Visit’, ‘#FFFFCC’, ‘Normal Office Visit’, 0, NULL, ‘a:5:{s:17:“event_repeat_freq”;s:1:“0”;s:22:“event_repeat_freq_type”;s:1:“0”;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”;}’, 0, 900, 0, 0, 0, 0, 0);
INSERT INTO `openemr_postcalendar_categories` VALUES (4, ‘Vacation’, ‘#EFEFEF’, ‘Reserved for use to define Scheduled Vacation Time’, 0, NULL, ‘a:5:{s:17:“event_repeat_freq”;s:1:“0”;s:22:“event_repeat_freq_type”;s:1:“0”;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”;}’, 0, 0, 0, 0, 0, 1, 0);
INSERT INTO `openemr_postcalendar_categories` VALUES (1, ‘No Show’, ‘#DDDDDD’, ‘Reserved to define when an event did not occur as specified.’, 0, NULL, ‘a:5:{s:17:“event_repeat_freq”;s:1:“0”;s:22:“event_repeat_freq_type”;s:1:“0”;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”;}’, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO `openemr_postcalendar_categories` VALUES (2, ‘In Office’, ‘#99CCFF’, ‘Reserved todefine when a provider may haveavailable appointments after.’, 1, NULL, ‘a:5:{s:17:“event_repeat_freq”;s:1:“1”;s:22:“event_repeat_freq_type”;s:1:“4”;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”;}’, 0, 0, 1, 3, 2, 0, 0);
@@ -2660,7 +2660,7 @@
   `id`                      bigint(20)   NOT NULL auto_increment,
   `date`                    datetime     default NULL,
   `title`                   varchar(255) NOT NULL DEFAULT ‘’,
-  `body`                    longtext     NOT NULL DEFAULT ‘’,
+  `body`                    longtext     NOT NULL,
   `pid`                     bigint(20)   default NULL,
   `user`                    varchar(255) NOT NULL DEFAULT ‘’,
   `groupname`               varchar(255) NOT NULL DEFAULT ‘’,
@@ -2678,10 +2678,10 @@
   `reply_init_diag`         varchar(255) NOT NULL DEFAULT ‘’,
   `reply_final_diag`        varchar(255) NOT NULL DEFAULT ‘’,
   `reply_documents`         varchar(255) NOT NULL DEFAULT ‘’,
-  `reply_findings`          text         NOT NULL DEFAULT ‘’,
-  `reply_services`          text         NOT NULL DEFAULT ‘’,
-  `reply_recommend`         text         NOT NULL DEFAULT ‘’,
-  `reply_rx_refer`          text         NOT NULL DEFAULT ‘’,
+  `reply_findings`          text         NOT NULL,
+  `reply_services`          text         NOT NULL,
+  `reply_recommend`         text         NOT NULL,
+  `reply_rx_refer`          text         NOT NULL,
   PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 ;

@@ -2765,7 +2765,7 @@
   PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

± -------------------------------------------------------

  • Table structure for table `automatic_notification`

axe:sql saiken$ svn diff -r 222 database.sql | less
Index: database.sql

– database.sql        (revision 222)
+++ database.sql        (working copy)
@@ -1311,7 +1311,7 @@
   `name_2` varchar(255) default NULL,
   `value_2` varchar(255) default NULL,
   `additional_history` text,
-  `exams`      text         NOT NULL DEFAULT ‘’,
+  `exams`      text         NOT NULL,
   `usertext11` varchar(255) NOT NULL DEFAULT ‘’,
   `usertext12` varchar(255) NOT NULL DEFAULT ‘’,
   `usertext13` varchar(255) NOT NULL DEFAULT ‘’,
@@ -1337,8 +1337,8 @@
   `userdate13` date DEFAULT NULL,
   `userdate14` date DEFAULT NULL,
   `userdate15` date DEFAULT NULL,
-  `userarea11` text NOT NULL DEFAULT ‘’,
-  `userarea12` text NOT NULL DEFAULT ‘’,
+  `userarea11` text NOT NULL,
+  `userarea12` text NOT NULL,
   PRIMARY KEY  (`id`),
   KEY `pid` (`pid`)
) ENGINE=MyISAM AUTO_INCREMENT=1 ;
) ENGINE=MyISAM AUTO_INCREMENT=1 ;
@@ -2142,7 +2142,7 @@

  • Dumping data for table `openemr_postcalendar_categories`

-INSERT INTO `openemr_postcalendar_categories` VALUES (5, ‘Office Visit’, ‘#FFFFCC’, ‘Normal Office Visit’, 0, NULL, 'a:5:{s:17:“event_repeat_freq”;s:1:“0”;s:22:"event_repeat_freq_typ
+INSERT INTO `openemr_postcalendar_categories` VALUES (5, ‘Office Visit’, ‘#FFFFCC’, ‘Normal Office Visit’, 0, NULL, 'a:5:{s:17:“event_repeat_freq”;s:1:“0”;s:22:"event_repeat_freq_typ
INSERT INTO `openemr_postcalendar_categories` VALUES (4, ‘Vacation’, ‘#EFEFEF’, ‘Reserved for use to define Scheduled Vacation Time’, 0, NULL, 'a:5:{s:17:“event_repeat_freq”;s:1:“0”;
INSERT INTO `openemr_postcalendar_categories` VALUES (1, ‘No Show’, ‘#DDDDDD’, ‘Reserved to define when an event did not occur as specified.’, 0, NULL, 'a:5:{s:17:“event_repeat_freq”
INSERT INTO `openemr_postcalendar_categories` VALUES (1, ‘No Show’, ‘#DDDDDD’, ‘Reserved to define when an event did not occur as specified.’, 0, NULL, 'a:5:{s:17:“event_repeat_freq”
@@ -2660,7 +2660,7 @@
   `id`                      bigint(20)   NOT NULL auto_increment,
   `date`                    datetime     default NULL,
   `title`                   varchar(255) NOT NULL DEFAULT ‘’,
-  `body`                    longtext     NOT NULL DEFAULT ‘’,
+  `body`                    longtext     NOT NULL,
   `pid`                     bigint(20)   default NULL,
   `user`                    varchar(255) NOT NULL DEFAULT ‘’,
   `groupname`               varchar(255) NOT NULL DEFAULT ‘’,
@@ -2678,10 +2678,10 @@
   `reply_init_diag`         varchar(255) NOT NULL DEFAULT ‘’,
   `reply_final_diag`        varchar(255) NOT NULL DEFAULT ‘’,
   `reply_documents`         varchar(255) NOT NULL DEFAULT ‘’,
-  `reply_findings`          text         NOT NULL DEFAULT ‘’,
-  `reply_services`          text         NOT NULL DEFAULT ‘’,
-  `reply_recommend`         text         NOT NULL DEFAULT ‘’,
-  `reply_rx_refer`          text         NOT NULL DEFAULT ‘’,
+  `reply_findings`          text         NOT NULL,
+  `reply_services`          text         NOT NULL,
+  `reply_recommend`         text         NOT NULL,
+  `reply_rx_refer`          text         NOT NULL,
   PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 ;

@@ -2765,7 +2765,7 @@
   PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

± -------------------------------------------------------

  • Table structure for table `automatic_notification`
3

saikensf wrote on Wednesday, June 02, 2010:

Eep, doubling … so that would be a copy/paste bug on my part =D

4

bradymiller wrote on Wednesday, June 02, 2010:

Rod,
What are your thoughts with this? looks like text entries in mysql throw an error by assigning NOT NULL and a default value of ‘’. Changes look fine to me, but I admittedly don’t know enough to make the decision here.
-brady

5

sunsetsystems wrote on Monday, June 14, 2010:

I checked in a 1-line fix for the column count error in 3.2.  It appears to be already fixed in the tip.

Agreed that the default values should probably be omitted from the TEXT fields but I didn’t check in a change for that since it’s just a warning and I don’t have time to test it properly right now.  If anyone can do that and report the results, please post here.

Generally, most fields should be NOT NULL.  The reason is that the possibility of null values complicates queries.  Use them where there is a good reason, such as dates that are optional (in my view ‘0000-00-00’ is much uglier than a null date).

Thanks!

Rod
www.sunsetsystems.com

6

bradymiller wrote on Tuesday, June 15, 2010:

just to cross-reference the tracker item associated with this item:
http://sourceforge.net/tracker/?func=detail&aid=3011876&group_id=60081&atid=493003