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

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 dis

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 feat

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.

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 a

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)); in

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 thes

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

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

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

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

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 pla

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

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 w

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.mysq

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

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.

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(*) fr

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;

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 >> MYS

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

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

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 shou

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 ins

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 wo

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 lik

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. An

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

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 replica

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 a

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

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.

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 port

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

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, Ci

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 b

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 customer

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

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 >, 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

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 smalli

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 :)

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,

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 "f

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: my

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 contro

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

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

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

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

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 th

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: m

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 c

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.

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.

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:

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 th

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

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

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 sym

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 BETW

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 be

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 int

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 r

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 '.'.

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

Re: Oracle date

The kind of processing you desired is easily accomplisches with the following perl program: #!/usr/bin/perl while () { if (/TO_DATE/) { s/TO_DATE/STR_TO_DATE/; s/(..)\/(..)\/()/$1-$2-$3/; s/MM\/dd\//%m-%d-%Y/; } print $_; } [EMAIL PROTECTED] cat Landon.

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_l

Large data set load and access

t; 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 fie

Possible benchmark for mySQL?

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 t