SOCI with MySQL on Linux (Ubuntu) - impossible to link static

[baziorek]

I'm trying to use soci with MySQL static (according to documentation it is possible).

I'll describe full process, because IMO there is too much "tricks", so probably I'm doing something wrong.

---

Preparation:

Running mysql:

To make sure connection is working I need to run MySQL, I'm using docker with command:

docker run --network=host --name mysql-stackoverflow -e MYSQL_ROOT_PASSWORD=mysecretpassword -e MYSQL_USER=someuser -e MYSQL_PASSWORD=somepass -e MYSQL_DATABASE=stack -d mysql

Then to make this is easy to reproduce I'm using docker to make all steps:

docker run -it --rm --network=host --name=ubuntusoci -v $(pwd):/home/ --workdir /home ubuntu:22.04

Rest of steps are inside docker:

Building soci:

Installing dependencies:

    apt-get update && apt-get upgrade -y
    apt install build-essential libmysqlclient-dev git cmake libfmt-dev -y

Building SOCI:

git clone --depth=1 -b v4.0.3 https://github.com/SOCI/soci.git

export installPath="$(pwd)/libs"
mkdir soci/building
mkdir "$installPath"
cd soci/building
cmake -DCMAKE_INSTALL_PREFIX="$installPath" -DSOCI_SHARED=OFF -DSOCI_TESTS=OFF ..
make install -j12
cd - # going back to previous directory

Compilation using SOCI

I have sample code: test.txt (the extension is changed), but when I try to compile I see compilation problem:

# g++ -isystem"${installPath}/include" -isystem/usr/include/mysql/ --std=c++23 test.cpp -L"${installPath}/lib" -lsoci_core -lsoci_mysql
In file included from /home/libs/include/soci/soci.h:12,
                 from test.cpp:4:
/home/libs/include/soci/soci-platform.h:172:10: error: #error "SOCI must be configured with C++11 support when using C++11"
  172 |         #error "SOCI must be configured with C++11 support when using C++11"
      |          ^~~~~

1. This is first problem to report You. Because as You see I'm using C++23, not C++11.

Dirty fix of the problem

I tried to rerun CMake with enabling C++11 support:

cd - # going back to building directory 
cmake -DCMAKE_INSTALL_PREFIX="$installPath" -DSOCI_SHARED=OFF -DSOCI_TESTS=OFF -DSOCI_CXX11=ON ..
make install -j12
cd -# going back to home directory (where is test.cpp file)

Now I'm able to compile:

g++ -isystem"${installPath}/include" -isystem/usr/include/mysql/ --std=c++23 test.cpp -L"${installPath}/lib" -lsoci_core -lsoci_mysql

But when I' trying to run I see problem with dynamic libraries:

./a.out 
Error: Failed to find shared library for backend mysql

Which are not visible with ldd:

ldd a.out 
        linux-vdso.so.1 (0x00007ffd7f1ea000)
        libstdc++.so.6 => /lib/x86_64-linux-gnu/libstdc++.so.6 (0x000073f07880a000)
        libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x000073f0787ea000)
        libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x000073f0785c1000)
        libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x000073f0784da000)
        /lib64/ld-linux-x86-64.so.2 (0x000073f078aaa000)

So I tried an answer for similar question and I've added register_factory_mysql(); in the beginning of main function.

But this time it does not link:

g++ -isystem"${installPath}/include" -isystem/usr/include/mysql/ --std=c++23 test.cpp -L"${installPath}/lib" -lsoci_core -lsoci_mysql -ldl -lmysqlclient
/usr/bin/ld: /home/libs/lib/libsoci_mysql.a(vector-into-type.cpp.o): in function `soci::mysql_vector_into_type_backend::post_fetch(bool, soci::indicator*)':
vector-into-type.cpp:(.text+0x4e8): undefined reference to `soci::details::parse_std_tm(char const*, tm&)'
/usr/bin/ld: /home/libs/lib/libsoci_mysql.a(standard-into-type.cpp.o): in function `soci::mysql_standard_into_type_backend::post_fetch(bool, bool, soci::indicator*)':
standard-into-type.cpp:(.text+0x3a1): undefined reference to `soci::details::parse_std_tm(char const*, tm&)'
collect2: error: ld returned 1 exit status

Am I doing something wrong for now?

---

Building with shared libraries:

So I was unable to fix compilation, so I've tried to build with shared libraries.

cd - # going back to installation directory
cmake -DCMAKE_INSTALL_PREFIX="$installPath" -DSOCI_TESTS=OFF -DSOCI_CXX11=ON -DSOCI_SHARED=ON .. -LA 
make install -j12
cd - # going back to home directory:

Building program with RPATH:

Now it works:

g++ -Wl,--disable-new-dtags,-rpath,"${installPath}/lib"  -isystem"${installPath}/include" -isystem/usr/include/mysql/ --std=c++23 test.cpp -L"${installPath}/lib" -lsoci_core -lsoci_mysql && ./a.out
community_id = 1
community_name_pl = Mamre
community_original_name = Error: Null value not allowed for this type

But I prefer static linking

[baziorek]

After night of sleeping I've tried something else.

---

There is information in official MySQL documentation how to have static linking:

Unfortunately doing this steps with SOCI didn't work (still problem with dynamic linking):

# first:
cmake -DCMAKE_INSTALL_PREFIX="$installPath" -DSOCI_SHARED=OFF -DSOCI_TESTS=OFF -DSOCI_CXX11=ON ..
make install -j12
cd -

#then:
g++ -isystem"${installPath}/include" -isystem/usr/include/mysql/ --std=c++23 test.cpp -c && g++ test.o `mysql_config --variable=pkglibdir`/libmysqlclient.a `pkg-config --static --libs mysqlclient` -L"${installPath}/lib"  -lsoci_core -lsoci_mysql  && ./a.out
# ... a lot of linkage errors...

But I tried to use just MySQL code (C++ code without SOCI) and I succedded:

g++ -isystem"${installPath}/include" -isystem/usr/include/mysql/ --std=c++23 test2.cpp -c && g++ test2.o `mysql_config --variable=pkglibdir`/libmysqlclient.a `pkg-config --static --libs mysqlclient` && ./a.out 

1, Mamre, %                                                                                                                                                                                                                 

# then ldd command:
ldd a.out 
linux-vdso.so.1 (0x00007ffd839a3000)
libzstd.so.1 => /lib/x86_64-linux-gnu/libzstd.so.1 (0x00007f9e476db000)
libssl.so.3 => /lib/x86_64-linux-gnu/libssl.so.3 (0x00007f9e46f5c000)
libcrypto.so.3 => /lib/x86_64-linux-gnu/libcrypto.so.3 (0x00007f9e46a00000)
libstdc++.so.6 => /lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007f9e46600000)
libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007f9e476bb000)
libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f9e46200000)
/lib64/ld-linux-x86-64.so.2 (0x00007f9e477d1000)
libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f9e46e75000)

The code is:

#include <iostream>
#include <mysql.h>
using namespace std;

constexpr char DATABASE_NAME[] = "stack";
constexpr char USERNAME[] = "someuser";
constexpr char PASSWORD[] = "somepass";

int main()
{
    MYSQL *conn = mysql_init(nullptr);

    // Connect to MySQL database
    if (!mysql_real_connect(conn, "127.0.0.1", USERNAME, PASSWORD, DATABASE_NAME, 0, nullptr, 0)) {
        std::cerr << "Error: " << mysql_error(conn) << std::endl;
        exit(1);
    }

    // Execute SQL query
    if (mysql_query(conn, "SELECT * FROM communities")) {
        std::cerr << "Error: " << mysql_error(conn) << std::endl;
        exit(1);
    }

    // Get result set
    MYSQL_RES *result = mysql_store_result(conn);

    // Print data from table
    MYSQL_ROW row;
    while ((row = mysql_fetch_row(result))) {
        for (unsigned int i = 0; i < mysql_num_fields(result); ++i) {
            if (i > 0) std::cout << ", ";
            std::cout << row[i];
        }
        std::cout << std::endl;
    }

    // Free result set
    mysql_free_result(result);

    // Close connection
    mysql_close(conn);
}

So there is some kind of bug in SOCI (or I'm doing something wrong?)

[zann1x]

It seems like you're linking the wrong targets. In order to use SOCI as a static library, you should link soci_core_static and soci_mysql_static.

[baziorek]

It seems like you're linking the wrong targets. In order to use SOCI as a static library, you should link soci_core_static and soci_mysql_static.

But it is not generating that files to link, only:

 ls -lah libs/lib
total 6.1M
drwxrwxr-x 3 agh agh 4.0K Mar 17 17:34 .
drwxrwxr-x 4 agh agh 4.0K Mar 17 17:34 ..
drwxrwxr-x 3 agh agh 4.0K Mar 17 17:34 cmake
-rw-r--r-- 1 agh agh 3.9M Mar 17 17:32 libsoci_core.a
-rw-r--r-- 1 agh agh 149K Mar 17 17:32 libsoci_empty.a
-rw-r--r-- 1 agh agh 1.1M Mar 17 17:32 libsoci_mysql.a
-rw-r--r-- 1 agh agh 1.2M Mar 17 17:32 libsoci_postgresql.a

---

All steps to reproduce:

sudo apt install libmysqlclient-dev libmysql++-dev libmysqlcppconn-dev

git clone --depth=1 -b v4.0.3 https://github.com/SOCI/soci.git

export installPath="$(pwd)/libs"

mkdir soci/building
mkdir "$installPath"
cd soci/building
cmake -DCMAKE_INSTALL_PREFIX="$installPath" -DSOCI_SHARED=OFF -DSOCI_TESTS=OFF -DSOCI_CXX11=ON ..
make -j12 && make install
cd - # going back to previous directory

I'm also attaching full output of cmake command: cmakeOutput.txt More interesting part:

cat cmakeOutput.txt| grep -i mysql 
-- MySQL:
-- Performing Test HAVE_MYSQL_OPT_EMBEDDED_CONNECTION
-- Performing Test HAVE_MYSQL_OPT_EMBEDDED_CONNECTION - Failed
-- Found MySQL: /usr/include/mysql, /usr/lib/x86_64-linux-gnu/libmysqlclient.so
-- MySQL Embedded not found.
-- MYSQL_INCLUDE_DIR                        = /usr/include/mysql
-- MYSQL_LIBRARIES                          = /usr/lib/x86_64-linux-gnu/libmysqlclient.so
-- Found MySQL: /usr/include/mysql, /usr/lib/x86_64-linux-gnu/libmysqlclient.so
-- MySQL Embedded not found.
-- MySQL - SOCI backend for MySQL
-- SOCI_MYSQL                               = ON
-- SOCI_MYSQL_TARGET                        = soci_mysql
-- SOCI_MYSQL_OUTPUT_NAME                   = soci_mysql
-- SOCI_MYSQL_COMPILE_DEFINITIONS           = SOCI_ABI_VERSION="4.0" HAVE_DL=1 BOOST_ALL_NO_LIB
-- SOCI_MYSQL_INCLUDE_DIRECTORIES           = /usr/include /home/agh/Nextcloud/AGH/progranistyczneDlaStudentowWspolne/sprawdzarkaDomowe/sociTest/soci/building /home/agh/Nextcloud/AGH/progranistyczneDlaStudentowWspolne/sprawdzarkaDomowe/sociTest/soci/include /home/agh/Nextcloud/AGH/progranistyczneDlaStudentowWspolne/sprawdzarkaDomowe/sociTest/soci/building/include /home/agh/Nextcloud/AGH/progranistyczneDlaStudentowWspolne/sprawdzarkaDomowe/sociTest/soci/include/private /home/agh/Nextcloud/AGH/progranistyczneDlaStudentowWspolne/sprawdzarkaDomowe/sociTest/soci/include/private /usr/include/mysql
MYSQL_EMBEDDED_LIBRARIES:FILEPATH=MYSQL_EMBEDDED_LIBRARIES-NOTFOUND
MYSQL_INCLUDE_DIR:PATH=/usr/include/mysql
MYSQL_LIBRARIES:FILEPATH=/usr/lib/x86_64-linux-gnu/libmysqlclient.so
SOCI_MYSQL:BOOL=ON
WITH_MYSQL:BOOL=ON

[baziorek]

I checked also installing SOCI with MySQL support with VCPKG package manager - it succeed with static building.

---

Details:

So first I'm installing with soci:

export vcpkg_path="$(pwd)"

git clone --depth=1 -b 2024.02.14 https://github.com/microsoft/vcpkg

./vcpkg/bootstrap-vcpkg.sh -disableMetrics

vcpkgBinaryPath="${vcpkg_path}/vcpkg/vcpkg"
$vcpkgBinaryPath install 'soci[mysql]'

Everything is success, then I see:

    find_package(SOCI CONFIG REQUIRED)
    # Using core (loading backends at runtime)
    target_link_libraries(main PRIVATE $<IF:$<TARGET_EXISTS:SOCI::soci_core>,SOCI::soci_core,SOCI::soci_core_static>)

    # Using the mysql backend directly
    target_link_libraries(main PRIVATE $<IF:$<TARGET_EXISTS:SOCI::soci_mysql>,SOCI::soci_mysql,SOCI::soci_mysql_static>)

But I want to use CMakeLists.txt, so I need to have link:

$vcpkgBinaryPath integrate install
Applied user-wide integration for this vcpkg root.
CMake projects should use: "-DCMAKE_TOOLCHAIN_FILE=/home/agh/Desktop/sociMySQL_withVcpkg/vcpkg/scripts/buildsystems/vcpkg.cmake"

Then I've created sample CMakeLists.txt file:

cmake_minimum_required(VERSION 3.20)

set(CMAKE_TOOLCHAIN_FILE "${CMAKE_CURRENT_SOURCE_DIR}/vcpkg/scripts/buildsystems/vcpkg.cmake"  CACHE STRING "")

project(sociMySQL_withVcpkg VERSION 0.1 LANGUAGES CXX)

set(CMAKE_CXX_STANDARD 23)
set(CMAKE_CXX_STANDARD_REQUIRED ON)

find_package(SOCI CONFIG REQUIRED static)

set(PROJECT_SOURCES
    main.cpp
)

add_executable(${PROJECT_NAME} ${PROJECT_SOURCES})

# Using core (loading backends at runtime)
target_link_libraries(${PROJECT_NAME} PRIVATE $<IF:$<TARGET_EXISTS:SOCI::soci_core>,SOCI::soci_core,SOCI::soci_core_static>)

# Using the mysql backend directly
target_link_libraries(${PROJECT_NAME} PRIVATE $<IF:$<TARGET_EXISTS:SOCI::soci_mysql>,SOCI::soci_mysql,SOCI::soci_mysql_static>)

And sample C++ code (with name main.cpp):

#include <iostream>
#include <string>
#include <exception>
#include <soci/soci.h>
#include <soci/soci-config.h>
#include <soci/mysql/soci-mysql.h>

#if __has_include(<format>)
    #include <format>
    namespace fmt = std;
#elif __has_include(<fmt/format.h>)
    #define FMT_HEADER_ONLY
    #include <fmt/format.h>
#else
    #error "No format library!"
#endif

using namespace std;
using namespace soci;

constexpr char DATABASE_NAME[] = "stack";
constexpr char USERNAME[] = "someuser";
constexpr char PASSWORD[] = "somepass";

int main()
{
    auto connectionString = fmt::format("host=127.0.0.1 db={} user={} password='{}'", DATABASE_NAME, USERNAME, PASSWORD);

    try
    {
        soci::session sql(soci::mysql, connectionString);

        sql << R"(
CREATE TABLE IF NOT EXISTS stack.communities (
    community_id INT PRIMARY KEY AUTO_INCREMENT NOT NULL COMMENT 'Unique identifier for each community',
    community_name_pl VARCHAR(100) NOT NULL COMMENT 'Name of the community in Polish',
    community_original_name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT 'Original name of the community if different from Polish',
    creators VARCHAR(100) NOT NULL COMMENT 'List of original founders of the community, comma-separated'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Table containing main information about Catholic communities';
)";

        sql << "INSERT INTO stack.communities (community_name_pl, creators) VALUES ('Mamre', 'ks. dr Włodzimierz Cyran'); ";

        soci::rowset<soci::row> rows = sql.prepare << "SELECT * FROM stack.communities";
        for(auto& row: rows )
        {
            for( std::size_t i = 0; i != row.size(); ++i )
            {
                const column_properties & props = row.get_properties( i );

                cout << props.get_name() << " = ";

                switch( props.get_data_type() )
                {
                case dt_string:
                    cout << row.get < std::string >( i ) << '\n';
                    break;
                case dt_double:
                    cout << row.get < double >( i ) << '\n';
                    break;
                case dt_integer:
                    cout << row.get < int >( i ) << '\n';
                    break;
                default:
                    cerr << "(unknown type!)\n";
                }
            }
        }
    }
    catch(const soci_error& e)
    {
        cerr << "Error: " << e.what() << '\n';
    }
}

After everything is ready I can generate the project:

mkdir building
cd building
cmake ..
make -j12
./sociMySQL_withVcpkg

Result of program is:

./sociMySQL_withVcpkg
community_id = 1
community_name_pl = Mamre
community_original_name = Error: Null value not allowed for this type

[zann1x]

The libsoci_*.a files are the static library files of SOCI. Although the files are named differently, you should still link against the targets soci_*_static -- or at least that's what works on my end.

In your test with vcpkg you're linking against exactly these targets as well. There, it should also be sufficient to only link soci_core_static and soci_mysql_static. soci_core and soci_mysql are the targets for dynamically linking SOCI.

[baziorek]

The libsoci_*.a files are the static library files of SOCI. Although the files are named differently, you should still link against the targets soci_*_static -- or at least that's what works on my end.

In your test with vcpkg you're linking against exactly these targets as well. There, it should also be sufficient to only link soci_core_static and soci_mysql_static. soci_core and soci_mysql are the targets for dynamically linking SOCI.

Unfortunately when I even try with version from master branch, instead of v4.0.3, unfortunately there is still problem with compilation. I'm using CMake command:

export installPath="$(pwd)/libs"
cmake -DCMAKE_INSTALL_PREFIX="$installPath" -DSOCI_STATIC=ON -DSOCI_SHARED=OFF -DSOCI_TESTS=OFF ..

and (after running make install) this is not building soci_*_static just:

find . -name 'libsoci*'
./libs/lib/libsoci_mysql.a
./libs/lib/libsoci_postgresql.a
./libs/lib/libsoci_empty.a
./libs/lib/libsoci_core.a
./libs/lib/libsoci_sqlite3.a
./soci/building/lib/libsoci_mysql.a
./soci/building/lib/libsoci_postgresql.a
./soci/building/lib/libsoci_empty.a
./soci/building/lib/libsoci_core.a
./soci/building/lib/libsoci_sqlite3.a

Of course I can force CMake to create those files: cmake ...... -DSOCI_MYSQL_TARGET=soci_mysql_static but it is just rename.

All commands:

git clone --depth=1 https://github.com/SOCI/soci.git
 export installPath="$(pwd)/libs"
 mkdir soci/building && mkdir "$installPath" && cd soci/building
 cmake -DCMAKE_INSTALL_PREFIX="$installPath" -DSOCI_STATIC=ON -DSOCI_SHARED=OFF -DSOCI_TESTS=OFF .. # -LA
make install -j12

cd -

# compilation:
g++ -isystem/usr/include/mysql/  -isystem"${installPath}/include" -isystem./soci/include/ --std=c++23 test.cpp `mysql_config --variable=pkglibdir`/libmysqlclient.a `pkg-config --static --libs mysqlclient` -L"${installPath}/lib" -lsoci_core -lsoci_mysql && ./a.out

unfortunately is still fails!

---

It is not compiling with commands, but fortunately version from VCPKG package manager works (link: https://github.com/SOCI/soci/issues/1131#issuecomment-2002623429). So for me it is enough.

[Han-ga86caq]

Same problem for me with Ubuntu 23.10, and backend SQLite3.

[Sildra]

Put soci_core after the backends, it will solve the link issue.

[baziorek]

Put soci_core after the backends, it will solve the link issue.

Unfortunately it still didn't work after changing order (I tried steps described: https://github.com/SOCI/soci/issues/1131#issuecomment-2042479403). If You have sample code could You please provide for other users? For me using VCPKG is enough:)

[Sildra]

in your comment, you put -lsoci_core -lsoci_mysql during the compilation phase. The function is not used in soci_core so it is flagged by the compiler as unused and stripped before the backend is added. To fix this, you must put -lsoci_core after -lsoci_mysql.

# compilation:
g++ -isystem/usr/include/mysql/ \
  -isystem"${installPath}/include" -isystem./soci/include/ \
  --std=c++23 test.cpp \
  `mysql_config --variable=pkglibdir`/libmysqlclient.a \
  `pkg-config --static --libs mysqlclient` -L"${installPath}/lib" \
  -lsoci_mysql -lsoci_core && ./a.out

[Han-ga86caq]

Put soci_core after the backends, it will solve the link issue.

Thanks for the suggestion, and using soci_core does work.

However, using soci_core seems to link to SOCI dynamically, but I prefer a static link because myself is compiling a dynamic library that needs to be shipped to both Windows and Linux systems; if it is dynamically linked, it will probably have trouble on Windows.

[Sildra]

use explicit static libraries : -l:libsoci_sqlite3.a -l:libsoci_core.a