Returning Resultsets from Stored Routines
Hi All, I want to restrict all direct access to tables and allow users access only through the stored procedures. However, I haven't seen any explicit mention in the docs that one can return a resultset/recordset from a stored routine - that's what I can do using MS-SQL Server. If this cannot be done then the security benefit of limiting access only to the stored routines cannot be achieved. I will very much appreciate your help, comments and advice. -- Thanks in advance, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I don't understand why SCSI is preferred.
* - For example: We faced a NASTY problem using AMD 64-bit CPUs + SATA + Linux where I/O on the system (the WHOLE system, not JUST the SATA spindles -- network, PATA, USB, EVERYTHING) would suddenly come to a grinding halt (or very nearly halted) randomly when the SATA subsystem was under heavy load. It required a LOT of trial-and-error kernel adjustments to find a configuration that did not suffer this problem. we have the same problem here. what did you do to solve this problem? i guess we need to trial-and-error our own kernel configuration depending on our hardware but what parameters did you changed? i'm very thankful about any help ... we have NO idea what's wrong :) best regards chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table specific privileges
MySQL - 4.0.18-standard-log How do you revoke all privileges from a user for one table in a database, and still maintain the existing privileges for the other tables? For example, I have these tables: Email Logbook Sales_tax Sessions Transactions Users Orders_A Orders_B Lets say I have two users, user_A and user_B Currently, both users have select, insert, update, and delete on all tables. I want to totally block user_A from touching Orders_B and totally block user_B from touching Orders_A Knowing how to do this the SQL way would help, ultimately, I have to show a client how to do this in phpMyAdmin, so if anyone knows how to do it in there, that would be nice as well. Thanks. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com 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: Table specific privileges
Sean, As http://dev.mysql.com/doc/refman/5.0/en/revoke.html states, REVOKE ALL ON `database`.`table` FROM 'user'@'hostname'; MySQL - 4.0.18-standard-log How do you revoke all privileges from a user for one table in a database, and still maintain the existing privileges for the other tables? For example, I have these tables: Email Logbook Sales_tax Sessions Transactions Users Orders_A Orders_B Lets say I have two users, user_A and user_B Currently, both users have select, insert, update, and delete on all tables. I want to totally block user_A from touching Orders_B and totally block user_B from touching Orders_A Knowing how to do this the SQL way would help, ultimately, I have to show a client how to do this in phpMyAdmin, so if anyone knows how to do it in there, that would be nice as well. Thanks. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com 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] __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- Original Message -- FROM: Scott Haneda [EMAIL PROTECTED] TO:MySql mysql@lists.mysql.com DATE: Wed, 12 Jul 2006 23:49:41 -0700 SUBJECT: Table specific privileges MySQL - 4.0.18-standard-log How do you revoke all privileges from a user for one table in a database, and still maintain the existing privileges for the other tables? For example, I have these tables: Email Logbook Sales_tax Sessions Transactions Users Orders_A Orders_B Lets say I have two users, user_A and user_B Currently, both users have select, insert, update, and delete on all tables. I want to totally block user_A from touching Orders_B and totally block user_B from touching Orders_A Knowing how to do this the SQL way would help, ultimately, I have to show a client how to do this in phpMyAdmin, so if anyone knows how to do it in there, that would be nice as well. Thanks. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com 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] __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
quickie for a non-blonde!
If I have a table primary key id | code| name 1 | ABC | company 1 name 2 | ABC | company 1 name 3 | ABC | new company 1 name 4 | ABC | new company 1 name 5 | DEF| company 2 name 6 | DEF| company 2 name 7 | DEF| new company 2 name 8 | DEF| new company 2 name (the company is changing name every now and then and i need to extract their latest name) how can i get back 4 | ABC | new company 1 name 8 | DEF| new company 2 name -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: quickie for a non-blonde!
Assuming that the lastest ID is the same as the current name - maybe something like : select * from sometab t1 where id=( select max(id) from sometab t2 where t1.code=t2.code ) On Thursday 13 July 2006 10:16, Helen M Hudson wrote: If I have a table primary key id | code| name 1 | ABC | company 1 name 2 | ABC | company 1 name 3 | ABC | new company 1 name 4 | ABC | new company 1 name 5 | DEF| company 2 name 6 | DEF| company 2 name 7 | DEF| new company 2 name 8 | DEF| new company 2 name (the company is changing name every now and then and i need to extract their latest name) how can i get back 4 | ABC | new company 1 name 8 | DEF| new company 2 name -- Mike Aubury -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB buffer cache internals available?
Hi, in order to size the innodb buffer cache optimally it would really be beneficial to see what it is composed of and how much memory each of the components (also internal structures!) consumes. Is there any chance to get this information? Regards, Frank. -- Dr. Frank Ullrich, DBA Netzwerkadministration Heise Zeitschriften Verlag GmbH Co KG, Helstorfer Str. 7, D-30625 Hannover E-Mail: [EMAIL PROTECTED] Phone: +49 511 5352 587; FAX: +49 511 5352 538 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Comparison problem
Hello all
I just suscribed to this list.
I am a scientist working in Strasbourg (France) on problems related to gene
expressions.
I have a first question : I am comparing 2 tables, gene-length and
150genes.
mysql describe 150genes;
+--+--+--+-+-+---+
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-+---+
| Spot_Id | varchar(8) | YES | | NULL| |
| Bank_name| varchar(8) | YES | | NULL| |
-
| Chimeric_Cluster_IDs | varchar(100) | YES | MUL | NULL| |
+--+--+--+-+-+---+
13 rows in set (0.00 sec)
mysql describe gene_length;
++-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
++-+--+-+-+---+
| Chr_Name | varchar(2) | YES | | NULL| |
| Start | bigint(20) unsigned | YES | | NULL| |
| End| bigint(20) unsigned | YES | | NULL| |
| Band | varchar(4) | YES | | NULL| |
| Unigene_ID | varchar(16) | YES | | NULL| |
++-+--+-+-+---+
5 rows in set (0.06 sec)
The 150genes table has several fields, Chimeric_Cluster_IDs contains a ill
formatted list of accession codes (AC) like Mm.128512|Mm.371574Mm.128512.
The 150genes table contains several informations such as chromosome names
(field Chr_name) with the correspnding AC (field Unigene_ID).
To see which AC listed in Chimeric_Cluster_IDs.150genes exist in
Unigene_ID.gene_length and retrieve the correspondig chromosome name I use a
command like :
mysql Select Chr_Name, Chimeric_Cluster_IDs, Unigene_ID from 150genes,
gene_length where Chimeric_Cluster_IDs like concat('%', Unigene_ID, '|%') or
Chimeric_Cluster_IDs like concat('%', Unigene_ID, 'M%') or
Chimeric_Cluster_IDs like concat('%', Unigene_ID) order by Chr_Name+0 limit
0,2\G
*** 1. row ***
Chr_Name: X
Chimeric_Cluster_IDs: Mm.128512|Mm.371574Mm.128512|Mm.371574Mm.128512|
Mm.371574Mm.128512|Mm.371574
Unigene_ID: Mm.128512
*** 2. row ***
Chr_Name: 1
Chimeric_Cluster_IDs: Mm.246952|Mm.30837
Unigene_ID: Mm.246952
2 rows in set (2.50 sec)
But how to list all AC in Chimeric_Cluster_IDs _ABSENT_ from the gene_length
table, this one for instance :
mysql select Chr_Name, Unigene_ID from gene_length where
Unigene_ID='Mm.371574';
Empty set (0.03 sec)
Any idea ?
A last question : are there structures like if...then, for...next, while etc.
in MySQL ?
Thank you in advance
Jean-Claude
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
which is better long rows in table or two short row tables
Dear Friends, I was to create a site with quite some heavy mySQL database. I wanted to know which is better longer rows in a table or two short rows tables. When compared in terms of speed etc. Pl. help me , with this question and any other tip you may find can be useful to me. Thanks, Abhishek jain
Re: Comparison problem
Jean-Claude
But how to list all AC in Chimeric_Cluster_IDs _ABSENT_ from the gene_length
table, this one for instance :
mysql select Chr_Name, Unigene_ID from gene_length where
Unigene_ID='Mm.371574';
Any idea ?
That is called an exclusion join. To get at it, you need to adopt
explicit join syntax instead of comma join syntax:
SELECT
Chr_Name, Chimeric_Cluster_IDs, Unigene_ID
FROM 150genes AS g
LEFT JOIN gene_length AS l
ON (Chimeric_Cluster_IDs LIKE concat('%', Unigene_ID, '|%')
OR Chimeric_Cluster_IDs LIKE concat('%', Unigene_ID, 'M%')
OR Chimeric_Cluster_IDs LIKE concat('%', Unigene_ID)
)
WHERE l.unigene_id IS NULL
ORDER BY Chr_Name+0
LIMIT 0,2
A last question : are there structures like if...then,
for...next, while etc. in MySQL ?
IF ... THEN and CASE .. are available within SELECT arguments. Control
flow constructs like FOR... and WHILE... are available only in stored
routines.
PB
-
Garaud Jean-Claude wrote:
Hello all
I just suscribed to this list.
I am a scientist working in Strasbourg (France) on problems related to gene
expressions.
I have a first question : I am comparing 2 tables, gene-length and
150genes.
mysql describe 150genes;
+--+--+--+-+-+---+
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-+---+
| Spot_Id | varchar(8) | YES | | NULL| |
| Bank_name| varchar(8) | YES | | NULL| |
-
| Chimeric_Cluster_IDs | varchar(100) | YES | MUL | NULL| |
+--+--+--+-+-+---+
13 rows in set (0.00 sec)
mysql describe gene_length;
++-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
++-+--+-+-+---+
| Chr_Name | varchar(2) | YES | | NULL| |
| Start | bigint(20) unsigned | YES | | NULL| |
| End| bigint(20) unsigned | YES | | NULL| |
| Band | varchar(4) | YES | | NULL| |
| Unigene_ID | varchar(16) | YES | | NULL| |
++-+--+-+-+---+
5 rows in set (0.06 sec)
The 150genes table has several fields, Chimeric_Cluster_IDs contains a ill
formatted list of accession codes (AC) like Mm.128512|Mm.371574Mm.128512.
The 150genes table contains several informations such as chromosome names
(field Chr_name) with the correspnding AC (field Unigene_ID).
To see which AC listed in Chimeric_Cluster_IDs.150genes exist in
Unigene_ID.gene_length and retrieve the correspondig chromosome name I use a
command like :
mysql Select Chr_Name, Chimeric_Cluster_IDs, Unigene_ID from 150genes,
gene_length where Chimeric_Cluster_IDs like concat('%', Unigene_ID, '|%') or
Chimeric_Cluster_IDs like concat('%', Unigene_ID, 'M%') or
Chimeric_Cluster_IDs like concat('%', Unigene_ID) order by Chr_Name+0 limit
0,2\G
*** 1. row ***
Chr_Name: X
Chimeric_Cluster_IDs: Mm.128512|Mm.371574Mm.128512|Mm.371574Mm.128512|
Mm.371574Mm.128512|Mm.371574
Unigene_ID: Mm.128512
*** 2. row ***
Chr_Name: 1
Chimeric_Cluster_IDs: Mm.246952|Mm.30837
Unigene_ID: Mm.246952
2 rows in set (2.50 sec)
But how to list all AC in Chimeric_Cluster_IDs _ABSENT_ from the gene_length
table, this one for instance :
mysql select Chr_Name, Unigene_ID from gene_length where
Unigene_ID='Mm.371574';
Empty set (0.03 sec)
Any idea ?
A last question : are there structures like if...then, for...next, while etc.
in MySQL ?
Thank you in advance
Jean-Claude
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.9.10/386 - Release Date: 7/12/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Date Problems
I have just started with MYSQL and am building a racing site using PHP. I have Date, Course, Time, Horse, Odds, Result as my fields and the database is poulated with the data. I'm having problems with the formatting of data when it is output. Problem 1. I want to display the date in the format dd/mm/. I know you can use the DATE_FORMAT command but I can't seem to get it to work. Can anyone give me the exact script I need to write. Problem 2. I also want to display the results in the last 7 days in descending order by date. I can't seem to work out a way of doing that either. I'm a newb so please be gentle! :-) -- View this message in context: http://www.nabble.com/Date-Problems-tf1937078.html#a5307385 Sent from the MySQL - General forum at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: which is better long rows in table or two short row tables
Not sure what your question is, but remember you can use indexes to speed up access to rows. Thus usually the less fields you need to seach in the better, avoid OR searches across fields. - Original Message - From: abhishek jain [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, July 13, 2006 7:54 AM Subject: which is better long rows in table or two short row tables Dear Friends, I was to create a site with quite some heavy mySQL database. I wanted to know which is better longer rows in a table or two short rows tables. When compared in terms of speed etc. Pl. help me , with this question and any other tip you may find can be useful to me. Thanks, Abhishek jain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: which is better long rows in table or two short row tables
At 08:54 AM 7/13/2006, abhishek jain wrote: Dear Friends, I was to create a site with quite some heavy mySQL database. I wanted to know which is better longer rows in a table or two short rows tables. When compared in terms of speed etc. Pl. help me , with this question and any other tip you may find can be useful to me. Thanks, Abhishek jain Properly denormalized data with appropriate use of indexes. Google for relational data denormalise. Without knowing your data, whether the application is heavy on INSERTs and UPDATEs, or if it is mostly SELECTs, it is impossible to answer your question. Regards - Miles Thompson -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.1.394 / Virus Database: 268.9.10/387 - Release Date: 7/12/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search optimization
Thanks for your respond.
Here is the CREATE TABLE:
CREATE TABLE `results_1` (
`id` int(11) NOT NULL auto_increment,
`filename` varchar(255) collate cp1251_bulgarian_ci default NULL,
`fileext` varchar(10) collate cp1251_bulgarian_ci default NULL,
`username` varchar(16) collate cp1251_bulgarian_ci default NULL,
`filesize` float default NULL,
`date` datetime default NULL,
`is_dir` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `results_1_filesize` (`filesize`),
KEY `results_1_username` (`username`),
KEY `results_1_filename_1` (`filename`),
FULLTEXT KEY `results_1_filename` (`filename`,`username`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COLLATE=cp1251_bulgarian_ci
More details:
SELECT count(*) FROM results_1;
+--+
| count(*)
+--+
| 2399315
+--+
1 row in set (0.03 sec)
And some sample data:
INSERT INTO results_1 (id, filename, fileext, username, filesize,
date, is_dir) VALUES (857845, '04 - Iron Maiden - Iron Maiden.mp3',
'mp3', 'drawer', '7.6546e+06', '2006-03-05 01:28:00', 0);
INSERT INTO results_1 (id, filename, fileext, username, filesize,
date, is_dir) VALUES (857844, '03 - Iron Maiden - Fear Of The
Dark.mp3', 'mp3', 'drawer', '1.25829e+07', '2006-03-05 01:29:00', 0);
INSERT INTO results_1 (id, filename, fileext, username, filesize,
date, is_dir) VALUES (857843, '02 - Iron Maiden - Hallowed Be Thy
Name.mp3', 'mp3', 'drawer', '1.25829e+07', '2006-03-05 01:33:00', 0);
INSERT INTO results_1 (id, filename, fileext, username, filesize,
date, is_dir) VALUES (857842, '01 - Iron Maiden - No More Lies.mp3',
'mp3', 'drawer', '1.36315e+07', '2006-03-05 01:31:00', 0);
INSERT INTO results_1 (id, filename, fileext, username, filesize,
date, is_dir) VALUES (857841, '09 - Iron Maiden - Lord Of The
Flies.mp3', 'mp3', 'drawer', '8.70318e+06', '2006-03-05 01:24:00', 0);
And I logged some of the queries which executes for more than 20sec:
query timing: 28.8102879524
SELECT id, filename, filesize, date, MATCH (filename, username)
AGAINST('+*.mp3' IN BOOLEAN MODE) as coef FROM results_1 WHERE MATCH
(filename, username) AGAINST('+*.mp3' IN BOOLEAN MODE) 0 ORDER BY
coef, filename DESC LIMIT 0, 50
query timing: 36.2581338882
SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename,
username) AGAINST('+007' IN BOOLEAN MODE) 0
query timing: 31.0913391113
SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename,
username) AGAINST('+ACE-HIGH +MP3' IN BOOLEAN MODE) 0 UNION ALL
SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename,
username) AGAINST('+ACE-HIGHMP3' IN BOOLEAN MODE) 0
query timing: 32.1210138798
SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename,
username) AGAINST('+.pdf' IN BOOLEAN MODE) 0
query timing: 29.8846437931
SELECT id, filename, filesize, date, MATCH (filename, username)
AGAINST('+star +craft +mp3' IN BOOLEAN MODE) as coef FROM results_1
WHERE MATCH (filename, username) AGAINST('+star +craft +mp3' IN
BOOLEAN MODE) 0 UNION ALL SELECT id, filename, filesize, date,
MATCH (filename, username) AGAINST('+starcraft' IN BOOLEAN MODE) as
coef FROM results_1 WHERE MATCH (filename, username) AGAINST
('+starcraft' IN BOOLEAN MODE) 0 UNION ALL SELECT id, filename,
filesize, date, MATCH (filename, username) AGAINST('+starcraftmp3' IN
BOOLEAN MODE) as coef FROM results_1 WHERE MATCH (filename, username)
AGAINST('+starcraftmp3' IN BOOLEAN MODE) 0 ORDER BY coef,
filename LIMIT 0, 50
query timing: 28.6531541348
SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename,
username) AGAINST('+need +somebody +to +love' IN BOOLEAN MODE) 0
UNION ALL SELECT count(id) as all_records FROM results_1 WHERE MATCH
(filename, username) AGAINST('+needsomebody' IN BOOLEAN MODE) 0
UNION ALL SELECT count(id) as all_records FROM results_1 WHERE MATCH
(filename, username) AGAINST('+needsomebodyto' IN BOOLEAN MODE) 0
UNION ALL SELECT count(id) as all_records FROM results_1 WHERE MATCH
(filename, username) AGAINST('+needsomebodytolove' IN BOOLEAN MODE) 0
I've tried with the MEMORY storage engine, unfortunately it doesn't
support fulltext indexes.
Svilen Spasov
On Jul 12, 2006, at 7:05 PM, John Hicks wrote:
Svilen Spasov (Ancient Media) wrote:
Hello,
I have a website with a MySQL database and I have a table with ~2
millions row (usernames, filenames; ~120MB db data file and ~230MB
db index file) with I would like to search using the fulltext
indeces.
Unfortunately the server get loaded pretty much. It always stays
on 20 load average and often gets 50-60 load average.
I'm sure that this is because of slow mysql response (slow
queries) to the apache web server.
Can you give me some advice how to optimize the mysql server?
The server hardware is: 1GB RAM, 1.8GHz Celeron, 40GB HDD
(currently I have 512MB free memory)
Run a SHOW CREATE TABLE tablename on the table and post the
results
Re: Comparison problem
On Thursday 13 July 2006 14:19, Peter Brawley wrote:
Jean-Claude
But how to list all AC in Chimeric_Cluster_IDs _ABSENT_ from the
gene_length table, this one for instance :
mysql select Chr_Name, Unigene_ID from gene_length where
Unigene_ID='Mm.371574';
Any idea ?
That is called an exclusion join. To get at it, you need to adopt
explicit join syntax instead of comma join syntax:
SELECT
Chr_Name, Chimeric_Cluster_IDs, Unigene_ID
FROM 150genes AS g
LEFT JOIN gene_length AS l
ON (Chimeric_Cluster_IDs LIKE concat('%', Unigene_ID, '|%')
OR Chimeric_Cluster_IDs LIKE concat('%', Unigene_ID, 'M%')
OR Chimeric_Cluster_IDs LIKE concat('%', Unigene_ID)
)
WHERE l.unigene_id IS NULL
ORDER BY Chr_Name+0
LIMIT 0,2
I agree that LEFT JOIN is probably part of the solution. But the command you
suggest do not work, perhaps because several Chimeric_Cluster_IDs and
Unigene_ID are void or NULL. I want a list of AC from Chimeric_Cluster_IDs
that are not found in the gene_length table (Unigene_ID). It would perhaps be
necessary to extract each AC from Chimeric_Cluster_IDs and test each against
gene_length.Unigene_ID, but I really don't know how to do it.
A last question : are there structures like if...then,
for...next, while etc. in MySQL ?
IF ... THEN and CASE .. are available within SELECT arguments. Control
flow constructs like FOR... and WHILE... are available only in stored
routines.
Thank you, found :
http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html
Jean-Claude
PB
-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Problems
I put one select on each question. wizard007 [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] I have just started with MYSQL and am building a racing site using PHP. I have Date, Course, Time, Horse, Odds, Result as my fields and the database is poulated with the data. I'm having problems with the formatting of data when it is output. Problem 1. I want to display the date in the format dd/mm/. I know you can use the DATE_FORMAT command but I can't seem to get it to work. Can anyone give me the exact script I need to write. *** select date_format(Date,%d/%m/%Y) as Date from table; *** Problem 2. I also want to display the results in the last 7 days in descending order by date. *** select date_format(Date,%d/%m/%Y) as Date from table where datediff(date_format(now(),\%Y-%m-%d %H:%i:%s\),date_format(Date,\%Y-%m-%d %H:%i:%s\))=7 order by Date desc; *** I can't seem to work out a way of doing that either. I'm a newb so please be gentle! :-) -- View this message in context: http://www.nabble.com/Date-Problems-tf1937078.html#a5307385 Sent from the MySQL - General forum at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
3.23.58 - 5.0.22 upgrade
Hi - I know we've rolled this around the list a few times, but I was wondering if there's been any real development on the subject lately. I know half of you are saying Forget it... go with a newer version and get over it, and I'd really really like to, but i have a lot of data store under 3.23.x. The documentation says that you must follow 3 - 4.0 - 4.1 - 5.0 ... ..., and that's just what I'm about to do. I guess what I'm looking for here is some advice from people who have done it. If there's not an all-in-one silly stupid way to upgrade the data in this manner (i.e. with one fell swoop of some handy dandy application), would someone on an RPM-based system simply upgrade RPMs in sequential order as indicated, and hope that the data follows? Is this probably one of the more successful ways of doing this? I'm still exploring my options, but I think that's what I'm going to end up going with here - one step at a time. If you guys wouldn't mind terribly talking about this for a few more posts, I (and hopefully others) would greatly appreciate it. Thanks for the time -dant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table full when using file_per_table
I'm using innodb_file_per_table on a version 4.1.12 server on RH linux. At one point I got error 1114 The table 'X' is full. Aren't these tables autoextending? I don't think I reached a linux file size limit. A call to ulimit shows the file size is unlimited. The file was a couple of Gig. Anyone have any ideas? -- Phone: +1 (856) 638-6097 Address: Hewlett-Packard Company 6000 Irwin Rd. Mt. Laurel, NJ 08054 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Connecting to mysql 4.1 server with a 4.0 client
Afternoon, Apologies if this has been discussed before, I could not find a reference of it. So here we go :) Currently, I have a need to get a mysql 4.0 client to talk to a mysql 4.1 database. The error message I get is ERROR 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL client Unfortunately, this is not possible. Is there any way of getting the server to use the 4.0 method of authentication? Cheers -- Iain Conochie UNIX System Administrator UK COLT Telecom Units 9-13 Powergate Business Centre, Volt Avenue, London NW10 6PW Tel: +44 (0) 20 7947 1286 Tel: +44 (0) 78 1514 5493 Email: [EMAIL PROTECTED] www.colt.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparison problem
Jean-Claude
I agree that LEFT JOIN is probably part of the solution. But the command you
suggest do not work, perhaps because several Chimeric_Cluster_IDs and
Unigene_ID are void or NULL. I want a list of AC from Chimeric_Cluster_IDs
that are not found in the gene_length table (Unigene_ID). It would perhaps be
necessary to extract each AC from Chimeric_Cluster_IDs and test each against
gene_length.Unigene_ID, but I really don't know how to do it.
If there are rows with NULL keys in the gene_length table, and if you
cannot fix that problem, you will need the NOT EXISTS(...) version of
that query (examples at http://www.artfulsoftware.com/queries.php#29),
something like ...
SELECT ...
FROM 150genes AS g
WHERE NOT EXISTS(
SELECT chr_name
FROM gene_length AS l
WHERE g.Chimeric_Cluster_IDs LIKE CONCAT('%', l.Unigene_ID, '|%')
OR g.Chimeric_Cluster_IDs LIKE CONCAT('%', l.Unigene_ID, 'M%')
OR g.Chimeric_Cluster_IDs LIKE CONCAT('%', l.Unigene_ID)
)
ORDER BY ...
but be prepared for slowness. If your MySQL version is pre-4.1, or if
the subquery version is too slow, move the subquery to a separate
query, save it to a temp table (excluding the rows with NULLs), and
join 150genes to that.
All this will perform much better if you can restructure the tables to
permit equality comparisons rather than require LIKE comparisons, which
are extremely slow.
PB
-
Garaud Jean-Claude wrote:
On Thursday 13 July 2006 14:19, Peter Brawley wrote:
Jean-Claude
But how to list all AC in Chimeric_Cluster_IDs _ABSENT_ from the
gene_length table, this one for instance :
mysql select Chr_Name, Unigene_ID from gene_length where
Unigene_ID='Mm.371574';
Any idea ?
That is called an exclusion join. To get at it, you need to adopt
explicit join syntax instead of comma join syntax:
SELECT
Chr_Name, Chimeric_Cluster_IDs, Unigene_ID
FROM 150genes AS g
LEFT JOIN gene_length AS l
ON (Chimeric_Cluster_IDs LIKE concat('%', Unigene_ID, '|%')
OR Chimeric_Cluster_IDs LIKE concat('%', Unigene_ID, 'M%')
OR Chimeric_Cluster_IDs LIKE concat('%', Unigene_ID)
)
WHERE l.unigene_id IS NULL
ORDER BY Chr_Name+0
LIMIT 0,2
I agree that LEFT JOIN is probably part of the solution. But the command you
suggest do not work, perhaps because several Chimeric_Cluster_IDs and
Unigene_ID are void or NULL. I want a list of AC from Chimeric_Cluster_IDs
that are not found in the gene_length table (Unigene_ID). It would perhaps be
necessary to extract each AC from Chimeric_Cluster_IDs and test each against
gene_length.Unigene_ID, but I really don't know how to do it.
A last question : are there structures like if...then,
for...next, while etc. in MySQL ?
IF ... THEN and CASE .. are available within SELECT arguments. Control
flow constructs like FOR... and WHILE... are available only in stored
routines.
Thank you, found :
http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html
Jean-Claude
PB
-
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.10.0/388 - Release Date: 7/13/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connecting to mysql 4.1 server with a 4.0 client
Iain Conochie wrote: Afternoon, Apologies if this has been discussed before, I could not find a reference of it. So here we go :) Currently, I have a need to get a mysql 4.0 client to talk to a mysql 4.1 database. The error message I get is ERROR 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL client Unfortunately, this is not possible. Is there any way of getting the server to use the 4.0 method of authentication? Cheers Hi - This will help: http://dev.mysql.com/doc/refman/4.1/en/old-client.html Unfortunately I've been in this position a few times, as well. Thanks -dant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Problems
Hi, Just tried the date format script posted but it returns the following error: Parse error: parse error, unexpected '%' in /homepages/7/d123417448/htdocs/PayGo/results_14days.php on line 33 I used $query_Recordset1 = SELECT date_format(Date, %d/%m/%Y) as Date, Course, Time, Horse, Odds1, Odds2, `Result` FROM Results; -- View this message in context: http://www.nabble.com/Date-Problems-tf1937078.html#a5310495 Sent from the MySQL - General forum at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Problems
On 13 Jul 2006 at 8:27, wizard007 wrote: I used $query_Recordset1 = SELECT date_format(Date, %d/%m/%Y) as Date, Course, Time, Horse, Odds1, Odds2, `Result` FROM Results; Hi, That's a PHP error because you have a double quotes: %d/%m/%Y within double quotes: $query_Recordset1 = SELECT .. .FROM Results; You need to escape the quotes in your statement. Regards Ian -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Field name DESC
Hello, i want to know how i can create a table with the feild name desc ? when i do: CREATE TABLE bank ( name varchar(50) NOT NULL default '', desc varchar(50) NOT NULL default '', ) the desc is badely interpreted... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Field name DESC
DESC is a reserved word in MySQL: it is short for DESCENDING and is used to reverse the sort order in SELECTs. You an create a field with that name by enclosing it in backticks: `desc` whenever you need it. However, this would be regarded by many as very bad practice. It would be better to change the field name e.g. to descr or even description. Making the field name longer and more meaningful costs next to nothing. Alec Anthony [EMAIL PROTECTED] 13/07/2006 16:42 To mysql@lists.mysql.com cc Subject Field name DESC Hello, i want to know how i can create a table with the feild name desc ? when i do: CREATE TABLE bank ( name varchar(50) NOT NULL default '', desc varchar(50) NOT NULL default '', ) the desc is badely interpreted... -- 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: Field name DESC
How about desc_ ? Erik On Jul 13, 2006, at 8:42 AM, Anthony wrote: Hello, i want to know how i can create a table with the feild name desc ? when i do: CREATE TABLE bank ( name varchar(50) NOT NULL default '', desc varchar(50) NOT NULL default '', ) the desc is badely interpreted... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] tec.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connecting to mysql 4.1 server with a 4.0 client
Cheers Dan. Exactly what I needed :) Iain Dan Trainor wrote: Iain Conochie wrote: Afternoon, Apologies if this has been discussed before, I could not find a reference of it. So here we go :) Currently, I have a need to get a mysql 4.0 client to talk to a mysql 4.1 database. The error message I get is ERROR 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL client Unfortunately, this is not possible. Is there any way of getting the server to use the 4.0 method of authentication? Cheers Hi - This will help: http://dev.mysql.com/doc/refman/4.1/en/old-client.html Unfortunately I've been in this position a few times, as well. Thanks -dant -- Iain Conochie UNIX System Administrator UK COLT Telecom Units 9-13 Powergate Business Centre, Volt Avenue, London NW10 6PW Tel: +44 (0) 20 7947 1286 Tel: +44 (0) 78 1514 5493 Email: [EMAIL PROTECTED] www.colt.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Field name DESC
At 12:42 PM 7/13/2006, Anthony wrote: Hello, i want to know how i can create a table with the feild name desc ? when i do: CREATE TABLE bank ( name varchar(50) NOT NULL default '', desc varchar(50) NOT NULL default '', ) the desc is badely interpreted... In a word - DON'T. DESC is a reserved word - if it is an abbreviation for description, use descr, if for descending, try dscnd. Use of reserved words for field names is bad practice and is guaranteed to cause grief in the future. Cheers - Miles Thompson -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.1.394 / Virus Database: 268.9.10/387 - Release Date: 7/12/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re[2]: Field name DESC
I'd have to agree with Alec here (over Erik's response). There's no technical reason, surely, to use 'desc' instead of 'description' - people always seem afraid of using non-abbreviated fieldnames / function names / variable names. Whilst it may add a little more to your typing, it adds so much to the making your system more understandable. Code is immediately readable, the DB schema is more obvious, result: the whole system is improved. /2 pennies' worth --Rob DESC is a reserved word in MySQL: it is short for DESCENDING and is used to reverse the sort order in SELECTs. You an create a field with that name by enclosing it in backticks: `desc` whenever you need it. However, this would be regarded by many as very bad practice. It would be better to change the field name e.g. to descr or even description. Making the field name longer and more meaningful costs next to nothing. Alec Anthony [EMAIL PROTECTED] 13/07/2006 16:42 To mysql@lists.mysql.com cc Subject Field name DESC Hello, i want to know how i can create a table with the feild name desc ? when i do: CREATE TABLE bank ( name varchar(50) NOT NULL default '', desc varchar(50) NOT NULL default '', ) the desc is badely interpreted... -- 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] __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- Original Message -- FROM: [EMAIL PROTECTED] TO:[EMAIL PROTECTED] DATE: Thu, 13 Jul 2006 17:01:10 +0100 SUBJECT: Re: Field name DESC DESC is a reserved word in MySQL: it is short for DESCENDING and is used to reverse the sort order in SELECTs. You an create a field with that name by enclosing it in backticks: `desc` whenever you need it. However, this would be regarded by many as very bad practice. It would be better to change the field name e.g. to descr or even description. Making the field name longer and more meaningful costs next to nothing. Alec Anthony [EMAIL PROTECTED] 13/07/2006 16:42 To mysql@lists.mysql.com cc Subject Field name DESC Hello, i want to know how i can create a table with the feild name desc ? when i do: CREATE TABLE bank ( name varchar(50) NOT NULL default '', desc varchar(50) NOT NULL default '', ) the desc is badely interpreted... -- 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] __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 3.23.58 - 5.0.22 upgrade
I recently upgraded from 3.23.58 - 4.1.20 without any hickups. I simply dumped my databases with mysqldump on the 3.23.58 installation and imported them again from inside the mysql client by using the source command. Dan Trainor wrote: Hi - I know we've rolled this around the list a few times, but I was wondering if there's been any real development on the subject lately. I know half of you are saying Forget it... go with a newer version and get over it, and I'd really really like to, but i have a lot of data store under 3.23.x. The documentation says that you must follow 3 - 4.0 - 4.1 - 5.0 ... ..., and that's just what I'm about to do. I guess what I'm looking for here is some advice from people who have done it. If there's not an all-in-one silly stupid way to upgrade the data in this manner (i.e. with one fell swoop of some handy dandy application), would someone on an RPM-based system simply upgrade RPMs in sequential order as indicated, and hope that the data follows? Is this probably one of the more successful ways of doing this? I'm still exploring my options, but I think that's what I'm going to end up going with here - one step at a time. If you guys wouldn't mind terribly talking about this for a few more posts, I (and hopefully others) would greatly appreciate it. Thanks for the time -dant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: transaction isolation level
Apologies for a late reply.
1) The query that tries to insert the invalid entry into Table2 fails.
Therefore, if you have 3 separate queries as in the first case, the
last one fails, but the first 2 are successful. In the second case,
they're all in one query, and if one fails, they all fail.
For optimization purposes, MySQL doesn't turn a query in extended
insert syntax into multiple queries. The whole point of extended
insert is that it batch processes inserts faster than individual
inserst.
2) Sure, there are plenty of ways. Look up IF(), user variables, and
most importanly, ROLLBACK. Basically, you want to find out if the
inserts were successful, and if any one insert wasn't successful, you
rollback your transaction. Your example never actually has a decision
point where you decide whether or not to commit or rollback.
3) There is no way to figure out which value to be inserted made an error.
On 6/14/06, Konrad Baginski [EMAIL PROTECTED] wrote:
Hi.
I have a few questions regarding the transaction levels in mysql 5.0.20
using InnoDB tables.
we are trying to populate two tables in the two following ways, we thought
that they would be equivalent, apparently they are not.
have a look at the following (questions last).
FIRST METHOD:
create database test10;
use test10;
DROP TABLE IF EXISTS Table2;
DROP TABLE IF EXISTS Table1;
CREATE TABLE Table1 (
id BIGINT NOT NULL AUTO_INCREMENT,
logid VARCHAR(32) NULL,
PRIMARY KEY(id),
UNIQUE KEY log_id_key(logid)
)ENGINE=InnoDB;
DROP TABLE IF EXISTS Table2;
CREATE TABLE Table2 (
id BIGINT NOT NULL AUTO_INCREMENT,
table1id BIGINT,
PRIMARY KEY(id),
FOREIGN KEY (table1id) REFERENCES Table1(id) ON DELETE CASCADE
)ENGINE=InnoDB;
START TRANSACTION;
INSERT INTO Table1(logid) VALUES('1');
INSERT INTO Table1(logid) VALUES('2');
COMMIT;
START TRANSACTION;
INSERT INTO Table2(table1id) VALUES('1');
INSERT INTO Table2(table1id) VALUES('2');
INSERT INTO Table2(table1id) VALUES('3');
COMMIT;
select * from Table1; select * from Table2;
++---+
| id | logid |
++---+
| 1 | 1 |
| 2 | 2 |
++---+
2 rows in set (0.00 sec)
++--+
| id | table1id |
++--+
| 1 | 1|
| 2 | 2|
++--+
2 rows in set (0.00 sec)
### END FIRST METHOD ###
SECOND METHOD:
create database test10;
use test10;
DROP TABLE IF EXISTS Table2;
DROP TABLE IF EXISTS Table1;
CREATE TABLE Table1 (
id BIGINT NOT NULL AUTO_INCREMENT,
logid VARCHAR(32) NULL,
PRIMARY KEY(id),
UNIQUE KEY log_id_key(logid)
)ENGINE=InnoDB;
DROP TABLE IF EXISTS Table2;
CREATE TABLE Table2 (
id BIGINT NOT NULL AUTO_INCREMENT,
table1id BIGINT,
PRIMARY KEY(id),
FOREIGN KEY (table1id) REFERENCES Table1(id) ON DELETE CASCADE
)ENGINE=InnoDB;
START TRANSACTION;
INSERT INTO Table1(logid) VALUES('1'), ('2');
COMMIT;
START TRANSACTION;
INSERT INTO Table2(table1id) VALUES('1'), ('2'), ('3');
COMMIT;
select * from Table1; select * from Table2;
++---+
| id | logid |
++---+
| 1 | 1 |
| 2 | 2 |
++---+
2 rows in set (0.00 sec)
Empty set (0.00 sec)
### END SECOND METHOD ###
Questions
1. Why are the two ways of adding rows not equivalent, after all, they both
happen in a trancation?
2. Is there some way to make both of them either add the two first rows to
Table2 or not to add any row?
3. If we look at the second method to insert values, how can i find out
exacly which of the values made an error?
(in this case, the third value has no matching row in Table1).
/konrad baginski
--
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: Warnings while trying to restore database
(apologies for the late reply). 1) A warning doesn't stop the rest of the script from running. Temporary tables go away at the end of the session, and aren't visible to any other session, so the server not being able to handle those commands is no big deal. 2) I'm using MySQL 5.0.19-standard-log on linux and I cannot reproduce your warning -- I put the create temporary table statements into a file, and ran it on the OS commandline (using mysql file) as well as on the MySQL commandline (using source file;). In both cases, the script ran just fine, no errors or warnings. -Sheeri On 6/15/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I'm trying to restore a MySQL database in v5.0 (that minor number is in the teens, I don't have it at hand). I get a bunch of warnings like: Warning: Do not know how to handle this statement at line 28: CREATE TEMPORARY TABLE `CHARACTER_SETS` ( `CHARACTER_SET_NAME` varchar(64) NOT NULL default '', `DEFAULT_COLLATE_NAME` varchar(64) NOT NULL default '', `DESCRIPTION` varchar(60) NOT NULL default '', `MAXLEN` bigint(3) NOT NULL default '0' ) ENGINE=MEMORY DEFAULT CHARSET=utf8; Ignoring this statement. Please file a bug-report including the statement if this statement should be recognized. Warning: Do not know how to handle this statement at line 86: CREATE TEMPORARY TABLE `COLLATIONS` ( `COLLATION_NAME` varchar(64) NOT NULL default '', `CHARACTER_SET_NAME` varchar(64) NOT NULL default '', `ID` bigint(11) NOT NULL default '0', `IS_DEFAULT` varchar(3) NOT NULL default '', `IS_COMPILED` varchar(3) NOT NULL default '', `SORTLEN` bigint(3) NOT NULL default '0' ) ENGINE=MEMORY DEFAULT CHARSET=utf8; Ignoring this statement. Please file a bug-report including the statement if this statement should be recognized. I've searched the list archive and the bug database without finding a clue. What's this about? How do I work around it? -- 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]
Vertical Tab problem
We recently exported data from Filemaker then imported into mySQL 4.0.22. The data shows up in mySQL queries just fine. But when we use our PHP based Web App it shows little boxes in the data. The little box is a Vertical Tab that Filemaker uses. The Ascii is 11 or 0xb or sometimes known as \v. What I am trying to do is a replace of the little boxes with a space or something. I can not get the query to find the records with the Vertical Tab in it. I tried: select * from accounts where billing_address_street LIKE %\v% That returns everything with the letter v in it. Any ideas would be great. Erik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: intended behavior of host %
Better late than never I believe the reason is because (at least on unix) if you connect to a database on the same machine (using -h localhost or just omitting the host) it will use the socket -- and therefore saying host=localhost is like saying if they come from the unix socket, where as saying host=IP or host=name is like saying if they come from the port and match the host. It's extremely frustrating; in a heterogenous OS environment it can be even worse. Machines with more than one A record don't always show up the same way when they're connecting, so you can't just specify host=IP or host=name, but have to specify something like host=xxx.xxx.% or host=%.domain.com (or both, since I have no idea what controls whether or not the connection is seen as coming from ip or host -- I believe it's OS specific). It stinks, but we can wait until LDAP authentication comes alongsee http://dev.mysql.com/tech-resources/faq.html#sec3 SEC 3. Does MySQL 5.0 have built-in Authentication against LDAP directories? No. Support for external authentication methods is on the MySQL roadmap as a rolling feature. This means that it is not a flagship feature, but will be implemented, development time permitting. Specific customer demand may change this scheduling. -Sheeri On 6/20/06, John Bishop [EMAIL PROTECTED] wrote: Is there a reason that '%' doesn't match 'localhost'? I recently spent more time than I care to admit tearing my hair out over this while setting up authorization for a user. I've looked at the link that James provided, which does at least mention this inconsistency, but it doesn't seem to give a reason for it. Anyone? James Barros wrote: Never mind. I'll just go RTFM and quit bugging you guys on list with stuff answered plain as day in http://dev.mysql.com/doc/refman/5.1/en/adding-users.html Sorry. I'll go caffeinate myself before asking more stupid questions. On Jun 14, 2006, at 11:55 AM, James Barros wrote: Hey guys, I'm running mysql 5.1.9 and I've got a user who's mysql.user host is set to % and can log in from any domain except localhost. if I change to localhost, and flush privileges they can log in. Is this intended behavior, or should % be inclusive of localhost? -- John Bishop -- [EMAIL PROTECTED] Lone Star Internet -- +1 512 708-8006 -- http://lone-star.net -- 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]
Random Hangs, Linux AMD 64, 5.0.22 AB Binaries
Hi,
Having a most odd problem with random crashes, that appear to be some
kind of deadlocking or blocked process/thread occuring within mysqld
(a wild guess, not any type of assertion).
mysql v4.0.24, ~350Mb DB, running in production for months, no
problems under fairly heavy daily use from multiple users.
Upgraded to 5.0.22 [1], re-importing the data from mysqldump files.
Periodically, a client process will lock up, while holding a lock on
the table. Separate clients can connect and issue queries, but not on
the table that is locked by the hung process. Accessing the locked
table in question hangs the clients, which never appear to return.
Just for the record, killing the client(s) doesn't release the lock.
First observed on our production machine [2]. We took a snapshot, by:
* stopping mysqld
* enabled the query log, copied the db files from /var/lib/mysql
* restarted mysqld, waited for the problem to re-occur,
* stopped mysqld, copied all the files to one side, then downgraded to v4.0.24.
On our dev box [3], we
* installed the v5 mysqld from the same deb package (and later from
MySQL AB binaries, see [4])
* copied the /var/lib/mysql files in that we took from our production box
* parsed the mysql query log with perl, and re-applied the statements
(1 DBI connection per user, as shown in query log, queries applied
in query log order.
DBI does set autocommit=1 on each connection)
Doing this:
* We can reproduce the crash
* It typically always crashes at the same point
(Doing a simple insert into one of a couple of a fairly simple MyISAM tables)
* Sometimes it doesn't crash at that point, but does eventually, while
doing an insert.
I can reduce it to about 40 queries that will crash it 90% of the
time. However, by running some innocent/any query in a separate
command line client immediately after restarting (with reset files in
/var/lib/mysql) and before running the query log commands, the crash
typically won't occur until a lot more queries have run.
Removing a couple of the earlier select queries (40) causes the
lockup not to happen until later on.
We recompiled with the --with-debug and have a couple of the trace
files from when it crashed (it appears to crash less often with the
debug-enabled mysqld). I'm not really up to reading the tracefiles
and making any sense out of them. The last few lines of both do
differ, but the last line is always:
process_alarm: info: sig: 14 active alarms: X
where X might be 1..4 typically.
When this occurs, in almost all cases:
mysql show processlist;
...
| 13 | wwwuser | localhost | our_db | Query | 6 | update | INSERT
INTO `` (`field_1`, `field_2`, `field_3`, `field_4`) VALUES
('', '1 |
...
Occasionally we instead see:
| 266 | wwwuser | localhost | our_db | Query | 29 | Locked | INSERT
INTO `` (`field_1`, `field_2`, `field_3`, `field_4`, `field_5`,
`field_6`, `da |
The time (6 and 29 in the above) would increase, but everything else
remained as shown. It would never complete. Any other client trying
to access that table would be blocked indefinitely. Any further
queries on the relevant table would show up in processlist as Locked
Next, we downloaded the MySQL AB binary [4]. Exactly the same behaviour.
I'm stumped, and wondering if anyone has some ideas on how to proceed?
Thanks,
Matt.
[1] We started from the Debian package for version 5.0.22-2bpo1 found here:
deb-src http://www.backports.org/debian sarge-backports main
and compiled it with --with-openssl
[2] Production : Linux production 2.6.8-11-amd64-generic #1 Wed Jun 1
00:42:47 CEST 2005 x86_64 GNU/Linux
AMD Opteron(tm) Processor 244, 4Gb RAM
Running Debian Sarge (stable), including libc6 2.3.2.ds1-22
[3] Development: Linux dev 2.6.15-1-amd64-k8-smp #2 SMP Tue Mar 7
21:00:29 UTC 2006 x86_64 GNU/Linux
AMD Athlon(tm) 64 X2 Dual Core Processor 4400+, 4Gb RAM
Running Debian Sarge (stable), including libc6 2.3.2.ds1-22
[4]
http://dev.mysql.com/get/Downloads/MySQL-5.0/mysql-standard-5.0.22-linux-x86_64-glibc23.tar.gz
We stopped the debian packaged version (/etc/init.d/mysql stop)
and ran this instead.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random Hangs, Linux AMD 64, 5.0.22 AB Binaries
Sounds like the well-known problem with Debian stable's glibc on x86_64: http://hashmysql.org/index.php?title=Opteron_HOWTO#pthread_rwlock_wrlock_hang_with_nptl Jim Winstead MySQL Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to RESET @@session.error_count system variable
As a session variable, it resets when you open a new session. -Sheeri On 6/21/06, Tony_10ph [EMAIL PROTECTED] wrote: Hello guys... I have stored procedures and I want when a calling program call my stored procedure it will return a response that the stored procedure execute successfuly or return an error code to the calling program. I found a @@session.error_count system variable but if theres an error this variable store error count occur. My problem is I can't RESET the value of the @@session.error_count variable coz it says this is a read only variable. Or is there any way functions I can use to handle error?. Tony -- View this message in context: http://www.nabble.com/How-to-RESET-%40%40session.error_count-system-variable-t1827840.html#a4986142 Sent from the MySQL - General forum at Nabble.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: Importing large data sets
On 6/22/06, Scott Haneda [EMAIL PROTECTED] wrote: I have two chunks of data to import, one is in this format: 01001 - AGAWAM, MA,01001,0,0,291,249,0,42.070206,-72.622739 Where it is comma sep and partially quoted The other is in this format 99502 ANCHORAGE, AK,256,265,1424,1962,1131,528,643,6209,99502,61.096163, -150.093943 Where everything is in quotes and comma sep Can someone tell me, how to rapidly import all this data into a table. Check out mysqlimport: http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html particularly the --fields-optionally-enclosed-by and --fields-terminated-by options. I'm sure it's too late for you, but you do NOT need to edit things in a text editor. -Sheeri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table specific privileges
I keep getting the error: ERROR 1147: There is no such grant defined for user 'username' on host 'host.example.com' on table 'orders_npfs' Sean, As http://dev.mysql.com/doc/refman/5.0/en/revoke.html states, REVOKE ALL ON `database`.`table` FROM 'user'@'hostname'; MySQL - 4.0.18-standard-log How do you revoke all privileges from a user for one table in a database, and still maintain the existing privileges for the other tables? For example, I have these tables: Email Logbook Sales_tax Sessions Transactions Users Orders_A Orders_B Lets say I have two users, user_A and user_B Currently, both users have select, insert, update, and delete on all tables. I want to totally block user_A from touching Orders_B and totally block user_B from touching Orders_A Knowing how to do this the SQL way would help, ultimately, I have to show a client how to do this in phpMyAdmin, so if anyone knows how to do it in there, that would be nice as well. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com 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: create view and insert into problems
create view v_authornames as
select authorid, CONCAT(lastname,',',firstname)
from t_authors;
Or replace the middle term in the CONCAT function to whatever you want
to separate it -- ie, ' ' for a space, or just
CONCAT(lastname,firstname) to get output KritzerSheeri.
-Sheeri
On 6/25/06, Andreas Bauer [EMAIL PROTECTED] wrote:
Hello NG,
I have two tables in my mysql database
created with phpmyadmin:
t_authors:
1 authorid (primary key, auto_increment)
2 lastname
3 firstname
And a table named t_books, fields inside:
t_books:
1 bookid (primary key, auto_increment)
2 authorid (Typ:index, reference to authorid from t_authors done
with phpmyadmin)
3 title
4 subtitle
Now I want to create a view from t_authors, so that the fields
of lastname and firstname are one field with
the value inside: lastname, firstname:
create view v_authornames as
select authorid, lastname || ', ' || firstname
from t_authors;
But this view created only an empty field named
lastname ||', ' firstname. How can I join this two
fields so that I get one and this value?
There is another problem of me inserting values sequently in
the two tables:
insert into t_authors (lastname, firstname)
values ('Meyers', 'Scott');
insert into t_books (authorid, title, subtitle)
values ('1'), 'Effektiv C++ Programmieren',
'50 Wege zur Verbesserung Ihrer Programme und Entwuerfe');
insert into t_books (authorid, title, subtitle)
values ('1'), 'Mehr Effektiv C++ Programmieren',
'35 neue Wege zur Verbesserung Ihrer Entwuerfe und Programme');
insert into t_authors (lastname, firstname)
values ('Schlossnagle', 'George');
insert into t_books (authorid, title, subtitle)
values ('1'), 'Advanced PHP Programming', 'A practical guide');
The problem is the authorid of t_books: which value should I take for
authorid of table t_books.
If I took '1', the referenced value of the authorid from t_authors was not
taken from the authorid from t_books, but always the value '1'?
If I took '0' or others I get errors.
In phpmyamin I set the reference to t_authors.authorid in the
t_books.authorid field.
Best regards and many thanks
Andreas
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB database Lost
Hopefully you're not still having this problem. I don't use phpMyAdmin, but I know that it allows you to run repair table to try to fix a table after a crash. Does that work? -Sheeri On 6/25/06, Khaled Jouda [EMAIL PROTECTED] wrote: Hello, I am having a problem with one of my MySQL databases, the server was crashed, and then all InnoDB tables seem to be empty, when I click any innoDB table name in PhpMyAdmin i get the following error: #1016 - Can't open file: 'forums.ibd' (errno: 1) when I click the database name, I get a list of the tables, where PhpMyAdmin writes in use under the following columns: Records,Type,Collation, and size Do you have any idea why such a thing happens, and what can be done to restore the database? thanks Khaled -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I don't understand why SCSI is preferred.
We're using Opterons, Linux 2.6.x, and a SiL (Silicon Image) SATA chipset whose particular model number I don't have in front of me. After MUCH MUCH MUCH trial and error we've discovered that: 1) 2.6.16 substantially alleviates the problem but doesn't eliminate it. 2) There is a 3Ware card that's MUCH better in this regard. Personally, I'm not a fan of 3Ware, having lost a RAID array due in no small part to a BUG in their firmware (whose existence they knew about but, naturally, refused to acknowledge until we presented them with proof that it had to be a bug...) but you can control for such variables... -JF On Jul 12, 2006, at 11:48 PM, living liquid | Christian Meisinger wrote: * - For example: We faced a NASTY problem using AMD 64-bit CPUs + SATA + Linux where I/O on the system (the WHOLE system, not JUST the SATA spindles -- network, PATA, USB, EVERYTHING) would suddenly come to a grinding halt (or very nearly halted) randomly when the SATA subsystem was under heavy load. It required a LOT of trial-and-error kernel adjustments to find a configuration that did not suffer this problem. we have the same problem here. what did you do to solve this problem? i guess we need to trial-and-error our own kernel configuration depending on our hardware but what parameters did you changed? i'm very thankful about any help ... we have NO idea what's wrong :) best regards chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 3.23.58 - 5.0.22 upgrade
Dan, I wouldn't bother with the intermediate steps (4.0, 4.1) . Waste of time (fine products, but you say you want to go to 5.0). I'd go direct to 5.0, using mysqldump'd data as Martin did. If you're all-MyISAM then it might also work to simply upgrade your binaries and keep your data files, though you'll want to watch for the fix permissions script and also be mindful of the password changes that came about with 4.1. Dan On 7/13/06, Martin Jespersen [EMAIL PROTECTED] wrote: I recently upgraded from 3.23.58 - 4.1.20 without any hickups. I simply dumped my databases with mysqldump on the 3.23.58 installation and imported them again from inside the mysql client by using the source command. Dan Trainor wrote: Hi - I know we've rolled this around the list a few times, but I was wondering if there's been any real development on the subject lately. I know half of you are saying Forget it... go with a newer version and get over it, and I'd really really like to, but i have a lot of data store under 3.23.x. The documentation says that you must follow 3 - 4.0 - 4.1 - 5.0 ... ..., and that's just what I'm about to do. I guess what I'm looking for here is some advice from people who have done it. If there's not an all-in-one silly stupid way to upgrade the data in this manner (i.e. with one fell swoop of some handy dandy application), would someone on an RPM-based system simply upgrade RPMs in sequential order as indicated, and hope that the data follows? Is this probably one of the more successful ways of doing this? I'm still exploring my options, but I think that's what I'm going to end up going with here - one step at a time. If you guys wouldn't mind terribly talking about this for a few more posts, I (and hopefully others) would greatly appreciate it. Thanks for the time -dant -- 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]
does Query_time in slow log include time for client to receive answer set?
I have some strange entries in my slow logs whose timestamps corrolate to an event we are investigating: Query_time in the 2-4 range Lock_time: 0 for ALL entries Rows_sent in the single to low double-digits Rows_examined in the low to mid hundreds the question is whether these are cause or effect? I am thinking the this reflects the clients becoming unresponsive and MySQL is saying this is how long it took to get it off the dock as opposed to this is how long it took to get it to shipping. is this correst? if not what are plausible explanations for a short burst of these queries which execute hundreds of thousands of time/day to suddeny slow log (w/such low Rows_% #s) across multiple MySQL nodes then just as suddenly stop?
Re: 3.23.58 - 5.0.22 upgrade
Dan has a very good point, be mindfull of the changed password algorithm, that actually was a bit of a bother to me since i have tons of users defined in my grant tables. Dan Buettner wrote: Dan, I wouldn't bother with the intermediate steps (4.0, 4.1) . Waste of time (fine products, but you say you want to go to 5.0). I'd go direct to 5.0, using mysqldump'd data as Martin did. If you're all-MyISAM then it might also work to simply upgrade your binaries and keep your data files, though you'll want to watch for the fix permissions script and also be mindful of the password changes that came about with 4.1. Dan On 7/13/06, Martin Jespersen [EMAIL PROTECTED] wrote: I recently upgraded from 3.23.58 - 4.1.20 without any hickups. I simply dumped my databases with mysqldump on the 3.23.58 installation and imported them again from inside the mysql client by using the source command. Dan Trainor wrote: Hi - I know we've rolled this around the list a few times, but I was wondering if there's been any real development on the subject lately. I know half of you are saying Forget it... go with a newer version and get over it, and I'd really really like to, but i have a lot of data store under 3.23.x. The documentation says that you must follow 3 - 4.0 - 4.1 - 5.0 ... ..., and that's just what I'm about to do. I guess what I'm looking for here is some advice from people who have done it. If there's not an all-in-one silly stupid way to upgrade the data in this manner (i.e. with one fell swoop of some handy dandy application), would someone on an RPM-based system simply upgrade RPMs in sequential order as indicated, and hope that the data follows? Is this probably one of the more successful ways of doing this? I'm still exploring my options, but I think that's what I'm going to end up going with here - one step at a time. If you guys wouldn't mind terribly talking about this for a few more posts, I (and hopefully others) would greatly appreciate it. Thanks for the time -dant -- 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: 3.23.58 - 5.0.22 upgrade
Martin Jespersen wrote: Dan has a very good point, be mindfull of the changed password algorithm, that actually was a bit of a bother to me since i have tons of users defined in my grant tables. Dan Buettner wrote: Dan, I wouldn't bother with the intermediate steps (4.0, 4.1) . Waste of time (fine products, but you say you want to go to 5.0). I'd go direct to 5.0, using mysqldump'd data as Martin did. If you're all-MyISAM then it might also work to simply upgrade your binaries and keep your data files, though you'll want to watch for the fix permissions script and also be mindful of the password changes that came about with 4.1. Dan On 7/13/06, Martin Jespersen [EMAIL PROTECTED] wrote: I recently upgraded from 3.23.58 - 4.1.20 without any hickups. I simply dumped my databases with mysqldump on the 3.23.58 installation and imported them again from inside the mysql client by using the source command. Dan Trainor wrote: Hi - I know we've rolled this around the list a few times, but I was wondering if there's been any real development on the subject lately. I know half of you are saying Forget it... go with a newer version and get over it, and I'd really really like to, but i have a lot of data store under 3.23.x. The documentation says that you must follow 3 - 4.0 - 4.1 - 5.0 ... ..., and that's just what I'm about to do. I guess what I'm looking for here is some advice from people who have done it. If there's not an all-in-one silly stupid way to upgrade the data in this manner (i.e. with one fell swoop of some handy dandy application), would someone on an RPM-based system simply upgrade RPMs in sequential order as indicated, and hope that the data follows? Is this probably one of the more successful ways of doing this? I'm still exploring my options, but I think that's what I'm going to end up going with here - one step at a time. If you guys wouldn't mind terribly talking about this for a few more posts, I (and hopefully others) would greatly appreciate it. Thanks for the time -dant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Hi - Thanks, both of you, for your replies. Like I said, I've experienced problems in the past with a mysqldump between major version changes. Maybe it's just my luck, or maybe it's a hit-or-miss from big jumps, I'm not entirely sure. Right now, most of my data is InnoDB. As I believe Martin pointed out (or maybe not... someone from [EMAIL PROTECTED]), be wary of transactions, which I can overwrite using an import switch. I was able to actually convert to 5.0.22 directly from 3.23.58, and I'm having my devs mow through the data to see if it all works as designed. They are having trouble with passwords, but they're fixing that on a case-by-case basis, which is fine with us. I'll keep you guys posted regardless. It seems that this subject pops up a lot with conflicting answers, so I'd like to give everyone else as much information about the process as I can. Thanks again, much appreciated. -dant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table specific privileges (BUMP)
Sorry to push this back out to the list, I am stumped, and the docs are not leading me to an answer. One users reply was close, and I had tried it, but it generates an error, which is also posted in this thread. Thanks everyone, original message follows: MySQL - 4.0.18-standard-log How do you revoke all privileges from a user for one table in a database, and still maintain the existing privileges for the other tables? For example, I have these tables: Email Logbook Sales_tax Sessions Transactions Users Orders_A Orders_B Lets say I have two users, user_A and user_B Currently, both users have select, insert, update, and delete on all tables. I want to totally block user_A from touching Orders_B and totally block user_B from touching Orders_A Knowing how to do this the SQL way would help, ultimately, I have to show a client how to do this in phpMyAdmin, so if anyone knows how to do it in there, that would be nice as well. Thanks. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com 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: Table specific privileges (BUMP)
Hi Scott,
One would think that you should be able to accomplish what you are
looking for by changing rows in table - tables_priv in mysql
database. And using flush privileges when you done.
mysql describe tables_priv;
+-+-
--+--+-+---+
---+
| Field | Type
| Null | Key | Default | Extra |
+-+-
--+--+-+---+
---+
| Host| char(60)
| | PRI | | |
| Db | char(64)
| | PRI | | |
| User| char(16)
| | PRI | | |
| Table_name | char(64)
| | PRI | | |
| Grantor | char(77)
| | MUL | | |
| Timestamp | timestamp
| YES | | CURRENT_TIMESTAMP | |
| Table_priv |
set('Select','Insert','Update','Delete','Create','Drop','Grant','Referen
ces','Index','Alter') | | | | |
| Column_priv | set('Select','Insert','Update','References')
| | | | |
+-+-
--+--+-+---+
---+
8 rows in set (0.00 sec)
mysql
See http://dev.mysql.com/doc/refman/4.1/en/request-access.html
Paragraph begins with words After determining the database-specific
privileges granted by the db and host table entries
Regards,
Mikhail Berman
-Original Message-
From: Scott Haneda [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 13, 2006 4:13 PM
To: MySql
Subject: Table specific privileges (BUMP)
Sorry to push this back out to the list, I am stumped, and the docs are
not leading me to an answer.
One users reply was close, and I had tried it, but it generates an
error, which is also posted in this thread. Thanks everyone, original
message
follows:
MySQL - 4.0.18-standard-log
How do you revoke all privileges from a user for one table in a
database, and still maintain the existing privileges for the other
tables?
For example, I have these tables:
Email
Logbook
Sales_tax
Sessions
Transactions
Users
Orders_A
Orders_B
Lets say I have two users, user_A and user_B Currently, both users have
select, insert, update, and delete on all tables.
I want to totally block user_A from touching Orders_B and totally block
user_B from touching Orders_A
Knowing how to do this the SQL way would help, ultimately, I have to
show a client how to do this in phpMyAdmin, so if anyone knows how to do
it in there, that would be nice as well.
Thanks.
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com 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: Table specific privileges (BUMP)
Hi Scott, One would think that you should be able to accomplish what you are looking for by changing rows in table - tables_priv in mysql database. And using flush privileges when you done. So does this mean my database privs of select, insert, update, and delete supercede any table specific ones? I am trying to not have to add new table privs every time I add a new table. That would be kind of a hassle. My goal is to have a set of tables, that are accessible to 10 mysql users, there will then be 10 additional tables in that database, each user is allowed to access only a certain one. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com 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]
I ran of disk space running optimize on table.... Now I can not open data.MYI
Now, I when I do a desc on data table, I get the error ERROR 1016 (HY000): Can't open file: 'data.MYI' (errorno: 144). As I recall I ran: optimize data; The Optimize command did not complete the error as I recall was /var filesystem full. Running the following commands: ls data.* data.MYD data.MYIdata.frm acid# ls -last snort/data.* 2 -rw-rw 1 mysql mysql1024 Jul 12 14:23 data.MYI 4543712 -rw-rw 1 mysql mysql 4650467248 Jul 12 14:09 data.MYD 10 -rw-rw 1 mysql mysql8632 Jun 28 20:54 data.frm Is there anything I can do to get this table back? r/Raymond
Re: I don't understand why SCSI is preferred.
At 03:45 PM 7/12/2006, Jon Frisby wrote: This REALLY should be an academic concern. Either you have a system that can tolerate the failure of a drive, or you do not. The frequency of failure rates is pretty much irrelevant: You can train incredibly non-technical (inexpensive) people to respond to a pager and hot-swap a bad drive. If you are in the position where the typical failure-rate of a class of drive is of concern to you then either: A) You have a different problem causing all your drives to fail ultra-fast (heat, electrical noise, etc) or B) You haven't adequately designed your storage subsystem. It all depends how valuable your uptime is. If you double or triple the time between hard disk failures, most people would pay extra for that so they buy SCSI drive. You wouldn't take your family car and race in the Indy 500, would you? After a few laps at 150 mph (if you can get it going that fast), it will seize up, so you go into the pit stop and what? Get another family car and drive that? And keep doing that until you finish the race? Down time is extremely expensive and embarrassing. Just talk to the guys at FastMail who has had 2 outages even with hardware raid in place. Recovery doesn't always work as smoothly as you think it should. Save yourself the headache, and just set up a RAID10 PATA/SATA array with a hot spare. Not sure if Linux/FreeBSD/et al support hot-swap of drives when using software RAID, but if it does then you don't even need to spend a few hundred bucks on a RAID controller. Software RAID? Are you serious? No way! Mike -JF On Jul 12, 2006, at 12:11 PM, mos wrote: At 12:42 PM 7/12/2006, you wrote: On Tuesday 11 July 2006 19:26, mos wrote: SCSI drives are also designed to run 24/7 whereas IDE drives are more likely to fail if used on a busy server. This used to be the case. But there are SATA drives out there now being made for enterprise class, 100% duty cycle operations. See, for example, http://www.westerndigital.com/en/products/Products.asp? DriveID=238Language=en No, I am not affiliated with WD, just had good experience with these drives. 1.2 Million Hours MTBF at 100% duty cycle and a five year warranty. Not bad. That's good to hear, but MTBF is really a pie in the sky estimate. I had an expensive HP tape drive that had something like 20,000 hr MTBF. Both of my units failed at under 70 hours. HP's estimate was power on hours (unit powered on and doing nothing), and did NOT include hours when the tape was in motion. Sheesh. To get the MTBF estimate, the manufacturer will power on 100 drives (or more) and time to see when the first one fails. If it fails in 1000 hours, then the MTBF is 100x1000hrs or 100,000 hours. This is far from being accurate because as we all know, the older the drive, the more likely it is to fail. (Especially after the warranty period has expired, failure rate is quite highg). I am hoping the newer SATA II drives will provide SCSI performance at a reasonable price. It would be interesting to see if anyone has polled ISP's to see what they're using. I know they charge more (or at least they used to) for SCSI drives if you are renting a server from them. It would be interesting to see what their failure rate is on IDE vs SCSI vs SATA. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I ran of disk space running optimize on table.... Now I can not open data.MYI
Hmmm. Doesn't look pretty, Raymond! You've got 4.6GB of data and just 1K of indexes now. Step ZERO - make a backup copy of the files you have NOW, in case any repair operations you try make things worse. On the bright side, your data file (.MYD) should sitll be intact - it's just the index file (.MYI) that's hosed. At a high level, you're going to want to rebuild that index file. How - is a little trickier. See http://dev.mysql.com/doc/refman/5.0/en/repair.html I expect you'll want to look at the section titled Stage 3: Difficult repair because the first part of your index file is in fact destroyed. Good luck! Dan On 7/13/06, Jacob, Raymond A Jr [EMAIL PROTECTED] wrote: Now, I when I do a desc on data table, I get the error ERROR 1016 (HY000): Can't open file: 'data.MYI' (errorno: 144). As I recall I ran: optimize data; The Optimize command did not complete the error as I recall was /var filesystem full. Running the following commands: ls data.* data.MYD data.MYIdata.frm acid# ls -last snort/data.* 2 -rw-rw 1 mysql mysql1024 Jul 12 14:23 data.MYI 4543712 -rw-rw 1 mysql mysql 4650467248 Jul 12 14:09 data.MYD 10 -rw-rw 1 mysql mysql8632 Jun 28 20:54 data.frm Is there anything I can do to get this table back? r/Raymond -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I don't understand why SCSI is preferred.
On Jul 13, 2006, at 3:03 PM, mos wrote: At 03:45 PM 7/12/2006, Jon Frisby wrote: This REALLY should be an academic concern. Either you have a system that can tolerate the failure of a drive, or you do not. The frequency of failure rates is pretty much irrelevant: You can train incredibly non-technical (inexpensive) people to respond to a pager and hot-swap a bad drive. If you are in the position where the typical failure-rate of a class of drive is of concern to you then either: A) You have a different problem causing all your drives to fail ultra-fast (heat, electrical noise, etc) or B) You haven't adequately designed your storage subsystem. It all depends how valuable your uptime is. If you double or triple the time between hard disk failures, most people would pay extra for that so they buy SCSI drive. You wouldn't take your family car and race in the Indy 500, would you? After a few laps at 150 mph (if you can get it going that fast), it will seize up, so you go into the pit stop and what? Get another family car and drive that? And keep doing that until you finish the race? Down time is extremely expensive and embarrassing. Just talk to the guys at FastMail who has had 2 outages even with hardware raid in place. Recovery doesn't always work as smoothly as you think it should. Again: Either your disk sub-system can TOLERATE (read: CONTINUE OPERATING IN THE FACE OF) a drive failure, or it cannot. If you can't hot-stop a dead drive, your system can't tolerate the failure of a drive. Your analogy is flawed. The fact that companies like Google are running with incredibly good uptimes while using cheap, commodity hardware (including IDE drives!) demonstrates it. SCSI drives WILL NOT improve your uptime by a factor of 2x or 3x. Using a hot-swappable disk subsystem, and having hot-spares WILL. Designing your systems without needless single points of failure WILL. Software RAID? Are you serious? No way! You make a compelling case for your position, but I'm afraid I still disagree with you. *cough* If you're using RAID10, or other forms of RAID that don't involve computing a checksum (and the write hole that accompanies it), there's little need for hardware support. It won't make things dramatically faster unless you spend a ton of money on cache -- in which case you should seriously consider a SAN for the myriad other benefits it provides. The reliability introduced by hardware RAID with battery backups is pretty negligible if you're doing your I/O right (I.E. you've made sure your drives aren't lying when they say a write has completed AND you're using fsync -- which MySQL does). -JF -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Returning Resultsets from Stored Routines
Asif Lodhi wrote: Hi All, I want to restrict all direct access to tables and allow users access only through the stored procedures. However, I haven't seen any explicit mention in the docs that one can return a resultset/recordset from a stored routine - that's what I can do using MS-SQL Server. If this cannot be done then the security benefit of limiting access only to the stored routines cannot be achieved. I will very much appreciate your help, comments and advice. How are your clients connecting to MySQL? If you're writing your own apps using the C libraries, then you are in luck. If you are using MyODBC, then you will have to wait until the v5 connector is complete. If you are using DBD::mysql, then I'd be interested to hear how it works out - I'm about to start testing that in the next couple of days. I don't know the status of the JDBC or PHP drivers. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: I ran of disk space running optimize on table.... Now I can not open data.MYI
Thank you, raymond -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Thursday, July 13, 2006 18:07 To: Jacob, Raymond A Jr Cc: mysql@lists.mysql.com Subject: Re: I ran of disk space running optimize on table Now I can not open data.MYI Hmmm. Doesn't look pretty, Raymond! You've got 4.6GB of data and just 1K of indexes now. Step ZERO - make a backup copy of the files you have NOW, in case any repair operations you try make things worse. On the bright side, your data file (.MYD) should sitll be intact - it's just the index file (.MYI) that's hosed. At a high level, you're going to want to rebuild that index file. How - is a little trickier. See http://dev.mysql.com/doc/refman/5.0/en/repair.html I expect you'll want to look at the section titled Stage 3: Difficult repair because the first part of your index file is in fact destroyed. Good luck! Dan On 7/13/06, Jacob, Raymond A Jr [EMAIL PROTECTED] wrote: Now, I when I do a desc on data table, I get the error ERROR 1016 (HY000): Can't open file: 'data.MYI' (errorno: 144). As I recall I ran: optimize data; The Optimize command did not complete the error as I recall was /var filesystem full. Running the following commands: ls data.* data.MYD data.MYIdata.frm acid# ls -last snort/data.* 2 -rw-rw 1 mysql mysql1024 Jul 12 14:23 data.MYI 4543712 -rw-rw 1 mysql mysql 4650467248 Jul 12 14:09 data.MYD 10 -rw-rw 1 mysql mysql8632 Jun 28 20:54 data.frm Is there anything I can do to get this table back? r/Raymond -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
