Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Descending Index segments AuroraDB

  1. #1
    Join Date
    Feb 2009
    Location
    Adelaide, South Australia
    Posts
    2,863

    Default Descending Index segments AuroraDB

    Hi

    There are still issues with the ODBC Driver for Aurora DB.

    To reproduce, please add to the table OrderHeader an index 5 with 2 segments: Order_date DESC, Order_Number ASC
    Then convert to Aurora DB

    The table is created as Standard table:
    Code:
    CREATE TABLE `OrderHeader` (
        `Order_Number` INT(11) NOT NULL DEFAULT '0',
        `Customer_Number` INT(11) NOT NULL DEFAULT '0',
        `Order_Date` DATE NOT NULL DEFAULT '0001-01-01',
        `Terms` VARCHAR(12) NOT NULL DEFAULT '' COLLATE 'latin1_bin',
        `Ship_Via` VARCHAR(12) NOT NULL DEFAULT '' COLLATE 'latin1_bin',
        `Ordered_By` VARCHAR(12) NOT NULL DEFAULT '' COLLATE 'latin1_bin',
        `SalesPerson_ID` VARCHAR(4) NOT NULL DEFAULT '' COLLATE 'latin1_bin',
        `Order_Total` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
        `Last_Detail_Num` SMALLINT(6) NOT NULL DEFAULT '0',
        PRIMARY KEY (`Order_Number`) USING BTREE,
        UNIQUE INDEX `OrderHeader002` (`Customer_Number`, `Order_Number`) USING BTREE,
        UNIQUE INDEX `OrderHeader003` (`Order_Date`, `Order_Number`) USING BTREE,
        UNIQUE INDEX `OrderHeader004` (`SalesPerson_ID`, `Order_Number`) USING BTREE,
        UNIQUE INDEX `OrderHeader005` (`Order_Date`, `Order_Number`) USING BTREE
    )
    COLLATE='latin1_bin'
    ENGINE=InnoDB
    ;
    or Recnum table
    Code:
    CREATE TABLE `OrderHeader` (
        `RECNUM` INT(11) NOT NULL,
        `Order_Number` INT(11) NOT NULL DEFAULT '0',
        `Customer_Number` INT(11) NOT NULL DEFAULT '0',
        `Order_Date` DATE NOT NULL DEFAULT '0001-01-01',
        `Terms` VARCHAR(12) NOT NULL DEFAULT '' COLLATE 'latin1_bin',
        `Ship_Via` VARCHAR(12) NOT NULL DEFAULT '' COLLATE 'latin1_bin',
        `Ordered_By` VARCHAR(12) NOT NULL DEFAULT '' COLLATE 'latin1_bin',
        `SalesPerson_ID` VARCHAR(4) NOT NULL DEFAULT '' COLLATE 'latin1_bin',
        `Order_Total` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
        `Last_Detail_Num` SMALLINT(6) NOT NULL DEFAULT '0',
        PRIMARY KEY (`Order_Number`) USING BTREE,
        UNIQUE INDEX `OrderHeader000` (`RECNUM`) USING BTREE,
        UNIQUE INDEX `OrderHeader002` (`Customer_Number`, `Order_Number`) USING BTREE,
        UNIQUE INDEX `OrderHeader003` (`Order_Date`, `Order_Number`) USING BTREE,
        UNIQUE INDEX `OrderHeader004` (`SalesPerson_ID`, `Order_Number`) USING BTREE,
        UNIQUE INDEX `OrderHeader005` (`Order_Date`, `Order_Number`) USING BTREE
    )
    COLLATE='latin1_bin'
    ENGINE=InnoDB
    ;
    Incident 1: Auto_increment is not defined.
    Incident 2: Index OrderHeader005 does not show DESC, in fact it is duplicate with OrderHeader003.

    This is not good and failing the promise of the connectivity kits.

    The INT is resp

    Code:
    ...
    INDEX_NUMBER 1
    INDEX_NAME PRIMARY
    
    
    INDEX_NUMBER 2
    INDEX_NAME OrderHeader002
    
    
    INDEX_NUMBER 3
    INDEX_NAME OrderHeader003
    
    
    INDEX_NUMBER 4
    INDEX_NAME OrderHeader004
    
    
    INDEX_NUMBER 5
    INDEX_NAME OrderHeader005
    and

    Code:
    ...
    INDEX_NUMBER 0
    INDEX_NAME OrderHeader000
    
    
    INDEX_NUMBER 1
    INDEX_NAME PRIMARY
    
    
    INDEX_NUMBER 2
    INDEX_NAME OrderHeader002
    
    
    INDEX_NUMBER 3
    INDEX_NAME OrderHeader003
    
    
    INDEX_NUMBER 4
    INDEX_NAME OrderHeader004
    
    
    INDEX_NUMBER 5
    INDEX_NAME OrderHeader005
    Now need to manually do:
    Code:
    ALTER TABLE `OrderHeader`
        CHANGE COLUMN `RECNUM` `RECNUM` INT(11) NOT NULL AUTO_INCREMENT FIRST;
    and the INT file to

    Code:
    INDEX_NUMBER 5
    INDEX_NAME OrderHeader005
    INDEX_NUMBER_SEGMENTS 2
    INDEX_SEGMENT_FIELD 3
    INDEX_SEGMENT_DIRECTION DESCENDING
    INDEX_SEGMENT_FIELD 1
    I think the driver should be updated to detect that the DECENDING did not 'stick' and process the fallback to setting in the INT.
    Please note that the descending index segment is only supported since MySql 8, so its a common issue for a lot of developers.

    Kind regards
    Marco
    Marco Kuipers
    DataFlex Consultant
    28 IT Pty Ltd - DataFlex Specialist Consultancy
    DataFlex Channel Partner for Australia and Pacific region
    Adelaide, South Australia
    www.28it.com.au

  2. #2
    Join Date
    Feb 2009
    Posts
    1,175

    Default Re: Descending Index segments AuroraDB

    What AuroraDB version and what MySQL ODBC driver version are you using?

    As you say descending index segments are only supported with MySQL 8 and later. Earlier versions just ignore any DESC setting in the index definition.
    AFAIK Aurora is based on earlier MySQL version than 8 and does not support descending index segments.
    Best regards,
    Data Access Worldwide

    Martin Moleman

  3. #3
    Join Date
    Feb 2009
    Location
    Adelaide, South Australia
    Posts
    2,863

    Default Re: Descending Index segments AuroraDB

    Hi Martin

    That is correct and exactly the reason why the conversion should make the setting in the INT file so it does not break the application after conversion.
    Marco Kuipers
    DataFlex Consultant
    28 IT Pty Ltd - DataFlex Specialist Consultancy
    DataFlex Channel Partner for Australia and Pacific region
    Adelaide, South Australia
    www.28it.com.au

  4. #4
    Join Date
    Feb 2009
    Location
    Florida, USA
    Posts
    695

    Default Re: Descending Index segments AuroraDB

    Hi Marco,

    I converted Order Entry to Aurora (5.7.12) using DataFlex 20.0.3.83 (ODBC CK 20.0.7.55) and MySQL Connector ODBC v.8.00.19.00. I converted Order Header as RECNUM and the autoincrement was set as expected (see below).

    What did you use in your conversion?

    Code:
    CREATE TABLE `OrderHeader` (  
      `RECNUM` int(11) NOT NULL AUTO_INCREMENT,
      `Order_Number` int(11) NOT NULL DEFAULT '0',
      `Customer_Number` int(11) NOT NULL DEFAULT '0',
      `Order_Date` date NOT NULL DEFAULT '0001-01-01',
      `Terms` varchar(12) NOT NULL DEFAULT '',
      `Ship_Via` varchar(12) NOT NULL DEFAULT '',
      `Ordered_By` varchar(12) NOT NULL DEFAULT '',
      `SalesPerson_ID` varchar(4) NOT NULL DEFAULT '',
      `Order_Total` decimal(10,2) NOT NULL DEFAULT '0.00',
      `Last_Detail_Num` smallint(6) NOT NULL DEFAULT '0',
      PRIMARY KEY (`Order_Number`),
      UNIQUE KEY `OrderHeader000` (`RECNUM`),
      UNIQUE KEY `OrderHeader002` (`Customer_Number`,`Order_Number`),
      UNIQUE KEY `OrderHeader003` (`Order_Date`,`Order_Number`),
      UNIQUE KEY `OrderHeader004` (`SalesPerson_ID`,`Order_Number`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2130 DEFAULT CHARSET=utf8mb4
    Regards,

    Marcia Booth (DAW)

  5. #5
    Join Date
    Feb 2009
    Location
    Adelaide, South Australia
    Posts
    2,863

    Default Re: Descending Index segments AuroraDB

    Aurora in Mariadb mode with Mariadb connector.
    Am ooo so can’t check the specifics at the moment.
    As for the DF, 20 beta 1.
    Marco Kuipers
    DataFlex Consultant
    28 IT Pty Ltd - DataFlex Specialist Consultancy
    DataFlex Channel Partner for Australia and Pacific region
    Adelaide, South Australia
    www.28it.com.au

  6. #6
    Join Date
    Feb 2009
    Location
    Florida, USA
    Posts
    695

    Default Re: Descending Index segments AuroraDB

    Thanks, Marco. I logged your suggestion in our systems.

    For the autoincrement, using MySQL Connector ODBC with Aurora presented no problems as you can see from my earlier post. When you have a chance, can you confirm that with your table/application?
    Regards,

    Marcia Booth (DAW)

  7. #7
    Join Date
    Feb 2009
    Location
    Adelaide, South Australia
    Posts
    2,863

    Default Re: Descending Index segments AuroraDB

    Some of the variables as reported by HeidiDB
    aurora_version 2.09.0
    default_storage_engine InnoDB
    innodb_file_format Barracuda
    innodb_version 5.7.12
    version 5.7.12
    version_comment MySQL Community Server (GPL)

    The choice of MariaDB ODBC over the MySQL one is based on https://stackoverflow.com/questions/...ess-aws-aurora

    Kind regards
    Marco
    Marco Kuipers
    DataFlex Consultant
    28 IT Pty Ltd - DataFlex Specialist Consultancy
    DataFlex Channel Partner for Australia and Pacific region
    Adelaide, South Australia
    www.28it.com.au

  8. #8
    Join Date
    Feb 2009
    Location
    The Netherlands
    Posts
    4,674

    Default Re: Descending Index segments AuroraDB

    Using MariaDB ODBC on Aurora is not something we have tested and thus can't support. I'd recommend to use the MySQL ODBC client.

  9. #9
    Join Date
    Feb 2009
    Location
    Florida, USA
    Posts
    695

    Default Re: Descending Index segments AuroraDB

    Hi Marco,

    Harm is correct. Just wanted to add something that I'm not sure if you noticed but the articles you referred to (and MariaDB website) talk about JDBC, not ODBC.
    Regards,

    Marcia Booth (DAW)

  10. #10
    Join Date
    Feb 2009
    Location
    Adelaide, South Australia
    Posts
    2,863

    Default Re: Descending Index segments AuroraDB

    Thanks Harm and Marcia
    I’m in the process of moving the workspaces to the MySQL driver following this advice.
    Will keep you informed.
    Marco Kuipers
    DataFlex Consultant
    28 IT Pty Ltd - DataFlex Specialist Consultancy
    DataFlex Channel Partner for Australia and Pacific region
    Adelaide, South Australia
    www.28it.com.au

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •