store file in MySQL database
Hi, I want to store a .txt file in a table in a MySQL database, typically what is the best type of data type/ column to use for this. The .txt will not exceed 100K. Thanks Joe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: store file in MySQL database
joe collins [EMAIL PROTECTED] wrote: I want to store a .txt file in a table in a MySQL database, typically what is the best type of data type/ column to use for this. The .txt will not exceed 100K. You need MEDIUMTEXT/MEDIUMBLOB column types: http://dev.mysql.com/doc/mysql/en/BLOB.html http://dev.mysql.com/doc/mysql/en/Storage_requirements.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]
Storing a space
Is there a way to force an invisible space character to be stored at the END of a string in a column (either VARCHAR or CHAR), so that when it is retrieved the space at the end is not cut off? theString + space or even, theString + space + space + space, etc. Currently, I can only get the string back as theString Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Storing a space
Hi John, I *think* VARCHAR is *supposed* to work that way, but doesn't in MySQL. So you'll have to use TINYTEXT. Its storage requirements are the same as VARCHAR(255) and it behaves the same way, except for, I think, 3 things: 1) the trailing space thing, obviously; 2) it can't have a DEFAULT value; and 3) you can't index the whole column -- but you can INDEX (col(255)), which has the same effect. :-) Hope that helps. Matt - Original Message - From: John Mistler Sent: Friday, April 30, 2004 3:54 AM Subject: Storing a space Is there a way to force an invisible space character to be stored at the END of a string in a column (either VARCHAR or CHAR), so that when it is retrieved the space at the end is not cut off? theString + space or even, theString + space + space + space, etc. Currently, I can only get the string back as theString Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication and Grants nightmare
Hi, I'm running mysql-max 4.0.18 on two servers on separate networks, master is A and slave is B. The problem is that if I don't grant on the master the full range of privileges to the slave the replication doesn't even start. Even worse is the fact that grants seems to be totally managed at random, as you can see: (A and B obviously are fake names). mysql A GRANT REPLICATION SLAVE, REPLICATION CLIENT, SUPER, RELOAD on *.* TO 'replica'@'B' IDENTIFIED BY PASSWORD '715a443962d324cc'; OK mysql A show grants for replica@'B'; ++ | Grants for [EMAIL PROTECTED] | ++ | GRANT RELOAD ON *.* TO 'replica'@'B' IDENTIFIED BY PASSWORD '715a443962d324cc' WITH GRANT OPTION | ++ 1 row in set (0.00 sec) - I didn't grant (yet) any option, so why does it say 'WITH GRANT OPTION' ? - where have all the other privileges gone? they vanished mysql A revoke ALL PRIVILEGES on *.* from replica@'B'; # identified by 'RC_rpl!'; Query OK, 0 rows affected (0.00 sec) mysql A flush privileges; Query OK, 0 rows affected (0.00 sec) mysql A show grants for replica@'B'; +--+ | Grants for [EMAIL PROTECTED] | +--+ | GRANT SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 'replica'@'B' IDENTIFIED BY PASSWORD '715a443962d324cc' WITH GRANT OPTION | +--+ 1 row in set (0.00 sec) where did it take these rights from? I revoked them all Now the replication part: mysql A GRANT super, reload, replication client, replication slave ON *.* TO 'replica'@'B' IDENTIFIED BY PASSWORD '715a443962d324cc' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec) mysql A flush privileges; Query OK, 0 rows affected (0.00 sec) these are the logs on B: 040430 11:10:34 InnoDB: Started /usr/sbin/mysqld-max: ready for connections. Version: '4.0.18-Max-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 040430 11:10:34 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'db-bin.3321' at position 34997604 040430 11:10:34 While trying to obtain the list of slaves from the master 'A:3306', user 'replica' got the following error: 'Access denied. You need the REPLICATION SLAVE privilege for this operation' 040430 11:10:34 Slave I/O thread exiting, read up to log 'db-bin.3321', position 34997604 What is going wrong? P.S. the manual doesn't say that grants super, reload, replication client and replication slave can be used only on *.* and not on DB.*. Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error dropping databases [in InnoDb]
Michael, Marko has fixed this to 4.0.19. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Michael McTernan [EMAIL PROTECTED] To: Tom O'Neil [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, April 29, 2004 12:40 PM Subject: RE: Error dropping databases [in InnoDb] Hi, I have found this too. I think it is as a result of a change introduced in 4.0.18. From http://www.innodb.com/ibman.php#InnoDB.history : MySQL/InnoDB-4.0.18, February 13, 2004 - Do not allow dropping a table referenced by a FOREIGN KEY constraint, unless the user does SET FOREIGN_KEY_CHECKS=0. The error message here is somewhat misleading 'Cannot delete or update a parent row...', and must be changed in a future version 4.1.x. It's a good change, but I'm guessing that InnoDb is handling a drop database first as if all tables are being dropped in some arbirtary order and hence not respecting foreign keys. One solution is to disable foreign key checks before the drop, and then re-enable them afterwards, or alternatively explicitly drop all tables in the correct order before dropping the database. I wonder if there are any plans to fix this in future versions on InnoDb? Thanks, Mike -Original Message- From: Tom O'Neil [mailto:[EMAIL PROTECTED] Sent: 25 March 2004 22:07 To: [EMAIL PROTECTED] Subject: Error dropping databases I am trying to drop several InnoDB and am unable to do so. I login to the command line client and enter drop database [db_name]; and then get this error: ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails Why am I unable to drop these databases? Tom = -- 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: Error dropping databases [in InnoDb]
Many Thanks :) Mike -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: 30 April 2004 11:56 To: Michael McTernan; Tom O'Neil Cc: [EMAIL PROTECTED] Subject: Re: Error dropping databases [in InnoDb] Michael, Marko has fixed this to 4.0.19. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Michael McTernan [EMAIL PROTECTED] To: Tom O'Neil [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, April 29, 2004 12:40 PM Subject: RE: Error dropping databases [in InnoDb] Hi, I have found this too. I think it is as a result of a change introduced in 4.0.18. From http://www.innodb.com/ibman.php#InnoDB.history : MySQL/InnoDB-4.0.18, February 13, 2004 - Do not allow dropping a table referenced by a FOREIGN KEY constraint, unless the user does SET FOREIGN_KEY_CHECKS=0. The error message here is somewhat misleading 'Cannot delete or update a parent row...', and must be changed in a future version 4.1.x. It's a good change, but I'm guessing that InnoDb is handling a drop database first as if all tables are being dropped in some arbirtary order and hence not respecting foreign keys. One solution is to disable foreign key checks before the drop, and then re-enable them afterwards, or alternatively explicitly drop all tables in the correct order before dropping the database. I wonder if there are any plans to fix this in future versions on InnoDb? Thanks, Mike -Original Message- From: Tom O'Neil [mailto:[EMAIL PROTECTED] Sent: 25 March 2004 22:07 To: [EMAIL PROTECTED] Subject: Error dropping databases I am trying to drop several InnoDB and am unable to do so. I login to the command line client and enter drop database [db_name]; and then get this error: ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails Why am I unable to drop these databases? Tom = -- 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: Command for getting back an auto_increment field?
Leandro Melo [EMAIL PROTECTED] wrote: If it`s a InnoDB table :-? my case If you want to start auto_increment sequence from the beginning, you must recreate InnoDB table. --- Paul DuBois [EMAIL PROTECTED] escreveu: At 12:27 -0300 4/29/04, Leandro Melo wrote: Hi, i got a table wich its pk is an auto_increment field. I have 10 elements in this table, wich makes the pk_id field = 10. I inserted incorrectly anoter row in this table (the 11th) and imediately deleted it. Although, i'd like that the next time i insert a row in this table, it's index be still 11 (not 12 as mysql would do by default). Is there any fast command i can make to correct the table situation? If it's a MyISAM table, yes, although you might ask yourself whether there's really any need to do so. (Answer: nearly always not.) This statement will cause the next AUTO_INCREMENT value generated to be one more than the current maximum value in the column: ALTER TABLE tbl_name AUTO_INCREMENT = 1; -- 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]
Create a table from SHOW TABLE STATUS ?
I'm running 4.1.1a-alpha-max-nt using innodb tables with foreign keys . I know how to use SHOW TABLE STATUS to see the referential linkages in the COMMENT column. Supppose I am typing away in MySQL monitor: Q: Is there a way to create a table from the SHOW TABLE STATUS command ? Q: Does v5 have system views that are equivalent to SHOW xyz commands ? Thanks, Richard A. DeVenezia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create a table from SHOW TABLE STATUS ?
Richard A. DeVenezia [EMAIL PROTECTED] wrote: I'm running 4.1.1a-alpha-max-nt using innodb tables with foreign keys . I know how to use SHOW TABLE STATUS to see the referential linkages in the COMMENT column. Supppose I am typing away in MySQL monitor: Q: Is there a way to create a table from the SHOW TABLE STATUS command ? No, but you can use output of SHOW CREATE TABLE command. Q: Does v5 have system views that are equivalent to SHOW xyz commands ? Nope. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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: SQL SELECT HELP
[EMAIL PROTECTED] wrote: Sorry. My english is not so good. :( I try to explain. I have table1 : ID value -- 1 100 1 101 1 102 1 200 2 100 2 300--- 2 310 | 3 100 | | and table2: | | value | --- | 300 - The result of the query should be from IDs of table1 (In this case 1,3) . The ID 2 is not allowed, because the table2 is the exception table wich is containing the value 300. You need something like: SELECT DISTINCT t3.id FROM table2 t2 INNER JOIN table1 t1 ON t1.value=t2.value RIGHT JOIN table1 t3 ON t1.id=t3.id WHERE t1.id IS NULL; -- 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: Storing a space
At 1:54 -0700 4/30/04, John Mistler wrote: Is there a way to force an invisible space character to be stored at the END of a string in a column (either VARCHAR or CHAR), so that when it is retrieved the space at the end is not cut off? No. I suggest that you use one of the TEXT types instead, which are not subject to trailing space trimming. theString + space or even, theString + space + space + space, etc. Currently, I can only get the string back as theString Right. That's the documented behavior. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication and Grants nightmare
P.S. the manual doesn't say that grants super, reload, replication client and replication slave can be used only on *.* and not on DB.*. Each of those privileges is listed as an administrative privilege here: http://dev.mysql.com/doc/mysql/en/Privileges_provided.html There are not listed as database or table privileges. I'm not sure what it could mean for them to be database-specific, actually. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication and Grants nightmare
Alle Friday 30 April 2004 15:22, Victoria Reznichenko ha scritto: Hmm.. Your queries worked fine for me: ANT REPLICATION SLAVE, REPLICATION CLIENT, SUPER, RELOAD ON *.* TO 'replica'@'B' IDENTIFIED BY PASSWORD '715a443962d324cc'; Query OK, 0 rows affected (0.00 sec) mysql show grants for replica@'B'; +-- + | Grants for [EMAIL PROTECTED] || +-- + | GRANT RELOAD, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO | 'replica'@'B' IDENTIFIED BY PASSWORD '715a443962d324cc' | +-- + 1 row in set (0.01 sec) mysql revoke ALL PRIVILEGES ON *.* FROM replica@'B'; Query OK, 0 rows affected (0.00 sec) mysql flush privileges; Query OK, 0 rows affected (0.00 sec) mysql show grants for replica@'B'; +-- -+ | Grants for [EMAIL PROTECTED] | | +-- -+ | GRANT USAGE ON *.* TO 'replica'@'B' IDENTIFIED BY PASSWORD | '715a443962d324cc' | +-- -+ 1 row in set (0.00 sec) Did you have account for 'replica'@'B' before? neither other users with the same name nor other entries for the same host Are the above queries exact that you used? yes, except the host names Which OS do you use? the master is a Redhat 7.3 with Mysql-max 4.0.18 (binary rpms from www.mysql.com), the slave is a very old Cobalt 6.0 with Mysql-max 4.0.18 compiled from the .src.rpm. Thanks for your help -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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: Command for getting back an auto_increment field?
At 14:48 +0300 4/30/04, Egor Egorov wrote: Leandro Melo [EMAIL PROTECTED] wrote: If it`s a InnoDB table :-? my case If you want to start auto_increment sequence from the beginning, you must recreate InnoDB table. To add to this, I'll mention something else that is relevant in the context of InnoDB tables. It's not just record deletes that can cause a sequence number to become unused. If you generate an AUTO_INCREMENT value within a transaction but roll back that transaction, the AUTO_INCREMENT value also becomes unused. For example, if you have transactions A, B, and C that each generate an AUTO_INCREMENT value of 1, 2, and 3 in turn, but B rolls back, the values stored in the table are 1 and 3. There is now a gap in the sequence. I'm going to re-emphasize that there is almost never any reason to resequence an AUTO_INCREMENT column. I know that people like to keep sequence numbering nice and neat, but having gaps and unused numbers isn't going to make applications work any differently, and MySQL doesn't care one way or the other. In those rare cases when you *must* have an unbroken sequence, AUTO_INCREMENT may be the wrong approach anyway. --- Paul DuBois [EMAIL PROTECTED] escreveu: At 12:27 -0300 4/29/04, Leandro Melo wrote: Hi, i got a table wich its pk is an auto_increment field. I have 10 elements in this table, wich makes the pk_id field = 10. I inserted incorrectly anoter row in this table (the 11th) and imediately deleted it. Although, i'd like that the next time i insert a row in this table, it's index be still 11 (not 12 as mysql would do by default). Is there any fast command i can make to correct the table situation? If it's a MyISAM table, yes, although you might ask yourself whether there's really any need to do so. (Answer: nearly always not.) This statement will cause the next AUTO_INCREMENT value generated to be one more than the current maximum value in the column: ALTER TABLE tbl_name AUTO_INCREMENT = 1; -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication and Grants nightmare
Alle Friday 30 April 2004 15:51, hai scritto:
P.S. the manual doesn't say that grants super, reload, replication client
and replication slave can be used only on *.* and not on DB.*.
Each of those privileges is listed as an administrative privilege here:
http://dev.mysql.com/doc/mysql/en/Privileges_provided.html
There are not listed as database or table privileges.
I'm not sure what it could mean for them to be database-specific, actually.
so I should have columns Repl_client_priv and Repl_slave_priv ?
my mysql db doesn't have them:
CREATE DATABASE /*!32312 IF NOT EXISTS*/ mysql;
USE mysql;
--
-- Table structure for table `columns_priv`
--
CREATE TABLE columns_priv (
Host char(60) binary NOT NULL default '',
Db char(64) binary NOT NULL default '',
User char(16) binary NOT NULL default '',
Table_name char(64) binary NOT NULL default '',
Column_name char(64) binary NOT NULL default '',
Timestamp timestamp(14) NOT NULL,
Column_priv set('Select','Insert','Update','References') NOT NULL default
'',
PRIMARY KEY (Host,Db,User,Table_name,Column_name)
) TYPE=MyISAM COMMENT='Column privileges';
--
-- Table structure for table `db`
--
CREATE TABLE db (
Host char(60) binary NOT NULL default '',
Db char(64) binary NOT NULL default '',
User char(16) binary NOT NULL default '',
Select_priv enum('N','Y') NOT NULL default 'N',
Insert_priv enum('N','Y') NOT NULL default 'N',
Update_priv enum('N','Y') NOT NULL default 'N',
Delete_priv enum('N','Y') NOT NULL default 'N',
Create_priv enum('N','Y') NOT NULL default 'N',
Drop_priv enum('N','Y') NOT NULL default 'N',
Grant_priv enum('N','Y') NOT NULL default 'N',
References_priv enum('N','Y') NOT NULL default 'N',
Index_priv enum('N','Y') NOT NULL default 'N',
Alter_priv enum('N','Y') NOT NULL default 'N',
PRIMARY KEY (Host,Db,User),
KEY User (User)
) TYPE=MyISAM COMMENT='Database privileges';
--
-- Table structure for table `func`
--
CREATE TABLE func (
name char(64) binary NOT NULL default '',
ret tinyint(1) NOT NULL default '0',
dl char(128) NOT NULL default '',
type enum('function','aggregate') NOT NULL default 'function',
PRIMARY KEY (name)
) TYPE=MyISAM COMMENT='User defined functions';
--
-- Table structure for table `host`
--
CREATE TABLE host (
Host char(60) binary NOT NULL default '',
Db char(64) binary NOT NULL default '',
Select_priv enum('N','Y') NOT NULL default 'N',
Insert_priv enum('N','Y') NOT NULL default 'N',
Update_priv enum('N','Y') NOT NULL default 'N',
Delete_priv enum('N','Y') NOT NULL default 'N',
Create_priv enum('N','Y') NOT NULL default 'N',
Drop_priv enum('N','Y') NOT NULL default 'N',
Grant_priv enum('N','Y') NOT NULL default 'N',
References_priv enum('N','Y') NOT NULL default 'N',
Index_priv enum('N','Y') NOT NULL default 'N',
Alter_priv enum('N','Y') NOT NULL default 'N',
PRIMARY KEY (Host,Db)
) TYPE=MyISAM COMMENT='Host privileges; Merged with database privileges';
--
-- Table structure for table `tables_priv`
--
CREATE TABLE tables_priv (
Host char(60) binary NOT NULL default '',
Db char(64) binary NOT NULL default '',
User char(16) binary NOT NULL default '',
Table_name char(60) binary NOT NULL default '',
Grantor char(77) NOT NULL default '',
Timestamp timestamp(14) NOT NULL,
Table_priv
set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter')
NOT NULL default '',
Column_priv set('Select','Insert','Update','References') NOT NULL default
'',
PRIMARY KEY (Host,Db,User,Table_name),
KEY Grantor (Grantor)
) TYPE=MyISAM COMMENT='Table privileges';
--
-- Table structure for table `user`
--
CREATE TABLE user (
Host char(60) binary NOT NULL default '',
User char(16) binary NOT NULL default '',
Password char(16) binary NOT NULL default '',
Select_priv enum('N','Y') NOT NULL default 'N',
Insert_priv enum('N','Y') NOT NULL default 'N',
Update_priv enum('N','Y') NOT NULL default 'N',
Delete_priv enum('N','Y') NOT NULL default 'N',
Create_priv enum('N','Y') NOT NULL default 'N',
Drop_priv enum('N','Y') NOT NULL default 'N',
Reload_priv enum('N','Y') NOT NULL default 'N',
Shutdown_priv enum('N','Y') NOT NULL default 'N',
Process_priv enum('N','Y') NOT NULL default 'N',
File_priv enum('N','Y') NOT NULL default 'N',
Grant_priv enum('N','Y') NOT NULL default 'N',
References_priv enum('N','Y') NOT NULL default 'N',
Index_priv enum('N','Y') NOT NULL default 'N',
Alter_priv enum('N','Y') NOT NULL default 'N',
PRIMARY KEY (Host,User)
) TYPE=MyISAM COMMENT='Users and global privileges';
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT HELP
Hi, Is it possible to create a Select performing a math formula? For example: First I need to add two values come from the same table but from different records. The result will be divided from one number got from another table. Now, the new result will be added with another value got from another table creating the final result. Like this: ((value_from_record_1_from_table_A + value_from_record_15_from_table_A) / value_from_table_B ) + value_from_table_C Is this possible? Is there anyone who can help me to create this SELETC? Thanks. -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Case Sensitive on Windows
Hi, I am trying to synchronize two databases - the source is on a FreeBSD web server and the target is on our local Windows 2000 Server machine. I am trying to do this using Webyog (www.webyog.com) which tells Windows to sync with the source machine every hour. However, this is producing errors because the MySQL installation on the local machine isn't case sensitive, therefore it isn't recognizing the same tables. How can I rectify this, I can't find any documentation on the MySQL site regarding this...? Thanks for your help _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication and Grants nightmare
At 16:22 +0200 4/30/04, Nico Sabbi wrote:
Alle Friday 30 April 2004 15:51, hai scritto:
P.S. the manual doesn't say that grants super, reload, replication client
and replication slave can be used only on *.* and not on DB.*.
Each of those privileges is listed as an administrative privilege here:
http://dev.mysql.com/doc/mysql/en/Privileges_provided.html
There are not listed as database or table privileges.
I'm not sure what it could mean for them to be database-specific, actually.
so I should have columns Repl_client_priv and Repl_slave_priv ?
my mysql db doesn't have them:
Indeed you should (in the user table only), and several other columns
as well.
Perhaps that is a clue to what is happening. Did you upgrade from a
release older than 4.0.2 to a release 4.0.2 or newer at some point,
without running the mysql_fix_privilege_tables script? If so, then
please read this:
http://dev.mysql.com/doc/mysql/en/Upgrading-grant-tables.html
--
-- Table structure for table `user`
--
CREATE TABLE user (
Host char(60) binary NOT NULL default '',
User char(16) binary NOT NULL default '',
Password char(16) binary NOT NULL default '',
Select_priv enum('N','Y') NOT NULL default 'N',
Insert_priv enum('N','Y') NOT NULL default 'N',
Update_priv enum('N','Y') NOT NULL default 'N',
Delete_priv enum('N','Y') NOT NULL default 'N',
Create_priv enum('N','Y') NOT NULL default 'N',
Drop_priv enum('N','Y') NOT NULL default 'N',
Reload_priv enum('N','Y') NOT NULL default 'N',
Shutdown_priv enum('N','Y') NOT NULL default 'N',
Process_priv enum('N','Y') NOT NULL default 'N',
File_priv enum('N','Y') NOT NULL default 'N',
Grant_priv enum('N','Y') NOT NULL default 'N',
References_priv enum('N','Y') NOT NULL default 'N',
Index_priv enum('N','Y') NOT NULL default 'N',
Alter_priv enum('N','Y') NOT NULL default 'N',
PRIMARY KEY (Host,User)
) TYPE=MyISAM COMMENT='Users and global privileges';
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT HELP
Andre, have a look at JOIN. This can solve your problem. Thomas Spahni On Fri, 30 Apr 2004, Andre MATOS wrote: Is it possible to create a Select performing a math formula? For example: First I need to add two values come from the same table but from different records. The result will be divided from one number got from another table. Now, the new result will be added with another value got from another table creating the final result. Like this: ((value_from_record_1_from_table_A + value_from_record_15_from_table_A) / value_from_table_B ) + value_from_table_C Is this possible? Is there anyone who can help me to create this SELETC? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Case Sensitive on Windows
shaun thornburgh wrote: Hi, I am trying to synchronize two databases - the source is on a FreeBSD web server and the target is on our local Windows 2000 Server machine. I am trying to do this using Webyog (www.webyog.com) which tells Windows to sync with the source machine every hour. Side note: I don't know anything about webyog, but MySQL's built-in replication works so well, I'd recommend looking into using it. However, this is producing errors because the MySQL installation on the local machine isn't case sensitive, therefore it isn't recognizing the same tables. How can I rectify this, I can't find any documentation on the MySQL site regarding this...? Your answer may be here, in the online docs where MySQL case sensitivity is detailed. http://dev.mysql.com/doc/mysql/en/Name_case_sensitivity.html Thanks for your help HTH, Robert Taylor [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication and Grants nightmare
Alle Friday 30 April 2004 16:44, hai scritto: At 16:22 +0200 4/30/04, Nico Sabbi wrote: Alle Friday 30 April 2004 15:51, hai scritto: P.S. the manual doesn't say that grants super, reload, replication client and replication slave can be used only on *.* and not on DB.*. Each of those privileges is listed as an administrative privilege here: http://dev.mysql.com/doc/mysql/en/Privileges_provided.html There are not listed as database or table privileges. I'm not sure what it could mean for them to be database-specific, actually. so I should have columns Repl_client_priv and Repl_slave_priv ? my mysql db doesn't have them: Indeed you should (in the user table only), and several other columns as well. Perhaps that is a clue to what is happening. Did you upgrade from a release older than 4.0.2 to a release 4.0.2 or newer at some point, without running the mysql_fix_privilege_tables script? If so, then please read this: http://dev.mysql.com/doc/mysql/en/Upgrading-grant-tables.html done, it works correctly now. Thanks very much, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Case Sensitive on Windows
At 14:43 + 4/30/04, shaun thornburgh wrote: Hi, I am trying to synchronize two databases - the source is on a FreeBSD web server and the target is on our local Windows 2000 Server machine. I am trying to do this using Webyog (www.webyog.com) which tells Windows to sync with the source machine every hour. However, this is producing errors because the MySQL installation on the local machine isn't case sensitive, therefore it isn't recognizing the same tables. How can I rectify this, I can't find any documentation on the MySQL site regarding this...? Search for lower_case_table_names. However, first determine this: On the FreeBSD machine, do you have tables in a given database that differ only in lettercase. For example, do you have a table named ABC and a table named abc in the same database? If you do, then you'll have some definite difficulty replicating them to a machine that doesn't have case-sensitive filenames. You'll be better off renaming one of the tables, or perhaps making a different choice of slave server host. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT HELP
Andre MATOS wrote: Hi, Is it possible to create a Select performing a math formula? For example: First I need to add two values come from the same table but from different records. The result will be divided from one number got from another table. Now, the new result will be added with another value got from another table creating the final result. Like this: ((value_from_record_1_from_table_A + value_from_record_15_from_table_A) / value_from_table_B ) + value_from_table_C Not knowing what the criteria for selecting the different records from table_A (1 and 15), I'll forgo a join clause and just illustrate a simple alias with where clause: SELECT (( a1.value + a2.value ) / b.value ) + c.value AS final_result FROM table_A a1, table_A a2, table_B b, table_C c WHERE a1.key = 1 AND a2.key = 15 AND /* guessing here */ b.key = a1.key AND c.key = a2.key AND a1.key a2.key AND a2.key IS NOT NULL AND a1.key IS NOT NULL; That's not correct as I am guessing your actual criteria, etc., but it gives the idea. Can you be more specific on the criteria for relating (joining) tables A, records 1 and 15, with themselves and with tables B and C? Is this possible? Is there anyone who can help me to create this SELETC? Thanks. HTH, Robert Taylor [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database structure
Hello, I would like an advise on the following problem : I have a table of patients. Each patient can make different biological assessments. Each assessment is always decomposed into different laboratory tests. A laboratory test is made of a test number and two values coming from analysers. The schema is : Patients(#patient_nr,name,etc...) Assessment(#assessment_nr, #patient_nr, assessment_type, other usefull values). Assessment_types(assessment_type, labtest_nr) An assessment is composed of different tests, let's say assessment type 1 is composed of lab test 1,2,3,5 and assessment type 2 of lab test number 10 to 70. I have an assessment with 60 different lab tests (always the same). I have two ways for storing the values : 1 - a table with 120 columns for the two values. results(#assessment_nr, p10,d10, p11,d11, .,p70,d70). where 10 to 70 represents the lab test number. 2 - a table with 60 rows for one assessment : results(#assessment_nr, labtest_nr, p, d) where p and d are my two results. Here comes my question. Which of the two would you choose? The firsrt solution has the advantage of returning one single row for one complete assessment. If I have to make statistics, it is easy. But, if I have to modify the composition of an assessment (which occurs very rarely), I shall have to use an alter table instruction. As I have 4 different assessment types, I have to create five different tables, one per assessment type. The second solution is normalized and more elegant. But I am preoccupied by the size of the table. For one assessment, I'll store 60 rows with only two useful integers in it. And you must add the size of the index. With 25.000 assessments a year, it makes 1.500.000 rows with only 4 columns amoung them 2 only for the results and 2 for identification. I would like to store 10 years online, so 15.000.000 rows. What about the size of index ? Any advise ? I thank you in advance. Alain Reymond (I hope that it is clear enough with my bad English). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mac OSX MySQL Client 4.1.1
My Mac mysql client does not appear to work correctly. The DB is fine and setup working great but I cannot use that version of the mysql client. Right now I'm using the 4.0.18 client on my mac. Are there known bugs with the 4.1.1 Mac OSX client? Sam Peterson
5.0 stored procedures
The MySQL 5.0 implementation of stored procedures is appreciated, but skeletal. Not much of it works very well. It would be very helpful indeed if the development team could give us a hint on when improvements in SP implementation might be expected. TIA. PB
Re: Mac OSX MySQL Client 4.1.1
At 9:26 -0600 4/30/04, Sam Peterson wrote: My Mac mysql client does not appear to work correctly. The DB is fine and setup working great but I cannot use that version of the mysql client. Right now I'm using the 4.0.18 client on my mac. Are there known bugs with the 4.1.1 Mac OSX client? Yes: http://bugs.mysql.com/bug.php?id=2058 -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT HELP
Hi Robert, the criteria for the record_1 and record_15 is that both are in the same table, but in different records and to find each one it is necessary to perform a WHERE clause. Let's I give you the real example: My problem is while inserting a new record in my table named ScanQuantification, I will need to update another table named Scan, IF a value from the field ScanQuantificationValue from table ScanQuantification is equal or greater than a calculate value. The calculate value comes from this formula: ( ( A + B ) / 2 + C) where: A is a value find from the field ScanQuantificationValue from table ScanQuantification where the TimePoint = 8 B is a value find from the field ScanQuantificationValue from table ScanQuantification where the TimePoint = 9 C is a value find from the field TrialBaseValue from table Trial This is easy to do using the PHP language. However I will have different Trials and each one has different formula. That's why I want to put the SELECT to work for me. Thanks Andre On Fri, 30 Apr 2004, Robert J Taylor wrote: Andre MATOS wrote: Hi, Is it possible to create a Select performing a math formula? For example: First I need to add two values come from the same table but from different records. The result will be divided from one number got from another table. Now, the new result will be added with another value got from another table creating the final result. Like this: ((value_from_record_1_from_table_A + value_from_record_15_from_table_A) / value_from_table_B ) + value_from_table_C Not knowing what the criteria for selecting the different records from table_A (1 and 15), I'll forgo a join clause and just illustrate a simple alias with where clause: SELECT (( a1.value + a2.value ) / b.value ) + c.value AS final_result FROM table_A a1, table_A a2, table_B b, table_C c WHERE a1.key = 1 AND a2.key = 15 AND /* guessing here */ b.key = a1.key AND c.key = a2.key AND a1.key a2.key AND a2.key IS NOT NULL AND a1.key IS NOT NULL; That's not correct as I am guessing your actual criteria, etc., but it gives the idea. Can you be more specific on the criteria for relating (joining) tables A, records 1 and 15, with themselves and with tables B and C? Is this possible? Is there anyone who can help me to create this SELETC? Thanks. HTH, Robert Taylor [EMAIL PROTECTED] -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
One Mysql For loads and one for query
I have a Mysql db that is loaded with about 500,000 records a night using LOAD DATA INFILE. This goes on for a month then a new table is created and so on. Then data is then just queryed nothing else. To take some stress off of this server I was wondering if there is a way to have One Mysql server for loads and one to do querys?? I was thinking of turning on tcp/3306 and have a Load server use LOAD DATA LOCAL to load the data over tcp. So the current server would bascailly become my query server. Can you use LOAD DATA that way??? If not please direct me to the mysql tool that will do this, or a smarter way? thanks,rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 5.0 stored procedures
At 10:42 -0500 4/30/04, Peter Brawley wrote: The MySQL 5.0 implementation of stored procedures is appreciated, but skeletal. Not much of it works very well. It would be very helpful indeed if the development team could give us a hint on when improvements in SP implementation might be expected. TIA. PB One thing that helps very much indeed is when people report bugs with reproduceable test cases at http://bugs.mysql.com/. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
triggers
Hi Are there any plans to have triggers introduced into MySQL? Regards Joe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: triggers
On Fri, 30 Apr 2004 17:04:57 +0100 joe collins [EMAIL PROTECTED] wrote: Are there any plans to have triggers introduced into MySQL? version 5.1 - probably a ways off with 5.0 still alpha. http://dev.mysql.com/doc/mysql/en/ANSI_diff_Triggers.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: triggers
At 17:04 +0100 4/30/04, joe collins wrote: Hi Are there any plans to have triggers introduced into MySQL? http://dev.mysql.com/doc/mysql/en/Roadmap.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: triggers
At 10:17 -0500 4/30/04, Josh Trutwin wrote: On Fri, 30 Apr 2004 17:04:57 +0100 joe collins [EMAIL PROTECTED] wrote: Are there any plans to have triggers introduced into MySQL? version 5.1 - probably a ways off with 5.0 still alpha. That's true, but if history is any guide, the very next posting on this subject will contain the question: So, when will 5.1 be available as a production release? http://dev.mysql.com/doc/mysql/en/ANSI_diff_Triggers.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create a table from SHOW TABLE STATUS ?
On Fri, 2004-04-30 at 06:03, Richard A. DeVenezia wrote: I'm running 4.1.1a-alpha-max-nt using innodb tables with foreign keys . I know how to use SHOW TABLE STATUS to see the referential linkages in the COMMENT column. Supppose I am typing away in MySQL monitor: Q: Is there a way to create a table from the SHOW TABLE STATUS command ? Not from SHOW TABLE STATUS, but you can duplicate a table. From the create table docs (http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html): In MySQL 4.1, you can also use LIKE to create a table based on the definition of another table, including any column attributes and indexes the original table has: CREATE TABLE new_tbl LIKE orig_tbl; CREATE TABLE ... LIKE does not copy any DATA DIRECTORY or INDEX DIRECTORY table options that were specified for the original table. Q: Does v5 have system views that are equivalent to SHOW xyz commands ? Thanks, Richard A. DeVenezia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: triggers
Paul DuBois wrote: At 10:17 -0500 4/30/04, Josh Trutwin wrote: On Fri, 30 Apr 2004 17:04:57 +0100 joe collins [EMAIL PROTECTED] wrote: Are there any plans to have triggers introduced into MySQL? version 5.1 - probably a ways off with 5.0 still alpha. That's true, but if history is any guide, the very next posting on this subject will contain the question: So, when will 5.1 be available as a production release? When will 5.1 be available as a production release? So now we can wait for Heikki to do his famous math on release dates ;-) Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: One Mysql For loads and one for query
I thought I could use mysqlimport, but I notice that the text files need to be the same name as the table. My text files are named with a time stamp every hour so I have 24 files that get loaded. And the table name is nothing like the text files names. Is there a way to have mysqlimport import to a table that does not match the text file it is reading from?? From manual: mysqlimport strips any extension from the filename and uses the result to determine which table to import the file's contents into. Im sure someone can help me?? Rob -Original Message- From: rmck [EMAIL PROTECTED] Sent: Apr 30, 2004 8:54 AM To: [EMAIL PROTECTED] Subject: One Mysql For loads and one for query I have a Mysql db that is loaded with about 500,000 records a night using LOAD DATA INFILE. This goes on for a month then a new table is created and so on. Then data is then just queryed nothing else. To take some stress off of this server I was wondering if there is a way to have One Mysql server for loads and one to do querys?? I was thinking of turning on tcp/3306 and have a Load server use LOAD DATA LOCAL to load the data over tcp. So the current server would bascailly become my query server. Can you use LOAD DATA that way??? If not please direct me to the mysql tool that will do this, or a smarter way? thanks,rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: One Mysql For loads and one for query
At 10:34 -0700 4/30/04, rmck wrote: I thought I could use mysqlimport, but I notice that the text files need to be the same name as the table. My text files are named with a time stamp every hour so I have 24 files that get loaded. And the table name is nothing like the text files names. Is there a way to have mysqlimport import to a table that does not match the text file it is reading from?? No, but mysqlimport just generates a LOAD DATA statement. You can bypass mysqlimport to create a LOAD DATA statement that fits your situation. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: triggers (or too-many-crappy-questions)
That's true, but if history is any guide, the very next posting on this subject will contain the question: So, when will 5.1 be available as a production release? When we were evaluating databases, I looked both at Postgres, SAPDB, and MySQL. I've been subscribed to the MySQL and Postgres mailing lists for quite some time. I found that the Postgres mailing list was full of very polite, super helpful people. I found that the MySQL list was full of relatively cranky people with little patience. Over that last year, I've I think I've discovered why. The average quality of the questions posted on the MySQL list are quite low (compared to the Postgres lists), and I think many of the people who are in a position to respond are fed up. Before asking a question, people don't read the documentation. They don't search the mailing list archive to see if the question has been asked 300 times in the last 2 months. I have to jam my hands into my pockets to prevent myself from writing a nasty response to questions like, Sir, How I use MySQL? Why I want use it? Where I download from? Kindest regard. What's truly unfortunate is that the people that most need to read this won't. Maybe a new MySQL list is needed - one called Dumb Questions, and when someone posts one to any other list, someone can quickly respond, Please repost this on the MySQL Dumb Questions list, as that's where it belongs. The footer for the Dumb Questions list should be, This question could have been answered in the Documentation Section of MySQL.com, or by searching the archives at lists.mysql.com David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Another Trailing Spaces Issue
The TINYTEXT format solves the problem of storing the string with spaces at the end. Now, I'm having trouble SELECTING a row WHERE the TINYTEXTcolumn = theStringWithTheSpacesAtTheEnd; If the string is theString + space and another row has an entry with theString (+ no space), the query returns BOTH rows. Is there a way to get around returning the latter entry? Thanks, John on 4/30/04 2:42 AM, Matt W at [EMAIL PROTECTED] wrote: Hi John, I *think* VARCHAR is *supposed* to work that way, but doesn't in MySQL. So you'll have to use TINYTEXT. Its storage requirements are the same as VARCHAR(255) and it behaves the same way, except for, I think, 3 things: 1) the trailing space thing, obviously; 2) it can't have a DEFAULT value; and 3) you can't index the whole column -- but you can INDEX (col(255)), which has the same effect. :-) Hope that helps. Matt - Original Message - From: John Mistler Sent: Friday, April 30, 2004 3:54 AM Subject: Storing a space Is there a way to force an invisible space character to be stored at the END of a string in a column (either VARCHAR or CHAR), so that when it is retrieved the space at the end is not cut off? theString + space or even, theString + space + space + space, etc. Currently, I can only get the string back as theString Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: triggers (or too-many-crappy-questions)
David Griffiths wrote: Maybe a new MySQL list is needed - one called Dumb Questions, and when someone posts one to any other list, someone can quickly respond, Please repost this on the MySQL Dumb Questions list, as that's where it belongs. The footer for the Dumb Questions list should be, This question could have been answered in the Documentation Section of MySQL.com, or by searching the archives at lists.mysql.com I have had an idea along these lines that maybe someone will consider implementing. I say have two or three levels of tech support, whether it be via a mailing list, News Group, phone number whatever. The lowest level would be accessible to anyone. The next level would only be accessible to those who can pass a short test which is made up of questions that can easily be answered by looking in the faq or other documentation. Then if there was a third level you would have to answer questions that would require looking at the documentation and some degree of logical reasoning. I would be happy to be forced to answer questions before I could subscribe to a mailing list if it meant the I wouldn't have to sift through questions like I installed it but now it won't run, why? Chris W http://thewishzone.com:8086 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another Trailing Spaces Issue
John Mistler wrote: The TINYTEXT format solves the problem of storing the string with spaces at the end. Now, I'm having trouble SELECTING a row WHERE the TINYTEXTcolumn = theStringWithTheSpacesAtTheEnd; If the string is theString + space and another row has an entry with theString (+ no space), the query returns BOTH rows. Is there a way to get around returning the latter entry? I'm not 100% sure but I remember reading about a binary compare option in the documentation that I bet would do the trick. Take a look at that. Chris W http://thewishzone.com:8086/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: triggers (or too-many-crappy-questions)
Hi, Can we have list for people who just like to give newbies shit? How about [EMAIL PROTECTED] How much have you guys paid for your support hmm? This is a free mailing list and I don't really see why people have to moan about a few simple questions every once in a while. The people who moan about RTFM crap are much more of a bother to me than a poor guy who doesn't know his way around mysql yet, doesn't get how useful and complete the manual is, and needs some basic help. Thanks, Eric At 11:49 AM 4/30/2004, Chris W wrote: David Griffiths wrote: Maybe a new MySQL list is needed - one called Dumb Questions, and when someone posts one to any other list, someone can quickly respond, Please repost this on the MySQL Dumb Questions list, as that's where it belongs. The footer for the Dumb Questions list should be, This question could have been answered in the Documentation Section of MySQL.com, or by searching the archives at lists.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database structure
I used to program for a medical tester. I used method 2: 2 - a table with 60 rows for one assessment : results(#assessment_nr, labtest_nr, p, d) where p and d are my two results. The BIG advantage was changes to the tests, adding new ones, or deleting fields. Made it much more flexible. I would like an advise on the following problem : I have a table of patients. Each patient can make different biological assessments. Each assessment is always decomposed into different laboratory tests. A laboratory test is made of a test number and two values coming from analysers. The schema is : Patients(#patient_nr,name,etc...) Assessment(#assessment_nr, #patient_nr, assessment_type, other usefull values). Assessment_types(assessment_type, labtest_nr) An assessment is composed of different tests, let's say assessment type 1 is composed of lab test 1,2,3,5 and assessment type 2 of lab test number 10 to 70. I have an assessment with 60 different lab tests (always the same). I have two ways for storing the values : 1 - a table with 120 columns for the two values. results(#assessment_nr, p10,d10, p11,d11, .,p70,d70). where 10 to 70 represents the lab test number. 2 - a table with 60 rows for one assessment : results(#assessment_nr, labtest_nr, p, d) where p and d are my two results. Here comes my question. Which of the two would you choose? The firsrt solution has the advantage of returning one single row for one complete assessment. If I have to make statistics, it is easy. But, if I have to modify the composition of an assessment (which occurs very rarely), I shall have to use an alter table instruction. As I have 4 different assessment types, I have to create five different tables, one per assessment type. The second solution is normalized and more elegant. But I am preoccupied by the size of the table. For one assessment, I'll store 60 rows with only two useful integers in it. And you must add the size of the index. With 25.000 assessments a year, it makes 1.500.000 rows with only 4 columns amoung them 2 only for the results and 2 for identification. I would like to store 10 years online, so 15.000.000 rows. What about the size of index ? Any advise ? I thank you in advance. Alain Reymond (I hope that it is clear enough with my bad English). -- 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]
Group By Order By problem
Hi all, I am trying to get a bunch of results, group them by category, and then order each group of categories. My query is thus: SELECT l.CatalogNumber, l.MP3Name, l.PDFLink, l.PDFName, l.Title, p.PublisherName, c.ComposerLname, a.ArrangerLname, l.Price, l.Description, o.Alias FROM listings l, publishers p, composers c, arrangers a, categories o WHERE (a.ArrangerLname like '%$Criteria%' or p.PublisherName like '%$Criteria%' or c.ComposerLname like '%$Criteria%' or l.Title like '%$Criteria%' or l.CatalogNumber like '%$Criteria%' or l.Price like '%$Criteria%' or l.Description like '%$Criteria%') AND l.PublisherID=p.PublisherID and l.ComposerID=c.ComposerID and l.ArrangerID=a.ArrangerID and l.CategoryID=o.CategoryID GROUP BY o.Alias ASC ORDER BY o.Alias, c.ComposerLname ASC; This only displays 1 row in each category, so clearly I have an error in my Group By and/or Order By clause(s). I am sure my error is fairly basic, but I don't have enough experience with MySQL to figure it out. Does anyone have any insight into the problem? -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql-3.23.35.0 on AIX 4.3: CPU Hog
Hi, I downloaded mysql-3.23.35.0 for AIX 4.3 from bullfreeware.com and as I soon as I start mysqld, it starts consuming almost all the CPU (hovers around 97%). Any idea what might be the cause of this?? Thanks, Paul
problem (bug?) with LOCATE(substr,str,pos)
Hi
I need to do a LOCATE(substr,str,pos) request where pos must be taken from a
column.
for unknown reason (bug?) it does not work.
I made the following example to ilustrate the problem:
select ERW, locate('xyz', A, 3000) as loc1, locate('xyz', A, ERW) as loc2
from B;
+--+-++
| ERW | loc1| loc2 |
+--+-++
| 2873 | 10363 | 0 |
| 2677 | 18027 | 0 |
| 2459 | 13016 | 0 |
| 2539 |3462 | 0 |
+--+-++
4 rows in set (0.00 sec)
in this example loc2 should have the same result as loc1, but loc2 always
presents 0.
whats going wrong here?
anyone can help?
I have mysql 4.0.18 on redhat 9.0.
ERW is a mediumint(8) unsigned not null
Regards
Boris
_
The new MSN 8: advanced junk mail protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database structure
The schema is : Patients(#patient_nr,name,etc...) Assessment(#assessment_nr, #patient_nr, assessment_type, other usefull values). Assessment_types(assessment_type, labtest_nr) An assessment is composed of different tests, let's say assessment type 1 is composed of lab test 1,2,3,5 and assessment type 2 of lab test number 10 to 70. Looking at the two alternatives, the second based upon a normalization of patient with assessments fits closer with standard medical identification. The assessments are usually based upon the ICD-9 coding. As an example 191.3 is a Neoplasm of the brain, Parietal lobe. There are corresponding elements for procedures ala 01.2 is a Craniotomy and craniectomy code with subsequent digits further describing the particular procedure. Using these codes provides an industry standard mechanism. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another Trailing Spaces Issue
If the string is theString + space and another row has an entry with theString (+ no space), the query returns BOTH rows. Is there a way to get around returning the latter entry? You could do something like: select stuff from mytable where stuff = 'foo ' and length(stuff) = 4; -- Steve Bacher Draper Laboratory Cambridge, MA, US -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
date format problem
Hi, I'm moving a JavaServlet app over from Tomcat on Win2K with a MS SQL 7 DB to Tomcat on Red Hat Linux with mySQL. Of course, there's about a hundred queries that use dates and of course, they're all in the format mm-dd-. is there a way to format the date column in my mysql tables to accept a date in this format or do i really have to go through every sql statement and parse the date and rebuild it to be -mm-dd? thanks so much. deadline is fast approaching. Matt Tucker thoughtbot
Reports
hi, Is there a way to do report writing from Mysql databases? I want to transfer all the records from Mysql to a file. i used the Into OUTFILE but it doesnt display properly.I want to diplay it properly like records or reports. is there a way?? Thanks, Liz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reports
Is there a way to do report writing from Mysql databases? I want to
transfer all the records from Mysql to a file. i used the Into OUTFILE but it
doesnt display properly.I want to diplay it properly like records or reports.
I wrote a basic Perl script to prettify the output of a MySQL query that
gets outputted in tab delimited format. So you would run MySQL with the
-B and -r flags and pipe the output through this script:
--- cut here ---
#!/usr/bin/perl
# input: a tab delimited file, as might come from a MySQL query
# output: the same data, arranged so that each column accommodates
# the maximum length of the data in that column
$i = 0;
while () {
chomp;
# s//\\/g;
@data = split(\t);
@sizes = map(length,@data);
$i++;
@sizevec[$i] = join(\t,@sizes);
@datavec[$i] = $_;
for ($j=0;$j[EMAIL PROTECTED];$j++) {
$maxsize[$j] = $sizes[$j] if $maxsize[$j] $sizes[$j];
}
}
for ($i=0; $i@datavec;$i++) {
@sizes = split(\t,@sizevec[$i]);
@data = split(\t,@datavec[$i]);
for ($j=0;$j[EMAIL PROTECTED];$j++) {
$_ = $data[$j];
$l = length;
$m = $maxsize[$j];
$padding = x ($m - $l);
if (/^[0-9. -]*$/) { print $padding . $_ ; }
else { print $_ . $padding ; }
print ;
}
print \n;
}
--- ereh tuc ---
--
Steve Bacher
Draper Laboratory
Cambridge, MA, US
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reports
EMS MySQL Manager (costs about $120 US) has a nice report-design and generation facility in addition to many other great things. Also, you could connect to your DB with PERL or Java, select your data out, and generate your reports in any format you wish. David. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, April 30, 2004 2:35 PM Subject: Reports hi, Is there a way to do report writing from Mysql databases? I want to transfer all the records from Mysql to a file. i used the Into OUTFILE but it doesnt display properly.I want to diplay it properly like records or reports. is there a way?? Thanks, Liz -- 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] Multiple instances of server starting
Hi, I've just set up a mySQL server and, upon starting it, and running ps ax | grep mysql , it appears that there are multiple instances running: [EMAIL PROTECTED] var]# ps ax | grep mysql 7808 pts/0S 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/var --pid-file=/usr/local/mysql/var/db02.pid 7840 pts/0S 0:00 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --user=mysql --pid-file=/usr/local/mysql/var/db02.pid --skip-locking --port=3306 --socket=/tmp/mysql.sock ..and so on. Ten instances. This should be the same config file (my.cnf) and same mysql.server start script (not that it should matter (??)) as a second machine, which does the right thing, and runs a single instance of [mysqld]: [EMAIL PROTECTED] root]# ps ax | grep mysql 1519 ?S 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/var --pid-file=/usr/local/mysql/var/emma.pid 1552 ?S778:21 [mysqld] 26985 pts/0S 0:00 grep mysql Looking at this post to this list: http://lists.mysql.com/mysql/154832 it seems that others have had this problem, but the answer there does not really explain why these two servers are behaving differently. Can anyone help? Thanks very much! -Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reports
[EMAIL PROTECTED] wrote: hi, Is there a way to do report writing from Mysql databases? I want to transfer all the records from Mysql to a file. i used the Into OUTFILE but it doesnt display properly.I want to diplay it properly like records or reports. is there a way?? Thanks, Liz You are going to want to add formatting with something like PERL or PHP. Alternately, you can use an odbc connection to have MS Access connect to your mysql server and make the reports for you. Let me know if you need help setting it up. Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Reports
i'm using an app called report manager, its a report designer for linux and windows done in delphi and kylix. -Original Message- From: Bob Ramsey [mailto:[EMAIL PROTECTED] Sent: Saturday, May 01, 2004 8:36 AM To: [EMAIL PROTECTED] Subject: Re: Reports [EMAIL PROTECTED] wrote: hi, Is there a way to do report writing from Mysql databases? I want to transfer all the records from Mysql to a file. i used the Into OUTFILE but it doesnt display properly.I want to diplay it properly like records or reports. is there a way?? Thanks, Liz You are going to want to add formatting with something like PERL or PHP. Alternately, you can use an odbc connection to have MS Access connect to your mysql server and make the reports for you. Let me know if you need help setting it up. Bob -- 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]
INTO OUTFILE
hi, I am trying to put the output of certain queries into a file using SELECT * INTO OUTFILE FILENAME FROM TABLE NAME WHERE CONDITION; I am able to see the file and the records are there. But is there a way I can see one record per line??? If i do the above i see all the records continously when i save it as txt file.dont see it as one record in one line. Thanks, liz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [MYSQL] Multiple instances of server starting
At 18:02 -0400 4/30/04, jim wrote: Hi, I've just set up a mySQL server and, upon starting it, and running ps ax | grep mysql , it appears that there are multiple instances running: [EMAIL PROTECTED] var]# ps ax | grep mysql 7808 pts/0S 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/var --pid-file=/usr/local/mysql/var/db02.pid 7840 pts/0S 0:00 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --user=mysql --pid-file=/usr/local/mysql/var/db02.pid --skip-locking --port=3306 --socket=/tmp/mysql.sock ..and so on. Ten instances. This should be the same config file (my.cnf) and same mysql.server start script (not that it should matter (??)) as a second machine, which does the right thing, and runs a single instance of [mysqld]: [EMAIL PROTECTED] root]# ps ax | grep mysql 1519 ?S 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/var --pid-file=/usr/local/mysql/var/emma.pid 1552 ?S778:21 [mysqld] 26985 pts/0S 0:00 grep mysql Very likely you're seeing threads being reported as processes on one machine and not the other. What operating system does each machine run? Looking at this post to this list: http://lists.mysql.com/mysql/154832 it seems that others have had this problem, but the answer there does not really explain why these two servers are behaving differently. No, but it does explain that this is not really a problem. Well, I suppose it's possible to consider it a problem. But if so, it's an operating system problem, not a MySQL problem. :-) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INTO OUTFILE
At 19:18 -0400 4/30/04, [EMAIL PROTECTED] wrote: hi, I am trying to put the output of certain queries into a file using SELECT * INTO OUTFILE FILENAME FROM TABLE NAME WHERE CONDITION; I am able to see the file and the records are there. But is there a way I can see one record per line??? If i do the above i see all the records continously when i save it as txt file.dont see it as one record in one line. ? The default _is_ to write one record per line. The line terminator is (as the manual indicates) newline (linefeed). Perhaps you are viewing the output with a program that doesn't understand how to display such files? If you believe that that output is being written otherwise, check it with a hexdump program to see what's really in the output file. Thanks, liz -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INTO OUTFILE
If you're using notepad to view it, it'll display it all as one line because notepad only recognises the complete CR/LF as a new line. A CR or an LF by themselves will just show up as a square, non-displayable character and won't break the line. Wordpad, on the other hand, understands that a CR or an LF by itself is often used interchangeably with a CRLF...and renders its output accordingly. hth Dan - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, April 30, 2004 4:41 PM Subject: Re: INTO OUTFILE At 19:18 -0400 4/30/04, [EMAIL PROTECTED] wrote: hi, I am trying to put the output of certain queries into a file using SELECT * INTO OUTFILE FILENAME FROM TABLE NAME WHERE CONDITION; I am able to see the file and the records are there. But is there a way I can see one record per line??? If i do the above i see all the records continously when i save it as txt file.dont see it as one record in one line. ? The default _is_ to write one record per line. The line terminator is (as the manual indicates) newline (linefeed). Perhaps you are viewing the output with a program that doesn't understand how to display such files? If you believe that that output is being written otherwise, check it with a hexdump program to see what's really in the output file. Thanks, liz -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
