11 Replies Latest reply on Feb 5, 2017 4:46 PM by user19752

    ESS problem

    rcschroeder

      I am using the actual odbc driver to connect from filemaker (ESS) to a mysql database.

      It works fine for some of the tables, but for one table I get the error: [Actual][MySQL] Got a packet bigger than ‘max_allowed_packet’ bytes

      I get this in every field no matter what size but only on this one particular table.

       

      Can  anyone help me fix this?

       

      Thanks,

       

      Richard

        • 1. Re: ESS problem
          beverly

          can  you do a SQL dump of that MySQL table (no data, just the schema) and post the result?

          beverly

          1 of 1 people found this helpful
          • 2. Re: ESS problem
            rcschroeder

            /*

            Date: 02/04/2017 11:02:22 AM

            */

             

             

            SET NAMES utf8;

            SET FOREIGN_KEY_CHECKS = 0;

             

             

            -- ----------------------------

            --  Table structure for `tblorder`

            -- ----------------------------

            DROP TABLE IF EXISTS `tblorder`;

            CREATE TABLE `tblorder` (

              `OrderID` int(10) NOT NULL AUTO_INCREMENT,

              `StoreCode` varchar(3) NOT NULL DEFAULT '',

              `OrderNumber` varchar(17) NOT NULL DEFAULT '',

              `OrderDate` date NOT NULL DEFAULT '0000-00-00',

              `TrayNo` varchar(20) NOT NULL DEFAULT '',

              `PatientFirstName` varchar(30) NOT NULL DEFAULT '',

              `PatientLastName` varchar(30) NOT NULL DEFAULT '',

              `EdgedUncut` varchar(20) NOT NULL DEFAULT '',

              `ODSphere` varchar(10) NOT NULL DEFAULT '',

              `OSSphere` varchar(10) NOT NULL DEFAULT '',

              `ODCyl` varchar(10) NOT NULL DEFAULT '',

              `OSCyl` varchar(10) NOT NULL DEFAULT '',

              `ODAxis` varchar(10) NOT NULL DEFAULT '',

              `OSAxis` varchar(10) NOT NULL DEFAULT '',

              `ODAdd` varchar(10) NOT NULL DEFAULT '',

              `OSAdd` varchar(10) NOT NULL DEFAULT '',

              `ODAdd2` varchar(10) NOT NULL DEFAULT '',

              `OSAdd2` varchar(10) NOT NULL DEFAULT '',

              `ODSegHt` varchar(25) NOT NULL DEFAULT '',

              `OSSegHt` varchar(25) NOT NULL DEFAULT '',

              `ODVPrism` varchar(10) NOT NULL DEFAULT '',

              `OSVPrism` varchar(10) NOT NULL DEFAULT '',

              `ODHPrism` varchar(10) NOT NULL DEFAULT '',

              `OSHPrism` varchar(10) NOT NULL DEFAULT '',

              `ODVDirection` varchar(10) NOT NULL DEFAULT '',

              `OSVDirection` varchar(10) NOT NULL DEFAULT '',

              `ODHDirection` varchar(10) NOT NULL DEFAULT '',

              `OSHDirection` varchar(10) NOT NULL DEFAULT '',

              `ODFarPD` varchar(10) NOT NULL DEFAULT '',

              `OSFarPD` varchar(10) NOT NULL DEFAULT '',

              `ODNearPD` varchar(10) NOT NULL DEFAULT '',

              `OSNearPD` varchar(10) NOT NULL DEFAULT '',

              `ODOCHt` varchar(10) NOT NULL DEFAULT '',

              `OSOCHt` varchar(10) NOT NULL DEFAULT '',

              `LensType` varchar(20) NOT NULL DEFAULT '',

              `LensStyle` varchar(50) NOT NULL DEFAULT '',

              `LensColor` varchar(30) NOT NULL DEFAULT '',

              `LensMaterial` varchar(50) NOT NULL DEFAULT '',

              `YourCost` double(15,2) NOT NULL DEFAULT '0.00',

              `TintType` varchar(20) NOT NULL DEFAULT '',

              `TintPercent` varchar(3) NOT NULL DEFAULT '',

              `TintColor` varchar(100) NOT NULL DEFAULT '',

              `FrameName` varchar(250) NOT NULL DEFAULT '',

              `FrameEdgeType` varchar(100) NOT NULL DEFAULT '',

              `FrameMaterial` varchar(250) NOT NULL DEFAULT '',

              `FrameMfr` varchar(250) NOT NULL DEFAULT '',

              `FrameColor` varchar(100) NOT NULL DEFAULT '',

              `FrameEnclosed` varchar(1) NOT NULL DEFAULT '',

              `FrameEyeSize` varchar(10) NOT NULL DEFAULT '',

              `FrameBridge` varchar(10) NOT NULL DEFAULT '',

              `FrameTemple` varchar(10) NOT NULL DEFAULT '',

              `FrameA` varchar(10) NOT NULL DEFAULT '',

              `FrameB` varchar(10) NOT NULL DEFAULT '',

              `FrameED` varchar(10) NOT NULL DEFAULT '',

              `FrameDBL` varchar(10) NOT NULL DEFAULT '',

              `FrameCirc` varchar(10) NOT NULL DEFAULT '',

              `Dispenser` varchar(100) NOT NULL DEFAULT '',

              `OrderStatus` varchar(20) NOT NULL DEFAULT '',

              `RejectedReason` varchar(250) NOT NULL DEFAULT '',

              `StatusDate` date NOT NULL DEFAULT '0000-00-00',

              `StatusTime` varchar(10) NOT NULL DEFAULT '',

              `Status` varchar(1) NOT NULL DEFAULT '',

              `TerminalNumber` varchar(3) NOT NULL DEFAULT '',

              `ResentOrder` int(1) NOT NULL DEFAULT '0',

              `TraceData` longtext,

              `flgShipping` varchar(7) NOT NULL DEFAULT 'True',

              `SelectJob` varchar(7) NOT NULL DEFAULT 'False',

              `TrackingNumber` varchar(100) NOT NULL DEFAULT '',

              `Coating` varchar(30) NOT NULL DEFAULT '',

              `SpecialInstructions` longtext NOT NULL,

              `Retransmitted` char(1) NOT NULL DEFAULT '0',

              `TerminalIndex` char(3) NOT NULL DEFAULT '',

              `DateShipped` date NOT NULL DEFAULT '0000-00-00',

              `TracedAtLab` int(1) NOT NULL DEFAULT '0',

              `InvoiceNumber` varchar(10) NOT NULL DEFAULT '',

              `InvoiceDate` date NOT NULL DEFAULT '0000-00-00',

              `GrossPrice` double(15,2) NOT NULL DEFAULT '0.00',

              `Discount` double(15,2) NOT NULL DEFAULT '0.00',

              `LensCost` double(15,2) NOT NULL DEFAULT '0.00',

              `ShippingCharge` double(15,2) NOT NULL DEFAULT '0.00',

              `TaxCharge` double(15,2) NOT NULL DEFAULT '0.00',

              `InvoiceTotal` double(15,2) NOT NULL DEFAULT '0.00',

              `HoyaLensMaterial` varchar(30) NOT NULL DEFAULT '',

              `HoyaLensStyle` varchar(30) NOT NULL DEFAULT '',

              `HoyaLensColor` varchar(30) NOT NULL DEFAULT '',

              `HoyaTintColor` varchar(30) NOT NULL DEFAULT '',

              `HoyaCoating` varchar(30) NOT NULL DEFAULT '',

              `LensWholesale` double(15,2) NOT NULL DEFAULT '0.00',

              `CoatingWholesale` double(15,2) NOT NULL DEFAULT '0.00',

              `TintWholesale` double(15,2) NOT NULL DEFAULT '0.00',

              `TotalWholesale` double(15,2) NOT NULL DEFAULT '0.00',

              `BillingFileName` varchar(100) NOT NULL DEFAULT '',

              `HoyaOrderStatus` varchar(100) NOT NULL DEFAULT '',

              `ARExport` int(1) NOT NULL DEFAULT '0',

              `APExport` int(1) NOT NULL DEFAULT '0',

              `ARInvoiceFileName` varchar(50) NOT NULL DEFAULT '',

              `APInvoiceFileName` varchar(50) NOT NULL DEFAULT '',

              `Shipper` varchar(50) NOT NULL DEFAULT '',

              `ReDo` int(1) NOT NULL DEFAULT '0',

              `ReDoReason` varchar(100) NOT NULL DEFAULT '',

              `ReplacedJobNumber` varchar(20) NOT NULL DEFAULT '',

              `ReplacingJobNumber` varchar(20) NOT NULL DEFAULT '',

              `PaymentMode` varchar(50) NOT NULL DEFAULT '',

              `RefNumber` varchar(50) NOT NULL DEFAULT '',

              `UserID` varchar(100) NOT NULL DEFAULT '',

              `BuyingGroupName` varchar(50) NOT NULL DEFAULT '',

              `LabNote` varchar(250) NOT NULL DEFAULT '',

              `flgFrameEnclosed` int(1) NOT NULL DEFAULT '0',

              `CancelledReason` longtext,

              `RedoReferenceNo` varchar(20) NOT NULL DEFAULT '',

              `FlgFinalRedo` int(1) NOT NULL DEFAULT '0',

              `CopyFrom` varchar(20) NOT NULL DEFAULT '',

              `CopiedTrace` char(1) NOT NULL DEFAULT '0',

              `flgTransmitted` char(1) NOT NULL DEFAULT '0',

              `HoyaStatusDate` date NOT NULL DEFAULT '0000-00-00',

              `HoyaStatusTime` varchar(10) NOT NULL DEFAULT '',

              `DateSentToLab` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

              `flgRejected` char(1) NOT NULL DEFAULT '0',

              PRIMARY KEY (`OrderID`,`OrderNumber`)

            ) ENGINE=InnoDB AUTO_INCREMENT=2937 DEFAULT CHARSET=latin1;

             

             

            -

            SET FOREIGN_KEY_CHECKS = 1;

            • 3. Re: ESS problem
              beverly

              You longtext, may or may not be a problem. Do you have these in any of your other tables that do work?

               

              PS you may wish to MOVE this topic to Discussion space as Community Feedback has to do with the forum software and user problems. You could get more people seeing your question.

               

              Sent from miPhone

              • 4. Re: ESS problem
                rcschroeder

                I would like to move it, per your suggestion, if only I could see how to do that

                • 5. Re: ESS problem
                  beverly

                  let's see if TSGal or TSPigeon can help you move the thread. On Monday, of course!

                  beverly

                  • 6. Re: ESS problem
                    beverly

                    or see this thread I just posted:

                    How to move from Community Feedback to Discussions

                    beverly

                    1 of 1 people found this helpful
                    • 7. Re: ESS problem
                      rcschroeder

                      Thanks Beverly, I moved it.

                       

                      Richard

                      • 8. Re: ESS problem
                        greatgrey

                        Don't know if this applies but as beverly hinted, there are some data formats that limit the number of characters in a field i.e. 256/512 per record and 128 per field.

                        1 of 1 people found this helpful
                        • 9. Re: ESS problem
                          nicolai

                          +1 greatgrey

                           

                          The largest possible packet that can be transmitted to or from a MySQL 5.7 server or client is 1GB.

                           

                          But by default it would be usually set to a few mb. The longtext column probably indicates a large amount of data, plus every single column is set to be non-empty.

                           

                          I think ini file lists this entry and this is where you can adjust it.

                          1 of 1 people found this helpful
                          • 10. Re: ESS problem
                            greatgrey

                            I have also seen cases where a hidden character has fouled up things that otherwise would work.

                            • 11. Re: ESS problem
                              user19752

                              The server's default max_allowed_packet value is 4MB (1MB before MySQL 5.6.6).

                              1 of 1 people found this helpful