Multiple inserts in one statement

I was just reading a forum post on mysql, someone said it was possible in mysql 4 to insert data into more than one table at once. Is this possible, if so, where in the docs can I see a example of this? -- - Scott Haneda

Re: Performance of big table

Hi Harald, Replacing the index on member_id by a compound index on (member_id, id) might help. Thank you very much for your suggestion. After replacing the index with compound index, the query works very fast now. -- MySQL General Mailing List For list archives:

Re: MySQL Restarts -UDF

coolcat2001 wrote: Hi, When an attempt is made to Create a Function that was dropped previously the mysql gets restarted. I have loaded the so into /usr/lib directory. The mysql is statically linked . If the mysql is statically linked, I can't see how you can load an UDF. You can either

Re: Tuning MySQL for Large Database

My 5 cents... tuning the MySQL database config file is half a solution. In my experience optimizing the application code and table/index structure delivers performance results. Michael Sleman wrote: Hello, We're running 1 web server (apache 2 php) / 1 dedicated DB server (MySQL

Re: after upgrade unicode characters changed to question marks

You were managing unicode characters with mysql 3.23? It was just storing and retrieving the data then? Because mysql can really manage unicode in 4.1 version, isn't it? Stefan Klopp wrote: Hello All, We recently upgraded our mysql server from 3.23 to 4.0.18 and have found that all of our Unicode

RE: Splitting data across tables

-Original Message- From: William H. Bowers [mailto:[EMAIL PROTECTED] Sent: 22 July 2004 03:55 To: [EMAIL PROTECTED] Subject: re: Splitting data across tables If indexing seems to be the cause of the slowdown, it may be faster to drop the indexes, add the new data, and then

Re: Update on installing mysql on linux

Levi Campbell wrote: Okay, I got the RPM package installed but when I use rpm -I mysql-server-4.0.20-0.i386.rpm, I am told that I need several files, most of which I can't find in the Debian package library. I need the following files: /usr/bin/perl, sh-utils and sh, where can I get these? my

Re: Splitting data across tables

In article [EMAIL PROTECTED], Andrew Hill [EMAIL PROTECTED] writes: Hi, Okay, cool. A couple of things: 1) We *think* that our bottleneck is RAM and the calculation of indexes when inserting into the table (about 500 million rows). There's certainly plenty of CPU and disk I/O left in the

Large amounts of SQL

Hi this is just a general question - how do other people organise a large amount of SQL? I have begun to have a lot of SQL files lying around, used for some data-mining type stuff, things are getting a bit messy. I do my best with: (a) putting user variables at the start of files for anything

RE: CIDR ranges in MySQL permissions?

Hi All, Can I assume by the lack of any responses that the anwser to my question is no? Cheers, Andrew -Original Message- From: Andrew Braithwaite [mailto:[EMAIL PROTECTED] Sent: Tuesday 20 July 2004 16:44 To: [EMAIL PROTECTED] Subject: RE: CIDR ranges in MySQL permissions? Sorry -

mysqldump overflow?

Hi, I want to save a database with 'mysqldump' and get the following error: mysqldump: Got error: 1105: File '/var/lib/mysql/mydb/J99.MYD' not found (Errcode: 24) when using LOCK TABLES But the file *does* exist: ls -l /var/lib/mysql/mydb/J99.MYD -rw-r-1 mysqlmysql 224 Feb

Foreign keys

Hello there, I have a table that with a TEXT filed as a primary key. I can't make a foreign key of this field on another table, why does this happens? Ex: Product --- | ref - text, PK | name - text | ProductsList | ID int, PK | profref - foreign key of the table product |

Re: Mysql growing pains, 4 days to create index on one table!

Split the myisam table into seperate tables. We will call each table a bucket. Create a MERGE table of all of them. For selecting the data. When inserting, use a hash function on your primary key values to determine which bucket to insert into. If you almost always select by primary key, then

Re: Foreign keys

Hi, I have a table that with a TEXT filed as a primary key. I can't make a foreign key of this field on another table, why does this happens? Ex: Product --- | ref - text, PK | name - text | ProductsList | ID int, PK | profref - foreign key of the table product | I'm

Re: How do I import a .dmp file?

David, What you use to bulk-import a file has more to do with its format than its extension. Different products can all produce files with the same extensions but with totally different formats. Not all SQL and DMP files are created the same. Read up on the native MySQL import/export

mysqld process hanging -- can't restart

Hi, I'm having a problem every so often with the following error message: 040718 15:21:59 InnoDB: Started /usr/sbin/mysqld: ready for connections. Version: '4.0.16-standard' socket: '/var/lib/mysql/mysql.sock' port: 3306 Number of processes running now: 1 mysqld process hanging, pid 12141 -

MySQL Daemons

Hi all, I'm administrating a LAMPhp system and have there values below.. Session Count : 6437 (find /tmp -name \sess*\ | wc -l) (session timeout is 30 mins.) MySQL Daemon Count : 85 (ps -ef | grep mysql | wc -l) Apache Daemon Count : 106 (ps -ef | grep httpd | wc -l) As you can see I

Error 1045

My first contact with MySQL. When I try to login with the following two lines, I get an error code 1045 mysql -h localhost -u jk -p ENTER PASSWORD: password ERROR 1045: Access denied for user: [EMAIL PROTECTED] using password: YES Please help. [EMAIL PROTECTED] -- MySQL General

Re: Error 1045

On Thursday 22 July 2004 09:06 am, Jernej Koprivec wrote: My first contact with MySQL. When I try to login with the following two lines, I get an error code 1045 mysql -h localhost -u jk -p ENTER PASSWORD: password ERROR 1045: Access denied for user: [EMAIL PROTECTED] using password: YES

Re: CIDR ranges in MySQL permissions?

Hi! On Jul 22, Andrew Braithwaite wrote: Hi All, Can I assume by the lack of any responses that the anwser to my question is no? The answer is yes: http://dev.mysql.com/doc/mysql/en/Connection_access.html Cheers, Andrew -Original Message- From: Andrew Braithwaite

FULLTEXT search

When doing a MATCH() AGAINST() search. Does a result get returned only when all of the words in the AGAINST() 'tag' matches a document or if any words match. Basically is this an AND or OR type of result that is returned? Thanks! -- Kind Regards Schalk Neethling Web

LOAD DATA INFILE

Hi All, Tearing my hair out with this one! I am trying to load data from a text file from the command line, this is the syntax... LOAD DATA INFILE 'users_1.csv' INTO TABLE 'ma0133' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'; The error I get

Re: IMPORTANT: Please Verify Your Message

Hello, I got a error the first time ... but is seems to be working. Michael. On Thu, 22 Jul 2004 07:04:37 -0400 [EMAIL PROTECTED] wrote: Verify Email Hello [EMAIL PROTECTED] , [EMAIL PROTECTED] is currently protecting themselves from receiving junk mail using Spamcease Just

Re: FULLTEXT search

OR From the manual: Every correct word in the collection and in the query is weighted according to its significance in the collection or query. http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html If you want AND, look at the boolean full-text searches format:

RE: LOAD DATA INFILE

Resolved, used this syntax... LOAD DATA INFILE '/path/from/root/to/file.csv' INTO TABLE ma0133 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' - Phil. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system

Re: FULLTEXT search

Match() can be called 'In Boolean Mode' if you wish. See the docs at http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html. PB - Original Message - From: Schalk Neethling To: [EMAIL PROTECTED] Sent: Thursday, July 22, 2004 9:17 AM Subject: FULLTEXT search When doing a

Re: Error 1045

hi jernej, using this command is normal: mysql -h localhost -u jk -p ENTER but you have to check the grants/permission you set to a particular database name, username with its password. use the manual for more info regarding grants. thanks, Ehrwin Miña Senior System Administrator NOC Chikka

ODBC Connector for Windows - how to handle large resultsets?

Hi all, I have a problem with the ODBC Connector since I need to handle very large resultsets. when I try a simple search on a 5Millon table, it tries to send all the info to the client hanging it before i can see a simple row. So is there a way to force the driver to use use_resultset

Re: after upgrade unicode characters changed to question marks

Hello, (B (BI had checked the variables on both the 3.23 and 4.0.18 and they both show (Bthe exact same character sets. Nothing appears to have changed with our data (Bin the tables or the setting of the server. (B (BHere is what the show variables reports: (B (B| character_set |

Re: Database design questions

Big post = long response. Thank you for your patience. When it comes to DB design I like to first identify the basic objects: List owners (the people who manage a group), subscribers (the people in the group), and the groups themselves. I think you equate a list owner to a User of your

Re: after upgrade unicode characters changed to question marks

I am not sure whether I was actually managing the Unicode characters or whether they were just being stored and retrieved. Regardless I am not on version 4.1 either, only 4.0. Stefan - Original Message - From: Jean-Marc PULVAR [EMAIL PROTECTED] To: Stefan Klopp [EMAIL PROTECTED] Cc:

Multiple Foreign Keys

Can a child table have multiple foreign key references linking its records to two or more parent tables? Can a parent table also be a child table? That is, can a parent table have a foreign key that links it to another table? I setting up my tables as INNODB types. Thanks, Roy Harrell --

Fw: Tuning MySQL for Large Database

I agree with Matthew. A database is a lot like a car. You can have a well-built, high-powered vehicle that could do 200mph while still carrying 15 people (good hardware + good tuning) but if you drive it off-road, alone, and in second gear (bad table structure, poor index coverage, poor SQL

Re: Fw: Tuning MySQL for Large Database

I went over your data. This is what I noticed first: | Select_full_join | 0| | Select_full_range_join | 0| | Select_range | 1| | Select_range_check | 0| | Select_scan | 301 | What command will provide this data? --

Re: Multiple Foreign Keys

The answer is yes to all of your questions. Just as an example, I have a table in one of my databases that is a child to nine other tables and is the parent to two. Just remember that any child table columns participating in a FK relationship must also be indexed (or the first column listed in

Re: Fw: Tuning MySQL for Large Database

This is part of the results of a SHOW STATUS command. See one of the earlier posts for the full list of his settings. Read These For More: http://dev.mysql.com/doc/mysql/en/SHOW.html http://dev.mysql.com/doc/mysql/en/SHOW_STATUS.html

Re: Fw: Tuning MySQL for Large Database

Hmm I'm guessing my stats arent too good, lots of full table scans, but this is to be expected, my users can query any table by any column, and I cant index all column combinations Variable_name Value

Re: Fw: Tuning MySQL for Large Database

Resend, firefox did not send the way it looked when I typed it! I'm guessing my stats arent too good, lots of full table scans, but this is to be expected, my users can query any table by any column, and I cant index all column combinations Variable_name Value Select_full_join

Re: Fw: Tuning MySQL for Large Database

From: http://dev.mysql.com/doc/mysql/en/Server_status_variables.html Key_reads The number of physical reads of a key block from disk. If Key_reads is big, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests.

mysqld being killed by VM

Hello, I have a mysql server that VM is killing about once every two weeks. Now my sar utility shows that the memory on the box continues to grow at a small rate from start to stop. But I am not sure how accurate that is. Now I am running slackware 9.1 with kernel 2.4.23 with mysql

Re: Fw: Tuning MySQL for Large Database

I've found the slow query log is useless to me, it's 50 meg right now. Is there a tool that will identify common querys? I could probably come up with some sql's if I load it into a table, but it would take quite a while to sort out. I posted a request on the mysql bugtraq to move it to a

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: How do I import a .dmp file?

David Did you look at MYSQL LOAD DATA INFILE ??? doc is available at http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html regards, Martin Gainty (cell) 617-852-7822 (e) [EMAIL PROTECTED] (http)www.laconiadatasystems.com From: David Blomstrom [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: How do I

Re: How do I import a .dmp file?

David Did you look at MYSQL LOAD DATA INFILE ??? doc is available at http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html regards, Load data infile only works with a text file going into a table, if the data is in another format, like raw oracle, or EBCDIC it wont work, you'll need to convert it to

str_to_date problem

I have a time field formatted as hhmm however the hours field does not add leading zeros. eg 09:01 is simple 901. I want to get a standard format time out but, mysql str_to_date('901','%k%i') gives a NULL result. A further test reveals that I can covert into the desired format but not back.

Re: How do I import a .dmp file?

--- matt ryan [EMAIL PROTECTED] wrote: David Did you look at MYSQL LOAD DATA INFILE ??? doc is available at http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html regards, Load data infile only works with a text file going into a table, if the data is in another format, like

Re: How do I import a .dmp file?

OK, so if I can convert it into ascii, then it will be a text file, which I can import using the instructions at http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html, right? Thanks. Yep, just have the table structure match the ascii file and load it in Matt -- MySQL General Mailing List For list

Re: Fw: Tuning MySQL for Large Database

Hi, On Thursday, July 22, 2004, at 01:42 PM, matt ryan wrote: I've found the slow query log is useless to me, it's 50 meg right now. Is there a tool that will identify common querys? I could probably come up with some sql's if I load it into a table, but it would take quite a while to sort

Re: str_to_date problem

Danny: What if you just pad the string, does it work then? str_to_date( LPAD( '901', 4, '0' ), '%k%i' ) Wes On Jul 22, 2004, at 2:05 PM, [EMAIL PROTECTED] wrote: I have a time field formatted as hhmm however the hours field does not add leading zeros. eg 09:01 is simple 901. I want to get a

find out who was online at a given time

The three query solution looks like a good one; but here are some notes... SELECT @starttime = max(time) There's a colon necessary in the syntax here -- SELECT @starttime := max(time) SELECT * FROM logtable WHERE IP='66.50.xxX.245' AND Date='2004-07-05' AND Time in

Re: Fw: Tuning MySQL for Large Database

There is a perl script that comes with MySQL called mysqldumpslow. You can just run it on your slow log and it will output summary statistics about the slow log. I saw that in the docs, but I definitly dont want to install perl on a production server, I never looked to see if I could do it

Re: Multiple Foreign Keys

On Thu, 22 Jul 2004 12:14:58 -0400 Roy Harrell [EMAIL PROTECTED] wrote: Can a child table have multiple foreign key references linking its records to two or more parent tables? yes - something like: CREATE TABLE child ( p1_id INT, p2_id INT, INDEX p1_id_ind (p1_id), INDEX

Another: LOAD DATA INFILE error.

Hi, I have the following sql statement: LOAD DATA INFILE '/Babylon5/Library/WebServer/Documents/~milesfiles.com/ eims_errorlog.txt' INTO TABLE eims_log FIELDS TERMINATED BY ' ' ENCLOSED BY ' ' LINES TERMINATED by '\r' I can't seem to get it to run. The path is correct but I keep getting the

foreign key constraints

Hi everyone, I'd like to learn more about foreign key constraints... I read this section of the manual... http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html Where can I find a more detailed description with some examples? Thanks, Sergei

Re: find out who was online at a given time

You're absolutely right about the := not just = (Sorry!!) Actually, I DID want to use an IN and not a BETWEEN. What I was doing was picking the two boundary times around a given point in time so once I got those numbers, I wanted to get both records. I could have written the WHERE like this:

mysqldump issue

Hello, MySQL gurus, Since I have been moving data around using mysqldump recently, I have found sth annoying: If a table has a column defined as 'float not null' and the corresponding txt file used to load it have sth like 'nan' for the column, although giving warnings, the record will be

Re: mysqldump issue

Ginger Cheng wrote: Hello, MySQL gurus, Since I have been moving data around using mysqldump recently, I have found sth annoying: If a table has a column defined as 'float not null' and the corresponding txt file used to load it have sth like 'nan' for the column, although giving

mysql 4.1 cluster

Hello, Anyone running mysql 4.1 with cluster support ? Is it sort of production ready ? -- Michael Gale -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: How do I import a .dmp file?

Are you referring to an Oracle .dmp export file? If so you will also need to do some parsing. -Original Message- From: matt ryan To: David Blomstrom Cc: [EMAIL PROTECTED] Sent: 7/22/04 1:40 PM Subject: Re: How do I import a .dmp file? OK, so if I can convert it into ascii, then it will

RE: ODBC Connector for Windows - how to handle large resultsets?

which ODBC driver are you using? -Original Message- From: Leonardo Javier Belén To: [EMAIL PROTECTED] Sent: 7/22/04 10:36 AM Subject: ODBC Connector for Windows - how to handle large resultsets? Hi all, I have a problem with the ODBC Connector since I need to handle very large

Re: mysql 4.1 cluster

Michael Gale [EMAIL PROTECTED] writes: Anyone running mysql 4.1 with cluster support ? Try the Mysql cluster list. Is it sort of production ready ? The 4.1 release is labeled BETA. See http://en.wikipedia.org/wiki/Development_stage -- Per Andreas Buer -- MySQL General Mailing List For

subquery problem.

Hi, My server version : 4.0.18-max-nt I have created 2 tables 'channels' and 'users' in my database. I can query these tables individually. I am trying to execute a query with 'exists' clause. mysql select * from channels where exists (select * from users); This give the following error.

Multiple inserts in one statement

I was just reading a forum post on mysql, someone said it was possible in mysql 4 to insert data into more than one table at once. Is this possible, if so, where in the docs can I see a example of this? -- - Scott Haneda

Re: subquery problem.

Version 4.0 doesn't support subqueries. In any event, your query would return all rows from channels as long as there are any rows in users, which I doubt is what you intended. --- nambi c [EMAIL PROTECTED] wrote: Hi, My server version : 4.0.18-max-nt I have created 2 tables 'channels'

Re: subquery problem.

Subqueries aren't implemented in 4.0 - only 4.1 and upwards support subqueries. Cheers Andrew. - Original Message - From: nambi c [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, July 22, 2004 10:31 PM Subject: subquery problem. Hi, My server version : 4.0.18-max-nt I

Transactions and mysql insert it

I have been pulling my hair out trying to get a solution to something, assuming idiotically that in a transaction scenario I would not be able to get the insert it back out. It seems to work, I am wondering how and if it is reliable. Give the scenario where I have 2 inserts I want to make, since

mysql CLI and inputrc

I just upgraded to 4.1.3 (on Sparc Solaris 2.8) and suddenly the mysql commandline utility no longer seems to pay attention to the set editing-mode vi commands in the /etc/inputrc or ~/.inputrc Additionally, the commandline history doesn't seem to stick between sessions. It works fine within a

Re: foreign key constraints

Sergei, Check out Paul DuBois' book MySQL - The Definitive Guide (2nd edition). It has a few good chapters that discuss foreign key constraints. --bmansell Brian E. Mansell MySQL Professional On Thu, 22 Jul 2004 13:06:07 -0700, Sergei Skarupo [EMAIL PROTECTED] wrote: Hi everyone, I'd

Re: mysqldump overflow?

24 = Too many open files. I think you can resolve this issue by increasing the number of file descriptors available to mysqld by setting an appropriately higher 'open-files-limit' in your configuration. --bmansell Brian E. Mansell MySQL Professional On Thu, 22 Jul 2004 13:20:24 +0200, Wolfgang

RE: How do I import a .dmp file?

--- Victor Pendleton [EMAIL PROTECTED] wrote: Are you referring to an Oracle .dmp export file? If so you will also need to do some parsing. * * * * * * * * * * I don't know; this is the first time I've tangled with a .dmp file. Someone told me it must have come from an Oracle system, because

Re: Transactions and mysql insert it

MySQL doesn't guarantee that there will be no gaps in sequence values. Assigment of the id is always atomic because innodb uses an AUTO_INC lock that lasts for the time of the insert, not the life of the transaction. lets say your highest order number is 10 transaction begins for client 1

Re: Transactions and mysql insert it

on 7/22/04 3:54 PM, Justin Swanhart at [EMAIL PROTECTED] wrote: MySQL doesn't guarantee that there will be no gaps in sequence values. Assigment of the id is always atomic because innodb uses an AUTO_INC lock that lasts for the time of the insert, not the life of the transaction. lets say

Re: How do I import a .dmp file?

While the extension on the file isn't set in stone, many people add the .dmp extension to files created with the Oracle exp utility. You can use a third party utility call nxtract (http://www.elmbronze.co.uk/nxtract/index.htm) to convert exp files into tab delimited files. The eval version only

RE: How do I import a .dmp file?

At 15:53 -0700 7/22/04, David Blomstrom wrote: --- Victor Pendleton [EMAIL PROTECTED] wrote: Are you referring to an Oracle .dmp export file? If so you will also need to do some parsing. * * * * * * * * * * I don't know; this is the first time I've tangled with a .dmp file. Someone told me it

Best Use of FULLTEXT and/or LIKE?

After reading quite a few online articles about using fulltext indexes in MySQL, I'm still a little confused about the best solution for my simple search script. I want to allow users to search for news articles. Here's the table structure: CREATE TABLE `news` ( `id` int(7) unsigned NOT NULL

Re: Cluster on Solaris 9/x86 works

Yes, I have set the ByteOrder to Big for Sparc. Changing that to Little (even though this is wrong) had no effect. At 05:05 PM 7/20/2004, Justin Swanhart wrote: You do have ByteOrder: Big in the .ini file for the sparc database servers, right? --- Alexander Haubold [EMAIL PROTECTED] wrote: Hi

Re: Another: LOAD DATA INFILE error.

on 7/22/04 1:03 PM, m i l e s at [EMAIL PROTECTED] wrote: I have the following sql statement: LOAD DATA INFILE '/Babylon5/Library/WebServer/Documents/~milesfiles.com/ eims_errorlog.txt' INTO TABLE eims_log FIELDS TERMINATED BY ' ' ENCLOSED BY ' ' LINES TERMINATED by '\r' I can't

Re: find out who was online at a given time

Actually, I DID want to use an IN and not a BETWEEN. What I was doing was picking the two boundary times around a given point in time so once I got those numbers, I wanted to get both records. Ah, yes, I see I didn't really understand it the first time -- and using IDs instead of timestamps is

Last insert id problem/bug

I am not sure this is just how it is, my issue, or a bug, I searched the bugs database, but don't really know what to call this in order to properly look up the info: MySQL 4.0.18-standard CREATE TABLE `addresses` ( `id` int(11) NOT NULL auto_increment, `user_id` int(11) NOT NULL default