SOLVED: SQL Error insert into <table> Duplicate entry for key PRIMARY

[frankt]

Hi,

We ran in to problems where no resources (templates/graphs/etc) could be added, resulting in error messages about duplicate keys. Thanks to this post, I've written the script below which repaired our database.


For tables with a PRIMARY key, zabbix records what the next id must be for that key in the table IDS. For what ever reason, this table got out of sync with reality (eg lower than the highest key for the listed table/field), causing the next key value to be already in use.

When run with "--check", the script below will only show if the next id record is ok or not. When run with "--repair", it will take the highest key number in use, add 1 and writes that as nextid to the IDS table.

Regards,
/Frank

#!/bin/bash
PATH=/bin:/usr/bin:/usr/local/bin

# --- config ---
DATABASE="zabbix"
MYSQL_CMD="mysql -N ${DATABASE}"
# --- end config ---


# Check or repair
case "$1" in
  "-c"|"--check")
    DO="check"
    ;;
  "-r"|"--repair")
    DO="repair"
    ;;
  *)
    echo "Usage: $(basename "$0") <-c|-r>"
    echo "  -c: check for inconsistancies"
    echo "  -r: repair inconsistancies"
    exit 1
    ;;
esac

# Print output headers
printf "%25s %25s %10s %10s %16s\n" "table" "primary field" "nextid" "maxid" "status"

# Loop all tables in IDS and check nextid against the highest (max) id in the referenced primary field
TABLE_NAMES="$(echo 'select table_name from ids;' | ${MYSQL_CMD})"

for table in ${TABLE_NAMES}; do
  field_name="$(echo "select field_name from ids where table_name = '${table}';" | ${MYSQL_CMD})"
  nextid="$(echo "select nextid from ids where table_name = '${table}';" | ${MYSQL_CMD})"
  maxid="$(echo "select max(${field_name}) from ${table};" | ${MYSQL_CMD})"

  printf "%25s %25s %10s %10s %8s" "${table}" "${field_name}" "${nextid}" "${maxid}"

  # maxid is allowed to be NULL
  if [ "${maxid}" == "NULL" ]
  then
    printf "%8s\n" "OK"
  else
    # maxid should be equal or less then nextid
    if [ "${maxid}" -le "${nextid}" ]
    then
      printf "%8s\n" "OK"
    else
      if [ "${DO}" == "check" ]
      then
        printf "%8s\n" "FAILED"
      elif [ "${DO}" == "repair" ]
      then
        # If nextid is lower then maxid, set nextid to maxid+1
        printf "UPDATE ids SET nextid = %d WHERE table_name = '%s' AND field_name = '%s'\n" "$((maxid+1))" "${table}" "${field_name}" | ${MYSQL_CMD}
        [[ $? -eq 0 ]] && printf "%8s\n" "REPAIRED"
      else
        echo ""
        echo "Script error: function '${DO}' is unknown"
        echo ""
        exit 1
      fi
    fi
  fi
done

[markfree]

Nice script.

I've changed brackets in line 46 from "[ ... ]" to "[[ ... ]]".
This avoids errors like "dup_db_check.sh: line 46: [: : integer expression expected"