influxdata / influxdata/influxdb

InfluxQL query to restore data to another database should preserve all data types

Open
#23,503 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
Rust
Stars
31.7k
Forks
3.7k
Avg merge
13h 37m
Merged PRs (30d)
8

Description

I was following the procedure to [restore data to an existing database](https://docs.influxdata.com/influxdb/v1.8/administration/backup_and_restore/#restore-data-to-an-existing-database) more specifically the InfluxQL query:

```
SELECT *
INTO "example-db".autogen.:MEASUREMENT
FROM "example-tmp-db".autogen./.*/ GROUP BY *
```

and found that this query is not preserving all data types in the destination database.

I'm reporting it here after discussing it with @Anaisdg on this [community post](https://community.influxdata.com/t/help-with-err-partial-write-field-type-conflict-error/25531).

__Steps to reproduce:__

I've tried to come up with a minimal example to reproduce this bug:

```
> CREATE DATABASE mydb
```

Load these two measurements into mydb:

```
measurement1 field1=1i
measurement1 field2=1i
measurement2 field1=1.0
```

```
> show field keys on mydb
name: measurement1
fieldKey fieldType
-------- ---------
field1 integer
field2 integer

name: measurement2
fieldKey fieldType
-------- ---------
field1 float
```

Copy data to another database
```
> CREATE DATABASE "mydb-copy"
> SELECT * INTO "mydb-copy".autogen.:MEASUREMENT FROM "mydb".autogen./.*/ GROUP BY *
name: result
time written
---- -------
0 2
```

__Expected behavior:__

I would expect data types to be preserved in`mydb-copy`.

__Actual behavior:__

We can see that `field1` in `measurement1` was created with the wrong data type.

```
> show field keys on "mydb-copy"
name: measurement1
fieldKey fieldType
-------- ---------
field1 float
field2 integer

name: measurement2
fieldKey fieldType
-------- ---------
field1 float
```

which probably happens because `field1` also exists in `measurement2` with a different data type.

This bug leads to the `ERR: partial write: field type conflict` error if we write data back to the original database:

```
> SELECT * INTO "mydb".autogen.:MEASUREMENT FROM "mydb-copy".autogen./.*/ GROUP BY *
ERR: partial write: field type conflict: input field "field1" on measurement "measurement1" is type float, already exists as type integer dropped=1
```

__Environment info:__

* System info:
```
# uname -r
3.10.0-1160.62.1.el7.x86_64
```
* InfluxDB version:
```
# influxd version
InfluxDB v1.8.10 (git: 1.8 688e697c51fd)
```

Contributor guide

Open the contributing guide

Research direction

Start by reproducing the reported CREATE DATABASE and SELECT * INTO sequence with measurement1 and measurement2 using different field types. Trace the InfluxQL SELECT INTO execution; done means the destination preserves each measurement's field types and copying the data back does not produce a field type conflict.

Written by the indexing model from the issue text.

Assessment

Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.