Linked server

is there a way to create linked server between 2 mysql servers like in SQL SERVER

Posted On: Monday 29th of October 2012 05:45:06 AM Total Views:  493
View Complete with Replies

Related Messages:

Displaying Time values from a linked MySQL table in MS Access   (165 Views)
When I link a MySQL table containing a TIME column to Microsoft Access the value does not display. If I edit the value it updates correctly in mysql but if I refresh the Access datasheet view the value disappears and the column shows as blank. Can anyone tell me how to display time values in Access. I am using MySQL ODBC driver 3.51, MySQL 4, Access 2002 Thanx, Rob!
Sql join (?) selecting from multiple linked tables   (234 Views)
Hi , I am trying to select from these tables: (seems I am not allowed to post links or images, so if you could be so kind if checking out the db schema at, it would be easier to see what I am looking for) I'm trying to write a query that, given a arbitrary id_tabla_enum, would answer back with: cf_cuadrante.id_fila, cf_cuadrante.id_colu, cf_cuadrante.valor, cf_fila.nombre, cf_fila.posicion cf_colu.nombre, cf_colu.posicion, I wont post the queries I am testing, because they are so bad it's not even funny. Hopefully the case is somewhat clear from what I posted and someone can lend a hand, at least cluing me in in what part of the FM should I be consulting.
Search across linked tables   (240 Views)
G'day all, After everybody help I managed to get my main table accessing keys from several other tables. Now comes the hard part ... trying to get data out from a search I have the following SELECT statement which will work when one of the FROM's comes into play but one I have it run 2+ it causes a ERROR 28 on the server (takes about 30/60 seconds) I am assuming that I have stuffed something and it is trying to grab way too much data Code: SELECT V.`vid`, V.`name`, V.`add1`, V.`add2`, V.`suburb`, V.`state`, V.`postcode`, V.`phone`, V.`fax`, V.`openhours`, V.`email`, V.`www`, V.`microimage`, L.`location`, C1.`cuisine` AS `cuisine1`, C2.`cuisine` AS `cuisine2`, S.`style` FROM `venue` AS V, `L_Location` AS L, `L_Style` AS S, `L_Cuisine` AS C1, `L_Cuisine` AS C2 WHERE V.`name` LIKE '%$search%' OR V.`suburb` LIKE '%$search%' OR V.`postcode` LIKE '%$search%' OR L.`location` LIKE '%$search%' OR S.`style` LIKE '%$search%' OR C1.`cuisine` LIKE '%$search%' OR C2.`cuisine` LIKE '%$search%' AND V.`ftype1` = C1.`lcid` AND V.`ftype2` = C2.`lcid` AND V.`location` = L.`llid` AND V.`style` = S.`lsid` ORDER BY V.`name` ASC What I am trying to do as you have probably guessed is search my database. Now I have read that FULLTEXT is the way to go but everythign I have read says that the fields for FULLTEXT searching need to be text - I can't do that (I think) because some of my feilds are keys
Using a left join with 2 values linked to the same tables   (258 Views)
, I have a table that has two values, author_id and approver_id. Both of these refer to the value id in my users table. Is it possible to use a left join and have the query get the author and approver name (also in the users table) Or will I have to use multiple queries (Can't use nested selects, this particular server is still running MySQL3 -sigh-).
Can't connect to local MySQL server through socket '/tmp/mysql.sock' (46)   (334 Views)
Can't connect to local MySQL server through socket '/tmp/mysql.sock' (46) I get this error when I try to communicate with a table I made using a text file (last posted item). Leoj :: pass => for the table realestate... User-----Host---------Type------------Privileges----------Grant leoj------localhost-----wildcard: %-----ALL PRIVILEGES-------Yes yroot----localhost-----global-----------ALL PRIVILEGES-------Yes conn.php: PHP Code: realestate.sql: PHP Code: --phpMyAdminSQLDump--version2.6.1-rc1--,2005at01:35AM--Serverversion:4.0.22--PHPVersion:4.3.10----Database:`vangon77_dcb`----------------------------------------------------------------Tablestructurefortable`re_admin`--CREATETABLE`re_admin`( `AdminID`varchar(50)NOTNULLdefault'', `AdminPass`varchar(32)NOTNULLdefault'', `AdminName`varchar(100)NOTNULLdefault'', `AdminEmail`varchar(150)NOTNULLdefault'', PRIMARYKEY (`AdminID`))TYPE=MyISAM;----Dumpingdatafortable`re_admin`--INSERTINTO`re_admin`VALUES('test','test','YourName','');--------------------------------------------------------------Tablestructurefortable`re_agents`--CREATETABLE`re_agents`( `AgentID`int(10)NOTNULLauto_increment, `username`varchar(50)NOTNULLdefault'', `password`varchar(32)NOTNULLdefault'', `FirstName`varchar(150)NOTNULLdefault'', `LastName`varchar(150)NOTNULLdefault'', `resume`textNOTNULL, `phone`varchar(50)NOTNULLdefault'', `cellular`varchar(50)NOTNULLdefault'', `pager`varchar(50)NOTNULLdefault'', `ResumeImages`textNOTNULL, `email`varchar(150)NOTNULLdefault'', `logo`varchar(255)NOTNULLdefault'', `RegDate`int(10)NOTNULLdefault'0', `ExpDate`int(10)NOTNULLdefault'0', `AccountStatus`varchar(20)NOTNULLdefault'pending', `PriorityLevel`int(1)NOTNULLdefault'0', `offers`int(3)NOTNULLdefault'0', `news`char(1)NOTNULLdefault'y', `NewsletterType`varchar(10)NOTNULLdefault'plain', `days10`char(1)NOTNULLdefault'n', `days5`char(1)NOTNULLdefault'n', `days1`char(1)NOTNULLdefault'n', PRIMARYKEY (`AgentID`), UNIQUEKEY`username`(`username`), UNIQUEKEY`email`(`email`))TYPE=MyISAMAUTO_INCREMENT=1;----Dumpingdatafortable`re_agents`----------------------------------------------------------------Tablestructurefortable`re_banners`--CREATETABLE`re_banners`( `ClientID`int(10)NOTNULLdefault'0', `BannerID`int(10)NOTNULLauto_increment, `BannerURL`varchar(255)NOTNULLdefault'', `BannerFile`varchar(255)NOTNULLdefault'', `BannerAlt`varchar(255)NOTNULLdefault'', `BannerType`varchar(50)NOTNULLdefault'', `bCat`int(10)NOTNULLdefault'0', `bSub`int(10)NOTNULLdefault'0', PRIMARYKEY (`BannerID`))TYPE=MyISAMAUTO_INCREMENT=1;----Dumpingdatafortable`re_banners`----------------------------------------------------------------Tablestructurefortable`re_categories`--CREATETABLE`re_categories`( `CategoryID`int(10)NOTNULLauto_increment, `CategoryName`varchar(255)NOTNULLdefault'', PRIMARYKEY (`CategoryID`), UNIQUEKEY`CategoryName`(`CategoryName`))TYPE=MyISAMAUTO_INCREMENT=5;----Dumpingdatafortable`re_categories`--INSERTINTO`re_categories`VALUES(3,'ForSale');INSERTINTO`re_categories`VALUES(4,'ForRent');--------------------------------------------------------------Tablestructurefortable`re_listings`--CREATETABLE`re_listings`( `ListingID`int(10)NOTNULLauto_increment, `AgentID`int(10)NOTNULLdefault'0', `CategoryID`int(10)NOTNULLdefault'0', `SubcategoryID`int(10)NOTNULLdefault'0', `address`textNOTNULL, `city`varchar(100)NOTNULLdefault'', `state`varchar(100)NOTNULLdefault'', `country`varchar(150)NOTNULLdefault'', `ShortDesc`textNOTNULL, `DetailedDesc`textNOTNULL, `Price`float(15,2)NOTNULLdefault'0.00', `PropertyType`varchar(50)NOTNULLdefault'', `neighbourhood`textNOTNULL, `rooms`int(2)NOTNULLdefault'0', `bathrooms`int(2)NOTNULLdefault'0', `fireplace`char(1)NOTNULLdefault'n', `garage`int(2)NOTNULLdefault'0', `SquareMeters`float(15,2)NOTNULLdefault'0.00', `LotSize`float(15,2)NOTNULLdefault'0.00', `HomeAge`int(3)NOTNULLdefault'0', `NearSchool`char(1)NOTNULLdefault'n', `NearTransit`char(1)NOTNULLdefault'n', `NearPark`char(1)NOTNULLdefault'n', `OceanView`char(1)NOTNULLdefault'n', `LakeView`char(1)NOTNULLdefault'n', `MountainView`char(1)NOTNULLdefault'n', `OceanWaterfront`char(1)NOTNULLdefault'n', `LakeWaterfront`char(1)NOTNULLdefault'n', `RiverWaterfront`char(1)NOTNULLdefault'n', `image`textNOTNULL, `DateAdded`int(10)NOTNULLdefault'0', `visits`int(10)NOTNULLdefault'0', PRIMARYKEY (`ListingID`))TYPE=MyISAMAUTO_INCREMENT=1;----Dumpingdatafortable`re_listings`----------------------------------------------------------------Tablestructurefortable`re_mail_archive`--CREATETABLE`re_mail_archive`( `subject`varchar(255)NOTNULLdefault'', `message`text, `MailDate`int(10)NOTNULLdefault'0')TYPE=MyISAM;----Dumpingdatafortable`re_mail_archive`----------------------------------------------------------------Tablestructurefortable`re_prices`--CREATETABLE`re_prices`( `PackageName`varchar(50)NOTNULLdefault'', `PriceID`int(10)NOTNULLauto_increment, `PriceValue`float(5,2)NOTNULLdefault'0.00', `Duration`varchar(10)NOTNULLdefault'', `PriorityLevel`int(1)NOTNULLdefault'0', `offers`int(10)NOTNULLdefault'0', PRIMARYKEY (`PriceID`))TYPE=MyISAMAUTO_INCREMENT=7;----Dumpingdatafortable`re_prices`--INSERTINTO`re_prices`VALUES('GrowUp',5,25.00,'1',3,15);INSERTINTO`re_prices`VALUES('StartMeUp!',4,14.95,'1',1,3);INSERTINTO`re_prices`VALUES('ShakeUp',6,34.95,'2',4,10);--------------------------------------------------------------Tablestructurefortable`re_priority`--CREATETABLE`re_priority`( `PriorityID`int(10)NOTNULLauto_increment, `PriorityName`varchar(50)NOTNULLdefault'', `PriorityLevel`int(1)NOTNULLdefault'0', PRIMARYKEY (`PriorityID`), UNIQUEKEY`PriorityName`(`PriorityName`))TYPE=MyISAMAUTO_INCREMENT=8;----Dumpingdatafortable`re_priority`--INSERTINTO`re_priority`VALUES(1,'Premium',5);INSERTINTO`re_priority`VALUES(2,'Gold',4);INSERTINTO`re_priority`VALUES(3,'Platinium',3);INSERTINTO`re_priority`VALUES(4,'Bronze',2);INSERTINTO`re_priority`VALUES(7,'Standart',1);--------------------------------------------------------------Tablestructurefortable`re_settings`--CREATETABLE`re_settings`( `id`int(1)NOTNULLdefault'0', `SiteTitle`varchar(255)NOTNULLdefault'', `SiteKeywords`varchar(255)NOTNULLdefault'', `SiteDescription`text, `ContactEmail`varchar(150)NOTNULLdefault'', `CompanyAddress`text, `PayPalEmail`varchar(150)NOTNULLdefault'', `SellerID`int(5)NOTNULLdefault'0', `Agreement`textNOTNULL)TYPE=MyISAM;----Dumpingdatafortable`re_settings`--INSERTINTO`re_settings`VALUES(1,'RealEstates','realestate,sales,rent,forrent,forsale,house,houses,apartment,apartments,studio,studios,rooms','FindpropertyforsaleandrentaccrosstheUSA','','Kingston,23Str.\r\n23332State','',84734,'yourtext\r\n\r\nsdfsdfgsdfgsdfgsdfgsdfsdfgsdfgsdfgdfg\r\n\r\nsdfsdfgsdfgsdfgsdfgsdfsdfgsdfgsdfgdfgsdfsdfgsdfgsdfgsdfgsdfsdfgsdfgsdfgdfg\r\n\r\n\r\nsdfsdfgsdfgsdfgsdfgsdfsdfgsdfgsdfgdfgsdfsdfgsdfgsdfgsdfgsdfsdfgsdfgsdfgdfgsdfsdfgsdfgsdfgsdfgsdfsdfgsdfgsdfgdfgsdfsdfgsdfgsdfgsdfgsdfsdfgsdfgsdfgdfg\r\n\r\nsdfsdfgsdfgsdfgsdfgsdfsdfgsdfgsdfgdfg\r\n\r\nsdfsdfgsdfgsdfgsdfgsdfsdfgsdfgsdfgdfgsdfsdfgsdfgsdfgsdfgsdfsdfgsdfgsdfgdfgsdfsdfgsdfgsdfgsdfgsdfsdfgsdfgsdfgdfg');--------------------------------------------------------------Tablestructurefortable`re_stats`--CREATETABLE`re_stats`( `BannerID`int(10)NOTNULLdefault'0', `impressions`int(10)NOTNULLdefault'0', `clicks`int(10)NOTNULLdefault'0', `mydate`int(10)NOTNULLdefault'0', `ip`varchar(50)NOTNULLdefault'')TYPE=MyISAM;----Dumpingdatafortable`re_stats`----------------------------------------------------------------Tablestructurefortable`re_subcategories`--CREATETABLE`re_subcategories`( `SubcategoryID`int(10)NOTNULLauto_increment, `SubcategoryName`varchar(255)NOTNULLdefault'', `CategoryID`int(10)NOTNULLdefault'0', PRIMARYKEY (`SubcategoryID`))TYPE=MyISAMAUTO_INCREMENT=10;----Dumpingdatafortable`re_subcategories`--INSERTINTO`re_subcategories`VALUES(3,'Houses',4);INSERTINTO`re_subcategories`VALUES(4,'Appartments',4);INSERTINTO`re_subcategories
how long would it take the production server to add new indexes?   (198 Views)
Below is the table structure. How long would it take to add two new indexes to this table that has 200,000 records I know it partially depends on what else the server is doing and the type of machine but will it take hours or seconds is my basic question CREATE TABLE `purchase_list` ( `purchase_list_id` int(10) unsigned NOT NULL default '0', `purchase_id` int(10) default '0', `menu_id` int(10) unsigned NOT NULL default '0', `purchase_list_price` float NOT NULL default '0', `purchase_list_specific` varchar(128) default NULL, `purchase_list_name` varchar(255) NOT NULL default '', `user_id` int(10) default NULL, `old_purchase_id` int(10) default NULL, `purchase_mod_date` datetime default NULL, PRIMARY KEY (`purchase_list_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Lost connection to MySQL server during query (error_no 2013)   (295 Views)
Hi people I am using PHP to invoke mysql_query to my database. In most cases everything works fine and the query executes perfectly. Sometimes, I will get a burst of errors and the query will fail with the following error: Lost connection to MySQL server during query (err_no 2013) Does anyone know what could cause this Could this be a mysql configuration problem, or maybe some network problem what can I do to pinpoint the problem
Please help me, mysql server is running really slow :(   (202 Views)
How big/complex are you queries what is the execution time for them What kind of data is stored in the database If you get any errors, you should post them too. , That sounds strange, what happens if you just keep that line disabled. Which lines/options did you made active Try to post the ini again after the changes. Have you any thoughts about updating MySQL to the newest version 5.5.27 A fast look at the version history for the most recent versions has several bug fixes for innodb.
How long does it take for mysql server to reboot?   (241 Views)
reboot command long does it take for mysql server to reboot
What is the best way to test queires that may crash the database server?   (187 Views)
When dealing with multi-million row tables, there are times where I have to write queries that could return wrong results (due to wrong JOIN or bad sql practice). What would be the best way to test such queries before running them on the server I see sometimes a wrong query can jam the whole database server. Even "EXPLAIN query" can jam the server. Does it make a difference to run the queries from out of phpmyadmin For example in Putty Anyways, tips would be very appreciated.
Mysql 2 server replication setup with fail over   (253 Views)
I have 2 servers in the UK and US. Currently they are rsync-ed and have a master - slave setup where the UK server is master. Now when the master goes offline (which happens too often) the whole eCommerce system isn't working anymore. So i'm looking for a setup to solve this problem. So far I've found that a master-master setup would solve the problem partly as the ecommerce functionality wouldn't be offline if UK server is offline neither if US server is offline, however as far as I've researched looks like master - master setup has some drawbacks and when one server goes down and then comes back online it will most likely have replication issues which need manual fixing. So what I need is a master - master with fail-over solution, however this seems to be very difficult with just 2 servers. Does anybody have some suggestion for a master - master + fail-over solution I found this which seems interesting: I think this does what I need, however I could use a bit more of step-by-step on what to do (a howto would be nice).
Column privleges not loading following MySQL server restart - help!   (258 Views)
I am having an issue with MySQL column/table privileges that has stumped me for the last 3 weeks. I would appreciate any help or ideas folks out there can provide. Here is the situation: My hosting provider automatically restarts the MySQL server that my databases sit on nightly as part of their maintenance script. A few weeks ago, I created MySQL user accounts that have very specific table and column privileges to increase site security and limit user access to only necessary areas of the db. After the server daemon restarted that night, I attempted to access the database via one of these "restricted" user accounts the next morning - access denied. I found that reloading privileges on the MySQL server magically makes everything work as I intended (all user account access returns). This behavior repeats nightly. For some reason, when the server goes down, it does not come back up with the correct privileges loaded into memory. As soon as I issue a reload privileges command, everything is fine. I've scowered the web and can't seem to find out why this is happening or what I need to do to fix it. I've attempted to delete and re-create the user accounts - same problem. Other user accounts seem to work fine - the problem is only with the user accounts that I created having specific table and column privileges. MySQL Server Version: 3.23.32 Thank you in advance for any suggestions.
Query actually killing whole server :(   (152 Views)
the following code is attempting to select user info and latest transaction date for any professionals who: ordered themselves (proff="yes"[first in() statement]) or their clients ordered (proff"yes"[second in() statement]) instead of the deed results, my server runs up to 120% cpu usage and the host screams at me Anyone willing to help me figure out what I am doing wrong Code: SELECT * FROM (SELECT transdate, userNum, usEmail, usDate, fName, lName, shipaddress1, shipaddress2, shipcity, shipstate, shipzip, phone1, phone2, fax, proff FROM users, transacts WHERE users.transType="ppst" AND transacts.OrderuserNum=userNum AND (userNum in(SELECT userNum from users, transacts WHERE transacts.transdate>"2010-07-17" AND transacts.OrderuserNum=userNum AND proff="yes" AND users.transType="ppst") OR userNum in(SELECT proffID from users, transacts WHERE transacts.transdate>"2010-07-17" AND transacts.OrderuserNum=userNum AND proff"yes" AND users.transType="ppst") ) ORDER BY transacts.transdate DESC) coregroup GROUP BY coregroup.userNum
Mysql client behind ISA server - with only proxy connections?   (163 Views)
hi there! i have a .NET app using MySQL connector library which needs to connect to the internet where the mysql server is. one of our users is trying to run the software in a network that uses several Microsoft ISA servers. the problem: the default gateway for their client machine(s) is set to a local intranet ISA server which does not connect to the internet. the only route to the internet is through another ISA proxy which must be specified in the application. i think this is a security thing so no apps can use the internet by default. this setup cannot be changed so i have to work around it. as mysql .net connector doesn't support connections through proxies, is there any other solution could i set the mysql server address in the software to the internet-facing ISA server and then set up some sort of routing in ISA bit of a weird situation - any thoughts or ideas appreciated
High bandwidth usage between web dedicated server and mysql dedicated server !   (248 Views)
I think your best bet would be the slow-query-log. If a query is returning a lot of data I there might be a chance that it also turns up in the slow-query-log. Otherwise I would suggest that you try to find queries with no WHERE or LIMIT clause, since it sounds like there are a couple of queries in the application that retrieves a lot of data from the database and then just discards it in the PHP code. Or it is simply that your application aggregates data like for example produces reports etc, which means that it reads much more data than what it returns. Either way you have some digging to do. Good luck!
Connecting to external MySQL DB from a web server not running MySQL   (435 Views)
While I've been working with MySQL for years, this is the first time I've run across this very newbie-esq issue. Due to a client demand, I must host their website files (PHP) on a IIS server that is not running MySQL (instead, they are running MSSQL). However, I have developed the site using a MySQL database which is located on an external host (Rackspace Cloud). Obviously, my mysql_connect function is now bombing because MySQL is not running on localhost. Question: Is it even possible to hit an external MySQL database if localhost is not running MySQL Apologies for the rookie question, and many thanks in advance. * To clarify, I know how to connect to a remote MySQL server, but it is the fact that my IIS web server is not running ANY form of MySQL (neither server nor client) that is giving me trouble. Put another way, phpinfo() does not return anything about MySQL. Additionally, I do not have access to that IIS web server other than FTP *
Ajax based multiserver MySql admin tool?   (180 Views)
I was wondering if anyone knows an Ajax based admin tool that is able to manage multiple servers I have 3 web hosting accounts and plenty of DBs to manage, so it's getting quite time consuming to use PhpMyadmin... I know there are a few tools that require to be installed locally, but I need something that can be used from a browser.
Read bin log of remote server   (189 Views)
Hi I have been trying to read the bin log of remote server. I don't want to use master slave mysql replication technique. I just started mysql server with --bin-log option and it is regularly generating bin logs. I just want to read the bin logs of remote server using following command: D:\Program Files\wamp\bin\mysql\mysql5.0.51b\bin>mysqlbinlog -R -h -u randhir -prandhir1800 devubas_posserver_backup.000002 | mysql -h localhost - u root -proot But i keep on getting following error: Got error reading packet from server: Misconfigured master - server id was not set I dont wan't to set up master slave replication.
Column name X is ambiguous on other server...   (178 Views)
hi on the other server i get an error that the column name is ambiguous if i dont prefix the table, while on my testing server i only need to prexif the second time i use it how can i get either server to act like the other
Can't connect to MySQL server on 'localhost'   (332 Views)
I'm on XP, running MySQL along with php. I used some tutorial to setup the server and a database. Yesterday everything was working great, but then today I got that error (the one which is the title of this post). After some searching it seems the problem is likely that sever just isn't on. But how do I know if it's on Also, I've tried "mysqld start" in the command line but it didn't change anything. Please help,