All,
PROBLEM/OVERVIEW
I had an initial Jahia test instance running against Postgresql 7.4. I then transfered data to Mysql 4.0.20. The transfer appeared to happen successfully, but when I restarted Jahia, I was getting errors...specifically missing field definition data when the JahiaData object was being created. I enabled debug on the Jahia, recreated an empty Mysql database, and transfered the data again from Postgresql. It turns out Mysql was throwing a constraint error (Mysql error 1062), due to case sensitivity is NOT enabled for the "name_jahia_fields_def".
Therefore, because the primary key for table "jahia_fields_def" is composed of "jahiaid_jahia_fields_def" and " name_jahia_fields_def, the tuples (2, 'Title') and (2, 'title') are assumed to be equal.
SOLUTION
In $JAHIA_HOME/tomcat/webapps/jahia/WEB-INF/var/db/mysql.script make the following change the "CREATE TABLE" entry for table "jahia_fields_def", as follows:
CREATE TABLE jahia_fields_def (id_jahia_fields_def mediumint(9),jahiaid_jahia_fields_def mediumint(9) NOT NULL,name_jahia_fields_def varchar(250) BINARY NOT NULL, PRIMARY KEY (jahiaid_jahia_fields_def, name_jahia_fields_def));
By adding the "BINARY" key word to the column definition for "name_jahia_fields_def", this fixed the problem.
I've attached the modified mysql.script as well.
Regards, Jeremy
#### JahiaScripts Database ############################ jahia.database.name = MySQL (MM MySQL JDBC) jahia.database.driver = com.mysql.jdbc.Driver jahia.database.url = jdbc:mysql://localhost/jahia?useUnicode=true&characterEncoding=UTF-8 jahia.database.user = jahia jahia.database.pass = jahia jahia.database.transactions = false jahia.database.support_embedded_select_statement = false #### Automatically creates tables ##################### CREATE TABLE jahia_accounts (id_jahia_accounts mediumint(9), key_jahia_users varchar(50), creation_date_jahia_accounts varchar(20), exp_date_jahia_accounts varchar(20), pwd_exp_date_jahia_accounts varchar(20), last_login_date_jahia_accounts varchar(20), activated_jahia_accounts mediumint(9), id_jahia_sites mediumint(9)); CREATE TABLE jahia_acl (id_jahia_acl mediumint(9) NOT NULL,parent_id_jahia_acl mediumint(9), inheritance_jahia_acl mediumint(9), PRIMARY KEY (id_jahia_acl)); CREATE TABLE jahia_acl_entries (id_jahia_acl mediumint(9) NOT NULL,type_jahia_acl_entries mediumint(9) NOT NULL,target_jahia_acl_entries varchar(50) NOT NULL,entry_state_jahia_acl_entries mediumint(9) NOT NULL,entry_trist_jahia_acl_entries mediumint(9) NOT NULL, PRIMARY KEY (id_jahia_acl, type_jahia_acl_entries, target_jahia_acl_entries)); CREATE TABLE jahia_app_def (id_jahia_app_def mediumint(9) NOT NULL,jahiaid_jahia_app_def mediumint(9),name_jahia_app_def varchar(250),context_jahia_app_def varchar(250),visible_jahia_app_def mediumint(9),shared_jahia_app_def mediumint(9),rights_jahia_app_def mediumint(9),filename_jahia_app_def varchar(100),desc_jahia_app_def varchar(250),PRIMARY KEY (id_jahia_app_def)); CREATE TABLE jahia_appdef_prop(appdefid_appdef_prop mediumint(9) NOT NULL, propname_appdef_prop varchar(250) BINARY NOT NULL, propvalue_appdef_prop varchar(250), PRIMARY KEY (appdefid_appdef_prop, propname_appdef_prop)); CREATE TABLE jahia_audit_log (id_jahia_audit_log mediumint(9) NOT NULL,time_jahia_audit_log varchar(50),username_jahia_audit_log varchar(50),objecttype_jahia_audit_log mediumint(9),objectid_jahia_audit_log mediumint(9),parenttype_jahia_audit_log mediumint(9),parentid_jahia_audit_log mediumint(9),operation_jahia_audit_log varchar(50),site_jahia_audit_log varchar(50),content_jahia_audit_log varchar(50),PRIMARY KEY (id_jahia_audit_log)); CREATE TABLE jahia_autoids (jahia_autoids_tablename varchar(250) NOT NULL,jahia_autoids_currentindex mediumint(9),PRIMARY KEY (jahia_autoids_tablename)); CREATE TABLE jahia_category (id_category mediumint(9) NOT NULL, key_category varchar(250) NOT NULL, aclid_category mediumint(9) NOT NULL, PRIMARY KEY (id_category, key_category, aclid_category)) CREATE TABLE jahia_category_prop(id_category mediumint(9) NOT NULL, name_category_prop varchar(250) BINARY NOT NULL, value_category_prop varchar(250) NOT NULL, PRIMARY KEY (id_category, name_category_prop)) CREATE TABLE jahia_ctn_def (id_jahia_ctn_def mediumint(9) NOT NULL,jahiaid_jahia_ctn_def mediumint(9),name_jahia_ctn_def varchar(250),PRIMARY KEY (id_jahia_ctn_def)); CREATE TABLE jahia_ctn_def_properties (id_jahia_ctn_def_properties mediumint(9) NOT NULL,ctndefid_jahia_ctn_def_prop mediumint(9),pagedefid_jahia_ctn_def_prop mediumint(9),title_jahia_ctn_def_properties varchar(250),PRIMARY KEY (id_jahia_ctn_def_properties)); CREATE TABLE jahia_ctn_entries (id_jahia_ctn_entries mediumint(9) NOT NULL,jahiaid_jahia_ctn_entries mediumint(9),pageid_jahia_ctn_entries mediumint(9),listid_jahia_ctn_entries mediumint(9),ctndefid_jahia_ctn_entries mediumint(9),rank_jahia_ctn_entries mediumint(9),rights_jahia_ctn_entries mediumint(9),version_id bigint(20) NOT NULL,workflow_state mediumint(9) NOT NULL,PRIMARY KEY (id_jahia_ctn_entries,version_id,workflow_state)); CREATE TABLE jahia_ctn_lists (id_jahia_ctn_lists mediumint(9) NOT NULL,parententryid_jahia_ctn_lists mediumint(9),pageid_jahia_ctn_lists mediumint(9),ctndefid_jahia_ctn_lists mediumint(9),rights_jahia_ctn_lists mediumint(9),version_id bigint(20) NOT NULL,workflow_state mediumint(9) NOT NULL,PRIMARY KEY (id_jahia_ctn_lists,version_id,workflow_state)); CREATE TABLE jahia_ctn_struct (ctnsubdefid_jahia_ctn_struct mediumint(9) NOT NULL,objtype_jahia_ctn_struct mediumint(9) NOT NULL,objdefid_jahia_ctn_struct mediumint(9) NOT NULL,rank_jahia_ctn_struct mediumint(9),PRIMARY KEY (ctnsubdefid_jahia_ctn_struct,objtype_jahia_ctn_struct,objdefid_jahia_ctn_struct)); CREATE TABLE jahia_db_test (testfield varchar(255)); CREATE TABLE jahia_fields_data (id_jahia_fields_data mediumint(9) NOT NULL,jahiaid_jahia_fields_data mediumint(9),pageid_jahia_fields_data mediumint(9),ctnid_jahia_fields_data mediumint(9),fielddefid_jahia_fields_data mediumint(9),type_jahia_fields_data mediumint(9),connecttype_jahia_fields_data mediumint(9),value_jahia_fields_data varchar(250),rank_jahia_fields_data mediumint(9),rights_jahia_fields_data mediumint(9),version_id bigint(20) NOT NULL,workflow_state mediumint(9) NOT NULL,language_code varchar(250) NOT NULL,PRIMARY KEY (id_jahia_fields_data, version_id, workflow_state,language_code)); CREATE TABLE jahia_fields_def (id_jahia_fields_def mediumint(9),jahiaid_jahia_fields_def mediumint(9) NOT NULL,name_jahia_fields_def varchar(250) BINARY NOT NULL, PRIMARY KEY (jahiaid_jahia_fields_def, name_jahia_fields_def)); CREATE TABLE jahia_fields_def_prop (id_jahia_fields_def_prop mediumint(9) NOT NULL,flddefid_jahia_fields_def_prop mediumint(9),pdefid_jahia_fields_def_prop mediumint(9),title_jahia_fields_def_prop varchar(250),type_jahia_fields_def_prop mediumint(9),default_jahia_fields_def_prop varchar(250),PRIMARY KEY (id_jahia_fields_def_prop)); CREATE TABLE jahia_fields_def_extprop (id_jahia_fields_def mediumint(9) NOT NULL, prop_name varchar(200) BINARY NOT NULL, prop_value varchar(255) , PRIMARY KEY (id_jahia_fields_def, prop_name)); CREATE TABLE jahia_fields_prop (fieldid_jahia_fields_prop mediumint(9) NOT NULL,propertyname_jahia_fields_prop varchar(250) BINARY NOT NULL,propvalue_jahia_fields_prop varchar(50),PRIMARY KEY (fieldid_jahia_fields_prop,propertyname_jahia_fields_prop)); CREATE TABLE jahia_file_fields (id_jahia_filefield mediumint(9) NOT NULL,prop_name_filefield varchar(250) BINARY NOT NULL,prop_value_filefield varchar(250),PRIMARY KEY (id_jahia_filefield,prop_name_filefield)); CREATE TABLE jahia_filemgr (id_jahia_filemgr mediumint(9) NOT NULL,storagepath_jahia_filemgr varchar(100),ownerid_jahia_filemgr mediumint(9),PRIMARY KEY (id_jahia_filemgr)); CREATE TABLE jahia_filemgr_files (id_jahia_file mediumint(9) NOT NULL,filemgrid_jahia_file mediumint(9),fldid_jahia_file mediumint(9),uploaduser_jahia_file varchar(100),pageid_jahia_file mediumint(9),is_public_jahia_file mediumint(9),realname_jahia_file varchar(150),storagename_jahia_file varchar(150),lastmodifdate_jahia_file bigint,size_jahia_file bigint,type_jahia_file varchar(120),title_jahia_file varchar(100),descr_jahia_file varchar(250),version_jahia_file varchar(250) NOT NULL,state_jahia_file mediumint(9) NOT NULL,PRIMARY KEY (id_jahia_file,version_jahia_file)); CREATE TABLE jahia_filemgr_folders (id_jahia_fld mediumint(9) NOT NULL,filemanagerid_jahia_fld mediumint(9),parent_jahia_fld mediumint(9),fldname_jahia_fld varchar(100),PRIMARY KEY (id_jahia_fld)); CREATE TABLE jahia_grp_access (id_jahia_member varchar(50) NOT NULL,id_jahia_grps varchar(50) NOT NULL,membertype_grp_access mediumint(9) NOT NULL,PRIMARY KEY (id_jahia_member,id_jahia_grps,membertype_grp_access)); CREATE TABLE jahia_grps (id_jahia_grps mediumint(9) NOT NULL,name_jahia_grps varchar(255),key_jahia_grps varchar(50),siteid_jahia_grps mediumint(9),PRIMARY KEY (id_jahia_grps)); CREATE TABLE jahia_grp_prop (id_jahia_grp mediumint(9) NOT NULL,name_jahia_grp_prop varchar(255) BINARY NOT NULL,value_jahia_grp_prop varchar(255),provider_jahia_grp_prop varchar(50) NOT NULL, grpkey_jahia_grp_prop varchar(50) NOT NULL, PRIMARY KEY (id_jahia_grp,name_jahia_grp_prop,provider_jahia_grp_prop,grpkey_jahia_grp_prop)); CREATE TABLE jahia_pages_data (id_jahia_pages_data mediumint(9) NOT NULL,jahiaid_jahia_pages_data mediumint(9),parentid_jahia_pages_data mediumint(9),pagetype_jahia_pages_data mediumint(9),title_jahia_pages_data varchar(250),pagedefid_jahia_pages_data mediumint(9),remoteurl_jahia_pages_data varchar(250),pagelinkid_jahia_pages_data mediumint(9),creator_jahia_pages_data varchar(250),doc_jahia_pages_data varchar(250),counter_jahia_pages_data mediumint(9),rights_jahia_pages_data mediumint(9),version_id bigint(20) NOT NULL,workflow_state mediumint(9) NOT NULL, language_code varchar(250) NOT NULL,PRIMARY KEY (id_jahia_pages_data,version_id,workflow_state,language_code)); CREATE TABLE jahia_pages_def (id_jahia_pages_def mediumint(9) NOT NULL,jahiaid_jahia_pages_def mediumint(9),name_jahia_pages_def varchar(250),sourcepath_jahia_pages_def varchar(250),visible_jahia_pages_def mediumint(9),browsable_jahia_pages_def mediumint(9),warning_msg_jahia_pages_def varchar(250),img_jahia_pages_def varchar(150),PRIMARY KEY (id_jahia_pages_def)); CREATE TABLE jahia_pages_def_prop(id_jahia_pages_def_prop mediumint(9) NOT NULL,jahiaid_pages_def_prop mediumint(9) NOT NULL,name_pages_def_prop varchar(100) BINARY NOT NULL,value_pages_def_prop varchar(250),PRIMARY KEY (id_jahia_pages_def_prop,jahiaid_pages_def_prop,name_pages_def_prop)); CREATE TABLE jahia_sessions_data (sessionid_jahia_sessions_data mediumint(9),time_jahia_sessions_data float,key_jahia_sessions_data varchar(255),value_jahia_sessions_data varchar(255)); CREATE TABLE jahia_sites(id_jahia_sites mediumint(9) NOT NULL,title_jahia_sites varchar(100),servername_jahia_sites varchar(200),key_jahia_sites varchar(50),active_jahia_sites mediumint(9),defaultpageid_jahia_sites mediumint(9),defaulttemplateid_jahia_sites mediumint(9),tpl_deploymode_jahia_sites mediumint(9), webapps_deploymode_jahia_sites mediumint(9),rights_jahia_sites mediumint(9),descr_jahia_sites varchar(250),PRIMARY KEY (id_jahia_sites)); CREATE TABLE jahia_user_prop (id_jahia_users mediumint(9) NOT NULL,name_jahia_user_prop varchar(255) BINARY NOT NULL,value_jahia_user_prop varchar(255),provider_jahia_user_prop varchar(50) NOT NULL,userkey_jahia_user_prop varchar(50) NOT NULL,PRIMARY KEY (id_jahia_users,name_jahia_user_prop,provider_jahia_user_prop,userkey_jahia_user_prop)); CREATE TABLE jahia_users (id_jahia_users mediumint(9) NOT NULL,name_jahia_users varchar(255),password_jahia_users varchar(255), key_jahia_users varchar(50),siteid_jahia_users mediumint(9),PRIMARY KEY (id_jahia_users)); CREATE TABLE jahia_sites_users(username_sites_users varchar(50) NOT NULL,siteid_sites_users mediumint(9) NOT NULL,userid_sites_users varchar(50),PRIMARY KEY (username_sites_users,siteid_sites_users)); CREATE TABLE jahia_sites_grps(grpname_sites_grps varchar(50) NOT NULL,siteid_sites_grps mediumint(9) NOT NULL,grpid_sites_grps varchar(50),PRIMARY KEY (grpname_sites_grps,siteid_sites_grps)); CREATE TABLE jahia_templates_set(id_templates_set mediumint(9) NOT NULL,jahiaid_templates_set mediumint(9),name_templates_set varchar(50),srcpath_templates_set varchar(50),PRIMARY KEY (id_templates_set)); CREATE TABLE jahia_apps_share(id_aps_appid mediumint(9) NOT NULL,id_aps_siteid mediumint(9) NOT NULL,PRIMARY KEY (id_aps_appid,id_aps_siteid)); CREATE TABLE jahia_site_prop (id_jahia_site mediumint(9) NOT NULL,name_jahia_site_prop varchar(255) BINARY NOT NULL,value_jahia_site_prop varchar(255),PRIMARY KEY (id_jahia_site,name_jahia_site_prop)); CREATE TABLE jahia_homepages(id_jahia_homepages mediumint(9) NOT NULL,name_jahia_homepages varchar(150),descr_jahia_homepages varchar(255),sitekey_jahia_homepages varchar(50),type_jahia_homepages mediumint(9),rights_jahia_homepages mediumint(9),PRIMARY KEY (id_jahia_homepages)); CREATE TABLE jahia_homepages_prop(id_jahia_homepages mediumint(9) NOT NULL,sitekey_homepages_prop varchar(50) NOT NULL,name_homepages_prop varchar(50) BINARY NOT NULL,value_homepages_prop varchar(255),PRIMARY KEY (id_jahia_homepages, sitekey_homepages_prop, name_homepages_prop)); CREATE TABLE jahia_ctndef_prop (id_jahia_ctn_def mediumint(9) NOT NULL, name_jahia_ctndef_prop varchar(255) BINARY NOT NULL, value_jahia_ctndef_prop varchar(255),PRIMARY KEY (id_jahia_ctn_def, name_jahia_ctndef_prop)); CREATE TABLE jahia_ctnlists_prop (ctnlistid_ctnlists_prop mediumint(9) NOT NULL, jahiaid_ctnlists_prop mediumint(9), name_ctnlists_prop varchar(255) BINARY NOT NULL, value_ctnlists_prop varchar(255),PRIMARY KEY (ctnlistid_ctnlists_prop,name_ctnlists_prop)); CREATE TABLE jahia_ctnentries_prop (ctnid_ctnentries_prop mediumint(9) NOT NULL, jahiaid_ctnentries_prop mediumint(9), name_ctnentries_prop varchar(255) BINARY NOT NULL, value_ctnentries_prop varchar(255),PRIMARY KEY (ctnid_ctnentries_prop,name_ctnentries_prop)); CREATE TABLE jahia_site_lang_list (id mediumint(9) NOT NULL, site_id mediumint(9), code varchar(255), rank mediumint(9), activated mediumint(9), mandatory mediumint(9),PRIMARY KEY (id)); CREATE TABLE jahia_site_lang_maps (id mediumint(9) NOT NULL, site_id mediumint(9), from_lang_code varchar(255), to_lang_code varchar(255),PRIMARY KEY (id)); CREATE TABLE jahia_pages_prop (page_id mediumint(9) NOT NULL, prop_name varchar(255) BINARY NOT NULL, prop_value varchar(255), language_code varchar(100) NOT NULL,PRIMARY KEY (page_id, prop_name, language_code)); CREATE TABLE jahia_link (id mediumint(9) NOT NULL, left_oid varchar(255), right_oid varchar(255), type varchar(255), status mediumint(9), creation_date timestamp, creation_user varchar(255), lastmodif_date timestamp, lastmodif_user varchar(255), PRIMARY KEY (id)); CREATE TABLE jahia_link_metadata (link_id mediumint(9) NOT NULL, link_position varchar(20) NOT NULL, property_name varchar(255) BINARY NOT NULL, property_value varchar(255), PRIMARY KEY (link_id, link_position, property_name) ); CREATE TABLE jahia_resources (name_resource varchar(200) BINARY NOT NULL, value_resource varchar(255), languagecode_resource varchar(10) NOT NULL, PRIMARY KEY (name_resource, languagecode_resource)); CREATE TABLE jahia_markupdef (id_markup mediumint(9) NOT NULL, name_markup varchar(200) BINARY, case_sensitive mediumint(9), PRIMARY KEY (id_markup)) CREATE TABLE jahia_markupdef_prop (id_markup mediumint(9) NOT NULL, prop_name_markup varchar(200) BINARY NOT NULL, prop_value_markup varchar(200), PRIMARY KEY (id_markup, prop_name_markup)) CREATE TABLE jahia_markup_set (setting_id mediumint(9) NOT NULL, setting_type varchar(200), markupdef_id mediumint(9) NOT NULL, apply_to_all_site mediumint(9) NOT NULL, PRIMARY KEY (setting_id)) CREATE TABLE jahia_markup_set_prop (setting_id mediumint(9) NOT NULL, prop_name varchar(200) BINARY NOT NULL, prop_value varchar(255), PRIMARY KEY (setting_id, prop_name)) CREATE TABLE jahia_markup_set_site (site_id mediumint(9) NOT NULL, setting_id mediumint(9) NOT NULL , PRIMARY KEY (site_id, setting_id)) CREATE TABLE jahia_locks (name_locks VARCHAR(50) NOT NULL, targetid_locks mediumint(9) NOT NULL, action_locks VARCHAR(50) NOT NULL, owner_locks VARCHAR(50), contextid_locks VARCHAR(80), timeout_locks mediumint(9), stolen_locks VARCHAR(10), PRIMARY KEY(name_locks, targetid_locks, action_locks)) create table jahia_slide_objects(uri varchar(255) NOT NULL, classname varchar(255), slidens varchar(200) NOT NULL, constraint sys_pk_objects PRIMARY KEY(uri,slidens) ); create table jahia_slide_children(uri varchar(255), childuri varchar(255), slidens varchar(200)); create table jahia_slide_links(link varchar(255), linkto varchar(255), slidens varchar(200)); create table jahia_slide_permissions(object varchar(255), revisionnumber varchar(20), subject varchar(255), action varchar(255), inheritable int, negative int, slidens varchar(200)); create table jahia_slide_locks(id varchar(255), object varchar(255), subject varchar(255), locktype varchar(255), expirationdate varchar(25), inheritable int, xexclusive int, slidens varchar(200)); create table jahia_slide_revisions(uri varchar(255) NOT NULL, isversioned int, initialrevision varchar(10), slidens varchar(200) NOT NULL, constraint sys_pk_revisions PRIMARY KEY(uri,slidens)); create table jahia_slide_workingrev(uri varchar(255), baserevision varchar(20), xnumber varchar(20), slidens varchar(200)); create table jahia_slide_latestrev(uri varchar(255), branchname varchar(255), xnumber varchar(20), slidens varchar(200)); create table jahia_slide_branches(uri varchar(255), xnumber varchar(20), childnumber varchar(20), slidens varchar(200)); create table jahia_slide_revision(uri varchar(255), xnumber varchar(20),branchname varchar(255), slidens varchar(200)); create table jahia_slide_label(uri varchar(255), xnumber varchar(20), davlabel varchar(255), slidens varchar(200)); create table jahia_slide_property(uri varchar(255), xnumber varchar(20), propname varchar(255), value varchar(255), namespace varchar(255), proptype varchar(100), protected int, slidens varchar(200));
