A panic occurred when querying MySQL using ORM.

[LostCat-Qian]

Describe the bug

I encountered a panic in V when querying the MySQL database through an ORM.

Reproduction Steps

databases/create_db_connection.v:

module databases

import db.mysql

pub fn create_db_connection() !mysql.DB {
    mut db := mysql.connect(
        host: '127.0.0.1',
        port: 3306,
        username: 'root'
        password: '123456'
        dbname: 'dbname'
    )!
    return db
}

main.v:

import databases

[table: 'user']
struct User {
    id int [primary]
mut:
    username string [nonull]
    password string [nonull]
    email    string
    hobby    string
}

fn main() {
    db := databases.create_db_connection()!

    user_one := User{
        username: 'ronald'
        password: '123123'
        email: 'ronald@123.com'
        hobby: 'coding'
    }
    users := sql db {
        select from User
    }!
}

Running code can get a panic.

Expected Behavior

Query success.

Current Behavior

V panic: result not set (You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM `user` LIMIT ?' at line 1 (1) (SELECT  FROM `user` LIMIT ?;); code: 1)
v hash: 3d2731f
C:/Users/Ronald/AppData/Local/Temp/v_0/Vlang.11404942233812769922.tmp.c:8039: at _v_panic: Backtrace
C:/Users/Ronald/AppData/Local/Temp/v_0/Vlang.11404942233812769922.tmp.c:8009: by panic_result_not_set
C:/Users/Ronald/AppData/Local/Temp/v_0/Vlang.11404942233812769922.tmp.c:18039: by main__main
C:/Users/Ronald/AppData/Local/Temp/v_0/Vlang.11404942233812769922.tmp.c:18215: by wmain
004a5270 : by ???
004a53d3 : by ???
7fff1d8455a0 : by ???

Possible Solution

No response

Additional Information/Context

No response

V version

V 0.4.1 3d2731f

Environment details (OS name and version, etc.)

OS: Windows11 21H2 Version: 22000.2057

[Ouri028]

Hey @LostCat-Qian ,

I tried using the example you provided, but I am not able to replicate the issue.

v -cg -cc gcc run .
[User{
    id: 0
    username: 'ronald'
    password: '123123'
    email: 'ronald@123.com'
    hobby: 'coding'
}]

V full version: V 0.4.1 815439a.12dd6e8
OS: windows, Microsoft Windows 11 Pro v22621 64-bit
Processor: 12 cpus, 64bit, little endian, 

getwd: C:\Users\sylve\OneDrive\Documents\Github\vlang\issue_19312
vexe: C:\v\v.exe
vexe mtime: 2023-09-10 22:03:05

vroot: OK, value: C:\v
VMODULES: OK, value: C:\Users\sylve\.vmodules
VTMP: OK, value: C:\Users\sylve\AppData\Local\Temp\v_0

Git version: git version 2.42.0.windows.1
Git vroot status: weekly.2023.35-82-g12dd6e8b
.git/config present: true

main.v

module main

import databases

[table: 'user']
struct User {
    id int [primary]
    mut:
    username string [nonull]
    password string [nonull]
    email    string
    hobby    string
}

fn main() {
    db := databases.create_db_connection()!
    sql db {
        create table User
    }!
    user_one := User{
        username: 'ronald'
        password: '123123'
        email: 'ronald@123.com'
        hobby: 'coding'
    }
    sql db {
        insert user_one into User
    }!

    users := sql db {
        select from User
    }!
    println(users)
}

create_db_connection.v

module databases

import db.mysql

pub fn create_db_connection() !mysql.DB {
    mut db := mysql.connect(
        host: '127.0.0.1',
        port: 8808,
        username: 'root'
        password: '123456'
        dbname: 'dbname'
    )!
    return db
}

If you're using Windows, please make sure you have the required dll's in the root directory of your project as well as the thirdparty dependencies.

From the documentation C:\v\vlib\db\mysql\README.md

For Windows, install the installer , then copy the include and lib folders to \thirdparty\mysql.

Troubleshooting If you encounter weird errors (your program just exits right away, without printing any messages, even though you have println('hi') statements in your fn main()), when trying to run a program that does import db.mysql on windows, you may need to copy the .dll file: thirdparty/mysql/lib/libmysql.dll, into the folder of the executable too (it should be right next to the .exe file).

This is a temporary workaround, until we have a more permanent solution, or at least more user friendly errors for that situation.

[LostCat-Qian]

Thank you very much for your patient response! @Ouri028 I have already attempted to place the libmysql.dll file in the root directory of the demo and used the v . command to generate an exe file in the root directory. When I attempt to run this exe file, it still gives me a panic. And I checked my MySQL installation directory and found an include folder that indeed contains comprehensive .h header files. Before, I used the v run . command to run the program. After seeing your reproduction, I tried using the v -cg -cc gcc run . command to run the program, but I still got a panic. And my other device will also experience the same panic, it is Windows10 OS. My MySQL Version: 8.1.0 MySQL Community Server - GPL

[Ouri028]

I also just tried building a .exe file and was able to run it successfully.

Please make sure the following files are included in the root directory (These files are also required to be in the same directory as the exe file when running it).

libmysql.dll libcrypto-1_1-x64.dll libssl-1_1-x64.dll

If that does not work, what I can do is upload a working example for you and you can test it locally on your side to see if you are still able to replicate the issue.

Hope this helps 😄

[LostCat-Qian]

Thank you for your solution! @Ouri028 I tried it, but I still got a same panic. I've replaced it temporarily with db.query(‘select * from user’), when I have some free time I will try to run it on Linux and see if I get the same panic. Thank you again, friend!

[Ouri028]

Hey @LostCat-Qian ,

I see, that is unfortunate that, that did not solve your problem, but please I have attached a zip file that contains a demo project that, I would like for you to run, if you don't mind.

issue_19312.zip

Please make sure you're on the latest version of V, you can run "v up" to update V.

I also included the DLLs as well as a docker-compose file that will spin up a container with MySQL on port 8088 if you want to eliminate the possibility of it being a MySQL issue.

To spin up the container, please make sure you have docker installed, once installed run the following:

docker-compose up -d

You will also find the username and password in the docker-compose.yml file.

It also includes a simple Makefile that you can use to run the program, or you can just use

v -cg run .

You will notice that there is a section of code in main.v, that you will need to uncomment to create the table as well as create the users.

Please test this and if possible with the Docker container to make sure its not a MySQL issue.

fn main() {
    // Uncomment and run only once! This will create the table and add the users.
    // create_tables()
    // add_users()
    db := databases.create_db_connection() or { panic(err) }
    get_users := sql db {
        select from User
    } or { panic(err) }
    println(get_users)
}

If you have any issues or questions, please let me know. 😄

[LostCat-Qian]

I updated V version to 0.4.2 beaa33a, this bug already fixed. Thank every one of you.