RE: compare tables from two systems
There is a product that will do the job. It is MYdbPAL (www.it-map.com) which is a free license to MySQL users. You can extract all or partial datasets from 2 databases, compare them and produce a 'differences' dataset. This can, be viewed, edited, and if needed be applied to the target database to update it and put it back in synch. Tim Hayes -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: 05 October 2005 23:25 To: Claire Lee; mysql@lists.mysql.com Subject: Re: compare tables from two systems - Original Message - From: Claire Lee [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, October 05, 2005 5:58 PM Subject: compare tables from two systems We have two clusters running the same data independently and saving output to databases on two systems (two hosts) respectively. We need to compare the output each day. Is there an easy way to compare tables from two systems? We are sure the structure of the tables are all the same and we need to compare the data. Any advice is welcome. Thanks. I'm not sure if there are any existing table comparison tools out there; in an ideal world, someone would have written something that works on any relational database that would compare any two tables to see if they are the same. I've never actually heard of such a tool but I've never looked for one either. If there is no such tool out there, you could create one of your own without too much difficulty. One way that should be pretty easy would be to do a mysqldump of each table then do standard file compares of each of the two resulting files. I have a freeware program for Windows called ExamDiff which seems to do that job okay, although I've never tried comparing output files from mysqldump with it. There may be one very difficult problem with this approach: it assumes that the mysqldump will write the INSERT statements for the individual rows in a specific order, ideally primary key sequence, for each table. Unfortunately, I don't see any options for mysqldump that ensure that this will happen and I don't see any statement in the manual that say it will happen automatically. Therefore, it is entirely possible that the mysqldumps of each table will write the INSERTs in some sequence other than primary key order. For example, mysqldump might use the sequence in which the rows were stored, retrieving them from oldest to newest, rather than primary key sequence. In any case where primary key sequence is not used - or where no primary key exists on the table - the two mysqldump files could have completely different sequences even though they have the identical rows. That would almost certainly preclude this approach working. Why not just try doing mysqldumps of each of your two tables and then do a file compare of the two files and see what happens? It shouldn't take long and you'll soon see if this approach will work. By the way, why are you keeping two independent - yet supposedly identical copies - of your tables? Isn't that redundant *and* expensive? Wouldn't it make more sense to backup a single copy of the database regularly so that you can restore when you have problems? Or, if you need the same database in two different locations, why not use replication to ensure that the two copies are automatically synchronised? Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.9/116 - Release Date: 30/09/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: good database design
I disagree completely. I prefer to have regard to the statement of requirement, which in this case is a concern over performance. If following conventional design rules creates performance issues, then performance related issues come first when considering design. In times long since gone by (I am showing my age here) client side message response times were written into contracts. Design had to take into account performance issues. With very high loaded web-sites as in this case, a little time spent on lateral thinking can make a big difference and save costs in the long run, and keeps customers happy. Tim Hayes MYdbPAL - www.it-map.com -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: 22 September 2005 09:02 To: mysql@lists.mysql.com Subject: Re: good database design I need links about good database design information for high loaded web sites... A database design should start with the logical data-related requirements, not with performance related issues. IMO, of course. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: good database design
This is an interesting subject area. In a data warehousing environment, one tends to adopt table structures such as snowflake layouts which lead to improved performance. Createing a perfect normalised database design may well lead to performance issues. The more joins you have, by far the worse the performance. You may need to consider horizontal or vertical table splits. You may need to consider replicating certain data in child tables to avoid joins. I am not saying you do not need to carry out data analysis and gain a full and first hand understanding of the data structures. It is just that when it comes to online performance, sometimes you have to break the rules. -Original Message- From: Sujay Koduri [mailto:[EMAIL PROTECTED] Sent: 22 September 2005 09:58 To: Ian Sales (DBA); [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: RE: good database design My 2 cents.. Before you actually start worrying about the performance tuning of database parameters or hardware required for the DB, you should make sure that you have designed the database properly by taking care of all aspects like normalisation, denormalisation (??). If you don't take care of these logical design aspects in the early stages properly, these things will prove you very costly in the long run. Th easy and recommended way to do it is .Draw an E-R diagram .Do any normalization. .Identify proper datatypes for the table creation. .Identify and add proper indexes. .And now actually you should start worrying abt the DB Tuning and harware requirements. sujay -Original Message- From: Ian Sales (DBA) [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 2:17 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: good database design Tim Hayes wrote: I disagree completely. I prefer to have regard to the statement of requirement, which in this case is a concern over performance. If following conventional design rules creates performance issues, then performance related issues come first when considering design. - personally, I would consider integrity, and then reliability, above performance. But then 80% of any performance hit is in the application code. Design a database that gives you confidence in the data it stores first and foremost. - ian -- +---+ | Ian Sales Database Administrator | | | | All your database are belong to us | | ebuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Changed Number
Ken Looks like you used a medium int field on the mysql table - if you import a figure that is too big for the medium int to handle - on overflow it places a value of 8388607 into the colum. You need to change to an integer column. Regards Tim hayes -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 06 April 2005 02:31 To: mysql@lists.mysql.com Subject: Changed Number I have loaded a large *.csv spreadsheet into mysql and one number, the grand total, changes from 16996941 on the Excel spreadsheet to 8388607 in the mysql database. The numbers surrounding this number are correct at all stages. I have reloaded, checked formatting and done various other things without success. Then I went to the text (*.sql) file in which I had dumped the mysql table, manually changed the number to 16996941 and put the text file on the server. However, the number that showed up on the web page table and the number in the database on the server is 8388607. Note that the smaller number is just under half of the larger number. This makes no sense. Any solutions? Ken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need Help with 813-MDB File
David Please also have a look at MYdbPAL for MySQL. Freeware again - this will sort out any schema conversions you might need - auto corrections. Plus you can model the schema. MDB conversion is shown in the tutorials. Tim Hayes -Original Message- From: David Blomstrom [mailto:[EMAIL PROTECTED] Sent: 30 March 2005 06:23 To: mysql@lists.mysql.com Subject: RE: Need Help with 813-MDB File Thanks for both your tips. I discovered by chance that Navicat (which I have) will do the conversion - very easily. Whether or not it will be a success is hard to say; it's loaded nearly 3 million rows so far, with over 8,000 errors recorded. But I'm going to download DB Tools, as I have frequent need for data conversion tools. Thanks. --- J.R. Bullington [EMAIL PROTECTED] wrote: DB Tools software will convert the file for you. You can download it at http://dbtools.com.br/EN/index.php. All you have to do is download and install the FreeWare version and then use the TOOLS DAO Import Wizard. J.R. -Original Message- From: David Blomstrom [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 29, 2005 11:29 PM To: mysql@lists.mysql.com Subject: Need Help with 813-MDB File I acquired a CD-ROM that lists many thousands of animal species. The main file is a 813-MB MDB file. I'm not sure if it's a spreadsheet or database, but it's apparently designed to work with Microsoft Access, which I THINK is a spreadsheet. (I don't have it.) I have just enough memory to open the file in WordPad, but it doesn't do any good because much of the data consists of unintelligible characters. Anyway, I need to figure out a way to import this monster into MySQL. Are you aware of any freeware programs that can open up files designed for Access? Is there a way to convert a MDB file directly into a csv file, which could then be imported into MySQL? I'll probably eventually break it into sections. At the very least, I'll probably divide it between vertebrates (which I'll use the most) and invertebrates. Thanks. __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help coverting MDB SQL
Try MydbPAL at www.it-map.com. Its a FREE product that will do a complete .mdb (access) to My SQL translation - schema and data. There is a learning curver but you can follow the tutorial which takes you thru an mdb conversion Tim Hayes -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: 26 March 2005 19:28 To: Ricky Groleau; mysql@lists.mysql.com Subject: Re: Need help coverting MDB SQL Sorry, I don't know how to help you with this; I've never really worked with MDB files or ASPs. I have no experience with the tools that you are using to extract data from the MDB files so I can't answer your questions about IP addresses either. Maybe someone else on this list can help you, although it isn't usually very active on weekends, especially holiday weekends. You could also try posting to one of the many Microsoft newsgroups; I believe there are newsgroups covering ASPs. Another possibility for you is to review the Microsoft manuals. There must be some documentation on how to use MDB files and get data from them. Rhino - Original Message - From: Ricky Groleau To: Rhino ; Ricky Groleau ; mysql@lists.mysql.com Sent: Saturday, March 26, 2005 10:35 AM Subject: Re: Need help coverting MDB SQL Microsofts Acesss that makes ASP pages. The databsae is saved as MDB. Yes, it does have a uplink, my problem is lack of experience with SQL. It asks for localhost, but of course that i smy computer and I dont have any SQL or anything on it. I tried to send it to my sever, but I am lost..is it the IP or web address? Where is SQL hidden on a linux/apache server? ---Original Message--- From: Rhino [EMAIL PROTECTED] Subject: Re: Need help coverting MDB SQL Sent: Mar 26 2005 10:26:58 - Original Message - From: Ricky Groleau [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, March 26, 2005 10:13 AM Subject: Need help coverting MDB SQL Hello, I have tried and i have failed. I am trying to help out a non-profit site in their move. The old site had a forum run by MDB and in the transfer as you know...it won't work. I have used PHPBB and I like it and want to use it. We need to convert the MDB to SQL. I have DL 2 converters...and MySql..again I have failed. The database is almost 9 meg. I need someone that can convert this for me and then explain how to upload since it is so big. I have read you cannot o it thru the web/mysql? Any help...guidancePLEASE! I'm not familiar with MDB but I'm assuming that it is some kind of database, like DB2 or Oracle. Does MDB have any kind of export facility? All of the good databases - and even a lot of lousy ones - have some way to convert the database format into one or more text files. There might be a single file for all of the data or a separate file for each table in the database. If MDB has such a facility, you can almost certainly use MySQLs import facilities to convert the export files from MDB into MySQL tables. Does MDB have some kind of export capability? If you don't know, can you point me to where the MDB documentation is located online and perhaps I can figure it out for you. Rhino -- No virus found in this outgoing message. Checked by AVG AntiVirus. Version: 7.0.308 / Virus Database: 266.8.3 - Release Date: 25/03/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ---Original Message--- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.8.3 - Release Date: 25/03/2005
BIGINT UNSIGNED issue?
Hi I am testing against MySQL 4.1 current release on Windows and there seems to be a fault with unsigned bigint datatypes. Using a BIGINT UNSIGNED datatype, the maximum value that will be accepted on insert query is 9223372036854775807 , which is actually the published maximum for signed Bigints. Also - with any other unsigend integer, an input value which exceeds the maximum will be corrected and be set to the maximum value (eg. input 7 to a smallint unsigned and you get 65535). However, with BigInt only the resulting value is set to zero. Is this a genuine fault or am misunderstanding something? Tim Hayes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to Version Control a database
Try using MYdbPAL from www.it-map.com . The product has complete schema version control, alowing you to track changes and compare sche,as for diffrences. Its FREE to MySQL users. Tim -Original Message- From: Will Merrell [mailto:[EMAIL PROTECTED] Sent: 20 February 2005 14:00 To: Mysql Mailing list Subject: How to Version Control a database I have a project that involves several developers working on their own machines. Each has a local copy of the database on their own machine. Since we have some developers who develop while not connected to the network, we cannot use a common database. How can I version control the database so that changes are not lost or stepped on. Right now, we use mysqldump to dump the database and version the dump file, but this still has problems. Is there a better way? -- Will -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Pulling a database schema out of a unknown MySQL database?
Dan Please try MYdbPAL for MySQL - www.it-map.com. It is FREE, and you can reverse-engineer the schema; view, model, forward engineer etc. Tim Hayes -Original Message- From: Dan Stromberg [mailto:[EMAIL PROTECTED] Sent: 02 February 2005 12:31 To: mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: Pulling a database schema out of a unknown MySQL database? I have a system with set of web pages that use PHP and a MySQL database. Apparently the old webmaster has disappeared, and a new webmaster has been hired. She needs to know the schema of the database. So my question is: Is there a way of querying MySQL not for values of fields, but rather for the schema of the database? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Data and strucuture importing from MSAccess to MySQL
Suggest you download MYdbPAL from www.it-map.com . It is FREE, will do the complete import of structure and data for you (in next to no time) and you can carry on using it for modeling and database development. There is a great write-up this week at : http://blogs.ittoolbox.com/database/solutions/archives/002944.asp Tim Hayes -Original Message- From: Paun [mailto:[EMAIL PROTECTED] Sent: 29 January 2005 04:44 To: mysql@lists.mysql.com Subject: Data and strucuture importing from MSAccess to MySQL Which way is easier to import data and structure of database from MSAccess to MySQL. I know that there is several commercial softvare tools, but is there some open source tool or other way in MySQL itself? -- Internal Virus Database is out-of-date. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.7.2 - Release Date: 1/21/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Data fields from one database to another
Try using MYdbPAL - its a new free program that will do the job plus lots of other goodies. www.it-map.com Tim Hayes -Original Message- From: Stuart Felenstein [mailto:[EMAIL PROTECTED] Sent: 28 September 2004 08:23 To: [EMAIL PROTECTED] Subject: Data fields from one database to another I want to move over 2 - 4 fields from a table in one database , to a table in another. The field names are not an exact match but they function identically (i.e. username, password) Whatever I'm using , web development garbage program ;) won't allow me to connect to 2 different databases so I can't do an update or insertion through that means. Wondering how I script something like that, and probably prefer a way to do it on the fly with each new sign up , or batched on a regular basis aka, every hour , every few hours. Thank you, Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Data fields from one database to another
Yes. Absolutely. MYdbPAL has a complete schema-to-schema table and data field mapping capability that will also let you do things like table splits and joins. It also has inbuilt scripting and data value translation lookups. Timk -Original Message- From: Stuart Felenstein [mailto:[EMAIL PROTECTED] Sent: 28 September 2004 09:57 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Data fields from one database to another So it will let me transfar individual fields ? Most of the clients allow for data transfer provided the database schema is the same, and then it is the entire record. Stuart --- Tim Hayes [EMAIL PROTECTED] wrote: Try using MYdbPAL - its a new free program that will do the job plus lots of other goodies. www.it-map.com Tim Hayes -Original Message- From: Stuart Felenstein [mailto:[EMAIL PROTECTED] Sent: 28 September 2004 08:23 To: [EMAIL PROTECTED] Subject: Data fields from one database to another I want to move over 2 - 4 fields from a table in one database , to a table in another. The field names are not an exact match but they function identically (i.e. username, password) Whatever I'm using , web development garbage program ;) won't allow me to connect to 2 different databases so I can't do an update or insertion through that means. Wondering how I script something like that, and probably prefer a way to do it on the fly with each new sign up , or batched on a regular basis aka, every hour , every few hours. Thank you, Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table Name Case Sensitivity
Can anyone offer advice? I have come across a MySQL database on Linux with duplicate table names - Accounts and accounts. This seems fine on Linux, but does not transfer to the Windows environment - it is rejected because of the duplicate name. However I do see that Column Names have to be unique in both environments. Q. Is there some sort of practical advantage for having case sensitive table names in Linux, or is this a design shortcoming? Postgress rejects duplicate table names. Thanks Tim Hayes
Re: Table Name Case Sensitivity
OK There is still the possibility of an in-compatability between the 2 platforms. However - in both Linux and Windows (MySQL 4.0.17) the variable is not recognized / updateable using the set command! I get - Unknown system variable 'lower_case_table_names' Tim Hayes - Original Message - From: Peter Zaitsev [EMAIL PROTECTED] To: Tim Hayes [EMAIL PROTECTED] Sent: Monday, February 23, 2004 4:19 PM Subject: Re: Table Name Case Sensitivity On Mon, 2004-02-23 at 08:15, Tim Hayes wrote: Can anyone offer advice? Run with lower_case_table_names=1 I have come across a MySQL database on Linux with duplicate table names - Accounts and accounts. This seems fine on Linux, but does not transfer to the Windows environment - it is rejected because of the duplicate name. However I do see that Column Names have to be unique in both environments. Q. Is there some sort of practical advantage for having case sensitive table names in Linux, or is this a design shortcoming? Postgress rejects duplicate table names. Thanks Tim Hayes -- Peter Zaitsev, Senior Support Engineer MySQL AB, www.mysql.com Meet the MySQL Team at User Conference 2004! (April 14-16, Orlando,FL) http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
