ofbiz-notifications mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Deepak Dixit (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (OFBIZ-2208) ServerHit aborts transactions when trying to create entries with duplicate startTime(s).
Date Fri, 28 Jul 2017 11:19:00 GMT

    [ https://issues.apache.org/jira/browse/OFBIZ-2208?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16104807#comment-16104807
] 

Deepak Dixit commented on OFBIZ-2208:
-------------------------------------

Hi,

Here is the actual changes that was committed to fix the date time issue
do not forget to change the package as per 13.07, as in trunk we are using org.apache.ofbiz.*,
and in 13.07 we were using org.ofbiz.*


{code}
Index: framework/entityext/servicedef/services.xml
===================================================================
--- framework/entityext/servicedef/services.xml	(revision 1793299)
+++ framework/entityext/servicedef/services.xml	(revision 1793300)
@@ -461,4 +461,11 @@
         <description>Entity sync permission Checking Logic</description>
         <implements service="permissionInterface"/>
     </service>
+    <service name="generateMySqlFileWithAlterTableForTimestamps" engine="java" auth="true"
transaction-timeout="14400"
+            location="org.apache.ofbiz.entityext.data.UpgradeServices" invoke="generateMySqlFileWithAlterTableForTimestamps">
+        <description>mysql timestamp Field migration service,
+            it will generate sql file with alter query statement to update the datatype of
timestamp field to support Fractional Seconds in Time Values
+            mySql 5.6.4 added support for Fractional Seconds in Time Values. </description>
+        <attribute name="groupName" type="String" mode="IN" optional="true" default-value="org.apache.ofbiz"/>
+    </service>
 </services>
Index: framework/entity/fieldtype/fieldtypemysql.xml
===================================================================
--- framework/entity/fieldtype/fieldtypemysql.xml	(revision 1793299)
+++ framework/entity/fieldtype/fieldtypemysql.xml	(revision 1793300)
@@ -26,9 +26,9 @@
     <field-type-def type="byte-array" sql-type="LONGBLOB" java-type="byte[]"/>
     <field-type-def type="object" sql-type="LONGBLOB" java-type="Object"/>
 
-    <field-type-def type="date-time" sql-type="DATETIME" java-type="java.sql.Timestamp"/>
+    <field-type-def type="date-time" sql-type="DATETIME(3)" java-type="java.sql.Timestamp"/>
     <field-type-def type="date" sql-type="DATE" java-type="java.sql.Date"/>
-    <field-type-def type="time" sql-type="TIME" java-type="java.sql.Time"/>
+    <field-type-def type="time" sql-type="TIME(3)" java-type="java.sql.Time"/>
 
     <field-type-def type="currency-amount" sql-type="DECIMAL(18,2)" java-type="java.math.BigDecimal"/>
     <field-type-def type="currency-precise" sql-type="DECIMAL(18,3)" java-type="java.math.BigDecimal"/>
Index: framework/entityext/src/main/java/org/apache/ofbiz/entityext/data/UpgradeServices.java
===================================================================
--- framework/entityext/src/main/java/org/apache/ofbiz/entityext/data/UpgradeServices.java
(nonexistent)
+++ framework/entityext/src/main/java/org/apache/ofbiz/entityext/data/UpgradeServices.java
(revision 1793300)
@@ -0,0 +1,110 @@
+package org.apache.ofbiz.entityext.data;
+
+import java.util.List;
+import java.util.Locale;
+import java.util.Map;
+
+import org.apache.ofbiz.base.util.Debug;
+import org.apache.ofbiz.base.util.UtilMisc;
+import org.apache.ofbiz.base.util.UtilProperties;
+import org.apache.ofbiz.entity.Delegator;
+import org.apache.ofbiz.entity.GenericEntityException;
+import org.apache.ofbiz.entity.GenericValue;
+import org.apache.ofbiz.entity.model.ModelEntity;
+import org.apache.ofbiz.entity.model.ModelField;
+import org.apache.ofbiz.security.Security;
+import org.apache.ofbiz.service.DispatchContext;
+import org.apache.ofbiz.service.ServiceUtil;
+
+import java.io.BufferedWriter;
+import java.io.File;
+import java.io.FileNotFoundException;
+import java.io.FileOutputStream;
+import java.io.OutputStreamWriter;
+import java.io.PrintWriter;
+import java.io.UnsupportedEncodingException;
+
+/**
+ * Entity Data Upgrade Services
+ *
+ */
+
+public class UpgradeServices {
+    public static final String module = UpgradeServices.class.getName();
+    public static final String resource = "EntityExtUiLabels";
+
+
+    /**
+     * Generate sql file for data migration from mySql.5 and earlier version to mySql.6 to
later version
+     * mySql added support in 5.6 to support microseconds for datetime field.
+     * https://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html
+     * <ul>
+     * <li>Service will take groupName as in param,</li>
+     * <li>iterate all the entity and check for datetime and time field</li>
+     * <li>it will generate alter table sql statement to update the field data type</li>
+     * <li>datetime will be altered with DATETIME(3)</li>
+     * <li>time will be altered with TIME(3)</li>
+     * <li>sql fiel will be created at following location</li>
+     * <li>${ofbiz.home}/runtime/tempfiles/<groupName>.sql</></li>
+     * </ul>
+     * @param dctx
+     * @param context
+     * @return Map with the success result of the service,
+     */
+    public static Map<String, Object> generateMySqlFileWithAlterTableForTimestamps
(DispatchContext dctx, Map<String, Object> context) {
+        Delegator delegator = dctx.getDelegator();
+        Security security = dctx.getSecurity();
+        Locale locale = (Locale) context.get("locale");
+
+        // check permission
+        GenericValue userLogin = (GenericValue) context.get("userLogin");
+        if (!security.hasPermission("ENTITY_MAINT", userLogin)) {
+            Debug.logError(UtilProperties.getMessage(resource, "EntityExtServicePermissionNotGranted",
locale), module);
+            return ServiceUtil.returnError(UtilProperties.getMessage(resource, "EntityExtServicePermissionNotGranted",
locale));
+        }
+
+        String groupName = (String) context.get("groupName");
+
+        Map<String, ModelEntity> modelEntities;
+        PrintWriter dataWriter = null;
+        try {
+            modelEntities = delegator.getModelEntityMapByGroup(groupName);
+            dataWriter = new PrintWriter(new BufferedWriter(new OutputStreamWriter(
+                    new FileOutputStream(new File(System.getProperty("ofbiz.home") + "/runtime/tempfiles/"
+ groupName + ".sql")), "UTF-8")));
+
+            /* TODO:
+            1) fetch the meta data of the "date-time" field using the JDBC connection and
JDBC meta data;
+            2) compare it to date-time and only generate the alter statement if they differs;
+            */
+
+            dataWriter.println("SET FOREIGN_KEY_CHECKS=0;");
+            for (ModelEntity modelEntity: modelEntities.values()) {
+                List<ModelField> fields = modelEntity.getFieldsUnmodifiable();
+                for (ModelField field: fields) {
+                    if (modelEntity.getPlainTableName() != null) {
+                        if ("date-time".equals(field.getType())) {
+                            dataWriter.println("ALTER TABLE " + modelEntity.getPlainTableName()
+ " MODIFY " + field.getColName() + " DATETIME(3);");
+                        }
+                        if ("time".equals(field.getType())) {
+                            dataWriter.println("ALTER TABLE " + modelEntity.getPlainTableName()
+ " MODIFY " + field.getColName() + " TIME(3);");
+                        }
+                    }
+                }
+            }
+            dataWriter.println("SET FOREIGN_KEY_CHECKS=1;");
+            dataWriter.close();
+        } catch (GenericEntityException e) {
+            Debug.logError(e, "Error getting list of entities in group: " + e.toString(),
module);
+            return ServiceUtil.returnError(UtilProperties.getMessage(resource, "EntityExtErrorGettingListOfEntityInGroup",
UtilMisc.toMap("errorString", e.toString()), locale));
+        } catch (FileNotFoundException | UnsupportedEncodingException e) {
+            Debug.logError(e, e.getMessage(), module);
+            return ServiceUtil.returnError(e.getMessage());
+        } finally {
+            if (dataWriter != null)
+                dataWriter.close();
+        }
+
+        return ServiceUtil.returnSuccess();
+    }
+
+}

Property changes on: framework/entityext/src/main/java/org/apache/ofbiz/entityext/data/UpgradeServices.java
___________________________________________________________________
Added: svn:eol-style
## -0,0 +1 ##
+native
\ No newline at end of property
Added: svn:keywords
## -0,0 +1 ##
+Date Rev Author URL Id
\ No newline at end of property
Added: svn:mime-type
## -0,0 +1 ##
+text/plain
\ No newline at end of property

{code}



> ServerHit aborts transactions when trying to create entries with duplicate startTime(s).
> ----------------------------------------------------------------------------------------
>
>                 Key: OFBIZ-2208
>                 URL: https://issues.apache.org/jira/browse/OFBIZ-2208
>             Project: OFBiz
>          Issue Type: Bug
>          Components: framework
>    Affects Versions: Release Branch 4.0, Trunk
>         Environment: any
>            Reporter: Karim Rahimpur
>            Assignee: Jacques Le Roux
>         Attachments: ServerHitBin.java.patch, ServerHitBin.patch, ServerHit.patch, SynchronizedHitBin.patch
>
>
> org.ofbiz.webapp.stats.ServerHitBin.saveHit(...) causes transactions to be aborted due
to duplicate PKs.
> The problem is that said method will try to create two or more entries with identical
startTimes (i.e. duplicate hitStartDateTime and the other fields composing the PK: visitId,
contentId, hitStartDateTime, hitTypeId) if two or more subsequent requests are made in a sufficiently
small interval. This can and *will* be the case for example if an application rapidly issues
requests for similar data. It would usually not be observed when human users cause requests
being issued.
> The cause is that the above mentioned method uses
> serverHit.create();
> to store the entries.
> A solution to this is to _create or update_ an existing entry, thus overwriting an existing
entry in case there already was an entry, but more importantly avoiding the actual transaction
being aborted. This may not satisfy those who want to record each and every server hit even
with equal startTimes. For these cases other approaches might be considered ...
> This is an example where a transaction fails due to saveHit(...). In our case an applet
tries to pull data off the server and fails ...
> {panel}Failure in create operation for entity [ServerHit]: org.ofbiz.entity.GenericEntityException:
Error while inserting: [GenericEntity:ServerHit][contentId,gdm.getProductFeatureApplAttrs(java.lang.String)][createdStamp,2009-02-24
10:34:29.454(java.sql.Timestamp)][createdTxStamp,2009-02-24 10:34:29.454(java.sql.Timestamp)][hitStartDateTime,2009-02-24
10:34:29.443(java.sql.Timestamp)][hitTypeId,REQUEST(java.lang.String)][lastUpdatedStamp,2009-02-24
10:34:29.454(java.sql.Timestamp)][lastUpdatedTxStamp,2009-02-24 10:34:29.454(java.sql.Timestamp)][partyId,KR(java.lang.String)][referrerUrl,(java.lang.String)][requestUrl,https://127.0.0.1:8443/gdm/control/getProductFeatureApplAttrs(java.lang.String)][runningTimeMillis,8(java.lang.Long)][serverHostName,Kitty(java.lang.String)][serverIpAddress,192.168.1.70(java.lang.String)][userLoginId,karim(java.lang.String)][visitId,11900(java.lang.String)]
(SQL Exception while executing the following:INSERT INTO SERVER_HIT (VISIT_ID, CONTENT_ID,
HIT_START_DATE_TIME, HIT_TYPE_ID, NUM_OF_BYTES, RUNNING_TIME_MILLIS, USER_LOGIN_ID, STATUS_ID,
REQUEST_URL, REFERRER_URL, SERVER_IP_ADDRESS, SERVER_HOST_NAME, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP,
CREATED_STAMP, CREATED_TX_STAMP, INTERNAL_CONTENT_ID, PARTY_ID, ID_BY_IP_CONTACT_MECH_ID,
REF_BY_WEB_CONTACT_MECH_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?) (Duplicate entry '11900-gdm.getProductFeatureApplAttrs-2009-02-24 10:34:29-REQUEST'
for key 'PRIMARY')). Rolling back transaction.
> Exception: org.ofbiz.entity.GenericEntityException{panel}
> This issue seems to be related (it refers to the releae branch 4.0 and the same error
shows up):
> [https://issues.apache.org/jira/browse/OFBIZ-2041?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel]



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

Mime
View raw message