Connect/ Join Query Multiple dbs Multiple Servers

[fuzzy1]

Hey all,

Struggling to connect simultaneously to remotehost@my_ip_address
AND localhost@my_field_db FROM my_field_tablet_pc to affect
a custom sycnchronization that SQLyog will not allow.

Both remotehost and localhost are built on WAMP stacks.

First, the remote connection & queries

With 'mysql_connect.php' on remotehost AS

<?php
define ('DB_USER', 'root');
define ('DB_PASSWORD', '');
define ('DB_HOST', 'localhost');
define ('DB_NAME', 'crm');
$dbc1 = mysql_connect (DB_HOST, DB_USER, DB_PASSWORD);
if (!$dbc1) {
    die('Could not connect: ' . mysql_error());
}
else {echo "Houston We Have a Connection <br>";}

$db_selected = mysql_select_db('crm', $dbc1);
if (!$db_selected) {
    die ('Can\'t use dbc1 : ' . mysql_error());
}
 ?>

AND index.php on localhost AS

<?php

if (isset($_POST['submit'])) { // Handle the form.
	//require_once ('mysql_connect.php'); // Connect to the local-secondary db.
	require_once ('http://my_ip_address/MySync/mysql_connect.php'); // Connect to remote-primary db.

$crmNote = " SELECT * FROM crm.notes WHERE id='d50dd78c-92da-2238-f69a-463c806fccba' ";
$result = @mysql_query ($crmNote)or die ("Select Query Failed " .mysql_error());
	while($row = mysql_fetch_array($result))
	  {
	  echo $row['id'] . "<br>" . $row['name'];
	  echo "<br />";
	  }
    }
 ?>

Echo's

Houston We Have a Connection
Select Query Failed Acces denied for user 'ODBC'@'localhost' (using password: NO)

How is that I get both a connectiion AND Access denied ????
Need I specifiy the link_identifier in the query?

[Sxooter]

The user you're trying to run the select as doesn't have permission to access that table. You're connected, you just don't have permission to do the select there.

[fuzzy1]

Thanks Sxooter, but IMHO that's not the problem.

1. root user has ALL priveleges,

2. if I enter another user with ALL priveleges AND password
   I still get connection, AND Acces denied for user 'ODBC'@'localhost' (using password: NO)

3. If I include the query in the remotehost mysql_connect.php the query results are returned as expected

so ... must be something else???

[Sxooter]

fuzzy1 wrote:

Thanks Sxooter, but IMHO that's not the problem.

1. root user has ALL priveleges,

2. if I enter another user with ALL priveleges AND password
   I still get connection, AND Acces denied for user 'ODBC'@'localhost' (using password: NO)

3. If I include the query in the remotehost mysql_connect.php the query results are returned as expected

so ... must be something else???

But that right there says you're connected as user ODBC, not root. So you're not connecting as who you think you are I think.

[fuzzy1]

Okay... I stand corrected ... sort of....
Setting User & Pass to "" in remotehost mysql_connect.php...

<?php 
define ('DB_USER', ''); 
define ('DB_PASSWORD', ''); 
define ('DB_HOST', 'localhost'); 
define ('DB_NAME', 'crm'); 
$dbc1 = mysql_connect (DB_HOST, DB_USER, DB_PASSWORD); 
if (!$dbc1) { 
    die('Could not connect: ' . mysql_error()); 
} 
else {echo "Houston We Have a Connection <br>";} 

$db_selected = mysql_select_db('crm', $dbc1); 
if (!$db_selected) { 
    die ('Can\'t use dbc1 : ' . mysql_error()); 
} 
?> 

returns

Warning: mysql_connect() [function.mysql-connect]:
Access denied for user 'ODBC'@'localhost' (using password: NO)
in C:\AreaPro\wamp\www\MySync\mysql_connect.php on line 6
Unable to Conntect to ServerAccess denied for user 'ODBC'@'localhost' (using password: NO)
Select Query Failed Access denied for user 'ODBC'@'localhost' (using password: NO)

so... Hmmmmm?

[Sxooter]

Where the heck is ODBC coming from?

Can you write a simple one page script with a mysql_connect with the args right in it? like

$conn = mysql_connect('host','user','pwd');

and see if that works?

[fuzzy1]

Well, yes... and no...

If I run localhost/server/mysql_connect.php AS

<?php
$conn = mysql_connect('my_ip_address','root','');
?>

I get

Warning: mysql_connect() [function.mysql-connect]:
Can't connect to MySQL server on 'my_ip_address' (10061)
in C:\AreaPro\wamp\www\MySync\mysql_connect.php on line 2

if called from index.php AS

if (isset($_POST['submit'])) { // Handle the form.
// Connect to remote DB from mysql_connect.php on localhost.
	require_once ('mysql_connect.php'); //	vs. 
              //require_once ('http://**my_ip_address**]/MySync/mysql_connect.php'); 
                 // Connect to remotehost db calling remotehost/mysql_connect.php 

followed by my test query I also get...

Select failed - from primary crmAccess denied for user 'ODBC'@'localhost' (using password: NO)

Only by using

require_once ('http://**my_ip_address**]/MySync/mysql_connect.php');

... Only in this way have I been able to get any indication of a connection WHICH as stated above -- if
the test query is included within remotehost/mysql_connect.php -- results ARE returned into localhost/index.php.parent !

ALL THIS is a desperate attempt to circumvent the need for SSH tunneling --
which does NOT look fun though I fear is what is needed --

THAT as a workaround for shortcomings of SQLyogEnterprise
-- which uses it's own tunneling AND requires a workaround or bridge script
AND an additional mirror local database on each of our field tablets....

Of course the field tablets only enjoy intermittent connectivity at best, and so need frequent syncronizateion...
which I am DESPERATE to automate as long lapses between Synchronizations have proven disastrous
and we can't rely on the users (home inspectors) to be diligent enough to perform a three step synchronization
process once per day, let alone once per hour so .

I guess what I'm hoping hear is that you or anyone might step in and reveal that
my search for an answer to the necessities of frequent and automated synchronization has
taken me far afield, or possibly be willing and able to point me down the path to establishing
ssh tunneling between two wamp servers--which is effectively what our install of SQLyog uses.

Many Thanks

[Sxooter]

Yeah, I hope someone else knows about tunneling between two WAMP boxes, cause I really don't. If it was linux I'd stand a chance to help out. Sorry. I do think something's happening that's making your connections happen as a user other than the one you're specifying, but not a clue what it is.

I'd post a question with a subject more closely matching this problem and see if anyone bites. good luck!