Re: What would happen in these two cases?
Thanks so much for the reply! So, if I understand correctly, to swap the table like I described, I should do: LOCK TABLES A write,A2 write; FLUSH TABLES; rename table A to A1; rename table A2 to A; UNLOCK TABLES; Right? If there is no write to either A or A2, then there is no need to lock the table, right? Thanks! On Mon, 09 Aug 2004 19:21:39 +0300, Egor Egorov [EMAIL PROTECTED] wrote: Haitao Jiang [EMAIL PROTECTED] wrote: Case 1: I have a table A under a running MySQL 4.1.3b server. If I replace A.MYD and A.MYI files without stop and start the server, will data gets corrupted? It depends on many factors. Honestly, there are lots of cases where you will get correct data and nothing wrong will happen. But you have to be an experience Unix developer to understand Unix internals in order to dance like that. :) So the general answer is: don't, it's too dangerous. Case 2: I rename A to A1, and A2 to A, assume A, A2 have the same schema but different data/index. Is it bad? Will data gets corrupted? I tied this one, it seems ok. Yes, you're right, it just *SEEMS* ok. :) If you really need to replace table files, use FLUSH TABLES, LOCK TABLES: http://dev.mysql.com/doc/mysql/en/FLUSH.html http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: Update with subquery problem
Subqueries are not supported until mysql 4.1. I'm guessing you have an earlier version. With version 4.0.4 or later, you can accomplish the same thing with a multi-table update: UPDATE tbl1, tbl2 SET tbl1.col1 = tbl1.col1 + 1 WHERE tbl1.ID = tbl2.ID AND tbl2.status='Active'; or equivalently UPDATE tbl1 JOIN tbl2 ON tbl1.ID = tbl2.ID SET tbl1.col1 = tbl1.col1 + 1 WHERE tbl2.status='Active'; See the manual for more http://dev.mysql.com/doc/mysql/en/UPDATE.html. Michael prolist wrote: I am trying to update a related table with a subquery. This is what I am using - update tbl1 set col1=col1+1 where ID IN (select ID from tbl2 where status='Active'); But I get syntax error. I am not much of a database guy, so can't understand what am I doing incorrectly. Can someone help? TIA, - Manish -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select first row within groups
Hi, If I want to find out highest score student from each class, how can I do that in MySQL? Assume the table looks like: classId INT, studentId INT, score INT In the case of multiple students from the same class has the same highest score, I would like to get the first one whose studentId is the smallest. I tried to use sub-query, but in the case of students of same highest score in the same class, they all get returned - I only want one from each class. Is it possible? Thanks a lot HT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Update with subquery problem
Manish, What version of MySQL are you using? The chances are subqueries are not supported in your version. Try restructuring your query as a join like: UPDATE tbl1, tbl2 SET tbl1.col1=tbl1.col1+1 WHERE tbl.ID = tbl2.ID AND tbl2.status='Active' http://dev.mysql.com/doc/mysql/en/UPDATE.html Regards, Lachlan -Original Message- From: prolist [mailto:[EMAIL PROTECTED] Sent: Tuesday, 10 August 2004 3:52 PM To: [EMAIL PROTECTED] Subject: Update with subquery problem I am trying to update a related table with a subquery. This is what I am using - update tbl1 set col1=col1+1 where ID IN (select ID from tbl2 where status='Active'); But I get syntax error. I am not much of a database guy, so can't understand what am I doing incorrectly. Can someone help? TIA, - Manish -- 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: left join issues!!!
Bruce,
I have reconstructed the database you have given me and used the following
query successfully (the one I originally gave you). I think there is
something wrong with your data.
SELECT
p2.statusID as parseStatus,
p2.action as parseAction,
u2.name,
p1.userID,
u3.ID,
u3.url as schoolUrl,
u3.urltype as urlType,
u1.name as school,
u1.city as city,
u1.stateVAL as state
FROM
universityTBL as u1
LEFT JOIN university_urlTBL as u3
ON u1.ID = u3.universityID
LEFT JOIN parsefileTBL as p1
ON u3.ID = p1.university_urlID
LEFT JOIN parsefilestatusTBL as p2
ON p1.fileID = p2.fileID
LEFT JOIN userTBL as u2
ON p1.userID = u2.ID
WHERE
u1.ID = '40';
To follow is the mysqldump of the test DB I used.. Hope this helps you out,
Regards,
Lachlan
-- MySQL dump 9.11
--
-- Host: localhostDatabase: mysql_testing
-- --
-- Server version 4.0.20-Max-log
--
-- Table structure for table `parsefileTBL`
--
CREATE TABLE parsefileTBL (
university_urlID int(5) NOT NULL default '0',
filelocation varchar(50) NOT NULL default '',
name varchar(50) NOT NULL default '',
userID int(10) NOT NULL default '0',
fileID int(10) NOT NULL auto_increment,
PRIMARY KEY (fileID),
UNIQUE KEY university_urlID (university_urlID,filelocation)
) TYPE=BerkeleyDB;
--
-- Dumping data for table `parsefileTBL`
--
INSERT INTO parsefileTBL VALUES (157,'','',0,1);
INSERT INTO parsefileTBL VALUES (158,'','',0,2);
INSERT INTO parsefileTBL VALUES (159,'','',0,3);
INSERT INTO parsefileTBL VALUES (160,'','',1,4);
--
-- Table structure for table `parsefilestatusTBL`
--
CREATE TABLE parsefilestatusTBL (
userID int(5) NOT NULL default '0',
testdate timestamp(14) NOT NULL,
action int(5) NOT NULL default '0',
statusID int(5) NOT NULL default '0',
fileID int(10) NOT NULL default '0',
UNIQUE KEY fileID (fileID,testdate)
) TYPE=BerkeleyDB;
--
-- Dumping data for table `parsefilestatusTBL`
--
--
-- Table structure for table `universityTBL`
--
CREATE TABLE universityTBL (
name varchar(50) NOT NULL default '',
city varchar(20) default '',
stateVAL varchar(5) NOT NULL default '',
userID int(10) NOT NULL default '0',
ID int(10) NOT NULL auto_increment,
PRIMARY KEY (ID),
UNIQUE KEY name (name)
) TYPE=BerkeleyDB;
--
-- Dumping data for table `universityTBL`
--
INSERT INTO universityTBL VALUES ('Auburn','city','state',0,40);
--
-- Table structure for table `university_urlTBL`
--
CREATE TABLE university_urlTBL (
universityID int(10) NOT NULL default '0',
urltype int(5) NOT NULL default '0',
url varchar(50) NOT NULL default '',
userID int(10) NOT NULL default '0',
actionID int(5) default '0',
status int(5) default '0',
ID int(10) NOT NULL auto_increment,
PRIMARY KEY (ID),
UNIQUE KEY url (url,universityID,urltype)
) TYPE=BerkeleyDB;
--
-- Dumping data for table `university_urlTBL`
--
INSERT INTO university_urlTBL VALUES (40,0,'url1',0,0,0,157);
INSERT INTO university_urlTBL VALUES (40,0,'url2',0,0,0,158);
INSERT INTO university_urlTBL VALUES (40,0,'url3',0,0,0,159);
INSERT INTO university_urlTBL VALUES (40,0,'url4',0,0,0,160);
--
-- Table structure for table `userTBL`
--
CREATE TABLE userTBL (
name varchar(20) NOT NULL default '',
email varchar(20) NOT NULL default '',
phone varchar(20) NOT NULL default '',
city varchar(20) NOT NULL default '',
state varchar(20) NOT NULL default '',
usergroup varchar(10) NOT NULL default '',
userlevel varchar(10) NOT NULL default '',
ID int(5) NOT NULL auto_increment,
UNIQUE KEY ID (ID)
) TYPE=BerkeleyDB;
--
-- Dumping data for table `userTBL`
--
INSERT INTO userTBL VALUES ('tom','','','','','','',1);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: left join issues!!!
Bruce,
I'm getting the four rows with one correctly matching the userID for tom and
the others returning NULLs for the user info.
Lachlan
-Original Message-
From: bruce [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 10 August 2004 5:13 PM
To: 'Lachlan Mulcahy'
Subject: RE: left join issues!!!
but what's your output...
when i run the select query you provided, i get a single row... as opposed
to the 4/four rows that i expect... (or at least the 4 rows that i'm trying
to get!!!)
i think what's happening is that the query is triggering off the only value
in the userTBL, and matching that with the match value in the parsefileTBL
which results in only a single row/match.. what i want/what i'm trying to
get is the names of the user in place of the userID (and NULL) if no user
exists...
so what's the result you're getting??
and i may have a data issue... if you're getting the 4 rows, then i'll send
the db/tabls i have to you so you can take a quick look it might be
something simple that i'm just missing!
thanks
-bruce
-Original Message-
From: Lachlan Mulcahy [mailto:[EMAIL PROTECTED]
Sent: Monday, August 09, 2004 11:17 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: RE: left join issues!!!
Bruce,
I have reconstructed the database you have given me and used the following
query successfully (the one I originally gave you). I think there is
something wrong with your data.
SELECT
p2.statusID as parseStatus,
p2.action as parseAction,
u2.name,
p1.userID,
u3.ID,
u3.url as schoolUrl,
u3.urltype as urlType,
u1.name as school,
u1.city as city,
u1.stateVAL as state
FROM
universityTBL as u1
LEFT JOIN university_urlTBL as u3
ON u1.ID = u3.universityID
LEFT JOIN parsefileTBL as p1
ON u3.ID = p1.university_urlID
LEFT JOIN parsefilestatusTBL as p2
ON p1.fileID = p2.fileID
LEFT JOIN userTBL as u2
ON p1.userID = u2.ID
WHERE
u1.ID = '40';
To follow is the mysqldump of the test DB I used.. Hope this helps you out,
Regards,
Lachlan
-- MySQL dump 9.11
--
-- Host: localhostDatabase: mysql_testing
-- --
-- Server version 4.0.20-Max-log
--
-- Table structure for table `parsefileTBL`
--
CREATE TABLE parsefileTBL (
university_urlID int(5) NOT NULL default '0',
filelocation varchar(50) NOT NULL default '',
name varchar(50) NOT NULL default '',
userID int(10) NOT NULL default '0',
fileID int(10) NOT NULL auto_increment,
PRIMARY KEY (fileID),
UNIQUE KEY university_urlID (university_urlID,filelocation)
) TYPE=BerkeleyDB;
--
-- Dumping data for table `parsefileTBL`
--
INSERT INTO parsefileTBL VALUES (157,'','',0,1);
INSERT INTO parsefileTBL VALUES (158,'','',0,2);
INSERT INTO parsefileTBL VALUES (159,'','',0,3);
INSERT INTO parsefileTBL VALUES (160,'','',1,4);
--
-- Table structure for table `parsefilestatusTBL`
--
CREATE TABLE parsefilestatusTBL (
userID int(5) NOT NULL default '0',
testdate timestamp(14) NOT NULL,
action int(5) NOT NULL default '0',
statusID int(5) NOT NULL default '0',
fileID int(10) NOT NULL default '0',
UNIQUE KEY fileID (fileID,testdate)
) TYPE=BerkeleyDB;
--
-- Dumping data for table `parsefilestatusTBL`
--
--
-- Table structure for table `universityTBL`
--
CREATE TABLE universityTBL (
name varchar(50) NOT NULL default '',
city varchar(20) default '',
stateVAL varchar(5) NOT NULL default '',
userID int(10) NOT NULL default '0',
ID int(10) NOT NULL auto_increment,
PRIMARY KEY (ID),
UNIQUE KEY name (name)
) TYPE=BerkeleyDB;
--
-- Dumping data for table `universityTBL`
--
INSERT INTO universityTBL VALUES ('Auburn','city','state',0,40);
--
-- Table structure for table `university_urlTBL`
--
CREATE TABLE university_urlTBL (
universityID int(10) NOT NULL default '0',
urltype int(5) NOT NULL default '0',
url varchar(50) NOT NULL default '',
userID int(10) NOT NULL default '0',
actionID int(5) default '0',
status int(5) default '0',
ID int(10) NOT NULL auto_increment,
PRIMARY KEY (ID),
UNIQUE KEY url (url,universityID,urltype)
) TYPE=BerkeleyDB;
--
-- Dumping data for table `university_urlTBL`
--
INSERT INTO university_urlTBL VALUES (40,0,'url1',0,0,0,157);
INSERT INTO university_urlTBL VALUES (40,0,'url2',0,0,0,158);
INSERT INTO university_urlTBL VALUES (40,0,'url3',0,0,0,159);
INSERT INTO university_urlTBL VALUES (40,0,'url4',0,0,0,160);
--
-- Table structure for table `userTBL`
--
CREATE TABLE userTBL (
name varchar(20) NOT NULL default '',
email varchar(20) NOT NULL default '',
phone varchar(20) NOT NULL default '',
city varchar(20) NOT NULL default '',
state varchar(20) NOT NULL default '',
usergroup varchar(10) NOT NULL default '',
userlevel varchar(10) NOT NULL default '',
ID int(5) NOT NULL auto_increment,
UNIQUE KEY ID (ID)
)
Joing two fields in a query
Hi, Is it possible to join two fields in a query so that they are displayed as one column? For exmaple: SELECT Firstname + ' ' + Lastname AS 'Name' FROM Users; I hope you can see what I am trying to achieve from SQL here! Thanks for your help _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joing two fields in a query
CONCAT() is what you need! SELECT CONCAT(Firstname,' ',Lastname) AS Name FROM Users; Terry - Original Message - Hi, Is it possible to join two fields in a query so that they are displayed as one column? For exmaple: SELECT Firstname + ' ' + Lastname AS 'Name' FROM Users; I hope you can see what I am trying to achieve from SQL here! Thanks for your help _ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LOAD DATA LOCAL INFILE issue
Hi Michael, Yes, I rebuilt it to ensure it was switched on. The configure line was ./configure --enable-local-infile --without-server Regards David Logan Database Administrator HP Managed Services 139 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Tuesday, 10 August 2004 3:19 PM To: Logan, David (SST - Adelaide) Cc: MySQL List Subject: Re: LOAD DATA LOCAL INFILE issue Well, as you say, that error message means it's been disabled in either the client or the server. You rebuilt the server from source with --enable-local-infile, and the server says local-infile is ON. You've tried turning it on in the client with --local-infile, but you haven't mentioned whether the client was built with --enable-local-infile. I'm not sure the command line switch does any good if local-infile was disabled at build time. So, did you build the client with --enable-local-infile? Michael Logan, David (SST - Adelaide) wrote: Hi Michael, Thanks. I rechecked things but porkribs /u2/lcscreative/sql_scripts $ mysql --local-infile -u davidl -p make_web_tables.sql Enter password: ERROR 1148 at line 46: The used command is not allowed with this MySQL version Still a most unhappy camper. I had to resort to placing the file on the server, owned and group mysql in the datadir before it would work. Even though I did chmod 777 on the file, it was still upset. I am running Solaris 8 on the client and 9 on the server. I don't know whether this would make a difference, I can't understand why it would. Regards David Logan Database Administrator HP Managed Services 139 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Tuesday, 10 August 2004 2:37 PM To: Logan, David (SST - Adelaide) Cc: MySQL List Subject: Re: LOAD DATA LOCAL INFILE issue Perhaps the problem is that there is no such option as --enable-local-infile in the mysql client. I believe you want --local-infile. Client options are detailed in the manual http://dev.mysql.com/doc/mysql/en/mysql.html. Michael Logan, David (SST - Adelaide) wrote: Hi Folks, I am having a few issues with a LOAD DATA LOCAL INFILE command. As you can see by the command below, I am receiving an error 1148. The documentation states this is generally because I don't have --enable-local-infile on in both client and server. I have switched it on via command line below and when I do a show variables on the server, I see local-infile = ON. I have also tried loose-local-infile in the my.cnf on both client and server. I have just rebuilt the server ensuring --enable-local-infile was in the ./configure options. Both client and server are version 4.0.20 and are on different hosts. The sql file I am using is CREATE DATABASE IF NOT EXISTS weblog; use weblog; CREATE TABLE IF NOT EXISTS dept (department INT PRIMARY KEY, main_dept INT NOT NULL, description VARCHAR(40)); CREATE TABLE IF NOT EXISTS dns_cache (ip_address CHAR(15) NOT NULL PRIMARY KEY, hostname VARCHAR(255)); CREATE TABLE IF NOT EXISTS web_rec (recnum INT NOT NULL PRIMARY KEY AUTO_INCREMENT, date DATE NOT NULL, time TIME NOT NULL, source_ip CHAR(15) NOT NULL, method CHAR(10) NOT NULL, department INT, source_port SMALLINT NOT NULL, username VARCHAR(255), c_ip CHAR(15), user_agent VARCHAR(255), referrer TEXT, last_status SMALLINT, last_substatus SMALLINT, win32_status SMALLINT, sent_bytes INT, recv_bytes INT); CREATE TABLE IF NOT EXISTS UserAgent (agentnum INT NOT NULL PRIMARY KEY AUTO_INCREMENT, browser VARCHAR(255), description VARCHAR(255)); CREATE TABLE IF NOT EXISTS Referrer (Refnum INT NOT NULL PRIMARY KEY AUTO_INCREMENT, ref_uri VARCHAR(255), hostname VARCHAR(255)); CREATE TABLE IF NOT EXISTS status (status INT NOT NULL PRIMARY KEY, description VARCHAR(255)); LOAD DATA LOCAL INFILE '/u2/lcscreative/sql_scripts/categories.csv' --- Line 46, error 1148 occurs here INTO TABLE dept FIELDS TERMINATED BY ',' ENCLOSED BY '' (main_dept,department,description); porkribs /u2/lcscreative/sql_scripts $ mysql --enable-local-infile -u davidl -p make_web_tables.sql Enter password: ERROR 1148 at line 46: The used command is not allowed with this MySQL version porkribs /u2/lcscreative/sql_scripts $ Anybody have any ideas on what I've done wrong? Regards David Logan Database Administrator HP Managed Services 139 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax --
Load data ini file
Dear Friends, My data in text file isn't been loaded to columns in table. Data text file is in data directory of server. rest of the commands are as follows, any guidance, please. mysql load data - infile 'kemailsusa.txt' - into table - kemailsusa; ERROR 1062: Duplicate entry '0-' for key 1
efficiency of searching in SETs (InnoDB)
Hi list, We're building a relatively large database with InnoDB tables. The database model is basically: store the data in a table based on the data type. So we have a param_int table for all integer type data: CREATE TABLE `param_int` ( `id` int(11) NOT NULL default '0', `name` varchar(32) NOT NULL default '', `value` int(14) NOT NULL default '0', PRIMARY KEY (`id`,`name`), KEY `name` (`name`), KEY `value` (`value`) ) TYPE=InnoDB COMMENT='integer parameters'; Etcetera for other basic types. (We did run tests with InnoDB, MyISAM, type based tables and the usual tables where all datatypes are stored in a few tables. InnoDB performed far better with larger number of records and the type based storage was easier when it comes to expanding the number of parameters). The question: I need to store flags which indicate access rights, etc. Since storing each flag in a tinyint or something similar doesn't seem efficient when the number of flags increases. My alternative was storing the flags as groups in one or more sets. The table will contain the sets for hundreds of thousands of IDs. How efficient is InnoDB with searching in such sets? Will it use an index or must it perform a full table search? Are there alternatives which are more efficient regarding search speed? Best regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load data ini file
[EMAIL PROTECTED] wrote: My data in text file isn't been loaded to columns in table. Data text file is in data directory of server. rest of the commands are as follows, any guidance, please. mysql load data - infile 'kemailsusa.txt' - into table - kemailsusa; ERROR 1062: Duplicate entry '0-' for key 1 See http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html In particular, you need IGNORE. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: Access Denied
[EMAIL PROTECTED] wrote: I didn't get a response to the question below, and my alternative solution produced another error message as follows. Rather than try to establish a new database, I used the Test database established when I reinstalled mysql. With a csv file saved under mysql/data/test, I did a load data command exactly as I have done many times before and got ERROR 1045 Access denied for user: '@localhost' (Using password: NO) I was able to create the table and successfully issue the commands SHOW TABLES and DESCRIBE TABLENAME As below mysql is installed on a pc running Windows 98. You are trying to connect as anonymous user and with no password. Not a good idea. Use login/password to access MySQL database. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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]
Errata in the Study Guide
Is this the most appropriate list to mention misprints? There doesn't
seem to be an indication where additional suggestions are to be sent.
I found something that, though not exactly incorrect, works for
reasons other than what a reader might think, so it's misleading.
--
___ Patrick Connolly
{~._.~}
_( Y )_ Good judgment comes from experience
(:_~*~_:) Experience comes from bad judgment
(_)-(_)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what is causing these XX.1.1.1.1 logs?
Ciarochi, Anthony [EMAIL PROTECTED] wrote: -rw-rw1 psccats mysql 63008 Aug 1 23:30 mysqld_bin.054.1.1.1.1 -rw-rw1 psccats mysql 0 Aug 4 04:05 mysqld_bin.054.1 -rw-rw1 psccats mysql 0 Aug 6 04:28 mysqld_bin.054.1.1 -rw-rw1 psccats mysql 0 Aug 8 04:33 mysqld_bin.054.1.1.1 =20 The pattern (in case it's not obvious): The logs are rotated every day or two, possibly by the backup script It looks more like a broken backup script is doing this... -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: Do Analyze Table before Optimize Table or the other way around??
Scott Fletcher [EMAIL PROTECTED] wrote: I wanna know is do I do the Analyze the table before the Optimize the table or should I do it the other way around??? If you need to defragment the table, you can just run OPTIMIZE TABLE. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: Finding Database and Tables
[EMAIL PROTECTED] wrote: I have mysql installed on a pc running Windows 98. I recently had to reformat a partitioned c drive. My tables were stored in a database called samp_db. When I give the command use samp_db I get Error 1044: Access denied for user: '@localhost' to database 'samp_db' When I give the command create database samp_db I get the same error message, and I get the same when I try to create a database with another name. How do I create the database (and what is this error)? Can I get the tables back under samp_db (I saved all mysql files to the d drive)? Thanks in advance. Recreate the data directory on C: and copy backup files into it like it was before you reformatted the drive. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: Multiple Connections
Paul McNeil [EMAIL PROTECTED] wrote: So this means that my DB driver is somehow using the same connection when I ask it to create 2 different ones? No way, it uses different connections. *BUT* check your DB interface. It can store the connection handle and don't really open a new connection when requested but instead return the previous open handle. This is how mysql_pconnect in PHP and connect_cached in DBI works. -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Monday, August 09, 2004 12:13 PM To: [EMAIL PROTECTED] Subject: Re: Multiple Connections Paul McNeil [EMAIL PROTECTED] wrote: This tells me that the DB is treating my connections as the same connection. I need to know if the problem is that MySQL is caching and reusing any connection from my client OR if the problem is with my driver. For each connection a new thread is created and this thread has every connection-dependent variables set to their default values. I.e. LAST_INSERT_ID() in a new thread will not return you a value from other. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: What would happen in these two cases?
Haitao Jiang [EMAIL PROTECTED] wrote: Thanks so much for the reply! So, if I understand correctly, to swap the table like I described, I should do: LOCK TABLES A write,A2 write; FLUSH TABLES; rename table A to A1; rename table A2 to A; UNLOCK TABLES; Right? If there is no write to either A or A2, then there is no need to lock the table, right? You can't rename locked tables. RENAME is atomic anyway so you can safely use it without lock. But your software should be aware of a possible race condition that happens between two RENAME TABLEs. Thanks! On Mon, 09 Aug 2004 19:21:39 +0300, Egor Egorov [EMAIL PROTECTED] wrote: Haitao Jiang [EMAIL PROTECTED] wrote: Case 1: I have a table A under a running MySQL 4.1.3b server. If I replace A.MYD and A.MYI files without stop and start the server, will data gets corrupted? It depends on many factors. Honestly, there are lots of cases where you will get correct data and nothing wrong will happen. But you have to be an experience Unix developer to understand Unix internals in order to dance like that. :) So the general answer is: don't, it's too dangerous. Case 2: I rename A to A1, and A2 to A, assume A, A2 have the same schema but different data/index. Is it bad? Will data gets corrupted? I tied this one, it seems ok. Yes, you're right, it just *SEEMS* ok. :) If you really need to replace table files, use FLUSH TABLES, LOCK TABLES: http://dev.mysql.com/doc/mysql/en/FLUSH.html http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: Uninstalling MySQL
[EMAIL PROTECTED] wrote: Greetings... How do I uninstall MySQL under linux (i have Fedora Core 2)... i will be installing a new version of it... I have 3.23 currently and will replace it with 4. Please Help Thanks Try like that rpm -qa | grep -i mysql This will give you the list of every package with word mysql in it. Then do 'rpm --erase' for all mysql packages (beware, don't delete everything listed - there might be something that just needs MySQL, but not the server itself). Then you can download the latest RPM binary version of MySQL from www.mysql.com and install it: rpm --install MySQL*rpm -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: What would you store in a BLOB field?
Levi Campbell [EMAIL PROTECTED] wrote: I know the blob field is binary but what would you store there? and if = you could give me an example of real-life uses please. You can store beer, juice or milk, but you need to escape the drink properly to get it back fresh and delicious. ;) Speaking seriously, BLOB is capable of storing any binary data. There is a long-lasting religion war about whether it's good to store, say, images in BLOB or not. I don't want to dive in that flame, but I must admit that both ways (storing binary data in BLOB or just a filename where the data is) has their pros and cons. Probably, the most popular real-life usage is storing generated website images (thumbnails) in BLOB fields. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: What would you store in a BLOB field?
Are you saying that BLOB can store external files? I'm not sure if I understood storing any binary data in a way you meant. I'm working on a project where program dumps a file along with report that comes with it. I was thinking maybe I could centerize multi programs' Data to Mysql... I hope I'm wrong knowing what SQL is like... -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 10, 2004 6:29 AM To: [EMAIL PROTECTED] Subject: Re: What would you store in a BLOB field? Levi Campbell [EMAIL PROTECTED] wrote: I know the blob field is binary but what would you store there? and if = you could give me an example of real-life uses please. You can store beer, juice or milk, but you need to escape the drink properly to get it back fresh and delicious. ;) Speaking seriously, BLOB is capable of storing any binary data. There is a long-lasting religion war about whether it's good to store, say, images in BLOB or not. I don't want to dive in that flame, but I must admit that both ways (storing binary data in BLOB or just a filename where the data is) has their pros and cons. Probably, the most popular real-life usage is storing generated website images (thumbnails) in BLOB fields. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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]
get extended infos over table-columns
hi, i need some extended infor over the table-columns! how can i get the maximumDataLength, numericPrecision, numericScale of a column? thx -- G H Softwareentwicklung GmbH Tel.: +49(0)7451/53706-20 Robert-Bosch-Str. 23 Fax: +49(0)7451/53706-90 D-72160 Horb a.N. http://www.guh-software.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: get extended infos over table-columns
Hi, i need some extended infor over the table-columns! how can i get the maximumDataLength, numericPrecision, numericScale of a column? Did you check the documentation? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: get extended infos over table-columns
yes!! Martijn Tonies schrieb am 10.08.2004 14:03: Hi, i need some extended infor over the table-columns! how can i get the maximumDataLength, numericPrecision, numericScale of a column? Did you check the documentation? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- G H Softwareentwicklung GmbH Tel.: +49(0)7451/53706-20 Robert-Bosch-Str. 23 Fax: +49(0)7451/53706-90 D-72160 Horb a.N. http://www.guh-software.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Errata in the Study Guide
Hi Patrick, On Tue, 2004-08-10 at 12:16, Patrick Connolly wrote: Is this the most appropriate list to mention misprints? There doesn't seem to be an indication where additional suggestions are to be sent. This could be a good starting place if you want to discuss something in general - if you have specifics that are not already mentioned in the Certification Study Guide Errata - http://www.mysql.com/training/certification/studyguides/ - then please send them to [EMAIL PROTECTED] I found something that, though not exactly incorrect, works for reasons other than what a reader might think, so it's misleading. Posting your thinking to the list might be a good way to raise people's awareness of the issues involved. That is of course said with no knowledge of what kind of issues you have in mind :-) Best regards, / Carsten -- Warning: Certification can seriously increase your wealth! http://www.mysql.com/certification Carsten Pedersen, Certification Manager MySQL AB, http://www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: get extended infos over table-columns
yes!! Did you find SHOW [FULL] COLUMNS FROM tablename I know I did when looking through the docs... With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: get extended infos over table-columns
YES! but these statement DON'T return my needed infos(maximumDataLength, numericPrecision, numericScale)!!! Martijn Tonies schrieb am 10.08.2004 14:27: yes!! Did you find SHOW [FULL] COLUMNS FROM tablename I know I did when looking through the docs... With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- G H Softwareentwicklung GmbH Tel.: +49(0)7451/53706-20 Robert-Bosch-Str. 23 Fax: +49(0)7451/53706-90 D-72160 Horb a.N. http://www.guh-software.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: get extended infos over table-columns
YES! but these statement DON'T return my needed infos(maximumDataLength, numericPrecision, numericScale)!!! Sure they do - in column Type. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com Martijn Tonies schrieb am 10.08.2004 14:27: yes!! Did you find SHOW [FULL] COLUMNS FROM tablename I know I did when looking through the docs... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqld_multi different server versions
You can use mysqld_multi to run different versions of MySQL on the same Server. You can run 4.0.x, 4.1.x and 5.0.x and manage these with mysqld_multi. -Original Message- From: sean c peters To: [EMAIL PROTECTED] Sent: 8/9/04 4:43 PM Subject: mysqld_multi different server versions In my ongoing quest to get upgraded to 4.1.3 beta (yes the version I'm upgrading to keeps changing), i have been reading about mysqld_multi to manage multiple server instances on the same machine. But, from what i've read, it appears that this is for running multiple instances of the same server version on one box. Same binary anyway. I say this because all the examples show as varying are the socket, port, pid-file, datadir, language, and user. The [mysqld_multi] directive for my.cnf points mysqld at a particular mysqld_safe, which would imply that all the instances would use the same server. So am i correct in thinking that i wont be able to use mysqld_multi for running two different server versions? This shouldn't be a problem, I dont think i'll need it. Just wanted to check if im missing something. thanks sean peters [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: select first row within groups
You are looking for a minimum value _within_ a maximum set. In this case, that will take two processing steps: DECLARE TEMPORARY TABLE tmpScores SELECT classid, Max(score) as topscore FROM tablename_goes_here GROUP BY classid; SELECT t.classid, t.Min(studentid), ts.topscore FROM tablename_goes_here t INNER JOIN tmpScores ts ON ts.classid = t.classid AND ts.topscore = t.score GROUP BY t.classid, ts.topscore; DROP TABLE tmpScores; That should answer the question For each class, what is the smallest student ID that scored highest for that class. You will need to replace tablename_goes_here with the actual name of your table. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Haitao Jiang [EMAIL PROTECTED] wrote on 08/10/2004 02:24:29 AM: Hi, If I want to find out highest score student from each class, how can I do that in MySQL? Assume the table looks like: classId INT, studentId INT, score INT In the case of multiple students from the same class has the same highest score, I would like to get the first one whose studentId is the smallest. I tried to use sub-query, but in the case of students of same highest score in the same class, they all get returned - I only want one from each class. Is it possible? Thanks a lot HT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: using temp tables...
CREATE TEMPORARY TABLE table SELECT * FROM source_table ... Query temp table ... DROP TABLE temp_table -Original Message- From: bruce To: [EMAIL PROTECTED] Sent: 8/10/04 12:45 AM Subject: using temp tables... hi... in trying to get a better understanding of temp tables. is there a way to perform a select, to write the results to a temp table, and then use the temp table to perform another operation on the information within the temp table... ie... select * from foo write the results of the select to a temp table perform an operation on the information stored within the temp table i'm curious as to how it can be done within the mysql env. searching through google/mysql didn't spell this out to me... thanks -bruce -- 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: What would you store in a BLOB field?
Thanks for the detailed answer. I'll change the whole plan, unifying multi databases into Mysql. Appreciate it very much. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 10, 2004 9:20 AM To: Scott Hamm Cc: [EMAIL PROTECTED] Subject: RE: What would you store in a BLOB field? Yes, you can store practically any data in a blob (within the limits of your storage space). As Egor stated, many places put thumbnail images (.bmp, .jpg, or .gif) files into their database for indexed retrieval and storage. These records probably also have a link to the larger (original sized) image that exists in disk storage but the smaller thumbnail image is stored in the database. Other ideas for BLOB columns (some of which were already mentioned in this thread): The digital sound recordings of birds (bird calls) Medical information: EEG strip data, Sonograms, CT/MRI/PET images Biometric information (retinal scans, fingerprints, voice profiles) Public and/or Private keys (for RSA-type encryptions) Encrypted data Digital Signatures Checksums Bit-field data (like that used by full-text indexes) Vector-based information (road maps, network diagrams, etc.) Basically anything you could store in a file on a disk, you could also store in a BLOB. That's ALSO why Egor mentioned the FLAME WAR about the efficiency of the storage and retrieval of the actual digital data with your database vs. the storage of only a link to that data in your database and serving it with another system (FTP, file share, web server, etc.) There is no universal answer as each situation is unique. Only testing and benchmarking will determine which is best for your situation. FWIW, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Scott Hamm [EMAIL PROTECTED] wrote on 08/10/2004 07:33:44 AM: Are you saying that BLOB can store external files? I'm not sure if I understood storing any binary data in a way you meant. I'm working on a project where program dumps a file along with report that comes with it. I was thinking maybe I could centerize multi programs' Data to Mysql... I hope I'm wrong knowing what SQL is like... -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 10, 2004 6:29 AM To: [EMAIL PROTECTED] Subject: Re: What would you store in a BLOB field? Levi Campbell [EMAIL PROTECTED] wrote: I know the blob field is binary but what would you store there? and if = you could give me an example of real-life uses please. You can store beer, juice or milk, but you need to escape the drink properly to get it back fresh and delicious. ;) Speaking seriously, BLOB is capable of storing any binary data. There is a long-lasting religion war about whether it's good to store, say, images in BLOB or not. I don't want to dive in that flame, but I must admit that both ways (storing binary data in BLOB or just a filename where the data is) has their pros and cons. Probably, the most popular real-life usage is storing generated website images (thumbnails) in BLOB fields. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: What would you store in a BLOB field?
Yes, you can store practically any data in a blob (within the limits of your storage space). As Egor stated, many places put thumbnail images (.bmp, .jpg, or .gif) files into their database for indexed retrieval and storage. These records probably also have a link to the larger (original sized) image that exists in disk storage but the smaller thumbnail image is stored in the database. Other ideas for BLOB columns (some of which were already mentioned in this thread): The digital sound recordings of birds (bird calls) Medical information: EEG strip data, Sonograms, CT/MRI/PET images Biometric information (retinal scans, fingerprints, voice profiles) Public and/or Private keys (for RSA-type encryptions) Encrypted data Digital Signatures Checksums Bit-field data (like that used by full-text indexes) Vector-based information (road maps, network diagrams, etc.) Basically anything you could store in a file on a disk, you could also store in a BLOB. That's ALSO why Egor mentioned the FLAME WAR about the efficiency of the storage and retrieval of the actual digital data with your database vs. the storage of only a link to that data in your database and serving it with another system (FTP, file share, web server, etc.) There is no universal answer as each situation is unique. Only testing and benchmarking will determine which is best for your situation. FWIW, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Scott Hamm [EMAIL PROTECTED] wrote on 08/10/2004 07:33:44 AM: Are you saying that BLOB can store external files? I'm not sure if I understood storing any binary data in a way you meant. I'm working on a project where program dumps a file along with report that comes with it. I was thinking maybe I could centerize multi programs' Data to Mysql... I hope I'm wrong knowing what SQL is like... -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 10, 2004 6:29 AM To: [EMAIL PROTECTED] Subject: Re: What would you store in a BLOB field? Levi Campbell [EMAIL PROTECTED] wrote: I know the blob field is binary but what would you store there? and if = you could give me an example of real-life uses please. You can store beer, juice or milk, but you need to escape the drink properly to get it back fresh and delicious. ;) Speaking seriously, BLOB is capable of storing any binary data. There is a long-lasting religion war about whether it's good to store, say, images in BLOB or not. I don't want to dive in that flame, but I must admit that both ways (storing binary data in BLOB or just a filename where the data is) has their pros and cons. Probably, the most popular real-life usage is storing generated website images (thumbnails) in BLOB fields. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: get extended infos over table-columns
And now that you know what TYPE a column is, you can use the information found on this page of the manual to answer your other questions http://dev.mysql.com/doc/mysql/de/Column_types.html Regards, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Martijn Tonies [EMAIL PROTECTED] wrote on 08/10/2004 08:46:09 AM: YES! but these statement DON'T return my needed infos(maximumDataLength, numericPrecision, numericScale)!!! Sure they do - in column Type. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com Martijn Tonies schrieb am 10.08.2004 14:27: yes!! Did you find SHOW [FULL] COLUMNS FROM tablename I know I did when looking through the docs... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with Slow Update Query
Hi all I have a problem with slow update queries like these (5 examples): update url_cat set domain = '01net' where left( domain, instr( domain, '.' ) -1 ) = '01net'; update url_cat set domain = '1-meta' where left( domain, instr( domain, '.' ) -1 ) = '1-meta'; update url_cat set domain = '105' where left( domain, instr( domain, '.' ) -1 ) = '105'; update url_cat set domain = '123love' where left( domain, instr( domain, '.' ) -1 ) = '123love'; update url_cat set domain = 'google' where left( domain, instr( domain, '.' ) -1 ) = 'google'; before the update, the field domain contains 'google.com' or 'google.de' or 'google.ch' and after the update it contains only 'google'. i have to make thousends of this updates an it takes a long time. the table 'url_cat' contains about 100'000 entries! an index would help, but mysql does not use any static index. the index should contain the where-condition... but this is not possible, because the lenght of the condition differs?! does anybody have another idea? thank you in advance. martin
Re: Problem with Slow Update Query
First create a table with the fixed domain names: CREATE TABLE FixedDomains SELECT DISTINCT domain, left( domain, instr( domain, '.' ) -1 ) as newdomain FROM url_cat Index your new table (for speed): ALTER FixedDomains ADD INDEX (Domain) *** NOTE: You really want to review (and modify, if necessary) the data in FixedDomains _before_ you do the next step. Make sure that the newdomain column has the correct extract for the value in the domain column. *** Now you can mass-update your data: UPDATE url_cat u INNER JOIN FixedDomains fd ON fd.domain = u.domain SET u.domain = fd.newdomain You can then DROP TABLE FixedDomains whenever you are through using it. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Martin Rytz [EMAIL PROTECTED] wrote on 08/10/2004 09:51:41 AM: Hi all I have a problem with slow update queries like these (5 examples): update url_cat set domain = '01net' where left( domain, instr( domain, '.' ) -1 ) = '01net'; update url_cat set domain = '1-meta' where left( domain, instr( domain, '.' ) -1 ) = '1-meta'; update url_cat set domain = '105' where left( domain, instr( domain, '.' ) -1 ) = '105'; update url_cat set domain = '123love' where left( domain, instr( domain, '.' ) -1 ) = '123love'; update url_cat set domain = 'google' where left( domain, instr( domain, '.' ) -1 ) = 'google'; before the update, the field domain contains 'google.com' or 'google.de' or 'google.ch' and after the update it contains only 'google'. i have to make thousends of this updates an it takes a long time. the table 'url_cat' contains about 100'000 entries! an index would help, but mysql does not use any static index. the index should contain the where-condition... but this is not possible, because the lenght of the condition differs?! does anybody have another idea? thank you in advance. martin
Re: Problem with Slow Update Query
An index on domain cannot be used to satisfy your WHERE clause, because you are comparing the result of a function performed on domain to a value. As soon as you feed your column to a function, you lose the use of an index on that column. So, each and every one of these queries performs a full table scan! Your best bet would be to have the application which inserts this data transform the url to a domain before inserting, so you have the data you want. Given your current setup, full table scans may be unavoidable, but you may be able to improve the situation. I suspect that with clever use of string functions http://dev.mysql.com/doc/mysql/en/String_functions.html we could dramatically reduce the number of such queries. If I understand you correctly, you want everything up to the first '.'. This should do the trick: UPDATE url_cat SET domain = SUBSTRING_INDEX(domain, '.', 1); That will replace domain with the contents of domain up to, but not including, the first '.' all in one pass. Yes, it's still a full table scan, but it's 1 full table scan. Michael Martin Rytz wrote: Hi all I have a problem with slow update queries like these (5 examples): update url_cat set domain = '01net' where left( domain, instr( domain, '.' ) -1 ) = '01net'; update url_cat set domain = '1-meta' where left( domain, instr( domain, '.' ) -1 ) = '1-meta'; update url_cat set domain = '105' where left( domain, instr( domain, '.' ) -1 ) = '105'; update url_cat set domain = '123love' where left( domain, instr( domain, '.' ) -1 ) = '123love'; update url_cat set domain = 'google' where left( domain, instr( domain, '.' ) -1 ) = 'google'; before the update, the field domain contains 'google.com' or 'google.de' or 'google.ch' and after the update it contains only 'google'. i have to make thousends of this updates an it takes a long time. the table 'url_cat' contains about 100'000 entries! an index would help, but mysql does not use any static index. the index should contain the where-condition... but this is not possible, because the lenght of the condition differs?! does anybody have another idea? thank you in advance. martin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: left join issues!!!
but what's your output...
when i run the select query you provided, i get a single row... as opposed
to the 4/four rows that i expect... (or at least the 4 rows that i'm trying
to get!!!)
i think what's happening is that the query is triggering off the only value
in the userTBL, and matching that with the match value in the parsefileTBL
which results in only a single row/match.. what i want/what i'm trying to
get is the names of the user in place of the userID (and NULL) if no user
exists...
so what's the result you're getting??
and i may have a data issue... if you're getting the 4 rows, then i'll send
the db/tabls i have to you so you can take a quick look it might be
something simple that i'm just missing!
thanks
-bruce
-Original Message-
From: Lachlan Mulcahy [mailto:[EMAIL PROTECTED]
Sent: Monday, August 09, 2004 11:17 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: RE: left join issues!!!
Bruce,
I have reconstructed the database you have given me and used the following
query successfully (the one I originally gave you). I think there is
something wrong with your data.
SELECT
p2.statusID as parseStatus,
p2.action as parseAction,
u2.name,
p1.userID,
u3.ID,
u3.url as schoolUrl,
u3.urltype as urlType,
u1.name as school,
u1.city as city,
u1.stateVAL as state
FROM
universityTBL as u1
LEFT JOIN university_urlTBL as u3
ON u1.ID = u3.universityID
LEFT JOIN parsefileTBL as p1
ON u3.ID = p1.university_urlID
LEFT JOIN parsefilestatusTBL as p2
ON p1.fileID = p2.fileID
LEFT JOIN userTBL as u2
ON p1.userID = u2.ID
WHERE
u1.ID = '40';
To follow is the mysqldump of the test DB I used.. Hope this helps you out,
Regards,
Lachlan
-- MySQL dump 9.11
--
-- Host: localhostDatabase: mysql_testing
-- --
-- Server version 4.0.20-Max-log
--
-- Table structure for table `parsefileTBL`
--
CREATE TABLE parsefileTBL (
university_urlID int(5) NOT NULL default '0',
filelocation varchar(50) NOT NULL default '',
name varchar(50) NOT NULL default '',
userID int(10) NOT NULL default '0',
fileID int(10) NOT NULL auto_increment,
PRIMARY KEY (fileID),
UNIQUE KEY university_urlID (university_urlID,filelocation)
) TYPE=BerkeleyDB;
--
-- Dumping data for table `parsefileTBL`
--
INSERT INTO parsefileTBL VALUES (157,'','',0,1);
INSERT INTO parsefileTBL VALUES (158,'','',0,2);
INSERT INTO parsefileTBL VALUES (159,'','',0,3);
INSERT INTO parsefileTBL VALUES (160,'','',1,4);
--
-- Table structure for table `parsefilestatusTBL`
--
CREATE TABLE parsefilestatusTBL (
userID int(5) NOT NULL default '0',
testdate timestamp(14) NOT NULL,
action int(5) NOT NULL default '0',
statusID int(5) NOT NULL default '0',
fileID int(10) NOT NULL default '0',
UNIQUE KEY fileID (fileID,testdate)
) TYPE=BerkeleyDB;
--
-- Dumping data for table `parsefilestatusTBL`
--
--
-- Table structure for table `universityTBL`
--
CREATE TABLE universityTBL (
name varchar(50) NOT NULL default '',
city varchar(20) default '',
stateVAL varchar(5) NOT NULL default '',
userID int(10) NOT NULL default '0',
ID int(10) NOT NULL auto_increment,
PRIMARY KEY (ID),
UNIQUE KEY name (name)
) TYPE=BerkeleyDB;
--
-- Dumping data for table `universityTBL`
--
INSERT INTO universityTBL VALUES ('Auburn','city','state',0,40);
--
-- Table structure for table `university_urlTBL`
--
CREATE TABLE university_urlTBL (
universityID int(10) NOT NULL default '0',
urltype int(5) NOT NULL default '0',
url varchar(50) NOT NULL default '',
userID int(10) NOT NULL default '0',
actionID int(5) default '0',
status int(5) default '0',
ID int(10) NOT NULL auto_increment,
PRIMARY KEY (ID),
UNIQUE KEY url (url,universityID,urltype)
) TYPE=BerkeleyDB;
--
-- Dumping data for table `university_urlTBL`
--
INSERT INTO university_urlTBL VALUES (40,0,'url1',0,0,0,157);
INSERT INTO university_urlTBL VALUES (40,0,'url2',0,0,0,158);
INSERT INTO university_urlTBL VALUES (40,0,'url3',0,0,0,159);
INSERT INTO university_urlTBL VALUES (40,0,'url4',0,0,0,160);
--
-- Table structure for table `userTBL`
--
CREATE TABLE userTBL (
name varchar(20) NOT NULL default '',
email varchar(20) NOT NULL default '',
phone varchar(20) NOT NULL default '',
city varchar(20) NOT NULL default '',
state varchar(20) NOT NULL default '',
usergroup varchar(10) NOT NULL default '',
userlevel varchar(10) NOT NULL default '',
ID int(5) NOT NULL auto_increment,
UNIQUE KEY ID (ID)
) TYPE=BerkeleyDB;
--
-- Dumping data for table `userTBL`
--
INSERT INTO userTBL VALUES ('tom','','','','','','',1);
--
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
Re: Mixing Innodb MyISAM tables
sean c peters [EMAIL PROTECTED] wrote: Im considering a design that mixes InnoDB and MyISAM tables. I want Innodb for speed, etc, but i have one table where i want a column to have a FULLTEXT index on. Thus the need for MyISAM. Im not worried about the performance in using the MyISAM tables, as we speak, the production version of the system is using MyISAM tables without problems. The main concern is that by using a MyISAM table, i lose foreign key support, and cannot do a cascade on delete, which i'd really like to have, and not have to manually mimic the cascade behaviour. I suppose i've gotten along fine without having foreign key support for a number of years, so this probably isnt that bad. Anyone else run into similar issues? Any thoughts? Also consider that data in MyISAM is updated independently of the transactions. So in case you run an open transaction or rollback one - this doesn't affect MyISAM. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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 1030 (HY000): Got error 127 from storage engine
Haitao Jiang [EMAIL PROTECTED] wrote: I have done following on a 8 million row table (4GB): repair table optimize table drop one of the fulltext index optimize table again Now fulltext search stop working and I am getting: ERROR 1030 (HY000): Got error 127 from storage engine What does this mean? Index corrupted? How that happen? Any place I can get a list of MySQL error code and know what they mean? [EMAIL PROTECTED] ~]$ perror 127 127 = Record-file is crashed -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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]
Permissions problem with MySQL 4.1.3 | Update: Same with 5.0.1
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello, I also tried the 5.0.1 MAX binaries looking for a solution to my previously posted problem of permissions (see at the bottom of the message for the previous post). According to the hostname.err file: 040810 13:31:44 mysqld started 040810 13:31:44 bdb: /usr/local/mysql5/data: Permission denied 040810 13:31:44 bdb: /usr/local/mysql5/data/log.01: Permission denied 040810 13:31:44 bdb: PANIC: Permission denied 040810 13:31:44 bdb: PANIC: DB_RUNRECOVERY: Fatal error, run database recovery 040810 13:31:44 bdb: fatal region error detected; run recovery 040810 13:31:44 bdb: /usr/local/mysql5/data: Permission denied 040810 13:31:44 InnoDB: Started; log sequence number 0 43634 040810 13:31:44 Can't init databases 040810 13:31:44 Aborting 040810 13:31:44 InnoDB: Starting shutdown... 040810 13:31:46 InnoDB: Shutdown completed; log sequence number 0 43634 040810 13:31:46 /usr/local/mysql5/bin/mysqld: Shutdown complete 040810 13:31:46 mysqld ended A complete archived plaintext strace logfile can be found here: http://www.lwo-lab.net/log.mysql-list.01.gz It was generated using: # strace -v -s 512 -o log -f -v -t ./mysqld --user=mysql Before suggesting any chmod/chown, please take a look at this: tearspath www # ls -l -h /usr/local/mysql5/data/ total 19M -rw-rw 1 mysql mysql 4.0M Aug 10 14:25 ib_logfile0 -rw-rw 1 mysql mysql 4.0M Aug 9 14:36 ib_logfile1 -rw-rw 1 mysql mysql 10M Aug 10 14:25 ibdata1 drwxr-x--- 2 mysql mysql 4.0K Aug 9 14:27 mysql -rw-rw 1 mysql root 6.1K Aug 10 13:31 tearspath.err drwxr-x--- 2 mysql mysql 4.0K Jul 27 20:53 test I'd like to add that mysqld is properly running as mysql:mysql according to the strace log: 12299 14:25:07 setgid32(506)= 0 12299 14:25:07 setuid32(1006) = 0 (506 and 1006 are the respective GID and UID for mysql and mysql) Any help will be really appreciated, I'm on this for a month now. Thanks in advance. Elie `woe` BLETON Elie `woe` BLETON wrote: | Hello, | | I'd like to ask for some help on a problem which have prevented my mysql | server to run since I updated it from 4.0 to 4.1 | Anyway, even if in the idea it's an update, in the facts it's a fresh | install, from sources. | Sources were configure'd with ./configure --prefix=/usr/local/mysql. | | After doing everything I could find on google, manual, mailing lists and | stuff, I still have basicly the same problems : | | (a) Running mysql_install_db --user=mysql /dev/null prints a lot of | errors, most of them are Errcode: 13 (Permission denied), and resulting | Table 'xxx.yyy' doesn't exist messages. See Annex A for detailed output. | | (b) Running libexec/mysqld --console --user=mysql prints : | InnoDB: Operating system error number 13 in a file operation. | InnoDB: The error means mysqld does not have the access rights to | InnoDB: the directory. | InnoDB: File name /usr/local/mysql/var/ibdata1 | InnoDB: File operation call: 'create'. | InnoDB: Cannot continue operation. | | Running both of these programs as root works fine, but I don't really | want mysql to run as root, even if it's just for testing purposes. | | There are some points which seems really strange to me. First point is | that mysql_install_db is able to create mysql and test directories | in var without problem. | Nothing changes if I chown -R mysql:mysql /usr/local/mysql/var | Nothing changes if I chown -R mysql:mysql /usr/local/mysql | Nothing changes if I chmod -R 777 /usr/local/mysql | Nothing changes if these these elements are made altogether. | | The other strange point is that if the var directory isn't owned by | mysql user, InnoDB complaints about not beeing able to create | innodb.status. in var. Once var is owned by mysql, it can create the | file without problem. | I can't understand why it cannot create ibdata1 if it can create the | other one. | | I've also tried to install_db as root, then run mysqld as root for one | time in order to get inno files created properly. Switching back to | mysqld --user=mysql isn't possible anyway. | | I'm open to any suggestion or help, and available to provide any further | information as needed. Thanks in advance for your time. | | Elie `woe` BLETON | | APPENDIX A | Output of /usr/local/mysql/bin/mysql_install_db --user=mysql | | ERROR: 1 Can't create/write to file '/usr/local/mysql/var/mysql/db.MYI' | (Errcode: 13) | ERROR: 1146 Table 'mysql.db' doesn't exist | ERROR: 1146 Table 'mysql.db' doesn't exist | ERROR: 1 Can't create/write to file | '/usr/local/mysql/var/mysql/host.MYI' (Errcode: 13) | ERROR: 1 Can't create/write to file | '/usr/local/mysql/var/mysql/user.MYI' (Errcode: 13)
Re: InnoDB Deletes / Swap Problem
Marvin Wright [EMAIL PROTECTED] wrote: We have just recently moved 1 of our InnoDB cache servers to a new much bigger machine, 4 CPU, 8GB Ram and masses amount of disk space available from a SAN. The OS is Redhat AS 3 with kernel 2.4. MySQL is the only application on this machine and its the latest stable release of 4.0.x. Throughout the day most of the queries are inserts and selects and the machine runs no problem with the mysql sitting at about 1.6Gb in memory. At night we run an expiry job where we clean our expired records from the cache, there are a few million records removed. The job runs for about 4/5 hours but for some reason the machine starts to use swapspace. I dont understand why the machine would use it. The MySQL process never goes above 2GB RAM in memory so where does all the other memory on the machine disappear to. 1. Have you watched MySQL processes with top command? 2. Have you tried to swapoff -a ? 3. What are your MySQL variables settings? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: Is LOAD DATA INFILE an atomic operation?
Sergei Skarupo [EMAIL PROTECTED] wrote: In MyISAM tables, can LOAD DATA INFILE be considered an atomic operation = under normal circumstances? Yes. What will happen if the server crashes or someone kills the thread? Probably, a table crash. Probably, repairable. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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]
bug
I have downloaded and installed MYSQL Client/Server 4.0. 1. when I get a query on SHOW DATABASES, it only shows test and not mysql which has the users info 2. It doesn't give me access to create a new DATABASE and gives me an access denied error. Please advise. Thanks. p.s. I have worked with MSQL 2.0 and I never had these problems. Regards, Farnaz Akhavi Research Engineer Engineering Technology Industrial Distribution 303 Fermier Hall , 3367 TAMU Texas AM University College Station, TX - 77843-3367 Phone: 979-845-4939 Fax:979-845-4980 http://etidweb.tamu.edu/industrialdistribution/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB slowness
sean c peters [EMAIL PROTECTED] wrote: I am in the process of upgrading one of my systems to use InnoDB tables, along with some other tweaks to my code. In any case, i just tried to delete around 7000 records from a table, where there are 9 other tables that will cascade delete when rows from the other table are deleted. Overall, id guess 140,000 rows are being deleted from all tables. This sat for at least 15 minutes, eventually i killed it and it took quite a while for that database to recover. (it was rolling back the tranaction i assume). In any case i didnt expect it to take that long to delete. Im now deleting in chunks of 100 rows in the main table, each chunk is taking 15-30 seconds. This is on a 4 processor sun box running solaris, with 4GB of ram, and a lot of swap (8 GB i think). This is running MySQL 4.0.2 alpha, so that could be part of the issue. Any comments. Should it take this long? First you definitely need to upgrade to a recent MySQL version. 4.0.20. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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]
combining information from 4 tables into a fifth.
Hey all, I've three tables (g2d, dgp and pocus), with a gene-name, a score and some other information: mysql select * from dgp; ++---+--+--+++ | chr| start | stop | gene_name | score | strand | ++---+--+--+++ | chr13 | 69810099 | 70239331 | ENSG0165659 | 738.681 | - | | chr13 | 64676788 | 65600573 | ENSG0184226 | 627.447 | - | | chr13 | 59781822 | 59787282 | ENSG0150506 | 619.467 | + | And I've a table with a lot of gene-name's. Now I want to combine these 4 tables into a fifth table, named score, wich list in the first column all the gene names, and in the three succesive columns the score for that gene from the g2d, dgp, and pocus tables, if a score is found. Otherwise the default value should be 0.00 Like this: mysql select * from score; +--+-+--+--+ | gene_name | score_g2d | score_dgp | score_pocus | +--+-+--+--+ | ENSG0165659 | 0.000 | 738.681 | 0.00 | | ENSG0184226 | 123,123 | 627.447 | 0.00 | | ENSG099 | 0.000 | 0.00 | 987,987 | .. How should I construct my query? I've been trying for hours now, and can't get it rigth. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: bug
Who did you log in as? -Original Message- From: Farnaz Akhavi To: [EMAIL PROTECTED] Sent: 8/10/04 9:57 AM Subject: bug I have downloaded and installed MYSQL Client/Server 4.0. 1. when I get a query on SHOW DATABASES, it only shows test and not mysql which has the users info 2. It doesn't give me access to create a new DATABASE and gives me an access denied error. Please advise. Thanks. p.s. I have worked with MSQL 2.0 and I never had these problems. Regards, Farnaz Akhavi Research Engineer Engineering Technology Industrial Distribution 303 Fermier Hall , 3367 TAMU Texas AM University College Station, TX - 77843-3367 Phone: 979-845-4939 Fax:979-845-4980 http://etidweb.tamu.edu/industrialdistribution/ -- 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: combining information from 4 tables into a fifth.
You didn't give the name of the 4th table, so I'll call it t4. I think this will do: CREATE TABLE score ( gene_name CHAR(15), score_g2d REAL, score_dgp REAL, score_pocus REAL) SELECT t4.gene_name, COALESCE(g2d.score,0), COALESCE(dgp.score,0), COALESCE(pocus.score,0) FROM t4 LEFT JOIN g2d ON t4.gene_name=g2d.gene_name LEFT JOIN dgp ON t4.gene_name=dgp.gene_name LEFT JOIN pocus ON t4.gene_name=pocus.gene_name; Change the column types to whatever is appropriate for your situation. Michael Jeroen Van Goey wrote: Hey all, I've three tables (g2d, dgp and pocus), with a gene-name, a score and some other information: mysql select * from dgp; ++---+--+--+++ | chr| start | stop | gene_name | score | strand | ++---+--+--+++ | chr13 | 69810099 | 70239331 | ENSG0165659 | 738.681 | - | | chr13 | 64676788 | 65600573 | ENSG0184226 | 627.447 | - | | chr13 | 59781822 | 59787282 | ENSG0150506 | 619.467 | + | And I've a table with a lot of gene-name's. Now I want to combine these 4 tables into a fifth table, named score, wich list in the first column all the gene names, and in the three succesive columns the score for that gene from the g2d, dgp, and pocus tables, if a score is found. Otherwise the default value should be 0.00 Like this: mysql select * from score; +--+-+--+--+ | gene_name | score_g2d | score_dgp | score_pocus | +--+-+--+--+ | ENSG0165659 | 0.000 | 738.681 | 0.00 | | ENSG0184226 | 123,123 | 627.447 | 0.00 | | ENSG099 | 0.000 | 0.00 | 987,987 | .. How should I construct my query? I've been trying for hours now, and can't get it rigth. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: combining information from 4 tables into a fifth.
I don't know about everyone else but I would build scores like this: Create an empty scores table. Make gene_name the Primary Key. Allow the other columns to be null or give them a default of 0.0. Whatever that value is will represent no score available (I suggest null as a score of 0 may be a possible value) Insert the contents of one of your 3 feeder table into scores (I picked g2d): INSERT scores (gene_name, g2d) SELECT gene_name, score FROM g2d What I would like to be able to do next is an INSERT...SELECT ... ON DUPLICATE KEY UPDATE statement for the other two tables. However, that INSERT form is not available (http://dev.mysql.com/doc/mysql/en/INSERT.html) So what I wind up doing is creating a temporary table of IDs that I will need to update. I use that list to help me merge the data for the two remaining tables. CREATE TEMPORARY TABLE tmpMatches SELECT DISTINCT s.gene_name FROM scores s INNER JOIN dgp ON dgp.gene_name = s.gene_name; ALTER TABLE tmpMatches ADD KEY(gene_name); INSERT scores (gene_name, dgp) SELECT gene_name, score FROM dgp LEFT JOIN tmpMatches tm ON tm.gene_name = dgp.gene_name WHERE tm.gene_name is null; UPDATE scores s INNER JOIN dgp ON dgp.gene_name = s.gene_name SET s.dgp = dgp.score; TRUNCATE TABLE tmpMatches; INSERT tmpMatches (gene_name) SELECT DISTINCT s.gene_name FROM scores s INNER JOIN pocus p ON p.gene_name = s.gene_name; INSERT scores (gene_name, pocus) SELECT gene_name, score FROM pocus p LEFT JOIN tmpMatches tm ON tm.gene_name = p.gene_name WHERE tm.gene_name is null; UPDATE scores s INNER JOIN pocus p ON p.gene_name = s.gene_name SET s.pocus = p.score; DROP TABLE tmpMatches; What you should end up with is that scores should list each gene only once with the score values from the other tables (where they are available). Wherever you have null values will indicate no data from that table. Notes on technique: It is much faster for the query engine to find matches than non-matches (That's why I build a table of gene_names that already exist in both tables) . There should already be indexes on both tables so this will be a very fast comparison. The index on my temporary table considerably speeds up the LEFT JOIN in the INSERT in the next step. (reducing the cost of our non-matching query) The UPDATE is also joining tables on indexed columns so it should also execute very quickly. Using this same basic method to combine columns between similar tables, just like your situation, I have merged two 500,000 row tables *on a laptop* in less than 15 seconds Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jeroen Van Goey [EMAIL PROTECTED] wrote on 08/10/2004 11:44:29 AM: Hey all, I've three tables (g2d, dgp and pocus), with a gene-name, a score and some other information: mysql select * from dgp; ++---+-- +--+++ | chr| start | stop | gene_name | score | strand | ++---+-- +--+++ | chr13 | 69810099 | 70239331 | ENSG0165659 | 738.681 | - | | chr13 | 64676788 | 65600573 | ENSG0184226 | 627.447 | - | | chr13 | 59781822 | 59787282 | ENSG0150506 | 619.467 | + | And I've a table with a lot of gene-name's. Now I want to combine these 4 tables into a fifth table, named score, wich list in the first column all the gene names, and in the three succesive columns the score for that gene from the g2d, dgp, and pocus tables, if a score is found. Otherwise the default value should be 0.00 Like this: mysql select * from score; +--+-+-- +--+ | gene_name | score_g2d | score_dgp | score_pocus | +--+-+-- +--+ | ENSG0165659 | 0.000 | 738.681 | 0.00 | | ENSG0184226 | 123,123 | 627.447 | 0.00 | | ENSG099 | 0.000 | 0.00 | 987,987 | .. How should I construct my query? I've been trying for hours now, and can't get it rigth. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: connect to mySQL without database
Probably I was late in replying (just busy with some stupid work). In all the API if you dont specify the dbname in the mysql_connect() functions. In C the connection function is mysql_real_connect(), in PHP its mysql_pconnect(), donno about other APIs. You can get a list of dbs by issueing show databases SQL. One thing you have to make sure is that you have the permission :) regards, karam --- Michael Seele [EMAIL PROTECTED] wrote: oh! sorry! i use java! [EMAIL PROTECTED] schrieb am 09.08.2004 14:33: Not which natural language, which programming language? We need to know in order to help you. C, C++, Java, Perl, PHP, Python... The answer will differ. Alec Michael Seele [EMAIL PROTECTED] 09/08/2004 13:20 To [EMAIL PROTECTED] cc Subject Re: connect to mySQL without database i use mySQL english version! why? Karam Chand schrieb am 09.08.2004 14:13: Yes. Which language you are using? Regards, Karam --- Michael Seele [EMAIL PROTECTED] wrote: hi, is it possible to connect to a mySQL-Server without entering a database and then check all available databases? thx mseele -- G H Softwareentwicklung GmbH Tel.: +49(0)7451/53706-20 Robert-Bosch-Str. 23 Fax: +49(0)7451/53706-90 D-72160 Horb a.N. http://www.guh-software.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail -- G H Softwareentwicklung GmbH Tel.: +49(0)7451/53706-20 Robert-Bosch-Str. 23 Fax: +49(0)7451/53706-90 D-72160 Horb a.N. http://www.guh-software.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB file per table directive
I've been reading (and reading...) the innodb documentation about using the innodb_file_per_table directive, and one thing still confuses me. If i use this directive, then is it correct that the setting innodb_data_file_path will specify the shared innodb information? Also, when i create databases, there will be a directory created off of datadir that will hold all the separate innodb table files for that database. I.E. when i do: Create database innodb_test; # this creates a directory [datadir]/innodb_test/ and when i issue: use database innodb_test; Create table test_1 (... )type=InnoDB; # this creates files: # [datadir]/innodb_test/test_1.frm # [datadir]/innodb_test/test_1.ibd And when i create a separate db, say innodb_test_2 then basically the same thing happens, but in directory innodb_test_2 ? What i want to do is have separate physical drives mounted in subdirectories of [datadir], so each physical disk holds separate databases. There will also be smaller DBs on the physical drive that [datadir] is on. Am i understanding all of this correctly? thanks much sean peters [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
trouble building mysql 4.1.3
I am getting an error from make test when trying to build MySQL 4.1.3 beta The error is as follows: ERROR: .../mysql-4.1.3-beta/mysql-test/var/run/master.pid was not created in 30 seconds; Aborting make: *** [test] Error 1 I read online about using the --with-extra-charset=complex with configure. (even though this is documented as fixed, ive tried without this directive also, with the same error) This runs on Solaris: SunOS [machine_name] 5.9 Generic_112233-11 sun4u sparc SUNW,Ultra-Enterprise Here is everything i did from the command line: (i am running as root - i tried as another user too) CC=gcc CFLAGS=-03 CXX=gcc CXXFLAGS=-03 felide-constructors -fno-exceptions -fno-rtti ./configure --with-low-memory --enable-assembler \ --with-extra-charset=complex --with-tcp-port=3307 \ --with-unix-socket-path=/tmp/mysql-4_1_3.sock \ --prefix=/usr/local/mysql-4.1.3 LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib; export LD_LIBRARY_PATH make make test So im at a loss. Note that i have all the nonstandard settings: port, etc. because i am trying to test this version ( tweak carious settings) on a production server without disturbing the 4.0.2 that is already running. Thanks again. sean peters [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using mysql in commercial software
On Aug 09, 2004, at 17:56, Jeremy Zawodny wrote: This is confusing. There are *two* official places to discuss the licensing and community issues? Ugh. Or am I misunderstanding things? The licensing forum seems to generally be used provide quick answers on licensing questions for proprietary software developers, while the community forum is more for discussing any non-technical issue that relates to the MySQL community. Cheers! --zak -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Pattern Matching
I have 2 tables set up in MySQL, one with a dialed number field and
duration, the other with a list of country codes, there names, and the
rates. I am trying to match the dialed number with country code. My
problem is I cannot get the results based on the longest possible match?
I am not even sure if the query is correct, but I feel like I am close:
mysql select distinctrow a.calldate, a.src, substring(a.dst,4,5),
sec_to_time(a.billsec) as billsec, format((a.billsec/60 * b.rate), 2)
totalcost, b.destination, b.name from cdr a left join rates b on
substring(a.dst,4,5) regexp (concat('^[2-9]?', b.destination)) where
src='erick' and dst like '011%' group by calldate order by 'calldate'
desc;
For example (511444 = a.dst) and it matches with (51 =
b.destination), (Peru = b.name).
I need this to match (511 = b.destination), (Peru Lima = b.name)
Any suggestions would be greatly appreciated.
Thanks in advance,
Jason Glicken
OOPs wrong query on initial post
I have 2 tables set up in MySQL, one with a dialed number field and
duration, the other with a list of country codes, there names, and the
rates. I am trying to match the dialed number with country code. My
problem is I cannot get the results based on the longest possible match?
I am not even sure if the query is correct, but I feel like I am close:
This was regexp test - did not match from beginning of string ( all
results wrong)
mysql select distinctrow a.calldate, a.src, substring(a.dst,4,5),
sec_to_time(a.billsec) as billsec, format((a.billsec/60 * b.rate), 2)
totalcost, b.destination, b.name from cdr a left join rates b on
substring(a.dst,4,5) regexp (concat('^[2-9]?', b.destination)) where
src='erick' and dst like '011%' group by calldate order by 'calldate'
desc;
correct query
select distinctrow a.calldate, a.src, substring(a.dst,4,5),
sec_to_time(a.billsec) as billsec, format((a.billsec/60 * b.rate), 2)
totalcost, b.destination, b.name from cdr a left join rates b on
substring(a.dst,4,5) regexp (concat('^', b.destination)) where
src='erick' and dst like '011%' group by calldate order by 'calldate'
desc;
For example (511444 = a.dst) and it matches with (51 =
b.destination), (Peru = b.name).
I need this to match (511 = b.destination), (Peru Lima = b.name)
Any suggestions would be greatly appreciated.
Thanks in advance,
Jason Glicken
Difference between PostgreSQL and MySQL
Hi all, mr.super newbie here, what is the best for cold fusion development? I know that NASA uses MySQL but I have also been told that more 'professionals'(???) use PostgreSQL. Let the flaming begin!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
want input on method to avoid replication conflicts
I have four machines A,B,C,D.. A and B are dual masters while C and D are read only slaves A --- B | | C D I have a load balancer that connects all clients to A, and when it fails, connects them to be. In practice it is a little more complicated, because if a server becomes backlogged, it recuses itself from rotation (but there is logic to prevent a cascade where all servers recuse themselves). The problem is, that once a failover has occured, from A - B, then B has to pretty much stay the master forever until it fails, and A takes over. My load balancer doesn't support this kind of logic and manual intervention would be required to implement it. I have thought about making the top few bits of the auto increment key reflect the server id (thanks for the idea Jeremy), but I don't really want partitioned auto-increment values. What I am thinking about doing, is modifying the autoincrement values so that server A always produces an EVEN value, while server B always produces an ODD value. That seems like it would nearly eliminate the possiblity of conflicts, and my writes could be load balanced without any problems. It won't fix problems with conflicts of other unique columns, but that seems like a good thing for the most part at least for my applications. My healthcheck logic will make sure that server A doesn't appear to be back up to the load balancer until it has caught up with server B, or vice versa. Does this sound like a good idea, or should I be thinking about some other way to do this? Justin Swanhart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Difference between PostgreSQL and MySQL
EWAGW wrote: Hi all, mr.super newbie here, what is the best for cold fusion development? I know that NASA uses MySQL but I have also been told that more 'professionals'(???) use PostgreSQL. Let the flaming begin!! No need for flames. I think the two are converging. PostgreSQL started out with a focus on being full-featured and as powerful as Oracle. MySQL started out with a focus on being fast and small. Today, PostgreSQL is as full-featured as ever, and guess what... it's becoming faster too. MySQL is still fast, but it's adding more and more features. They are approaching the asme point... they just started on different ends of the spectrum. Five years from now, IMO, there'll be little if any difference. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What would happen in these two cases?
It would be great if there is a swap table command that is atomic. Thanks a lot Haitao On Tue, 10 Aug 2004 13:23:30 +0300, Egor Egorov [EMAIL PROTECTED] wrote: Haitao Jiang [EMAIL PROTECTED] wrote: Thanks so much for the reply! So, if I understand correctly, to swap the table like I described, I should do: LOCK TABLES A write,A2 write; FLUSH TABLES; rename table A to A1; rename table A2 to A; UNLOCK TABLES; Right? If there is no write to either A or A2, then there is no need to lock the table, right? You can't rename locked tables. RENAME is atomic anyway so you can safely use it without lock. But your software should be aware of a possible race condition that happens between two RENAME TABLEs. Thanks! On Mon, 09 Aug 2004 19:21:39 +0300, Egor Egorov [EMAIL PROTECTED] wrote: Haitao Jiang [EMAIL PROTECTED] wrote: Case 1: I have a table A under a running MySQL 4.1.3b server. If I replace A.MYD and A.MYI files without stop and start the server, will data gets corrupted? It depends on many factors. Honestly, there are lots of cases where you will get correct data and nothing wrong will happen. But you have to be an experience Unix developer to understand Unix internals in order to dance like that. :) So the general answer is: don't, it's too dangerous. Case 2: I rename A to A1, and A2 to A, assume A, A2 have the same schema but different data/index. Is it bad? Will data gets corrupted? I tied this one, it seems ok. Yes, you're right, it just *SEEMS* ok. :) If you really need to replace table files, use FLUSH TABLES, LOCK TABLES: http://dev.mysql.com/doc/mysql/en/FLUSH.html http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: Difference between PostgreSQL and MySQL
On Tue, 10 Aug 2004 15:35:20 -0500, EWAGW [EMAIL PROTECTED] wrote: Hi all, mr.super newbie here, what is the best for cold fusion development? The best depends on the requirements. You have not told us any requirements, except that you work with ColdFusion. If that is *really* the only consideration, go with PostgreSQL: ColdFusion MX is Java based and PostgreSQL has a stable release with Unicode support. But I am sure that in an answer you will give us many more requirements which may or may not change my recommendation :) Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Difference between PostgreSQL and MySQL
Thanks for the reply Brad, in that case I think I will stick with mysql as it seems more people use mysql in the cold fusion community at present, cheers - Original Message - From: Brad Tilley [EMAIL PROTECTED] To: EWAGW [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, August 10, 2004 3:45 PM Subject: Re: Difference between PostgreSQL and MySQL EWAGW wrote: Hi all, mr.super newbie here, what is the best for cold fusion development? I know that NASA uses MySQL but I have also been told that more 'professionals'(???) use PostgreSQL. Let the flaming begin!! No need for flames. I think the two are converging. PostgreSQL started out with a focus on being full-featured and as powerful as Oracle. MySQL started out with a focus on being fast and small. Today, PostgreSQL is as full-featured as ever, and guess what... it's becoming faster too. MySQL is still fast, but it's adding more and more features. They are approaching the asme point... they just started on different ends of the spectrum. Five years from now, IMO, there'll be little if any difference. -- 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: Difference between PostgreSQL and MySQL
Well, since you admitted to being a newbie, I would suggest that you learn with MySQL. It supports several types of data storage (memory only, ISAM, full-relational) and both transacted and non-transacted execution models. That's just about everything you could want a database to do. Development is nearing completion on Stored Procedures which means that Triggers are not far away. Those last two elements really round out the already impressive features built into MySQL. Sometime, in the far distant future, if you find that MySQL is somehow unable to meet your development needs then I encourage you to find a product that can do what you need. However, I have found very few situations that MySQL was not up to the job. The only other thing to consider is whether Cold Fusion has integrated its development environment better with one or the other. I don't use it (CF) or I could comment on whether it's easier to use one DB over the other. my two cents Shawn Green Database Administrator Unimin Corporation - Spruce Pine EWAGW [EMAIL PROTECTED] wrote on 08/10/2004 04:35:20 PM: Hi all, mr.super newbie here, what is the best for cold fusion development? I know that NASA uses MySQL but I have also been told that more 'professionals'(???) use PostgreSQL. Let the flaming begin!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
make test grant_cache test failing
Hi all, still building 4.1.3 I am now getting an error in make test like this: ERROR: ... At line 136: Result length mismatch (the last lines may be the most important ones) Below are the diffs between actual and expected results: --- *** r/grant_cache.resultMon Jun 28 01:26:46 2004 --- r/grant_cache.rejectTue Aug 10 23:44:49 2004 *** *** 112,119 Variable_name Value Qcache_not_cached 1 show grants for current_user(); ! Grants for @localhost ! GRANT USAGE ON *.* TO ''@'localhost' select user2; user2 user2 --- 112,119 Variable_name Value Qcache_not_cached 1 show grants for current_user(); ! Grants for [EMAIL PROTECTED] ! GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION select user2; user2 user2 --- Please follow the instructions outlined at http://www.mysql.com/doc/en/Reporting_mysqltest_bugs.html to find the reason to this problem and how to report this. Aborting: grant_cache failed. To continue, re-run with '--force'. Ending Tests Shutting-down MySQL daemon Master shutdown finished Slave shutdown finished make: *** [test] Error 1 When i run: diff grant_cache.result grant_cache.reject here is the output: 115,116c115,116 Grants for @localhost GRANT USAGE ON *.* TO ''@'localhost' --- Grants for [EMAIL PROTECTED] GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION This looks somewhat trivial, but i cant get beyond it to finish the test. make does not understand the --force directive, so the advice in the error wont work. thanks much, sean peters [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Difference between PostgreSQL and MySQL
On Tue, 10 Aug 2004 16:45:29 -0400 Brad Tilley [EMAIL PROTECTED] wrote: No need for flames. I think the two are converging. One area where MySQL beat Postgres is in Windows installation. Installing postgres on Windohs is like pulling your fingernails off slowly. I hear they are close to full Windows support though in the 8.x branch. MySQL's command line interface and programming API also are nicer for newer users. Why in the world do I need to remember to type \d to show my tables? That said though, I do like both. Oracle is nice too. :) It'll be really interesting to compare pg and mysql in a couple years... Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What capacity of registers will mysql support?
Will it work with 20 million for example? Thanks a lot for any advice
MySQL 4.0.20 vs MySQL 4.1.3b
Hi, I just wondering if there is a list of important features or bug fixes comparing these two versions. We are debating whether to go one or the another. Also, when is the 4.1.4 going to come out? Is it still beta? Thanks so much! HT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What capacity of registers will mysql support?
- Original Message - From: EWAGW To: [EMAIL PROTECTED] Sent: Tuesday, August 10, 2004 4:08 PM Subject: What capacity of registers will mysql support? Will it work with 20 million for example? Thanks a lot for any advice
Re: make test grant_cache test failing
On Tue, 2004-08-10 at 16:59, sean c peters wrote: Hi all, still building 4.1.3 I am now getting an error in make test like this: ERROR: ... At line 136: Result length mismatch (the last lines may be the most important ones) Below are the diffs between actual and expected results: --- *** r/grant_cache.resultMon Jun 28 01:26:46 2004 --- r/grant_cache.rejectTue Aug 10 23:44:49 2004 *** *** 112,119 Variable_name Value Qcache_not_cached 1 show grants for current_user(); ! Grants for @localhost ! GRANT USAGE ON *.* TO ''@'localhost' select user2; user2 user2 --- 112,119 Variable_name Value Qcache_not_cached 1 show grants for current_user(); ! Grants for [EMAIL PROTECTED] ! GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION select user2; user2 user2 --- Please follow the instructions outlined at http://www.mysql.com/doc/en/Reporting_mysqltest_bugs.html to find the reason to this problem and how to report this. Aborting: grant_cache failed. To continue, re-run with '--force'. Ending Tests Shutting-down MySQL daemon Master shutdown finished Slave shutdown finished make: *** [test] Error 1 When i run: diff grant_cache.result grant_cache.reject here is the output: 115,116c115,116 Grants for @localhost GRANT USAGE ON *.* TO ''@'localhost' --- Grants for [EMAIL PROTECTED] GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION This looks somewhat trivial, but i cant get beyond it to finish the test. make does not understand the --force directive, so the advice in the error wont work. thanks much, sean peters [EMAIL PROTECTED] I have run into this before. Are you running your tests as system user 'root' by any chance? MySQL recognizes your system user when there is a mysql user of the same name and, since by default MySQL user 'root' needs no password, there is nothing to prevent it. Try running your test as a regular user and this should fix itself. -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: make test grant_cache test failing
UPDATE: I modified the grant_cache.result file to the expected output and got past the test. It looked like the incorrect query was being issued, or in any case that make test was doing something different than what the grant_Cache.result file expected On Tuesday 10 August 2004 15:59, sean c peters wrote: Hi all, still building 4.1.3 I am now getting an error in make test like this: ERROR: ... At line 136: Result length mismatch (the last lines may be the most important ones) Below are the diffs between actual and expected results: --- *** r/grant_cache.resultMon Jun 28 01:26:46 2004 --- r/grant_cache.rejectTue Aug 10 23:44:49 2004 *** *** 112,119 Variable_name Value Qcache_not_cached 1 show grants for current_user(); ! Grants for @localhost ! GRANT USAGE ON *.* TO ''@'localhost' select user2; user2 user2 --- 112,119 Variable_name Value Qcache_not_cached 1 show grants for current_user(); ! Grants for [EMAIL PROTECTED] ! GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION select user2; user2 user2 --- Please follow the instructions outlined at http://www.mysql.com/doc/en/Reporting_mysqltest_bugs.html to find the reason to this problem and how to report this. Aborting: grant_cache failed. To continue, re-run with '--force'. Ending Tests Shutting-down MySQL daemon Master shutdown finished Slave shutdown finished make: *** [test] Error 1 When i run: diff grant_cache.result grant_cache.reject here is the output: 115,116c115,116 Grants for @localhost GRANT USAGE ON *.* TO ''@'localhost' --- Grants for [EMAIL PROTECTED] GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION This looks somewhat trivial, but i cant get beyond it to finish the test. make does not understand the --force directive, so the advice in the error wont work. thanks much, sean peters [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Difference between PostgreSQL and MySQL
Subject: Re: Difference between PostgreSQL and MySQL Well, since you admitted to being a newbie, I would suggest that you learn with MySQL. It supports several types of data storage (memory only, ISAM, full-relational) and both transacted and non-transacted execution models. Ehm no - not FULLY relational ... InnoDB doesn't do CHECK constraints. Then again, no DBMS is to be said fully relational anyway. That's just about everything you could want a database to do. Development is nearing completion on Stored Procedures which means that Triggers are not far away. Those last two elements really round out the already impressive features built into MySQL. From what I've heard, Triggers will be included in the next 5 beta. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Difference between PostgreSQL and MySQL
No need for flames. I think the two are converging. One area where MySQL beat Postgres is in Windows installation. Installing postgres on Windohs is like pulling your fingernails off slowly. I hear they are close to full Windows support though in the 8.x branch. FYI: http://archives.postgresql.org/pgsql-announce/2004-08/msg1.php With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Difference between PostgreSQL and MySQL
Thanks a lot Shawn, Josh and Brad for your great advice. The command line interface you talk about is that in MySQL administrator? MySQL's command line interface and programming API also are nicer for newer users. Why in the world do I need to remember to type \d to show my tables? That said though, I do like both. Oracle is nice too. :) It'll be really interesting to compare pg and mysql in a couple years... Josh -- 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: Difference between PostgreSQL and MySQL
Thank Jochem as well interesting reply got me thinking - Original Message - From: Jochem van Dieten [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 10, 2004 3:51 PM Subject: Re: Difference between PostgreSQL and MySQL On Tue, 10 Aug 2004 15:35:20 -0500, EWAGW [EMAIL PROTECTED] wrote: Hi all, mr.super newbie here, what is the best for cold fusion development? The best depends on the requirements. You have not told us any requirements, except that you work with ColdFusion. If that is *really* the only consideration, go with PostgreSQL: ColdFusion MX is Java based and PostgreSQL has a stable release with Unicode support. But I am sure that in an answer you will give us many more requirements which may or may not change my recommendation :) Jochem -- 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: What would happen in these two cases?
You can put multiple renames in one statement, and the entire rename will be atomic.. I create summary tables from some of my data, and I periodically refresh them. When refreshing them I create new tables to replace the old tables with.. Then I do: rename current_table to old_table, new_table to current_table On Tue, 10 Aug 2004 13:50:34 -0700, Haitao Jiang [EMAIL PROTECTED] wrote: It would be great if there is a swap table command that is atomic. Thanks a lot Haitao On Tue, 10 Aug 2004 13:23:30 +0300, Egor Egorov [EMAIL PROTECTED] wrote: Haitao Jiang [EMAIL PROTECTED] wrote: Thanks so much for the reply! So, if I understand correctly, to swap the table like I described, I should do: LOCK TABLES A write,A2 write; FLUSH TABLES; rename table A to A1; rename table A2 to A; UNLOCK TABLES; Right? If there is no write to either A or A2, then there is no need to lock the table, right? You can't rename locked tables. RENAME is atomic anyway so you can safely use it without lock. But your software should be aware of a possible race condition that happens between two RENAME TABLEs. Thanks! On Mon, 09 Aug 2004 19:21:39 +0300, Egor Egorov [EMAIL PROTECTED] wrote: Haitao Jiang [EMAIL PROTECTED] wrote: Case 1: I have a table A under a running MySQL 4.1.3b server. If I replace A.MYD and A.MYI files without stop and start the server, will data gets corrupted? It depends on many factors. Honestly, there are lots of cases where you will get correct data and nothing wrong will happen. But you have to be an experience Unix developer to understand Unix internals in order to dance like that. :) So the general answer is: don't, it's too dangerous. Case 2: I rename A to A1, and A2 to A, assume A, A2 have the same schema but different data/index. Is it bad? Will data gets corrupted? I tied this one, it seems ok. Yes, you're right, it just *SEEMS* ok. :) If you really need to replace table files, use FLUSH TABLES, LOCK TABLES: http://dev.mysql.com/doc/mysql/en/FLUSH.html http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What would happen in these two cases?
That is what I need! Thanks so much again to everyone! HT On Tue, 10 Aug 2004 14:32:13 -0700, Justin Swanhart [EMAIL PROTECTED] wrote: You can put multiple renames in one statement, and the entire rename will be atomic.. I create summary tables from some of my data, and I periodically refresh them. When refreshing them I create new tables to replace the old tables with.. Then I do: rename current_table to old_table, new_table to current_table On Tue, 10 Aug 2004 13:50:34 -0700, Haitao Jiang [EMAIL PROTECTED] wrote: It would be great if there is a swap table command that is atomic. Thanks a lot Haitao On Tue, 10 Aug 2004 13:23:30 +0300, Egor Egorov [EMAIL PROTECTED] wrote: Haitao Jiang [EMAIL PROTECTED] wrote: Thanks so much for the reply! So, if I understand correctly, to swap the table like I described, I should do: LOCK TABLES A write,A2 write; FLUSH TABLES; rename table A to A1; rename table A2 to A; UNLOCK TABLES; Right? If there is no write to either A or A2, then there is no need to lock the table, right? You can't rename locked tables. RENAME is atomic anyway so you can safely use it without lock. But your software should be aware of a possible race condition that happens between two RENAME TABLEs. Thanks! On Mon, 09 Aug 2004 19:21:39 +0300, Egor Egorov [EMAIL PROTECTED] wrote: Haitao Jiang [EMAIL PROTECTED] wrote: Case 1: I have a table A under a running MySQL 4.1.3b server. If I replace A.MYD and A.MYI files without stop and start the server, will data gets corrupted? It depends on many factors. Honestly, there are lots of cases where you will get correct data and nothing wrong will happen. But you have to be an experience Unix developer to understand Unix internals in order to dance like that. :) So the general answer is: don't, it's too dangerous. Case 2: I rename A to A1, and A2 to A, assume A, A2 have the same schema but different data/index. Is it bad? Will data gets corrupted? I tied this one, it seems ok. Yes, you're right, it just *SEEMS* ok. :) If you really need to replace table files, use FLUSH TABLES, LOCK TABLES: http://dev.mysql.com/doc/mysql/en/FLUSH.html http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Difference between PostgreSQL and MySQL
On Tue, 10 Aug 2004 16:00:12 -0500, Josh Trutwin wrote: One area where MySQL beat Postgres is in Windows installation. Installing postgres on Windohs is like pulling your fingernails off slowly. It is more like following the manual. Not hard, you just have to take it one step at a time. I hear they are close to full Windows support though in the 8.x branch. The current BETA offers identical features on Windows and Linux. But don't use beta software in production (neither PostgreSQL nor MySQL). MySQL's command line interface and programming API also are nicer for newer users. Why in the world do I need to remember to type \d to show my tables? Why in the world do I need to remember SHOW TABLES? Why can't the standard information schema work? :-) Command line interface and programming API are pretty much irrelevant if you are using ColdFusion. It is all abstracted out behind a JDBC API (ColdFusion is still at JDC 2), which in turn is behind ColdFusion's API, so unless you go the way of the Java ninja you can't even reach it. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What would happen in these two cases?
FYI, the atomicity of rename and using it to swap tables is discussed in the manual here: http://dev.mysql.com/doc/mysql/en/RENAME_TABLE.html Justin On Tue, 10 Aug 2004 13:50:34 -0700, Haitao Jiang [EMAIL PROTECTED] wrote: It would be great if there is a swap table command that is atomic. Thanks a lot Haitao -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Difference between PostgreSQL and MySQL
On Tue, 10 Aug 2004 16:49:26 -0400, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Well, since you admitted to being a newbie, I would suggest that you learn with MySQL. It supports several types of data storage (memory only, ISAM, full-relational) and both transacted and non-transacted execution models. That's just about everything you could want a database to do. No, not really: http://sql-info.de/mysql/gotchas.html Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Difference between PostgreSQL and MySQL
Thanks Emmett and Martijn!! - Original Message - From: EWAGW [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 10, 2004 4:31 PM Subject: Re: Difference between PostgreSQL and MySQL Thank Jochem as well interesting reply got me thinking - Original Message - From: Jochem van Dieten [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 10, 2004 3:51 PM Subject: Re: Difference between PostgreSQL and MySQL On Tue, 10 Aug 2004 15:35:20 -0500, EWAGW [EMAIL PROTECTED] wrote: Hi all, mr.super newbie here, what is the best for cold fusion development? The best depends on the requirements. You have not told us any requirements, except that you work with ColdFusion. If that is *really* the only consideration, go with PostgreSQL: ColdFusion MX is Java based and PostgreSQL has a stable release with Unicode support. But I am sure that in an answer you will give us many more requirements which may or may not change my recommendation :) Jochem -- 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: Difference between PostgreSQL and MySQL
At 03:35 PM 8/10/2004, you wrote: Hi all, mr.super newbie here, what is the best for cold fusion development? I know that NASA uses MySQL but I have also been told that more 'professionals'(???) use PostgreSQL. Let the flaming begin!! One thing that wasn't mentioned is MySQL requires a license (approx $500) if you distribute non-gpl applications, like commercial applications or applications where you're not providing the source code. PostgreSQL applicatoins can be distributed for free. If you are using MySQL on a webserver then the license isn't required because you're not distributing it. But if you are distributing applications then the $500 might make a difference to your bottom line. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: want input on method to avoid replication conflicts
Each server inserts a hearbeat value into a replicated table. The server can see when the last heartbeat it received from its master was. If a heartbeat has not been received from the master in 3 minutes, and the master is available, then the replication status is checked with show slave status to see what the status is. If replication is running and there are no errors, then I assume a long operation is taking place (such as create index) and allow both servers to remain up. If either of the replication threads have stopped because of an error, then the server recuses itself and a page is automatically sent to me. The biggest problem here is that the servers can't both recuse themselves. I have a number of healthchecks and each is assigned a severity level. The more severe the problems the less healthy the server is said to be. My script basically says if I am more healthy than my peer, then I will report up, if I am equally healthy as my peer, if my server_id is 1 then I will report down, if I am less healthy than my peer, then I will report down That is the logic for writes. For reads basically it is if I am not backlogged, then I am up On Tue, 10 Aug 2004 17:50:27 -0400, Mayuran Yogarajah [EMAIL PROTECTED] wrote: Justin Swanhart wrote: Im curious about this part. My healthcheck logic will make sure that server A doesn't appear to be back up to the load balancer until it has caught up with server B, or vice versa. How do you accomplish this ? We have a similar situation here where a check is needed to be done to see if a slave has caught up to a master. Are you checking the binary log names/positions or something more ? thanks, Mayuran -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Jeremy Zawodny's gcc flags or MySQL AB' for a FreeBSD?
On Thu, Aug 05, 2004 at 12:19:37PM +0900, Evgeny Chuykov wrote: Good day. From these sources: http://jeremy.zawodny.com/blog/archives/000458.html http://dev.mysql.com/doc/mysql/en/FreeBSD.html Jeremy is using -O -march=pentiumpro and MySQL AB -O2 -fno-strength-reduce. Does anyone compared this? Or it make no sense? PS. MySQL 4.x and FreeBSD 4.x I know that mine work. :-) I suspect you'll have a hard time finding a performance differnce between the two unless you try really hard to measure it. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setting custom information in processlist
On Thu, Aug 05, 2004 at 12:36:55PM +0100, Naran Hirani wrote: Hi, I'm using a single shared user-login for a web-based application to my mysql database - is there a way of including some information at connect time or during processing that would show up when issuing `show processlist'? Only if you prefixed each query with a comment: /* foo #3 */ SELECTL * FROM world ORDER BY... But not at connect time. Basically, I need to able to distinguish potentially multiple connections with the same login and process information some how. This sort of thing is possible in other SQL engines so probably should be in MySQL too. Interesting. How do other systems handle this? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: High Memory Usage
On Thu, Aug 05, 2004 at 10:27:54AM -0500, Sashi Ramaswamy wrote: Hi, All of a sudden the memory used by mysql threads has gone up. Each thread is consuming about 20 M of RAM. My databases are really small and usage is not very intense. Tables in the database are of type INNODB. MySQL server version is 4.0.14-standard. I suspect most of that is shared memory, not private. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update on condition
I need to do a certain update based on a condition: MySql4 Update table set bounce_count = bounce_count+1 is the basic query, somewhere in there I need to only do a if bounce_count+1 10 then update status = 'bounced_out' -- - 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: Setting custom information in processlist
Oracle has a procedure called DBMS_APPLICATION_INFO.SET_APPLICATION_INFO
that allows you to specify up to 64k of addtional information about
the current connection. It doesn't have any way to specify this
information at connect time though.
The data can be accessed in Oracle through the V$SESSION system view,
or through
userenv('CLIENT_INFO')
Something in mysql that would be similar and just as easy to implement would be:
create table process_info(ThreadID int, Information text, primary key
(ThreadID));
then in each connection do:
replace into process_info values (CONNECTION_ID(), 'Connection details here');
Add in a little cron job that removes old values from the process_info
table nightly and that should do the trick.
On Tue, 10 Aug 2004 15:24:34 -0700, Jeremy Zawodny [EMAIL PROTECTED] wrote:
On Thu, Aug 05, 2004 at 12:36:55PM +0100, Naran Hirani wrote:
Hi,
I'm using a single shared user-login for a web-based application to
my mysql database - is there a way of including some information at
connect time or during processing that would show up when issuing
`show processlist'?
Only if you prefixed each query with a comment:
/* foo #3 */ SELECTL * FROM world ORDER BY...
But not at connect time.
Basically, I need to able to distinguish potentially multiple
connections with the same login and process information some how.
This sort of thing is possible in other SQL engines so probably
should be in MySQL too.
Interesting. How do other systems handle this?
Jeremy
--
Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo!
[EMAIL PROTECTED] | http://jeremy.zawodny.com/
[book] High Performance MySQL -- http://highperformancemysql.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: Difference between PostgreSQL and MySQL
Just in addition, The current pricing on the MySQL site is 440EUR for the Pro License and 220EUR for the Classic License. The difference is that classic excludes the InnoDB storage engine. People may argue over the strict legal line where licenses are required to be purchased, especially when the boundaries of precisely what it is you may be selling begin to blur, but the spirit of the licensing is that for each server you implement in any kind of proprietary application you pay the appropriate licensing fee. Quote from the site: In their simplest form, the following are general licensing guidelines: If your software is licensed under either the GPL-compatible Free Software License as defined by the Free Software Foundation or approved by OSI, then use our GPL licensed version. If you distribute a proprietary application in any way, and you are not licensing and distributing your source code under GPL, you need to purchase a commercial license of MySQL If you are unsure, we recommend that you buy our cost effective commercial licenses. That is the safest solution. Licensing questions can submitted online for our advice, and we encourage you to refer to the Free Software Foundation or a lawyer as appropriate. Lachlan -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Wednesday, 11 August 2004 7:58 AM To: [EMAIL PROTECTED] Subject: Re: Difference between PostgreSQL and MySQL At 03:35 PM 8/10/2004, you wrote: Hi all, mr.super newbie here, what is the best for cold fusion development? I know that NASA uses MySQL but I have also been told that more 'professionals'(???) use PostgreSQL. Let the flaming begin!! One thing that wasn't mentioned is MySQL requires a license (approx $500) if you distribute non-gpl applications, like commercial applications or applications where you're not providing the source code. PostgreSQL applicatoins can be distributed for free. If you are using MySQL on a webserver then the license isn't required because you're not distributing it. But if you are distributing applications then the $500 might make a difference to your bottom line. Mike -- 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: CREATE performance degradation from 4.0.17 - 4.0.20
On Wed, Aug 04, 2004 at 04:32:42PM +0300, Egor Egorov wrote: Sergei Golubchik [EMAIL PROTECTED] wrote: We're upgrading from 3.23.58 to 4.0.20 and found that that although the ALTER test results of sql-bench had been greatly improved, CREATE has shown nasty performance degradation. Just before needing to make the decision to revert back to 3.23.58, we found a post here where someone had a similar problem when using SAN storage. We see the problem using hardware RAID, shared storage or local SCSI disks. Yes. Since 4.0.17 MySQL sync()'s after it created an .frm file (in CREATE/ALTER TABLE). And note that the sync() call not only physically writes .frm file to disk, but also everything else which is in write cache. If the server is under load, sync() call may take seconds, tens of seconds or even hundreds of seconds. As one usually doesn't create tables at the huge rate, it is not a problem. Unfortunately, it is apparently a problem for sql-bench :( Time to add a NO_SYNC option to CREATE TABLE, Sergei ? :) Wouldn't it make more sense to use fsync() on just the .frm file? Or am I missing something here? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Update on condition
Scott, This may work for you: UPDATE tableName SET bounce_count = bounce_count +1, status = 'bounced_out' WHERE (bounce_count + 1) 10; UPDATE tableName SET bounce_count = bounce_count + 1 WHERE status 'bounced_out' AND (bounce_count + 1) = 10; You might want to lock the table while you run those to ensure that nothing else is breaking the atomicity of the queries. If you are using InnoDB you would wrap it in a transaction. Lachlan -Original Message- From: Scott Haneda [mailto:[EMAIL PROTECTED] Sent: Wednesday, 11 August 2004 8:28 AM To: MySql Subject: Update on condition I need to do a certain update based on a condition: MySql4 Update table set bounce_count = bounce_count+1 is the basic query, somewhere in there I need to only do a if bounce_count+1 10 then update status = 'bounced_out' -- - 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update on condition
How about UPDATE table SET bounce_count = bounce_count+1, status = if(bounce_count 9, 'bounced_out', status) WHERE ...; Michael Scott Haneda wrote: I need to do a certain update based on a condition: MySql4 Update table set bounce_count = bounce_count+1 is the basic query, somewhere in there I need to only do a if bounce_count+1 10 then update status = 'bounced_out' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update on condition
And note that assignments are evaluated left to right, so the value of bounce_count has already been incremented when we get to the status assignment. Adjust accordingly. Michael Michael Stassen wrote: How about UPDATE table SET bounce_count = bounce_count+1, status = if(bounce_count 9, 'bounced_out', status) WHERE ...; Michael Scott Haneda wrote: I need to do a certain update based on a condition: MySql4 Update table set bounce_count = bounce_count+1 is the basic query, somewhere in there I need to only do a if bounce_count+1 10 then update status = 'bounced_out' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using foreign char sets
Hi, My native tongue is english but I'm learning finnish and want to create a database using my new language. Swe7 will apparently cover the few characters I need which aren't part of the latin1 set. So I've restarted the server with the option, --default-character-set=swe7 What I think I need now is a keymap so I can pick out the characters from my english-based keyboard. I've spent a good deal of time searching for this without success. Anyone know where I should look? Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.20 vs MySQL 4.1.3b
On Tue, Aug 10, 2004 at 02:12:50PM -0700, Haitao Jiang wrote: Hi, I just wondering if there is a list of important features or bug fixes comparing these two versions. We are debating whether to go one or the another. Also, when is the 4.1.4 going to come out? Is it still beta? Sure there is. They're listed in the documentation. http://www.mysql.com/doc/ Jeremy (Yes, I know that's the old URL, but my fingers memorized it years ago) -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: efficiency of searching in SETs (InnoDB)
On Tue, Aug 10, 2004 at 11:40:28AM +0200, Jigal van Hemert wrote: How efficient is InnoDB with searching in such sets? Will it use an index or must it perform a full table search? Are there alternatives which are more efficient regarding search speed? Sets result in table scans if they're the only condition in WHERE clause. Until MySQL has a way of indexes them, you're stuck. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
