MySQL : [1] are synchronous queries possible ? [2] Error thrown when empty varchar returned

[nouknouk]

Hi,

I'm currently trying to use MySQL from server side Javascript
framework and I face two issues:

1/ the MySQL binding works in an asynchronous way. But this can be
annoying if you want for example to make a MySQL request into a
command code : as MySQL query is asynchronous, you cannot directly
send a 'command response' to the command sent by the client.

Is there any way to force the use of MySQL binding in an synchronous
way and thus avoid the need to send a 'command response' immediatly,
plus a second raw just after which contains the result of my query.

2/ When i run a query returning at least one field with an empty
varchar, I get an error: " ERROR (17) : Mysql binary mode field
corrupt ".

Notes:

- my APE server is a v1.00
- my MySQL database (v5.1.41-3) is running on the same host as the APE
server
- DB is using "utf8_general_ci" as charset (?)
- the field leading to the error is of type varchar(150), not null but
containing an empty string
- when the same field contains a non-empty string, everything behaves
well.

Any idea ?
Many thanks in advance.

Nouk²

[nouknouk]

Nobody ?

[Cadrach]

For your first question, what you could do is, inside your event or
command on the server:

var sqlFinished = false;
var sqlResult = null;
sql.query(statement, function(result){
sqlResult = result;
sqlFinished = true;
})

while(sqlFinished === false)
{
//waiting loop
}
//now work on sqlResult

Ugly but could work. However I would advise against going synchronous,
in my experience js is made to work asynchronously, and every time I
tried to went against that it ended up badly :)

For your second question I did not have the issue myself yet, so
cannot really help, sorry.

Cadrach

On Jan 15, 11:46 am, nouknouk <noukn...@gmail.com> wrote:
> Nobody ?

[Hugo Vacher]

Hey guys,

Going synchoneously on aAPE server will just stop everithing else until sql request ends, you'll exeriment lags on all other responses and messages.

if you use APE Server only for this raw, it's cool, but if you have other concurent raws, they will just stop until you loop is finished.

Have a nice day
---------------------------------------------------------------------------------------------

Hugo Vacher, Développeur Web

2010/1/15 Cadrach <cad...@gmail.com>

--
You received this message because you are subscribed to the Google
Groups "APE Project" group.
To post to this group, send email to ape-p...@googlegroups.com
To unsubscribe from this group, send email to
ape-project...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/ape-project?hl=en
---
APE Project (Ajax Push Engine)
Official website : http://www.ape-project.org/
Git Hub : http://github.com/APE-Project/

[Nouk NOUK]

Hi,

first, thanks for your answers.

Going synchoneously on aAPE server will just stop everithing else until sql request ends, you'll exeriment lags on all other responses and messages.

Yes, I'm aware of that, but if the query is simple, the DB server runs on the same host, and the 'real time' needs for your APE application are not too much constrained (ie. supporting a few dozen of milliseconds lag), it could be a good option.

 

For your first question, what you could do is, inside your event or
command on the server:

var sqlFinished = false;
var sqlResult = null;
sql.query(statement, function(result){
sqlResult = result;
sqlFinished = true;
})

while(sqlFinished === false)
{
//waiting loop
}
//now work on sqlResult

I already tried something similar ('active wait') but it leads to a complete freeze of the whole APE server.

I suspect the thread doing the 'read' on the 'connection to DB socket' and the javascript interpreter's thread being the same.

Ugly but could work. However I would advise against going synchronous,
in my experience js is made to work asynchronously, and every time I
tried to went against that it ended up badly :)

I think this can be suitable for simple needs. It you look at PHP for example, the 'synchronous' paradigm is used for every call made to MySQL DB.

Ideally, it could be really interresting to have both approach available (synchronous and asynchronous) ; the developper would have then the choice and could use the one that fits it needs.
 

For your second question I did not have the issue myself yet, so
cannot really help, sorry.

I finally found a workaround using the MySQL 'CASE' statement.
In my program, the field 'username' is the one that may contain empty strings. Here is my code to avoid the issue:

var myQuery = "
SELECT
    CASE (username)
        WHEN '' then 'guest'
        ELSE username
    END
    AS theUsername,
    session_id,
    user_id
FROM mytable_user_sessions
WHERE
    session_id = '"+sessionId+'
";


Best regards,

Nouk²

[flipkick]

you don't want to use synchronous requests because this will block your
whole module with all users until the sql for one user has returned.
and in the idea of cadrach for instance:

while(sqlFinished === false)
{
//waiting loop
}

is horrible, this could use up to 100% cpu only for one user, able to
slow down your whole server just for one single SQL request. there's a
reason why there is no sleep-function in javascript.

get used to asynchronous handling. send the cmd result to the client
when it is available and make the client work with this. in my
rails+ape+mysql-example you'll find a way how you could this, take a
look at
it:http://flip.netzbeben.de/2010/01/howto-create-a-web-chat-with-ape-rails-and-mysql/

flip

[Cadrach]

Maybe what we should have asked is: Why having the SQL request
processed and the result sent on another raw is an issue for you?

Cadrach

On Jan 15, 3:41 pm, flipkick <flipk...@gmail.com> wrote:
> you don't want to use synchronous requests because this will block your
> whole module with all users until the sql for one user has returned.
> and in the idea of cadrach for instance:
>
> while(sqlFinished === false)
> {
> //waiting loop
>
> }
>
> is horrible, this could use up to 100% cpu only for one user, able to
> slow down your whole server just for one single SQL request. there's a
> reason why there is no sleep-function in javascript.
>
> get used to asynchronous handling. send the cmd result to the client
> when it is available and make the client work with this. in my
> rails+ape+mysql-example you'll find a way how you could this, take a
> look at

> it:http://flip.netzbeben.de/2010/01/howto-create-a-web-chat-with-ape-rai...

[Anthony Catel]

Just for information :

You can delay any command using "return -2" :

Ape.registerCmd("foo", true, function(params, cmd) {
example_async(function() {
cmd.sendReponse("ASYNC_REPLY", {"foo":"bar"}); // (or
cmd.user.pipe.sendRaw)
});

return -2.
});

Le 15/01/10 16:33, Cadrach a �crit :

[Anthony Catel]

(only on github repository, not in 1.0 release)

Le 15/01/10 16:53, Anthony Catel a �crit :

[flipkick]

awesome, thanks for information.

[Nouk NOUK]

2010/1/15 flipkick <flip...@gmail.com>

you don't want to use synchronous requests because this will block your whole module with all users until the sql for one user has returned.
and in the idea of cadrach for instance:

And as said before, it's not an issue for my own project's constraints.
 

is horrible, this could use up to 100% cpu only for one user, able to slow down your whole server just for one single SQL request.

That's why I think having two APIs to access MySQL (one synchronous, one asynchronous) would be definitely the best solution.

 

there's a reason why there is no sleep-function in javascript.

Because historically, Javascript was only designed for browser needs ; we are not in the same context here.

 

get used to asynchronous handling.

Being used to develop networked apps, that's not the problem here.

2010/1/15 Cadrach <cad...@gmail.com>

Maybe what we should have asked is: Why having the SQL request
processed and the result sent on another raw is an issue for you?

 

Because in my own project, saving bandwidth is more important than responsiveness for the whole server;

Note there could be lot's of other reasons, depending of the particular needs of the project you're working on. For example, responsiveness for the user that sent the command itself would be better with a synchronous scheme (in case of 'long polling transport mode', closing the TCP connexion and waiting for the next one before sending the query result implies lot more latency, ...)
 

2010/1/15 Anthony Catel <a.c...@weelya.com>

Just for information :

You can delay any command using "return -2" :

Many thanks, this is the tip I was waiting for :-)


Best regards,

nouk²

[flipkick]

Am 15.01.2010 19:21, schrieb Nouk NOUK:

there's a reason why there is no sleep-function in javascript.

Because historically, Javascript was only designed for browser needs ; we are not in the same context here.

that's not what i meant, using sleep in a browser app makes no sense just as in your javascript server side ape module.

 

get used to asynchronous handling.

Being used to develop networked apps, that's not the problem here.

2010/1/15 Anthony Catel <a.c...@weelya.com>

You can delay any command using "return -2" :

Many thanks, this is the tip I was waiting for :-)

lol, pretty contradictory, you don't want to use the asynchronous model but say thanks for the tip do to use it.

flip

[Nouk NOUK]

2010/1/15 flipkick <flip...@gmail.com>

that's not what i meant, using sleep in a browser app makes no sense just as in your javascript server side ape module.

sleep is a very common function available in most of programmting languages. sleep has never been a 'non sense', and there's not reason for Javascript to be an exception (but that's another debate). In the current context, it would have been usefull as the best workaround to do 'active wait' without burning CPU resources.

 

lol, pretty contradictory, you don't want to use the asynchronous model but say thanks for the tip do to use it.

Not at all. As said before, my goal has never been to avoid an asynchronous scheme, but to be able to send a command's response filled with my DB data.



Anyway, thanks again Anthony for your concrete help.


Nouk²

[flipkick]

2010/1/15 flipkick <flip...@gmail.com>

that's not what i meant, using sleep in a browser app makes no sense just as in your javascript server side ape module.

sleep is a very common function available in most of programmting languages. sleep has never been a 'non sense', and there's not reason for Javascript to be an exception (but that's another debate). In the current context, it would have been usefull as the best workaround to do 'active wait' without burning CPU resources.

oh a professional, lol.. go get yourself some books ;) sleep is senseless in this context because it will block anything! it won't help that you just say "no", lol. you already found out earlier by try and error that it blocked your ape.

lol, pretty contradictory, you don't want to use the asynchronous model but say thanks for the tip do to use it.

Not at all. As said before, my goal has never been to avoid an asynchronous scheme, but to be able to send a command's response filled with my DB data.

in your mail you said "Is there any way to force the use of MySQL binding in an synchronous". so just saying "not at all" also won't let this contradictions go away. it's not a problem if you don't know better, but pretending to be a professional, this is just awkward. but keep trying and thanks for the entertainment.

flip

[Nouk NOUK]

You know flipkick, my little SQL request is typically performed in less than 150 microseconds on my linux server. That means an active wait would have frozen my whole APE server for something like the same time. Huuuge !

Compare it to the typical time needed to execute most of your javascript functions and you'll then understand that such time is just nothing. Even compare it to the typical time slice of a linux scheduler. Or compare it to the execution time of your asynchronous solution, where you need to handle the one callback function and one supplementary TCP connection because you sent the data in a second RAW.

You'll probably understand that in my case, an 'active wait' with a while loop and a sleep(0) call - or event better, a yiel() call - would have been more efficient than the asynchronous solution, even in CPU load.

You were right one one point: I made too quickly the assumption that the command's response had to be returned at the end of the command callback function, that's why I was thinking about a synchronous solution.

In any case, the synchronous solution is not an option here, as the APE JSF (likely mono-threaded) and the javascript language don't allow it (at least for the moment). As my goal is simply to send the response directly without the need of a second RAW, the solution provided by Anthony is the best one so far.
It actually fits my needs, at least enough to let me stop wasting my time on this part of my application.

For the rest let's keep reading books and blindly repeat what you have readen, no matter if you don't think by yourself and/or try to adapt you knowledge to the concrete context explained by the initiator of the thread. It is well known that there is always only one solution to a given problem as well as the world is only black and white, etc...

I'm convinced that flaming in discussion groups is the best way to prove your 'profesionnal' attitude and skills, and surely will let you make lot's of friends, all ready to provide you help the next time you'll need some.


Best regards,

Nouk²


[end of discussion for me]

[Anthony Catel]

Keep cool guys :)

Indeed, we are neither in a multi thread context nor in a single user application, so it's not logic to add a "sleep()" call or similar.
APE is fully mono threaded and only works with async API (non blocking socket, events API (epoll, kqueue), and so forth).
Therefore the JS API must be asynchronous as well.

You know flipkick, my little SQL request is typically performed in less than 150 microseconds on my linux server. That means an active wait would have frozen my whole APE server for something like the same time. Huuuge !

Even if your SQL query is very fast, I can't agree. You can't trust your MySQL server. What if you have to handle 1M entries? Imagine that your query take 100ms, every users will be affected by this latencity.
It's definitly not the good way to write scalable applications ;-)

Anthony