influxdata / influxdata/influxdb
InfluxQL query to restore data to another database should preserve all data types
- 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
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