Count() within Join

2004-08-05 Thread Scott Haneda
Mysql 4 SELECT c.campaign_name, count(*), c.id FROM addresses as a INNER JOIN addresses_incampaign as i on (a.email_address = i.email_address) INNER JOIN campaigns as c on (i.campaign_id = c.id) WHERE a.user_id = "1" AND i.user_id = "1" GROUP BY c.id ORDER BY c.id This gives me FooNmae

Table query and column overlap

2004-08-05 Thread Remember14a
Dear Friends, I am using mysql 4.0.17-nt I have pasted structure of table below, while managing through command prompt using sql without GUI. Once I use without GUI, via command prompt select * from quiz to see contents of full table in each column I get overlap of columns as pasted below,

Re: Question

2004-08-05 Thread Rhino
- Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, August 05, 2004 7:05 PM Subject: Question > Dear friends, > > I am using mysql 4.0.17-nt with php 4.3.3 > > In mysql tables, I can write to columns of tables with varchar, int etc etc > datatypes, ho

Re: Question

2004-08-05 Thread eoghan
In mysql tables, I can write to columns of tables with varchar, int etc etc datatypes, however text datatype columns I am not able to write. you mean from some admin gui? just use sql... insert into table_name text_field values 'lots of text' etc -- MySQL General Mailing List For list archives: h

Question

2004-08-05 Thread Remember14a
Dear friends, I am using mysql 4.0.17-nt with php 4.3.3 In mysql tables, I can write to columns of tables with varchar, int etc etc datatypes, however text datatype columns I am not able to write. Any guidance, please.

Re: Is LOAD DATA INFILE an atomic operation?

2004-08-05 Thread Paul DuBois
At 14:45 -0700 8/5/04, Sergei Skarupo wrote: Hi everyone, In MyISAM tables, can LOAD DATA INFILE be considered an atomic operation under normal circumstances? What will happen if the server crashes or someone kills the thread? It's not atomic. The records loaded prior to the error will be in the

Re: problem with 4.1.3

2004-08-05 Thread Jeff Mathis
yes indeed. I will have to change my column definitions. However, this behavior was not the case with 4.0.4. What seemed to be going on with that branch was I could have an 11 digit number, with a maximum of 9 digits behind the decimal. so numbers like 100493.43 were fine. I'm assuming that this

Is LOAD DATA INFILE an atomic operation?

2004-08-05 Thread Sergei Skarupo
Hi everyone, In MyISAM tables, can LOAD DATA INFILE be considered an atomic operation under normal circumstances? What will happen if the server crashes or someone kills the thread? Thanks, Sergei

Re: problem with 4.1.3

2004-08-05 Thread Dan Nelson
In the last episode (Aug 05), Jeff Mathis said: > after lokking at this, it appears that our float(11,9) columns cannot > store an number larger than 100 or smaller than -100. the database is > rounding the number! If we insert numbers -100 < x < 100, then its fine. > > is there a configuration

Re: problem with 4.1.3

2004-08-05 Thread Jeff Mathis
well, it appears that you are quite correct. changing the column definition to float(11,3) for example now does the correct thing. sean c peters wrote: This is not a bug, its behaving exactly as it should. When you specify a float(11,9) - you're saying an 11 digit number where 9 are after the de

InnoDB slowness

2004-08-05 Thread sean c peters
Hi all, I am in the process of upgrading one of my systems to use InnoDB tables, along with some other tweaks to my code. In any case, i just tried to delete around 7000 records from a table, where there are 9 other tables that will cascade delete when rows from the other table are deleted. Over

Re: problem with 4.1.3

2004-08-05 Thread Jeff Mathis
after lokking at this, it appears that our float(11,9) columns cannot store an number larger than 100 or smaller than -100. the database is rounding the number! If we insert numbers -100 < x < 100, then its fine. is there a configuration setting somewhere, or is this a known bug? thanks jeff Jef

Re: Insert problems with InnoDB (big table)

2004-08-05 Thread Harrison
Hi, A few more ideas you can try: 1. SET UNIQUE_CHECKS=0; You have a unique key that is quite large (model, id name). If you know the data is already unique (ie. importing from another data source), then this can speed up the import *a lot*. 2. SET FOREIGN_KEY_CHECKS=0; You didn't mention i

Re: Why drop a index takes so long to do?

2004-08-05 Thread Dan Nelson
In the last episode (Aug 05), Jeremy Zawodny said: > On Thu, Aug 05, 2004 at 11:27:40AM -0700, Haitao Jiang wrote: > > I have a 3GB index which includes a fulltext index on 2 columns. I > > wanted to drop this fulltext index from the table, but it took > > forever. Why it is so slow? Is it because

problem with 4.1.3

2004-08-05 Thread Jeff Mathis
after upgrading from 4.0.4 to 4.1.3, we noticed that some of our float(11,9) columns refused to store anything other than the numer -100 or the number 100, even though the insert or update sql clearly specifies other numbers. We've tried dropping the table and reloading the data -- same problem

Re: BLOB columns

2004-08-05 Thread Wesley Furgiuele
I tend to use LOAD_FILE(), but your file has to be on the server and readable by MySQL ("INSERT INTO table ( field1, field2 ) VALUES ( 'a', LOAD_FILE( 'filepath' )" ). If you're using a programming language rather than just the MySQL client, you can also read the file into a variable and insert

Re: Why drop a index takes so long to do?

2004-08-05 Thread Jeremy Zawodny
On Thu, Aug 05, 2004 at 11:27:40AM -0700, Haitao Jiang wrote: > Hi, > > I have a 3GB index which includes a fulltext index on 2 columns. I > wanted to drop this fulltext index from the table, but it took > forever. Why it is so slow? Is it because MySQL basically go through > the index file (there

Re: Why drop a index takes so long to do?

2004-08-05 Thread mos
At 01:27 PM 8/5/2004, you wrote: Hi, I have a 3GB index which includes a fulltext index on 2 columns. I wanted to drop this fulltext index from the table, but it took forever. Why it is so slow? Is it because MySQL basically go through the index file (there is only one) and re-write it to another f

Why drop a index takes so long to do?

2004-08-05 Thread Haitao Jiang
Hi, I have a 3GB index which includes a fulltext index on 2 columns. I wanted to drop this fulltext index from the table, but it took forever. Why it is so slow? Is it because MySQL basically go through the index file (there is only one) and re-write it to another file and then copy it back? Any

BLOB columns

2004-08-05 Thread Nathan Mealey
I am at a loss as to how to insert files into a BLOB column in a database, and how to extract them. Basically, I want to store files in the database itself, but do not know how to go about doing this. What sort of queries do you run to do this? Is it the same as regular "insert" and "select"

Re: Front-end for beginner trying to learn mysql

2004-08-05 Thread Tiago Serafim
Hi, I´ve been using MySQL Front since I started learning MySQL I should try too... Hmm, I think is a better idea to learn php instead ASP php has a lot of features than asp, it´s opensource, is plattaform idenpendent, and so on... Cheers, On Thu, 5 Aug 2004 10:28:51 -0700 (PDT), Karam C

Re: Front-end for beginner trying to learn mysql

2004-08-05 Thread Karam Chand
I always preferred SQLyog at http://www.webyog.com Karam --- Scott Hamm <[EMAIL PROTECTED]> wrote: > To all, > > Is there any simplest front-end for beginner like > me to learn how > mysql run? > > I am intensive in html code and currently learning > ASP. > > Is there anythi

Front-end for beginner trying to learn mysql

2004-08-05 Thread Scott Hamm
To all, Is there any simplest front-end for beginner like me to learn how mysql run? I am intensive in html code and currently learning ASP. Is there anything else that I need to complete transactions between mysql and front-end application? Thanks in advance, Scott

Re: time zone leap seconds

2004-08-05 Thread Jeff Mathis
i seemed to have gotten around it by manually sourcing the script to update the password field, which had the create table statements in it, and then loading time zones based on our system time zone files. the manual section on upgrading from 4.0 to 4.1 did explain this -- i just had to dig a l

RE: Using the Index (Tables)....

2004-08-05 Thread Peter Vertes
Here's how I do it with tables that have multiple keys: CREATE TABLE Users ( Id SMALLINT UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT, FirstName VARCHAR(25) NOT NULL, LastName VARCHAR(25) NOT NULL, PRIMARY KEY(Id), KEY LastNameIndex (LastName), Key Firs

FW: Using the Index (Tables)....

2004-08-05 Thread Scott Fletcher
-Original Message- From: Scott Fletcher Sent: Thursday, August 05, 2004 11:00 AM To: 'KSTrainee' Subject: RE: Using the Index (Tables) One more question. Does the column's name have to be in order by col1, col2 and col3? Or does it not matter which order, like col1, col3, col2, whe

Re: mysql-4.0.20 configure fails mac os x 10.3.1 client

2004-08-05 Thread Ron Phelps
I now have mysql running on my Mac OS X 10.3.4 box. The socket file is now being created correctly in my /usr/local/mysql/run directory. Thank you for all your help. Regards, Ron Phelps --- Ron Phelps <[EMAIL PROTECTED]> wrote: > 1. I re-examined the error messages in config.log. I > couldn'

Re: Table Corruption

2004-08-05 Thread Egor Egorov
Odhiambo Washington <[EMAIL PROTECTED]> wrote: >> > +--++--++ >> > | atmail.Users | repair | info | Key 1 - Found wrong stored record at 0 | >> > | atmail.Users | repair | status | OK | >>

Re: Insert problems with InnoDB (big table)

2004-08-05 Thread Luc Charland
[EMAIL PROTECTED] wrote: Estimado Luc, Con fecha jueves 5 de agosto de 2004, 11.07.23, escribió: Did you try disable index table? When you import millon of records there is an overload indexing it. First import and then create your index or: ALTER TABLE tb_name DISABLE KEYS; import data... ALTER

Unique Key

2004-08-05 Thread Paul McNeil
Thanks to all for helping me sort this out. I am setting the field to tinyblob vs tinytext. Thanks again. God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. Microneil research Sniffer Anti Spam Sortmonster Research GOD BLESS AMERICA! To God Be The Gl

Unique Key question

2004-08-05 Thread Paul McNeil
Good morning. I have a table with field name DATA type tinytext. The table is already populated. I need DATA to be unique. It seems that MySQL doesn't look at the uniqueness of a field in a binary fashion. Example (chosen because I think it is just plain odd) june = Júne If I query as - Select

High Memory Usage

2004-08-05 Thread Sashi Ramaswamy
Hi, All of a sudden the memory used by mysql threads has gone up. Each thread is consuming about 20 M of RAM. My databases are really small and usage is not very intense. Tables in the database are of type INNODB. MySQL server version is 4.0.14-standard. Any ideas on how to fix this problem?

Re[2]: updating table based on other table values

2004-08-05 Thread DebugasRu
PP> try: PP> update tmpISM,ta PP> set tmp.SUMA=tmp.SUMA-ta.SUMA PP> where (tmp.PID=ta.PID) and (tmp.PAJ=ta.PAJ) Does update work on more than one table ? thank you for your input but i've just resolved the problem as follows (i have had forgotten the EXISTS keyword for a moment): update tmp

Re: Unique Key question

2004-08-05 Thread Michael Stassen
BINARY is for CHAR and VARCHAR, but he's using TINYTEXT. The binary version of TINYTEXT is TINYBLOB. Michael mos wrote: Paul, Just use the "Binary" column attribute to make it case sensitive.. From MySQL Manual: http://dev.mysql.com/doc/mysql/en/CHAR.html As of MySQL 4.1, values in CHAR

Using the Index (Tables)....

2004-08-05 Thread Scott Fletcher
I have one question. Once I create 2 or more indexes to a table, should I instruct MySQL to use which index in certain order? (Like use that 2nd index, not the 1st one). Another question, do I need to run the maintance on the index? If so, how? With IBM DB2, I had to run the index maintance we

[ANN] SQL Service Center 1.2 released

2004-08-05 Thread SQL Maestro Group
Hello, SQL Maestro Group is happy to announce the release of SQL Service Center 1.2, a Windows GUI tool for MySQL server maintenance and data reservation. The trial version of the software is available at http://www.sqlmaestro.com/products/myservice/download.html. In this version we have imple

Re: Insert problems with InnoDB (big table)

2004-08-05 Thread Luc Charland
Hi Frank, you actually got me doubting here. We don't use mysql client, but I made sure that autocommit was turned off. I double checked (with select count(*) from smalltest) to see the inserts were in fact commited by chunk of 100,000 and not one by one, and it was. We still see exactly the s

Re: Insert problems with InnoDB (big table)

2004-08-05 Thread Luc Charland
[EMAIL PROTECTED] wrote: Are you disabling autocommit before doing the inserts? And committing after all inserts are complete? We tried commiting every 1000, 1, 10 and 100 records. We basically have the same results. I will try to see if commiting after 10 million inserts does any

Re: updating table based on other table values

2004-08-05 Thread Philippe Poelvoorde
DebugasRu wrote: PP> try: PP> update tmpISM,ta PP> set tmp.SUMA=tmp.SUMA-ta.SUMA PP> where (tmp.PID=ta.PID) and (tmp.PAJ=ta.PAJ) Does update work on more than one table ? Yes you can : http://dev.mysql.com/doc/mysql/en/UPDATE.html thank you for your input but i've just resolved the problem as f

Re: unable to connect mysql and jsp

2004-08-05 Thread Jonathan Mangin
If you've installed the jar in $JAVA_HOME/lib/ext it shouldn't need to be referenced in $CLASSPATH. - Original Message - From: "Mahesh S" <[EMAIL PROTECTED]> To: "my sql" <[EMAIL PROTECTED]> Sent: Thursday, August 05, 2004 1:36 AM Subject: unable to connect mysql and jsp > hi all, > >

Re: updating table based on other table values

2004-08-05 Thread Philippe Poelvoorde
DebugasRu wrote: Hello i can't figure out how to update the table tmpISM based on other table ta values. What i need i can intuitivly express as follows: update tmpISM set tmp.SUMA=tmp.SUMA-ta.SUMA where (tmp.PID=ta.PID) and (tmp.PAJ=ta.PAJ) try: update tmpISM,ta set tmp.SUMA=tmp.SUMA-ta.SUMA whe

updating table based on other table values

2004-08-05 Thread DebugasRu
Hello i can't figure out how to update the table tmpISM based on other table ta values. What i need i can intuitivly express as follows: update tmpISM set tmp.SUMA=tmp.SUMA-ta.SUMA where (tmp.PID=ta.PID) and (tmp.PAJ=ta.PAJ) index PID+PAJ is unique both for tmpISM and ta Any suggestions how the u

Re: concatenation

2004-08-05 Thread Martijn Tonies
> I have been trying to concatenate two fields with MySql contaning the > NAME and SURNAME, whatever tool I use, it returns me all the time a > "number". What it is wrong with my request? Hard to tell without showing your query, isn't it? With regards, Martijn Tonies Database Workbench - deve

concatenation

2004-08-05 Thread Fernand St-Georges
I have been trying to concatenate two fields with MySql contaning the NAME and SURNAME, whatever tool I use, it returns me all the time a "number". What it is wrong with my request? thanks [EMAIL PROTECTED] --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://

Re: using NOW() as a default..

2004-08-05 Thread Alec . Cawley
"bruce" <[EMAIL PROTECTED]> wrote on 05/08/2004 06:12:58: > hi... > > a simple question... > > i'm creating a table and want to set a column to have the default of the > current day/time when the row is created... > > i've tried... > > create table foo( > dog int, > timestamp1 default NOW() >

load data replace problem

2004-08-05 Thread Lorenzo Maestri
Hi all, i have problems importing rows with the command: load data concurrent infile '' replace into table fields escaped by '' lines terminated by '\r'; this command exits with the error: ERROR 1062 (23000): Duplicate entry '12835265' for key 1 It seems that the replace option is ignored !! (1