Re: Error reading binlog - recoverable?
I tried mysqlbinlog for 5.0.18, but whether I use the --hexdump option or not, it still aborts with the same error: ERROR: Error in Log_event::read_log_event(): 'Event too big', data_len: 1635131433, event_type: 109 Could not read entry at offset 1384:Error in log format or read error This looks like it's past the end of the binlog file, or very near the end. Check to see if the file is larger than the location the slave is looking at. Is there anything else I can try? I'd consider doing the mysqlbinlog on the binlog file piped '|' into tail to see what the last log entry looks like. I've seen this kind of an error when the machine crashes and the whole binlog entry doesn't make it into the file. If this is the case you can either skip to the next binlog or try skipping the log entry via SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;START SLAVE; Brad Eacker ([EMAIL PROTECTED] ne [EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: merge table: speed benefits?
Tom Cunningham writes: (a) The docs say that spreading the underlying tables across different disks can make queries faster. I don't quite understand how this will work in a normal query: if I do a SUM(amount) over the entire table, will it be quicker if the table is spread across different disks? I don't see how mysql can avoid doing this sequentially. Tom, Multiple disks definitely helps, since in my case it allows me to have N times the available read speed and N times the available number iops (IOs/sec). In my case I have a 61GB merge table that is based upon 180 separate myisam tables. This table contains 487M records. The kinds of queries I'm doing, I would not be able to handle cleanly without merge tables on 4.1.15. Since trying to back this up would be a nightmare, I rely upon a standby master, and 3 query slaves for this data. This way things work reasonably, and I have some hope of surviving some kind of hardware failure. This is just one small portion of the substantial data set I'm responsible for over at Technorati. Brad Eacker ([EMAIL PROTECTED]) [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The Intel MySQL compiled Server
Josh Chamas [EMAIL PROTECTED] writes Dyego Souza Dantas Leal wrote: I want to use INTEL SHARED MySQL 4.0.25 - Pro with innodb tables on PRODUCTION SERVER DELL 2600 The Linux Kernel is 2.6.12 and the hardware is DUAL XEON 2.4 HT iwth 4 GB of RAM (i'm using raw partitions feature on innodb tables) These binaries tend to be faster, but also have some odd/rare bugs that don't occur with other binaries. So I would suggest if its speed you want use them, and if they seem to have a problem on your system, then revert back to the normal binaries. One thing I've noticed with Linux 2.6 is a lack of available free pages. With the current mechanisms in place searching for free pages tends to get short shrift. This seems to lead to a lack of available pages for use with the networking code. I've alleviated this somewhat by changing the swappiness value with echo 20 /proc/sys/vm/swappiness From what I've been able to understand from the tuning items I've seen, the most recent paging algorithms tend to favor swapping out large programs. H, sounds like mysqld, right? So changing this swappiness value seems to allow a reasonable number of free pages for various kernel needs. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: myisamchk error
Hank [EMAIL PROTECTED] writes: How do I find which record is duplicated (without doing the typical self-join query)? This table has 70 million rows, so that's not really feasible. select col, count(1) cnt from tab group by col having cnt 1; This will print out all duplicated col rows. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why NOT NULL in PRIMARY key??
Jigal van Hemert [EMAIL PROTECTED] writes: Because the SQL standard says so. A true observation, but still no explanation or reason why ;-P MySQL doesn't follow the standard in every situation, so that's not an excuse... (no offense!) There must be a good reason other than because our ancestors always did it this way. Let's look at it from a pure logic point of view. Given the table: create table a ( b int not null, c int null primary_key(b,c) ); With values: 1 null 1 null Logically these are unique records under the standard proviso that null != null. Yet how could I uniquely identify the first row to delete that row? Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How does a multi-row INSERT work?
Chris W. Parker writes: I searched the archives, looked through the manual, and searched google for info on how to actually perform a multi-row INSERT but didn't find an answer. The basic syntax is to separate the (...) with commas (,) ala: create table table1 (sku int, title varchar (20)); insert into table1 (sku, title) values (1,'A'), (2, 'B'); Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index on boolean column
Duan Pavlica writes: maybe this is a silly question but how useful it is to create indexes on columns containing only values 0 and 1 (true and false)? Most of the time I'd say such an index would not be real useful. If the distribution of this column's values is equally distributed between these 2 values, then you will be accessing the rows via an index for half the values. Index accesses in such a manner are usually more expensive in a table scan. The rule of thumb that I've used and seen is about 20% of the table. So if you have evenly distributed values within the column, you'd want a cardinality of at least 5 for this guideline to be fulfilled. Especially when it comes to large queries, I've seen an index based group by take 4 times as long as a full table scan query (31 hours vs 7 hours). Most likely due to the seeks that slow down reading the table from the disk. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT help
Gran Giddens writes: SELECT table1.title, table2.feature FROM table1, table2 WHERE (table1.sku = $table2.sku) AND table1.sku in ($sku1, $sku2, $sku3) ORDER BY FIELD(table1.sku, $sku1, $sku2, $sku3) ASC ... How can I run my query to get 3 results and if the feature is missing still return the table.title and NULL for the feature? This is a job for 'LEFT JOIN' :) Given this data from your described tables: mysql select * from table1; +--+---+ | sku | title | +--+---+ |1 | A | |2 | B | |3 | C | +--+---+ 3 rows in set (0.00 sec) mysql select * from table2; +--+-+ | sku | feature | +--+-+ |1 | a | |1 | aa | |2 | b | |2 | bb | |2 | bbb | +--+-+ 5 rows in set (0.00 sec) SELECT table1.title, table2.feature FROM table1 LEFT JOIN table2 using (sku) WHERE table1.sku in (1, 2, 3) ORDER BY FIELD(table1.sku, 1, 2, 3) ASC mysql SELECT table1.title, table2.feature - FROM table1 LEFT JOIN table2 using (sku) - WHERE table1.sku in (1, 2, 3) - ORDER BY FIELD(table1.sku, 1, 2, 3) ASC - ; +---+-+ | title | feature | +---+-+ | A | a | | A | aa | | B | bbb | | B | b | | B | bb | | C | NULL| +---+-+ 6 rows in set (0.04 sec) Take a look at the manual for 'LEFT JOIN' to see where I came up with this information. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Any means to get the optimizer out of the way?
Harrison Fisk [EMAIL PROTECTED] writes: The difference between the count(*) and the other query is that the real query has to use the datafile to retrieve the data when you are involving the actual columns. With the count(*) query it is using an Index only read, meaning that it doesn't have to the use the datafile at all to resolve it. If you do an EXPLAIN on the count(*) query, you should see a 'Using Index' in the Extra column. So it is using the index and estimating it is going to have to read 3885524 rows from the data file. Assuming the estimate is close, that will be an extra 3885524 disk seeks and reads to find the data for your query. That is why vmstat is showing the query doing much more disk i/o. Harrison, Thanks for helping me to better understand what the explain was telling me. Though I'm not sure it quite fits with the other data I collected from the strace of the mysqld that servicing my query. It looked to be doing a sequential pread, based on the record size of 9 bytes. This was one reason I felt the query was doing a table scan to fulfill the query. One interesting experiment I did was to try to do a summation query with and without an index. The query with an index too 31 hrs. While the same data set without the index took 7 hours. The only way you could improve this is to make a combined index across (member_id, pts_awarded) and get rid of the only (member_id) index. Then MySQL would be able to again use only the index to resolve the query. Keep in mind this would increase your index size by about a third, so it would take more diskspace and you would fit less into cache, so it would decrease response times slightly for the count(*) query. Thank you for this suggestion. I've dropped the original index and have added the composite index to the table. It increased the size of the index file by about 50% as expected, but the summation query seems to be doing a much better job at scanning the data in the index rather than in the index/table combination. We'll see how long this summation query takes at this point. Thank you for your help and explanations, Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bash powered MySQL Queries
I just wanted to know what would be the easiest way to retrieve simple data from a MySQL database from a bash script. Easiest way I've used to do it is: mysql EOQ select count(1) from tables; EOQ Which allows you to feed random queries to mysql from a bash script. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How-to copy a column
I have a table (table1) which has 4 columns, I want to copy all the contents of col1 into col2. Col3 is the primary unique key, so the copy has to keep the data matched with col3. Sounds like what you want to do is update table1 set col2 = col1; which will copy the contents of col1 in each record to col2. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: list of error codes
Note that this error list is for the Linux version (parts differs for another OS). More error descriptions can be found in the header files. (forgot currently which ones). If you search the forum for error codes and my name than you will find the info (roughly a year+ old) The typical place for the error codes is usually /usr/include/errno.h But that tends to be references to OS specific places. On Linux the actual numbers/mappings for i386 are in /usr/include/asm/errno.h Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1006: Can't create database
ERROR 1006: Can't create database 'foo'. (errno: 13) errno 13 is 'Permission denied' drwx--x--x 2 mysql root 4096 Sep 15 10:34 mysql locate the directory that contains the 'test' and 'mysql' databases. This will be the directory that you do not have permission to write in for mysql. This is based upon mysqld running as user 'mysql' Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert if Update failed without Select
Please also note hat UPDATE returns the number of records updated. If your UPDATE returns 0, you know that the record does not exist, and you might want to INSERT instead. There is one situation where the number of records updated will return 0, yet the row exists. If you update the record with the exact same information, mySQL will return a count of 0 rows updated. Yet the row exists. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select count
I have table account (see below). I need to get count of received,
count of send and cound of cancelled records. I know that I can do
this in 3 queries.
#1. select count(*) from account where status='received';
#2. select count(*) from account where status='send';
#3. select count(*) from account where status='cancelled';
How about:
select status, count(*)
from account where status in ('received', 'send', 'cancelled')
group by status;
Brad Eacker ([EMAIL PROTECTED])
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Excluding Rows
How do I exclude some rows in a table? I am merging columns from three
tables all of which show all congressional districts in all states. I
want to exclude those congressional districts in TX, PA and ME. My coding
that brings up data for all congressional districts is shown below.
How about using 'not in' ala:
where fh1109.state not in ('TX', 'PA', 'ME')
Brad Eacker ([EMAIL PROTECTED])
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import Excel data into table
Steve Grosz [EMAIL PROTECTED] writes: Can anyone tell me a good way to import individual column data into a table? Is there a tool to assist with this? If your data is a .csv file [a.csv] similar to: 1,Steve,Grosz 2,Brad,Eacker Consistent with the output from Excel. You could use LOAD DATA LOCAL INFILE 'a.csv' INTO TABLE Users FIELDS TERMINATED BY ',' (id,firstName,lastName); Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import Excel data into table [modified]
I forgot one element LOAD DATA LOCAL INFILE 'a.csv' INTO TABLE Users FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' (id,firstName,lastName); Brad ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sum() Function and NULL values
Is there any way to make sum() return 0 instead of NULL when one or more of the rows being sum()'d is null? Phrased another way, is there a way to make mySQL treat NULL as 0 when dealing with mathematical functions? You can use ifnull select sum(ifnull(points,0)) from abc; Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using Visio to diagram MySQL db, export SQL
Is there a version or a product available for LINUX? On Tue, 28 Sep 2004 13:55:19 -0500, Tim Hayes [EMAIL PROTECTED] wrote: ari MYdbAL which you can download at www.it-map.com is completely FREE and includes data modeling, DDL generation or whatever you need to create your MYSQL database. You might consider taking a look at DBDesigner4, which does a good job with a number of databases. http://www.fabforce.com/dbdesigner4 Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT question
Andre Matos writes: SELECT * FROM scan WHERE TimePointID = 3 AND ScanQCResult = 'n' AND (ScanStatusID 90 OR ScanStatusID 98); I realized latter analyzing this select that I made a mistake using OR at this point: (ScanStatusID 90 OR ScanStatusID 98), it should be AND. The second rendition (ScanStatusID 90 AND ScanStatusID 98) will return no rows every time. Since ScanStatusID can not be less than 90 and greater than 98 at the same time :) Something that I've found to improve readability of these kinds of tests is to use BETWEEN(a, b) And if you need to exclude a range the use of NOT BETWEEN(a, b). This way it becomes obvious what you are looking for. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems making a mysql.so file
Jough P writes: I upgraded from MySQL 3.23 to 4.0. I placed all the new mysql files in /usr/local/mysql/bin, /usr/local/mysql/include and /usr/local/mysql/lib. I compiled PHP5 and it is still using the 3.23 client API. So I removed all the old mysql files from /usr/bin, /usr/include and /usr/lib and tried again. No luck. When I built my PHP version I used: ./configure \ '--with-mysql=/usr/local/mysql4' \ '--with-apxs=/usr/local/apache/bin/apxs' This lets configure know which version of mysql to utilize. I've also built systems with both MySQL and Oracle should anyone need some help. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Repeat loops in mysql, large data issue, suggestions wanted
Scott Haneda writes: My trouble is that the data file could be 100,000 lines in length, I have a few options: Scott, I have a 30 million record dataset that I load into MySQL every couple of months. To do this propitiously I use the 'LOAD DATA INFILE' syntax. From your description it should be quite possible for you to utilize this same loading mechanism. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: optimizing inserts
Ron Gilbert [EMAIL PROTECTED] writes: It currently takes 15 or 20 minutes to run though a 10K to 20K GPS track logs. This seems too long to me. I took out the INSERTS to just to make sure it wasn't my PHP scripts, and they run in a few seconds without the MySQL calls. Doing a lot of inserts in this manner introduces considerable delays for each one as the information is sent across the connection, processed, and the result returned. Have you considered using LOAD DATA INFILE...? You can transfer across the entire data set into a temporary file and then use the LOAD DATA command of the temporary file. To make sure you haven't already loaded the data set you could do a single select on the first element of the data set, loading if there are no rows returned. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need Table Joins Example
Jigal van Hemert writes: Do you mean temporary tables? These are only necessary when there's no way to solve the problem with a join. Actually a temporary table can be used with a join to do what is usually knows as a sub-select or sub query. In this fashion you select the elements that would normally be part of an 'IN (select ...)' into a temporary table. Then join the selection table with the newly created temporary to get the final data set desired. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another Stupid Newbie Question ^.^
Query failed : You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'index = 21' at line 3 I believe 'index' is a reserved word, usually used for creating indexes. Using it as a field in a table could quite likely cause confusion for mysql, if not also for someone trying to read the code :) Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup strategy
You may wish to also look into replication, which is a cinch to setup with MySQL. Unfortunately replication does not handle point in time recovery. This is usually required to happen when someone accidentally drops a table or deletes too many rows from the database inadvertently. Under replication these changes will be dutifully applied to the replica. One mechanism would be to mirror the data disks, raid-1. This would provide the necessary reliability, but again will not account for user mistakes. Best bet is to utilize one of the backup strategies to make a copy of the data in a reasonable fashion. And this may also require replication so the actual backup may happen from the replica without unduly effecting the primary. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Html and mysql..
Josh Trutwin writes: Javascript is a client-side language, the code is executed by the user's browser. It has no way to connect to the database server and run queries so you need to use a server-side programming language like Java (JDBC), PhP, Perl, etc. Tomcat is a decent servlet engine with a nice price tag (free) and PhP/Perl are pretty easy to get working with an Apache server. If you've never done this before, I'd advise to use PhP as it has the smallest learning curve in my opnion. It might be possible to build a backend php or cgi script that could act as the backend access for JavaScript. But it would definitely be a kludge and fraught with security issues. Mainly because you'd be sending the requests, authorization, and responses across the net. Rather than just across the connection between the web server and the database server. I have a PHP script that I've been hacking around with that I can send a random SQL statement and get the results displayed in a basic web page column headings and all :) It isn't real pretty, but does allow me to test out SQL and my skills with PHP. Would I allow such access via HTTP, not by a long shot. It's just too fraught with security issues. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Complicated query problem
The query as written works just fine although I'm certain there's got to be a more efficient way of doing the same thing. I'm relatively new to MySQL so I took the brute force approach. My problem is that I want to produce totals of each of the columns and can't figure out how to do it. Any suggestions on how I can do this? Might I suggest a mapping table relating speed to the labels desired and the speed ratings (int - strings). That way it's extensible and can easily be joined to the original query? Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database structure
The schema is : Patients(#patient_nr,name,etc...) Assessment(#assessment_nr, #patient_nr, assessment_type, other usefull values). Assessment_types(assessment_type, labtest_nr) An assessment is composed of different tests, let's say assessment type 1 is composed of lab test 1,2,3,5 and assessment type 2 of lab test number 10 to 70. Looking at the two alternatives, the second based upon a normalization of patient with assessments fits closer with standard medical identification. The assessments are usually based upon the ICD-9 coding. As an example 191.3 is a Neoplasm of the brain, Parietal lobe. There are corresponding elements for procedures ala 01.2 is a Craniotomy and craniectomy code with subsequent digits further describing the particular procedure. Using these codes provides an industry standard mechanism. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create table results in (errno: 121)
ERROR 1005: Can't create table './TNMailServer/TNSession.frm' (errno: 121) This is on version mysql-standard-4.0.18-pc-linux-i686 If this is on a typical linux box, errno 121 is #define EREMOTEIO 121 /* Remote I/O error */ typically relate to an NFS mounted file system. Does the MySQL server have permission to write on this mounted file system? Or might there be an issue with the way the remote system is mounted? Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding configure command after installation
I just recompiled mysql and I am wondering if there is something like in php (phpinfo();) where you can see the configure command after the db is installed. It would just be nice to have that in a later time, or even to make sure that the new version has replaced the old one. The initial portion of config.log in the directory where the source was built contains the initial config line: It was created by configure, which was generated by GNU Autoconf 2.53. Invocation command line was $ ./configure --prefix=/usr/local/mysql4 Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql MAtch against query help
I have a query that searches my database for people with C++ on their resume . ... Hehe I've also had a problem with searching for something like it's , anything with a single quote doesnt return anything, maybe try adding a slash , C\+\+ ?? heheh maybe i'm wrong, it could be a limitation. Couldn't say for sure, but the '+' is definitely a special character in regexp syntax. Another possibility is how text gets broken down into tokens. The '+' symbol will likely be separated from the C when parsing the text into tokens. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best practice on table design
Cities (CityID, Name) People (PersonID, Name) Travel_Exp (ExpID, Date, PersonID, Per_Diem) Travel_Exp_Cities (CityID, ExpID) Based on the descriptions I'd tend to go with a normalized table set of this nature: Cities (CityID, Name) People (PersonID, Name) Travel_Exp (ExpID, Date, PersonID, CityID, Exp) This provides consistent use of person and city. Along with gathering related data into the same record. It is doubtful that an expense would reference more than one person or city. Normalizing to this table set provides a simple means of querying related data, without undue duplication of data elements with the possibility of errors creeping in during the data input. But these observations are based upon my own common sense view of the kinds of business rules/processes that are likely to be used. If your business processes would not follow the described mechanisms, say you do indeed share travel_expenses between individuals, or the expense can be across cities, the normalization I've described would not fit. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: free software and open source
Can anyone in your own words clarify the difference between open source and free software. Interesting question, though you missed one other label 'public domain'. These are all interesting elements of who controls the source and to what extent. MySQL as I understand it, allows the source to be distributed with the proviso that if it is utilized in a for profit manner a license is necessary. This comes from either charging for the distribution, or for use by a company to make money. I will hope that the people at MySQL will correct me if I am mistaken in this opinion. Open Source, usually based upon the GPL means that the source can be distributed, but the source must be made available to someone you've given binaries to, if asked. This would also include any incorporated changes made before distribution. Public domain means you can do anything you want with the source or binaries. Including modifying, distributing, or selling, the original or derivatives. Most source is at least copyrighted to prevent someone claiming what is not rightfully theirs. But public domain does not fall in this category. And copyright is inherent to the creation, unless released to the public domain due to expressed permission, or copyright expiration. Please be aware these are my own opinions, and not any form of legal advice. If you are looking for such legal advice, please get advice from someone in the legal profession (a lawyer :) My views are based upon my own experience with the libdbf library that I created for accessing and manipulating dBase files/indexes back in '90. Which I have copyrighted yet have seen no compensation from during that entire period. Not sure if anybody even uses them any more? Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Better Solution than Multiple Queries?
Tim McDonough writes: The solution I presently have does a query for the first criteria. Then, I loop through the results of that query and do another query for each returned row. This produces the desired results but requires a lot of queries, i.e.-- if the first query returns 1000 customers then I make 1000 additional queries to get the remaining info if it exists. Another possible solution would be to create the secondary queries utilizing the IN (...) criteria? You would run the first query, then build one or more queries using the returned values as references for the IN portion of the second query. This would allow you to have a much smaller number of secondary queries. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Am I doing things right? (selecting groups of objects problems)
Lecho [EMAIL PROTECTED] writes: I have a db with objects table, each of those objects may belong to groups of objects. The number of groups can be about 256 and an object belongs from one to many different groups at once. Lecho, I threw together the following tables/data/queries that I believe handle your setup: create table objs ( obj_id int, obj_namevarchar(20) ); create table grps ( grp_id int, grp_descvarchar(20) ); create table grp_map ( obj_id int, grp_id int ); insert into objs (obj_id, obj_name) values (1, 'Obj1'); insert into objs (obj_id, obj_name) values (2, 'Obj2'); insert into objs (obj_id, obj_name) values (3, 'Obj3'); insert into objs (obj_id, obj_name) values (4, 'Obj4'); insert into grps (grp_id, grp_desc) values (10, 'Grp10'); insert into grps (grp_id, grp_desc) values (20, 'Grp20'); insert into grps (grp_id, grp_desc) values (30, 'Grp30'); insert into grp_map (obj_id, grp_id) values (1, 10); insert into grp_map (obj_id, grp_id) values (1, 30); insert into grp_map (obj_id, grp_id) values (2, 20); insert into grp_map (obj_id, grp_id) values (2, 30); insert into grp_map (obj_id, grp_id) values (3, 10); insert into grp_map (obj_id, grp_id) values (3, 20); insert into grp_map (obj_id, grp_id) values (3, 30); insert into grp_map (obj_id, grp_id) values (4, 30); select distinct(obj_id) from grp_map where grp_id in (10, 20); mysql select distinct(obj_id) from grp_map - where grp_id in (10, 20) - ; ++ | obj_id | ++ | 1 | | 2 | | 3 | ++ 3 rows in set (0.00 sec) This will tell you all the objects that belong to groups 10 or 20 Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Learner Here Getting Frustraighted
Barry Smith writes: i have 2 tables one pet containing petName and petType other table is color containgin petName and petColor. The code which i keep getting errors on is: Select * from pet outer join color using (pet.petName=petcolor.petName) ; Unfortunately petcolor is not a table. Based upon your prior information I think you are looking for: Select * from pet left join color on pet.petName=color.petName; Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Perl Modelues
it mentions that the easiest way to install Perl DBI is to use CPAN. However when I go to the link provided in the documentation http://search.cpan.org, I can't figure out how to find the Perl module. Can someone show me the light. I'm not sure about the light, but when I typed in DBI in the search box at http://search.cpan.org and hit the search button it displayed a list of links and descriptions, the first of which was DBI. You can then either go to the descriptions DBI link or to the DBI-1.42 link just below it to get to the source directly. Brad Eacker ([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 creating table...
Marvin Cummings writes: I attempt to create this table from the command line and get the following error: Marvin, the use of the ' [single quote] appears to be your problem. I've been able to create the table on 4.0.18 using the following syntax: CREATE TABLE nuke_zc_ads ( ad_id smallint(5) unsigned NOT NULL auto_increment, cat_id smallint(5) unsigned NOT NULL default 0, user_name varchar(20) NOT NULL default '', email varchar(50) NOT NULL default '', price text NOT NULL, condition varchar(10) NOT NULL default '', city varchar(20) NOT NULL default '', state varchar(20) NOT NULL default '', country varchar(20) NOT NULL default '', lastup_date int(11) NOT NULL default 0, subject text NOT NULL, descript text NOT NULL, url text NOT NULL, views int(11) NOT NULL default 0, paypal char(3) NOT NULL default 'No', add_date int(11) NOT NULL default 0, exp_date int(11) NOT NULL default 0, PRIMARY KEY (ad_id) ) TYPE=MyISAM AUTO_INCREMENT=1 ; Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data help
David McBride [EMAIL PROTECTED] writes: Thanks so much, that did the trick. I really appreciate the mercy on a poor newbie. David, You're quite welcome. Now if I could find an employer willing to pay me for utilizing the 23 years of knowledge I used to provide such a solution :) Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data help
Thanks for the reply. I was a little confussed on the exact defintion of a line, I thought it meant a whole row of data. The space seperated fields worked great, but still gave me the error: ERROR 1054: Unknown column 'col1' in 'field list' I could not find anywhere on how to define the field list. In my example, col1 was just a place holder for the first column you wish to load data into. This info will come from the mysql command DESC persondata; This will give you a list of the columns which you can then use to specify which data element from the line you want to go into each column from the table. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data help
My question is, how can I take a log file that has 25 columns of data and tell mysql to only load column 1, column 3, and column 7 from the raw log file? I'm not sure mysql can do this. I'd be more inclined to use cut on a Linux system in the following fashion: cut -d ' ' -f 1,3,7 in.dat out.dat then use out.dat as the file spec for the 'Load Data INFILE' Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data help
David McBride writes: I need to load data from a log file. The file is a space seperated file. I can already ignore the first 7 lines (that are commnet lines), but what I can not seem to do is: 1. get load data to use the space seperated format. 2. only load certain columns. I tried: mysql LOAD DATA INFILE 'persondata.txt' - INTO TABLE persondata (col1,col2,...); David, Based upon the manual for 4.0.17 the following syntax would fit your description: LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata FIELDS TERMINATED by ' ' (col1,col2,...); You may need the LOCAL key word if you want the client to read the data file. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to diagnose MySQL syntax error
Ken Elder writes: Today I experimented on two computers. One consistently got the error message; the other consistently did not get the error message. Both computers were Win98SE with IE6.0. Their Win 98 and IE settings were identical (except for trivial stuff like home page and mouse controllers). They are on the same network. I would take a look at the logs for the web-server and see if there are differences between the two accesses that show up in the error or access logs. You may also want to check their DNS settings. If you are accessing two different machines due to different name - ip mappings you could see completely different responses. The return from the DNS reverse lookup could also cause some errors within the accesses. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP script cannot connect MySQL server
Sami Maisniemi [EMAIL PROTECTED] writes: It seems that the correct socket is used. Here is the output: unix 2 [ ACC ] STREAM LISTENING 3303 private/relay unix 2 [ ACC ] STREAM LISTENING 3307 public/showq ... I tried to connect to socket #3307 as follows: $link = mysql_connect(localhost:3307, root, passwd); Sami, Could you verify that you can connect with mysql via mysql -h localhost -P 3307 If we have identified the proper host/port you should receive the standard 'mysql ' prompt. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select statement not working in a php page
I am trying to get a single result from a database, which works fine at the sql command line. I have tried several versions of code, this being the most recent - ... No matter what I do I do not get the desired max id number from the column WarrantyID. The same query works fine at the sql command line. What am I doing wrong? Chip, Are you getting any output at all on the browser? You may want to check the error output on the web server. It may have some information as to what could be going wrong. Alternatively you could run the script from the php program that is likely to be available. It will put out what looks like the HTML source for the page, but will point out errors if there are any. Another possibility is that the connect is not working properly due to access rights/limitations. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP script cannot connect MySQL server
I finally managed to install MySQL succesfully. I created a simple DB with two different tables. I also created a simple PHP script to list all DBs and tables. However, it seems that the PHP script cannot access MySQL server, because the following error message is displayed: Warning: mysql_connect(): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /srv/www/htdocs/sqltest.php on line 4 This error could result from a number of possible problems. First off I would check to make sure the mysqld is running and attaching to the default socket (3306). If you have it set up otherwise, you will likely need the connect to contain the ':3307' socket adjustment to the name of the host being sought. I have validated that the rest of your logic is correct once the connection is established. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP script cannot connect MySQL server
It seems that mysqld is up and running (view the results below), but could
you
specify how to check the socket, please?
Based on your ps information, the socket will likely be in the
/usr/local/mysql/data/my.cnf file under the [mysqld] area. Another way
to tell if there is a socket open for the server would be to use
netstat -a | grep 330
Which would show a line similar to this:
tcp0 0 *:3307 *:* LISTEN
with the port I'm using {3307} showing up as currently in use, tcp
protocol with a listener {LISTEN} connected to the socket. Hopefully
the port specified in the my.cnf will show on the netstat output as
described above.
Brad Eacker ([EMAIL PROTECTED])
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP script cannot connect MySQL server
Sami Maisniemi writes: I think I am using root access, but should I create another user name for MySQL? On the command line, I start MySQL by typing just 'MySQL'. How can I view the user? from the mysql program: mysql use mysql mysql select User,Host,Password,Select_priv from user; which will show basic access capabilities for user/host/password mysql select Host,Db,User,Table_Name,Table_priv from tables_priv; will show similar information on a db/table basis for the host/user combinations. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP script cannot connect MySQL server
tcp0 0 *:3307 *:* LISTEN It seems that the correct socket is used. Here is the output: unix 2 [ ACC ] STREAM LISTENING 3303 private/relay unix 2 [ ACC ] STREAM LISTENING 3307 public/showq Looks like 3307 is indeed open, so you may want to modify your mysql_connect to use localhost:3307 - since that is likely to be the port mysqld is using based upon this output. The default is usually 3306, so if you do not include the :3307 port reference you may very well not be able to connect. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP script cannot connect MySQL server
Sami Maisniemi [EMAIL PROTECTED] writes:
mysql use mysql
mysql select User,Host,Password,Select_priv from user;
which will show basic access capabilities for user/host/password
Hmmm ... did not work as you can see:
mysql select User,Host,Password,Select_priv from user;
ERROR 1054: Unknown column 'User' in 'field list'
H, that's interesting. You'll probably need to take a look
at the 'desc user;' output. Mine reports the following initial
portion:
mysql desc user
- ;
+---+---+--+-+-
+---+
| Field | Type | Null | Key |
Default | Extra |
+---+---+--+-+-
+---+
| Host | varchar(60) binary| | PRI |
| |
| User | varchar(16) binary| | PRI |
| |
| Password | varchar(16) binary| | |
| |
| Select_priv | enum('N','Y') | | | N
| |
...
If yours does not report Host and User as the first two columns, your user
table has been corrupted in some manner. You haven't tried to define your
own user table have you? If so, that may be what is causing the errors.
Brad Eacker ([EMAIL PROTECTED])
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie: increment an existing record
Is there a mysql command that will increment the value of a field in an existing row? I would think this would be pretty basic, but I can't seem to find anything in the online documentation. update t1 set f = f + 1 Unfortunately that will update all the records in the database. It's likely he would prefer to update a particular record via update t1 set f = f + 1 where id = 1234; Which will update only the record matching the id of 1234. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update using fields from another table
Table 1 id_2 | date Table 2 id_1 | id_2 Table 3 id_1 I want to set the table1.date = '2004-03-18' for each record in table3. Any ideas? Based upon your description you could probably use: UPDATE table3, table2, table1 SET table1.date = '2004-03-18' where (table3.id1 = table2.id1) AND (table2.id2 = table1.id2); Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hierarchical data design
In a simple tree, one can easily see that using the title of a node as it's primary key is not smart... names can easily collide: The names can definitely collide. But under a file system paradigm the combination of name with parentID will be unique and define the tree structure you mention: create table nodes ( nodeID integer NOT NULL PRIMARY KEY, parentIDinteger NOT NULL, namechar(50), INDEX p_id_idx (parentID) ); example to list a directory from this: SELECT names FROM nodes WHERE parentID = 1234; Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BETWEEN
Keith writes: i'm looking for a way to do two BETWEEN ranges. Currently I have sys.sectorID BETWEEN 1 AND 20 but I want it so that I can search between 1 and 20 and also between 30 and 42 but all my efforts net an error and the manual doesn't go into a lot of detail. If there's a faster way than BETWEEN then BETWEEN can be accomplished with = coupled with 'and'. Using your examples: SELECT sys.sectorID from sys where (1 = sys.sectorID and sys.sectorID = 20) or (30 = sys.sectorID and sys.sectorID = 42) ; Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQ puzzle
It is interesting to see the various solutions being proposed. Though one item missing is the inclusion of a 1 year old child. It is likely this will be the telling point since it provides one of the possible limiting criteria not mentioned. Most buildings have an even number of windows. So one of the few ways to get an even sum is to utilize the 1 with respect to the other pair of possible ages. What I see as likely candidates are (1, 3, 12) and (1, 4, 9). Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to install data on a RAID HDD??
I have no problem installing MySQL on the main HDD but can not figure out what changes are needed so all the data goes to RAID disks. I am a newbie so please, give direction!! The easiest way to do this would be to move the data directory and all its contents onto the RAID disks. Then symlink to that directory from the original location. Example only from basic source install: mv /usr/local/mysql/var /hdRAID/var ln -s /hdRAID/var /usr/local/mysql/var Please make sure that mysql is shutdown before doing this task. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to install data on a RAID HDD??
Kirti S. Bajwa [EMAIL PROTECTED] wrote: Worked like a charm. Thanks. You are a life saver. The easiest way to do this would be to move the data directory and all its contents onto the RAID disks. Then symlink to that directory from the original location. Example only from basic source install: mv /usr/local/mysql/var /hdRAID/var ln -s /hdRAID/var /usr/local/mysql/var You're quite welcome. Now if I could just find a company in need of these skills :) Anyone in the SF Bay Area know of any openings that could use someone skilled in handling RDBMS tasks? My background includes 9 years of RDBMS experience - mySQL, Oracle, Informix, Sybase, SQL-server, 8 years of web/back-end integration PHP, perl/CGI, and 13 years of Unix kernel internals. 22 years using C and 20 years of perl history (3, 4, and 5). Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie question
Bernard Clement [EMAIL PROTECTED] writes: Your problem is too much experiences not the lack of skills. I am in the same situation...it is very frustating. Bernard, It appears I'm not the only one :( Though such an observation makes me wonder if it may be a case of age discrimination being a possibility? One would hope this would not be the case since I am only 44. But I have to wonder? Thank you for the response, Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL versus MS SQL
Chris Fossenier writes:
... Query 1
a1.phone_pander_flag 'Y'
AND state.state = 'PA'
AND ( h1.homeowner = 'Y'
OR h2.probable_homeowner IN ('8','9')
OR h2.homeowner_probability_model BETWEEN '080' AND '102' )
AND ( p1c.exact_age BETWEEN '40' AND '60'
OR estimated_age BETWEEN '40' AND '60' )
AND a1.phone is not null
AND p1a.first is not null
AND p1a.last is not null
AND a3.address is not null;
Chris,
Have you been able to get an 'explain plan' on the query? I'd
estimate that the combination of state (1 of 50), exact_age (20 of 80),
and estimated_age (20 of 80) should reduce you possible record search
from the original 160M to 200,000 (1/50 * 20/80 * 20/80 * 160M) if all
these indexes can be utilized. The real question is what MySQL chooses
to use as far as indexes are concerned.
Brad Eacker ([EMAIL PROTECTED])
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Improving seek/access times -- does RAID help?
Can anyone tell me whether or not some kind of RAID will improve the seek/access times during lots of random reads from, say, MyISAM data files? I *do not care* about improved [sequential] transfer rates; I want the fastest possible random access. RAID will only help reduce the average random access time not an individual random access. This would require you to have a large number of accesses/sec, with multiple accesses in progress at the same time. If you are dealing with a single-threaded type situation I doubt that RAID will help the situation much. The read-ahead most disks provide may prove useful depending on how much of the data actually gets scanned. But large requests are where this excells, typical of a table scan should it occur. For random small reads, like an index access, you would need many of them in progress simultaneously to get real benefit from RAID. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AW: C compared to C++/Java; Was: Re: InnoDB Hot Backup + MySQL em bedded?
I prefer TCL because on my opinion it is the best of both worlds ( i never had a memleak except with a bad API written in C). Bad...written in C...was it a SCO library? Complex tasks should be done from skilled programmers - thats all. Additionally, complex tasks should be decomposed into simple tasks by skilled programmers. :-) Trying to turn this discussion back toward MySQL, a language not mentioned here that has withstood the test of time is PERL. When looking at the results coming from and RDBMS, it is basically a list of data. This is where PERL excels, dealing with text and manipulating it. Showing my age, I will state that I have been using C for over 21 years, and PERL almost as long. When it comes to dealing with speed, C is likely to be the best candidate since it is just a small step above the assembly language that would be the fastest, but least maintainable. I am somewhat familiar with this due to my 13 years working inside the Unix kernel at various companies. Coupled with 9 years of RDBMS experience, including a 200 GB Oracle database back in '95, I have dealt with many different programming tasks. Bottom line on all this is using the right tool for the job. As an analogy, it is quite possible to remove a switch plate from the wall with a claw hammer. But the results aren't real pretty. :) Knowing which tool to utilize, is where experience and skill comes into play for the programmer worth his salt. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0.17 - Still no SSL joy
Greg G [EMAIL PROTECTED] writes: gcc -DHAVE_CONFIG_H -I. -I. -I.. -I./../include -I../include-O3 -DDBUG_OFF -c `test -f strxmov.c || echo './'`strxmov.c In file included from strxmov.c:33: ../include/my_global.h:1127: openssl/opensslv.h: No such file or directory This indicates to me that the configure script needs to be run with the --with-openssl-includes=DIR Where DIR is the location of the include directory from your openssl installation. DIR in this case may need a symlink such as: ln -s ../openssl/include /usr/local/include/openssl then DIR would be '/usr/local/include' so the openssl/opensslv.h include will be found properly. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0.17 - Still no SSL joy
I shouldn't need to make a symlink in /usr/local/include. That may be the case, but the only way you will satisfy the #include in the my_global.h file is to either pull the 'openssl/' from in front of opensslv.h, or to have a symlink in the openssl include directory for openssl to '.'. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Oracle date
The kind of processing you desired is easily accomplisches
with the following perl program:
#!/usr/bin/perl
while (STDIN) {
if (/TO_DATE/) {
s/TO_DATE/STR_TO_DATE/;
s/(..)\/(..)\/()/$1-$2-$3/;
s/MM\/dd\//%m-%d-%Y/;
}
print $_;
}
[EMAIL PROTECTED] cat Landon.odat
insert into log_book values
(TO_DATE('08/12/1973','MM/dd/'),'C150','N5787G',1,1.8);
[EMAIL PROTECTED] ./Landon.pl Landon.odat
insert into log_book values
(STR_TO_DATE('08-12-1973','%m-%d-%Y'),'C150','N5787G',1,1.8);
Brad Eacker ([EMAIL PROTECTED])
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Possible benchmark for mySQL?
Hello, I'm in the midst of using mySQL for some genetic information searching based upon the GenBank data from the NCBI, National Center for Biotechnology Information. In doing some testing on using mySQL, and began to wonder if this data set would be of interest as a benchmark for the database? The following information was taken from a recent run at loading in a portion (500k records) of the data. The full data set has almost 30M records so would not likely be pleasant to store and/or distribute. But the data is publicly available and substantial. Please take a look at the timings on some of the activities shown below. Brad Eacker ([EMAIL PROTECTED]) Load in the data (500,000) rows mysql create table gb_locus ( - gbl_id int primary key, - gbl_fileID int, - gbl_locus varchar(20), - gbl_sizeint, - gbl_datedate, - gbl_phylum char(3), - gbl_foffset int - ); Query OK, 0 rows affected (0.00 sec) mysql load data infile '/hda3/beacker/gene/genbank/a' into table gb_locus - fields terminated by ','; Query OK, 50 rows affected (10.58 sec) Records: 50 Deleted: 0 Skipped: 0 Warnings: 0 Storage used: -rw-rw1 mysqlmysql18141068 Dec 3 20:03 gb_locus.MYD -rw-rw1 mysqlmysql 4098048 Dec 3 20:03 gb_locus.MYI Access data: mysql select gbl_phylum, count(*) from gb_locus group by gbl_phylum; ++--+ | gbl_phylum | count(*) | ++--+ | BCT| 210778 | | CON|11472 | | EST| 277750 | ++--+ 3 rows in set (6.83 sec) Raw data: [EMAIL PROTECTED] genbank]$ ls -l a -rw-rw-r--1 beacker beacker 25758542 Dec 3 17:33 a -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Large data set load and access
Folks, Just completed an interesting task utilizing mySQL 4.0.16. The database I'm creating is some summary information from the GenBank info from the NCBI. I must say that I am quite impressed by the performance that I am seeing. The data set is pretty substantial, consisting of almost 30M records. Yet it was loaded from the text file in less than 6 minutes. The text file itself is about 1.6GB in size. An aggregation of the data also took just under a minute as shown in the information I've included with this message. I was also impressed by the time to select a particular record without the use of an index on the column being selected upon. I'm sure that would change once I create an index on this column. For information, this test was done on a 1300 MHz RH Linux 7.3 system with 896MB of memory, and WDC ATA drives. Not the top of the line machine, but pretty respectable. Brad Eacker ([EMAIL PROTECTED]) Particulars: mysql create table gb_locus ( - gbl_id int primary key, - gbl_fileID int, - gbl_locus varchar(20), - gbl_sizeint, - gbl_datedate, - gbl_phylum char(3), - gbl_foffset int - ); Query OK, 0 rows affected (0.00 sec) mysql load data infile '/hda3/beacker/gene/genbank/gbl_locus.txt' - into table gb_locus fields terminated by ','; Query OK, 29830869 rows affected (5 min 44.68 sec) Records: 29830869 Deleted: 0 Skipped: 0 Warnings: 0 Input file information: [EMAIL PROTECTED] genbank]$ ls -l gbl_locus.txt -rw-rw-r--1 beacker beacker 1583781135 Dec 4 13:41 gbl_locus.txt Aggregation select: mysql select gbl_phylum, count(*) from gb_locus group by gbl_phylum; ++--+ | gbl_phylum | count(*) | ++--+ | BCT| 210778 | | CON|11472 | | EST| 18836635 | | GSS| 7585521 | | HTC| 148411 | | HTG|68390 | | INV| 186924 | | MAM|52858 | | PAT| 1345394 | | PHG| 2396 | | PLN| 368927 | | PRI| 302997 | | ROD| 115600 | | STS| 257403 | | SYN|10988 | | UNA| 1093 | | VRL| 203738 | | VRT| 121344 | ++--+ 18 rows in set (59.74 sec) [EMAIL PROTECTED] gene]# ls -l gb_locus* -rw-rw1 mysqlmysql8766 Dec 4 13:57 gb_locus.frm -rw-rw1 mysqlmysql1075530216 Dec 4 14:04 gb_locus.MYD -rw-rw1 mysqlmysql244406272 Dec 4 14:04 gb_locus.MYI Single record selections: mysql select * from gb_locus where gbl_id = 1400; +--++---+--+++-+ | gbl_id | gbl_fileID | gbl_locus | gbl_size | gbl_date | gbl_phylum | gbl_foffset | +--++---+--+++-+ | 1400 | 310212 | AL556818 | 1027 | 2003-05-31 | EST|41517291 | +--++---+--+++-+ 1 row in set (0.04 sec) mysql select * from gb_locus where gbl_locus = 'AL556818'; +--++---+--+++-+ | gbl_id | gbl_fileID | gbl_locus | gbl_size | gbl_date | gbl_phylum | gbl_foffset | +--++---+--+++-+ | 1400 | 310212 | AL556818 | 1027 | 2003-05-31 | EST|41517291 | +--++---+--+++-+ 1 row in set (45.69 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Large data set load and access
So, if you were willing to give up a little storage space, make the gbl_locus field a Char(20) instead of a varchar(20) and see if it speeds things up. I found noticable speed increase in my selects doing this. Thanks for the heads up on this. Unfortunately the only varchar is the gbl_locus field, so I'm not sure how much this would by me for the space. Thanks again, Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
