TiDB v6.1.0 1105, 'invalid data type: Failed to decode row v2 data as u64

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: tidb v6.1.0 1105, 'invalid data type: Failed to decode row v2 data as u64

| username: 张小凉1

tidb v6.1.0 suddenly reported an error: “1105, ‘invalid data type: Failed to decode row v2 data as u64’”
Data in multiple databases is partially corrupted and cannot be queried.
The status of the tidb cluster has always been normal.

2
| username: 张小凉1 | Original post link

Has anyone encountered the same problem?

3
| username: WalterWj | Original post link

Has anyone used the Lightning local mode to import data?

4
| username: 张小凉1 | Original post link

I didn’t use Lightning. I once used mysqldump to export data from MySQL, and then directly executed the .sql file to import it into TiDB v6.1.

5
| username: WalterWj | Original post link

:thinking:

  1. Admin, check this table. If there are issues with the index, delete and rebuild it.
  2. If there are no issues in step 1, try using Dumpling to back up this table and see if it can be backed up. I have a feeling there might be invalid data in this table.
6
| username: 张小凉1 | Original post link

Exporting data with mysqldump and then importing it into a new TiDB (v6.5) also has the same issue.

7
| username: Min_Chen | Original post link

Hello,
This kind of error is usually caused by corrupted data. The best approach is to identify the corrupted data and then overwrite it with the correct data.

8
| username: ljluestc | Original post link

Check TiDB server logs for error messages.
Verify TiDB server configuration.
Check network connectivity between TiDB server and other components of the TiDB cluster.
Update statistics for the affected table.
Use the CAST function to convert the affected column to the correct data type.

#!/bin/bash

# Set the IP address and port number of the TiDB server
TIDB_IP="10.88.76.146"
TIDB_PORT="4000"
TABLE_NAME="my_table"
COLUMN_NAME="my_column"

# Check for error messages in the TiDB server logs
echo "Checking TiDB server logs..."
if grep -q "ERROR" /path/to/tidb-server.log; then
  echo "Error: TiDB server log contains error messages. Please check log file for more details."
  exit 1
fi

# Verify TiDB server configuration
echo "Verifying TiDB server configuration..."
if [ "$(hostname -i)" != "$TIDB_IP" ] || [ "$(curl -IsS http://$TIDB_IP:$TIDB_PORT/status | head -1 | awk '{print $2}')" != "200" ]; then
  echo "Error: TiDB server configuration is incorrect. Please check the configuration file for the correct IP address and port number."
  exit 1
fi

# Check network connectivity between TiDB server and other components
echo "Checking network connectivity..."
if ! ping -c 1 $TIDB_IP > /dev/null; then
  echo "Error: TiDB server is not reachable on the network. Please check the network connection."
  exit 1
fi

# Update statistics for the affected table
echo "Updating statistics for table $TABLE_NAME..."
mysql -h $TIDB_IP -P $TIDB_PORT -u root -p -e "ANALYZE TABLE $TABLE_NAME;"

# Use the CAST function to convert the affected column to the correct data type
echo "Using CAST function to convert column $COLUMN_NAME to the correct data type..."
mysql -h $TIDB_IP -P $TIDB_PORT -u root -p -e "SELECT CAST($COLUMN_NAME AS UNSIGNED) FROM $TABLE_NAME;"

echo "TiDB server check completed successfully."
exit 0