date:20050426

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
Access is a DBMS (backend) plus graphical frontend. The frontend of MS
Access has VBA for all kinds of scripting purposes.

If you use Perl or PHP as a frontend you can use those languages to process
query results, etc. If you use something like OpenOffice.org as a frontend
you can use the Basic language that is built into OpenOffice.org.

MySQL provides a very, very basic frontend through the MySQL client that
provides the MySQL prompt, but this frontend (client) does not contain any
scripting language.

Regards, Jigal.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: LOAD DATA INFILE and STR_TO_DATE

 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, serviceType,STR_TO_DATE(gmtDate,'%m/%d/%Y');

 The date in the CSV-file field #3 is in format MM/DD/ and therefore
not importet correctly;
 Obvisouly I'm missing something on how to use functions for columns with
LOAD DATA INFILE ?

I'm afraid that the syntax states that you can only use a list of column
names.

I think the best you can do is make an extra varchar column to hold the raw
'date' value from the csv and use an extra query to convert it into a format
suitable for the gmtDate column.

Regards, Jigal.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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 called procedural languages, as seen in
most other DBs (Oracle's PLSQL, PostgreSQL
PLpgSQL/plperl/pltcl/plpython, DB2's SQL PL, etc.).

If you want them you have to use bleeding edge :) MySQL version 5.0
or later (see: http://dev.mysql.com/doc/mysql/en/stored-procedures.html).
Haven't used it though.

   Regards,
 Dawid

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

 

 the my.cnf looks like:

 [mysqld]

 datadir=/usr/local/mysql/data/

 socket=/tmp/mysql.sock

 set-variable = max_connections=200

 set-variable = thread_cache_size=20

 innodb_data_home_dir =

 innodb_data_file_path = /usr/local/mysql/data/ibdata1:2000M:autoextend

 set-variable = innodb_buffer_pool_size=200M

 set-variable = innodb_additional_mem_pool_size=20M

 innodb_log_group_home_dir = /usr/local/mysql/data/innodb/

 innodb_log_arch_dir = /usr/local/mysql/data/innodb/

 set-variable = innodb_log_files_in_group=2

 set-variable = innodb_log_file_size=50M

 set-variable = innodb_log_buffer_size=8M

 innodb_flush_log_at_trx_commit=0

 set-variable = innodb_lock_wait_timeout=50

 set-variable = long_query_time=15

 #log-long-format

 log-slow-queries = /var/log/mysqld.slow.log

 

 

 [mysql.server]

 user=mysql

 basedir=/usr/local/mysql

 

 

 [safe_mysqld]

 err-log=/var/log/mysqld.log

 pid-file=/usr/local/mysql/data/db.bcmanga.se.pid

 

 The ibdata1 is now around 7gb and it keeps growing..

 

 How big can it be? Is it innodb dependent or os dependent? I'm currently 

 running Fedora Core release 3 (Heidelberg) on a dual xeon 3ghz with 3gb 

 ram.

 

 Can I just define some more ibdata files and it will re-arrange the data 

 by itself, or do I need to export and them import it? Or whats the best 

 practice here?

 

 Thanks in advance,

Eric

 

 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query question


 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 to know my level of knowledgne, 10 years, oracle, sybase,
 sqlserver, db2. I can help for migration from or to...
 I said don't use joins for the query given in the example or queries
 using just the joining columns from the first table.  Normal forms is
 bla bla here ...

10 yrs? Time to read a book then.

--
Martijn

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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 List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Fw: modifying InnoDB storage

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 12:31 PM
Subject: Re: modifying InnoDB storage


 From: Gleb Paharenko

 Hi Gleb,

 Thanks for your response.

http://dev.mysql.com/doc/mysql/en/adding-and-removing.html

 If it currently reads:
 innodb_data_file_path =

/ibdata/ibdata1:2000M;/ibdata/ibdata2:2000M;/ibdata/ibdata3:2000M;/ibdata/ib
 data4:10M:autoextend

 Can you change this into:
 =

/ibdata/ibdata1:2000M;/ibdata/ibdata2:2000M;/ibdata/ibdata3:2000M;/ibdata/ib
 data4:2000M;/ibdata/ibdata5:10M:autoexten
 or
 =

/ibdata/ibdata1:2000M;/ibdata/ibdata2:2000M;/ibdata/ibdata3:2000M;/ibdata/ib
 data5:2000M;/ibdata/ibdata4:10M:autoexten
 ???

  Think also about per-table tablespaces. See:

 Sorry, it's still 4.0.x that our sysadmin wants...

 Regards, Jigal.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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 FATENE
 
Hope that helps
*This not an official mysql support answer
 


-Original Message-
From: Martijn Tonies [mailto:[EMAIL PROTECTED] 
Sent: mardi 26 avril 2005 12:29
To: mysql@lists.mysql.com
Subject: Re: Query question



 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 to know my level of knowledgne, 10 years, oracle, sybase, 
 sqlserver, db2. I can help for migration from or to... I said don't 
 use joins for the query given in the example or queries using just the

 joining columns from the first table.  Normal forms is bla bla here 
 ...

10 yrs? Time to read a book then.

--
Martijn

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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 helps

Chris

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

why NOT NULL in PRIMARY key??

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 defined as NOT
NULL. If they are not explicitly declared as NOT NULL, MySQL declares them
so implicitly (and silently). 

Why is this? I actually do need NULL values (they really are not equal to
'0', etc.), but also need this column as part of the PRIMARY key in an
InnoDB table...
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.

Regards, Jigal.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: why NOT NULL in PRIMARY key??

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 be defined as NOT
NULL. If they are not explicitly declared as NOT NULL, MySQL declares
them
so implicitly (and silently). 

Why is this? I actually do need NULL values (they really are not equal
to
'0', etc.), but also need this column as part of the PRIMARY key in an
InnoDB table...
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.
[/snip]

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 vast difference between a zero value, blank value and NULL
(absence of value).

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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 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 unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


-- 
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 21/04/2005




-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 21/04/2005


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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;
++--++
| tag| num_part | rest_of_string |
++--++
| 1foo   |1 | foo|
| 23bar  |   23 | bar|
| 234baz |  234 | baz|
++--++
3 rows in set (0.00 sec)

Eamon Daly
Unless the string starts wit a '0'.

- Original Message - From: dixie [EMAIL PROTECTED]
To: MySQL mysql@lists.mysql.com
Sent: Friday, April 22, 2005 6:18 PM
Subject: extract numeric value from a string.

Hi at all, I've this necessity.
In a table I've a field popolated by a string where the first (not
costant lenght) part are number and the second part caracter.
I want extract, in other field, the first part and the second in another
field.
There is a function to obtained it?
Tks in advance
Paolo
--
dixie [EMAIL PROTECTED]



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: why NOT NULL in PRIMARY key??

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 an error message giving the first
duplicate is returned. Multiple NULL values are considered duplicates
when UNIQUE index is created.


SYBASE:
Create Unique Index
Prohibits duplicate index (also called key) values. The system checks
for duplicate key values when the index is created (if data already
exists), and checks each time data is added with an insert or update. If
there is a duplicate key value or if more than one row contains a null
value, the command is aborted and an error message giving the duplicate
is printed. 


 Ed

-Original Message-
From: Jay Blanchard [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, April 26, 2005 6:50 AM
To: Jigal van Hemert; mysql@lists.mysql.com
Subject: RE: why NOT NULL in PRIMARY key??

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 vast difference between a zero value, blank value and NULL
(absence of value).

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: why NOT NULL in PRIMARY key??



 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 defined as NOT
 NULL. If they are not explicitly declared as NOT NULL, MySQL declares them
 so implicitly (and silently). 

 Why is this? I actually do need NULL values (they really are not equal to
 '0', etc.), but also need this column as part of the PRIMARY key in an
 InnoDB table...
 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.

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?

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL  MS SQL
Server
Upscene Productions
http://www.upscene.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: why NOT NULL in PRIMARY key??

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 vast difference between a zero value, blank value and NULL
 (absence of value).

The situation is pretty challenging:
- because of high concurrency and huge tables I need to use InnoDB
- InnoDB really needs a PRIMARY key for fast results
- a PRIMARY key does not allow NULL values as part of the key

Anyhow, we something to think about...

Regards, Jigal.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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 with
no noticable improvement.

The server is running Linux 2.4.21-4 Elsmp RedHat 3.2.3.-20.

The server is dedicated to MySQL. The my.cnf file is
attached. 

There are 8 Gbytes of RAM and 2, Hyperthreaded CPUs (top
shows 4 processors).

SHOW STATUS shows very good buffer hit ratio. Current stats
are attached. Server was re-booted last Friday. It has been running the INSERT
query since Saturday mid-morning and has yet to complete.



Table 1 230
million rows total compund
PK index  4 columns range 1 should
select 35 million rows. Explain plan shows it to be using the PK

Table 2 598
million rows compound
PK index  4 columns and one secondary index. Range 1 should select about
130 million rows



Table 3 Empty
table unindexed. Two keys from Table 1 and 17 columns from table 2 populate this
table



The general form of the query is:



INSERT INTO Table 3 (col1, .
Col18)

SELECT

 Col1,,.col18

FROM

 Table
1 a

INNER JOIN Table 2 b ON (PK columns and range selection)

WHERE

 a.col5
= b.col5

AND

 .

AND

 

AND

 a.col18
= b.col18





Any suggestions are welcome.



Ed Sweeney








Variable_name   Value
Aborted_clients 52
Aborted_connects5
Binlog_cache_disk_use   0
Binlog_cache_use0
Bytes_received  337148
Bytes_sent  40680011
Com_admin_commands  0
Com_alter_db0
Com_alter_table 0
Com_analyze 0
Com_backup_table0
Com_begin   0
Com_change_db   38
Com_change_master   0
Com_check   0
Com_checksum0
Com_commit  0
Com_create_db   0
Com_create_function 0
Com_create_index0
Com_create_table1
Com_dealloc_sql 0
Com_delete  0
Com_delete_multi0
Com_do  0
Com_drop_db 0
Com_drop_function   0
Com_drop_index  0
Com_drop_table  0
Com_drop_user   0
Com_execute_sql 0
Com_flush   3
Com_grant   4
Com_ha_close0
Com_ha_open 0
Com_ha_read 0
Com_help0
Com_insert  0
Com_insert_select   4
Com_kill2
Com_load0
Com_load_master_data0
Com_load_master_table   0
Com_lock_tables 0
Com_optimize0
Com_preload_keys0
Com_prepare_sql 0
Com_purge   0
Com_purge_before_date   0
Com_rename_table0
Com_repair  0
Com_replace 0
Com_replace_select  0
Com_reset   0
Com_restore_table   0
Com_revoke  0
Com_revoke_all  0
Com_rollback0
Com_savepoint   0
Com_select  90
Com_set_option  15
Com_show_binlog_events  0
Com_show_binlogs0
Com_show_charsets   1
Com_show_collations 34
Com_show_column_types   0
Com_show_create_db  0
Com_show_create_table   2
Com_show_databases  3
Com_show_errors 0
Com_show_fields 25
Com_show_grants 0
Com_show_innodb_status  0
Com_show_keys   20
Com_show_logs   0
Com_show_master_status  0
Com_show_new_master 0
Com_show_open_tables0
Com_show_privileges 0
Com_show_processlist6603
Com_show_slave_hosts0
Com_show_slave_status   0
Com_show_status 6619
Com_show_storage_engines0
Com_show_tables 5
Com_show_variables  29
Com_show_warnings   0
Com_slave_start 0
Com_slave_stop  0
Com_truncate0
Com_unlock_tables   0
Com_update  0
Com_update_multi0
Connections 89
Created_tmp_disk_tables 2
Created_tmp_files   3
Created_tmp_tables  4
Delayed_errors  0
Delayed_insert_threads  0
Delayed_writes  0
Flush_commands  1
Handler_commit  0
Handler_delete  0
Handler_discover0
Handler_read_first  14
Handler_read_key34664442
Handler_read_next   2460468597
Handler_read_prev   0
Handler_read_rnd42
Handler_read_rnd_next   184703062
Handler_rollback0
Handler_update  1
Handler_write   348602337
Key_blocks_not_flushed  0
Key_blocks_unused   590180
Key_blocks_used 1811829
Key_read_requests   1691116639
Key_reads   16737509
Key_write_requests  148881620
Key_writes  1949178
Max_used_connections5
Not_flushed_delayed_rows0
Open_files  43
Open_streams0
Open_tables 23
Opened_tables   51
Qcache_free_blocks  1
Qcache_free_memory  25094216
Qcache_hits 14
Qcache_inserts  15
Qcache_lowmem_prunes0
Qcache_not_cached   47
Qcache_queries_in_cache 14

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 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 silently). 
 
 Why is this?

Because the SQL standard says so.

Jochem

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: why NOT NULL in PRIMARY key??

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 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 silently). 
Why is this? I actually do need NULL values (they really are not equal
to
'0', etc.), but also need this column as part of the PRIMARY key in an
InnoDB table...
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.
[/snip]
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.

I'm not on dev team, but my understanding of RDMS theory is that retrieving 
data via PK will always return a single row from the table.  Since NULL 
values are never considered equal to each other, allowing them in a column 
that is part of PK would bypass this expected behaviour. 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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 from the list anymore :-(
Can we ever get rid of the autoexpanding ibdata file without completely
rebuilding the databases?
unfortunately no. ibdata files cannot be removed or truncated.
You should consider using
innodb_file_per_table
if disk space is sparse.
Regards, Jigal.
Best regards,
Heikki
Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables
http://www.innodb.com/order.php

Order MySQL Network from http://www.mysql.com/network/

- Original Message - 
From: Jigal van Hemert [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Wednesday, April 20, 2005 12:31 PM
Subject: Re: modifying InnoDB storage


From: Gleb Paharenko
Hi Gleb,
Thanks for your response.
   http://dev.mysql.com/doc/mysql/en/adding-and-removing.html
If it currently reads:
innodb_data_file_path =
/ibdata/ibdata1:2000M;/ibdata/ibdata2:2000M;/ibdata/ibdata3:2000M;/ibdata/ib
data4:10M:autoextend
Can you change this into:
=
/ibdata/ibdata1:2000M;/ibdata/ibdata2:2000M;/ibdata/ibdata3:2000M;/ibdata/ib
data4:2000M;/ibdata/ibdata5:10M:autoexten
or
=
/ibdata/ibdata1:2000M;/ibdata/ibdata2:2000M;/ibdata/ibdata3:2000M;/ibdata/ib
data5:2000M;/ibdata/ibdata4:10M:autoexten
???
 Think also about per-table tablespaces. See:
Sorry, it's still 4.0.x that our sysadmin wants...
Regards, Jigal.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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 corresponds to your MySQL server version for the right 
syntax to use near 'WHERE MINUTE(CURRENT_TIMESTAMP()) = 45' at line 1
mysql 

It works only when I put a FROM with an existing table on it.
Is there a solution to do it without FROM or to use a virtual table?

Thanks in advance for your help.
-- 
Best regards,
Stephan Ferraro
NOOFS - Network Object Oriented File System for UNIX platforms.
Core Developper - http://noofs.com/
GnuPG public key: gpg --keyserver www.keyserver.net --recv-key 94B2664F

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: why NOT NULL in PRIMARY key??

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 identify each row and not use an internally generated ROWID (at
least that happens for InnoDB). PKs are critical to data integrity (and
usually internal table organization,too) and are frequently used as one
side of a Foreign Key (FK) relationship.

http://dev.mysql.com/doc/mysql/en/table-and-index.html
=
If you do not define a PRIMARY KEY for your table, MySQL picks the first
UNIQUE index that has only NOT NULL columns as the primary key and InnoDB
uses it as the clustered index. If there is no such index in the table,
InnoDB internally generates a clustered index where the rows are ordered
by the row ID that InnoDB assigns to the rows in such a table.
=

http://dev.mysql.com/doc/mysql/en/create-table.html
=
In MySQL, a UNIQUE index is one in which all values in the index must be
distinct. An error occurs if you try to add a new row with a key that
matches an existing row. The exception to this is that if a column in the
index is allowed to contain NULL values, it can contain multiple NULL
values. This exception does not apply to BDB tables, for which an indexed
column allows only a single NULL.
=
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 silently). A table can have only one PRIMARY KEY.
If you don't have a PRIMARY KEY and an application asks for the PRIMARY
KEY in your tables, MySQL returns the first UNIQUE index that has no NULL
columns as the PRIMARY KEY.
=

Notice that UNIQUE indexes in MySQL allow multiple nullable columns.
However because of their critical importance to record identification,
PRIMARY KEYs cannot contain null values. This is true even for the other
systems you quoted

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_04_888k.asp
(MSDN online documentation for MS SQL Server)

A table usually has a column or combination of columns whose values
uniquely identify each row in the table. This column (or columns) is
called the primary key of the table and enforces the entity integrity of
the table. You can create a primary key by defining a PRIMARY KEY
constraint when you create or alter a table.

A table can have only one PRIMARY KEY constraint, and a column that
participates in the PRIMARY KEY constraint cannot accept null values.
Because PRIMARY KEY constraints ensure unique data, they are often defined
for identity column.
===

http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/21064
(online Sybase Transact-SQL User's guide)
=
You can declare unique or primary key constraints to ensure that no two
rows in a table have the same values in the specified columns. Both
constraints create unique indexes to enforce this data integrity. However,
primary key constraints are more restrictive than unique constraints.
Columns with primary key constraints cannot contain a NULL value. You
normally use a table's primary key constraint in conjunction with
referential integrity constraints defined on other tables.
=

Respectfully,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

[EMAIL PROTECTED] wrote on 04/26/2005 09:16:03 AM:

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 an error message giving the first
duplicate is returned. Multiple NULL values are considered duplicates
when UNIQUE index is created.

SYBASE:
Create Unique Index
Prohibits duplicate index (also called key) values. The system checks
for duplicate key values when the index is created (if data already
exists), and checks each time data is added with an insert or update. If
there is a duplicate key value or if more than one row contains a null
value, the command is aborted and an error message giving the duplicate
is printed.

-Original Message-
From: Jay Blanchard [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 26, 2005 6:50 AM
To: Jigal van Hemert; mysql@lists.mysql.com
Subject: RE: why NOT NULL in PRIMARY key??

Since NULL is the absence of a

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): 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 'WHERE MINUTE(CURRENT_TIMESTAMP()) = 45' at line 1
 mysql 
 
 It works only when I put a FROM with an existing table on it.
 Is there a solution to do it without FROM or to use a virtual table?

What response do you want, exactly?

Try either of the two formulations below, depending upon whether you want 
to know what the minute is, or just whether or not it is 45 (returns 1 if 
it is).

mysql select minute(now()) ;
+---+
| minute(now()) |
+---+
|58 |
+---+
1 row in set (0.06 sec)

mysql select minute(now()) = 45 ;
++
| minute(now()) = 45 |
++
|  0 |
++
1 row in set (0.00 sec)


  Alec

 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: why NOT NULL in PRIMARY key??

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 parameters for accounts:

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 (on might argue that a part of the name would be sufficient,
but that is not the issue here).
Indeed almost all queries are very fast.

A NULL value indicates that it is a mandatory field that was not filled with
(a valid) value or that the value was reset, or...

Deleting the record is also not very nice, since it will prevent the use of
(inner) joins.

I have to think back why we dropped the left joins and started using NULL
values, but the first thing that came to mind was an increase in speed.

An extra challenge: the queries are generated because of the large number of
different queries that might be used. We can switch between left joins and
inner joins quickly though.

Regards, Jigal.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: why NOT NULL in PRIMARY key??

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 you a more informative response.
 There is a vast difference between a zero value, blank value and NULL
 (absence of value).
The situation is pretty challenging:
- because of high concurrency and huge tables I need to use InnoDB
- InnoDB really needs a PRIMARY key for fast results
- a PRIMARY key does not allow NULL values as part of the key
Anyhow, we something to think about...
Regards, Jigal.
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.
Such a unique index therefore cannot be a primary key.   However, for the
purposes you describe above, it sounds like the solution is simply to
define your columns as allowing NULL, and to create your index using UNIQUE
rather than PRIMARY KEY.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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 will not allow them access 
to their respective database any more. We get alot of access denied to 
databases... lock tables.. or other options.

TIA
--
Mike(mickalo)Blezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://thunder-rain.com/
Tel: 1.712.395.0670
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How to make a virtual SELECT?

[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): 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 'WHERE MINUTE(CURRENT_TIMESTAMP()) = 45' at line 1
 mysql 
 
 It works only when I put a FROM with an existing table on it.
 Is there a solution to do it without FROM or to use a virtual table?
 
 Thanks in advance for your help.
 -- 
 Best regards,
 Stephan Ferraro
 NOOFS - Network Object Oriented File System for UNIX platforms.
 Core Developper - http://noofs.com/
 GnuPG public key: gpg --keyserver www.keyserver.net --recv-key 94B2664F
 

There are several ways to approach this. My preferred method (because it 
is less version dependent) is to do your logical comparison AS your SELECT 
term and check for 0 or 1 as a result in your code

SELECT (MINUTE(CURRENT_TIMESTAMP())=45);

However, if you are stuck using the full select statement try FROM 
Dual. It's a compatibility enhancement (I think to keep converted Oracle 
code working) that was added in of 4.1.0.

SELECT MINUTE(CURRENT_TIMESTAMP()) 
FROM dual
WHERE MINUTE(CURRENT_TIMESTAMP()) = 45;
(http://dev.mysql.com/doc/mysql/en/select.html)

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

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 database?

 Martijn,

 The table contains an variable number of integer parameters for accounts:

 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

That's a horrible denormalization.  If one named parameter can hold
only one INT value for one account id, then (id, name) could be a
primary key; otherwise, you'd need a surrogate primary key.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: why NOT NULL in PRIMARY key??

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
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 could well be a UNIQUE index
with the restriction that the complete key (the parts combined) may not be
NULL...

 Such a unique index therefore cannot be a primary key.   However, for the
 purposes you describe above, it sounds like the solution is simply to
 define your columns as allowing NULL, and to create your index using
UNIQUE
 rather than PRIMARY KEY.

Unfortunately ther is a big difference in performance between the primary
and secudary indexes in InnoDB. We made (secundary) indexes and didn't have
a primary index at all (so MySQL used a 64-bit integer as primary key).
After we changed the index to primary the performance increased
considerably.
Some queries turned out to be quite slow and we found that these relied on
NULL values. Converting the index to primary silently converted all NULL
constraints to NOT NULL for the columns that are part of the primary key
and converted all NULL values in the db to the appropriate default values
for the various column types.

Regards, Jigal.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: why NOT NULL in PRIMARY key??



 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 an error message giving the first
 duplicate is returned. Multiple NULL values are considered duplicates
 when UNIQUE index is created.


 SYBASE:
 Create Unique Index
 Prohibits duplicate index (also called key) values. The system checks
 for duplicate key values when the index is created (if data already
 exists), and checks each time data is added with an insert or update. If
 there is a duplicate key value or if more than one row contains a null
 value, the command is aborted and an error message giving the duplicate
 is printed.

An unique index is not a primary key constraint.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL  MS SQL
Server
Upscene Productions
http://www.upscene.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: why NOT NULL in PRIMARY key??

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

 That's a horrible denormalization.  If one named parameter can hold
 only one INT value for one account id, then (id, name) could be a
 primary key; otherwise, you'd need a surrogate primary key.

And what if you want to find the IDs which have a certain value in a
parameter? Then you're searching for name and value and only need the id as
the glue for joins.

The sitution is that I have to store a variable (and changing) number of
parameters for a large number of IDs. The traditional construction of a
column for each parameter is not usable anymore:
- modifying the table structure for new parameters requires a lot of time
- all unused parameters will still take space
- design of index(es) is virtually impossible because searches are made on
various combinations of parameters
The parameter table solution is rather simple, but has some performance
complications once you have 26,000,000 records for 475,000 accounts in a
5,3GB database...

How would *you* normalize this situation? All suggestions are welcome!

Regards, Jigal.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: why NOT NULL in PRIMARY key??

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 silently). 
 
 Why is this? I actually do need NULL values (they really are not equal to
 '0', etc.), but also need this column as part of the PRIMARY key in an
 InnoDB table...
 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 which each row can be explicitly addressed.  So if you have 2000
rows in a table, you can write 2000 SELECT statemens which will use
columns in primary key and each of these SELECT statements will
return exactly one (different) row.

Since PRIMARY KEY is a primary key it cannot have NULL values.
And there can be only one primary key on the table, for the same
reason.

If your PRIMARY KEY would allow NULL values, it would not be possible
to address these rows with NULL values (*) and therefore it would not
be a real primary key, by definiton.  It would be a unique key.

(*).  Supposedly if there could be only one NULL value per column it
might be possible, but since NULL means unknown, it should not
be mixed with real values.

From what you are saying, you need a UNIQUE key, not a PRIMARY KEY.

   Regards,
  Dawid

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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 set because of a typo 
and no index in use for a join.

Second, I would check the size of the data file to see if it is 
growing. At least you'll be able to see if something is going on.

Third, I would try just the select part with a limit of say 10 or 20. 
This will show you how long MySQL is taking to do the search and joins, 
eliminating data transfer time.

You need to find where it's bottlenecking. It may very well be that you 
are I/O bound (iostat). MySQL is reading from the tables, certainly 
using temporary tables with a data set of that size and trying to load 
data into a new table. That's a lot of reads and writes going on at 
once, your disk(s) may be getting hammered, especially if you are not 
using RAID.

You could also try disabling indexes on table 3 (if you have any) and 
then enabling after the import is done. That will speed up the import 
process.

Lastly, instead of doing and insert select, just select to a file (INTO 
OUTFILE). Then import the data using load data infile. A two step 
processing, but something that will allow you to control all the I/O 
that's going on.

On Apr 26, 2005, at 9:22 AM, Ed Sweeney wrote:
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 with no 
noticable improvement.

The server is running Linux 2.4.21-4 Elsmp RedHat 3.2.3.-20.
The server is dedicated to MySQL. The my.cnf file is attached.
 There are 8 Gbytes of RAM and 2, Hyperthreaded CPUs (top shows 4 
processors).

SHOW STATUS shows very good buffer hit ratio. Current stats are 
attached. Server was re-booted last Friday. It has been running the 
INSERT query since Saturday mid-morning and has yet to complete.


Table 1 230 million rows total compund PK index  4 
columns range 1 should select 35 million rows. Explain plan shows it 
to be using the PK

Table 2 598 million rows compound PK index  
4 columns and one secondary index. Range 1 should select about 130 
million rows


Table 3 Empty table unindexed. Two keys from Table 1 and 
17 columns from table 2 populate this table


The general form of the query is:

INSERT INTO Table 3 (col1, . Col18)
SELECT
 Col1,,.col18
FROM
 Table 1 a
INNER JOIN Table 2 b ON (PK columns and range selection)
WHERE
 a.col5 = b.col5
AND
 .
AND
 
AND
 a.col18 = b.col18


Any suggestions are welcome.

Ed Sweeney

show_status.txt--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search  Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: why NOT NULL in PRIMARY key??


  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 PRIMARY key might not be NULL.

Because a value in a PK should uniquely identify a row.

Using one or more NULLs negates this...

The best systems don't use NULLs at all. NULL is an abomination.

You should only store values that are true.

Eg: employee with number 1 has a name of Martijn.

If salary is optional, do not make it nullable, but create a separate
table employee_salaries.

This way, there's never NULL confusion either.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL  MS SQL
Server
Upscene Productions
http://www.upscene.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: why NOT NULL in PRIMARY key??

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 always did it
this way.

Regards, Jigal.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: why NOT NULL in PRIMARY key??

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) 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 could well be a UNIQUE index
with the restriction that the complete key (the parts combined) may not be
NULL...
I'm not sure I understand what you are saying.  But if part of a PRIMARY KEY
could be NULL, then it _wouldn't_ uniquely identify records.


 Such a unique index therefore cannot be a primary key.   However, for the
 purposes you describe above, it sounds like the solution is simply to
 define your columns as allowing NULL, and to create your index using
UNIQUE
 rather than PRIMARY KEY.
Unfortunately ther is a big difference in performance between the primary
and secudary indexes in InnoDB. We made (secundary) indexes and didn't have
a primary index at all (so MySQL used a 64-bit integer as primary key).
After we changed the index to primary the performance increased
considerably.
Some queries turned out to be quite slow and we found that these relied on
NULL values. Converting the index to primary silently converted all NULL
constraints to NOT NULL for the columns that are part of the primary key
and converted all NULL values in the db to the appropriate default values
for the various column types.
Regards, Jigal.

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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;
+-+--+--+--+
| tag | cast_num | num_part | txt_part |
+-+--+--+--+
| 1foo|1 | 1| foo  |
| 23bar   |   23 | 23   | bar  |
| 234baz  |  234 | 234  | baz  |
| 001quux |1 | 001  | quux |
+-+--+--+--+
Same concept, but the LOCATE finds the first occurrence of
the casted number. Then add the length of the casted number
et voila.

Eamon Daly

- Original Message - 
From: gerald_clark [EMAIL PROTECTED]
To: Eamon Daly [EMAIL PROTECTED]
Cc: dixie [EMAIL PROTECTED]; MySQL mysql@lists.mysql.com
Sent: Tuesday, April 26, 2005 8:10 AM
Subject: Re: extract numeric value from a string.


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;
++--++
| tag| num_part | rest_of_string |
++--++
| 1foo   |1 | foo|
| 23bar  |   23 | bar|
| 234baz |  234 | baz|
++--++
3 rows in set (0.00 sec)

Eamon Daly
Unless the string starts wit a '0'.

- Original Message - From: dixie [EMAIL PROTECTED]
To: MySQL mysql@lists.mysql.com
Sent: Friday, April 22, 2005 6:18 PM
Subject: extract numeric value from a string.

Hi at all, I've this necessity.
In a table I've a field popolated by a string where the first (not
costant lenght) part are number and the second part caracter.
I want extract, in other field, the first part and the second in another
field.
There is a function to obtained it?
Tks in advance
Paolo
--
dixie [EMAIL PROTECTED]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: why NOT NULL in PRIMARY key??

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 key
 with which each row can be explicitly addressed.  So if you have 2000
 rows in a table, you can write 2000 SELECT statemens which will use
 columns in primary key and each of these SELECT statements will
 return exactly one (different) row.
With the NULL values included it will still uniquely identify each row...

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.

 If your PRIMARY KEY would allow NULL values, it would not be possible
 to address these rows with NULL values (*) and therefore it would not
 be a real primary key, by definiton.  It would be a unique key.

That would be true for the entire key, but not for each part of the key...

Regards, Jigal.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: why NOT NULL in PRIMARY key??

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 could well be a UNIQUE
index
 with the restriction that the complete key (the parts combined) may not
be
 NULL...

 I'm not sure I understand what you are saying.  But if part of a PRIMARY
KEY
 could be NULL, then it _wouldn't_ uniquely identify records.

It would IMHO if the other parts combined would be unique.

In this case it's a table that contains account_id, parameter_name and
value.
Account_id and parameter_name would be sufficient to uniquely identify a
records (only one parameter with the same name per account allowed).

But since searches use the parameter_name/value combination in almost all
cases 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.
My combined key would be able to uniquely identify records. I know the SQL
standard defines a PRIMARY KEY as a combination between UNIQUE and NOT NULL,
but it's still not clear to me why this implies that all *parts* of the
primary key *must* also have the NOT NULL constraint.

Regards, Jigal.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: why NOT NULL in PRIMARY key??

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 PRIMARY KEY.  I mean phrase 'PRIMARY KEY' means a key
 with which each row can be explicitly addressed.  So if you have 2000
 rows in a table, you can write 2000 SELECT statemens which will use
 columns in primary key and each of these SELECT statements will
 return exactly one (different) row.
With the NULL values included it will still uniquely identify each row...
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, thus compromising uniqueness of that part, and of the entire
key as well.
Suppose you have a two part key on columns a and b, with b allowed to
be NULL.  Then you could have these values:
x y
x NULL
x z
x NULL
Now, how do you uniquely identify the 2nd and 4th rows?

  If your PRIMARY KEY would allow NULL values, it would not be possible
 to address these rows with NULL values (*) and therefore it would not
 be a real primary key, by definiton.  It would be a unique key.
That would be true for the entire key, but not for each part of the key...
You have it backwards.  Any individual column in a primary key might
contain duplicates.  But the combination of values in all of the columns
must be unique.

Regards, Jigal.

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: why NOT NULL in PRIMARY key??


   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 which each row can be explicitly addressed.  So if you have 2000
  rows in a table, you can write 2000 SELECT statemens which will use
  columns in primary key and each of these SELECT statements will
  return exactly one (different) row.
 With the NULL values included it will still uniquely identify each row...

 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.

It isn't?

Imagine this:

mytable with PK
(id, value)

1, NULL
1, NULL
2, value
2, NULL

now, given that NULL  NULL ... what row would 1, NULL identify?

Or would you only allow a single NULL for each part of the PK?

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL  MS SQL
Server
Upscene Productions
http://www.upscene.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: why NOT NULL in PRIMARY key??

Jigal van Hemert [EMAIL PROTECTED] 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 (or range) of one or more
   parameters, a usable primary key is:
   name-value-id
 
  That's a horrible denormalization.  If one named parameter can hold
  only one INT value for one account id, then (id, name) could be a
  primary key; otherwise, you'd need a surrogate primary key.
 
 And what if you want to find the IDs which have a certain value in a
 parameter? Then you're searching for name and value and only need the id 
as
 the glue for joins.
 
 The sitution is that I have to store a variable (and changing) number of
 parameters for a large number of IDs. The traditional construction of a
 column for each parameter is not usable anymore:
 - modifying the table structure for new parameters requires a lot of 
time
 - all unused parameters will still take space
 - design of index(es) is virtually impossible because searches are made 
on
 various combinations of parameters
 The parameter table solution is rather simple, but has some performance
 complications once you have 26,000,000 records for 475,000 accounts in a
 5,3GB database...
 
 How would *you* normalize this situation? All suggestions are welcome!
 
 Regards, Jigal.
 

I think what tripped us up (and raised the red flags) was that you called 
your index on (id, name, value) a PRIMARY KEY. However, the unique 
combination of values on your parameters table should have only been for 
(id, name), right?  The id-name combination will uniquely identify a 
parameter for an account (and each account cannot have the same parameter 
more than once, can it?). So your PK should be on (id, name) and a regular 
index could be placed on (id, name, value) as a covering index.

However, your covering index will be a complete duplicate of your table 
and based on the numbers you give us, it probably won't fit into memory. 
You would end up trying to use paged memory to search an index to get at 
the value of the 'value' column just to avoid an extra trip to the disk to 
get the value based on an PK match.  I don't think the extra effort (and 
disk space used and working memory consumed) would actually help your 
retrieval speed. If it were me, I would not index that table beyond the PK 
on just (id, name). But, I would still test it both ways just to be sure.

I had a similar situation as you and chose a similar solution. My data 
storage challenge was to make millions of laboratory reports accessible 
for statistical analysis. Each report can contain any number of analyses 
on them (from 2 to 60) depending on what tests were requested when the 
sample was submitted to the lab. I also created a table for reports and a 
separate table for each test-result pair (fortunately for me, all of my 
results were numeric or could be represented with numbers). The two tables 
are linked through a FK (I am also using InnoDB). Not only is this saving 
space but the design is flexible because I do not need to change my tables 
every time the lab comes up with a new analysis it can perform (for the 
exact same reasons you described above).

Don't get discouraged, OK? ;-)
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: why NOT NULL in PRIMARY key??

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
 would be sufficient for a primary key IMHO. It could well be a UNIQUE
index
 with the restriction that the complete key (the parts combined) may not
be
 NULL...
 I'm not sure I understand what you are saying.  But if part of a PRIMARY
KEY
 could be NULL, then it _wouldn't_ uniquely identify records.
It would IMHO if the other parts combined would be unique.
If the other parts are unique, then you don't need the nullable column
in your primary key.  Just define it with the other columns.

In this case it's a table that contains account_id, parameter_name and
value.
Account_id and parameter_name would be sufficient to uniquely identify a
records (only one parameter with the same name per account allowed).
But since searches use the parameter_name/value combination in almost all
cases 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.
My combined key would be able to uniquely identify records. I know the SQL
standard defines a PRIMARY KEY as a combination between UNIQUE and NOT NULL,
but it's still not clear to me why this implies that all *parts* of the
primary key *must* also have the NOT NULL constraint.
Regards, Jigal.

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: why NOT NULL in PRIMARY key??

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 (id,name,value) values (1,NULL,12).

Before inserting MySQL will try to find a record with the same values.
But since comparison with NULL value returns always FALSE
MySQL will think that there is no such record in the table.
After this point you will get 2 identical records in the table.

Mikhail.


- Original Message - 
From: Jigal van Hemert [EMAIL PROTECTED]
To: Dawid Kuroczko [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Tuesday, April 26, 2005 4:47 PM
Subject: Re: why NOT NULL in PRIMARY key??


 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 key
  with which each row can be explicitly addressed.  So if you have 2000
  rows in a table, you can write 2000 SELECT statemens which will use
  columns in primary key and each of these SELECT statements will
  return exactly one (different) row.
 With the NULL values included it will still uniquely identify each row...

 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.

  If your PRIMARY KEY would allow NULL values, it would not be possible
  to address these rows with NULL values (*) and therefore it would not
  be a real primary key, by definiton.  It would be a unique key.

 That would be true for the entire key, but not for each part of the key...

 Regards, Jigal.


 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: why NOT NULL in PRIMARY key??

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
 NULL values, thus compromising uniqueness of that part, and of the entire
 key as well.

 Suppose you have a two part key on columns a and b, with b allowed to
 be NULL.  Then you could have these values:

 x y
 x NULL
 x z
 x NULL

 Now, how do you uniquely identify the 2nd and 4th rows?

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?


If your PRIMARY KEY would allow NULL values, it would not be possible
   to address these rows with NULL values (*) and therefore it would not
   be a real primary key, by definiton.  It would be a unique key.
 
 That would be true for the entire key, but not for each part of the
key...

 You have it backwards.  Any individual column in a primary key might
 contain duplicates.  But the combination of values in all of the columns
 must be unique.

Which is the case! The same key definition would be valid as a UNIQUE key
plus no combined key value is NULL.
It just seems a matter of a definition in the SQL standard...

Regards, Jigal.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: why NOT NULL in PRIMARY key??

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 NULLs in
it.

My combined key would be able to uniquely identify records. 

What!?? NULL has no length. In key values like stringvalueNULLintvalue,
what position in the key value is the engine supposed to consider the
beginning of intvalue!?

I know the SQL standard defines a PRIMARY KEY as a combination 
between UNIQUE and NOT NULL, but it's still not clear to me why 
this implies that all *parts*
of the primary key *must* also have the 
NOT NULL constraint.

Allow NULLs as PK components and you destroy two things at once, PK
logic and performance.

PB

-

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
  
  

  would be sufficient for a primary key IMHO. It could well be a UNIQUE
  

  
  index
  
  

  with the restriction that the complete key (the parts combined) may not
  

  
  be
  
  

  NULL...
  

I'm not sure I understand what you are saying.  But if part of a PRIMARY

  
  KEY
  
  
could be NULL, then it _wouldn't_ uniquely identify records.

  
  
It would IMHO if the other parts combined would be unique.

In this case it's a table that contains account_id, parameter_name and
value.
Account_id and parameter_name would be sufficient to uniquely identify a
records (only one parameter with the same name per account allowed).

But since searches use the parameter_name/value combination in almost all
cases 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.
My combined key would be able to uniquely identify records. I know the SQL
standard defines a PRIMARY KEY as a combination between UNIQUE and NOT NULL,
but it's still not clear to me why this implies that all *parts* of the
primary key *must* also have the NOT NULL constraint.

Regards, Jigal.


  



No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: why NOT NULL in PRIMARY key??

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 create row 4.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: why NOT NULL in PRIMARY key??

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 range) of one or more
  parameters, a usable primary key is:
  name-value-id
 
 That's a horrible denormalization.  If one named parameter can hold
 only one INT value for one account id, then (id, name) could be a
 primary key; otherwise, you'd need a surrogate primary key.

 How would *you* normalize this situation? All suggestions are welcome!

Your table structure is fine AFAICS, but a primary key should span a
minimum number of columns uniquely identifying a single row.  value
does not meet that need.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: why NOT NULL in PRIMARY key??


  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, thus compromising uniqueness of that part, and of the
entire
  key as well.
 
  Suppose you have a two part key on columns a and b, with b allowed to
  be NULL.  Then you could have these values:
 
  x y
  x NULL
  x z
  x NULL
 
  Now, how do you uniquely identify the 2nd and 4th rows?
 
 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?

Yes well, exactly the point: this is not possible in a primary key :-)

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL  MS SQL
Server
Upscene Productions
http://www.upscene.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: why NOT NULL in PRIMARY key??

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: NULL doesn't fit
particularly well in relational theory and there has probably been
considerable pressure from certain vendors (imagine the problems when
an empty string is indistinguishable from a NULL so both '' = '' and
NULL  NULL must be true, but now not just for some wacky varchar but
for your primary key).

Just speculation of course :)

Jochem

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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 1024 * 1024 bytes (= 1MB), and 
specify the rounded size explicitly in innodb_data_file_path. Then you can add 
another data file.



I think  you can.







Jigal van Hemert [EMAIL PROTECTED] wrote:

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

-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

 

 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Get a Random Row on a HUGE db




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

Can someone think up 
a better way of doing this? I wish Mysql would just bring me back 1 valid 
random row It could be used in so many ways it should just be a part of 
MySql anyway.

?phpini_set("display_errors", '1');header("Pragma: 
private");header("Cache-Control: post-check=0, pre-check=0", false); 
header("Cache-Control: no-cache, 
must-revalidate");require_once("firebase.conf.php");$dbi = new 
DBI(DB_URL);$stmt = "Select * from firebase_content Order By rand() DESC 
Limit 0, 1";$result = $dbi-query($stmt);while($row = 
$result-fetchRow()){$title = $row-title;$cate = 
$row-category;$get = "Select cat_url from firebase_categories 
where cat_name='$cate'";$now = $dbi-query($get);$rows = 
$now-fetchRow();$url = "">$link = $url . 
$title;}header("Location: http://www.prnewsnow.com/$link");exit;/* 
Sudo code that I am trying to create to relieve server stress.function 
randomRow(table, column) {var maxRow = query("SELECT MAX($column) AS maxID 
FROM $table");var randomID;var randomRow;do {randomID = 
randRange(1, maxRow.maxID);randomRow = query("SELECT * FROM $table WHERE 
$column = $randomID");} while (randomRow.recordCount == 0); return 
randomRow;}*/?

ThanksDonny LairsonPresident29 
GunMuse LaneP.O. box 166Lakewood NM 88254http://www.gunmuse.com469 228 2183

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

Can someone think up a better way of doing this?  I wish Mysql would
just bring me back 1 valid random row  It could be used in so many ways
it should just be a part of MySql anyway.

?php
ini_set(display_errors, '1');
header(Pragma: private);
header(Cache-Control: post-check=0, pre-check=0, false); 
header(Cache-Control: no-cache, must-revalidate);
require_once(firebase.conf.php);
$dbi = new DBI(DB_URL);
$stmt = Select * from firebase_content Order By rand() DESC Limit 0,
1;
$result = $dbi-query($stmt);
while($row = $result-fetchRow())
{
 $title = $row-title;
 $cate = $row-category;
 $get = Select cat_url from firebase_categories where
cat_name='$cate';
 $now = $dbi-query($get);
 $rows = $now-fetchRow();
 $url = $rows-cat_url;
 $link = $url . $title;
}
header(Location: http://www.prnewsnow.com/$link;);
exit;
/* Sudo code that I am trying to create to relieve server stress.
function randomRow(table, column) {
var maxRow = query(SELECT MAX($column) AS maxID FROM $table);
var randomID;
var randomRow;
do {
randomID = randRange(1, maxRow.maxID);
randomRow = query(SELECT * FROM $table WHERE $column = $randomID);
} while (randomRow.recordCount == 0); return randomRow;
}
*/
?
[/snip]

Try this ...
SELECT * FROM foo ORDER BY RAND(NOW()) LIMIT 1;

12000 rows is not huge at all, so this should be pretty quick

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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 
  Sent: Tuesday, April 26, 2005 11:33 
  AM
  Subject: Get a Random Row on a HUGE 
  db
  
  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 gig.
  
  Can someone think 
  up a better way of doing this? I wish Mysql would just bring me back 1 
  valid random row It could be used in so many ways it should just be a 
  part of MySql anyway.
  
  ?phpini_set("display_errors", 
  '1');header("Pragma: private");header("Cache-Control: post-check=0, 
  pre-check=0", false); header("Cache-Control: no-cache, 
  must-revalidate");require_once("firebase.conf.php");$dbi = new 
  DBI(DB_URL);$stmt = "Select * from firebase_content Order By rand() DESC 
  Limit 0, 1";$result = $dbi-query($stmt);while($row = 
  $result-fetchRow()){$title = $row-title;$cate 
  = $row-category;$get = "Select cat_url from firebase_categories 
  where cat_name='$cate'";$now = $dbi-query($get);$rows 
  = $now-fetchRow();$url = "">$link = 
  $url . $title;}header("Location: http://www.prnewsnow.com/$link");exit;/* 
  Sudo code that I am trying to create to relieve server stress.function 
  randomRow(table, column) {var maxRow = query("SELECT MAX($column) AS maxID 
  FROM $table");var randomID;var randomRow;do {randomID = 
  randRange(1, maxRow.maxID);randomRow = query("SELECT * FROM $table WHERE 
  $column = $randomID");} while (randomRow.recordCount == 0); return 
  randomRow;}*/?
  
  ThanksDonny LairsonPresident29 
  GunMuse LaneP.O. box 166Lakewood NM 88254http://www.gunmuse.com469 228 2183 
  
  
  

  No virus found in this incoming message.Checked by AVG 
  Anti-Virus.Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 
  21/04/2005
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 21/04/2005

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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 in these tables...and 
then retrieve the saved information from the four different tables.

Question:
Should I just search each of the tables individually?
Or should I create another table that will hold the keywords, the 
tablename, and the ID of the saved record in that particular 
table...and then perform my search on this NEW table?

Thanks.
--
-James
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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 = $dbh-prepare($sql);
  $sth-execute($element) or die $sth-errstr();
  my @row = $sth-fetchrow_array;
  $sql = insert table2 (col1, col2, col3, col4)
  values (NULL, ?, ?, ?);
  $sth = $dbh-prepare($sql);
  $sth-execute($row[0], $row[1], $row[2]) or die $sth-errstr();
}
Is this efficient db interaction, or is there a better way?
This is 3.23 but can upgrade if necessary.
Thanks,
Jon

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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 PROTECTED]:

 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;
  ++--++
  | tag| num_part | rest_of_string |
  ++--++
  | 1foo   |1 | foo|
  | 23bar  |   23 | bar|
  | 234baz |  234 | baz|
  ++--++
  3 rows in set (0.00 sec)
 
  
  Eamon Daly

 Unless the string starts wit a '0'.

 
 
 
  - Original Message - From: dixie [EMAIL PROTECTED]
  To: MySQL mysql@lists.mysql.com
  Sent: Friday, April 22, 2005 6:18 PM
  Subject: extract numeric value from a string.
 
 
  Hi at all, I've this necessity.
  In a table I've a field popolated by a string where the first (not
  costant lenght) part are number and the second part caracter.
  I want extract, in other field, the first part and the second in another
  field.
  There is a function to obtained it?
 
  Tks in advance
 
  Paolo
  --
  dixie [EMAIL PROTECTED]
 
 
 


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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 information.
   You can also try to start the mysqld daemon with:
   ./bin/mysqld --skip-grant 
   You can use the command line tool
   ./bin/mysql to connect to the mysql
   database and look at the grant tables:
   shell ./bin/mysql -u root mysql
   mysql show tables
   Try 'mysqld --help' if you have problems with paths. Using --log
   gives you a log in ./data that may be helpful.
   The latest information about MySQL is available on the web at
   http://www.mysql.com
   Please consult the MySQL manual section: 'Problems running 
mysql_install_db',
   and the manual section that describes problems on your OS.
   Another information source is the MySQL email archive.
   Please check all of the above before mailing us!
   And if you do mail us, you MUST use the ./bin/mysqlbug script!
   bash-3.00#
How-To-Repeat:
   Install mysql-standard-5.0.4-beta or mysql-max-5.0.4-beta on Solaris 10
   x86 using the pkg.gz files and then run mysql_install_db.
Fix:

Submitter-Id:   
Originator:Super-User
Organization:

MySQL support: none
Synopsis:Error running mysql_install_db on Solaris10
Severity:critical
Priority:high
Category:mysql
Class:sw-bug
Release:mysql-5.0.4-beta-standard (MySQL Community Edition - 
Standard (GPL))

C compiler:
C++ compiler:
Environment:
  
System: SunOS Sol1 5.10 Generic i86pc i386 i86pc
Architecture: i86pc

Some paths:  /usr/bin/perl /usr/local/bin/make /opt/sfw/bin/gmake 
/opt/sfw/bin/gcc /usr/ucb/cc
GCC: Reading specs from /opt/sfw/lib/gcc/i386-pc-solaris2.10/3.4.2/specs
Configured with: ../gcc-3.4.2/configure --prefix=/opt/sfw 
--with-ld=/usr/ccs/bin/ld --with-gnu-as --with-as=/opt/sfw/bin/gas 
--enable-shared --disable-libgcj
Thread model: posix
gcc version 3.4.2
Compilation info: CC='cc'  CFLAGS='-xO3 -mt -fsimple=1 -ftrap=%none 
-nofstore -xbuiltin=%all -xlibmil -xlibmopt -xtarget=native'  CXX='CC'  
CXXFLAGS='-xO3 -mt -fsimple=1 -ftrap=%none -nofstore -xbuiltin=%all 
-xlibmil -xlibmopt -xtarget=native'  LDFLAGS=''  ASFLAGS=''
LIBC:
lrwxrwxrwx   1 root root   9 Feb 17 16:29 /lib/libc.so - 
libc.so.1
-rwxr-xr-x   1 root bin  1031152 Jan 23 02:18 /lib/libc.so.1
lrwxrwxrwx   1 root root  19 Feb 17 16:16 /usr/lib/libc.so 
- ../../lib/libc.so.1
lrwxrwxrwx   1 root root  19 Feb 17 16:16 /usr/lib/libc.so.1 
- ../../lib/libc.so.1
Configure command: ./configure '--prefix=/usr/local/mysql' 
'--localstatedir=/usr/local/mysql/data' 
'--libexecdir=/usr/local/mysql/bin' '--with-comment=MySQL Community 
Edition - Standard (GPL)' '--with-extra-charsets=complex' 
'--with-server-suffix=-standard' '--enable-thread-safe-client' 
'--enable-local-infile' '--with-named-curses=-lcurses' 
'--disable-shared' '--with-big-tables' '--with-readline' 
'--with-embedded-server' '--with-archive-storage-engine' '--with-innodb' 
'CC=cc' 'CFLAGS=-xO3 -mt -fsimple=1 -ftrap=%none -nofstore 
-xbuiltin=%all -xlibmil -xlibmopt -xtarget=native' 'CXXFLAGS=-xO3 -mt 
-fsimple=1 -ftrap=%none -nofstore -xbuiltin=%all -xlibmil -xlibmopt 
-xtarget=native' 'CXX=CC'

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

 Chris

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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 MySQL.
 
 I'm using JBoss 3.2.5 with JDBC 3.0.16 and MySQL 4.0.23a.
 
 Any idea why?
 
 
 Regards,
 Rafal
 
 

Rafal,

Define what you mean by not cached by MySQLDo you mean the query
cache? If so, what do your query cache settings look like?

-Mark

- --
Mark Matthews
MySQL AB, Software Development Manager - Connectivity
www.mysql.com
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.6 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFCbm1JtvXNTca6JD8RAtT9AJ9k8HnIkRh+U9UE3ROf+eff5ZudRgCgxNNI
KeS6Iiq5ttoKjZsaDlyXV74=
=gCL+
-END PGP SIGNATURE-

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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
 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 PROTECTED]:

  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;
   ++--++
   | tag| num_part | rest_of_string |
   ++--++
   | 1foo   |1 | foo|
   | 23bar  |   23 | bar|
   | 234baz |  234 | baz|
   ++--++
   3 rows in set (0.00 sec)
  
   
   Eamon Daly
 
  Unless the string starts wit a '0'.
 
  
  
  
   - Original Message - From: dixie [EMAIL PROTECTED]
   To: MySQL mysql@lists.mysql.com
   Sent: Friday, April 22, 2005 6:18 PM
   Subject: extract numeric value from a string.
  
  
   Hi at all, I've this necessity.
   In a table I've a field popolated by a string where the first (not
   costant lenght) part are number and the second part caracter.
   I want extract, in other field, the first part and the second in another
   field.
   There is a function to obtained it?
  
   Tks in advance
  
   Paolo
   --
   dixie [EMAIL PROTECTED]
  
  
  
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 
 






-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: why NOT NULL in PRIMARY key??

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; mysql@lists.mysql.com
Subject: Re: why NOT NULL in PRIMARY key??

 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 an error message giving the
first
 duplicate is returned. Multiple NULL values are considered duplicates
 when UNIQUE index is created.

 SYBASE:
 Create Unique Index
 Prohibits duplicate index (also called key) values. The system checks
 for duplicate key values when the index is created (if data already
 exists), and checks each time data is added with an insert or update.
If
 there is a duplicate key value or if more than one row contains a null
 value, the command is aborted and an error message giving the
duplicate
 is printed.

An unique index is not a primary key constraint.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL  MS
SQL
Server
Upscene Productions
http://www.upscene.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: database design question

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 tables have in common.)
 
 Later on, I want to search all the keywords in these tables...and 
 then retrieve the saved information from the four different tables.
 
 Question:
 Should I just search each of the tables individually?
 
 
 Or should I create another table that will hold the keywords, the 
 tablename, and the ID of the saved record in that particular 
 table...and then perform my search on this NEW table?
 
 Thanks.
 -- 
 -James
 


I would properly index each table and UNION the results of the 4 searches. 
Have you considered creating a Full Text index for your keyword fields?

Say your 4 tables are called: running, swimming, jumping, and walking

SELECT 'running', column list
FROM running
WHERE keywords search condition
UNION
SELECT 'swimming', column list
FROM swimming
WHERE keywords search condition
UNION
SELECT 'jumping', column list
FROM jumping
WHERE keywords search condition
UNION
SELECT 'walking', column list
FROM walking
WHERE keywords search condition;

I used the first column only to identify which table each match comes 
from. That way if you have records in each table with matching PK values, 
you know which table to go back to in order to get any additional 
information. The only problem with this type of search is that your 
column list columns must be compatible between each of the tables. If 
the second column is numeric in your first query then the second column 
will be coerced to numeric for each of the remaining 3 queries. If for 
some reason that fails, then the whole UNION fails and you get an error.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

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 I'm using mysql official binary. 
I have two servers, one is webserver and the other is the database
server. The webserver connects to the mysql server over tcp. 
at some times there are many connections and mysql creates more than
1000 threads and then just can't create any more



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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`...
-James

At 1:03 PM -0400 4/26/05, [EMAIL PROTECTED] wrote:
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 tables have in common.)
 Later on, I want to search all the keywords in these tables...and
 then retrieve the saved information from the four different tables.
 Question:
 Should I just search each of the tables individually?
 Or should I create another table that will hold the keywords, the
 tablename, and the ID of the saved record in that particular
 table...and then perform my search on this NEW table?
 Thanks.
 --
 -James

I would properly index each table and UNION the results of the 4 
searches. Have you considered creating a Full Text index for your 
keyword fields?

Say your 4 tables are called: running, swimming, jumping, and walking
SELECT 'running', column list
FROM running
WHERE keywords search condition
UNION
SELECT 'swimming', column list
FROM swimming
WHERE keywords search condition
UNION
SELECT 'jumping', column list
FROM jumping
WHERE keywords search condition
UNION
SELECT 'walking', column list
FROM walking
WHERE keywords search condition;
I used the first column only to identify which table each match 
comes from. That way if you have records in each table with matching 
PK values, you know which table to go back to in order to get any 
additional information. The only problem with this type of search is 
that your column list columns must be compatible between each of 
the tables. If the second column is numeric in your first query then 
the second column will be coerced to numeric for each of the 
remaining 3 queries. If for some reason that fails, then the whole 
UNION fails and you get an error.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

--
-James Tu
---
ESI Design
111 Fifth Avenue 12th floor
New York, NY 10003
(212) 989-3993 ext. 357
(212) 673-4061 (fax)
---

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 to use this setup (as
 usual, I have many more selects than inserts/updates) but I am a little
 concerned what happens if the slave is behind the master in updating its DB.

 Say I do like this:
 1. update something set `a`=1 where c=d (using the master server)
 2. update something set `a`=2 where c=d (using the master server)
 and then immediately
 3. select `a` from something where c=d (using the slave)

 What if #3 fetches the value of `a` from the slave before `a`=2 takes
 place? Is it possible that I get `a`==1? Or does replication take care
 of that?

If your replication setup is all local to your network, you shouldn't have
any problems. Replication is pretty much instant. We have setups where
there is 60k/s replication traffic (450 updates/s avg) and the slave is
very rarely lagging behind.


 Other than that: does anybody here have a Nagios script that checks if
 replication is running O.K.? :-)

I just put the one I use up on the web, use it at your own risk! :)
http://byveka.com/nagios/


Regards,

Atle
-
Flying Crocodile Inc, Unix Systems Administrator



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: database design question

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 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`...
 
 -James
 
 
 
 At 1:03 PM -0400 4/26/05, [EMAIL PROTECTED] wrote:
 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 tables have in common.)
 
   Later on, I want to search all the keywords in these tables...and
   then retrieve the saved information from the four different tables.
 
   Question:
   Should I just search each of the tables individually?
 
 
   Or should I create another table that will hold the keywords, the
   tablename, and the ID of the saved record in that particular
   table...and then perform my search on this NEW table?
 
   Thanks.
   --
   -James
 
 
 
 I would properly index each table and UNION the results of the 4 
 searches. Have you considered creating a Full Text index for your 
 keyword fields?
 
 Say your 4 tables are called: running, swimming, jumping, and walking
 
 SELECT 'running', column list
 FROM running
 WHERE keywords search condition
 UNION
 SELECT 'swimming', column list
 FROM swimming
 WHERE keywords search condition
 UNION
 SELECT 'jumping', column list
 FROM jumping
 WHERE keywords search condition
 UNION
 SELECT 'walking', column list
 FROM walking
 WHERE keywords search condition;
 
 I used the first column only to identify which table each match 
 comes from. That way if you have records in each table with matching 
 PK values, you know which table to go back to in order to get any 
 additional information. The only problem with this type of search is 
 that your column list columns must be compatible between each of 
 the tables. If the second column is numeric in your first query then 
 the second column will be coerced to numeric for each of the 
 remaining 3 queries. If for some reason that fails, then the whole 
 UNION fails and you get an error.
 
 Shawn Green
 Database Administrator
 Unimin Corporation - Spruce Pine
 
 -- 
 -James Tu
 ---
 ESI Design
 111 Fifth Avenue 12th floor
 New York, NY 10003
 (212) 989-3993 ext. 357
 (212) 673-4061 (fax)
 ---

RE: Get a Random Row on a HUGE db

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 row_id and
get a random number between 1 and End  Jump to that row to create the link.
Very fast. Zero load

So what I am trying is this.

$last_row =SELECT from firebase_content LAST_INSERT_ID();
$last_row_query = $dbi-query($last_row);
$last_row_result = $row-id;

But what I am seeing is this:

Object id #9

and not the number that is in the database.

What am I sending to this variable that is wrong?



[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 gig.

Can someone think up a better way of doing this?  I wish Mysql would
just bring me back 1 valid random row  It could be used in so many ways
it should just be a part of MySql anyway.

?php
ini_set(display_errors, '1');
header(Pragma: private);
header(Cache-Control: post-check=0, pre-check=0, false);
header(Cache-Control: no-cache, must-revalidate);
require_once(firebase.conf.php);
$dbi = new DBI(DB_URL);
$stmt = Select * from firebase_content ORDER BY RAND(NOW()) LIMIT 1;
$result = $dbi-query($stmt);
while($row = $result-fetchRow())
{
 $title = $row-title;
 $cate = $row-category;
 $get = Select cat_url from firebase_categories where
cat_name='$cate';
 $now = $dbi-query($get);
 $rows = $now-fetchRow();
 $url = $rows-cat_url;
 $link = $url . $title;
}
header(Location: http://www.prnewsnow.com/$link;);
exit;
/* Sudo code that I am trying to create to relieve server stress.
function randomRow(table, column) {
var maxRow = query(SELECT MAX($column) AS maxID FROM $table);
var randomID;
var randomRow;
do {
randomID = randRange(1, maxRow.maxID);
randomRow = query(SELECT * FROM $table WHERE $column = $randomID);
} while (randomRow.recordCount == 0); return randomRow;
}
*/
?
[/snip]

Try this ...
SELECT * FROM foo ORDER BY RAND(NOW()) LIMIT 1;

12000 rows is not huge at all, so this should be pretty quick

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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 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 row_id and
get a random number between 1 and End  Jump to that row to create the link.
Very fast. Zero load
So what I am trying is this.
$last_row =SELECT from firebase_content LAST_INSERT_ID();
$last_row_query = $dbi-query($last_row);
$last_row_result = $row-id;
But what I am seeing is this:
Object id #9
and not the number that is in the database.
What am I sending to this variable that is wrong?

[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 gig.
Can someone think up a better way of doing this?  I wish Mysql would
just bring me back 1 valid random row  It could be used in so many ways
it should just be a part of MySql anyway.
?php
ini_set(display_errors, '1');
header(Pragma: private);
header(Cache-Control: post-check=0, pre-check=0, false);
header(Cache-Control: no-cache, must-revalidate);
require_once(firebase.conf.php);
$dbi = new DBI(DB_URL);
$stmt = Select * from firebase_content ORDER BY RAND(NOW()) LIMIT 1;
$result = $dbi-query($stmt);
while($row = $result-fetchRow())
{
$title = $row-title;
$cate = $row-category;
$get = Select cat_url from firebase_categories where
cat_name='$cate';
$now = $dbi-query($get);
$rows = $now-fetchRow();
$url = $rows-cat_url;
$link = $url . $title;
}
header(Location: http://www.prnewsnow.com/$link;);
exit;
/* Sudo code that I am trying to create to relieve server stress.
function randomRow(table, column) {
var maxRow = query(SELECT MAX($column) AS maxID FROM $table);
var randomID;
var randomRow;
do {
randomID = randRange(1, maxRow.maxID);
randomRow = query(SELECT * FROM $table WHERE $column = $randomID);
} while (randomRow.recordCount == 0); return randomRow;
}
*/
?
[/snip]
Try this ...
SELECT * FROM foo ORDER BY RAND(NOW()) LIMIT 1;
12000 rows is not huge at all, so this should be pretty quick
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

 


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Get a Random Row on a HUGE db


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 in db that get updated and row_ids change for a
catalog and want to randomly display a product.

I should be able to ask Mysql for a Random valid row.  It indexes with a
Primary so it knows what valid at that time.  There is too much jumping
around just to say get random and be fair about it, so no one row comes up
every time or more often that others.


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 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 row_id and
get a random number between 1 and End  Jump to that row to create the link.
Very fast. Zero load

So what I am trying is this.

$last_row =SELECT from firebase_content LAST_INSERT_ID();
$last_row_query = $dbi-query($last_row);
$last_row_result = $row-id;

But what I am seeing is this:

Object id #9

and not the number that is in the database.

What am I sending to this variable that is wrong?



[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 gig.

Can someone think up a better way of doing this?  I wish Mysql would
just bring me back 1 valid random row  It could be used in so many ways
it should just be a part of MySql anyway.

?php
ini_set(display_errors, '1');
header(Pragma: private);
header(Cache-Control: post-check=0, pre-check=0, false);
header(Cache-Control: no-cache, must-revalidate);
require_once(firebase.conf.php);
$dbi = new DBI(DB_URL);
$stmt = Select * from firebase_content ORDER BY RAND(NOW()) LIMIT 1;
$result = $dbi-query($stmt);
while($row = $result-fetchRow())
{
 $title = $row-title;
 $cate = $row-category;
 $get = Select cat_url from firebase_categories where
cat_name='$cate';
 $now = $dbi-query($get);
 $rows = $now-fetchRow();
 $url = $rows-cat_url;
 $link = $url . $title;
}
header(Location: http://www.prnewsnow.com/$link;);
exit;
/* Sudo code that I am trying to create to relieve server stress.
function randomRow(table, column) {
var maxRow = query(SELECT MAX($column) AS maxID FROM $table);
var randomID;
var randomRow;
do {
randomID = randRange(1, maxRow.maxID);
randomRow = query(SELECT * FROM $table WHERE $column = $randomID);
} while (randomRow.recordCount == 0); return randomRow;
}
*/
?
[/snip]

Try this ...
SELECT * FROM foo ORDER BY RAND(NOW()) LIMIT 1;

12000 rows is not huge at all, so this should be pretty quick

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]








--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Get a Random Row on a HUGE db


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 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 row_id and
get a random number between 1 and End  Jump to that row to create the link.
Very fast. Zero load

So what I am trying is this.

$last_row =SELECT from firebase_content LAST_INSERT_ID();
$last_row_query = $dbi-query($last_row);
$last_row_result = $row-id;

But what I am seeing is this:

Object id #9

and not the number that is in the database.

What am I sending to this variable that is wrong?



[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 gig.

Can someone think up a better way of doing this?  I wish Mysql would
just bring me back 1 valid random row  It could be used in so many ways
it should just be a part of MySql anyway.

?php
ini_set(display_errors, '1');
header(Pragma: private);
header(Cache-Control: post-check=0, pre-check=0, false);
header(Cache-Control: no-cache, must-revalidate);
require_once(firebase.conf.php);
$dbi = new DBI(DB_URL);
$stmt = Select * from firebase_content ORDER BY RAND(NOW()) LIMIT 1;
$result = $dbi-query($stmt);
while($row = $result-fetchRow())
{
 $title = $row-title;
 $cate = $row-category;
 $get = Select cat_url from firebase_categories where
cat_name='$cate';
 $now = $dbi-query($get);
 $rows = $now-fetchRow();
 $url = $rows-cat_url;
 $link = $url . $title;
}
header(Location: http://www.prnewsnow.com/$link;);
exit;
/* Sudo code that I am trying to create to relieve server stress.
function randomRow(table, column) {
var maxRow = query(SELECT MAX($column) AS maxID FROM $table);
var randomID;
var randomRow;
do {
randomID = randRange(1, maxRow.maxID);
randomRow = query(SELECT * FROM $table WHERE $column = $randomID);
} while (randomRow.recordCount == 0); return randomRow;
}
*/
?
[/snip]

Try this ...
SELECT * FROM foo ORDER BY RAND(NOW()) LIMIT 1;

12000 rows is not huge at all, so this should be pretty quick

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]








--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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 = $q-param();  # HTML checkboxes
foreach my $element (@array) {
  my $sql = select col2, col3, col4 from table1
 where col1 = ?;
  my $sth = $dbh-prepare($sql);
  $sth-execute($element) or die $sth-errstr();
  my @row = $sth-fetchrow_array;
  $sql = insert table2 (col1, col2, col3, col4)
  values (NULL, ?, ?, ?);
  $sth = $dbh-prepare($sql);
  $sth-execute($row[0], $row[1], $row[2]) or die $sth-errstr();
}
Is this efficient db interaction, or is there a better way?
This is 3.23 but can upgrade if necessary.
Thanks,
Jon

Further...
I thought I could use fetchrow_arrayref and push an array
of arrays. The DBI docs say:
Note that the same array reference is returned for each fetch,
so don't store the reference and then use it after a later fetch.
Sounds like I can't use that. Now I see execute_for_fetch. Does this
sound like a job for execute_for_fetch?
--J

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Efficient select/insert