innodb, optimizer and outer join
We are considering using Hibernate as a persistence layer to our web application. It seems to only want to do outer joins and this concerns me b/c they can be expensive. I created the following benchmark experiment and learned that the explain plan for the two constrained queries is the same. What I would like to know is can I depend on the performance being the same, or is the optimizer doing something different b/c of the outer join? I seem to remember something about it not using the index all the time or forcing a full table scan in some cases. Since Hibernate seems to using only an outer join rather than a join, I would like this concern put to rest. Thanks for any insight. Boyd create table foo ( foo_id int unsigned not null auto_increment primary key, foo_sn varchar(15), ) ; create table foo_child ( foo_child_id int unsigned not null auto_increment primary key, foo_id int unsigned not null, foo_child_sn varchar(15), index fk_foo$foo_child (foo_id) ) ; insert into foo values (1,'a'), (2,'b'), (3,'c'), (4,'d') ; insert into foo_child values (1,1,'z'), (2,1,'y'), (3,2,'x'), (4,3,'w'), (5,9,'v bad 1'), (6,9,'v bad 2'), (7,3,'t'), (8,4,'s') ; -- unconstrained select * from foo_child fc join foo f on fc.foo_id = f.foo_id select * from foo_child fc left join foo f on fc.foo_id = f.foo_id -- constrained select * from foo_child fc join foo f on fc.foo_id = f.foo_id where f.foo_id = 1 select * from foo_child fc left join foo f on fc.foo_id = f.foo_id where f.foo_id = 1 Best Regards, Boyd E. Hemphill MySQL Certified Professional [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2278 x 405
Apple install
I am considering the use of a new OS X machine with the Free BSD back end. I would like answers to the following if anyone has the experience. What switches should be set for compiling? I only need the Innodb storage engine. Which is the best compiler to use for MySQL on an Apple machine? Best Regards, Boyd E. Hemphill WEST Project Manager MySQL Certified Professional [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2278 x 405 M: (512) 470-6146
RE: SELECT DISTINCT Problem
People, including me, often expect the wrong thing from SELECT DISTINCT, May suggest you do the following: Create table temp_tb_spots as Select * from tb_spots where aired_station = '??' ; select distinct Date from temp_tb_spots group ; In a different window run Select Date from temp_tb_spots group by Date ; The creation of the extra table is so you can browse it to help convince yourself you are getting what you intend The two select statements are equivalent in MySQL and will give you the same result. This result should be the same as your original query. If not then there is a bug. Hope that helps set your expectation of the distinct key word. Best Regards, Boyd E. Hemphill WEST Project Manager MySQL Certified Professional [EMAIL PROTECTED] Triand, Inc. www.triand.com -Original Message- From: James Purser [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 08, 2005 7:19 PM To: mysql@lists.mysql.com Subject: SELECT DISTINCT Problem I have a large database that I am trying to run a SELECT DISTINCT across. SELECT DISTINCT Date FROM tb_spots WHERE aired_station = '??' However the results I am getting from this query do not match up with the data on the database, instead there are large gaps. Is there any know problem with SELECT DISTINCT across large databases? -- James Purser Winnet Developer +61 2 4223 4131 http://www.winnet.com.au -- 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]
FW: Moving innodb from Linux to Windows
Best Regards, Boyd E. Hemphill MySQL Certified Professional [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2278 M: (713) 252-4688 -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Sunday, November 14, 2004 11:32 AM To: Boyd E. Hemphill Cc: [EMAIL PROTECTED] Subject: Re: Moving innodb from Linux to Windows Boyd, please send this correspondence to [EMAIL PROTECTED] Right, lower_case_table_names=2 does not work with InnoDB. I guess the way for you is then to use lower_case_table_names=1 on both Unix and Windows. The drawback is that table dumps will have the name in lower case. Note that, according to SQL standards, a database server should convert all table names to UPPER CASE by default. My RENAME TABLE advice was for the case where you already have some tables, and want to make them to conform to lower_case_table_names=1. Regards, Heikki - Alkuperäinen viesti - Lähettäjä: Boyd E. Hemphill [EMAIL PROTECTED] Vastaanottaja: 'Heikki Tuuri' [EMAIL PROTECTED] Kopio: [EMAIL PROTECTED] Lähetetty: Friday, November 12, 2004 3:51 PM Aihe: RE: Moving innodb from Linux to Windows Heikki Gleb: Thank you for the quick responses. I had read the documentation that Dr. Tuuri quotes and took it to mean that I had to account for this with parameter settings, etc. The DB I am working with is a 40G production tablespace. It has over 100k lines of Java code against it. We capitalize the first letter in each word of table and column names. Changing this would require a very serious undertaking. I understood this document http://dev.mysql.com/doc/mysql/en/Name_case_sensitivity.html to mean that this problem could be dealt with by setting lower_case_table_names=2 I think the issue is that the doc was written for the MyISAM engine and not the InnoDB engine. Could one of you clarify? I appreciate your time in the matter. Best Regards, Boyd E. Hemphill MySQL Certified Professional [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2278 M: (713) 252-4688 -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Friday, November 12, 2004 3:08 AM To: [EMAIL PROTECTED] Subject: Re: Moving innodb from Linux to Windows Boyd, --=_NextPart_000_0038_01C4C7F5.22045240 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit All: I am attempting to move a set of db's from Linux (Mysql 4.0.20) to Windows (4.1.6). I have everything running, and I can query tables that are all lower case. However I cannot issue a query on a table that contains uppercase letters. This is the my.ini file: on Windows, InnoDB puts all database names and table names to lower case, because on Windows MySQL is case-insensitive. You should use, e.g., RENAME TABLE UppercaseDBName.UpperCaseTable TO uppercasedbname.uppercasetable; in your Unix database to make it suitable for porting to Windows. And set lowercase_table_names=1 in the my.cnf of your Unix database, so that your applications can find the tables after renaming them. http://dev.mysql.com/doc/mysql/en/Moving.html On Windows, InnoDB internally always stores database and table names in lowercase. To move databases in a binary format from Unix to Windows or from Windows to Unix, you should have all table and database names in lowercase. A convenient way to accomplish this on Unix is to add the following line to the [mysqld] section of your `my.cnf' before you start creating your databases and tables: [mysqld] set-variable = lower_case_table_names=1 On Windows, lower_case_table_names is set to 1 by default. We may fix this in the future, so that on Windows InnoDB will also find tables with uppercase letters in their name. For that, we need a MySQL function that looks from the .frm file name and directory what is the 'canonical' case of the letters in the database name and the table name. Best Regards, Boyd E. Hemphill Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.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]
Moving innodb from Linux to Windows
All: I am attempting to move a set of db's from Linux (Mysql 4.0.20) to Windows (4.1.6). I have everything running, and I can query tables that are all lower case. However I cannot issue a query on a table that contains uppercase letters. This is the my.ini file: [client] port=3306 [mysqld] port=3306 basedir=C:/Program Files/MySQL/MySQL Server 4.1/ datadir=F:/xxx/ tmpdir=F:\\temp\\ default-character-set=latin1 default-storage-engine=INNODB max_connections=100 query_cache_size=100M table_cache=256 tmp_table_size=103M thread_cache=8 #*** INNODB Specific options *** innodb_data_home_dir=F:/xxx/ innodb_log_group_home_dir=F:/xxx/ innodb_data_file_path=ibdata1:15G:autoextend innodb_log_arch_dir=F:/xxx/ innodb_additional_mem_pool_size=7M innodb_flush_log_at_trx_commit=1 innodb_log_buffer_size=4M innodb_buffer_pool_size=305M innodb_log_file_size=10M innodb_thread_concurrency=8 #*** MyISAM Specific options myisam_max_sort_file_size=100M myisam_max_extra_sort_file_size=100M myisam_sort_buffer_size=205M key_buffer_size=157M read_buffer_size=64K read_rnd_buffer_size=256K sort_buffer_size=256K #set-variable = lower_case_table_names=2 And this is the error I get if I attempt to access the table Grade Select * from Grade ERROR 1016 (HY000): Can't open file: 'grade.InnoDB' (errno: 1) In the .err log I have: 04 13:47:03 InnoDB error: Cannot find table tpri/grade from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? Look from section 15.1 of http://www.innodb.com/ibman.html how you can resolve the problem. 04 13:47:03 [ERROR] C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt: Can't open file: 'grade.InnoDB' (errno: 1) I am pulling my hair out! Please help! Best Regards, Boyd E. Hemphill MySQL Certified Professional [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2278
optimizing InnoDB tables
The documentation is not clear on this point. Here is a quote: 'For BDB tables, OPTIMIZE TABLE is currently mapped to ANALYZE TABLE. It was also the case for InnoDB tables before MySQL 4.1.3; starting from this version it is mapped to ALTER TABLE.' What is meant by its being mapped to ALTER TABLE? Too, what exactly happens after 4.1.3? Is space, in fact, recovered and defragged? Thanks for your time! Best Regards, Boyd E. Hemphill MySQL Certified Professional [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2278 M: (713) 252-4688 -Original Message- From: Christopher L. Everett [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 06, 2004 6:23 PM To: 'Mysql List' Subject: Re: Repeated corruption with MySQL 4.1.x using FULLTEXT indexes Ed Lazor wrote: -Original Message- From: Christopher L. Everett [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 06, 2004 1:47 AM To: Mysql List Subject: Repeated corruption with MySQL 4.1.x using FULLTEXT indexes I have an application where I create a faily large table (835MB) with a fulltext index. One of our development workstations and our production server will run the script to load the table, but afterwards we have a pervasive corruption, with out of range index index pointer errors. Oddly, my development workstation doesn't have those problems. My box and the ones having the problems have the following differences: - my box runs ReiserFS, the problem boxes run XFS - my box has a nice SCSI HD subsystem, the problem boxes do IDE. All three boxes run Linux 2.6.x kernels, and my workstation and production server share the same mobo. Come to think of it, I saw similar corruption issues under 2.4.x series kernels and MySQL v4.0.x, it just wasn't the show stopper it is now. Also, on all three boxes, altering the table to drop an index and create a new one requires a myisamchk -rq run afterwards when a fulltext index either exists or gets added or dropped, which I'd also call a bug. The problems you're describing are similar to what I've run into when there have been hardware related problems. One system had a problem with ram. Memory tests would test and report ram as ok, but everything started working when I replaced the ram. I think it was just brand incompatibility or something odd, because the ram never gave any problems in another system. I can generate the problem on much smaller data sets, in the mid tens of thousands of records rather than the millions of records. I'll do a memtest86 run on the development boxes overnight, but as I did that just after I installed linux on them and used the linux badram patch to exclude iffy sections of RAM, I don't think thats a problem. One system had hard drive media slowly failing and this wasn't obvious until we ran several full scan chkdsks. 3 hard drives all of different brand, model size, and the problem happening in the same place on both? Not likely. The funniest situation was where enough dust had collected in the CPU fan to cause slight over heating, which resulted in oddball errors. This isn't a problem on my box. I have a 1.5 pound copper heatsink with a 90mm heat sensitive fan and a fan+heatsink for the hard drive, and I saw myisamchk consistently generate the same error in the same place over and over. The sensors report my CPU running in the 45 degree centigrade range on my box pretty consistently. In each of these cases, everything would work fine until the system would start processing larger amounts of data. Small amounts of corruption began to show up that seemed to build on itself. This may or may not relate to what you're dealing with, but maybe it will help =) I'll look, but I don't think that's the problem. I'm going to see how small of a data set will cause this problem and file a bug report. -- Christopher L. Everett Chief Technology Officer www.medbanner.com MedBanner, Inc. www.physemp.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]
innodb monitoring
I notice the following special tables innodb_monitor, innodb_lock_monitor, innodb_tablespace_monitor, innodb_table_monitor, and innodb_validate. The information from the first can be accessed from the MySQL client prompt with show innodb status without creating the table and watch standard output by issuing show innodb status. Is it possible to access the others in a similar way? Thanks for your time! Best Regards, Boyd E. Hemphill MySQL Certified Professional [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2278 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Using CREATE PROCEDURE/FUNCTION
Marti: Stored procedures are new in version 5.0. Best Regards, Boyd E. Hemphill MySQL Certified Professional [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -Original Message- From: Marti Quixal [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 25, 2004 8:23 AM To: [EMAIL PROTECTED] Subject: Using CREATE PROCEDURE/FUNCTION Hi! Anybody knows how to use CREATE PROCEDURE and CREATE FUNCTION in 4.1.1-alpha-Max. I keep trying the CREATE PROCEDURE and CREATE FUNCTION examples found in the MySQL web documentation, but there's no way to make it work. It always prompts problems with syntax. Is delimiter a function working in all versions of MySQL? Is there a bug in this version? Any helping hand? Thanks! Marti -- 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: excel
Another way to do this is to get the data as HTML then just open the file in Excel. The mysql client has a switch for HTML output. Just redirect the output of your query to a file. Try the following: mysql -h query.sql /path/result.sql Best Regards, Boyd E. Hemphill MySQL Certified Professional [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -Original Message- From: Andrew Pattison [mailto:[EMAIL PROTECTED] Sent: Friday, August 20, 2004 8:01 AM To: [EMAIL PROTECTED] Subject: Re: excel Two main ways: 1. Dump your tables to a file and import them using the text import filter. 2. Install MyODBC and open a connection to the MySQL tables in Excel. Since Excel is a proprietary file format, MySQL does not support it. Also, bear in mind that Excel has a 65,535 row limit hard coded into it - which means if you want column headings you are limited to 65,534 rows. Cheers Andrew. - Original Message - From: Scott Hamm [EMAIL PROTECTED] To: 'Mysql ' (E-mail) [EMAIL PROTECTED] Sent: Friday, August 20, 2004 2:53 PM Subject: excel How do I export from mysql into excel format? -- 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: Best method to export Excel data into MySQL?
The quick answer is to save your data as a comma delimited file with no headers. (I suggest actually using bang | since your data may contain commas.) Then look in the docs for the load data in file statement. Be careful to note the fields delimited by, lines terminated by and such. Best Regards, Boyd E. Hemphill MySQL Certified Professional [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -Original Message- From: Eve Atley [mailto:[EMAIL PROTECTED] Sent: Friday, August 20, 2004 8:53 AM To: [EMAIL PROTECTED] Subject: Best method to export Excel data into MySQL? What would be the best method (using free tools, or Office suite) to export an Excel spreadsheet into a format suitable for import into MySQL? I am on a PC platform. Thanks, Eve -- 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]
check table
All: I would like to run the check table tablename medium statement on a regular basis. We use InnoDB exclusively (go Heikki!). My problem is that the documentation seems to indicate the data can be changed. I am specifically referring to this phrase in the docs: So, these are my questions: Note: In some cases, CHECK TABLE will change the table! This happens if the table is marked as ``corrupted'' or ``not closed properly'' but CHECK TABLE doesn't find any problems in the table. In this case, CHECK TABLE marks the table as okay. 1. What causes this statement to change data? I think what is being said is that if, during the check, the table is found to be corrupt or not closed properly, it will be changed. This leads to the next question: 2. What are the nature of the changes to the data? 3. What information do I get about data that has changed from the server? 4. The doc is unclear about what is performed for InnoDB specifically. I am assuming it the same operation as for MyISAM but would like to have this clarified. Best Regards, Boyd E. Hemphill MySQL Certified Professional [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Restoring dump file problem
Andre: If you are using enforcing FK constraints it may be that the tables are in the wrong order. Try inserting the line SET foreign_key_checks = 0; at the front of the file and SET foreign_key_checks = 1; at the end, then try again. Hope this helps. Best Regards, Boyd E. Hemphill MySQL Certified Professional [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -Original Message- From: Andre Mato [mailto:[EMAIL PROTECTED] Sent: Thursday, August 19, 2004 9:00 AM To: [EMAIL PROTECTED] Subject: Restoring dump file problem Hi List, Today I got a surprise when I tried to restore a dump file did yesterday. I did not work. I don't know if because is too big, I don't think so (just 3 lines). However, when I remove one table AuditTrail that tracks changes in the database, it worked. I am using MySQL 4.0.18-standard with InnoDB on Mac OS X 10.3.5. Is there any issue related a dumped file size? Thanks!!! Andre -- Andre Matos [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: 1 day 28 min insert
Matt: You would be very well served to give the table a unique physical key. Alternate or primary, you could then join to this single column. I am not sure how big the table to get, however, so you will definitely want to use a bigint unsigned if you plan to test this theory. Benefits: - You can remove the 6 to 8 columns of the PK in the table you are writing to and replace it with a small integer column. Over 17m inserts and comparisons you can imagine the savings in disk IO. - Easier for the optimizer to choose the correct index. - More likely to get reads straight from the index. - Easier code to write/read/maintain Also, piling the records to be inserted into a temporary table can be much quicker. This is my experience with tables of 2 to 3 million rows. B/c this operation may still be long I do not recommend the use of a temporary table, but rather a regular table that you create and destroy as part of the process. Are you using MyIsam or InnoDB? If MyIsam, then if you are scheduling the insert as delayed and the table is still being read from, you may be experiencing an issue where there are enough reads to keep the insert from getting started. Also if MyIsam, is your row pointer large enough? If not this will slow you down too. Have the tables been analyzed and checked lately? Really, there are a number of server parameters to check. Setting this will be very unique to your situation. Oracle is the same way, esp when clustering. Seems to me that spending $1500 or so on some MySQL consulting would be much less expensive than an Oracle license, plus the cost of porting, plus the consulting it would take to get your Oracle server tuned for your app. Best Regards, Boyd E. Hemphill MySQL Certified Professional [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Thursday, August 19, 2004 10:48 AM To: matt ryan Cc: [EMAIL PROTECTED] Subject: Re: 1 day 28 min insert In the last episode (Aug 19), matt ryan said: One alternative, since you know you don't have many records to insert, is to pull the IDs of the missing records and insert just those. Do an outer join on the two tables (joining on the primary key), get a list of the IDs of records in stat_in but not in 321st_stat, and add a WHERE id IN (list,of,ids) clause to the end of your INSERT ... SELECT statement. If you're running 4.1, you can use a subquery and embed the first query directly in the INSERT. Running 4.0.x something like, select a.*, b.* from a left outer join b on a.col1=b.col1, a.col2=b.col2, a.col3=b.col3 where b.col1 is null into temptable then insert from temptable into table a I think I tried this once, but it ran all day The select, or the insert? If the select, run an EXPLAIN and see if it's using the wrong index or no index. Is there a way to join on an index, instead of on each column? The primary key is 6-8 columns I forget You have to list each column. SHOW KEYS FROM table or SHOW CREATE TABLE table will list all the keys and which fields are used in each key. -- Dan Nelson [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: Database export
Edd: The best way is likely dependent on some factors that involve your storage engine(s) (e.g. MyISAM, Innodb, etc) and the nature of the media you plan to use to move the file created. The smallest file you can produce is via mysqldump. Your 4GB db includes all of your indexes. When using mysql or mysqlimport these will be rebuilt on the fly. Also, since it is a text file, you can compress it even further with TAR to very high efficiency. This would be the optimal way to do the job if you plan to use some bandwidth dependent transfer. It is likely that the dump file will fit on a CD. The other advantage this method is simplicity. Dump one file, move the file, run the file and you are done, regardless of your storage engine. If speed is of utmost concern, then consider following (closely) the directions for making binary copies. These directions and requirements are different per storage engine, so closely consult the documentation. (Note the repetition of closely). Given the size of your DB it should fit on a DVD if you have a burner available, otherwise you can likely rig a USB or Firewire drive to do the trick. Best Regards, Boyd E. Hemphill MySQL Certified Professional [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -Original Message- From: Edd Dawson [mailto:[EMAIL PROTECTED] Sent: Monday, August 16, 2004 4:39 AM To: [EMAIL PROTECTED] Subject: Database export I have a 4GB database which i need to move to another hosting company. The source and destination databases are mysql so there is no conversion issue. My question is has anyone any recomendations on which is the best method for doing this. I am aware of doing a database dump and then importing into the new server. But i want to know if there are any issues with the file size. i.e. will carrying out an import time out with the file size being so large. I have also come across mysqlhotcopy, will this do? Any thoughts on this will be much appreciated. -- 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]
Useful DBA Unix for analyze (check) tables
All: I created this script to analyze the tables on a periodic basis. It accounts for changes in the schema by using a database table called SysTable in each db to be analyzed. Create table SysTable TableName varchar(255) not null ); We run this once per week now using a CRON job. A quick overview: 1. Empty SysTable if it was previously used. 2. Get a list of tables in a file 3. Load those into SysTable 4. Create the analyze statements 5. Run them and place the result in a log file. if [ ! -d /dba] ; then mkdir /dba fi mysql db1name -e 'truncate SysTable' mysql db1name -s -e 'show tables' /dba/tables_db1name.txt mysql db1name -e 'load data infile /dba/tables_db1name.txt into table SysTable' mysql db1name -s -e 'select concat(analyze table db1name., TableName,;) from SysTable' /dba/analyze_db1name.sql mysql db2name -e 'truncate SysTable' mysql db2name -s -e 'show tables' /dba/tables_db2name.txt mysql db2name -e 'load data infile /dba/tables_db2name.txt into table SysTable' mysql db2name -s -e 'select concat(analyze table db2name., TableName,;) from SysTable' /dba/analyze_db2name.sql cat analyze_*.sql analyze.sql mysql db1name analyze.sql analyze.log rm /dba/tables_*.txt rm /dba/analyze*.sql Of course, this can be done with filenames in unix as well using ls, cut and cat on the .frms in the data directory. However, the side-effect of having a table of table names in each data base can be quite desirable. Consider that you can add the check table command to this script and periodically/automatically check the tables too. Hope some of you out there find this useful. Best Regards, Boyd E. Hemphill MySQL Certified Professional [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Tuning InnoDB situation
All: I have been nosing about for some time now and think I need some help. The Problem: Mytop is telling me that I am running no more than 1000 queries per second, and the key efficiency is 100%. But, some select and replace statements are taking an unusually long time. These seem to revolve around a couple of tables that are written to and read from very often. The queries normally take no longer than 5 seconds in a test environment on a slower machine. In production (where the problem is) they can last from 90 to 1400 seconds. A few of these bring our site to a crawl. Suspicions: I have seen this machine run at 3000 to 6000 qps and still move data out fast. Some of the queries it is now performing slow are were part of this performance in the past. So my first idea is that the server, rather than the query, needs to be tuned. Here is the my.cnf stuff: [mysqld] port= 3306 socket = /var/run/mysqld/mysqld.sock skip-locking skip-bdb set-variable= key_buffer=16M set-variable= max_allowed_packet=10M set-variable= max_connections=1200 set-variable= table_cache=256 set-variable= sort_buffer=2M set-variable= net_buffer_length=64K set-variable= myisam_sort_buffer_size=32M log-bin server-id = 2 pid-file= /var/run/mysqld/mysqld.pid #log = /var/log/mysql/mysql.log log-slow-queries basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language= /usr/share/mysql/english default-table-type = innodb query-cache-type = 1 query-cache-size = 20M set-variable = net_read_timeout=600 set-variable= net_write_timeout=600 innodb_data_home_dir = /var/lib/mysql/innodb innodb_log_group_home_dir = /var/lib/mysql/ innodb_log_arch_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:15G:autoextend set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=1G set-variable = innodb_log_buffer_size=16M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=800M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 I am considering raising the table_cache to 1500 and the innodb_buffer_pool_size to 2.5GB. Comments on this would be appreciated as well. If this is not the issue then I suspect there is contention in some of the busy tables. Where do I look at the SHOW INNODB STATUS output to detect this situation? What am I looking for? Can I schedule InnoDB transactions? We are running Gentoo Linux 2.6.4 on a dual AMD Opteron machine with 3.5 GB of memory and a RAID 1+0 disc array. We are using the InnoDB storage engine. The DB contains about 160 tables. The DB is about 50GB in size. Thanks for your time. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. Life is not a journey to the grave arriving safely in a well preserved body, but rather a skid in broadside, thoroughly used, totally worn, and loudly proclaiming: WOW! What a ride! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
assigning values to user variables in the mysql client
I would like to do something like this in the mysql client Select CourseId Into @CourseId From Course Where CourseCd='ENGL'; I also tried @CourseId = select CourseId from Course where CourseCd = 'ENGL'; Neither syntax works. So I am wondering if there is a way to assign values using SQL in the mysql client. This would be _very_ handy for scripting. Here is what I produced to get around the problem. It is a bit convoluted but if you are wishing for this functionality (and it does not yet exist) it will do the trick: insert into Course values(null, 21, 14, '', 99, 'Not Assigned', 0, 0) ; select concat( 'set sql_auto_is_null = 1; ', 'set @CourseId = ', CourseId,'; ', 'update Test set CourseId = @UniCourseId where Title like \'SAT%\'; ' ) into outfile 'update_test.sql' from Course where CourseId is null ; source /var/lib/mysql/db_name/update_unitest.sql ; set sql_auto_is_null = 0 ; Be sure to clean up your data directory by deleting the file created as select ... into outfile places files into the diretory of the db on which the query is entered Boyd E. Hemphill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Display of ? and Hex conversion
All: We discovered a rather odd situation where some space characters where being displayed as ?. In tracking this down, it was determined that the server had stored the hex value A0 rather than 20 by using this query: select LocationId, LevelId, Hex(NameLn),NameLn from Location where LevelId = 3 order by NameLn; So, I issued this statement to fix it (using 4.0.16) update Location set NameLn = replace(hex(NameLn), 'A0' , '20') where hex(NameLn) like '%A0%' Now for the NameLn field I have the hex string (arrg my data has been hexed!!! :-) So, my questions are: 1. How do I go back from the hex string to characters? 2. Has anyone else seen this problem? At this point I can say the diplay issue only appears on some browsers. Thanks Boyd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: COPY row?
John: Try: Insert into TABLE Select * from TABLE where TABLEID = ?; Update TABLE set column = 'desired value' where TABLEID = newPK; It's a bit manual, but defn the quick and dirty for a single row now and again. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -Original Message- From: John Mistler [mailto:[EMAIL PROTECTED] Sent: Thursday, June 10, 2004 1:40 PM To: [EMAIL PROTECTED] Subject: COPY row? Is there a COPY row or DUPLICATE row command? I would like to duplicate a row with in a table with 38 columns (auto-incrementing the Primary Key on the copied row, of course) and then alter the entry in ONE of its columns. Can this be done without doing a SELECT, then INSERT, then UPDATE? Thanks, John -- 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]
EJB - Entity Beans
All: First let me say I know only the most basic of Java stuff. I am working with a Java colleague on the development of a data model (MySQL of course :-) and we seem to run into the problem an Entity Beans cannot support joins. Can anyone tell me if this is true (If so, what use are they for any sort of complex data model?). If no, can you send me some information on how to implement a join with the Beans and some possible resources to consult? My naïveté leads me to believe I should be able to create a container of beans that is the result of any query I can write against the DB. Or something like this anyway! Please help! Thanks for your time. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -Original Message- From: Sudip Shekhawat [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 09, 2004 2:33 PM To: [EMAIL PROTECTED] Subject: Foreign Key on text field Hi, I am using MySQL 4.0.17. When I try to create a foreign key on a field of the type text, I get this error: 040609 15:02:31 Error in foreign key constraint of table db_name/tb_name: There is no index in the table db_name/tb_name where the columns appear as the first columns. Constraint: FOREIGN KEY (CEA_key) REFERENCES relation (CEA_key) ON DELETE CASCADE )TYPE=InnoDB; Can I create a foreign key on the text field?? In the parent table the parent column has a index of length 64. Thanks Sudip _ Looking to buy a house? Get informed with the Home Buying Guide from MSN House Home. http://coldwellbanker.msn.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: 4.1 Beta
I just installed 4.0.18 under Windows XP Home (I know, I know). After searching my hard drive I cannot find my.cnf or my.ini Where do I find the file? What is its name? Thanks Boyd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb FK column rename
All: What are the consequences of renaming a column in a child table that is the FK to the parent? Will innodb remap or do I need to destroy the relationship, index and column and recreate? I am using 4.0.13 Thanks for your time! Boyd E. Hemphill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Very large query text?
I am having a tough time of it in production today ... Any help would be appreciated. I am executing a query of about 10k in size. When I do this it takes about 15 seconds. If I remove a bunch of case, sum and if statements but get the same explain plan the query runs in 5 seconds (it size is less than 500 bytes). What is the limiting factor? Is it the size of the query or all the computation I am doing with case, etc? I have tried increasing query_prealloc_block but this did not help. Any suggestions or advice would be appreciated. Thanks Boyd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
urban myth?
My boss says that if you do a select statement against a table the result set always comes back in the same order. I say that this is a myth and that the result is random, except when some ordering is specified in the SQL statement. Who is right? Is this behavior specified by ANSI or ISO? Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: urban myth?
To all who answered thank you. This answer below is the one that I can use to convince him what he proposes is not necessarily safe. Now I just need to decide how to convince him it was his idea :-) Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Monday, May 03, 2004 12:57 PM To: Bob Ramsey Cc: [EMAIL PROTECTED] Subject: Re: urban myth? Bob Ramsey wrote: Ah, but the ordering is not random. As your example has it, the results are in the order that the entries were inserted into the table. There is an explanation for the order of the returned data. snip Apparently not random, but not in the order inserted either. Consider: create temporary table foo (num int(10)); insert into foo values (1), (2), (3), (4), (5); select * from foo; delete from foo where num = 3; insert into foo values (6); insert into foo values (3); mysql select * from foo; +--+ | num | +--+ |1 | |2 | |6 | |4 | |5 | |3 | +--+ 6 rows in set (0.01 sec) (Same example as before with the delete...where num=6 removed.) Note the 6 is where the 3 was originally, because the slot where the first 3 was inserted/deleted was reused for the 6. This trivial example yields results which are ordered neither by num nor by the order inserted. The lesson is clear: The *only* way to be sure your rows are sorted in a particular way is to explicitly request it with an ORDER BY clause, as several others have pointed out. This is really a fundamental principle: It is the data in the row that matters, not how or where it is stored. Michael -- 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]
sql_no_cache
I am trying to turn of the query caching for select queries I am testing as I would like to rerun the as if they were the first hit. The query-cache-type = 1. I am suspicious b/c I run a query and it takes 12 seconds. I then run the same query with no changes and it takes .17 seconds. Another piece to this puzzle might be that when I issued the FLUSH QUERY CACHE command and then reran the query and it still took a very short time. Since this is a devel server and I am the only one around I don't think there are any other obvious things going on. Mytop is clear of processes. Am I missing something? Boyd E. Hemphill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: sql_no_cache
In an update to the last, I think I have the issue in hand: I am using TOAD 1.0 and it seems to be doing some sort of caching itself or going directly to the query cache. I issued the identical query and it would return the result immediately. Checking Mytop (did I mention that JZ rules?) the query was still running on the server. Thus it appears as if TOAD either cached the result or was going to the query cache itself. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -Original Message- From: Boyd E. Hemphill [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 27, 2004 1:41 PM To: [EMAIL PROTECTED] Subject: sql_no_cache I am trying to turn of the query caching for select queries I am testing as I would like to rerun the as if they were the first hit. The query-cache-type = 1. I am suspicious b/c I run a query and it takes 12 seconds. I then run the same query with no changes and it takes .17 seconds. Another piece to this puzzle might be that when I issued the FLUSH QUERY CACHE command and then reran the query and it still took a very short time. Since this is a devel server and I am the only one around I don't think there are any other obvious things going on. Mytop is clear of processes. Am I missing something? Boyd E. Hemphill -- 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: Going Crazy in KY
Chris: The easiest way to do this is to start the MySQL client in the directory of the file. To do this start your dos prompt, cd to the directory and then start MySQL. Type your source command and all should be could. If you're feeling frisky, then type source path\file.name and it should run. Using an absolute path is more simple (c:\path\etc...). Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -Original Message- From: Chris Stevenson [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 27, 2004 2:12 PM To: [EMAIL PROTECTED] Subject: Going Crazy in KY I'm trying to run a saved command in windows format. At mysql I'm typing source create_event.sql Create.event.sql is my saved command and is my c:\mysql\bin\sampdb directory. 1. What is the correct entry to get the table EVENT created in X database using a stored command? Please send help!? Or at least play some Motley Crue.. Have a great day! Chris Stevenson Call Center Manager The American Board of Family Practice 859-269-5626 ext. 299 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimization and the use of indexes
I thought I heard at the conference that the optimizer only uses a one index per table in a query regardless of the number of indexes on the table. Is this true? Are there any more details regarding the use of indexes I can find in the documentation? Specifically how the optimizer picks which index to use? I read through 7.2.4 and several other sections but no luck. Paul D...Jeremy Z, are you out there? Save me! :-) Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Optimization and the use of indexes
Jeremy: That has to be a record for fastest response with the most use. WOW! I do use Explain but knowing this info allows me to use it in a more intelligent way. Thank you very much for your time. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Monday, April 26, 2004 4:23 PM To: Boyd E. Hemphill Cc: [EMAIL PROTECTED] Subject: Re: Optimization and the use of indexes On Mon, Apr 26, 2004 at 05:17:45PM -0500, Boyd E. Hemphill wrote: I thought I heard at the conference that the optimizer only uses a one index per table in a query regardless of the number of indexes on the table. Is this true? Yes. The only exception is in a UNION, but that's best thought of as multiple queries anyway. Are there any more details regarding the use of indexes I can find in the documentation? Specifically how the optimizer picks which index to use? I read through 7.2.4 and several other sections but no luck. The optimizer looks at the available indexes and attempts to find the one that will require the least amount of work, which usually translates to reading the fewest records to find a match. Have you run your queries thru EXPLAIN to see which keys is considers and which one it chooses? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: BETWEEN
I too cannot get the site correctly. I am using IE. If I wait long enough (about 2 min) text will appear but the images never seem to make it. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -Original Message- From: Andy Eastham [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 10:12 AM To: Mysql List Subject: RE: BETWEEN Max, You can measure the elapsed time by writing a linux shell script to do the inserts, then use the linux time command to run it. However, the user and system times displayed will not include the amount of cpu time used by the db server. Do it a few times and vary the number of inserts to build an accurate picture. Alternatively, you may be able to do this easier in version 4.1, where you can use %f in time_format to get milliseconds, so hopefully now() retrieves milliseconds too(?): select time_format(now(), '%H:%i:%s.%f'); to get timestamps to the nearest millisecond. Of course, getting the timestamp takes a finite amount of time, which you may want to measure. Andy -Original Message- From: Boyd E. Hemphill [mailto:[EMAIL PROTECTED] Sent: 20 April 2004 14:29 To: 'Max Michaels'; 'mysql' Subject: RE: BETWEEN Max: Thanks for the tip. Unfortunately I am not using a FreeBSD environment. My options are to either run a WinXP client remotely or to run something Linux based in a terminal emulator (Putty). Any suggestions would be appreciated. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -Original Message- From: Max Michaels [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 7:07 AM To: 'Boyd E. Hemphill'; 'mysql' Subject: RE: BETWEEN Hello: I am trying to measure the difference between a single insert statement of 10,000 rows and 10,000 insert statements. It is easy for me to see the single statement takes about 2 seconds. However I can come up with no good way to get the total time for individual statements. Can anyone provide a suggestion? Thanks in advance. Try super-smack. It works great for this type of testing. http://jeremy.zawodny.com/mysql/super-smack/ Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -- 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] -- 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: BETWEEN
Hello: I am trying to measure the difference between a single insert statement of 10,000 rows and 10,000 insert statements. It is easy for me to see the single statement takes about 2 seconds. However I can come up with no good way to get the total time for individual statements. Can anyone provide a suggestion? Thanks in advance. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: BETWEEN
Max: Thanks for the tip. Unfortunately I am not using a FreeBSD environment. My options are to either run a WinXP client remotely or to run something Linux based in a terminal emulator (Putty). Any suggestions would be appreciated. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -Original Message- From: Max Michaels [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 7:07 AM To: 'Boyd E. Hemphill'; 'mysql' Subject: RE: BETWEEN Hello: I am trying to measure the difference between a single insert statement of 10,000 rows and 10,000 insert statements. It is easy for me to see the single statement takes about 2 seconds. However I can come up with no good way to get the total time for individual statements. Can anyone provide a suggestion? Thanks in advance. Try super-smack. It works great for this type of testing. http://jeremy.zawodny.com/mysql/super-smack/ Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -- 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]
Question regarding defaults
Hello: I have need to declare a column as type integer then default is at '0' (that is a string with a zero in it). An example may be: Create table foo ( foo_id int not null default '0' ) My question centers on the notion of implicit type conversion. Is the server converting the type at the time the ddl (not really too big a deal) or is it doing the conversion at run time (i.e. each time a row is inserted in the DB). Thanks for your time and expertise! Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
XML to MySQL
Are there any open source (or other) to load an XML document directly to a MySQL table or set of tables? Any recommendations would be greatly appreciated. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. Life is not a journey to the grave arriving safely in a well preserved body, but rather a skid in broadside, thoroughly used, totally worn, and loudly proclaiming: WOW! What a ride! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What exactly is happening with this table create?
Using a tool to generate a data model I go the following statement: Create table StateN ( StateId Int NOT NULL AUTO_INCREMENT, StateNm Char(50) NOT NULL DEFAULT '', StateCd Char(7) NOT NULL DEFAULT '', SortInt Int NOT NULL DEFAULT 0, UNIQUE (StateId), UNIQUE (StateCd), Primary Key (StateId), UNIQUE Index ak_State (StateCd) ) TYPE = InnoDB ROW_FORMAT = Default; The last four statements seem to be redundant in that the UNIQUE(StateId) is a proper subset of the Primary Key(StateId) statement and is StateCd to its index. My question is, what is happening in terms of the objects I am creating? That is: Am I creating for indexes or two? Are they the pk and ak that I want (last two statements) or is the server only creating the first two? Any insight here is greatly appreciated! Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. Life is not a journey to the grave arriving safely in a well preserved body, but rather a skid in broadside, thoroughly used, totally worn, and loudly proclaiming: WOW! What a ride! -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: Monday, March 15, 2004 6:24 AM To: [EMAIL PROTECTED] Subject: Re: Getting Identity after INSERT Alex Curvers [EMAIL PROTECTED] wrote: Whats the preferred syntax to insert values in multiple (related) tables I insert one record, then i need the ID of the inserted value to add it the with the other inserts with MSSQL stored procs its INSERT INTO . SELECT @MyID = @@IDENTITY after that you can use @MyID So whats the preferred method with MySQL, is there a easy way to get the Identity back from a just inserted record, or do i just have to do a select based on a value i did just insert ? Use AUTO_INCREMENT column and LAST_INSERT_ID() function to retrieve most recent auto_increment value: http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html http://www.mysql.com/doc/en/Information_functions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.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: Changing the primary key
Michael: One thing to keep in mind with Richard's answer is that when you recreate the key with the third column, you are no longer guaranteeing uniqueness on the original two column key. Any tables referencing the old key could become corrupt (in the data sense) if a second identical entry becomes available. EG If you have in the table in question a key of (a,b) now you add the new column... you could have a key of (a,b,c) and (a,b,d). Any existing data will not know which item to reference if you need it to reference only one. So, give a need for uniqueness in the referencing tables you will need to accommodate the new key in each of them as well. If it is OK to get more than one row back from the FK reference then you can ignore the concern. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. Life is not a journey to the grave arriving safely in a well preserved body, but rather a skid in broadside, thoroughly used, totally worn, and loudly proclaiming: WOW! What a ride! -Original Message- From: Richard Davey [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 7:53 AM To: [EMAIL PROTECTED] Subject: Re: Changing the primary key Hello Michael, Wednesday, March 3, 2004, 1:40:00 PM, you wrote: RM I have a table, that has a primary key with two columns and want to add a RM third column to this primary key. RM Is this possible and when yes: Do I have to delete all tables, that RM reference to this table? No, you don't have to delete all tables that reference this table. You can drop the key: ALTER TABLE tablename DROP PRIMARY KEY and then re-create it: ALTER TABLE tablename ADD PRIMARY KEY (a,b,c) Please note that if you have a field with a property such as auto-increment then dropping the primary key will fail because it will leave an invalid table definition. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- 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]
Show Processlist command
All: I recently discovered the Show Processlist statement which is great. My question is this... One the process completes it falls off this list. Is there another command that will show me the run time of the processes that have completed? Is this info in a log? If so, which one? Thanks for your time. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. Life is not a journey to the grave arriving safely in a well preserved body, but rather a skid in broadside, thoroughly used, totally worn, and loudly proclaiming: WOW! What a ride! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: found rows in union
Victoria: In response you wrote: Also... if I run the same query twice.. first run and selecting columns.. then I run the same query but selecting COUNT(*).. does the second time will run using MySQL's cache? No. Queries should be the same, byte for byte, otherwise MySQL will treat them as different queries. Does this mean that MySQL does _not_ have the concept of a bind variable and thus have to reparse any query that has parameter changes? Thanks for your time. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. Life is not a journey to the grave arriving safely in a well preserved body, but rather a skid in broadside, thoroughly used, totally worn, and loudly proclaiming: WOW! What a ride! -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 10:47 AM To: [EMAIL PROTECTED] Subject: Re: found rows in union Lorderon [EMAIL PROTECTED] wrote: I'm running MySQL 4.0 and it doesn't support sub-queries... I've checked and found that the next query returns rows of counting each union part seperatedly and could make sum on its rows: (SELECT COUNT(*) FROM table WHERE id100) UNION ALL (SELECT COUNT(*) FROM table2 WHERE id150) returns: ++ | COUNT(num) | ++ |124 | |912 | ++ When running the query without union, you could use SQL_CALC_FOUND_ROWS even when having LIMIT, but this option not works when using union.. :( Is there anything like SQL_CALC_FOUND_ROWS in union? since I don't want to run the query twice SQL_CALC_FOUND_ROWS returns those number of rows for UNION that it should be returned without _global_ LIMIT clause. Also... if I run the same query twice.. first run and selecting columns.. then I run the same query but selecting COUNT(*).. does the second time will run using MySQL's cache? No. Queries should be the same, byte for byte, otherwise MySQL will treat them as different queries. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.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]
Working around Insert ... Select
Hello to all: I am attempting to move two columns of data into a large table by using the insert into . select . construct. My problem is that I need to join to the table I am inserting to. To get around this my idea is to create a temporary database and populate it with the table then join to this temporary table. While this will work (the table is static) it will take a long time. Does anyone have a better solution? Thank you for your time! Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. Life is not a journey to the grave arriving safely in a well preserved body, but rather a skid in broadside, thoroughly used, totally worn, and loudly proclaiming: WOW! What a ride!
RE: Need help with ledger type query
Charles: I am not quite sure by what you mean, How do I get it to show me as I like, one in and one out per line, rather then an in for every out and vice-versa? It seems for your example below you want a result set of 4 rows I don't think your design is appropriate, however your suggestion at the end is on the right track. Tbl_users is good. Now use these: Tbl_transaction Trans_Id | user | trans_type_id | trans_amt | entry_dtm -+--++---+-- 1 |1 | 1 | 20.00 | timestamp 2 |1 | 2 |100.00 | timestamp 3 |1 | 2 | 50.00 | timestamp 4 |1 | 1 | 40.00 | timestamp Tbl_transaction_type Trans_type_id | trans_nm | trans_cd --+--+- 1 | Debit | D 2 | Credit | C This provides you with a foundation for flexibility if you wish to add more transaction types in the future such as a transfer or stock option. BTW, you can use a case statement to help with signing the number properly. I just discovered this the other day and am really tickled with it! Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. Life is not a journey to the grave arriving safely in a well preserved body, but rather a skid in broadside, thoroughly used, totally worn, and loudly proclaiming: WOW! What a ride! -Original Message- From: charles kline [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 02, 2004 10:50 AM To: [EMAIL PROTECTED] Subject: Need help with ledger type query Hi all, I have 3 tables: tbl_users id | user --- 1 | charles tbl_ins id | in |date|user 1 | 100.00 |timestamp |1 2 | 50.00 |timestamp |1 tbl_outs id | out|date|user 1 | 20.00 |timestamp |1 2 | 40.00 |timestamp |1 I want this to work like a checkbook register, where by date, I display the ins and outs (one per line) with a running balance. Here is my query (I know it does not have the balance, but I can work that part out). SELECT tbl_users.id, tbl_ins.in, tbl_outs.out FROM tbl_users LEFT JOIN tbl_ins ON tbl_ins.user = tbl_users.id LEFT JOIN tbl_outs ON tbl_outs.user = tbl_users.id WHERE tbl_users.id = 1 How do I get it to show me as I like, one in and one out per line, rather then an in for every out and vice-versa? My other thought was to have one table with positive and negative values in an AMOUNT field... Thanks, Charles -- 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: Who can help the Newbie???
Rand: I think you referring to the MySQL command line program. Download MySQLCC from the site, install it and fire away. It is a bit unstable at times, but it is a nice tool IMHO. If you want to learn DOS (do your mean Unix?) then try Sam's publishing. I have had good luck with the xxx in 21 days series. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -Original Message- From: Randal [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 02, 2004 1:26 PM To: [EMAIL PROTECTED] Subject: Who can help the Newbie??? I am very new to this database thing. I don't want to clog up the board with all the basic questions that I need answered. I have been cataloging contact info and other things in MS Excel for years. I am familiar with the base concepts of a database; I think, but I have no idea what goes on in a DOS window. It took me a few days to figure out that I was going to have to use it to access the software. (I said I was new to this) I was hoping that the vast network of knowledge could point me in the direction of a source of what it is that I need to know about DOS to get moving on this darn 21 day tutorial I am stuck on day two of. Thanks in advance, Rand -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Load Data and Timestamps
Mark: I am not familiar with timestamp, but you may need to explicitly specify a NULL value in the timestamp column for the loader. Hope this helps Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. Life is not a journey to the grave arriving safely in a well preserved body, but rather a skid in broadside, thoroughly used, totally worn, and loudly proclaiming: WOW! What a ride! -Original Message- From: Mark Riehl [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 02, 2004 1:30 PM To: [EMAIL PROTECTED] Subject: RE: Load Data and Timestamps Sorry for the confusion, maybe I wasn't clear in the question. I reread my post and thought it might be misunderstood. The data in my CSV file isn't all zeros, but the timestamp column in my table is all zeros. According to the timestamp docs, auto update of the first timestamp column occurs if the column isn't specified in an INSERT or LOAD DATA INFILE statement. Let's say that my table has 3 columns (last is a timestamp type). My CSV file has two columns. Shouldn't my timestamp column get auto updated using LOAD DATA INFILE? Thanks, Mark -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 02, 2004 2:17 PM To: Mark Riehl Cc: [EMAIL PROTECTED] Subject: Re: Load Data and Timestamps An invalid datetime, or in your case, an empty value, will be set to the nearest zero value representation. Original Message On 3/2/04, 1:06:50 PM, Mark Riehl [EMAIL PROTECTED] wrote regarding Load Data and Timestamps: All - I'm running MySQL 4.0.13 under Red Hat 9. I've defined a table, and the last column is a timestamp type. +-+--+--+-+-+--- -+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+--- -+ | id | int(10) unsigned | | UNI | NULL| auto_increment | | dataType| varchar(64) | | PRI | | | | description | tinytext | YES | | NULL| | | dbName | tinytext | YES | | NULL| | | directory | tinytext | YES | | NULL| | | updated | timestamp(14)| YES | | NULL| | +-+--+--+-+-+--- -+ I'm using load data to parse local CSV files. All of the columns are being properly parsed and inserted into the tables, however, the timestamp column is always all zeros (000). I don't have a column in the CSV file that maps to the timestamp column (didn't think I needed it based on the documentation). An insert off the command line with a standard insert works fine, timestamp has the proper value in it. Any suggestions? Thanks, Mark -- 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: Re[2]: Type TEXT
If speed is a concern and storage is not, I believe that the fixed length char() will provide a better speed. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -Original Message- From: Richard Davey [mailto:[EMAIL PROTECTED] Sent: Monday, March 01, 2004 5:32 PM To: Jim McAtee Cc: [EMAIL PROTECTED] Subject: Re[2]: Type TEXT Hello Jim, Monday, March 1, 2004, 8:58:51 PM, you wrote: JM Thanks. What (if any) difference is there then be between a varchar(255) and JM a tinytext column? On the surface nothing I believe. Varchar too is a variable length data type. There may well be internal differences in the way MySQL handles them however, but I couldn't tell you what. JM And what disadvantages are there be in using columns of type text, rather JM than varchar(n)? Are there performance penalties? If your data will always fit into 255 characters (or less) then use a varchar, if you always know the exact length then use a char. Use the text range of data types if you need more storage space but still want to be able to run fulltext indexes etc. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- 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]
