Argthtjtr

I consistently get this error (Error Code: 1034. Incorrect key file for table '/rdsdbdata/tmp/#sql_1709_0'; try to repair it) in Amazon Web Services (AWS) Aurora MySQL 5.7 when I run the following INSERT statement (or run the SELECT clause in the INSERT statement as a separate SELECT statement).

What do I need to change about the INSERT statement (or the AWS Aurora MySQL 5.7 instance or other AWS ecosystem components) so that I don't get this error? (I'm not primarily interested in how to repair the temp table that is referenced in the error message - I want to avoid getting the error in the first place, since this INSERT statement will run as part of a larger script).

The INSERT statement is as follows:

INSERT INTO GEO_AREA ( Geo_Area_Nm,     Geo_Area_Abrv,      Geo_Area_Full_Qualfd_Nm,    Geo_Area_Full_Qualfd_Abrv,      

Geo_Area_Typ_CK,    Geo_Area_Prim_ID,   Last_Modfd_By_ID,   Crtd_By_ID  

)

  SELECT 

    Concat("Precinct ", S.PrecinctNumber) AS Geo_Area_Nm,  -- The plain Precinct identifier, preceded by the word "Precinct",

        -- without any county or state information added.

    S.PrecinctNumber AS Geo_Area_Abrv,  -- The plain Precinct identifier without any county or state information added.

    Concat(S.Jurisname, ", ", STATE.Geo_Area_Nm," - Precinct: ", 

        S.PrecinctNumber) AS Geo_Area_Full_Qualfd_Nm,  -- [Elector Jurisdiction name, State Name:] Precinct: [Precinct ID]      

    Concat(S.Jurisname, ", ", STATE.Geo_Area_Abrv," - Prcnct: ", 

        S.PrecinctNumber) AS Geo_Area_Full_Qualfd_Nm,  -- [Elector Jurisdiction name, State Name:] Precinct: [Precinct ID]

    @Prcnct_CK AS Geo_Area_Typ_CK, -- => Precinct

    Concat("US-", S.State, "-",S.Juriscode, "-Prcnct:", S.PrecinctNumber) AS Geo_Area_Prim_ID,

      -- Example: US-WY-5602100000-Prcnct:3 08

    Max(Concat("DTVF-", S.SourceID)) As Last_Modfd_By_ID,

    Max(Concat("DTVF-", S.SourceID)) As Crtd_By_ID

  FROM VW_STG_DATA_TRUST_VOTR_INSERT S 

    LEFT JOIN GEO_AREA STATE -- STATE is the source of the State name and abbreviation that was loaded in Initial LOAD

      ON Concat('US-',S.State) = STATE.Geo_Area_Prim_ID 

    LEFT JOIN GEO_AREA EXIST_GA  -- EXIST_GA is the set of records in GEO_AREA before this insert

      ON Concat("US-", S.State, "-",S.Juriscode, "-Prcnct:", S.PrecinctNumber) = EXIST_GA.Geo_Area_Prim_ID

  WHERE STATE.Geo_Area_Typ_CK = @State_CK -- => Connection for US State level info

    -- AND Concat(S.JurisName, ",  ", STATE.Geo_Area_Nm) = 'Abbotsford City,  Wisconsin'

    AND EXIST_GA.Geo_Area_Prim_ID IS NULL

  GROUP BY 1,2,3,4,5,6

;

I have taken the following steps to try to correct the problem, none of which have worked:

⦁ Dropping and recreating the target table (GEO_AREA) in the same AWS Aurora MySQL database instance.

⦁ Stopping and restarting the RDS instance.

⦁ Creating a new AWS Aurora MySQL 5.7 database instance (wpai-archimedes-dev-dbi) and creating and loading within it a fairly small set of tables and views (including those that are needed to support this INSERT statement). Therefore, I don't think that it is a disk space problem.

Background:

⦁ This INSERT statement (and the SELECT statement within it) draw data from a large staging table/view that contains one row per person/voter and attempts to load one row per [Electoral] Precinct into the Geographic Area (GEO_AREA) table. There can be many rows in the staging table/view that contain voters in a single Electoral Precinct.

⦁ I have two similarly structured INSERT statements that load one row per County and and Electoral Jurisdiction into the GEO_AREA table. These two insert statements work properly (without errors). An example of the insert statement that correctly loads counties is at the bottom of this case description.

⦁ After receiving this error, I can still run other SQL queries against the GEO_AREA table without getting an error, e.g.,

SELECT * FROM GEO_AREA WHERE Geo_Area_Typ_CK in (252, 255) order by 6 desc, 4;

⦁ Rows for Country and State/Province level geographic areas are loaded into GEO_AREA from a .csv file on my laptop with a LOAD DATA LOCAL INFILE command.

⦁ I've tried this INSERT statement in two separate Aurora MySQL database instances. The instance type in both cases is db.r4.xlarge. The first DB instance (the instance referenced in this tech support case) was loaded from a 1.002 GB .csv file (a 1M record random sample of voters from all 50 states in the US), which currently uses 1.334GB of storage space in all tables, and shows (in the results from the snapshot taken this morning) that the instance uses 1 GB of storage space. The second instance was loaded from a 4.623GB csv file (4M records describing all voters in the state of Tennessee), which currently uses 3.727 GB of storage space in all tables, and shows (again, in the results from the snapshot taken this morning) that the instance uses 7 GB of storage space.

I found the problem: I had used the same alias (Geo_Area_Full_Qualfd_Nm) for two separate columns in the SELECT clause. I don’t know why it generated the error message (code 1034) which referred to fixing a temp table. It seems like I remember doing the same thing before in another context a few weeks ago and getting an error message that much more clearly identified the problem, and that led me to solve the issue much more quickly.