Question about mySQL running on a remote server not returning all data

[tkuypers]

Hi all,

I have a problem I can't seem to solve...

I'm running a mySQL server locally on my Mac, version 5 7 23. When I try to retrieve using a direct link from LiveCode Community 9 6 3, everything works perfectly.
Remote my provider switched to version 10 6 23 and there's a "small" issue.

When using a php page to extract the data, everything comes in, a little slow, but all data is there.
But when using the same direct option from LiveCode, only a part of the data comes in. Blazing fast, but the data stops after a "random" amount of chars...
The first query direct gives 5 523 chars, php returns 5 472 chars
The second query via a direct livecode query retrieves 419 530 chars, the PHP pages returns 2 664 296 chars
And a third query returns 1355 characters direct and 10 231 through php...

I'm getting crazy, been working with the tech-guys at the hosting-company for days now, without getting a result.
They even turned off the firewall (for 5 minutes) to see if something was being blocked there, but no difference.

Anybody a suggestion?

[Klaus]

One thread per problem is sufficient!
Will delete the post in "Experienced developers"!

[stam]

The fact that it runs fine on your personal system suggests it's the 'middleware' that's the issue.

I presume you are just accessing MySQL at 127.0.0.1 locally but obviously can't do that from remote, and hence some kind of middleware (PHP or LC server/app) will usually reside on the remote server, unless of course you have white-listed your specific IP. You mention PHP so I presume this is running on the server and accessing MySQL via 127.0.0.1 on the remote.

How is your LC app connecting? Could that middleware be your issue?

Failing that, perhaps post your LC code that fails?

S.

[tkuypers]

let me clarify:

I'm running a mySQL server locally on my Mac, version 5 7 23. When I try to retrieve using a direct link from LiveCode Community 9 6 3, everything works perfectly.
Remote my provider switched to version Maria-db 10 6 23 and there's a "small" issue.

When using a php page to extract the data, everything comes in, a little slow, but all data is there.
But when using the same direct option from LiveCode, only a part of the data comes in. Blazing fast, but the data stops after a "random" amount of chars...
The first query direct gives 5523 chars, php returns 5472 chars
The second query via a direct livecode query retrieves 419530 chars, the PHP pages returns 2664296 chars
And a third query returns 1355 characters direct and 10231 through php...

So I upgraded to Maria-DB on my Mac: same problem as remote
Removed this installation and installed the latest version of MySQL: cannot use this version, because the mysql_native_password does no longer exist, so I can’t login anymore.
So I removed that version as well and went back to MySql 8 0 42. Login works again, but als the problem with the chopped off data is back, same as the remote Maria-DB version.

I need a working remote database, preferably the latest Maria-DB version as installed (it is an OpenCart website whose database I use for a locally operated cash register and invoicing system), but I need to be able to get all the data, using the direct connection commands in LiveCode… PHP work fine, but is is slowwwwwwwwww

[stam]

Connecting to a hosted MySQL database is tricky because a direct connection, such s what you would do from your development machine, is discouraged for security reasons.

I believe it is possible, if discouraged, to whitelist specific IP addresses, but if your IP changes that all breaks.

The usual recommendation is that you use a middleware layer (typically PHP or in our cases, LC Server) that can be accessed freely from the internet, but it then securely communicates with the database server and returns data back to you.

Or at least that’s my understanding of how things should work.

Here is an example od how to use LC Server to remotely connect to a MySQL database server:
https://www.hostm.com/tutorials/livecod ... iadb-mysql

There is a similar process for PHP which is more universally available server-side. But some services, such as hostm, will install LC server upon request and the old, free, community server v9.6.3 would suffice.

If you are set up with PHP to access your database, and your database has changed, chances are it’s your PHP service needs updating.