MySQL- combinie two queries

I can't solve this problem:

I have mysql DB with many geo-coordinates.

I liked to know which points are within a specific number of miles from specific coordinates AND Which are within specific square.

Points within a specific area:

(SELECT *, 'area' as type, ( 3959 * acos( cos( radians(".$Plat.") ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(".$Plng.") ) + sin( radians(".$lat.") ) * sin( radians( lat ) ) ) ) AS distance FROM ".$db." HAVING distance < ".$radius."

points within a specific square:

SELECT *, 'square' as type, -1 AS distance FROM ".$db." WHERE ((Alat <= ".$Alat." AND ".$Alat." <= Blat) OR (Alat >= ".$Alat." AND ".$Alat." >= Blat)) AND ((Alng <= ".$Alng." AND ".$Alng." <= Blng) OR (Alng >= ".$Alng." AND ".$Alng." >= Blng)) ...

I use UNION to combine both queries but the thing is "type" and "distance" have different values. So I get the searched point twice. I liked to have every point just once.

But I have no idea how to handle that.

Edit:

For an overview I took just some of the data.

As result I get something like that:

+------+-----------+-----------+----------+-------------+| id | Alat | Alng | distance | type |+------+-----------+-----------+----------+-------------+| 42 | 53.704678 | 10.202164 | 12345 | area +------+-----------+-----------+------------------------+| 72 | 23.704678 | 15.202164 | 12345 | area+------+-----------+-----------+------------------------+ ......+------+-----------+-----------+------------------------+| 42 | 53.704678 | 10.202164 | -1 | square+------+-----------+-----------+------------------------+| 81 | 43.778 | 15.201212 | -1 | square+------+-----------+-----------+------------------------+ ......id(72) exists just in the area an not in the square. id(81) exists just in the square an not in the area.id(42) is in the area and in the square too. So it appears two times. But it only should once. The area should get priority.

but it just should be

+------+-----------+-----------+----------+-------------+| id | Alat | Alng | distance | type |+------+-----------+-----------+----------+-------------+| 42 | 53.704678 | 10.202164 | 12345 | area +------+-----------+-----------+------------------------+| 72 | 23.704678 | 15.202164 | 12345 | area+------+-----------+-----------+------------------------+ ......+------+-----------+-----------+------------------------+| 81 | 43.778 | 15.201212 | -1 | square+------+-----------+-----------+------------------------+ ......

View the original article here

MySQL Proxy Error: Cannot Load Module "mysql.password"

I am trying to test out a MySQL Proxy Script to handle authentication. And I keep getting errror

2013-12-25 00:25:31: (critical) (lua-error) [E:\Downloads\MySQL\proxy\script.txt][string "E:\Downloads\MySQL\proxy\script.txt"]:2: module 'mysql.password' not found: no field package.preload['mysql.password'] no file 'E:\Downloads\MySQL\proxy\lib\mysql-proxy\lua\mysql\password.lua' no file 'E:\Downloads\MySQL\proxy\bin\lua-mysql\password.dll' no module 'mysql.password' in file 'E:\Downloads\MySQL\proxy\bin\lua-mysql.dll'

Following is my code. By the looks of the message I am missing some files. But I could not find any help by searching. Any help will be appreciated.

local proto = assert(require("mysql.proto"))local password = assert(require("mysql.password"))function read_auth() print(" username : " .. proxy.connection.client.username) print(" password : " .. string.format("%q", proxy.connection.client.scrambled_password)) local c = proxy.connection.client local s = proxy.connection.server if c.username == "test" and -- the username we want to map password.check( s.scramble_buffer, c.scrambled_password, password.hash(password.hash("test")) -- its valid password ) then proxy.queries:append(1, proto.to_response_packet({ username = "root", response = password.scramble(s.scramble_buffer, password.hash("admin")), charset = 8, -- default charset database = c.default_db, max_packet_size = 1 * 1024 * 1024 }) ) return proxy.PROXY_SEND_QUERY -- works if you use lp:mysql-proxy r694 or later endend

View the original article here

Optimising MySQL Database Indexes InnoDB

I have been Googleling for hours now and I haven't quite figured out how indexes work, because there are so many mixed answers available.

Let's say I have an query 1:

SELECT subid, subid2 FROM clicks WHERE wid=100 AND uid=123 AND time >= 1387886885

And a query 2:

SELECT subid, subid2 FROM clicks WHERE fid=100 AND time >= 1387886885 AND uid=123

1) How should I place indexes here? Should I use multi column index or single column indexes?

2) I found an answer on stackoverflow that suggested adding indexes to subid and subid2 also, should I do that?

View the original article here

Why JDBC cant connect to local Mysql Server

I have a machine with CentOS and mysql server on it (let's call it 'Server'). And another(my desktop) machine with Debian (let's call it 'Desktop'). I tried to launch java program on Desktop machine. Application connects to Server's mysql database without any troubles.

When I finished development of this application (it's still works and connects to DB well) I tried to deploy this app to Server machine. I'v built it the app on the server and tried to launch, using database, which still hosted on Server machine. So now it seems to be local database for my application. But it fails during connection to DB using Server IP address or "localhost" or 127.0.0.1. I have only:

java.sql.SQLException: null, message from server: "Host '82.192.90.179' is not allowed to connect to this MySQL server".

In my.cnf file on Server I've set "bind-address=0.0.0.0".

PS:The most interesting thing, I do everything on server using ssh, and if I try to connect to database with "mysql" console tool, it connects OK either with -h82.192.90.179 and without -h option(seems to be localhost as a dafault)

View the original article here

mysql query and encoding it in json format

I'm using this simple php code to encode the MySql query result in json format. But Don't know why it is not giving me the desirable output. I'm actually trying to get the employee details by entering their 'employee_number'.

0) { $result = mysql_fetch_array($result); echo("Success !! Yoo"); $employee = array(); $employee["employee_number"] = $result["employee_number"]; $employee["employee_name"] = $result["employee_name"]; $employee["flag"]=$result["flag"]; // success $response["success"] = 1; // user node $response["employee"] = array(); array_push($response["employee"], $employee); // echoing JSON response echo json_encode($response); } else { // no employee found $response["success"] = 0; $response["message"] = "No employee found"; // echo no users JSON echo json_encode($response); } }else { // no employee found $response["success"] = 0; $response["message"] = "No employee found"; // echo no users JSON echo json_encode($response);}/*} else {// required field is missing$response["success"] = 0;$response["message"] = "Required field(s) is missing";*/// echoing JSON responseecho json_encode($response);?>

View the original article here