MySQL extension

For the MySQL extension, I now have a basic set of functions implemented. Here is a list of all functions and the status. I would like to know if the functions I still plan to implement are worth the trouble or can be skipped, and if any of the ones I think should be ignored are on the must-haves list of someone.

mysql_affected_rows()            mysql-affected-rows            Basic functions
mysql_autocommit()               mysql-autocommit               ?
mysql_change_user()              mysql-change-user              ?
mysql_character_set_name()       mysql-character-set-name       Done
mysql_close()                    mysql-close                    Basic functions
mysql_commit()                   NO                             Perform query "commit;"
mysql_connect()                  NO                             Deprecated function
mysql_create_db()                NO                             Deprecated function
mysql_data_seek()                mysql-data-seek                Done
mysql_debug()                    NO                             Needs super rights.
mysql_drop_db()                  NO                             Deprecated function
mysql_dump_debug_info()          NO                             Needs super rights
mysql_eof()                      NO                             Deprecated function
mysql_errno()                    mysql-errno                    Basic functions
mysql_error()                    mysql-error                    Basic functions
mysql_escape_string()            NO                             Depricated (Use a wrapper function)
mysql_fetch_field()              mysql-fetch-field              Done
mysql_fetch_field_direct()       mysql-fetch-field-direct       Done
mysql_fetch_fields()             mysql-fetch-fields             Done
mysql_fetch_lengths()            mysql-fetch-lengths            Done
mysql_fetch_row()                mysql-fetch-row                Basic functions
mysql_field_count()              mysql-field-count              Basic functions
mysql_field_seek()               mysql-field-seek               Done
mysql_field_tell()               mysql-field-tell               Done
mysql_free_result()              mysql-free-result              Basic functions
mysql_get_character_set_info()   mysql-get-character-set-info   Done
mysql_get_client_info()          mysql-get-client-info          Basic functions
mysql_get_client_version()       mysql-get-client-version       Done
mysql_get_host_info()            mysql-get-host-info            Basic functions
mysql_get_option()               NO                             Not available in older versions pre 2012
mysql_get_proto_info()           mysql-get-proto-info           Done
mysql_get_server_info()          mysql-get-server-info          Basic functions
mysql_get_server_version()       mysql-get-server-version       Done
mysql_get_ssl_cipher()           mysql-get-ssl-cipher           ?
mysql_hex_string()               mysql-hex-string               ?
mysql_info()                     mysql-info                     Done
mysql_init()                     NO                             Not in MySQL 8.0
mysql_insert_id()                mysql-insert-id                Done
mysql_kill()                     mysql-kill                     For Threads?
mysql_library_end()              mysql-library-end              For Threads?
mysql_library_init()             mysql-library-init             For Threads?
mysql_list_dbs()                 NO    
mysql_list_fields()              NO    
mysql_list_processes()           mysql-list-processes           For Threads?
mysql_list_tables()              NO    
mysql_more_results()             mysql-more-results             Done
mysql_next_result()              mysql-next-result              Done
mysql_num_fields()               mysql-num-fields               Done
mysql_num_rows()                 mysql-num-rows                 Basic functions
mysql_options()                  mysql-options                  ?
mysql_options4()                 mysql-options4                 ?
mysql_ping()                     mysql-ping                     Done
mysql_query()                    mysql-query                    Basic functions
mysql_real_connect()             mysql-connect                  Basic functions
mysql_real_connect_dns_srv()     NO    
mysql_real_escape_string()       NO                             Use a wrapper function
mysql_real_escape_string_quote() NO                             Use a wrapper function
mysql_real_query()               NO    
mysql_refresh()                  NO                             Needs RELOAD privilege
mysql_reload()                   NO                             Needs RELOAD privilege
mysql_reset_connection()         NO                             Not available in older versions pre 2012
mysql_reset_server_public_key()  NO    
mysql_result_metadata()          NO    
mysql_rollback()                 NO                             Perform query "rollback;"
mysql_row_seek()                 mysql-row-seek                 Done
mysql_row_tell()                 mysql-row-tell                 Done
mysql_select_db()                NO    
mysql_server_end()               NO    
mysql_server_init()              NO    
mysql_session_track_get_first()  NO    
mysql_session_track_get_next()   NO    
mysql_set_character_set()        mysql-set-character-set        Done
mysql_set_local_infile_default() NO    
mysql_set_local_infile_handler() NO    
mysql_set_server_option()        NO    
mysql_shutdown()                 NO    
mysql_sqlstate()                 mysql-sqlstate                 Done
mysql_ssl_set()                  mysql-ssl-set                  ?
mysql_stat()                     mysql-stat                     Done
mysql_store_result()             mysql-store-result             Basic functions
mysql_thread_end()               mysql-thread-end               For Threads?
mysql_thread_id()                mysql-thread-id                For Threads?
mysql_thread_init()              mysql-thread-init              For Threads?
mysql_thread_safe()              mysql-thread-safe              For Threads?
mysql_use_result()               mysql-use-result               Done
mysql_warning_count()            mysql-warning-count            Done

TBD: To Be Done, planning to add this in the next version
Done: Added
?: Not sure if needed and if so why is it needed as a separate native?
NO: I do not see the need for this, especially the list functions that show items based on Regular Expressions.

If you're building a "real" interface, then it would be important for you to expose SQL escaping in some way. It's dangerous to make your API just based on "query string in, result string out"'s too easy for data being spliced into the string to be interpreted as code. This is why systems like ODBC are parameterized in ways that have the code part indicate the slots where substitutions will be done, and make sure those slots can never be run as code.

The natives you are writing need to be parameterized somehow in a way that separates the code and the data. Something like:

table: "widgets"
quantity: 10
mysql-query "select data from ? where x = ?" [table quantity]  ; "good"

mysql-query spaced ["select data from" table "where x =" quantity]  ; "bad"

You're not using the mysql_escape_xxx or mysql_real_escape_xxx functions. So I'm not sure where you're at with this separation. Be sure to read up on this topic and why it is important.

Bigger picture: realistically speaking, it will be a long-time-if-ever before anyone uses the MySQL extension besides you. So being able to do what you want, and learn what you want, should be your main goal.

If you are serious enough to want to think about what other people might care about, that will require moving to a focus on setting up reproducible tests. Since you're comfortable with VMs now, you should probably be able to get started with a Travis project that installs MariaDB, makes a fresh database, and interacts with it. The ODBC tests show setting up MySQL databases on the command line in a script, and you'd want to write some similar lines.

You'd start by making a .travis.yml for your extension. There'll be options for installing MariaDB with something analogous to sudo apt install...but you'll have to ask Travis to wget or curl a mysql-enabled-interpreter from your website that you've built each time it runs. (The goal is to move all the existing extensions out of the main repository, so I don't want to complicate the core build any more with another extension. We'll have to cross the bridge of making the executables online when we get to it.)

As for what the test does...that's up to you. The ODBC test creates tables, inserts values, and makes sure it gets the same thing back. That's a fairly basic idea for a test which helps show general functioning. But you can make it more specific to your use case if you want.

All that said: I'm busy devoting most of my time right now to studying other languages to try and revisit longstanding design holes in Redbols. All under the lens of a full stackless coversion. That's going to be the situation for the foreseeable future, and the Wasm build remains the main target of interest.

But I'll try to help unblock you if you keep with it. Like I said, it seems like you are actually sticking with something for a while, so congratulations on that. I would imagine you're learning some things in this process. Maybe you'll find a new calling in your career because of it?


Thank you for your great answer again!

I added the legend for describing the meaning of the remarks.

The escape functions are marked with a question mark and I know about escaping and its importance. I was still wondering if I should make them also a native or use them inside other functions so you do not have to worry about escaping, or maybe you should take care of the escaping using Rebol functions before submitting to the MySQL functions. (Like PHP uses addslashes and stripslashes).

In a small community like ours at the moment it will be indeed the question if anybody else will use this feature. On the other hand, providing this will give some daring souls the opportunity to try this out for themselves, and even get them interested in the rest of the endeavor.

I have updated the list of implemented functions. Unfortunately mysql_get_option and mysql_reset_connection were not present in the mysql.h header file for the CentOS 7 operating system, as the headerfile that is available on that system is pre 2013 and no later version is provided. Even updating my local VM is not an option as I do not update the hosting server by that action. I had a lot of coding done before I found out. Let's say that is some free and helpful programming experience in C for me.

I will leave the mysql with this functionality if no urgent needs are posted here.

I'll have a go at a Travis - CI automated test, but I think it is best left to have testscripts that can be run on a real server. Would very much to have it included in the renc repo.

We definitively need a new build process that has a default to not include extensions unless the build opts in.

1 Like

Is your web host running a pre-2013 distribution of an OS? :-/

Well, we do like to have things tested on a wide set of platforms...and within reason, old solid ones help with dependency control.

People apparently have used Docker to run CentOS 7 on Travis. So that would be neat to see used for both the build and the tests.

I have changed the post, I think that is just a part that is not provided for by the CentOS maintainers for any (good?) reason. I believe the hosting is taking care of keeping up to date on security updates, ah and yes they are using MariaDB which may not have that problem. I wanted to stick to MySQL but I'll see if there is a different option, creating another VM soon. (NOTE_1)

Perhaps I could make an compiler directive MYSQL_PRE_2013 or alike that will not include some functions.

I had a look at docker and CentOS 7 when I had some trouble with the VM, but it was a lot of information coming towards me and I did not see how to make it work for me. Free time is even today a scarce commodity.

If I understand the article correct, Travis-CI is using Ubuntu to test stuff on, therefor they use Docker to get an instance of CentOS (an EL type OS, RHEL stands for Red Hat Enterprise Linux(?)) running and execute their testscripts on that.
Docker is not so hard, but getting Travis for our repo is perhaps harder.

(NOTE_1) Created a new VM and installed MariaDB and the -devel package. The mysql.h file still was the old "Copyright (c) 2000, 2012, Oracle and/or its affiliates" version. And "mysql -V
mysql Ver 15.1 Distrib 5.5.65-MariaDB, for Linux (x86_64) using readline 5.1", I could not find a mariadb.h, they use mysql.h also(?).
As turns out this version is coming with the OS, but it will not automatically upgrade to a newer version like Distrib 10.2.33-MariaDB without the admin to do this explicitly by uninstalling the old version and ordering yum to go for the 10.2 version.
So I did that. And guess what? The comments still mention the copyright until 2012 like before, terrible! But... the functions missing before are now present! So it is a newer version

Another soap episode. Off course the program from the new VM crashes on my hostproviders server.
Because it cannot find the library file on its system.
I checked and these are the mysql_config data on the old

Usage: /bin/mysql_config [OPTIONS]
--cflags [-I/usr/include/mysql]
--include [-I/usr/include/mysql]
--libs [-L/usr/lib64/mysql -lmysqlclient -lpthread -lz -lm -ldl -lssl -lcrypto]
--libs_r [-L/usr/lib64/mysql -lmysqlclient -lpthread -lz -lm -ldl -lssl -lcrypto]
--plugindir [/usr/lib64/mysql/plugin]
--socket [/var/lib/mysql/mysql.sock]
--port [0]
--version [5.5.65]
--libmysqld-libs [-L/usr/lib64/mysql -lmysqld]
--variable=VAR VAR is one of:
pkgincludedir [/usr/include/mysql]
pkglibdir [/usr/lib64/mysql]
plugindir [/usr/lib64/mysql/plugin]

and on the new VM

Usage: /bin/mysql_config [OPTIONS]
--cflags [-I/usr/include/mysql -I/usr/include/mysql/mysql]
--include [-I/usr/include/mysql -I/usr/include/mysql/mysql]
--libs [-L/usr/lib64/ -lmariadb]
--libs_r [-L/usr/lib64/ -lmariadb]
--plugindir [/usr/lib64/mysql/plugin]
--socket [/var/lib/mysql/mysql.sock]
--port [3306]
--version [10.2.33]
--libmysqld-libs [-L/usr/lib64 -lmysqld -lpthread -lz -lm -ldl -lssl -lcrypto -lcrypt -llzma -laio -lsystemd]
--variable=VAR VAR is one of:
pkgincludedir [/usr/include/mysql]
pkglibdir [/usr/lib64]
plugindir [/usr/lib64/mysql/plugin]

The error I get is

error while
loading shared libraries: cannot open shared object
file: No such file or directory

As the providers says this library is present but not at /usr/lib64/ but at /usr/lib64/pleskspecificdirectory/ and indeed (I have SSH connection now) I can affirm it is at that location. It is also in /lib64/.../ .

So I changed the link step to not use `mysql_config --libs` but to use this specific location -L/usr/lib64/pleskspecificdirectory/ -lmariadb and I copied the libfile also to that very same location on my new VM.

Linking without such an additive results in errors, using the above or using the `mysql_config --libs` result libraries from the old VM is OK and gets an executable.

Yet both of those still err on the same problem.

Just one thing I noticed.. the r3's that are produced by linking one way or the other are exactly the same size, where I expect a location of different length could make a difference.

Also is present in the /usr/lib64/ directory.
That is thus why the old VM can create the working version.

I did an ldd of the sources I have created and found that the is sought inside the common /usr/lib64 directory, where it is not at.
I hoped the -L would make it search for the directory provided BUT APPARENTLY not.
Any suggestions are welcome.

-L tells the linker where to search for libraries at compile time...for putting code directly into the executable. These are .a files.

But the .so files are like DLLs. They aren't linked into your program, but expected to be somewhere around on your system. As with DLLs, sometimes the easiest thing to do is just copy it to the same directory with your executable (or better yet, make a file linkage with ln).

You can use ldd to dump out the .so files that an executable looks for

You can read up on rules about where the dynamic loader looks.

So to put the pieces of the puzzle together, you need to make sure that the executable you build is looking for an .so file available on the target. If all else fails, find the .so on your local machine and put it in the same directory as the executable.

Somehow I tricked the entire thing and convinced it to use the, last two lines of my linking step

vector-init.o objs/view/mod-view.o objs/tmp-mod-view-init.o -lm -ldl 
-L/usr/lib64/ -lmysqlclient -lpthread -lz -lm -ldl -lssl -lcrypto objs/main.o

but though things work..

First I got

ldd r3 =>  (0x00007ffed2df4000) => /lib64/ (0x00007ff098f10000) => /lib64/ (0x00007ff098d0c000) => not found => /lib64/ (0x00007ff09893e000)
/lib64/ (0x00007ff099212000)

Which already was shorter than the 'complete' list I got before, but look at it now: =>  (0x00007ffe2d692000) => /lib64/ (0x00007f4149ba2000) => /lib64/ (0x00007f414999e000) => /lib64/ (0x00007f4149782000) => /lib64/ (0x00007f414956c000) => /lib64/ (0x00007f41492fa000) => /lib64/ (0x00007f4148e97000) => /lib64/ (0x00007f4148ac9000)
/lib64/ (0x00007f4149ea4000) => /lib64/ (0x00007f414887c000) => /lib64/ (0x00007f4148593000) => /lib64/ (0x00007f414838f000) => /lib64/ (0x00007f414815c000) => /lib64/ (0x00007f4147f4c000) => /lib64/ (0x00007f4147d48000) => /lib64/ (0x00007f4147b2e000) => /lib64/ (0x00007f4147907000) => /lib64/ (0x00007f41476a5000)

No libmariablahblahblah but also no libmysqlclientetc !
So got it working but totally dissatisfied because I do not understand :frowning:

Yes I think I nailed it!

When searching for "how does gcc know which libs to load at runtime" this sent me To the link on Stackexchange that Brian already posted before And from here I found my definitive clue at howto C Libraries But you need to read carefully.

The hint that sent me of in the right direction was

If you create your own shared object files and do not install them in /usr/lib, then you need to set your LD_LIBRARY_PATH environment variable so that the runtime linker can find them and load them at run time. For example, if I put my .so files in a directory named lib in my home directory, I'd set my LD_LIBRARY_PATH enviroment to the following:

if running bash:
export LD_LIBRARY_PATH=/terrible/new/pathname/lib:$LD_LIBRARY_PATH

if running tcsh:
setenv LD_LIBRARY_PATH /terrible/new/pathname/lib:$LD_LIBRARY_PATH

So I went ahead and appended the cruel directoryname used by the Plesk creators. (My best guess is they did this on purpose because they are running MySQL and MariaDB instances on their platforms and do not want them to interfere with each other)

Now the linking returns an executable with the proper library found

ldd r3 =>  (0x00007ffc1e72b000) => /lib64/ (0x00007fc9ae615000) => /lib64/ (0x00007fc9ae411000) => /usr/lib64/libmariadbclient-plesk-3.0/ (0x00007fc9ae1bb000) => /lib64/ (0x00007fc9added000)
/lib64/ (0x00007fc9ae917000) => /lib64/ (0x00007fc9adbd1000) => /lib64/ (0x00007fc9ad9bb000) => /lib64/ (0x00007fc9ad749000) => /lib64/ (0x00007fc9ad2e6000) => /lib64/ (0x00007fc9ad099000) => /lib64/ (0x00007fc9acdb0000) => /lib64/ (0x00007fc9acbac000) => /lib64/ (0x00007fc9ac979000) => /lib64/ (0x00007fc9ac769000) => /lib64/ (0x00007fc9ac565000) => /lib64/ (0x00007fc9ac34b000) => /lib64/ (0x00007fc9ac124000) => /lib64/ (0x00007fc9abec2000)

That looks a whole lot better.

It's all pretty messy stuff. But in this particular day and age, computers aren't psychic. So if you change anything that anything else depends on, everything has to be explicit.

My biggest complaint about these library dependencies isn't that they exist, but that they are shuffled so far into darkness that you cannot reason about them. Once you learn about tools like ldd you can start to do that... and you are. So you're actually learning some things. Good work. :slight_smile:


Thank you Brian!
Strange thing is how it now looks to me. Because I added that directory to a path, it is like any executable starting up is going "hey listen everybody I need to do some work and I have to use a list of functions. First function is function A anybody familiar with a function A? Ah, according to some magic info I just found, there should be a library by the name of libA that should know function A. Are you there libA?"

My VM froze on me just after I had tested the program on the host and before I could save the correct link step used.
I had to reset.
Now I have a working program again, but the ldd dependencies do not mention the libmariadbclient (nor some libmysql) =>  (0x00007ffcb4f1d000) => /lib64/ (0x00007f04c07c3000) => /lib64/ (0x00007f04c05bf000) => /lib64/ (0x00007f04c03a3000) => /lib64/ (0x00007f04c018d000) => /lib64/ (0x00007f04bff1b000) => /lib64/ (0x00007f04bfab8000) => /lib64/ (0x00007f04bf6ea000)
/lib64/ (0x00007f04c0ac5000) => /lib64/ (0x00007f04bf49d000) => /lib64/ (0x00007f04bf1b4000) => /lib64/ (0x00007f04befb0000) => /lib64/ (0x00007f04bed7d000) => /lib64/ (0x00007f04beb6d000) => /lib64/ (0x00007f04be969000) => /lib64/ (0x00007f04be74f000) => /lib64/ (0x00007f04be528000) => /lib64/ (0x00007f04be2c6000)

So how is this possible? Did I static link the library, the program is about 0.3 MB larger..

If it works, that's almost a certainty.

If you want to go even deeper into understanding what's in an executable, there is nm.

I got a libmariadbclient.a file in my /usr/lib64 on my local VM, that explains.

1 Like

So I experimented more with this and the conclusion is a little disappointing.
Because the loader searches on runtime in the default directories for the shared library, it will not find the one in the special directory. Using the environment variable $LD_LIBRARY_PATH can help on compile time to find the dependency, and when run. But the environment variable is not persistent between SSH sessions so setting this is of no use in my case.
The best option I have for now is continuing with linking the .a library (so remove or rename the .so one) and making it static.
I suspect the PHP on this system uses the very same method and is also not using the .so lib.

And just when writing this up, another idea pops up. Passing the environment variable through the .htaccess file using SetEnv directive.

Well that would not work because the libname is not .so but .so.3 and a symlink is also not an option.
Putting the .so.3 and a symlink .so in the directory (cgi-bin) is also no option, because that also does not work. (Strange but true)

There's almost always some way to configure environment variables persistently.

There's .profile .bash-profile, and .bashrc, for example.

(Remember to be sure to do export VAR=xxx and not set VAR=xxx, because SET doesn't pass the variable up to the calling environment of a shell script.)

If you're trying to configure an environment variable to be present when the web server is running (e.g. you're not logged in) that's different. But it's certainly something a host that let you run your own processes should let you specify one way or another.

I suspect the PHP on this system uses the very same method and is also not using the .so lib.

Seems you now know enough to dig around in the executables, the libraries they depend on, and that those depend on etc. to find the answers to such questions vs. guess...

1 Like

Great. So having no symlink and no host provider to put one up, I dug deeper again.


  1. It will be easiest to link with the static library ( .a) and use the resulting executable.

  2. It is possible to have a workaround!

Let me shortly explain the workaround now.

Instead of using the direct method using the Rebol CGI program on the shebang line like this

#!/path/to/cgi-bin/r3 -c

We will use the bash shell as an intermediate.

export LD_LIBRARY_PATH=/path/to/cgi-bin/
sed -n -e '5,$p' < "$0" | /path/to/cgi-bin/r3 -c "$0" "$@"
exit $?
print "Content-type: text/html^/Foo"

(Source Multiline Shebang )
What happens here?
First line calls the bash shell
Second line exports the LD_LIBRARY_PATH environment variable to add the directory where the runtime loader will look for the dynamic libraries used. In this case I made a symbolic link here that points to the appropriate library.
Third line catches the content of the Rebol script by streaming it from line 5 where it starts until the end ( $p ) into the variable $0 and pipes this through to the Rebol r3 CGI program, using -c to signal it is to be executed as a CGI script (I managed to catch the welcome message of the Rebol console in my error log! :smiley: )
On line 4 the bash shell is exited and the script itself is following after that.

I will document this option, but as you will understand I will be using the static linked version from now on.

sed is one of those unreadable things we're attempting to replace. What keeps you from making the output of the script exactly what you want? And if you need to manipulate it, it would be a better exercise to do it with the r3 executable...

It's often easiest to use statically linked versions of things. But then two programs running on the same machine that both static link a library wind up loading two copies of the library into memory if they both run. So it multiplies the disk space you use, as well as potentially the memory when you are running multiple copies at once.

An additional thing is that when you statically link a library version into your executable, then that means if it's something that gets security updates or patches (like OpenSSL) your executable won't get those patches even if the installed shared library does.

For these reasons (excuses?) you will find static linking is not always made all that easy...and sometimes, even made impossible.

The fact that I cannot use the r3 executable is because it will not load for the missing of the dynamic library for mariadb, and putting one extra version of it there to use just for this sole purpose feels like overkill to me.

However if the r3 executable could load the mysql module on the fly by reading a setup file from the current folder (perhaps also using a symlink to the host provided library from the current folder) ... that would be nice.