Comments

• Jim_Knicely - Select Field - Administrator

  June 2019 edited June 2019

  Does this info help?

  https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/ErrorCodes/SQLStates.htm

  0
• robns ✭

  June 2019

  Hi Jim
  Unfortunately not as the errors I see are somewhat different. e.g.

  dbadmin=> select distinct error_level, error_code, message from v_monitor.error_messages;
  error_level | error_code | message

  -------------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------

  ERROR | 16801924 | Syntax error at or near "."
  ERROR | 16801924 | Syntax error at or near "ORDER"
  ERROR | 16801924 | Syntax error at or near "epool_name"
  ERROR | 16801924 | Syntax error at or near "like"
  ERROR | 16801924 | Syntax error at or near "q"
  ERROR | 16932996 | Relation "resorce_acquisitions" does not exist
  ERROR | 16932996 | Relation "resource_pool_ststus" does not exist
  ERROR | 50360452 | Column "45035996444232585" does not exist
  ERROR | 50360452 | Column "GENERAL_MEMORY_BORROWED_KB" does not exist
  ERROR | 50360452 | Column "NOTICE" does not exist
  ERROR | 50360452 | Column "timestamp" does not exist
  ERROR | 50856066 | Timestamp units "iso_week" not recognized
  ERROR | 52461700 | Function catalog.timestampadd(unknown, numeric, timestamp) does not exist, or permission is denied for catalog.timestampadd(unknown, numeric, timest
  ERROR | 52461700 | Operator does not exist: int >= timestamptz
  ERROR | 52461700 | Operator does not exist: int ~~ unknown

  0
• Bryan_H Vertica Employee Administrator

  June 2019

  Hi, could you be more specific what you need? The "error_code" is the internal Vertica code number. The message is what is returned to the client. Do you need:
  The SQLSTATE value, which is SQLSTATE 22023 with Description: ERRCODE_INVALID_PARAMETER_VALUE;
  Or the Vertica error code shown in the docs, which is
  ERROR 2624: Column "string" does not exist

  0
• Jim_Knicely - Select Field - Administrator

  June 2019 edited June 2019

  Also, note errors listed in the docs are specific to each Vertica release! Don't compare the errors listed for 9.2 to an 8.1 DB

  0
• Bryan_H Vertica Employee Administrator

  June 2019

  From development: that error_code is the SQL state, encoded with 6 bits for each of the 5 alphanumeric characters in the code. So:
  16801924 = 000100, 000010, 000110, 000000, 000001 = SQLState 42601 That's the SQL state for Syntax Error. If you take that and go to the back of manual, you'll see the Vertica-specific errors that go with this SQLState. The vertica-specific code tells which one.

  0
• Bryan_H Vertica Employee Administrator

  June 2019

  You can convert the error_code to SQLSTATE using the following:

  SELECT 
  case 
  when error_code % 64 < 10 then (error_code % 64)::varchar
  else CHR((error_code % 64) + 48)
  end 
  ||
  case 
  when floor(error_code / 64) % 64 < 10 then (floor(error_code / 64) % 64)::varchar
  else CHR((floor(error_code / 64)::integer % 64) + 48)
  end
  ||
  case 
  when floor(error_code / 4096) % 64 < 10 then (floor(error_code / 4096) % 64)::varchar
  else CHR((floor(error_code / 4096)::integer % 64) + 48)
  end
  ||
  case 
  when floor(error_code / 262144) % 64 < 10 then (floor(error_code / 262144) % 64)::varchar
  else CHR((floor(error_code / 262144)::integer % 64) + 48)
  end
  ||
  case 
  when floor(error_code / 16777216) % 64 < 10 then (floor(error_code / 16777216) % 64)::varchar
  else CHR((floor(error_code / 16777216)::integer % 64) + 48)
  end
  AS SQLSTATE
  ,*
  from v_monitor.error_messages limit 10;
  

  0
• robns ✭

  June 2019

  Great, thank you Bryan & Jim for your help for clarifying.

  0