Re: transaction with bdb table gives error Lock table is out of available locks
Bdb tables locks are at page level, how can I determine the number of
pages of my table?
mysql show variables like '%binlog_cache_size%';
+---++
| Variable_name | Value |
+---++
| binlog_cache_size | 32768 |
| max_binlog_cache_size | 4294967295 |
+---++
How can I estimate the binlog chache size I need? The manual is very
vague about this issue..
Thanx,
Marco
sheeri kritzer wrote:
Hi Marco,
My hint is to read the BDB section of the manual:
http://dev.mysql.com/doc/refman/5.0/en/bdb-start.html
specifically the part that says:
With the bdb_max_lock variable, you can specify the maximum number
of locks that can be active on a BDB table. The default is 10,000. You
should increase this if errors such as the following occur when you
perform long transactions or when mysqld has to examine many rows to
execute a query:
bdb: Lock table is out of available locks
Got error 12 from ...
You may also want to change the binlog_cache_size and
max_binlog_cache_size variables if you are using large
multiple-statement transactions. See Section 5.11.3, The Binary Log.
What is your binlog_cache_size and max_binlog_cache_size?
-Sheeri
On 12/7/05, Marco Baroetto [EMAIL PROTECTED] wrote:
Hi,
I have a berkeley db table containing about 5 rows where I do this
transaction (pseudocode follows):
begin work
delete from mytable where myfield='boo' /*delete about 100 rows*/
for (i=0; i=100; i++){
insert into mytable values(...);
}
commit
During the insert command i get the following error:
Lock table is out of available locks
I tried to resolve the problem starting mysqld with -O
bdb_max_lock=6 and later with -O bdb_max_lock=12 but i still
receive the same error.
dbd related variables follows:
mysql show variables like %bdb%;
+-++
| Variable_name |
Value |
+-++
| bdb_cache_size |
8388600|
| bdb_home|
/var/lib/mysql/|
| bdb_log_buffer_size |
32768 |
| bdb_logdir
||
| bdb_max_lock|
12 |
| bdb_shared_data |
OFF|
| bdb_tmpdir |
/tmp/ |
| have_bdb|
YES|
| version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (May 13,
2005) |
+-++
9 rows in set (0.00 sec)
Any hint is welcome..
Thanx in advance for the help,
Marco
--
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: transaction with bdb table gives error Lock table is out of available locks
Hi, here's my create table: mysql show create table parametrivaloriplc; ++-+ | Table | Create Table| ++-+ | parametrivaloriplc | CREATE TABLE `parametrivaloriplc` ( `idParametroPLC` int(11) NOT NULL auto_increment, `name` varchar(100) NOT NULL default '', `idDataType` int(11) NOT NULL default '0', `unit` varchar(20) default NULL, `molt` int(11) default NULL, `div` int(11) default NULL, `accesso` char(3) NOT NULL default '', `idCategoriaParametriPLC` int(11) NOT NULL default '0', `max` int(11) default NULL, `min` int(11) default NULL, `idParametroPadre` int(11) default NULL, `idParametroMax` int(11) default NULL, `idParametroMin` int(11) default NULL, `invalidable` tinyint(1) default NULL, `id_centralina` int(10) unsigned NOT NULL default '0', `valore` varchar(50) NOT NULL default '', PRIMARY KEY (`idParametroPLC`) ) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC | ++-+ 1 row in set (0.00 sec) Tonight i had the following unexpected restart, may it be caused by the high value of bdb_max_lock? --- mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388600 read_buffer_size=131072 max_used_connections=12 max_connections=100 threads_connected=2 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x9964008 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xae3fa304, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x813198c 0x8a07c8 0x82770aa 0x82770aa 0x827651c 0x8276adb 0x824ee13 0x824f6d2 0x824fb82 0x81b6566 0x81a8bac 0x81855fa 0x814514a 0x8148dfa 0x8149638 0x814a263 0x814ab5a 0x89a341 0x71a6fe New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x996d548 = delete from parametrivaloriplc where id_centralina=343 thd-thread_id=1 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Memory status: Non-mmapped space allocated from system: 6262784 Number of free chunks: 18 Number of fastbin blocks:0 Number of mmapped regions: 11 Space in mmapped regions:86974464 Maximum total allocated space: 0 Space available in freed fastbin blocks: 0 Total allocated space: 5625120 Total free space:637664 Top-most, releasable space: 16 Estimated memory (with thread stack):93433856 Number of processes running now: 0 051209 01:00:31 mysqld restarted 051209 1:00:32 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 051209 1:00:33 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 52400171. InnoDB: Doing recovery: scanned up to log sequence number 0 52400171 051209 1:00:33 InnoDB: Flushing modified pages from the buffer pool... 051209 1:00:33 InnoDB: Started; log sequence number 0 52400171 051209 1:00:33 [Warning] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them /usr/libexec/mysqld: ready for connections. Version: '4.1.12' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution -- More details about my environment follows: # cat /proc/version Linux version 2.6.9-22.0.1.ELsmp ([EMAIL PROTECTED]) (gcc version 3.4.4 20050721 (Red Hat 3.4.4-2)) #1 SMP Thu Oct 27 13:14:25 CDT 2005 # mysql --version mysql Ver 14.7 Distrib 4.1.12, for redhat-linux-gnu (i686) using readline 4.3 # cat /etc/init.d/mysqld /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --pid-file=$mypidfile -O
Re: Optimize: 14 hours and still running!
Hello. As was mentioned by other members without seeing your configuration settings it is difficult to say about InnoDB performance. You can indirectly monitor the OPTIMIZE speed by ROW OPERATIONS section of SHOW INNODB STATUS. For InnoDB it maps to ALTER TABLE, which rebuilds the table. Nathan Gross wrote: On a 1.6ghz, 1gb ram, Linux machine running Mysql 4.1x. I have an Innodb table with over 20 million records and index size about 3.7 gig, data size 2.2gig (yes, many indexes, more space then the data itself). Last night I tried an Optimize from the Admin gui console (logged in as root at the host server), and the thing is still running! Problem is I need to leave early this evening and have to take some action. The Linux 'top' utility has it on the top since then at about 11%-18% cpu Disk activity is continuously heavy. 1. How long should it take? 2. If I hit cancel will it: a) Roll back what it did, another 14 hours! b) Just stop as if nothing happened. c) The table will be partially optimized and will run normally. d) hang the process and/or machine. 3. Is the data in jeopardy? Thank you all. -nat -- 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: transaction with bdb table gives error Lock table is out of available locks
Maybe i got it! For the chronicle: I resolved the problem decreasing the max_bdb_lock to the default value and creating an index on the column used in the where clause of my query. Probably mysql tried to lock the whole table because the primary key wasn't in the where clause. Cheers, Marco Marco Baroetto wrote: Hi, here's my create table: mysql show create table parametrivaloriplc; ++-+ | Table | Create Table| ++-+ | parametrivaloriplc | CREATE TABLE `parametrivaloriplc` ( `idParametroPLC` int(11) NOT NULL auto_increment, `name` varchar(100) NOT NULL default '', `idDataType` int(11) NOT NULL default '0', `unit` varchar(20) default NULL, `molt` int(11) default NULL, `div` int(11) default NULL, `accesso` char(3) NOT NULL default '', `idCategoriaParametriPLC` int(11) NOT NULL default '0', `max` int(11) default NULL, `min` int(11) default NULL, `idParametroPadre` int(11) default NULL, `idParametroMax` int(11) default NULL, `idParametroMin` int(11) default NULL, `invalidable` tinyint(1) default NULL, `id_centralina` int(10) unsigned NOT NULL default '0', `valore` varchar(50) NOT NULL default '', PRIMARY KEY (`idParametroPLC`) ) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC | ++-+ 1 row in set (0.00 sec) Tonight i had the following unexpected restart, may it be caused by the high value of bdb_max_lock? --- mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388600 read_buffer_size=131072 max_used_connections=12 max_connections=100 threads_connected=2 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x9964008 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xae3fa304, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x813198c 0x8a07c8 0x82770aa 0x82770aa 0x827651c 0x8276adb 0x824ee13 0x824f6d2 0x824fb82 0x81b6566 0x81a8bac 0x81855fa 0x814514a 0x8148dfa 0x8149638 0x814a263 0x814ab5a 0x89a341 0x71a6fe New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x996d548 = delete from parametrivaloriplc where id_centralina=343 thd-thread_id=1 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Memory status: Non-mmapped space allocated from system: 6262784 Number of free chunks: 18 Number of fastbin blocks:0 Number of mmapped regions: 11 Space in mmapped regions:86974464 Maximum total allocated space: 0 Space available in freed fastbin blocks: 0 Total allocated space: 5625120 Total free space:637664 Top-most, releasable space: 16 Estimated memory (with thread stack):93433856 Number of processes running now: 0 051209 01:00:31 mysqld restarted 051209 1:00:32 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 051209 1:00:33 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 52400171. InnoDB: Doing recovery: scanned up to log sequence number 0 52400171 051209 1:00:33 InnoDB: Flushing modified pages from the buffer pool... 051209 1:00:33 InnoDB: Started; log sequence number 0 52400171 051209 1:00:33 [Warning] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them /usr/libexec/mysqld: ready for connections. Version: '4.1.12' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution -- More details about my environment follows: # cat /proc/version Linux version
Re: String Manipulation
On 09 Dec 2005 10:45:04 +0800, Jeffrey G. Ubalde
I have been thinking about this for hours, and I cant quite get the
correct keyword for me to search it in the manual.
for example i have a field that contains string1:string2:string3
where colon ':' is the delimiter.
How do i create a select statement that only returns string2?
Something like this may be what you're after?
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
mysql select
substring_index(substring_index('string1:string2:string3',':',2),':',-1);
+--+
| substring_index(substring_index('string1:string2:string3',':',2),':',-1) |
+--+
| string2 |
+--+
1 row in set (0.00 sec)
mysql
Will.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT all except ... ?
On 09/12/05, Frank Rust [EMAIL PROTECTED] wrote: Is there a possibility to select all columns from a table except one or two columns? For example I have a table with 30 columns and want all columns but one column *not*. Do I have to write a very long select statement with 29 column names that i want to get? If you're using MySQL 5.0 an up you could achieve similar functionality using views. http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-views.html Will. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: recursive queries
Hello. Though I haven't read this article, but its title looks promicing: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html Duncan Miller wrote: I am looking to do a query on a self join table that returns the parent records. Obviously there are ways to do this, but just wondered if there are any functions in MySQL that make this easier, or specific functions available to stored procedures. I have read the manual and couldn't find anything, but thought I would ask. I notice that the new MS SQL Server has support for recursive queries and oracle also, so I thought maybe... Thanks -- 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: Rpm installation problem
Hello. It is a bug: http://bugs.mysql.com/bug.php?id=15223 Like temporary workaroud I use on my FC4 system MySQL-shared-compat-4.1.13-0 rpm. Logg, Connie A. wrote: I am trying to install the following rpm on a red hat linux machine.=20 MySQL-client-standard-5.0.16-0.rhel3.i386.rpm MySQL-devel-standard-5.0.16-0.rhel3.i386.rpm MySQL-server-standard-5.0.16-0.rhel3.i386.rpm MySQL-shared-standard-5.0.16-0.rhel3.i386.rpm MySQL-standard-debuginfo-5.0.16-0.rhel3.i386.rpm All work fine except one: [EMAIL PROTECTED] cal]# rpm -i = MySQL-shared-standard-5.0.16-0.rhel3.i386.rpm warning: MySQL-shared-standard-5.0.16-0.rhel3.i386.rpm: V3 DSA = signature: NOKEY, key ID 5072e1f5 error: Failed dependencies: libz.so.0 is needed by MySQL-shared-standard-5.0.16-0.rhel3 Is this libz something that should have been installed with the redhat = installation, and didn't? Also, where is the perl MYSQL bundle that matched this release of Mysql? Thanks, Connie Connie Logg, Network Analyst Stanford Linear Accelerator Center ph: 650-926-2879=20 Happiness is found along the way, not at the end of the road, and 'IF' = is the middle word in life. -- 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: SELECT all except ... ?
Frank Rust [EMAIL PROTECTED] wrote on 12/09/2005 01:59:35 AM: Is there a possibility to select all columns from a table except one or two columns? For example I have a table with 30 columns and want all columns but one column *not*. Do I have to write a very long select statement with 29 column names that i want to get? -- Frank Rust, Technische Universität, Institut für Theoretische Informatik Tel.: +49 531 391 9525 Postfach 3329, D-38023 Braunschweig Fax.: +49 531 391 9529 Mühlenpfordtstr. 22-23, D-38106 Braunschweig Nope, it's not part of the SQL standard to write a query that way. In fact, I cannot think of a single database (commercial or free) with that capability in their query syntax (and I have used a lot of them). Sorry! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Fw: SELECT all except ... ?
Oops, I meant to send this to the original poster _and_ the list :-) Rhino - Original Message - From: Rhino [EMAIL PROTECTED] To: Frank Rust [EMAIL PROTECTED] Sent: Friday, December 09, 2005 9:02 AM Subject: Re: SELECT all except ... ? - Original Message - From: Frank Rust [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, December 09, 2005 1:59 AM Subject: SELECT all except ... ? Is there a possibility to select all columns from a table except one or two columns? For example I have a table with 30 columns and want all columns but one column *not*. Do I have to write a very long select statement with 29 column names that i want to get? I've heard requests for Select all except for years now but have never seen anyone implement it. Mind you, I've only ever used two SQL databases seriously, DB2 and MySQL, so I can't be sure that Oracle or one of the others doesn't have this ability. The only thing that I've seen which comes close to what you describe is that DB2 has a set of dialogs that can be used to generate SQL. These dialogs let you select your table name(s) from a list, then select your column name(s) from a list, etc. When you select column names, there is a button for selecting all columns in the table(s), which is the equivalent of Select * if you were coding your own SQL. Once you've clicked that button, you can select one, several or all of the columns that were chosen for the query and de-select them again. Therefore, if you clicked Select all, then de-selected one or two of the columns, it would have the same effect as you want. Aside from that, I'm not sure why the syntax you want couldn't be added to the SQL language so maybe you should ask for it via a feature request. I could easily imagine a change to SQL that would allow something like this: select * except e.salary, d.deptname from employee e inner join department d on e.workdept = d.deptno Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date: 09/12/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Normalization question
Hi Everyone, I've got a little debate that maybe you can all help me settle. I have three tables that I need to join, but there are performance problems with the joins due to some misunderstandings of my predecessor about what's needed to join the tables efficiently. Here's the setup. t1 has a unique key defined on it, call it command_id, t1 has a 1 to many relationship with t2. t2 has t1's command_id as a foreign key constraint, plus a set of one or more instrument_id's. Each command results in one or more instruments taking data. The commanding elements defined here are then set to our instrument to aquire the data. When the data comes back it is organized into t3 by command_id and instrument_id. So the primary key on t3 looks like this: command_id_instrument_id. So, now I need to write a query that looks at what was commanded in t1 and t2 and then look for any missing data that has not yet been received. So, I've got a query that looks something like this: SELECT stuff FROM t1 JOIN t2 ON t1.command_id = t2.command_id LEFT JOIN t3 ON t3.data_id = concat(t1.command_id,'_',t2.instrument_id) Now, I think everyone is going to see immediately that the join on t3 is going to have absolutely horrible performance, the join condition here can't take advantage of any indexes since the string function pretty much destroys any hope of that. To make matters worse, the left join is a total killer. So my suggestion to solve the performance bottleneck is to add two columns to t3, command_id and instrument_id, and create a combined index on the command_id and instrument_id columns. the join condition on t3 then becomes: LEFT JOIN t3 ON t3.command_id = t1.command_id AND t3.instrument_id = t2.instrument_id This works beautifully! The performance of this new join condition is about 480 times faster than the original. Here's the rub. Since there is a unique data_id that already exists which combines the information in both command_id and instrument_id keys, I'm being told by our seasoned software developer that I am violating classic database design rules against redundant data. In my opinion, this is a minor violation at best. We have a good reason for wanting to identify each data segment with our originally defined data_id, but this is not strictly a requirement on the database, it's more of an agreed upon convention that we are going to use to identify the data segments and distribute them to our consumers. From a database stand point, the only requirement is that the data_id be unique. It could be anything as far as the database is concerned, as long as the data_id remains unique, it doesn't matter that it may be overloaded with some duplicate information. Any more experienced DBA's than I have an opinion on this? Thanks!
Re: Normalization question
Rod Heyd [EMAIL PROTECTED] wrote on 12/09/2005 11:01:38 AM:
Hi Everyone,
I've got a little debate that maybe you can all help me settle.
I have three tables that I need to join, but there are performance
problems
with the joins due to some misunderstandings of my predecessor about
what's
needed to join the tables efficiently.
Here's the setup.
t1 has a unique key defined on it, call it command_id, t1 has a 1 to
many
relationship with t2.
t2 has t1's command_id as a foreign key constraint, plus a set of one or
more instrument_id's.
Each command results in one or more instruments taking data.
The commanding elements defined here are then set to our instrument to
aquire the data.
When the data comes back it is organized into t3 by command_id and
instrument_id.
So the primary key on t3 looks like this: command_id_instrument_id.
So, now I need to write a query that looks at what was commanded in t1
and
t2 and then look for
any missing data that has not yet been received.
So, I've got a query that looks something like this:
SELECT stuff
FROM
t1
JOIN
t2
ON
t1.command_id = t2.command_id
LEFT JOIN
t3
ON
t3.data_id = concat(t1.command_id,'_',t2.instrument_id)
Now, I think everyone is going to see immediately that the join on t3 is
going to have absolutely horrible performance,
the join condition here can't take advantage of any indexes since the
string
function pretty much destroys any hope of that. To make matters worse,
the
left join is a total killer.
So my suggestion to solve the performance bottleneck is to add two
columns
to t3,
command_id and instrument_id, and create a combined index on the
command_id
and instrument_id
columns.
the join condition on t3 then becomes:
LEFT JOIN
t3
ON
t3.command_id = t1.command_id
AND
t3.instrument_id = t2.instrument_id
This works beautifully! The performance of this new join condition is
about
480 times faster than the original.
Here's the rub. Since there is a unique data_id that already exists
which
combines the information in both
command_id and instrument_id keys, I'm being told by our seasoned
software
developer that I am violating classic database design rules against
redundant data.
In my opinion, this is a minor violation at best. We have a good
reason
for wanting to identify each data segment with our originally defined
data_id, but this is not strictly a requirement on the database, it's
more
of an agreed upon convention that we are going to use to identify the
data
segments and distribute them to our consumers. From a database stand
point,
the only requirement is that the data_id be unique. It could be
anything as
far as the database is concerned, as long as the data_id remains unique,
it
doesn't matter that it may be overloaded with some duplicate
information.
Any more experienced DBA's than I have an opinion on this?
Thanks!
You are not duplicating data by referring to objects by their id values.
The false-normalization through the use of the composite key on t3 was a
mistake. By obscuring the actual relationships between t1, t2, and t3,
your original designer broke one of the cardinal rules of designing a
relational database. Each foreign key should point to at least (and
usually at most) one row on a single table. His foreign key pointed to any
row on either table. That was a bad design decision that created serious
performance bottlenecks. If you have the chance to fix his design of t3
to keep the separate columns for each FK, please do it. If you want to
keep the composite key for historical reasons, I don't see why that can't
happen but you should stop using it as the primary key for the table.
The drawback to composite keys is that they could possibly refer to more
than one thing. Assume for a moment that you are creating a composite key
out of two text-type fields. Also imagine that you are compositing those
fields with a _ character (as in your example). This pair ('testme_a',
'secondhalf') and this pair ('testme', 'a_secondhalf') would both evaluate
to 'testme_a_secondhalf'. We lose our 1:1 correspondence between key and
parent.
Obviously, my example was contrived and using just numbers can avoid this
kind of behavior but it illustrated why it is just not correct to
construct foreign keys. Foreign keys are SUPPOSED to contain values that
actually match something in the other table. Using them does not violate
the no duplicates rule your friend describes and can be used to enforce
relational integrity.
Just so you have it on record (IMHO): You are correct and the other DBA
should read up about modern relational databases and modern theories of
normalization.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: Normalization question
Rod Heyd wrote: Hi Everyone, I've got a little debate that maybe you can all help me settle. I have three tables that I need to join, but there are performance problems with the joins due to some misunderstandings of my predecessor about what's needed to join the tables efficiently. Here's the setup. t1 has a unique key defined on it, call it command_id, t1 has a 1 to many relationship with t2. t2 has t1's command_id as a foreign key constraint, plus a set of one or more instrument_id's. Each command results in one or more instruments taking data. The commanding elements defined here are then set to our instrument to aquire the data. When the data comes back it is organized into t3 by command_id and instrument_id. So the primary key on t3 looks like this: command_id_instrument_id. Yuck! So, now I need to write a query that looks at what was commanded in t1 and t2 and then look for any missing data that has not yet been received. So, I've got a query that looks something like this: SELECT stuff FROM t1 JOIN t2 ON t1.command_id = t2.command_id LEFT JOIN t3 ON t3.data_id = concat(t1.command_id,'_',t2.instrument_id) Now, I think everyone is going to see immediately that the join on t3 is going to have absolutely horrible performance, the join condition here can't take advantage of any indexes since the string function pretty much destroys any hope of that. To make matters worse, the left join is a total killer. I think the LEFT JOIN isn't that big a deal, if the proper index is there and usable. You can easily find out by comparing the speed of your query below against the speed of the same query without LEFT. So my suggestion to solve the performance bottleneck is to add two columns to t3, command_id and instrument_id, and create a combined index on the command_id and instrument_id columns. Yes, exactly. The combined index should be UNIQUE. the join condition on t3 then becomes: LEFT JOIN t3 ON t3.command_id = t1.command_id AND t3.instrument_id = t2.instrument_id This works beautifully! The performance of this new join condition is about 480 times faster than the original. Yes, this is the way this should be done. Here's the rub. Since there is a unique data_id that already exists which combines the information in both command_id and instrument_id keys, I'm being told by our seasoned software developer that I am violating classic database design rules against redundant data. It is true that you now have redundant data. Clearly, you do not need both the two new columns, command_id and instrument_id, and the old column, command_id_instrument_id. Redundant data is a violation of classic database design rules, so one or the other has to go if you want to follow the rules. Which should go? Well, the old column, command_id_instrument_id, combines the answer to two questions, Which command? and Which instrument?, into one column. That is also also a violation of the classic database design rules, and a really bad idea. It leads to precisely the sort of problem you are trying to fix. It also makes it difficult to find the rows in t3 which belong to a particular command, or to a particular instrument. Those queries require string matching, and the latter could not use an index. If you want to follow the rules, drop the old column. In my opinion, this is a minor violation at best. We have a good reason for wanting to identify each data segment with our originally defined data_id, but this is not strictly a requirement on the database, it's more of an agreed upon convention that we are going to use to identify the data segments and distribute them to our consumers. From a database stand point, the only requirement is that the data_id be unique. It could be anything as far as the database is concerned, as long as the data_id remains unique, it doesn't matter that it may be overloaded with some duplicate information. Any more experienced DBA's than I have an opinion on this? The strictly correct solution is to replace the old, broken column with your two new columns. You can either make the combined index on the two new columns the PRIMARY KEY, or you can make an AUTO_INCREMENT primary key and define the combination of the two columns as UNIQUE. It is then trivial to SELECT CONCAT(command_id, '_', instrument_id) ... when you want to display the t3 id using the agreed upon convention. In short, your seasoned software developer is right to want to follow the rules, but the rules dictate replacing the old column with the two new columns. Thanks! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT all except ... ?
It's not possible in the query, but I wonder if there's a UDF you could write that takes in the name of a table, and then a list of columns NOT to show, and outputs a string of comma-separated values. That'd be neat, so then you could write: SELECT allBut(mytable,badfield1,badfield2,. . .) FROM mytable; Would that even work? Is the query done first, and then the UDF performed? (my apologies for throwing out an idea that may or may not work). -Sheeri On 12/9/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Frank Rust [EMAIL PROTECTED] wrote on 12/09/2005 01:59:35 AM: Is there a possibility to select all columns from a table except one or two columns? For example I have a table with 30 columns and want all columns but one column *not*. Do I have to write a very long select statement with 29 column names that i want to get? -- Frank Rust, Technische Universität, Institut für Theoretische Informatik Tel.: +49 531 391 9525 Postfach 3329, D-38023 Braunschweig Fax.: +49 531 391 9529 Mühlenpfordtstr. 22-23, D-38106 Braunschweig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL newb trying to write a trigger...
you could also have a look at http://dev.mysql.com/tech-resources/articles/mysql-triggers.pdf which I found useful. -jp
cursor in 4.1
All, I am using the perl DBI to access a large table. I need to a simple manipulation of the table then write it to a file. If I use the simple select * from table then process this statement on the client side, it looks that the client first receive all the content of the table. This causes memory out on the client machine. Looking into the manual of 4.1, I don't seem to see the create cursor method. So what is the best way to accomplish this job using 4.1. If anyone had some experience on this would you let me know. Kemin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
copying data!!!
Hello Friend, Can you please let me know how to copy data from a column in table A to a column in table B. e.g. I want to copy value of column age from table 2 to column age in table 1, provided the entries in column name, which is there in both the table, should match. In MySQL v 4 it could be done using Update tbl1,tbl2 syntax, but i have now MySQL 3.23 which does not supports this Update syntax... Also, can i use ldap to authenticate the MySQL users, if yes, please tell me how?? Tnax. Sachin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: cursor in 4.1
At 11:17 -0800 12/9/05, Kemin Zhou wrote: All, I am using the perl DBI to access a large table. I need to a simple manipulation of the table then write it to a file. If I use the simple select * from table then process this statement on the client side, it looks that the client first receive all the content of the table. This causes memory out on the client machine. Looking into the manual of 4.1, I don't seem to see the create cursor method. So what is the best way to accomplish this job using 4.1. If anyone had some experience on this would you let me know. Set the mysql_use_result statement attibute. For details, try perldoc DBD::mysql -- 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: copying data!!!
Hello, We expect to offer native support for LDAP and PAM authentication, late next year. Thanks, Jimmy Guerrero, Senior Product Manager MySQL Inc, www.mysql.com -Original Message- From: Sachin Bhugra [mailto:[EMAIL PROTECTED] Sent: Friday, December 09, 2005 1:38 PM To: mysql@lists.mysql.com Subject: copying data!!! Hello Friend, Can you please let me know how to copy data from a column in table A to a column in table B. e.g. I want to copy value of column age from table 2 to column age in table 1, provided the entries in column name, which is there in both the table, should match. In MySQL v 4 it could be done using Update tbl1,tbl2 syntax, but i have now MySQL 3.23 which does not supports this Update syntax... Also, can i use ldap to authenticate the MySQL users, if yes, please tell me how?? Tnax. Sachin -- 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]
CAST as SIGNE=?ISO-8859-1?Q?D,_bad_id=E9a??=
in an application i have written there is the need to do a search from mysql using numbers that are stored in a varchar column. it is not possible to store only the results with numbers in a seperate column. so i was looking at CAST(), is this a big performance loss? is there some way of benchmarking different queries easy? - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CAST as SIGNE=?ISO-8859-1?Q?D,_bad_id=E9a??=
Assuming that your text data is in the column `userinput` and you want the integer values to be in the column `numericvalue`, this statement will populate the `numericvalue` column all at once: UPDATE `odd_data_table` SET `numericvalue` = `userinput` + 0; You are better off checking for type-correctness before you enter data into the database than you are trying to correct it after the input. However, I have had to do just this kind of conversion on many occasions (old data, bad batch inputs, text file bulk loads, etc.) so I know techniques like this still have their place. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Test USER [EMAIL PROTECTED] wrote on 12/09/2005 03:30:17 PM: in an application i have written there is the need to do a search from mysql using numbers that are stored in a varchar column. it is not possible to store only the results with numbers in a seperate column. so i was looking at CAST(), is this a big performance loss? is theresome way of benchmarking different queries easy? - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CAST as S=?ISO-8859-1?Q?IGNED,_bad_id=E9a??=
Hello, thanks for your help! I dont really get it :) You suggestion is to have a seperate column with the name numericvalue and insert userinput into that and add a zero, right? Could you explain more, why when how will this help me :) Quoting [EMAIL PROTECTED]: Assuming that your text data is in the column `userinput` and you want the integer values to be in the column `numericvalue`, this statement will populate the `numericvalue` column all at once: UPDATE `odd_data_table` SET `numericvalue` = `userinput` + 0; You are better off checking for type-correctness before you enter data into the database than you are trying to correct it after the input. However, I have had to do just this kind of conversion on many occasions (old data, bad batch inputs, text file bulk loads, etc.) so I know techniques like this still have their place. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Test USER [EMAIL PROTECTED] wrote on 12/09/2005 03:30:17 PM: in an application i have written there is the need to do a search from mysql using numbers that are stored in a varchar column. it is not possible to store only the results with numbers in a seperate column. so i was looking at CAST(), is this a big performance loss? is theresome way of benchmarking different queries easy? - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: copying data!!!
Tnx for the reply Jimmy. I also sent another question( i know its a very silly question for you all..but believe me i am tryin this for past three days and not able to get it) Pls hlp..(just give me hint in right direction, and i will try to do the rest) Tnx Sachin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CAST as S=?ISO-8859-1?Q?IGNED,_bad_id=E9a??=
I misunderstood, I thought you were looking for a way of converting your numbers-as-strings into a native numeric format. Please describe you situation better: What language are you using to build your application. Are you composing the SQL statement client-side or server-side? What kind of SQL statement are you trying to execute? Your table structures (the output of SHOW CREATE TABLE ... works very well) and some sample data would also help. Sorry for the confusion! Shawn Green Database Administrator Unimin Corporation - Spruce Pine Test USER [EMAIL PROTECTED] wrote on 12/09/2005 04:28:44 PM: Hello, thanks for your help! I dont really get it :) You suggestion is to have a seperate column with the name numericvalue and insert userinput into that and add a zero, right? Could you explain more, why when how will this help me :) Quoting [EMAIL PROTECTED]: Assuming that your text data is in the column `userinput` and you want the integer values to be in the column `numericvalue`, this statement will populate the `numericvalue` column all at once: UPDATE `odd_data_table` SET `numericvalue` = `userinput` + 0; You are better off checking for type-correctness before you enter data into the database than you are trying to correct it after the input. However, I have had to do just this kind of conversion on many occasions (old data, bad batch inputs, text file bulk loads, etc.) so I know techniques like this still have their place. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Test USER [EMAIL PROTECTED] wrote on 12/09/2005 03:30:17 PM: in an application i have written there is the need to do a search from mysql using numbers that are stored in a varchar column. it is not possible to store only the results with numbers in a seperate column. so i was looking at CAST(), is this a big performance loss? is theresome way of benchmarking different queries easy?
Re: recursive queries
I believe that some time in the future mysql will support oracle style connect by prior syntax but it's not implemented yet. Gleb Paharenko wrote: Hello. Though I haven't read this article, but its title looks promicing: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html Duncan Miller wrote: I am looking to do a query on a self join table that returns the parent records. Obviously there are ways to do this, but just wondered if there are any functions in MySQL that make this easier, or specific functions available to stored procedures. I have read the manual and couldn't find anything, but thought I would ask. I notice that the new MS SQL Server has support for recursive queries and oracle also, so I thought maybe... Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: copying data!!!
Check this out. http://dev.mysql.com/doc/refman/5.0/en/insert-select.html On 12/9/05, Sachin Bhugra [EMAIL PROTECTED] wrote: Hello Friend, Can you please let me know how to copy data from a column in table A to a column in table B. e.g. I want to copy value of column age from table 2 to column age in table 1, provided the entries in column name, which is there in both the table, should match. In MySQL v 4 it could be done using Update tbl1,tbl2 syntax, but i have now MySQL 3.23 which does not supports this Update syntax... Also, can i use ldap to authenticate the MySQL users, if yes, please tell me how?? Tnax. Sachin -- 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: CAST as S=?ISO-8859-1?Q?IGNED,_bad_id=E9a??=
Hi again :) The table contains a column named value and is in the format varchar(255). This column contains specifications for different computer products. There is also a id column so i know which product it belongs to. value(varchar) 80 17 1024x768 USB DiVX For example, the first value 80 tells me with som joins that the product maxtor diamondmax has 80Gb capacity. And that a Philips DVD-player supports DiVX for the last value in this example. Now i want to select all harddrvies with a capacity greater or equal to 80. Doing a select value from tbl where value =80 order by value DESC will give som unexpected results. If you have 80, 120, 250 in the database the result will be: 80 250 120 I don't really know how to solve this other than to use CAST(value as SIGNED). Maybe i could rebuild the database but i don't know how a good databasedesign for this would look like :) Thanks for you help! Quoting [EMAIL PROTECTED]: I misunderstood, I thought you were looking for a way of converting your numbers-as-strings into a native numeric format. Please describe you situation better: What language are you using to build your application. Are you composing the SQL statement client-side or server-side? What kind of SQL statement are you trying to execute? Your table structures (the output of SHOW CREATE TABLE ... works very well) and some sample data would also help. Sorry for the confusion! Shawn Green Database Administrator Unimin Corporation - Spruce Pine Test USER [EMAIL PROTECTED] wrote on 12/09/2005 04:28:44 PM: Hello, thanks for your help! I dont really get it :) You suggestion is to have a seperate column with the name numericvalue and insert userinput into that and add a zero, right? Could you explain more, why when how will this help me :) Quoting [EMAIL PROTECTED]: Assuming that your text data is in the column `userinput` and you want the integer values to be in the column `numericvalue`, this statement will populate the `numericvalue` column all at once: UPDATE `odd_data_table` SET `numericvalue` = `userinput` + 0; You are better off checking for type-correctness before you enter data into the database than you are trying to correct it after the input. However, I have had to do just this kind of conversion on many occasions (old data, bad batch inputs, text file bulk loads, etc.) so I know techniques like this still have their place. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Test USER [EMAIL PROTECTED] wrote on 12/09/2005 03:30:17 PM: in an application i have written there is the need to do a search from mysql using numbers that are stored in a varchar column. it is not possible to store only the results with numbers in a seperate column. so i was looking at CAST(), is this a big performance loss? is theresome way of benchmarking different queries easy? - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimize: 14 hours and still running!
Is the box swapping? Gleb Paharenko wrote: Hello. As was mentioned by other members without seeing your configuration settings it is difficult to say about InnoDB performance. You can indirectly monitor the OPTIMIZE speed by ROW OPERATIONS section of SHOW INNODB STATUS. For InnoDB it maps to ALTER TABLE, which rebuilds the table. Nathan Gross wrote: On a 1.6ghz, 1gb ram, Linux machine running Mysql 4.1x. I have an Innodb table with over 20 million records and index size about 3.7 gig, data size 2.2gig (yes, many indexes, more space then the data itself). Last night I tried an Optimize from the Admin gui console (logged in as root at the host server), and the thing is still running! Problem is I need to leave early this evening and have to take some action. The Linux 'top' utility has it on the top since then at about 11%-18% cpu Disk activity is continuously heavy. 1. How long should it take? 2. If I hit cancel will it: a) Roll back what it did, another 14 hours! b) Just stop as if nothing happened. c) The table will be partially optimized and will run normally. d) hang the process and/or machine. 3. Is the data in jeopardy? Thank you all. -nat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ANN: Database Workbench 2.8.0 update
Ladies, gentlemen, This week, Database Workbench 2.8.0 was released. However, that build included an error with MySQL and loading index metadata and an specific error with Firebird/InterBase. There's a new installer available at our website. Below is the original announcement... --announcement-- Upscene Productions is proud to announce the next version of the popular database development tool: Database Workbench 2.8.0 has been released today! Download a trial at: http://www.upscene.com What's new?: http://www.upscene.com/products/dbw/whatsnew.htm Full list of features and fixes: http://www.upscene.com/news/20051206a.htm Database Workbench supports: - Borland InterBase ( v4.x - v7.x ) - Firebird ( v1.x ) - MS SQL Server/MSDE ( v6.5, 7, 2000, 2005, MSDE 1 2, SQL Express ) - MySQL 4, 4.1, 5.0 - Oracle Database ( 8i, 9i, 10g ) If you experience any problems with this new version, don't hestitate and either go to the website and send a support email or email directly to [EMAIL PROTECTED] New - Microsoft SQL 2005 support - MySQL 5 support - Two-way Visual Query Builder - Increased Oracle support - New SQL Insight - Create INSERT script from ODBC datasource Enhancements - Code/SQL Editor enhancements - More complte Schema Compare/Migration - Automatic image-type recognition in BLOB Editor - many user interface improvements Thank you for your support, Martijn Tonies Database Workbench - the database developer tool for professionals 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: copying data!!!
This is kind of ugly, but with the multitable limitations of 3.23 it probably works. CREATE TABLE TEMP SELECT table_1 columns except age, table_2 age FROM table_1 INNER JOIN table_2 USING (name); TRUNCATE table_1; INSERT INTO table_1 SELECT * FROM TEMP; DROP TABLE_1; -Original Message- From: Sachin Bhugra [mailto:[EMAIL PROTECTED] Sent: Friday, December 09, 2005 3:00 PM To: mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: Re: copying data!!! Tnx for the reply Jimmy. I also sent another question( i know its a very silly question for you all..but believe me i am tryin this for past three days and not able to get it) Pls hlp..(just give me hint in right direction, and i will try to do the rest) Tnx Sachin -- 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: copying data!!!
Sorry the DROP TABLE Should be TEMP not table1 -Original Message- From: Gordon Bruce [mailto:[EMAIL PROTECTED] Sent: Friday, December 09, 2005 3:51 PM To: Sachin Bhugra; mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: RE: copying data!!! This is kind of ugly, but with the multitable limitations of 3.23 it probably works. CREATE TABLE TEMP SELECT table_1 columns except age, table_2 age FROM table_1 INNER JOIN table_2 USING (name); TRUNCATE table_1; INSERT INTO table_1 SELECT * FROM TEMP; DROP TABLE_1; -Original Message- From: Sachin Bhugra [mailto:[EMAIL PROTECTED] Sent: Friday, December 09, 2005 3:00 PM To: mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: Re: copying data!!! Tnx for the reply Jimmy. I also sent another question( i know its a very silly question for you all..but believe me i am tryin this for past three days and not able to get it) Pls hlp..(just give me hint in right direction, and i will try to do the rest) Tnx Sachin -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CAST as S=?ISO-8859-1?Q?IGNED,_bad_id=E9a??=
This sounds like a simple case of bad design. You need to be able to locate specific values for various product descriptions but they are all mangled together into just one field. You end up trying to do substring matches and all hell breaks loose and performance hits the skids. My suggestion is to somehow re-process your value column into separate specific columns or child tables, one for each distinct value held in the value field. I can identify the potential values of `hdd_size`, `monitor_size`, `monitor_resolution`,`hw_port`,`cd_supp_format` Your data is unmanageable in its present format and you need to scrub and massage it into shape before what you have will be marginally useful. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Test USER [EMAIL PROTECTED] wrote on 12/09/2005 04:56:21 PM: Hi again :) The table contains a column named value and is in the format varchar(255). This column contains specifications for different computer products. There is also a id column so i know which product it belongs to. value(varchar) 80 17 1024x768 USB DiVX For example, the first value 80 tells me with som joins that the product maxtor diamondmax has 80Gb capacity. And that a Philips DVD-player supportsDiVX for the last value in this example. Now i want to select all harddrvies with a capacity greater or equal to 80. Doing a select value from tbl where value =80 order by value DESCwill give som unexpected results. If you have 80, 120, 250 in the database the result will be: 80 250 120 I don't really know how to solve this other than to use CAST(value as SIGNED). Maybe i could rebuild the database but i don't know how a good databasedesign for this would look like :) Thanks for you help! Quoting [EMAIL PROTECTED]: I misunderstood, I thought you were looking for a way of converting your numbers-as-strings into a native numeric format. Please describe you situation better: What language are you using to build your application. Are you composing the SQL statement client-side or server-side? What kind of SQL statement are you trying to execute? Your table structures (the output of SHOW CREATE TABLE ... works very well) and some sample data would also help. Sorry for the confusion! Shawn Green Database Administrator Unimin Corporation - Spruce Pine Test USER [EMAIL PROTECTED] wrote on 12/09/2005 04:28:44 PM: Hello, thanks for your help! I dont really get it :) You suggestion is to have a seperate column with the name numericvalue and insert userinput into that and add a zero, right? Could you explain more, why when how will this help me :) Quoting [EMAIL PROTECTED]: Assuming that your text data is in the column `userinput` and you want the integer values to be in the column `numericvalue`, this statement will populate the `numericvalue` column all at once: UPDATE `odd_data_table` SET `numericvalue` = `userinput` + 0; You are better off checking for type-correctness before you enter data into the database than you are trying to correct it after the input. However, I have had to do just this kind of conversion on many occasions (old data, bad batch inputs, text file bulk loads, etc.) so I know techniques like this still have their place. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Test USER [EMAIL PROTECTED] wrote on 12/09/2005 03:30:17 PM: in an application i have written there is the need to do a search from mysql using numbers that are stored in a varchar column. it is not possible to store only the results with numbers in a seperate column. so i was looking at CAST(), is this a big performance loss? is theresome way of benchmarking different queries easy? - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu
Re: CAST as S=?ISO-8859-1?Q?IGNED,_bad_id=E9a??=
Test USER wrote: Hi again :) The table contains a column named value and is in the format varchar(255). This column contains specifications for different computer products. There is also a id column so i know which product it belongs to. value(varchar) 80 17 1024x768 USB DiVX For example, the first value 80 tells me with som joins that the product maxtor diamondmax has 80Gb capacity. And that a Philips DVD-player supports DiVX for the last value in this example. Now i want to select all harddrvies with a capacity greater or equal to 80. Doing a select value from tbl where value =80 order by value DESC will give some unexpected results. If you have 80, 120, 250 in the database the result will be: 80 250 120 I don't really know how to solve this other than to use CAST(value as SIGNED). Maybe i could rebuild the database but i don't know how a good databasedesign for this would look like :) Is the ordering your only concern? Your value column is a string, so your results are ordered alphabetically rather than numerically. If all you want is numeric ordering, you need to tell mysql to treat value as a number in the order by: SELECT value FROM tbl WHERE value =80 ORDER BY value+0 DESC; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CAST as S=?ISO-8859-1?Q?IGNED,_bad_id=E9a??=
Oh, is it really such a bad design? Here is some more. What is substring matches, and why do i need them? TBL_PRODUCTS ID PRODUCTNAME 1 SAMSUNG TV 2 PHILIPS DVD-PLAYER 3 PHILIPS TV 4 MAXTOR DMAX 5 LaCie HARDDIVE - TBL_SPECS ID DETAIL 1 Widescreen 2 VCD 3 DiVX 4 Capacity 5 Inch - PRODUCT_SPECS PRODID SPECID VALUE 1 1 YES 1 5 32 2 2 NO 2 3 3.11 3 1 NO 3 1 28 4 4 80 5 4 120 - Thanks again for your help! Quoting [EMAIL PROTECTED]: This sounds like a simple case of bad design. You need to be able to locate specific values for various product descriptions but they are all mangled together into just one field. You end up trying to do substring matches and all hell breaks loose and performance hits the skids. My suggestion is to somehow re-process your value column into separate specific columns or child tables, one for each distinct value held in the value field. I can identify the potential values of `hdd_size`, `monitor_size`, `monitor_resolution`,`hw_port`,`cd_supp_format` Your data is unmanageable in its present format and you need to scrub and massage it into shape before what you have will be marginally useful. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Test USER [EMAIL PROTECTED] wrote on 12/09/2005 04:56:21 PM: Hi again :) The table contains a column named value and is in the format varchar(255). This column contains specifications for different computer products. There is also a id column so i know which product it belongs to. value(varchar) 80 17 1024x768 USB DiVX For example, the first value 80 tells me with som joins that the product maxtor diamondmax has 80Gb capacity. And that a Philips DVD-player supportsDiVX for the last value in this example. Now i want to select all harddrvies with a capacity greater or equal to 80. Doing a select value from tbl where value =80 order by value DESCwill give som unexpected results. If you have 80, 120, 250 in the database the result will be: 80 250 120 I don't really know how to solve this other than to use CAST(value as SIGNED). Maybe i could rebuild the database but i don't know how a good databasedesign for this would look like :) Thanks for you help! Quoting [EMAIL PROTECTED]: I misunderstood, I thought you were looking for a way of converting your numbers-as-strings into a native numeric format. Please describe you situation better: What language are you using to build your application. Are you composing the SQL statement client-side or server-side? What kind of SQL statement are you trying to execute? Your table structures (the output of SHOW CREATE TABLE ... works very well) and some sample data would also help. Sorry for the confusion! Shawn Green Database Administrator Unimin Corporation - Spruce Pine Test USER [EMAIL PROTECTED] wrote on 12/09/2005 04:28:44 PM: Hello, thanks for your help! I dont really get it :) You suggestion is to have a seperate column with the name numericvalue and insert userinput into that and add a zero, right? Could you explain more, why when how will this help me :) Quoting [EMAIL PROTECTED]: Assuming that your text data is in the column `userinput` and you want the integer values to be in the column `numericvalue`, this statement will populate the `numericvalue` column all at once: UPDATE `odd_data_table` SET `numericvalue` = `userinput` + 0; You are better off checking for type-correctness before you enter data into the database than you are trying to correct it after the input. However, I have had to do just this kind of conversion on many occasions (old data, bad batch inputs, text file bulk loads, etc.) so I know techniques like this still have their place. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Test USER [EMAIL PROTECTED] wrote on 12/09/2005 03:30:17 PM: in an application i have written there is the need to do a search from mysql using numbers that are stored in a varchar column. it is not possible to store only the results with numbers in a seperate column. so i was looking at CAST(), is this a big performance loss? is theresome way of
Re: CAST as S=?ISO-8859-1?Q?IGNED,_bad_id=E9a??=
Yes the ordering is the only problem i have seen so far but i´m concerned that the = ? might cause problems to? It seems to work but since it orders the results wrong can i be sure that it will always do this correct for me? In the ordering it seems like mysql thinks that 80Gb is larger than 120 and 250. And my concerne is that i might be situations where it thinks that 80 should be returned when doing a =120. Quoting Michael Stassen [EMAIL PROTECTED]: Test USER wrote: Hi again :) The table contains a column named value and is in the format varchar(255). This column contains specifications for different computer products. There is also a id column so i know which product it belongs to. value(varchar) 80 17 1024x768 USB DiVX For example, the first value 80 tells me with som joins that the product maxtor diamondmax has 80Gb capacity. And that a Philips DVD-player supports DiVX for the last value in this example. Now i want to select all harddrvies with a capacity greater or equal to 80. Doing a select value from tbl where value =80 order by value DESC will give some unexpected results. If you have 80, 120, 250 in the database the result will be: 80 250 120 I don't really know how to solve this other than to use CAST(value as SIGNED). Maybe i could rebuild the database but i don't know how a good databasedesign for this would look like :) Is the ordering your only concern? Your value column is a string, so your results are ordered alphabetically rather than numerically. If all you want is numeric ordering, you need to tell mysql to treat value as a number in the order by: SELECT value FROM tbl WHERE value =80 ORDER BY value+0 DESC; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Min permissions to run `mysqladmin version`
I need to run `mysqladmin version` from a remote host, on the same LAN, what are the min permissions for a user I need to set up to get this data? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED] Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Min permissions to run `mysqladmin version`
At 16:33 -0800 12/9/05, Scott Haneda wrote: I need to run `mysqladmin version` from a remote host, on the same LAN, what are the min permissions for a user I need to set up to get this data? No permissions are necessary, you just need an account that has no privileges. The account needs only to be able to connect to the server. For example, either of these statements will create the account: CREATE USER 'vuser'@'remote-host' IDENTIFIED BY 'vpass'; GRANT USAGE ON *.* TO 'vuser'@'remote-host' IDENTIFIED BY 'vpass'; -- 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]
IN(INT VS CHAR)
When using IN should i design the database to use int's or is the performance
equal?
WHERE col IN('test','test2','test3')
vs
WHERE col IN(1,2,3)
-
FREE E-MAIL IN 1 MINUTE!
- [EMAIL PROTECTED] - http://www.pc.nu
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CAST as S=?ISO-8859-1?Q?IGNED,_bad_id=E9a??=
MUCH BETTER!! Sorry I doubted you. However you have to remember that unless you declare a second numeric column in your PRODUCT_SPECS table then everything will be treated as strings. Sorting will be as strings, comparisons will be as strings, and any attempt to use them as numbers while they are strings will invalidate any indexes. I would suggest a second DECIMAL column on your PRODUCT_SPECS table or be prepared for performance hits whenever you need numeric ordering. If you compare them alphabetically, 8 comes after 1 so 8 is greater than 10, 100, 1000, 20, 30, or any other word that starts with a letter smaller than 8. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Test USER [EMAIL PROTECTED] wrote on 12/09/2005 05:47:41 PM: Oh, is it really such a bad design? Here is some more. What is substring matches, and why do i need them? TBL_PRODUCTS ID PRODUCTNAME 1 SAMSUNG TV 2 PHILIPS DVD-PLAYER 3 PHILIPS TV 4 MAXTOR DMAX 5 LaCie HARDDIVE - TBL_SPECS ID DETAIL 1 Widescreen 2 VCD 3 DiVX 4 Capacity 5 Inch - PRODUCT_SPECS PRODID SPECID VALUE 1 1 YES 1 5 32 2 2 NO 2 3 3.11 3 1 NO 3 1 28 4 4 80 5 4 120 - Thanks again for your help! Quoting [EMAIL PROTECTED]: This sounds like a simple case of bad design. You need to be able to locate specific values for various product descriptions but they are all mangled together into just one field. You end up trying to do substring matches and all hell breaks loose and performance hits the skids. My suggestion is to somehow re-process your value column into separate specific columns or child tables, one for each distinct value held in the value field. I can identify the potential values of `hdd_size`, `monitor_size`, `monitor_resolution`,`hw_port`,`cd_supp_format` Your data is unmanageable in its present format and you need to scrub and massage it into shape before what you have will be marginally useful. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Test USER [EMAIL PROTECTED] wrote on 12/09/2005 04:56:21 PM: Hi again :) The table contains a column named value and is in the format varchar(255). This column contains specifications for different computer products. There is also a id column so i know which product it belongs to. value(varchar) 80 17 1024x768 USB DiVX For example, the first value 80 tells me with som joins that the product maxtor diamondmax has 80Gb capacity. And that a Philips DVD-player supportsDiVX for the last value in this example. Now i want to select all harddrvies with a capacity greater or equal to 80. Doing a select value from tbl where value =80 order by value DESCwill give som unexpected results. If you have 80, 120, 250 in the database the result will be: 80 250 120 I don't really know how to solve this other than to use CAST(value as SIGNED). Maybe i could rebuild the database but i don't know how a good databasedesign for this would look like :) Thanks for you help! Quoting [EMAIL PROTECTED]: I misunderstood, I thought you were looking for a way of converting your numbers-as-strings into a native numeric format. Please describe you situation better: What language are you using to build your application. Are you composing the SQL statement client-side or server-side? What kind of SQL statement are you trying to execute? Your table structures (the output of SHOW CREATE TABLE ... works very well) and some sample data would also help. Sorry for the confusion! Shawn Green Database Administrator Unimin Corporation - Spruce Pine Test USER [EMAIL PROTECTED] wrote on 12/09/2005 04:28:44 PM: Hello, thanks for your help! I dont really get it :) You suggestion is to have a seperate column with the name numericvalue and insert userinput into that and add a zero, right? Could you explain more, why when how will this help me :) Quoting [EMAIL PROTECTED]: Assuming that your text data is in the column `userinput` and you want the integer values to be in the column `numericvalue`, this statement will populate the `numericvalue` column all at once: UPDATE `odd_data_table` SET `numericvalue` = `userinput` + 0; You are better off checking for type-correctness before you enter data into the database than you are trying to correct it after the input.
