Shulou(Shulou.com)05/31 Report--

This article is to share with you what to do if the error record is too long in the database. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Phenomenon description

We may encounter an error when migrating data from Oracle or other databases to Dream Database (including using DTS), and even when we insert data at ordinary times:

The record is too long

How can we solve this kind of problem?

There are three ways.

Consider a more appropriate page size (expansion), reinitialize the instance, and re-import the data

Enable super-long column storage for the table and re-import (or insert) data

For a very long varchar type, consider rebuilding the table using CLOB before inserting it

Treatment method

Processing method 1: appropriate page size

In the underlying storage of the Dameng database, for more information about [page], please refer to the introduction of "Chapter 1.2.3" in the official DBA manual, which is the smallest IO unit of the Dameng database, similar to the block size of Oracle.

Here we emphasize two points:

1. For the page size of a database instance, it cannot be changed during the database instance lifecycle

two。 We can simply understand that for a row of records in any table (excluding large fields), their total length (byte size) cannot exceed half the page size. (that is, the so-called inline data, for LOB fields, most of them are off-line data.)

This calculation is so strict that even if it is more than a little bit, it will report an error, for example:

Update set number_col=23.8 where number_col=23

This sql statement may encounter the error that the record is too long.

3. Our optional page sizes are: 4KB, 8KB, 16KB, or 32KB (default is 8KB), which is specified once when the instance is initialized and cannot be changed during the lifecycle of the database instance. -- correspondingly, the total length of a row of records (excluding large fields) is limited to 2, 4, 8 and 16 KB.

How to view the page size of the current database instance:

`select page`

In other words, if our page size is 8KB and the insert Datagram error "record is too long", then if we are an instance of 16KB, then we do not necessarily report an error. And for instances migrated from Oracle, we recommend that the page size be 16KB.

Method 2: enable ultra-long columns

The function of extra-long columns is implemented to solve this problem. Here is how to enable super-long columns:

1. Graphical interface (manager client)

Under the mode, find the corresponding table, right-click, open the menu bar, and enable the super-long recording setting through the storage option à in the menu bar.

two。 Enable the extra-long columns of a table through the SQL statement

3. When creating a table, set the properties that enable ultra-long columns

Method 3: use the large field CLOB to rebuild the table

This is to say, originally we have a watch test1 (v1 int, … , vx int,vy varchar (8000), insert data Times is wrong, this is us:

Alter table test1 rename to test1_modify

Create table test1 (v1 int, … , vx int,vy clob)

Insert into test1 select * from test1_modify;commit

Then, in the relevant operations, we will not encounter the problem of long records.

Description:

1. This approach may involve code modifications, such as the handling of the corresponding fields on the application side, and the difference between the varchar type and the CLOB type.

2. The CLOB field can also be directly like, but it has something to do with the parameter CLOB_LIKE_MAX_LEN. Please learn the dream configuration file (dm.ini refer to DBA manual 2.1.1):

The default value of CLOB_LIKE_MAX_LEN is 31 static parameters

The maximum length of the CLOB type in the LIKE statement, in KB, valid value range (8 / 102400)

Note:

-pay attention to the difference between string truncation and string truncation, string truncation, but pure precision is not enough, such as varchar (400), can put 400byte string, we let it put 401, will report error string truncation. String truncation and data overflow is a type of error, data overflow is a non-string type, lack of precision.

Thank you for reading! This is the end of the article on "what to do if the error record is too long in the database". I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.