Re: MySQL Memory Issue
Brian, unfortuntately, to use AWE/PAE in the InnoDB buffer pool you have to compile MySQL yourself. See /mysql/innobase/os/os0proc.c. The reason is that the MySQL official binary is built using an older version of MS Visual C++. Then you could also lower the default 1 MB that Windows allocates as the stack of each thread. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, January 12, 2005 5:20 AM Subject: MySQL Memory Issue --_=_NextPart_001_01C4F854.7050C7A0 Content-Type: text/plain Windows 2000 Advance Server (latest service pack) Physical Address Extension enabled Dual 2.4Ghz Intel Pentium 4 Xeon 6gb of memory This system is running as a dedicated MySQL 4.0.12-max-nt server. Issue: The problem first exhibited itself as a connection issue. We weren't able to obtain more than 250 active connections. After troubleshooting we found that lowering the innodb_buffer_pool_size variable increased the number of available active connections. The innodb_buffer_pool_size was originally set at 1500M. Further investigation has determined that MySQL isn't able to use more than 2gb of memory on this system. It is our understanding that with PAE(Physical Address Extension) enables on the system that MySQL should be able utilize more than 2gb of memory. To prove that we were having problems with the 2gb memory limit we increased the innodb_buffer_pool_size to 2500M. At that time the MySQL service wouldn't start. We currently have the innodb_buffer_pool_size set to 1000M which is giving us the possibility of having about 650 active connections in MySQL. Does anyone have any suggestions as to what would be causing the memory limitation in MySQL? -Brian --_=_NextPart_001_01C4F854.7050C7A0-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Rows_sent vs Rows_examined
Just wondering... I have this simple join: SELECT b.id_commentaire AS id_commentaire, id_skynaute, id_article, created_on, read, expediteur, email, text FROM comment_base AS b LEFT JOIN comment_content AS c USING (id_commentaire) WHERE id_skynaute = 4671022 AND id_article = 58366086; id_commentaire is the primary key of comment_base and comment_content tables. id_skynaute and id_articles are non-unique keys of comment_base. The slow query log shows: # Query_time: 9 Lock_time: 0 Rows_sent: 1601 Rows_examined: 3187 EXPLAIN shows that only indexes are used: id_article from comment_base and id_commentaire (primary key) from comment_content. My obvious question is: why 3187 rows were examined since everything is indexed and there's only one matching entry of the comment_content table for every comment_base entry? Best regards, -- Frank - my stupid blog: http://00f.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error: 1040 too many connections
Hi, MySQL 4.0.14 This may have been queried a lot here. We get this error and after re-starting the server (MySQL) it seems to go away for a while. As per instructions we have changed the max connection in the My.ini to 500 (max_connections=500). MySQLAdmin displays connections = 120. Is there anything else we can do to deal with this issue? regards ___ ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can't get the order I want after inserting new rows
Thanks for your help guys!
By looking at your solutions I understand how it's meant to work. The
problem is that I'm using the older MySQL 3.23.58. And I'm bound to
stick with that version since it's standard in most Redhat
distributions, and the program I'm writing will be used on several of
those platforms. Thus I get syntax error messages when I run some of
your suggested queries, for example
mysql UPDATE blast, ranks SET blast.sbj_count = ranks.rank WHERE
blast.sbj_name=ranks.sbj_name;
ERROR 1064: You have an error in your SQL syntax near ' ranks SET
blast.sbj_count = ranks.rank WHERE blast.sbj_name=ranks.sbj_name' at
line 1
and
mysql CREATE TEMPORARY TABLE tmpGenSbjCount (sbj_count int
auto_increment, sbj_name varchar(255) primary_key, score int not null);
ERROR 1064: You have an error in your SQL syntax near 'primary_key,
score int not null)' at line 1
They way I eventually made it work was
CREATE TABLE temp_table
SELECT sbj_name,max(score)
AS order_score FROM table
GROUP BY sbj_name
(indexing both sbj_name columns to speed up the query below)
CREATE INDEX sbj_name_index
ON temp_table(sbj_name(30))
CREATE INDEX sbj_name_index
ON $table(sbj_name(30))
SELECT table.sbj_name, table.sbj_count, table.hsp_count,
table.score,temp_table.order_score
FROM table,temp_table
WHERE table.sbj_name = temp_table.sbj_name
ORDER BY temp_table.order_score DESC, table.sbj_count ASC
I then looped (perl DBI) over the result of the latter query updating
sbj_count values:
my $new_sbj_count = 0;
my $old_sbj_name = ;
foreach my $row (@{$update_set}) {
$new_sbj_count++ if ($row-{sbj_name} ne $old_sbj_name);
$dbh-do(UPDATE $table SET sbj_count=$new_sbj_count WHERE
sbj_name='$row-{sbj_name}' AND hsp_count='$row-{hsp_count}');
$old_sbj_name = $row-{sbj_name};
}
I know I'm not using especially the sbj_count column in an optimal way,
but since the rest of my program is doing other stuff with this table, I
try to stick to only one table and keep the database structure (if you
can call it that ;-)) as simple as possible.
Many thanks for all your help and effort!!
Marcus
On Tue, 2005-01-11 at 17:08, [EMAIL PROTECTED] wrote:
Marcus Claesson [EMAIL PROTECTED] wrote on 01/11/2005 11:05:49
AM:
Thanks for helping!
So you ARE having problems trying to change your sbj_count and
hsp_count columns after adding information to your table your so that
data is re-ranked according to some rule(s) you have. Exactly, how do
you determine the new ranking? I could make a guess based on your
data but I would probably be wrong.
Walk me through this process, please. I give you these data points:
(sbjname, score): (a,500), (a,900), (b,800), (c,500), (c,600),
(c,700), (d,400), (d,1000), (e,500), (e,900), (f,700), (g,600)
Could you please explain to me how to generate(create) the sbj_count
and hsp_count values for each row? As I said, I can probably guess but
I want the official method from you. I can help you write the SQL to
do it but I need to know what you need to do.
Both the old and new ranking are based on score values. The highest
score get the lowest sbj_count, but sbj_names must be ordered next to
each other (a a b b c c etc) with ascending hsp_count values. Thus,
(sbj_count,sbjname, score,hsp_count)
(1,d,1000,1)
(1,d,400,2)
(2,a,900,1)
(2,a,500,2)
(3,e,900,1)
(3,e,500,2)
(4,b,800,1)
(5,c,700,1)
(5,c,600,2)
(5,c,500,3)
(6,f,700,1)
(7,g,600,1)
Hope that helps...
Regards,
Marcus
Perfect! just what I needed. First step. Re-rank all sbjnames according to
their scores (substitute the actual column size for sbj_name here and in
following steps).
CREATE TEMPORARY TABLE tmpGenSbjCount (
sbj_count int auto_increment
, sbj_name varchar(xxx) primary_key
, score int not null
);
INSERT tmpGenSbjCount (sbj_name, score)
SELECT sbj_name, max(score) as topscore
FROM datatable
GROUP BY sbj_name
ORDER BY topscore desc;
Now, tmpGenSbjCount has ranked all of your sbj_names according to their
score. The next step is to rank all scores by name to generate their
hsp_count.
CREATE tmpGenHspCount (
sbj_count int not null
, sbj_name varchar(xxx)
, score int not null
, hsp_count int auto_increment
, old_sbj_count int not null
, old_hsp_count int not null
, PRIMARY KEY (sbj_count, sbj_name, hsp_count)
);
INSERT tmpGenHspCount(sbj_count, sbj_name, score, old_sbj_count,
old_hsp_count)
SELECT tgsc.sbj_count, tgsc.sbj_name, dt.score, dt.sbj_count, dt.hsp_count
FROM tmpGenSbjCount tgsc
INNER JOIN datatable dt
on tgsc.sbj_name = dt.sbj_name
ORDER by dt.score DESC;
tmpGenHspCount should now contain the correct (new) sbj_count and
hsp_counts for each (sbj_name, score) pair.(please check!!) We can use the
data in that table to update your original data (datatable). I added the
columns for the
Specifying location of .my.cnf to load_defaults
Since a search on the MySQL website doesn't yield any returns for load_defaults() - in fact it splits it into Load and defaults and doesn't search for it directly - and none of the MySQL documentation covers this function, does anybody know how to specify and alternate location for the .my.cnf file (defaults to $HOME/.my.cnf). Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. **
Re: Specifying location of .my.cnf to load_defaults
From: Kevin Cowley Since a search on the MySQL website doesn't yield any returns for load_defaults() - in fact it splits it into Load and defaults and doesn't search for it directly - and none of the MySQL documentation covers this function, does anybody know how to specify and alternate location for the .my.cnf file (defaults to $HOME/.my.cnf). I think you'll find the info at http://dev.mysql.com/doc/mysql/en/Option_files.html Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem in LOAD DATA INFILE
Hi, I am facing problem while inserting more than 20,000 records using LOAD DATA INFILE statement through comma seperated file. Here is my statement: LOAD DATA LOCAL INFILE '/home/mysql/isa_logs/a.txt' INTO TABLE FirewallLog_1 FIELDS TERMINATED BY ','; I have persistantly observed that when in try to run this statement with a file having 20,000 records. The problems starts when records in the files are more then 20,262 ERROR 1030: Got error 28 from table handler This is the error I get. MySQL Version: 4.0.13 Host: Linux - Red Hat 7,0 Is this related to my sql global variables or hardware resources ? Thanks in advance. Regards, Alpesh Kothari Oracle DBA Net4Nuts Limited [EMAIL PROTECTED]
RE: Specifying location of .my.cnf to load_defaults
Thanks for the response but I already knew about that page. The problem is
with a client application it doesn't know anything about how the server is
configured and it doesn't matter.
Basically it's a case of doing
main(int argc, char **argv)
{
my_init();
load_defaults();
handle_options();
}
So you need to be able to tell load_defaults where to locate the .my.cnf
file so it can go and load the defaults.
Kevin Cowley
RD
Tel: 0118 902 9099 (direct line)
Email: [EMAIL PROTECTED]
Web: http://www.alchemetrics.co.uk
-Original Message-
From: Jigal van Hemert [mailto:[EMAIL PROTECTED]
Sent: 12 January 2005 12:26
To: Kevin Cowley; mysql@lists.mysql.com
Subject: Re: Specifying location of .my.cnf to load_defaults
From: Kevin Cowley
Since a search on the MySQL website doesn't yield any returns for
load_defaults() - in fact it splits it into Load and defaults and
doesn't
search for it directly - and none of the MySQL documentation covers this
function, does anybody know how to specify and alternate location for
the
.my.cnf file (defaults to $HOME/.my.cnf).
I think you'll find the info at
http://dev.mysql.com/doc/mysql/en/Option_files.html
Regards, Jigal.
**
ALCHEMETRICS LIMITED (ALCHEMETRICS)
Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX
Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001
This e-mail is confidential and is intended for the use of the addressee only.
If you are not the intended recipient, you are hereby notified that you must
not use, copy, disclose, otherwise disseminate or take any action based on
this e-mail or any information herein.
If you receive this transmission in error, please notify the sender
immediately by reply e-mail or by using the contact details above and then
delete this e-mail.
Please note that e-mail may be susceptible to data corruption, interception
and unauthorised amendment. Alchemetrics does not accept any liability for
any such corruption, interception, amendment or the consequences thereof.
**
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Resource messagedlg_confirmation not found
I am trying to install Mysql 4.1 on a Toshiba laptop running XP, when I get to the stage of specifying install as Windows service and include Bin directory in Windows PATH the installation halts. Can anyone advise how to overcome this problem / what is causing it, please? Thanks for any help. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.6.9 - Release Date: 06/01/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Where are warning messages redirected or logged to ?
Warning messages, where are they to be found? Part of the construct from a cron job script file run at the command line interface $ /usr/local/bin/mysqlimport -v -l --fields-terminated-by=| --local fisheries -L -u administr -p $CRONBASE/trawler.tmp Connecting to localhost Selecting database fisheries Locking tables for write Loading data from LOCAL file: /usr/local/trawler.tmp into trawler fisheries.trawler: Records: 1 Deleted: 0 Skipped: 1 Warnings: 5 Disconnecting from localhost $ But where are the Warnings -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Repeated table corruption
All, We run mysql 3.23.58 on a Tru64 OSF 4.0F. There are 3 tables that are consitently corrupt; these tables are fixed using myisamchk and after a couple of hours they are corrupt again. The following are errors in mysql error log. /usr/local/mysql/bin/myisamchk: ISAM file /usr/local/mysql/data/duat_eng/REGISTRY_TABLE.MYI /usr/local/mysql/bin/myisamchk: error: Size of datafile is: 960 Should be: 10560 /usr/local/mysql/bin/myisamchk: error: record delete-link-chain corrupted /usr/local/mysql/bin/myisamchk: error: Found key at page 1024 that points to record outside datafile Thank you in advance for any help you may provide. -Teresa This is a PRIVATE message. If you ar not the intended recipient, please delete without copying and kindly advise us by e-mail of the mistake in delivery. NOTE: Regardless of content, this e-mail shall not operate to bind CSC to any order or other contract unless pursuant to explicit written agreement or government initiative expressly permitting the use of e-mail for such purpose. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem in LOAD DATA INFILE
Alpesh Kothari wrote: Hi, I am facing problem while inserting more than 20,000 records using LOAD DATA INFILE statement through comma seperated file. Here is my statement: LOAD DATA LOCAL INFILE '/home/mysql/isa_logs/a.txt' INTO TABLE FirewallLog_1 FIELDS TERMINATED BY ','; I have persistantly observed that when in try to run this statement with a file having 20,000 records. The problems starts when records in the files are more then 20,262 ERROR 1030: Got error 28 from table handler This is the error I get. MySQL Version: 4.0.13 Host: Linux - Red Hat 7,0 Is this related to my sql global variables or hardware resources ? Thanks in advance. Regards, Alpesh Kothari Oracle DBA Net4Nuts Limited [EMAIL PROTECTED] bash$ perror 28 Error code 28: No space left on device Free up some disk space, get a bigger disk, or move your data or temp directory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
join 1 of 2 table depending on a column value
HI, I've 2 fields in a select query rif_CF, ID_CF... the rif_CF value (C or F) indicate the meaning of the ID_CF... IF rif_CF=C then the ID_CF is a customer ID, IF rif_CF=F the ID_CF is a furnisher ID. How can I join 2 tables (customer, and furnisher) in a query? SELECT ID_doc, data_doc, rif_CF, ID_CF.!?!?!? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: join 1 of 2 table depending on a column value
[snip] How can I join 2 tables (customer, and furnisher) in a query? [/snip] RTFM http://dev.mysql.com/doc/mysql/en/JOIN.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: join 1 of 2 table depending on a column value
I have done this numerous times. What you have to do is match 2 conditions in your ON clause. I need to make up some information to answer your question because I don't know all of the table names involved in this particular query (you didn't say in your original post). I know you have two tables, customer and furnisher, but you seem to have some other table that contains the column rif_CF so I will call it rif. SELECT ...(some columns) FROM rif LEFT JOIN customer c ON c.customer_id = rif.ID_CF AND rif.rif_CF='C' LEFT JOIN furnisher f ON f.furnisher_ID = rif.ID_CF AND rif.rif_CF = 'F' WHERE NOT (c.customer_ID is null AND f.furnisher_id is null) AND ... I used qualified names on all of the fields I used. I know your fields are probably uniquely named but I prefer this convention for consistency when joining tables. I used a LEFT JOIN so that you could get results from either customer or furnisher even if both tables didn't match a row in rif. The first condition in the WHERE clause makes sure you had at least one match of either table (which may not be an issue with your data but I can't know that without more info from you). Let me know if this answers your question. If not, come back to the list and explain what you really want and I am sure that someone here can help. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Wakan [EMAIL PROTECTED] wrote on 01/12/2005 09:27:40 AM: HI, I've 2 fields in a select query rif_CF, ID_CF... the rif_CF value (C or F) indicate the meaning of the ID_CF... IF rif_CF=C then the ID_CF is a customer ID, IF rif_CF=F the ID_CF is a furnisher ID. How can I join 2 tables (customer, and furnisher) in a query? SELECT ID_doc, data_doc, rif_CF, ID_CF.!?!?!? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Locked myself out of the mysql database!
Like an idiot I locked myself out of the mysql database when I went to change the password for the root user. Is there any way I can get back into that database and restore my mistake? Thanks, jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Locked myself out of the mysql database!
[snip] Like an idiot I locked myself out of the mysql database when I went to change the password for the root user. Is there any way I can get back into that database and restore my mistake? [/snip] Stop the mysql serivce, then start it from a command line with the skip-grant-tables option. You can then log in as root with no password and then reset the password. Then stop mysql and restart it normally. In linux: mysqld --skip-grant-tables In windows: mysqld-nt --skip-grant-tables --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: join 1 of 2 table depending on a column value
OK, the 2° condition on the ON caluse works now I'll use an IF caluse to select 1 column only, like this: SELECT IF(rif_CF='C',A.descr,F.descr) thanks Ciao, Carlo At 15.43 12/01/2005, you wrote: I have done this numerous times. What you have to do is match 2 conditions in your ON clause. I need to make up some information to answer your question because I don't know all of the table names involved in this particular query (you didn't say in your original post). I know you have two tables, customer and furnisher, but you seem to have some other table that contains the column rif_CF so I will call it rif. SELECT ...(some columns) FROM rif LEFT JOIN customer c ON c.customer_id = rif.ID_CF AND rif.rif_CF='C' LEFT JOIN furnisher f ON f.furnisher_ID = rif.ID_CF AND rif.rif_CF = 'F' WHERE NOT (c.customer_ID is null AND f.furnisher_id is null) AND ... I used qualified names on all of the fields I used. I know your fields are probably uniquely named but I prefer this convention for consistency when joining tables. I used a LEFT JOIN so that you could get results from either customer or furnisher even if both tables didn't match a row in rif. The first condition in the WHERE clause makes sure you had at least one match of either table (which may not be an issue with your data but I can't know that without more info from you). Let me know if this answers your question. If not, come back to the list and explain what you really want and I am sure that someone here can help. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Wakan [EMAIL PROTECTED] wrote on 01/12/2005 09:27:40 AM: HI, I've 2 fields in a select query rif_CF, ID_CF... the rif_CF value (C or F) indicate the meaning of the ID_CF... IF rif_CF=C then the ID_CF is a customer ID, IF rif_CF=F the ID_CF is a furnisher ID. How can I join 2 tables (customer, and furnisher) in a query? SELECT ID_doc, data_doc, rif_CF, ID_CF.!?!?!? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error: 1040 too many connections
I had the same error for quite some time, the issue seemed to be server resources not being able to kill off connections quickly enough. We had a lot of traffic and some pretty slow queries. Optimizing the queries bought us some time, but ultimately we had to upgrade hardware. This bought us about a year and we are against the wall again now with the 1040 issue. We are probably going to get another server and set up replication. I'd be interested to hear anyone elses stories around this issue, it's been quite a headache for us... Quoting A Z [EMAIL PROTECTED]: Hi, MySQL 4.0.14 This may have been queried a lot here. We get this error and after re-starting the server (MySQL) it seems to go away for a while. As per instructions we have changed the max connection in the My.ini to 500 (max_connections=500). MySQLAdmin displays connections = 120. Is there anything else we can do to deal with this issue? regards ___ ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com -- 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]
compilation errors in c++ wrapper
Hi everyone. I have been putting together a c++ wrapper for the mysql c libraries and have stumbled across the following error... g++ -L/usr/local/mysql/lib -I/usr/local/mysql/include -o connection connection.cpp -lmysqlclient -lnsl -lsocket -lz -lm Undefined first referenced symbol in file main /usr/local/lib/gcc-lib/sparc-sun-solaris2.8/3.3.2/crt1.o Result::Result[in-charge](st_mysql_res*)/var/tmp//cc13NSwj.o ld: fatal: Symbol referencing errors. No output written to connection collect2: ld returned 1 exit status The error mentions the Constructor Result::Result (for the class Result) which I don't have declared. Any ideas what it is telling me. It ultimately comes up with an ld referencing error pointing at /usr/local/lib/gcc-lib/sparc-sun-solaris2.8/3.3.2/crt1.o Any other ideas Thanks Andy -- perl -e print qq^bIG VeRN ! ^^qq^#'#Yv#=D+ ^ This e-mail is private and may be confidential and is for the intended recipient only. If misdirected, please notify us by telephone and confirm that it has been deleted from your system and any copies destroyed. If you are not the intended recipient you are strictly prohibited from using, printing, copying, distributing or disseminating this e-mail or any information contained in it. We use reasonable endeavours to virus scan all e-mails leaving the Company but no warranty is given that this e-mail and any attachments are virus free. You should undertake your own virus checking. The right to monitor e-mail communications through our network is reserved by us. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Specifying location of .my.cnf to load_defaults
Kevin Cowley [mailto:[EMAIL PROTECTED] wrote:
Thanks for the response but I already knew about that page.
The problem is with a client application it doesn't know anything about how
the server is configured and it doesn't matter.
Basically it's a case of doing
main(int argc, char **argv)
{
my_init();
load_defaults();
handle_options();
}
So you need to be able to tell load_defaults where to locate the .my.cnf
file so it can go and load the defaults.
Are you on a *ix system?
You might try something like this, assuming that you know the name of
the script that starts the mysql daemon at boot time:
#!/bin/sh
mysqld=/etc/init.d/mysqld
realmysqlserver=`awk '$2 == start {print $1;exit}' $mysqld`
mydotconf=`grep '^[ ]*conf=' $realmysqlserver | sed 's/^.*=//'`
mysqldatadir=`grep '^[ ]*datadir=' $realmysqlserver | sed 's/^.*=//'`
As you might guess, I use a technique like this to locate the MySQL
data directory in scripts I run overnight to check the databases
and manage backups.
Steve Bacher
System Administrator
Draper Laboratory
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB files corrupt after copy to another disk???
Hello, I am trying to copy my innodb table spaces to a new array. When I copy the files to their new location and start mysql-max with the new configuration, invariably I get the following after I attempt to actually connect to a database with the command line client: mysqld-max process hanging, pid 20607 - killed 050112 12:58:38 mysqld restarted /usr/bin/mysqld_safe: line 1: 23476 Killed nohup /usr/sbin/mysqld-max --defaults-file=/idb/d1/mysql/logdata/my.cnf --basedir=/ --datadir=/idb/d1/mysql/logdata/data/ --user=mysql --pid-file=/idb/d1/mysql/logdata/mysql.logdata.pid --skip-locking --port=3301 --socket=/tmp/mysql.logdata.sock.idb /idb/d1/mysql/logdata/log/mysql.logdata.err.log 21 050112 12:58:38 mysqld ended InnoDB: Error: page n:o stored in the page read in is 1333252, should be 1589252! InnoDB: Error: page n:o stored in the page read in is 1333249, should be 1589249! InnoDB: Error: trying to access page number 1872904192 in space 0 InnoDB: which is outside the tablespace bounds. InnoDB: Byte offset 0, len 16384, i/o type 10 050112 12:58:38 InnoDB: Assertion failure in thread 1132853616 in file fil0fil.c line 1204 InnoDB: Failing assertion: 0 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to mysql@lists.mysql.com 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=2093056 max_used_connections=0 max_connections=1000 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 4100184 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Number of processes running now: 1 mysqld-max process hanging, pid 20607 - killed 050112 12:58:38 mysqld restarted I have tried the copy many times over. Yes I shut the database down before the copy. I have tried using cp, and pax to do the copies. I have run an md5sum agains't some of the innodb files comparing source and destination after copy, and they are identical in all tests. I cannot possibly imagine what I am missing. I copy InnoDB table spaces to other machines using similar methods without this type of corruption. I doubt it's the array becuase the md5sum's are correct. Has anyone any clue why moving the innodb spaces to another partition/disk could possibly cause this type of problem? I double checked my innodb table space directives, the paths are all correct. Thanks! -- Richard F. Rebel cat /dev/null `tty` signature.asc Description: This is a digitally signed message part
Re: Trouble w/ mysqldump (images attached)
Hello. [mysqldump] default_character_set = latin1 [EMAIL PROTECTED] wrote: amazing.. I failed to see that in the last msg - but that seemed to do the trick - The images appear correctly now.. is there anyway in my.cnf to tell it to always use the command?? Thanks much for the help! In a message dated 1/11/2005 4:48:27 AM Eastern Standard Time, Gleb Paharenko [EMAIL PROTECTED] writes: -- 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: Locked myself out of the mysql database!
Hello. See: http://dev.mysql.com/doc/mysql/en/Resetting_permissions.html Jay Paulson [EMAIL PROTECTED] wrote: Like an idiot I locked myself out of the mysql database when I went to change the password for the root user. Is there any way I can get back into that database and restore my mistake? Thanks, jay -- 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: Resource messagedlg_confirmation not found
Hello. Installations often aborts at this stage when you already have the MySQL service. What exact version of MySQL do you use? The recommended is 4.1.8. Have you seen any error message? Simon Tierney [EMAIL PROTECTED] wrote: I am trying to install Mysql 4.1 on a Toshiba laptop running XP, when I get to the stage of specifying install as Windows service and include Bin directory in Windows PATH the installation halts. Can anyone advise how to overcome this problem / what is causing it, please? Thanks for any help. -- 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: Where are warning messages redirected or logged to ?
Hello. Try increasing the verbosity level, for example, with -vv or -vvv. Joseph E. Maxwell [EMAIL PROTECTED] wrote: Warning messages, where are they to be found? Part of the construct from a cron job script file run at the command line interface $ /usr/local/bin/mysqlimport -v -l --fields-terminated-by=| --local fisheries -L -u administr -p $CRONBASE/trawler.tmp Connecting to localhost Selecting database fisheries Locking tables for write Loading data from LOCAL file: /usr/local/trawler.tmp into trawler fisheries.trawler: Records: 1 Deleted: 0 Skipped: 1 Warnings: 5 Disconnecting from localhost $ But where are the Warnings -- 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: mysql error
Hello. Such errors often occurs when you already have installed MySQL service. freshm4n [EMAIL PROTECTED] wrote: I got a mysql-4.1.8-win.zip,when installed,i got a error:Cannot create Windows service for MySQL.Error:0~~How to solve?thanks~~ :) sorry for my poor english~~ -- 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: Error: 1040 too many connections
Hello. There reason can be in big queries wich consume the resources (temp space for example). Turn on log_slow_queries to find the guilty queries. See: http://dev.mysql.com/doc/mysql/en/Slow_query_log.html You have old enough version of MySQL. Use the latest release. A Z [EMAIL PROTECTED] wrote: Hi, MySQL 4.0.14 This may have been queried a lot here. We get this error and after re-starting the server (MySQL) it seems to go away for a while. As per instructions we have changed the max connection in the My.ini to 500 (max_connections=500). MySQLAdmin displays connections = 120. Is there anything else we can do to deal with this issue? regards ___ ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com -- 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: Trouble w/ mysqldump (images attached)
Thanks for all your help Gleb! I Appreciate all your hard work! In a message dated 1/12/2005 5:31:19 AM Eastern Standard Time, Gleb Paharenko [EMAIL PROTECTED] writes: Hello. [mysqldump] default_character_set = latin1 [EMAIL PROTECTED] wrote: amazing.. I failed to see that in the last msg - but that seemed to do the trick - The images appear correctly now.. is there anyway in my.cnf to tell it to always use the command?? Thanks much for the help! In a message dated 1/11/2005 4:48:27 AM Eastern Standard Time, Gleb Paharenko [EMAIL PROTECTED] writes: -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Specifying location of .my.cnf to load_defaults
Steve
Yes I am on a *ix system but this has nothing to do with the server startup
or configuration.
This is how to tell a client application where it can locate a .my.cnf file
not the system my.cnf file for the database itself.
The purpose of the .my.cnf file is to allow the client to know which
database/server/port/user/password to use when connecting. By default it
looks for it in $HOME. The problem is if you've got 5 ordinary users but you
only want one .my.cnf file then you need to be able to tell the client app
where the file is - I can do this with a command line parameter.
The problem I have is how do I tell the MySQL client library, ie the
load_defaults function where it is as there's no parameter to do this.
Kevin Cowley
RD
Tel: 0118 902 9099 (direct line)
Email: [EMAIL PROTECTED]
Web: http://www.alchemetrics.co.uk
-Original Message-
From: Steve Bacher [mailto:[EMAIL PROTECTED]
Sent: 12 January 2005 17:55
To: mysql@lists.mysql.com
Subject: RE: Specifying location of .my.cnf to load_defaults
Kevin Cowley [mailto:[EMAIL PROTECTED] wrote:
Thanks for the response but I already knew about that page.
The problem is with a client application it doesn't know anything about
how
the server is configured and it doesn't matter.
Basically it's a case of doing
main(int argc, char **argv)
{
my_init();
load_defaults();
handle_options();
}
So you need to be able to tell load_defaults where to locate the .my.cnf
file so it can go and load the defaults.
Are you on a *ix system?
**
ALCHEMETRICS LIMITED (ALCHEMETRICS)
Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX
Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001
This e-mail is confidential and is intended for the use of the addressee only.
If you are not the intended recipient, you are hereby notified that you must
not use, copy, disclose, otherwise disseminate or take any action based on
this e-mail or any information herein.
If you receive this transmission in error, please notify the sender
immediately by reply e-mail or by using the contact details above and then
delete this e-mail.
Please note that e-mail may be susceptible to data corruption, interception
and unauthorised amendment. Alchemetrics does not accept any liability for
any such corruption, interception, amendment or the consequences thereof.
**
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange issue with fulltext searching (is self reserved)?
I have a problem with searching for a specific term. I have a database
of about 50k names, and am attempting to search the lastname field for
'self'. My query looks something like this:
SELECT * FROM users where MATCH (first_name, last_name) against ('self').
This query works fine if I use any other word except self. For
example, if I use smith or johnson I get the desired results, but
the name self does not return any results, even though I have verified
there are 6-7 people with the last name self in the database. Strangely,
changing their last name to self1 or sel or anything else and then
searching for the new term results in me locating the record. Upon
changing the name back and rerunning the query I am unable to find them.
Is self a reserved word? I looked and did not find it.
FWIW I am running MySQL 4.0.18 on Debian.
Jeremy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Getting info about db columns
Hi, mysql newb here, coming mostly from ms sql server. I'm wondering how to find out basic info (type, str length, column name if getting all cols from a list of tables, etc), about a set of columns from a number of different tables. I'm also interested in getting a list of the tables in a db. SQL Server has a pair of views (information_schema.tables and information_schema.columns) that were useful for this kind of thing. Since they're normal views, you can restrict the tables or cols found by table, column, type, etc, and retrieve only the info you want about them; anything that works in a std query works here. The only way I know of to get this kind of metadata from mysql is explain, which appears to only return a fixed set of info about all the columns in a single table. Is there any way to get: - A list of tables in a db - Column info for more than one table at once - Only certain info about these cols (name and type only, say) Thanks, Dave Merrill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting info about db columns
May I introduce you to the family of SHOW commands? SHOW DATABASES - lists all databases on a server SHOW TABLES - lists all tables within your current database SHOW CREATE TABLE x - returns a table containing a SQL statement you could use to recreate the table x if you needed to (including all indexes and constraints) SHOW COLUMNS FROM x - returns a table similar to INFORMATION_SCHEMA.columns but only for the table you specify. and the list goes on and on: http://dev.mysql.com/doc/mysql/en/SHOW.html Alas, the INFORMATION_SCHEMA views are not production ready. They are being added to MySQL with the 5.x+ branch of development, which is not yet stable. Be patient, many good things are coming with v5. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Dave Merrill [EMAIL PROTECTED] wrote on 01/12/2005 04:07:16 PM: Hi, mysql newb here, coming mostly from ms sql server. I'm wondering how to find out basic info (type, str length, column name if getting all cols from a list of tables, etc), about a set of columns from a number of different tables. I'm also interested in getting a list of the tables in a db. SQL Server has a pair of views (information_schema.tables and information_schema.columns) that were useful for this kind of thing. Since they're normal views, you can restrict the tables or cols found by table, column, type, etc, and retrieve only the info you want about them; anything that works in a std query works here. The only way I know of to get this kind of metadata from mysql is explain, which appears to only return a fixed set of info about all the columns in a single table. Is there any way to get: - A list of tables in a db - Column info for more than one table at once - Only certain info about these cols (name and type only, say) Thanks, Dave Merrill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange issue with fulltext searching (is self reserved)?
It isn't a reserved word, but self is a full-text stopword, so it isn't
indexed.
If you've built from source, the stopwords are in
path-to-source/myisam/ft_static.c
You can create your own stopword list, or turn off stopwords altogether, if
you want. See http://dev.mysql.com/doc/mysql/en/Fulltext_Fine-tuning.html
for the details.
Michael
Jeremy Durham wrote:
I have a problem with searching for a specific term. I have a database
of about 50k names, and am attempting to search the lastname field for
'self'. My query looks something like this:
SELECT * FROM users where MATCH (first_name, last_name) against ('self').
This query works fine if I use any other word except self. For
example, if I use smith or johnson I get the desired results, but
the name self does not return any results, even though I have verified
there are 6-7 people with the last name self in the database. Strangely,
changing their last name to self1 or sel or anything else and then
searching for the new term results in me locating the record. Upon
changing the name back and rerunning the query I am unable to find them.
Is self a reserved word? I looked and did not find it.
FWIW I am running MySQL 4.0.18 on Debian.
Jeremy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting info about db columns
In addition to Shawn's suggestions, the other approach you could take would be to write Java programs that make use of the DataBaseMetaData and ResultSetMetaData interfaces. I've used this approach with success, although not to any great extent yet. Of course, if you don't know Java or don't approve of having to write applications to get this sort of information, Shawn's approaches are going to be better for you ;-) Rhino - Original Message - From: Dave Merrill [EMAIL PROTECTED] To: MySql mysql@lists.mysql.com Sent: Wednesday, January 12, 2005 4:07 PM Subject: Getting info about db columns Hi, mysql newb here, coming mostly from ms sql server. I'm wondering how to find out basic info (type, str length, column name if getting all cols from a list of tables, etc), about a set of columns from a number of different tables. I'm also interested in getting a list of the tables in a db. SQL Server has a pair of views (information_schema.tables and information_schema.columns) that were useful for this kind of thing. Since they're normal views, you can restrict the tables or cols found by table, column, type, etc, and retrieve only the info you want about them; anything that works in a std query works here. The only way I know of to get this kind of metadata from mysql is explain, which appears to only return a fixed set of info about all the columns in a single table. Is there any way to get: - A list of tables in a db - Column info for more than one table at once - Only certain info about these cols (name and type only, say) Thanks, Dave Merrill -- 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: InnoDB files corrupt after copy to another disk???
Richard, - Original Message - From: Richard F. Rebel [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, January 12, 2005 8:07 PM Subject: InnoDB files corrupt after copy to another disk??? --=-55yKssoEPEmA1J8GXefY Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Hello, I am trying to copy my innodb table spaces to a new array. When I copy the files to their new location and start mysql-max with the new configuration, invariably I get the following after I attempt to actually connect to a database with the command line client: mysqld-max process hanging, pid 20607 - killed 050112 12:58:38 mysqld restarted /usr/bin/mysqld_safe: line 1: 23476 Killed nohup /usr/sbin/mysqld-max --defaults-file=3D/idb/d1/mysql/logdata/my.cnf --basedir=3D/ --datadir=3D/idb/d1/mysql/logdata/data/ --user=3Dmysql --pid-file=3D/idb/d1/mysql/logdata/mysql.logdata.pid --skip-locking --port=3D3301 --socket=3D/tmp/mysql.logdata.sock.idb /idb/d1/mysql/logdata/log/mysql.logdata.err.log 21 050112 12:58:38 mysqld ended InnoDB: Error: page n:o stored in the page read in is 1333252, should be 1589252! the page number is exactly 4000 MB too small. Maybe you have ibdata files in the wrong order, or have copied an ibdata file over another, or some ibdata files are mentioned more than once in the innodb_data_file_path? ... Richard F. Rebel Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
The mysql.server script , simple question
I've got a question the mysql.server script (came with 4.0.12 and 4.1.8 at with the Linux binary distribution) . My shell scripting knowledge is not even good enough to be called 'limited', but here goes: I'm trying to use mysql.server (Redhat 9) to start MySQL in a non-standard location. On line 47: datadir=/usr/local/mysql/data Should that line be datadir=$basedir/data ? I tried changing basedir to my new path, but it didn't quite work until I changed that line. I'm mostly asking because it *seems* right, but I'm essentially clueless and wanted to double check. Thanks, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Getting info about db columns
[Sending this to this list, just for general reference, since I didn't notice that Reply on this list goes to the poster, not the list. No other list I'm on works that way, so I plead Failure To Open Eyes.] Dave Merrill Dave Merrill wrote on 01/12/2005 04:56:34 PM: Thanks Shawn, looks like the stuff. SHOW TABLES is exactly one of my answers. SHOW COLUMNS from dbname like '%name%' is useful in that it can restrict by column name, but am I right that it can't restrict by type or keyed-ness, or span more than one table? That's correct, SHOW COLUMNS only works for one table at a time and the like option only restricts by name. Also, it looks like if you wanted to find out the pk columns in a set of tables, SHOW INDEX would need to be called separately from the column listing, and separately for each table. Actually, I prefer the results of SHOW CREATE TABLE as it shows you not only which columns are in the PRIMARY KEY but in which order they are listed. That command also is the only one that gives me good information about FOREIGN KEYs as well. Good news about INFORMATION_SCHEMA too, sounds like a good thing. Do you know if the goal is to be syntax compatible w SQL Server? Is there a std spec for this? I thought it was a microsoft idiosyncracy, though, for once, one I liked. Yes, the INFORMATION_SCHEMA views are part of every SQL standard (at least as far back as SQL92) so, if M$ wasn't too full of themselves, the results should be comparable. However, I haven't done a side-by-side comparison yet so I can't tell you for sure. Thanks again, Dave You're most welcome, Shawn Green Database Administrator Unimin Corporation - Spruce Pine PS. It's a good habit to hit REPLY TO ALL (or whatever your email client uses) so that the list is always copied (cc: -ed) on any responses. It performs two basic functions: everyone gets to share in the conversation so the knowledge gets spread around and you will generally get more responses. May I introduce you to the family of SHOW commands? SHOW DATABASES - lists all databases on a server SHOW TABLES - lists all tables within your current database SHOW CREATE TABLE x - returns a table containing a SQL statement you could use to recreate the table x if you needed to (including all indexes and constraints) SHOW COLUMNS FROM x - returns a table similar to INFORMATION_SCHEMA.columns but only for the table you specify. and the list goes on and on: http://dev.mysql.com/doc/mysql/en/SHOW.html Alas, the INFORMATION_SCHEMA views are not production ready. They are being added to MySQL with the 5.x+ branch of development, which is not yet stable. Be patient, many good things are coming with v5. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Hi, mysql newb here, coming mostly from ms sql server. I'm wondering how to find out basic info (type, str length, column name if getting all cols from a list of tables, etc), about a set of columns from a number of different tables. I'm also interested in getting a list of the tables in a db. SQL Server has a pair of views (information_schema.tables and information_schema.columns) that were useful for this kind of thing. Since they're normal views, you can restrict the tables or cols found by table, column, type, etc, and retrieve only the info you want about them; anything that works in a std query works here. The only way I know of to get this kind of metadata from mysql is explain, which appears to only return a fixed set of info about all the columns in a single table. Is there any way to get: - A list of tables in a db - Column info for more than one table at once - Only certain info about these cols (name and type only, say) Thanks, Dave Merrill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting info about db columns
SHOW TABLES lists tables in a db DESCRIBE tablename (= SHOW CREATE TABLE tablename) outputs column info, but your application will have to parse it. EXPLAIN is for query optimisation analysis, not table column listings. The manual's pretty clear on these topics. PB - Dave Merrill wrote: Hi, mysql newb here, coming mostly from ms sql server. I'm wondering how to find out basic info (type, str length, column name if getting all cols from a list of tables, etc), about a set of columns from a number of different tables. I'm also interested in getting a list of the tables in a db. SQL Server has a pair of views (information_schema.tables and information_schema.columns) that were useful for this kind of thing. Since they're normal views, you can restrict the tables or cols found by table, column, type, etc, and retrieve only the info you want about them; anything that works in a std query works here. The only way I know of to get this kind of metadata from mysql is explain, which appears to only return a fixed set of info about all the columns in a single table. Is there any way to get: - A list of tables in a db - Column info for more than one table at once - Only certain info about these cols (name and type only, say) Thanks, Dave Merrill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
primary key performance
I have a pretty standard database schema here with the primary key prod_id being my most often used join column in select queries. Categories, rankings .. just about anything having to do with products uses prod_id in a join, and user access on these queries is pretty heavy. I wonder whether the performance of a select join depends on the length of that column. Given that it is a primary key I have indexing on it, and all other rules apply, but is there a difference in performance (CPU) between: - 10 products in both cases. One time the column is a MediumInt, the other time a BigInt. I know there is a difference in disk space usage, but is there also one in performance at all ? - 10 products in both cases. One table has its prod_id span from exactly 1 to 10, the other one uses any random values between 1 and ... ahh lets say 884570294. Any performance issue ? Curious about what you guys have to say, anyway ... Dan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select count
| Field| Type
| Null | Key | Default | Extra |
+--+--+--+-+-++
| id | int(10)
| | PRI | NULL| auto_increment |
| status | enum('received','send','cancelled')
| YES | | NULL||
| notes| longblob
| YES | | NULL||
| date | datetime
| YES | | NULL||
+--+--+--+-+-++
I have table account (see below). I need to get count of received,
count of send and cound of cancelled records. I know that I can do
this in 3 queries.
#1. select count(*) from account where status='received';
#2. select count(*) from account where status='send';
#3. select count(*) from account where status='cancelled';
Is it possible to get count of each status in one query instead of 3?
TH
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select count
Jerry Swanson wrote:
| Field| Type
| Null | Key | Default | Extra |
+--+--+--+-+-++
| id | int(10)
| | PRI | NULL| auto_increment |
| status | enum('received','send','cancelled')
| YES | | NULL||
| notes| longblob
| YES | | NULL||
| date | datetime
| YES | | NULL||
+--+--+--+-+-++
I have table account (see below). I need to get count of received,
count of send and cound of cancelled records. I know that I can do
this in 3 queries.
#1. select count(*) from account where status='received';
#2. select count(*) from account where status='send';
#3. select count(*) from account where status='cancelled';
Is it possible to get count of each status in one query instead of 3?
- you could use a cross-tab query:
SELECT
date,
SUM(IF(CONCAT(status,id) LIKE 'received%',1,0)) as received,
SUM(IF(CONCAT(status,id) LIKE 'send%',1,0)) as send,
SUM(IF(CONCAT(status,id) LIKE 'cancelled%',1,0)) as cancelled
FROM
account
GROUP BY date;
- ian
--
+---+
| Ian Sales Database Administrator |
| |
| eBuyer http://www.ebuyer.com |
+---+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select count
Jerry,
You could also have 3 rows returned by running the following:
select count(*)
from account
where status in ('received','send','canceled')
group by status
;
This will return 3 rows, similar to the following
+-+---+
| status | count(*) |
+-+
| received | 8 |
| send |10 |
| canceled |12 |
+-+
I probably wouldn't use 'like' in this case, since the field is an
enum. If you are interested in having the database only compare the
first character of the string, maybe have 'status' be an enum of
'r','s', 'c'. Furthermore, to make it even faster, try setting the
status field to be an unsigned int, so you can do a status = 1, 2, 3 or
whatever.
Thanks,
Ryan Yagatich
,__,
/ Ryan Yagatich Pantek Incorporated |
\ http://www.pantek.com/linux [EMAIL PROTECTED] |
/ One Hour Emergency Response (877) 546-8934 |
\___|
Ian Sales wrote:
Jerry Swanson wrote:
| Field| Type
| Null | Key | Default | Extra |
+--+--+--+-+-++
| id | int(10)
| | PRI | NULL| auto_increment |
| status | enum('received','send','cancelled')
| YES | | NULL||
| notes| longblob
| YES | | NULL||
| date | datetime
| YES | | NULL||
+--+--+--+-+-++
I have table account (see below). I need to get count of received,
count of send and cound of cancelled records. I know that I can do
this in 3 queries.
#1. select count(*) from account where status='received';
#2. select count(*) from account where status='send';
#3. select count(*) from account where status='cancelled';
Is it possible to get count of each status in one query instead of 3?
- you could use a cross-tab query:
SELECT
date,
SUM(IF(CONCAT(status,id) LIKE 'received%',1,0)) as received,
SUM(IF(CONCAT(status,id) LIKE 'send%',1,0)) as send,
SUM(IF(CONCAT(status,id) LIKE 'cancelled%',1,0)) as cancelled
FROM
account
GROUP BY date;
- ian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select count
I have table account (see below). I need to get count of received, count of send and cound of cancelled records. I know that I can do this in 3 queries. #1. select count(*) from account where status='received'; #2. select count(*) from account where status='send'; #3. select count(*) from account where status='cancelled'; Is it possible to get count of each status in one query instead of 3? How about this: select (select count(*) from account where status = 'received') as count_received, (select count(*) from account where status = 'send') as count_send, (select count(*) from account where status = 'cancelled') as count_cancelled You could also build a stored proc that looped over a list of the values to find, or, I think maybe, over the actual distinct values in the field. Not so sure about that part. Dave Merrill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
