[MariaDB] Error 1366: Incorrect string value #1567
Comments
|
This filename may contain special characters |
|
I'll check it. so there are incompatibilities between a standard FS (zfs in this case) and JuiceFS ? Is this documented ? |
|
@zhijian-pro filename is |
solracsf
changed the title
|
We think this is a compatibility issue with mariadb, the same logic can run normally on mysql. |
|
I can confirm, but server is set to that collation. Global configurationBut this can be normally be override at table creation, or after, like here below: |
|
We did something similar after creating the table, you can see the corresponding logic here. When I tested mariadb, the encoding was |
|
Yeah but even after manually changing the collation, the same error is produced, and column
@markus456 can we get your input here why MariaDB (10.4) is not behaving correctly here? |
solracsf
changed the title
|
We think this is a compatibility issue with mariadb, you can insert unicode characters into the Below is the test using This is the test program package main
import (
"database/sql"
"fmt"
"math/rand"
"time"
_ "github.com/go-sql-driver/mysql"
)
func main() {
mariadb := "user:password@(ip:port)/database"
mysql := "user:password@(ip:port)/database"
_, err := testInsertUnicodeChar(mysql)
if err != nil {
fmt.Println("mysql test insert unicode char error:", err)
} else {
fmt.Println("mysql test insert unicode char success")
}
_, err = testInsertUnicodeChar(mariadb)
if err != nil {
fmt.Println("mariadb test insert unicode char error:", err)
} else {
fmt.Println("mariadb test insert unicode char success")
}
}
func testInsertUnicodeChar(jdbcStr string) (int64, error) {
db, err := sql.Open("mysql", jdbcStr)
if err != nil {
return 0, err
}
// unicode char
s := "\xE9sa"
// random seed
rand.Seed(time.Now().UnixNano())
res, err := db.Exec("INSERT INTO jfs_edge VALUES (?,?,?,?)", rand.Intn(1000), s, 1, 1)
if err != nil {
return 0, err
}
id, err := res.LastInsertId()
if err != nil {
return 0, err
}
return id, nil
} |
@solracsf I've got no idea. Wrong person by any chance? |
|
Ok I found the root cause. MariaDB sets
Once set to:
After that, executing your program, you get But i don't know how to solve this JuiceFS side... |
|
Test also passes by replacing, in your program, line by |
|
Also, take this simple test: with the default but this works as expected: |
|
I found this bit of the code that should alter it. If you create the table with: It works also. Note character set is the range of what it can store. Collation is its sort ordering. Focus on character set. Per @zhijian-pro 's second comment, what is your Do filesystems actually enforce a correct encoding of utf8 filenames? If not maybe |
|
@solracsf It can indeed be solved by changing the sql_mode. |
|
@grooverdan the |
|
Keep in mind the |
Here it is: |
Removing STRICT_TRANS_TABLES (https://mariadb.com/kb/en/sql-mode/#strict_trans_tables) simply turns off strict mode. It is not the root cause. MySQL only sets it as default starting with 5.7. It would be interesting to run the test against MySQL 5.6 and MySQL 5.7. MariaDB sets it as default already on 10.2 and possibly earlier. |
|
I second comments by @grooverdan to be mindful of the length of the field chosen and possible implications (off-by-one, buffer overflows, variable char lengths, special char handling, translations of file names between different file systems, etc.). When it comes to filenames, this is an area test engineers (and likely hackers, regrettably) love for a reason. You will want to test this well. |
|
Closed by #1762 |
|
@solracsf @grooverdan @mariadb-RoelVandePaar We have changed it to use varbinary(255), thanks for the feedback! For existing JuiceFS volumes, please use the latest JuiceFS from main branch and change the column manually. |
What happened:
While rsync from local disk to juicefs mount, it suddenly (after several hours) stopped with
Failed to sync with 11 errors: last error was: open /mnt/juicefs/folder/file.xls: input/output errorOn the jfs log, i can find these:
Nothing was logged at MariaDB side.
Environment:
The text was updated successfully, but these errors were encountered: