SL Communication with MariaDB Issues

[Ayame Musashi]

Does anyone have experience working with communication between SL and a web server based MariaDB?

I have the "SLDB: SIMPLE DATABASE STORAGE FOR LSL" script that I have used in the past, and I have it setup and connecting to a normal MySQL database on my main website server. This works just fine. But then recently I decided to rent a VPS to work on projects and such, and after a whole lot of research and tinkering, I got a LEMP stack setup on it, and even have a website working on it. I have created a simple PHP file for another project and I am able to interact with that from SL with no trouble.

So now I am setting up another object to interface with a database on my VPS, and I am running into trouble. I can't tell if there is some difference in how PHP interacts with a MariaDB vs a MySQL DB, or if I have missed some configuration on my server that needs to be done in order for this to work. I would assume that since Wordpress is working just fine with the DB, that the DB calls and such are identical to MySQL, and that the server is configured properly to have a PHP file interface with the MariaDB, but I am rather stumped by this simple interface script failing.

If I manually call the PHP file from a browser it does the same thing it does when I call it from within SL, that is, it redirects to a 404 of the website hosted on the server. I feel this is happening past the point that the script has ran and is trying to output data, because if I leave out the arguments for the PHP database interface, then the PHP database interface actually responds with an error message of insufficient arguments. All of this again from a browser. This leads me to believe it is a configuration on the server that is preventing the final output, but I can't quite put my finger on it.

If anyone has experience with this kind of thing, please enlighten me.

Thank you!

Edited February 5, 2022 by Ayame Musashi
clarifying working DB interface on different server

[tomm55]

Not sure if I understood it correctly, but are you basically saying that:

- If you run your PHP script while leaving out the DB connection arguments, then it results in a PHP script error about missing arguments,
- But if run your PHP script while having the arguments, then it redirects to 404 page?

If yes, then you have to find out why it is redirecting you to a 404 page. There might be more reasons for that. I don't know if you are using some PHP framework, or you just use a simple PHP script. The web server might be also configured to redirect to a 404 under some circumstance. I can't say without additional info 🙂 Maybe try adding some debugging output to your PHP script, or if you have access to logs, check logs to see what's happening.

And regarding MariaDB vs MySQL, I think that you don't have to worry about that, as they both should be compatible and if I am not wrong, then I think you can use the same PHP code to connect to both Maria and MySQL.

[Ayame Musashi]

The PHP script has multiple functions for interacting with the database, you must tell it what records you are wanting to access, whether you are wanting to read, write, delete. so the PHP script has arguments it needs in order to know what to do. if I leave these arguments out, then the PHP script responds with an error of insufficient arguments. this tells me the PHP script is accessible, and is working, but when I give it the arguments it needs to complete an interaction with the database, THEN it redirects to a 404 page, instead of passing back the information from the database, or a response. Does that make more sense?

So it seems to me, it is in the final stages of the PHP execution, where it should be outputting the information, or at least a response when I am storing information into the database, that it is redirecting to a 404 page.

 

[animats]

2 hours ago, Ayame Musashi said:

I have the "SLDB: SIMPLE DATABASE STORAGE FOR LSL"

Er, the PHP side of that badly needs a security upgrade.

See https://bobby-tables.com/

Someone who codes PHP might want to do that. Thanks. (I code mostly in Rust, Go, Python, LSL, and in emergencies Javascript, but not PHP).

Edited February 6, 2022 by animats

[Ayame Musashi]

Security is not much of a concern at the moment. The information being stored is trivial. Mainly wanting to use it for memory storage of an AI, for an NPC I have roaming around my sim. 😀 I will make it more secure once I have it working properly. Thanks for the feedback though!

Roaming Bear NPC (Obviously Kumamon, I made the mesh for him myself.) He is already interactive, but quickly outgrowing script limits, so I need to break his AI into multiple scripts and move his memories to a database.

 

[tomm55]

19 hours ago, Ayame Musashi said:

The PHP script has multiple functions for interacting with the database, you must tell it what records you are wanting to access, whether you are wanting to read, write, delete. so the PHP script has arguments it needs in order to know what to do. if I leave these arguments out, then the PHP script responds with an error of insufficient arguments. this tells me the PHP script is accessible, and is working, but when I give it the arguments it needs to complete an interaction with the database, THEN it redirects to a 404 page, instead of passing back the information from the database, or a response. Does that make more sense?

So it seems to me, it is in the final stages of the PHP execution, where it should be outputting the information, or at least a response when I am storing information into the database, that it is redirecting to a 404 page.

 

Could you be little bit more specific? For example, what arguments are you talking about? Do you mean the URL query arguments, like "http://server/myscript.php?these=are&some=query&arguments" ? Or do you talk about function arguments inside the PHP script? Maybe would be best if you gave an example.

But anyway, normally a PHP script should not redirect you to a 404 page if it's not made to do so. I don't know if you know how to work with PHP or not, but try adding the following code into your PHP script, for example right after the place where it connects to DB:

die("Hey, the script has successfuly run until this point");

Then try to access your script from web browser to see what is the output. If it still redirects you to 404, then try to put that "die" before the connecting part. Or if you don't understand PHP at all, then simply put it at the beginning of the PHP file, right after <? or right after <?php (e.g. on a new line). That should give you a better idea if the redirection is being made from the PHP script or whether it is made by the web server.

Edit: I have found the SLDB thing and now I understand more. You are probably talking about URL query arguments. Anyway, I would still suggest to do what I wrote above and also give us an example of the URL that you tried and caused a 404 redirect (you don't have to share the domain name with us if you are concerned about security, just share the script name and query arguments part, for example "myscript.php?these=are&some=query&arguments".

Edited February 6, 2022 by tomm55

[tomm55]

7 hours ago, animats said:

Er, the PHP side of that badly needs a security upgrade.

See https://bobby-tables.com/

Someone who codes PHP might want to do that. Thanks. (I code mostly in Rust, Go, Python, LSL, and in emergencies Javascript, but not PHP).

Yes, I have checked it too right now, and it's basically like a one big security hole. But as long as nobody guesses the secret password that's needed to communicate with the PHP part, then the OP should be probably safe

Edit: well, actually you will be safe only if you don't put user input from SL users to the DB.

Edited February 6, 2022 by tomm55

[Xiija]

@Ayame Musashi

are you logging the requests to the php script?.. are all the values getting thru?

[Ayame Musashi]

On 2/6/2022 at 1:59 AM, tomm55 said:

Edit: I have found the SLDB thing and now I understand more. You are probably talking about URL query arguments. Anyway, I would still suggest to do what I wrote above and also give us an example of the URL that you tried and caused a 404 redirect (you don't have to share the domain name with us if you are concerned about security, just share the script name and query arguments part, for example "myscript.php?these=are&some=query&arguments".

I am a web developer. I am not 100% in PHP, but I know enough to get things done. but I do not normally configure servers, and have not ran into an issue like this before, which is why I was asking. It seems to be more about the server configuration, something I am overlooking. The 404 message was coming back to SL, to the script, and being output instead of the expected output from that basic PHP script. since you have it, you know. So when I saw this output in SL, I first tried calling the PHP script with no URL arguments, and it gave me the "ERROR: NOT AUTHENTICATED". Then I threw in the "secret passphrase" as the only URL argument, and I got "ERROR: INSUFFICIENT ARGUMENTS". so then I just had the LSL script generate it's URL and output it to chat, then took that and fed it to the browser, and got the 404 page for the Wordpress installation that is setup in the same hosting space.  The same thing I was seeing in the body of the http_response returned to SL.

I will try adding some breaks in the code to see how much further it is getting, but I suspect that it is completing, but rather than directing the output back, it's redirecting to 404.

On 2/6/2022 at 9:58 AM, Xiija said:

@Ayame Musashi

are you logging the requests to the php script?.. are all the values getting thru?

I am not logging anything currently. I guess I can try that as well. I can't see why anything would not get through, as this is a very basic script, both on the PHP side and the LSL side, and I have the exact same set of files setup and working on a shared hosting server, and it is working as expected. I duplicated the setup in a second object in SL and on my new VPS, and ran into this issue. Since I have not installed or administrated a Unix/Linux machine in probably 30 years, my first suspicion is that I have not configured something properly on the server and it is causing this.

I previously created another PHP page on the new server for handling YouTube videos, and it is working fine. It's basically a simple PHP that builds a YouTube iframe embed using the arguments you pass it with the URL, which allows me to play any YouTube video on a media screen in SL without ads, and scales the video properly to fit the prim/object/face. It also accepts a list of YouTube video codes as CSV, so you can have a long playlist of videos, and when it reaches the last one, it loops. This script works fine, though obviously the output is different.

FYI I have been coding in LSL for roughly 18 years.

Actually I just double checked the 404 output in SL and it DOES say "ERROR: CANNOT CONNECT TO SERVER" at the top of the output before dumping the HTML for the 404 page, which is why I initially thought there might be something different between MySQL and MariaDB. The config file for the Simple Database Storage PHP script has "$db_host  = 'localhost';" ... I know for a fact the database exists, and since the php script is the one accessing the database, localhost seems like it would be correct.

I will try a few things, add some logging on the server side, add in a break and track down exactly where the failure is, but it seems it is around the point it connects to the database based on the error message. However the LSL code that generates this "CANNOT CONNECT" error message, gives this response for anything other than a status of 200. so it could be connecting, but then redirecting to 404 and that is the status the LSL is getting back?

Thank you for responding with your ideas!

 

[tomm55]

5 hours ago, Ayame Musashi said:

I am a web developer. I am not 100% in PHP, but I know enough to get things done. but I do not normally configure servers, and have not ran into an issue like this before, which is why I was asking. It seems to be more about the server configuration, something I am overlooking. The 404 message was coming back to SL, to the script, and being output instead of the expected output from that basic PHP script. since you have it, you know. So when I saw this output in SL, I first tried calling the PHP script with no URL arguments, and it gave me the "ERROR: NOT AUTHENTICATED". Then I threw in the "secret passphrase" as the only URL argument, and I got "ERROR: INSUFFICIENT ARGUMENTS". so then I just had the LSL script generate it's URL and output it to chat, then took that and fed it to the browser, and got the 404 page for the Wordpress installation that is setup in the same hosting space.  The same thing I was seeing in the body of the http_response returned to SL.

I will try adding some breaks in the code to see how much further it is getting, but I suspect that it is completing, but rather than directing the output back, it's redirecting to 404.

I am not logging anything currently. I guess I can try that as well. I can't see why anything would not get through, as this is a very basic script, both on the PHP side and the LSL side, and I have the exact same set of files setup and working on a shared hosting server, and it is working as expected. I duplicated the setup in a second object in SL and on my new VPS, and ran into this issue. Since I have not installed or administrated a Unix/Linux machine in probably 30 years, my first suspicion is that I have not configured something properly on the server and it is causing this.

I previously created another PHP page on the new server for handling YouTube videos, and it is working fine. It's basically a simple PHP that builds a YouTube iframe embed using the arguments you pass it with the URL, which allows me to play any YouTube video on a media screen in SL without ads, and scales the video properly to fit the prim/object/face. It also accepts a list of YouTube video codes as CSV, so you can have a long playlist of videos, and when it reaches the last one, it loops. This script works fine, though obviously the output is different.

FYI I have been coding in LSL for roughly 18 years.

Actually I just double checked the 404 output in SL and it DOES say "ERROR: CANNOT CONNECT TO SERVER" at the top of the output before dumping the HTML for the 404 page, which is why I initially thought there might be something different between MySQL and MariaDB. The config file for the Simple Database Storage PHP script has "$db_host  = 'localhost';" ... I know for a fact the database exists, and since the php script is the one accessing the database, localhost seems like it would be correct.

I will try a few things, add some logging on the server side, add in a break and track down exactly where the failure is, but it seems it is around the point it connects to the database based on the error message. However the LSL code that generates this "CANNOT CONNECT" error message, gives this response for anything other than a status of 200. so it could be connecting, but then redirecting to 404 and that is the status the LSL is getting back?

Thank you for responding with your ideas!

 

Two ideas come up on my mind:

1. Your PHP file causes some error (e.g. can't connect to DB, or something else) and your server is configured to return a 404 page in that case. Although usually servers return a 500 error (Internal server error) in such case, but who knows, maybe your server is configured in that way.

2. Your PHP script finishes fine without errors or is not executed at all, and there is a problem with your URL query. I have quickly checked the SLDB LSL script and it uses HTTP GET method for communicating with the PHP end. So it put's all data into a URL query. If you are using Wordpress, then maybe you are also using some "URL prettifier". I don't know how that thing is called exactly, but it basically turns e.g. "www.server.com/index.php?some=argument" into "www.server.com/index/some/argument/". So maybe this might be also causing problems...

So, I would first make sure that your PHP script finishes without errors (e.g. by adding those debug outputs). Then I would check the URL that is being requested. In PHP you can add this to the beginning of the PHP file:

echo("The requested URL is: {$_SERVER['REQUEST_URI']}");

In LSL script you can see the URL by outputting the URL argument of llHTTPRequest.

 

Edit: Or maybe it would be better to use "die" instead of "echo":

die("The requested URL is: {$_SERVER['REQUEST_URI']}");

Edited February 8, 2022 by tomm55

[Xiija]

@Ayame Musashi

Is your SL script using http .. or https?  .. if the url you are sending back to SL has https, you may need a cert?

here is a thread on that ...just incase it helps.

Http Certificate

Edited February 9, 2022 by Xiija

[Ayame Musashi]

12 hours ago, Xiija said:

@Ayame Musashi

Is your SL script using http .. or https?  .. if the url you are sending back to SL has http, you may need a cert?

here is a thread on that ...just incase it helps.

Http Certificate

Both the server, and the script, are setup to use HTTPS, as I installed Let's Encrypt and have the server secured with a certificate. I have been managing 60+ client hosting accounts for many years now. We were using cPanel/comodo certificate signing but recently cPanel has had trouble with their certificate renewal (Auto-SSL) and we switched to Let's Encrypt for those as well.

Edit: But all of that is on managed VPS hosting, and this is the first time I have installed and configure the backend of a VPS. Which is why I am leaning towards some configuration I missed. I thought to ask here for obvious reasons. Like possible common issues that I don't know about.

Sorry, i have not had a chance to look at the script and test more yet. I will post something as soon as I do. Busy with work again, and also trying to finish my sim build to open it to the public. This database is for my side project, so it's lower priority.

Thank you for the ideas!

 

Edited February 9, 2022 by Ayame Musashi
clarification :D

[Ayame Musashi]

I have finally had a chance to look at this issue again, and I have it narrowed down but I am still unsure how to resolve the issue. It is obviously a server configuration issue of some sort, and as such is not a SL issue per say. So if anyone has objections to me posting this here, I understand. Again, my main purpose in posting here, is that this project is SL based, and it's possible others have run into the same issue and could shed some light on it, both for my benefit and possibly others in the future.

What I am seeing to be the issue here is this. The database interface on the server resides at https://websiteaddress.com/takeshi/index.php ... and if you simply go to https://websiteaddress.com/takeshi/ then it loads the index.php, even though there are multiple files in this directly. This is default behavior for web servers, and something that the SLDB kit relies on. Because the way the SLDB kit is setup, it makes a call to the server, including the type of database request in the path, and expects the interface to pickup on this and use it. So the call to the interface is for example:

https://websiteaddress.com/takeshi/read/uuid?secret="authenticationcode"&fields[]=name&fields[]=size&separators[]=&&separators[]=&

So on my old web host, this works. the index.php located at https://websiteaddress.com/takeshi/ is called, and it reads the URL path, cuts the "read" from the path to know what it is supposed to be doing with the database, cuts the rest of the arguments from the path, and successfully returns the name of the user and their size which were previously stored in the database.

So on my new VPS, this does not work. the server appears to think I am trying to access something deeper at https://websiteaddress.com/takeshi/read/ and this is where it ends up redirecting to a 404 page. I am guessing the easy solution here is to modify everything to move the database function out of the path and into an argument, though I am not sure why it works on my old server, and not on the new VPS. This is how the SLDB kit was designed to work and has worked for me on more than one server, but does not work on the VPS I installed everything on, leading me to believe it is something I need to change in the VPS configuration.

Anyway, that is the status of this issue, for anyone who was following along and curious. If you have any recommendations for resolving this issue, or why it is behaving in this way, please do let me know. I would rather correct what I am doing wrong on my VPS, than make changes to code that works everywhere else.

And again, thank you for the ideas!

 

[Ayame Musashi]

After a LOT of testing and digging, I finally found the issue. So for anyone else who might end up in the same situation. I am including the final piece of the puzzle and ending this thread.

I setup my VPS with a LEMP stack (Linux, NGINX, MariaDB, PHP) instead of a LAMP stack (Linux, Apache, MySQL, PHP) as I read that the NGINX server was more efficient and since I am doing stuff on a little $15/year VPS, keeping resource use to a minimum is important. The fact that I went with NGINX for my web server instead of Apache, means... that the .htaccess files that come with this SLDB kit, are completely useless, as NGINX does not use .htaccess files to override URL direction.

After making several changes to the .htaccess file to see if I could resolve the issue, I realized it was having 0 effect, and then did more research to find the above revelation. Then I looked up how NGINX handles situations where Apache would use .htaccess files. I found the equivalent configuration is directly in the NGINX Config file for each hosting space. I then found a good tutorial on configuring this kind of setup.

https://serversforhackers.com/c/nginx-php-in-subdirectory

One thing to note, I did not go with the separate top level folder for the SLDB app, as is shown in that tutorial, because it would be a hassle to access this folder via FTP for updating project files, without setting up a new FTP account, and having to toggle between them while working on the site. So I went ahead and nested the folder as a sub folder under the website hosting folder and it worked just fine. I simply had to make a couple edits to the NGINX config file to correct the path.

So in the end, it is now working properly, and loads the index.php at https://websiteaddress.com/takeshi/ even though the URL call to the database includes what appears to be a sub folder lower, but is in fact a directive to the SLDB to know what function you are invoking.

Example: https://websiteaddress.com/takeshi/read/uuid?secret="authenticationcode"&fields[]=name&fields[]=size&separators[]=&&separators[]=&

So the /read/uuid... etc in that line gets passed to websiteaddress.com/takeshi/index.php and that file parses the string and uses "read" as the function it should perform, and then uses the remaining values to process the request.

TL;DR: It WORKS! And now I have a working connection between SL and my database and can start expanding on my NPC's AI, storing information, and "memories" in the database.

I will look into ways of making this connection more secure, but presently this is not an issue for my purposes.

Thank you all again for your input and trying to help me resolve this issue. I really appreciate you taking the time to help.

~Aya