blob: 39d02f8261a1dae65579813eb569c38659e8f0ac (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
|
# Database: `ProbeSetSE` Schema Bug
## Tags
* type: bug
* priority: critical
* status: open
* keywords: database, mariadb, schema
* assigned:
## Description
The schemas are defined as follows:
```
MariaDB [db_webqtl]> DESC Strain;
+-----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------------------+------+-----+---------+----------------+
| Id | int(20) | NO | PRI | NULL | auto_increment |
| Name | varchar(100) | YES | MUL | NULL | |
| Name2 | varchar(100) | YES | | NULL | |
| SpeciesId | smallint(5) unsigned | NO | | 0 | |
| Symbol | varchar(20) | YES | MUL | NULL | |
| Alias | varchar(255) | YES | | NULL | |
+-----------+----------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
MariaDB [db_webqtl]> DESC ProbeSetData;
+----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| Id | int(10) unsigned | NO | PRI | 0 | |
| StrainId | int(20) | NO | PRI | NULL | |
| value | float | NO | | NULL | |
+----------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
MariaDB [db_webqtl]> DESC ProbeSetSE;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| DataId | int(10) unsigned | NO | PRI | 0 | |
| StrainId | smallint(5) unsigned | NO | PRI | 0 | |
| error | float | YES | | NULL | |
+----------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
```
From this, you can see that "ProbeSetSE" has the column "StrainId" but with a different size integer (smaller) than that in table "Strain". This could at best, cause errors when inserting data, if the strain exceeds the value 65535, or at worst, do a silent conversion of inserted "StrainId" values into a value in the range [0, 65535] leading to data corruption.
The schema needs to be updated accordingly.
## Solution
Run this query against the database:
```
ALTER TABLE ProbeSetSE MODIFY StrainId INT(20) NOT NULL;
```
|
