RE: Python related MySQL question

2005-04-26 Thread mathias fatene
Look at db.use_result() and db.store_result() here : http://www.birgerblixt.com/doc/packages/python-mysql/MySQLdb-2.html#ss2. 2 Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Smelly Socks

ibdata1 grows beyound borders..

2005-04-26 Thread Eric Persson
Hi, I've been running a innodbdatabase for a while, and it works nice, however, I've noticed that it have grown beyound the specifications in the my.cnf file. I did define autoextend so its not very strange, however, I was wondering, how far can it grow, and is it a bad idea to just let it

Database languages supported by MySQL

2005-04-26 Thread Ochungo, Pamela (ILRI)
Hallo, I would like to know whether there are any other languages supported by MySQL apart from SQL. e.g. MsAccess provides VB for aplications. Does MySQL support any such lower level language? Regards Pamela -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To

LOAD DATA INFILE and STR_TO_DATE

2005-04-26 Thread Michael Markstaller
Hi, using mysql 4.0.24 I'm stuck with a problem importing a CSV-file converting the date in the CSV from MM/DD/ to a mysql table.. I'm trying: LOAD DATA INFILE '/tmp/mydata.csv' INTO TABLE mytable FIELDSTERMINATED BY ','OPTIONALLY ENCLOSED BY '' (recordType,

Re: Database languages supported by MySQL

2005-04-26 Thread Jigal van Hemert
From: Ochungo, Pamela (ILRI) I would like to know whether there are any other languages supported by MySQL apart from SQL. e.g. MsAccess provides VB for aplications. Does MySQL support any such lower level language? MySQL is a database management system (DBMS), a so called backend system. MS

Re: LOAD DATA INFILE and STR_TO_DATE

2005-04-26 Thread Jigal van Hemert
using mysql 4.0.24 I'm stuck with a problem importing a CSV-file converting the date in the CSV from MM/DD/ to a mysql table.. I'm trying: LOAD DATA INFILE '/tmp/mydata.csv' INTO TABLE mytable FIELDSTERMINATED BY ','OPTIONALLY ENCLOSED BY '' (recordType,

Re: Database languages supported by MySQL

2005-04-26 Thread Dawid Kuroczko
On 4/25/05, Ochungo, Pamela (ILRI) [EMAIL PROTECTED] wrote: Hallo, I would like to know whether there are any other languages supported by MySQL apart from SQL. e.g. MsAccess provides VB for aplications. Does MySQL support any such lower level language? I believe you are asking about so

Re: ibdata1 grows beyound borders..

2005-04-26 Thread Gleb Paharenko
Hello. Searching in the archives says you could get worse performance, because of extending during transactions: http://lists.mysql.com/mysql/180037 http://lists.mysql.com/mysql/170946 Eric Persson [EMAIL PROTECTED] wrote: Hi, I've been running a innodbdatabase for a while,

Re: Query question

2005-04-26 Thread Martijn Tonies
If my englsih is so bad, i'll try to explain and stop this thread now. That's not what was being said. I'm not teaching, i'm answering questions. If someone wants to read docs, he (she) doesn't ask a question on the list. So if i answer, i answer the question, just the question. You want

Re: ibdata1 grows beyound borders..

2005-04-26 Thread Eric Persson
Gleb Paharenko wrote: Hello. Searching in the archives says you could get worse performance, because of extending during transactions: Yes, read a few of those, but how about the fix for this, will the data rearrange itself automatically when I specify more files? //Eric -- MySQL General Mailing

Fw: modifying InnoDB storage

2005-04-26 Thread Jigal van Hemert
Unfortunately no response from the list anymore :-( Can we ever get rid of the autoexpanding ibdata file without completely rebuilding the databases? Regards, Jigal. - Original Message - From: Jigal van Hemert [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, April 20, 2005

RE: Query question

2005-04-26 Thread mathias fatene
Yes ten years and forgot mysql certified. I can offer i-am-a-dummy to you if you lack. I've never imagined find so bad people on the list. But i'll write to the moderator to see who is on. But i'm pleased to help people wihout naz mentality than yours. Best Regards Mathias

Re: Query question

2005-04-26 Thread Chris Ramsay
Mathias There are no *bad* people on this list - different point of view, yes. Participating on this and other lists requires give AND take - taking advice as well as giving it... Participating is always going to be a two way process so just accept it, and if you can't - unsubscribe. Hope this

why NOT NULL in PRIMARY key??

2005-04-26 Thread Jigal van Hemert
http://dev.mysql.com/doc/mysql/en/silent-column-changes.html mentions that Columns that are part of a PRIMARY KEY are made NOT NULL even if not declared that way. And http://dev.mysql.com/doc/mysql/en/create-table.html tells me that A PRIMARY KEY is a unique KEY where all key columns must be

RE: why NOT NULL in PRIMARY key??

2005-04-26 Thread Jay Blanchard
[snip] http://dev.mysql.com/doc/mysql/en/silent-column-changes.html mentions that Columns that are part of a PRIMARY KEY are made NOT NULL even if not declared that way. And http://dev.mysql.com/doc/mysql/en/create-table.html tells me that A PRIMARY KEY is a unique KEY where all key columns must

Re: Database languages supported by MySQL

2005-04-26 Thread Rhino
The manual is your friend. See this link: http://dev.mysql.com/doc/mysql/en/features.html. Rhino - Original Message - From: Ochungo, Pamela (ILRI) [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, April 25, 2005 3:20 AM Subject: Database languages supported by MySQL Hallo, I

Re: extract numeric value from a string.

2005-04-26 Thread gerald_clark
Eamon Daly wrote: Easy enough. Get the numeric part via CONVERT, then get the rest of the string from the length of the numeric part, plus one: SELECT tag, @num := CONVERT(tag, SIGNED) AS num_part, SUBSTRING(tag, LENGTH(@num) + 1) AS rest_of_string from tags; ++--++

RE: why NOT NULL in PRIMARY key??

2005-04-26 Thread emierzwa
Not every DBMS... MSSQL: Create Unique Index Microsoft(r) SQL Server(tm) checks for duplicate values when the index is created (if data already exists) and checks each time data is added with an INSERT or UPDATE statement. If duplicate key values exist, the CREATE INDEX statement is canceled and

Re: why NOT NULL in PRIMARY key??

2005-04-26 Thread Martijn Tonies
http://dev.mysql.com/doc/mysql/en/silent-column-changes.html mentions that Columns that are part of a PRIMARY KEY are made NOT NULL even if not declared that way. And http://dev.mysql.com/doc/mysql/en/create-table.html tells me that A PRIMARY KEY is a unique KEY where all key columns must

Re: why NOT NULL in PRIMARY key??

2005-04-26 Thread Jigal van Hemert
From: Jay Blanchard Since NULL is the absence of a value and PRIMARY keys must have a value a NULL column cannot be included as a portion of a PRIMARY key. AFAIK this is the case with every RDBMS out there. Asking the development team might get you a more informative response. There is a

performance problem on INSERT into MyISAM table

2005-04-26 Thread Ed Sweeney
I have been trying to run an fairly large INSERT into an empty table joining two other tables now for several weeks and have not been able to get the query to run to completion even when sub-seting the data into smaller ranges. I have tried this at MySQL releases 4.1.8a and 4.1.10a

Re: why NOT NULL in PRIMARY key??

2005-04-26 Thread Jochem van Dieten
On 4/26/05, Jigal van Hemert wrote: http://dev.mysql.com/doc/mysql/en/silent-column-changes.html mentions that Columns that are part of a PRIMARY KEY are made NOT NULL even if not declared that way. And http://dev.mysql.com/doc/mysql/en/create-table.html tells me that A PRIMARY KEY is a

RE: why NOT NULL in PRIMARY key??

2005-04-26 Thread Frank Bax
At 08:49 AM 4/26/05, Jay Blanchard wrote: [snip] http://dev.mysql.com/doc/mysql/en/silent-column-changes.html mentions that Columns that are part of a PRIMARY KEY are made NOT NULL even if not declared that way. And http://dev.mysql.com/doc/mysql/en/create-table.html tells me that A PRIMARY KEY

Re: modifying InnoDB storage

2005-04-26 Thread Heikki Tuuri
Jigal, - Alkuperinen viesti - Lhettj: Jigal van Hemert [EMAIL PROTECTED] Vastaanottaja: mysql@lists.mysql.com Kopio: Gleb Paharenko [EMAIL PROTECTED]; Heikki Tuuri [EMAIL PROTECTED] Lhetetty: Tuesday, April 26, 2005 2:09 PM Aihe: Fw: modifying InnoDB storage Unfortunately no response

How to make a virtual SELECT?

2005-04-26 Thread mailarch
Hello, I have a big problem, I only want to check if it's the minute 45 currently. I want to make a virtual SELECT without tables: mysql SELECT MINUTE(CURRENT_TIMESTAMP()) WHERE MINUTE(CURRENT_TIMESTAMP()) = 45; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that

RE: why NOT NULL in PRIMARY key??

2005-04-26 Thread SGreen
Ed, UNIQUE indexes (which you quote) and PRIMARY KEYS are similar but are NOT the same thing. Both types of keys guard against duplication of values for the tuple defining the index. However, PRIMARY KEYs hold special significance in that many RDBMS storage engines will use the PK to uniquely

Re: How to make a virtual SELECT?

2005-04-26 Thread Alec . Cawley
[EMAIL PROTECTED] wrote on 26/04/2005 14:46:37: Hello, I have a big problem, I only want to check if it's the minute 45 currently. I want to make a virtual SELECT without tables: mysql SELECT MINUTE(CURRENT_TIMESTAMP()) WHERE MINUTE(CURRENT_TIMESTAMP()) = 45; ERROR 1064 (42000):

Re: why NOT NULL in PRIMARY key??

2005-04-26 Thread Jigal van Hemert
From: Martijn Tonies Ehm... it might be me - but what sense does it make to have a NULL in a PK? If you need this, then your primary key probably isn't a primary key. Care to explain why and how you're designing your database? Martijn, The table contains an variable number of integer

Re: why NOT NULL in PRIMARY key??

2005-04-26 Thread Paul DuBois
At 15:20 +0200 4/26/05, Jigal van Hemert wrote: From: Jay Blanchard Since NULL is the absence of a value and PRIMARY keys must have a value a NULL column cannot be included as a portion of a PRIMARY key. AFAIK this is the case with every RDBMS out there. Asking the development team might get

mysqldump privileges

2005-04-26 Thread Mike Blezien
Hello, what type of privileges need to be assigned and/or changed when upgrading form MySQL 4.0.24 to 4.1.10 in order to allow users use of the mysqldump utility. Users where allowed to use the mysqldump to backup their databases and worked fine with 4.0.24, till the upgrade to 4.1.10 which no

Re: How to make a virtual SELECT?

2005-04-26 Thread SGreen
[EMAIL PROTECTED] wrote on 04/26/2005 09:46:37 AM: Hello, I have a big problem, I only want to check if it's the minute 45 currently. I want to make a virtual SELECT without tables: mysql SELECT MINUTE(CURRENT_TIMESTAMP()) WHERE MINUTE(CURRENT_TIMESTAMP()) = 45; ERROR 1064 (42000):

Re: why NOT NULL in PRIMARY key??

2005-04-26 Thread Harald Fuchs
In article [EMAIL PROTECTED], Jigal van Hemert [EMAIL PROTECTED] writes: From: Martijn Tonies Ehm... it might be me - but what sense does it make to have a NULL in a PK? If you need this, then your primary key probably isn't a primary key. Care to explain why and how you're designing your

Re: why NOT NULL in PRIMARY key??

2005-04-26 Thread Jigal van Hemert
From: Paul DuBois Hi Paul, A primary key absolutely forbids duplicate values. Indexes created with the UNIQUE keyword do not allow duplicates, except for the special case that multiple NULL values are allowed. I realise that it may (and is) defined in such a way, but it still does not

Re: why NOT NULL in PRIMARY key??

2005-04-26 Thread Martijn Tonies
Not every DBMS... MSSQL: Create Unique Index Microsoft(r) SQL Server(tm) checks for duplicate values when the index is created (if data already exists) and checks each time data is added with an INSERT or UPDATE statement. If duplicate key values exist, the CREATE INDEX statement is

Re: why NOT NULL in PRIMARY key??

2005-04-26 Thread Jigal van Hemert
From: Harald Fuchs id INT(11) - accountID name VARCHAR(32) - parameter name value INT(11) - parameter value Other tables contain string, datetime, etc. parameters. Since most searches are made for a value (or range) of one or more parameters, a usable primary key is: name-value-id

Re: why NOT NULL in PRIMARY key??

2005-04-26 Thread Dawid Kuroczko
On 4/26/05, Jigal van Hemert [EMAIL PROTECTED] wrote: And http://dev.mysql.com/doc/mysql/en/create-table.html tells me that A PRIMARY KEY is a unique KEY where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and

Re: performance problem on INSERT into MyISAM table

2005-04-26 Thread Brent Baisley
I must assume you have all the proper indexes setup and your configuration variables are fairly optimal. First, I would run just the select part with an explain in front of it to see what MySQL is trying to do. I've had MySQL run a query for an inordinate amount of time on a fairly small data

Re: why NOT NULL in PRIMARY key??

2005-04-26 Thread Martijn Tonies
A primary key absolutely forbids duplicate values. Indexes created with the UNIQUE keyword do not allow duplicates, except for the special case that multiple NULL values are allowed. I realise that it may (and is) defined in such a way, but it still does not explain *why* part of a

Re: why NOT NULL in PRIMARY key??

2005-04-26 Thread Jigal van Hemert
From: Jochem van Dieten Why is this? 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

Re: why NOT NULL in PRIMARY key??

2005-04-26 Thread Paul DuBois
At 16:25 +0200 4/26/05, Jigal van Hemert wrote: From: Paul DuBois Hi Paul, A primary key absolutely forbids duplicate values. Indexes created with the UNIQUE keyword do not allow duplicates, except for the special case that multiple NULL values are allowed. I realise that it may (and is)

Re: extract numeric value from a string.

2005-04-26 Thread Eamon Daly
Good point. I assumed that number meant a real number. This should work for leading zeroes: SELECT tag, @num := CONVERT(tag, SIGNED) AS cast_num, SUBSTRING(tag, 1, LOCATE(@num, tag) + LENGTH(@num) - 1) AS num_part, SUBSTRING(tag, LOCATE(@num, tag) + LENGTH(@num)) AS txt_part FROM tags;

Re: why NOT NULL in PRIMARY key??

2005-04-26 Thread Jigal van Hemert
From: Dawid Kuroczko It can't have anything to do with the 'uniqueness' of the data, since I can have a lot of 'zero'-values in the column, as long as the combination of columns in the PRIMARY key results in unique values. Because it is a PRIMARY KEY. I mean phrase 'PRIMARY KEY' means a

Re: why NOT NULL in PRIMARY key??

2005-04-26 Thread Jigal van Hemert
From: Paul DuBois I realise that it may (and is) defined in such a way, but it still does not explain *why* part of a PRIMARY key might not be NULL. If the combination of parts in the PRIMARY key is such that it can uniquely identify a record it would be sufficient for a primary key IMHO. It

Re: why NOT NULL in PRIMARY key??

2005-04-26 Thread Paul DuBois
At 16:47 +0200 4/26/05, Jigal van Hemert wrote: From: Dawid Kuroczko It can't have anything to do with the 'uniqueness' of the data, since I can have a lot of 'zero'-values in the column, as long as the combination of columns in the PRIMARY key results in unique values. Because it is a

Re: why NOT NULL in PRIMARY key??

2005-04-26 Thread Martijn Tonies
It can't have anything to do with the 'uniqueness' of the data, since I can have a lot of 'zero'-values in the column, as long as the combination of columns in the PRIMARY key results in unique values. Because it is a PRIMARY KEY. I mean phrase 'PRIMARY KEY' means a key with

Re: why NOT NULL in PRIMARY key??

2005-04-26 Thread SGreen
Jigal van Hemert [EMAIL PROTECTED] wrote on 04/26/2005 10:35:06 AM: From: Harald Fuchs id INT(11) - accountID name VARCHAR(32) - parameter name value INT(11) - parameter value Other tables contain string, datetime, etc. parameters. Since most searches are made for a value

Re: why NOT NULL in PRIMARY key??

2005-04-26 Thread Paul DuBois
At 16:56 +0200 4/26/05, Jigal van Hemert wrote: From: Paul DuBois I realise that it may (and is) defined in such a way, but it still does not explain *why* part of a PRIMARY key might not be NULL. If the combination of parts in the PRIMARY key is such that it can uniquely identify a record it

Re: why NOT NULL in PRIMARY key??

2005-04-26 Thread Mikhail Entaltsev
Jigal, create table YourTable ( id INT(11), name VARCHAR(32), value INT(11), PRIMARY KEY(id,name,value) ) let's assume that PRIMARY KEY works like you want (accept NULLs) and we have a row in your table: (id,name,value) = (1,NULL,12) Then you insert a new row: insert into YourTable

Re: why NOT NULL in PRIMARY key??

2005-04-26 Thread Jigal van Hemert
From: Paul DuBois I would understand it if it would mean that the key as a whole could not be NULL, but the restriction that each column that is part of a PRIMARY KEY must have the NOT NULL constraint is not logical. Sure it is. If any part could be NULL, then it could contain duplicate

Re: why NOT NULL in PRIMARY key??

2005-04-26 Thread Peter Brawley
Jigal, I would define the key as: parameter_name-value-account_id. InnoDB is very fast if you use the primary key and a lot slower if you use secudary key(s), so queries can get considerably faster if you use a primary key. One reason the PK is faster is that the engine needn't handle

RE: why NOT NULL in PRIMARY key??

2005-04-26 Thread Jay Blanchard
[snip] The same is true for any other value... Now that the columns have a NOT NULL constraint the records that previously contained NULL now hold '0'. x y x 0 x z x 0 Now, how do you uniquely identify the 2nd and 4th rows? [/snip] The database would have thrown an error when you tried to

Re: why NOT NULL in PRIMARY key??

2005-04-26 Thread Harald Fuchs
In article [EMAIL PROTECTED], Jigal van Hemert [EMAIL PROTECTED] writes: From: Harald Fuchs id INT(11) - accountID name VARCHAR(32) - parameter name value INT(11) - parameter value Other tables contain string, datetime, etc. parameters. Since most searches are made for a value (or

Re: why NOT NULL in PRIMARY key??

2005-04-26 Thread Martijn Tonies
I would understand it if it would mean that the key as a whole could not be NULL, but the restriction that each column that is part of a PRIMARY KEY must have the NOT NULL constraint is not logical. Sure it is. If any part could be NULL, then it could contain duplicate NULL values,

Re: why NOT NULL in PRIMARY key??

2005-04-26 Thread Jochem van Dieten
On 4/26/05, Jigal van Hemert wrote: From: Jochem van Dieten Why is this? Because the SQL standard says so. A true observation, but still no explanation or reason why ;-P I consider it a good enough explanation of why MySQL doesn't allow it. As to why the SQL standard doesn't allow it:

Re: Fw: modifying InnoDB storage

2005-04-26 Thread Gleb Paharenko
Hello. According to this: If your last data file was defined with the keyword autoextend, the procedure to edit my.cnf must take into account the size to which the last data file has grown. You have to look at the size of the data file, round the size downward to the closest multiple of

Re: ibdata1 grows beyound borders..

2005-04-26 Thread Gleb Paharenko
Hello. I don't think so. As I've understood InnoDB doesn't do it. See: http://dev.mysql.com/doc/mysql/en/innodb-file-space.html Eric Persson [EMAIL PROTECTED] wrote: Gleb Paharenko wrote: Hello. Searching in the archives says you could get worse performance, because of

Get a Random Row on a HUGE db

2005-04-26 Thread gunmuse
I am wanting to display a random page from my site, But I have over 12,000 articles right now and we add over 150 per day. What I wound up doing was a Virtual DOS attack on my own server because the 40 mb db was being loaded to many times. I have tons of memory and a Dell Dual Xeon 2.8

RE: Get a Random Row on a HUGE db

2005-04-26 Thread Jay Blanchard
[snip] I am wanting to display a random page from my site, But I have over 12,000 articles right now and we add over 150 per day. What I wound up doing was a Virtual DOS attack on my own server because the 40 mb db was being loaded to many times. I have tons of memory and a Dell Dual Xeon 2.8

Re: Get a Random Row on a HUGE db

2005-04-26 Thread Rhino
How about using the rand() function built into MySQL? You could use it to generate a random number, then find the row whose primary key equals that random number, then do a single-row select on that row. Rhino - Original Message - From: [EMAIL PROTECTED] To: Mysql

database design question

2005-04-26 Thread james tu
I have four different activities. Each has its own set of data that I want to save. So, I made four different tables to hold the saved data. Each record also has 'keywords' field (essentially this is the only field that all tables have in common.) Later on, I want to search all the keywords

Efficient select/insert

2005-04-26 Thread Jonathan Mangin
I would like to select several rows from one table and insert them into another nearly identical table using Perl/DBI: my @array = $q-param(); # HTML checkboxes foreach my $element (@array) { my $sql = select col2, col3, col4 from table1 where col1 = ?; my $sth =

Re: extract numeric value from a string.

2005-04-26 Thread mfatene
Hi all, The trick i can see if the string start with '0' is to make it starting with a positive number. FOr example if my string is '0123FOO' : set @a='0123FOO'; set @b=substring(0+concat('0',@a),-length(0+concat('0',@a))+1); select @b,replace(@a,@b,''); Mathias Selon gerald_clark [EMAIL

Error running mysql_install_db on Solaris10 x86

2005-04-26 Thread Joshua Mendenhall
Description: Running mysql_install_db generates an error. As follows: bash-3.00# scripts/mysql_install_db --user=mysql Installing all prepared tables Illegal Instruction - core dumped Installation of system tables failed! Examine the logs in ./data for more

Re: Query question

2005-04-26 Thread mfatene
sorry Chris again, i mean in what they speak about. i try help if i can, just that. :o) Mathias Selon Chris Ramsay [EMAIL PROTECTED]: Mathias There are no *bad* people on this list - different point of view, yes. Participating on this and other lists requires give AND take - taking advice

Re: JBoss queries aren't cached by MySQL

2005-04-26 Thread Mark Matthews
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Rafal Kedziorski wrote: Hi, we have the problem, that queries generated by JBoss or our code which runns under JBoss will be not cached by MySQL. The same query sendet from an external application or MySQLFront will be cached by the same

Re: extract numeric value from a string.

2005-04-26 Thread mfatene
I just did the oposite :o) set @a='0123FOO'; set @b=substring(0+concat('9',@a),-length(0+concat('9',@a))+1); select @b,replace(@a,@b,''); have to concat a positive number !!! Selon [EMAIL PROTECTED]: Hi all, The trick i can see if the string start with '0' is to make it starting with a

RE: why NOT NULL in PRIMARY key??

2005-04-26 Thread emierzwa
Martin, Shawn, you are correct. An oversight on my part...this is why I still follow this list, I am always able to learn something and never cease to be humbled. Ed -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 26, 2005 8:01 AM To: emierzwa;

Re: database design question

2005-04-26 Thread SGreen
james tu [EMAIL PROTECTED] wrote on 04/26/2005 12:06:34 PM: I have four different activities. Each has its own set of data that I want to save. So, I made four different tables to hold the saved data. Each record also has 'keywords' field (essentially this is the only field that all

Re: mysql threads and debian woody

2005-04-26 Thread mihai
Francesc Guaschwrote: Gleb Paharenko wrote: I thought it was OS related, and the mysql version was not the problem. So I tweaked the limit of bash and linux. In addition I don't want to loose the deb dependency. Is there something else I can try before ? I'm having the same problem, and

Re: database design question

2005-04-26 Thread James
I tried that and maybe I'm doing something wrong but... -I have to select the same number of columns...for each UNION -And each of the records from the union fall under the same column headings as the first SELECT... I even tried to define column aliases.. SELECT `running` as `running_blah`...

Re: Replication - is there a server lag?

2005-04-26 Thread Atle Veka
On Mon, 25 Apr 2005, Fagyal Csongor wrote: Hi, I am new to replication so excuse me if my question is stupid. The manual recommends that a nice scenario to take advantage of replication in MySQL is to send all updating queries to the master server, and reading from the slave. I would like

Re: database design question

2005-04-26 Thread SGreen
If you posted your actual table structures (SHOW CREATE TABLE xx\G) I think I could be more helpful. Right now I am just shooting in the dark. Shawn Green Database Administrator Unimin Corporation - Spruce Pine James [EMAIL PROTECTED] wrote on 04/26/2005 02:15:49 PM: I tried that and

RE: Get a Random Row on a HUGE db

2005-04-26 Thread gunmuse
Thanks for that I implemented to my Random code. Same problem that select * portion is just a nightmare. Remember I selecting 38mb of data when I do that. What I want to do is jump to a Valid random row. Now If I didn't delete content often that would be easy grab the last autoincremented

Re: Get a Random Row on a HUGE db

2005-04-26 Thread Peter Brawley
Gunmuse, SELECT from firebase_content LAST_INSERT_ID() In that cmd, 'from ...' ain't right. I didn't understand either what's wrong with ORDER BY RAND() LIMIT 1. Also check the Perl manual for how to retrieve a single value. PB - [EMAIL PROTECTED] wrote: Thanks for that I implemented to my

RE: Get a Random Row on a HUGE db

2005-04-26 Thread gunmuse
What I had to do was do this for my navigation db and not my content db. My server can easily handle lots of calls to a 4mb table then tell it to fetch the content once that has been achieved. The reason I bringing this up is this seems to be a patched way of doing this. If I have 40,000 items

RE: Get a Random Row on a HUGE db

2005-04-26 Thread gunmuse
This difference between using a 40 mb table and 4mb table with the same traffic was a 70 server load versus a .9 server load. So it was the amount of data that I was selecting that was choking this feature. - [EMAIL PROTECTED] wrote: Thanks for that I implemented to my Random code. Same

Re: Efficient select/insert

2005-04-26 Thread Jonathan Mangin
- Original Message - From: Jonathan Mangin [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, April 26, 2005 11:26 AM Subject: Efficient select/insert I would like to select several rows from one table and insert them into another nearly identical table using Perl/DBI: my @array

Re: Efficient select/insert

2005-04-26 Thread SGreen
Jonathan Mangin [EMAIL PROTECTED] wrote on 04/26/2005 12:26:20 PM: I would like to select several rows from one table and insert them into another nearly identical table using Perl/DBI: my @array = $q-param(); # HTML checkboxes foreach my $element (@array) { my $sql = select col2,

Re: Efficient select/insert

2005-04-26 Thread Jonathan Mangin
- Original Message - From: [EMAIL PROTECTED] To: Jonathan Mangin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, April 26, 2005 3:20 PM Subject: Re: Efficient select/insert Jonathan Mangin [EMAIL PROTECTED] wrote on 04/26/2005 12:26:20 PM: I would like to select several rows

Re: database design question

2005-04-26 Thread James
I haven't created real project tables yet. But here are the test ones that I'm experimenting with. CREATE TABLE east ( id int(11) NOT NULL auto_increment, keywords varchar(255) default NULL, east_1 varchar(255) default NULL, PRIMARY KEY (id) ) ; CREATE TABLE north ( north_id int(11) NOT

Re: Get a Random Row on a HUGE db

2005-04-26 Thread Gary Richardson
Why don't you generate a random integer in your code and select for an article? If there is no article there, do it again. Even if you have to call it 50 times it may be faster than doing a full scan on the table. It may not work so well if there are lots of gaps in your autoincrement. In perl

Multi Table Delete in 3.23.47

2005-04-26 Thread zzapper
Hi, The following query runs fine in recent versions of mysql 4.1x etc delete from t2 using tbl_User as t1,tbl_UserTopic as t2 where (t1.txtemail='[EMAIL PROTECTED]') and (t1.intID = t2.intID); But not in 3.23.47 I can't find any reference in the manuals to why this might be. Is there any

RE: MYSQL to XML

2005-04-26 Thread Mikel -
Mathias Thanks for your help, I really appreciated it. And I was just wondering if MySQL has another statment (besides show create table) that only displays the foreign key, but I see that only with the show create table MyTable could get this. Greetings From: [EMAIL PROTECTED] To: Mikel -

Re: Multi Table Delete in 3.23.47

2005-04-26 Thread zzapper
On Tue, 26 Apr 2005 23:22:46 +0100, wrote: Hi, The following query runs fine in recent versions of mysql 4.1x etc delete from t2 using tbl_User as t1,tbl_UserTopic as t2 where (t1.txtemail='[EMAIL PROTECTED]') and (t1.intID = t2.intID); But not in 3.23.47 I can't find any reference in the

update and select question

2005-04-26 Thread 黄高峰
i use this mail first . (B (Bi have a problem in under sql program: (B (B (BUPDATE (BNGLDENHDT (BSET (BEDT_HUK_FLG = :EDT_HUK_FLG (BWHERE (B KAI_CDE = :KAI_CDE (B AND EDT_NUM = (SELECT MAX(EDT_NUM) (B FROM NGLDENHDT (B

update and select question

2005-04-26 Thread 黄高峰
(Bi use this mail first . (B (Bi have a problem in under sql program: (B (B (BUPDATE (BNGLDENHDT (BSET (BEDT_HUK_FLG = :EDT_HUK_FLG (BWHERE (B KAI_CDE = :KAI_CDE (B AND EDT_NUM = (SELECT MAX(EDT_NUM) (B FROM NGLDENHDT (B

FW: update and select question

2005-04-26 Thread J.R. Bullington
http://dev.mysql.com/doc/mysql/en/update.html (B (BSee the last line on the page. You cannot, in the most current stable (Bversion of MySQL, SELECT from the table you are trying to UPDATE. (B (BJ.R. (B (B-Original Message- (BFrom: $B2+9bJv(B [mailto:[EMAIL PROTECTED] (BSent: