0

New to MySql.

I have an export file from server version 5.6.34. I am importing this on a server with the same version. I get an error like:

ERROR 1064 (42000) at line 4343: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*/' at line 1
Operation failed with exitcode 1

This is the line (4343)

/*!50003 CREATE*/ /*!50017 DEFINER=`roomroot`@`%`*/ /*!50003 TRIGGER invoicelines_AFTER_UPDATE AFTER UPDATE ON invoicelines

Full code:

DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`roomroot`@`%`*/ /*!50003 TRIGGER invoicelines_AFTER_UPDATE AFTER UPDATE ON invoicelines
FOR EACH ROW
updateTrigger: BEGIN
  IF (@MIGRATION_SKIP_TRIGGERS = TRUE)
  THEN
    LEAVE updateTrigger;
  END IF;

  IF (new.Invoicenumber < 1000000000) THEN /* No history for Proforma invoices */
    IF not (old.AutoGen <=> new.AutoGen) or not (old.Reservation <=> new.Reservation) or not (old.RoomReservation <=> new.RoomReservation) or not (old.SplitNumber <=> new.SplitNumber) 
      or not (old.ItemNumber <=> new.ItemNumber) or not (old.PurchaseDate <=> new.PurchaseDate) or not (old.invoiceNumber <=> new.InvoiceNumber)
      or not (old.ItemID <=> new.ItemID) or not (old.Number <=> new.Number) or not (old.Description <=> new.Description) or not (old.Price <=> new.Price)
      or not (old.VATType <=> new.VATType) or not (old.ilVATPercentage <=> new.ilVATPercentage) or not (old.Total <=> new.Total) or not (old.TotalWOVat <=> new.TotalWOVat) or not (old.Vat <=> new.Vat)
      or not (old.AutoGenerated <=> new.AutoGenerated) or not (old.CurrencyRate <=> new.CurrencyRate) or not (old.Currency <=> new.Currency) or not (old.ReportDate <=> new.ReportDate)
      or not (old.ReportTime <=> new.ReportTime) or not (old.Persons <=> new.Persons) or not (old.Nights <=> new.Nights) or not (old.BreakfastPrice <=> new.BreakfastPrice)
      or not (old.Ayear <=> new.Ayear) or not (old.Amon <=> new.Amon) or not (old.Aday <=> new.Aday) or not (old.ilTmp <=> new.ilTmp)
      or not (old.ilAccountKey <=> new.ilAccountKey) or not (old.ItemCurrency <=> new.ItemCurrency) or not (old.ItemCurrencyRate <=> new.ItemCurrencyRate) or not (old.Discount <=> new.Discount)
      or not (old.Discount_isprecent <=> new.Discount_isprecent) or not (old.ImportRefrence <=> new.ImportRefrence) or not (old.ImportSource <=> new.ImportSource) or not (old.isPackage <=> new.isPackage)
      or not (old.confirmDate <=> new.confirmDate) or not (old.confirmAmount <=> new.confirmAmount) or not (old.RoomReservationAlias <=> new.RoomReservationAlias) or not (old.ItemSource <=> new.ItemSource)
      or not (old.InvoiceIndex <=> new.InvoiceIndex) or not (old.staffCreated <=> new.staffCreated) or not (old.staffLastEdit <=> new.staffLastEdit) or not (old.itemAdded <=> new.itemAdded)
      or not (old.VisibleOnInvoice <=> new.VisibleOnInvoice) or not (old.Revenue <=> new.Revenue) 
      or not (old.parentlineGUID <=> new.parentlineGUID) 
      or not (old.PURCHASE_CURRENCY <=> new.PURCHASE_CURRENCY) or not (old.PURCHASE_UNIT_PRICE <=> new.PURCHASE_UNIT_PRICE) or not (old.PURCHASE_TOTAL_PRICE_WO_TAX <=> new.PURCHASE_TOTAL_PRICE_WO_TAX) 
      or not (old.PURCHASE_TOTAL_PRICE <=> new.PURCHASE_TOTAL_PRICE) or not (old.PURCHASE_TO_NATIVE_CURR_CONV <=> new.PURCHASE_TO_NATIVE_CURR_CONV) 
      or not (old.NATIVE_UNIT_PRICE <=> new.NATIVE_UNIT_PRICE) or not (old.NATIVE_TOTAL_PRICE_WO_TAX <=> new.NATIVE_TOTAL_PRICE_WO_TAX) or not (old.NATIVE_TOTAL_PRICE <=> new.NATIVE_TOTAL_PRICE) 
      or not (old.NATIVE_TO_CHARGE_CURR_CONV <=> new.NATIVE_TO_CHARGE_CURR_CONV)  or not (old.CHARGE_CURRENCY <=> new.CHARGE_CURRENCY) or not (old.CHARGE_UNIT_PRICE <=> new.CHARGE_UNIT_PRICE)
      or not (old.CHARGE_TOTAL_PRICE_WO_TAX <=> new.CHARGE_TOTAL_PRICE_WO_TAX) or not (old.CHARGE_TOTAL_PRICE <=> new.CHARGE_TOTAL_PRICE)
      then
      INSERT INTO invoicelines_history
      (AutoGen, Reservation, RoomReservation, SplitNumber, ItemNumber, PurchaseDate, InvoiceNumber, ItemID, Number, Description, Price, VATType, ilVATPercentage, Total, TotalWOVat, Vat, AutoGenerated,
      CurrencyRate, Currency, ReportDate, ReportTime, Persons, Nights, BreakfastPrice, Ayear, Amon, Aday, ilTmp, ilAccountKey, ItemCurrency, ItemCurrencyRate, Discount, Discount_isprecent,
      ImportRefrence, ImportSource, isPackage, confirmDate, confirmAmount, RoomReservationAlias, ItemSource, InvoiceIndex, staffCreated, staffLastEdit, itemAdded, VisibleOnInvoice, Revenue,
      PURCHASE_CURRENCY, PURCHASE_UNIT_PRICE, PURCHASE_TOTAL_PRICE_WO_TAX, PURCHASE_TOTAL_PRICE, PURCHASE_TO_NATIVE_CURR_CONV, 
      NATIVE_UNIT_PRICE, NATIVE_TOTAL_PRICE_WO_TAX, NATIVE_TOTAL_PRICE, NATIVE_TO_CHARGE_CURR_CONV,
      CHARGE_CURRENCY, CHARGE_UNIT_PRICE, CHARGE_TOTAL_PRICE_WO_TAX, CHARGE_TOTAL_PRICE,
      action, lineGUID, lineId, parentlineGUID)
        SELECT
          AutoGen, Reservation, RoomReservation, SplitNumber, ItemNumber, PurchaseDate, InvoiceNumber, ItemID, Number, Description, Price, VATType, ilVATPercentage, Total, TotalWOVat, Vat, AutoGenerated,
          CurrencyRate, Currency, ReportDate, ReportTime, Persons, Nights, BreakfastPrice, Ayear, Amon, Aday, ilTmp, ilAccountKey, ItemCurrency, ItemCurrencyRate, Discount, Discount_isprecent,
          ImportRefrence, ImportSource, isPackage, confirmDate, confirmAmount, RoomReservationAlias, ItemSource, InvoiceIndex, staffCreated, staffLastEdit, itemAdded, VisibleOnInvoice, Revenue,
          PURCHASE_CURRENCY, PURCHASE_UNIT_PRICE, PURCHASE_TOTAL_PRICE_WO_TAX, PURCHASE_TOTAL_PRICE, PURCHASE_TO_NATIVE_CURR_CONV,
          NATIVE_UNIT_PRICE, NATIVE_TOTAL_PRICE_WO_TAX, NATIVE_TOTAL_PRICE, NATIVE_TO_CHARGE_CURR_CONV,
          CHARGE_CURRENCY, CHARGE_UNIT_PRICE, CHARGE_TOTAL_PRICE_WO_TAX, CHARGE_TOTAL_PRICE,
          'UPDATE_RECORD', lineGUID, NEW.ID, parentlineGUID
        FROM invoicelines WHERE ID = NEW.ID;
    END IF;
  END IF;
END; */;;

A same version of this line is present before this error line and that don't give errors. Working line not giving error

    /*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`roomroot`@`%`*/ /*!50003 TRIGGER invoicelines_BEFORE_UPDATE BEFORE UPDATE ON invoicelines 
 FOR EACH ROW
 BEGIN
  DECLARE rate DOUBLE DEFAULT 1.0;
     
  IF IFNULL((SELECT value FROM pms_settings WHERE keyGroup='INVOICE_HANDLING_FUNCTIONS' AND `key`='SHOW_INCLUDED_BREAKFAST_ON_INVOICE'), 'FALSE') != 'TRUE' THEN
    SET NEW.revenueCorrection=0.0;
    SET NEW.revenueCorrectionVAT=0.0;
  END IF;
      
  if (NEW.VATType is not null) then
    set NEW.ilVATPercentage = (select max(v.VATPercentage) from vatcodes v where v.VATCode=NEW.VATType);
  end if;
    
    /* Fix for externally added invoicelines which dont set revenue field by themselves */
  if NEW.itemsource = 'EXTERNAL' and NEW.revenue = 0 then
      set NEW.revenue = NEW.total;
  end if;
    
  SET rate = IF(IFNULL(NEW.currencyrate, 0) != 0, NEW.currencyrate, 1.0);

  /* Sync new fields when old fields have changed */
  IF (NEW.revenue <> OLD.revenue) 
      OR (NEW.currency <> OLD.currency) 
      OR (NEW.currencyRate <> OLD.currencyRate) 
      THEN
    SET NEW.NATIVE_TOTAL_PRICE = NEW.revenue;
    SET NEW.NATIVE_UNIT_PRICE = NEW.revenue / NEW.number;
    SET NEW.NATIVE_TOTAL_PRICE_WO_TAX = NEW.totalWOVat;
    SET NEW.PURCHASE_TOTAL_PRICE = NEW.revenue / rate;
    SET NEW.PURCHASE_UNIT_PRICE = NEW.revenue / NEW.number / rate;
    SET NEW.PURCHASE_TOTAL_PRICE_WO_TAX = NEW.totalWOVat / rate;
    SET NEW.PURCHASE_CURRENCY = NEW.Currency;
    SET NEW.PURCHASE_TO_NATIVE_CURR_CONV = NEW.currencyRate;
    SET NEW.CHARGE_CURRENCY = NEW.currency;
  END IF;

    /* Fill PURCHASE field on open invoices */
  IF (NEW.PURCHASE_TOTAL_PRICE IS NULL) THEN
      SET NEW.PURCHASE_TOTAL_PRICE = NEW.revenue / rate;
      SET NEW.PURCHASE_UNIT_PRICE = NEW.revenue / NEW.number  / rate;
      SET NEW.PURCHASE_TOTAL_PRICE_WO_TAX = NEW.totalWOVat / rate;
      SET NEW.PURCHASE_CURRENCY = NEW.Currency;
      SET NEW.PURCHASE_TO_NATIVE_CURR_CONV = rate;
      SET NEW.CHARGE_CURRENCY = NEW.currency;
  END IF;

  IF (IFNULL(NEW.invoicenumber, -1) > 0 AND NEW.CHARGE_TOTAL_PRICE IS NULL) THEN
      SET NEW.CHARGE_TOTAL_PRICE = NEW.revenue / rate;
      SET NEW.CHARGE_UNIT_PRICE = NEW.revenue / NEW.number / rate;
      SET NEW.CHARGE_TOTAL_PRICE_WO_TAX = NEW.totalWOVat / rate;
      SET NEW.CHARGE_CURRENCY = NEW.currency;
      SET NEW.NATIVE_TO_CHARGE_CURR_CONV = 1 / rate;
  END IF;
    
END */;; 

Whay could cause the error? I am on the same server version. And a same type of line is executed correctly

  • Show full triggers creation codes, not only 1st line of them (whole trigger creation is ONE statement). And 3-4 lines before. – Akina Aug 12 at 10:22
  • Added full trigger code – Danny Aug 12 at 10:29
  • Should a restore to a same version server not work out of the box? – Danny Aug 12 at 10:37
  • Syntax highlighting shows the problem - nested multi-line comments (outer comment is used for version-specific wrapping, inner comment is one in trigger creation code) which makes the code broken. – Akina Aug 12 at 10:48
  • Which line? I see errors in highlighting in both triggers. I see nested comment in working version also – Danny Aug 12 at 10:51
0

The last line for the trigger was causing my problem. It should work. See comment by Akina with example

END; */;;

Should be

END */;;
| improve this answer | |
  • ; after BEGIN-END block is ignored (more precisely is treated as empty line). Trigger with both END and END; is created successfully. Example - both triggers are created and executed successfully. – Akina Aug 12 at 13:05
  • When I remove the ; after the END in my case I have no more errors during import. That is also the only difference with other triggers. What could be happening here? Could this be a strange behavior of the import function? – Danny Aug 12 at 13:48
  • In your example the /*!50003 CREATE*/ /*!50017 DEFINER= synthax is also not used – Danny Aug 12 at 13:52
  • Maybe this is an issue of the tool which you use for importing, or this is (I doubt) version-dependent? In your exmaple the /*!50003 CREATE*/ /*!50017 DEFINER= synthax is also not used This is version-dependent code - you may read about it in the User manual. – Akina Aug 12 at 13:52
  • I use the workbench tool. V8. MySql version 5.6.34 – Danny Aug 12 at 13:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.