Query two different databases for differences
I have two different databases on the same 5.0 server that have the same tables and structure. They also have very nearly the same data. For one of the tables I'd like to run a query to find the records that are present in one database but not the other. Is this possible and what would such a query look like? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query two different databases for differences
From: Peter Brawley
I'd like to run a query to find the records that
are present in one database but not the other.
See 'Compare data in two tables' at
http://www.artfulsoftware.com/infotree/queries.php.
Thanks. That's a start.
SELECT
MIN(TableName) as TableName, id, col1, col2, col3, ...
FROM (
SELECT 'Table a' as TableName, a.id, a.col1, a.col2, a.col3, ...
FROM a
UNION ALL
SELECT 'Table b' as TableName, b.id, b.col1, b.col2, b.col3, ...
FROM b
) AS tmp
GROUP BY id, col1, col2, col3, ...
HAVING COUNT(*) = 1
ORDER BY ID;
This finds common rows. That doesn't help when the tables have about
20,000 rows and most are the same. How do I invert the query so that I
can find the 'uncommon' rows? Second, the primary key ('id' in the
example) values do not match, so how/where in the query can you specify
how to match rows from the two tables using other columns?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
64 bit build for Windows?
Is there a 64 bit build of MySQL 5.0 available for Windows Server 2003 64 bit edition? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 64 bit build for Windows?
Doh... found it. Why are the 'without installer' versions significantly larger than those with the installer? I would expect it to be the other way around? - Original Message - From: James Eaton [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, July 17, 2007 3:24 PM Subject: 64 bit build for Windows? Is there a 64 bit build of MySQL 5.0 available for Windows Server 2003 64 bit edition? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Concatenate a column in multiple rows into one column?
I have a database with roughly the following structure: album -- albumid INT title VARCHAR(255) artist -- artistidINT nameVARCHAR(255) albumartist -- albumid INT artistidINT From the above, you can see that any given album can have more than one artist. For instance: album title: A Meeting by the River artist name: Ry Cooder artist name: V.M. Bhatt I'd like to run a query that returns one row per album, but somehow also returns all of the artists associated with the album (ideally concatenated with an ampersand seperator). Can this be done? titleartists --- -- A Meeting by the River Ry Cooder V.M.Bhat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB, 1 file per table or 1 BIG table?
- Original Message - From: Dan Nelson [EMAIL PROTECTED] To: Ow Mun Heng [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, October 09, 2006 9:12 AM Subject: Re: InnoDB, 1 file per table or 1 BIG table? In the last episode (Oct 09), Ow Mun Heng said: Just wanted to know if it would be faster/better to implement this option into my.cnf innodb_file_per_table = 1 which would essentially make each table a file on it's own rather than have it all in 1 file. My belief is that it would be slightly more advantageous compared to 1 BIG file. eg: 1 10GB file would perform poorer than 10 1GB files. Is this statement true and how far is is true? I don't think that the number of files has any impact on query speed. The advantage file-per-table gives you is the ability to recover unused space easily by running OPTIMIZE TABLE. With a single tablespace, the only way to recover space is to dump all the tables, delete all the tablespace files, and reload. Are you saying OPTIMIZE TABLE has no effect when there's a single tablespace, or are you saying that running an OPTIMIZE table causes MySQL to go through all those gyrations? So then what are the advantages of having a single tablespace over file-per-table? I'd assume there must be some if that's the default configuration. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB, 1 file per table or 1 BIG table?
- Original Message - From: Bruce Dembecki [EMAIL PROTECTED] To: mysql@lists.mysql.com Cc: Ow Mun Heng [EMAIL PROTECTED] Sent: Monday, October 09, 2006 3:13 PM Subject: Re: InnoDB, 1 file per table or 1 BIG table? There are some minor performance benefits here when run against benchmarks... but tiny. It is generally true that for tiny tiny We really really like innodb_file_per_table - but mostly because it makes our lives easier in many ways, not so much for performance reasons. Interesting stuff. Is the directory/file organization then similar to that used by MyISAM tables when this option is enabled? That is, along the lines of innodb_data_root/database/table? How do you go about converting InnoDB databases from the single tablespace to those using the table-per-file file organization? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sorting numerically within a varchar
If I have the following strings in a varchar column: Piano Sonata 1 - Brendel Piano Sonata 10 - Brendel Piano Sonata 11 - Brendel Piano Sonata 12 - Brendel Piano Sonata 13 - Brendel Piano Sonata 14 - Brendel Piano Sonata 15 - Brendel Piano Sonata 16 - Brendel Piano Sonata 17 - Brendel Piano Sonata 18 - Brendel Piano Sonata 19 - Brendel Piano Sonata 2 - Brendel Piano Sonata 20 - Brendel Piano Sonata 21 (Waldstein) - Brendel Is it possible to sort the records into the following order? Piano Sonata 1 - Brendel Piano Sonata 2 - Brendel Piano Sonata 10 - Brendel Piano Sonata 11 - Brendel Piano Sonata 12 - Brendel Piano Sonata 13 - Brendel Piano Sonata 14 - Brendel Piano Sonata 15 - Brendel Piano Sonata 16 - Brendel Piano Sonata 17 - Brendel Piano Sonata 18 - Brendel Piano Sonata 19 - Brendel Piano Sonata 20 - Brendel Piano Sonata 21 (Waldstein) - Brendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 3.23 bind-address
I have a legacy app that only supports MySQL 3.23. I'd like to run it along with 5.0.24a on a Windows 2000 server, with each instance using the standard port 3306. So I need to have each instance bound to just one IP address. In MySQL 3.23 can you specify a bind-address in the my.ini file, or can it only be specified using the command line option --bind-address=IP? I can only find documentation for the command-line option. If it _can_ be designated in the ini file, which form does it take? [mysqld] set-variable = bind-address=192.168.3.8 or [mysqld] bind-address=192.168.3.8 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to query bind address?
Is there a way to query a MySQL (5.0.xx) server to find out which IP address(es) it's listening on? It should be listening on all, but I'm unable to connect from remote machines, so need to troubleshoot a bit. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to query bind address?
- Original Message -
From: Dan Buettner [EMAIL PROTECTED]
I believe MySQL listens on all IPs ... what if any error message are
you getting?
From SQLyog I get:
Error No. 2003
Can't connect to MySQL server on '192.168.9.2' (10061)
Can you access other services (SSH, telnet, FTP) over the same routes?
Yes.
Can you telnet to MySQL over those routes? If so you may have an
authentication problem, not a connection problem. Here's a sample of
what a telnet session might look like:
% telnet 127.0.0.1 3306
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
A
5.0.21-standard-log6{-kS?!,$2lMx:Ty%I!d
(I then pressed return and the connection closed)
I can telnet to MySQL from the localhost, but not from any other location.
On 8/23/06, James Eaton [EMAIL PROTECTED] wrote:
Is there a way to query a MySQL (5.0.xx) server to find out which IP
address(es) it's listening on? It should be listening on all, but I'm
unable to connect from remote machines, so need to troubleshoot a bit.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: How to query bind address?
Yes, it's running on Windows XP, but the Windows Firewall has been
disabled.
Just figured it out... This was a version of MySQL bundled with an
application. I wrongly assumed that shutting down the application also
shut down the included MySQL server. The original my.cnf had a
bind-address of 127.0.0.1, which I'd removed, but since the MySQL server
wasn't actually being restarted, the new configuration was never picked
up.
- Original Message -
From: Dan Buettner [EMAIL PROTECTED]
To: James Eaton [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Wednesday, August 23, 2006 3:07 PM
Subject: Re: Re: How to query bind address?
Is it running on Windows, James? You may have to allow connections
through to MySQL in Windows Firewall.
Otherwise it seems like maybe it's been started with --skip-networking ?
Dan
On 8/23/06, James Eaton [EMAIL PROTECTED] wrote:
- Original Message -
From: Dan Buettner [EMAIL PROTECTED]
I believe MySQL listens on all IPs ... what if any error message are
you getting?
From SQLyog I get:
Error No. 2003
Can't connect to MySQL server on '192.168.9.2' (10061)
Can you access other services (SSH, telnet, FTP) over the same
routes?
Yes.
Can you telnet to MySQL over those routes? If so you may have an
authentication problem, not a connection problem. Here's a sample of
what a telnet session might look like:
% telnet 127.0.0.1 3306
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
A
5.0.21-standard-log6{-kS?!,$2lMx:Ty%I!d
(I then pressed return and the connection closed)
I can telnet to MySQL from the localhost, but not from any other
location.
On 8/23/06, James Eaton [EMAIL PROTECTED] wrote:
Is there a way to query a MySQL (5.0.xx) server to find out which IP
address(es) it's listening on? It should be listening on all, but
I'm
unable to connect from remote machines, so need to troubleshoot a
bit.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Should we petition GoDaddy?
- Original Message - From: Nicolas Verhaeghe [EMAIL PROTECTED] To: 'MySQL General' mysql@lists.mysql.com Sent: Saturday, April 15, 2006 11:20 PM Subject: Should we petition GoDaddy? I am slowly considering leaving GoDaddy, who has a very good bandwidth and ok tech support (I have seen better but much much worse) and acceptable prices, but unfortunately does not support MySQL 5 and PHP 5 either. I wonder if I could give these people a chance but one thing I'd like to do is see if we could petition these guys and simply threaten to close out our accounts if they do not get on with the music. The current version of MySQL is 3.23. I have no time upgrading my dedicated server to a RedHat machine with custom PHP and MySQL, I don't have the time to do that. Who think we could make them make the right move and pretty quickly? Save your energy and just move your web site to another host. Don't be too impressed by extravagent bandwidth claims. In a shared hosting environment, where servers are often grossly overloaded, you'll either have very little chance of reaching those bandwidth numbers, or if you do, you'll be asked to upgrade or close your account because of the high processing load needed to push that much traffic. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
