basic SQL question
Dear all, I have the following problem, I am trying to select records from two tables. the tables are constructed like: block block_id sequence_id snp_required first_polymorphism_index last_polymorphism_index first_reference_positio last_reference_position start_pos end_pos tiled_bp polymorphism_block_map block_id polymorphism_index polymorphism_id When I try to do: mysql select first_polymorphism_index,last_polymorphism_index from block where first_polymorphism_index like 14163 OR first_polymorphism_index like 14750 OR first_polymorphism_index like 14932; +--+-+ | first_polymorphism_index | last_polymorphism_index | +--+-+ |14163 | 14235 | |14750 | 14802 | |14932 | 14980 | +--+-+ 3 rows in set (0.02 sec) everything works fine, easy ;) but if I try to link these two tables I got: +--++-+- ++-+ | first_polymorphism_index | polymorphism_index | polymorphism_id | last_polymorphism_index | polymorphism_index | polymorphism_id | +--++-+- ++-+ |14163 | 14163 | P0216196 | 14235 | 14235 | P0216288| |14750 | 14750 | P0217007 | 14235 | 14235 | P0216288| |14932 | 14932 | P0217251 | 14235 | 14235 | P0216288| +--++-+- ++-+ 3 rows in set (1 min 27.48 sec) ## # this does not return the right values on the las polymorphism index ## SELECT b1.first_polymorphism_index, p1.polymorphism_index, p1.polymorphism_id, b2.last_polymorphism_index, p2.polymorphism_index, p2.polymorphism_id FROM block as b1, polymorphism_block_map as p1, block as b2, polymorphism_block_map as p2 WHERE b1.first_polymorphism_index = p1.polymorphism_index AND b2.last_polymorphism_index = p2.polymorphism_index LIMIT 5; But the second polymorphism_index is always the same, How I can get the right one? Thanks in advance P -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
alter date
Hi there, Do you know a sql command which I can write to obtain the last date that I updated a table
mysql installation problem
Hi, I am trying to install myql on redhat. mysql package used: mysql-3.23.52-unknown-freebsdelf4.6-i386.tar.gz While following the instructions in install document..when i run the follwing command.. scripts/mysql_install_db following errors appear: - scripts/mysql_install_db: ./bin/my_print_defaults: /usr/libexec/ld-elf.so.1: bad ELF interpreter: No such file or directory WARNING: The host 'njsupport.niksun.com' could not be looked up with resolveip. This probably means that your libc libraries are not 100 % compatible with this binary MySQL version. The MySQL deamon, mysqld, should work normally with the exception that host name resolving will not work. This means that you should use IP addresses instead of hostnames when specifying MySQL privileges ! Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables scripts/mysql_install_db: ./bin/mysqld: /usr/libexec/ld-elf.so.1: bad ELF interpreter: No such file or directory Installation of grant tables failed! Examine the logs in ./data for more information. You can also try to start the mysqld daemon with: ./bin/mysqld --skip-grant You can use the command line tool ./bin/mysql to connect to the mysql database and look at the grant tables: shell ./bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in ./data that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com Please consult the MySQL manual section: 'Problems running mysql_install_db', and the manual section that describes problems on your OS. Another information source is the MySQL email archive. Please check all of the above before mailing us! And if you do mail us, you MUST use the ./bin/mysqlbug script! -- As i am new bie to mysql ..please tell how can i make mysql work. Thanks and Regards Anil. p.s.(Can i get precomplied package (.tgz) to which i can directly pkg_add?). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using a foreign character set in MYSQL
but the different lanaguage setting in MySQL seem to mainly apply to its error messages Collation and sorting, too. To my knowledge, MySQL does not yet offer full UTF-8 support True, but UTF-8 is Unicode, and it sounds like the OP wants to work with BIG-5, a two-byte Chinese national encoding. store the special characters using the \u notation or something similar. Depends on the application language, not on MySQL. MySQL either takes it as text or as a BLOB (well, essentially that's what it does). (I still have trouble imagining how the westerners keep thinking that \u is a solution for the CJK languages, but that's off-topic.) -- Joel Rees, programmer, Systems Group Altech Corporation (Alpsgiken), Osaka, Japan http://www.alpsgiken.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: storing large integers properly
On 5 Aug 2003 at 9:49, Eben Goodman wrote: The data type of the field I am storing this info in is a bigint(16) unsigned. It appears that isbns that start with 0 are going in as 9 digit numbers, the 0 is being ignored or stripped. I have experienced this before with integer data types ignoring leading 0s. I'm wondering how to address this? Should I change the field to a varchar or char data type? Yes. Phone numbers, zip codes, Social Security numbers, etc., are generally stored as strings, not integers, since they can have leading 0s and you're not going to do calculations on them. Besides, don't some ISBNs end in 'X'? -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Connections
Thank you for the information about LIKE and equal. I have another question. What is better/quicly 50 simultaneously query/update at the same time from 50 different users or 50 simultaneously query/update at the same time from one user? Thanks for your information in advanced, eli At 16:53 +0100 8/5/03, eli wrote: Hi, I have a question about using LIKE or equal. I mean, comparing two strings with exact coincidence, without case sensitive, which is better? Or are they the same? Do they work equal? For Instance. abc=abc Or abc LIKE abc I use Mysql 4.0.12. Thanks in advanced. Eli Functionally, the two expressions are the same. In terms of efficiency, the = operator's probably somewhat better than LIKE. You can try checking this for yourself as follows: mysql select benchmark(1000,'abc' LIKE 'abc'); +--+ | benchmark(1000,'abc' LIKE 'abc') | +--+ |0 | +--+ 1 row in set (2.60 sec) mysql select benchmark(1000,'abc' = 'abc'); +---+ | benchmark(1000,'abc' = 'abc') | +---+ | 0 | +---+ 1 row in set (2.09 sec) SetFile DATABASE FACTORY - Aplicaciones a Medida en FileMaker Pro ( Windows y Macintosh ) [EMAIL PROTECTED]www.setfile.com/esTEL 93 238 56 00 FileMaker Inc es miembro del BSA ( 900 211 048) SetFile - FSA Partners -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table error 127
My database experiences a similar effect, but I can't pinpoint the specific queries because it gets thousands per second. I have not noticed the problem on a machine that is only used occasionally. Is there a way to get queries out of the binlog for a specific date/time range? That might help me pinpoint the problem and ultimately get this resolved. Thanks. Richard Gabriel Director of Technology, CoreSense Inc. (518) 306-3043 x3951 - Original Message - From: Jonathan Patton [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, August 05, 2003 8:58 AM Subject: RE: table error 127 Walt, Thanks for the suggestion. I ran myisamchk on the table and it said it was corrupted. So I ran myismachk on the table with the -r and it said the table was fixed. I then ran the update queries I was running before and received the same 127 error. The update queries were: update discussion_categories discussion_categories1, discussion_categories set discussion_categories1.parent_1 = discussion_categories.category_id where discussion_categories1.`parent_1_text` = discussion_categories.name; update discussion_categories discussion_categories1, discussion_categories set discussion_categories1.parent_2 = discussion_categories.category_id where discussion_categories1.`parent_2_text` = discussion_categories.name; update discussion_categories discussion_categories1, discussion_categories set discussion_categories1.parent_3 = discussion_categories.category_id where discussion_categories1.`parent_3_text` = discussion_categories.name; update discussion_categories discussion_categories1, discussion_categories set discussion_categories1.parent_4 = discussion_categories.category_id where discussion_categories1.`parent_4_text` = discussion_categories.name; So I shut the mysql server down, ran mysqlchk again and all the tables were okay. I had a backup of the database, so I just dropped the whole thing and imported from the backup. (The backup comes from another mysql server). The errors still persisted. The table in question only has 167 rows in it. I had a text file as well with the data in it, so I deleted all the data from the file and loaded the data with the load data infile command. I did get 1300 some warnings. Could the data being loaded in cause a table corruption? I'm going to go through the 169 rows being loaded to see if I can find out the problem or at least eliminate that problem from this problem. Also, could it be the backup is corrupted? Jonathan -Original Message- From: walt [mailto:[EMAIL PROTECTED] Sent: Monday, August 04, 2003 12:12 PM To: Jonathan Patton Cc: [EMAIL PROTECTED] Subject: Re: table error 127 Jonathan Patton wrote: I have mysql setup on two computers with identical databases. When I run a group of queries on the one computer I get back an error 127 which I checked on an it appears to be a table corruption error. On the other computer, the queries run fine. Since I had all the data for the table in question in a data file, I just dropped the table and recreated it. The error still appeared. Any suggestions on what to try next? The only other thing I can think of is to compare the mysql versions to see if I have an older version on the other computer. Jonathan, Have you tried running myisamchk on the database in question before running queries on it? walt -- 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: Using a foreign character set in MYSQL
When I try copying and pasting it into the mysql client command-line, the data gets trashed. Not necessarily. May just be that the command-line window doesn't know to display Chinese unless you tell it to. What OS are you working on? Working on Windoze with a telnet window open to a Linux box (which has the MySQL client/server) Thanks, Vikram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert BUG into MyISAM table (Linux + MySQL 3.23.56)
Hi!
On Aug 06, David Bordas wrote:
Hi list,
I've got a little bug with MySQL.
I can insert a row into my table but this row will not appear in the table
:(
Server is under linux redhat, MySQL is 3.23.56 installed from binary tar.gz
from MySQL team.
Table Description :
mysql desc Log_Forums;
+--+--+--+-+-++
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-++
| Pseudo | char(16) | | PRI | | |
| Date | timestamp(14)| YES | PRI | NULL| |
| Numero | smallint(5) unsigned | | PRI | NULL|auto_increment |
| Type_message | enum('0','1')| | | 0 | |
+--+--+--+-+-+-- --+
4 rows in set (0.00 sec)
And some code :
mysql select * from Log_Forums where Date=2003080611;
Empty set (0.08 sec)
mysql insert into Log_Forums (pseudo,date,Type_Message) values
(doss08,2003080611,0);
Query OK, 1 row affected (0.00 sec)
mysql select * from Log_Forums where Date=2003080611;
++++--+
| Pseudo | Date | Numero | Type_message |
++++--+
| doss08 | 2003080611 | 1 | 0|
++++--+
1 row in set (0.08 sec)
mysql insert into Log_Forums (pseudo,date,Type_Message) values
(coss08,2003080611,0);
Query OK, 1 row affected (0.00 sec)
mysql select * from Log_Forums where Date=2003080611;
++++--+
| Pseudo | Date | Numero | Type_message |
++++--+
| doss08 | 2003080611 | 1 | 0|
++++--+
1 row in set (0.08 sec)
As you can see, i can't find coss08 in my table :(
Table check is ok :
mysql check table Log_Forums;
+--+---+--+--+
| Table| Op| Msg_type | Msg_text |
+--+---+--+--+
| jeuxvideo.Log_Forums | check | status | OK |
+--+---+--+--+
1 row in set (0.15 sec)
Table have got lots of records with pseudo=coucou.
It seems now that i can't insert any row which pseudo coucou
Can someone help me ?
So i've make a dump of this table and import it on a new server.
There, i haven't got any problem.
Si i tried to understand how to make MySQL work like it must works.
In fact my table have got 1200 different pseudos and always 60K records.
When i'm doing an insert, i also do a delete ...
But pseudo coucou is repeated 53250 times.
I tried something like :
insert into Log_Forums (pseudo,date,Type_Message) values
(buh08,2003080611,0);
Don't work.
But if i do :
update Log_Forums set pseudo =boucou where pseudo=coucou;
then
insert into Log_Forums (pseudo,date,Type_Message) values
(buh08,2003080611,0);
WORK ...
That seems that a large number of one specific pseudo make MySQL consider
that this value will be the lowest possible value.
Can someone have some clue to help me ?
Can you create a repeatable test case ?
Regards,
Sergei
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: column privilege problem
Tiffany Wilkes [EMAIL PROTECTED] wrote: I am having problems granting column privileges. I want to grant the update privilege (only) to a column (called pass) in a table (called Acct). Here's what I get: mysql grant update pass on practicedb.Acct to [EMAIL PROTECTED]; ERROR 1064: You have an error in your SQL syntax near 'pass on practicedb.Acct to [EMAIL PROTECTED]' at line 1 I don't understand how the syntax is wrong. I think I followed the instructions in the manual. I don't have any problems granting table privileges--just column. GRANT UPDATE(pass) ON practicedb.Acct TO 'JohnDoe'@'localhost'; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 3.23.54 + update
Hello dear all, i have 2 table :tbl1 and tbl2 i need to make an update on tbl1 based from tbl2 records. Ex: update tbl1, tbl2 set tbl1.conf='1' where tbl2.name='AD' and tbl2.date=tbl1.date; mysql: MySQL 3.23.54 i see this kind of query works on mysql 4.0.2 How i can do this with my version? I also tried with left joins ...but...noways! Any ideas? thanks in advances and regards, fabrizio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: table error 127
Walt, Thanks for the suggestion. I ran myisamchk on the table and it said it was corrupted. So I ran myismachk on the table with the -r and it said the table was fixed. I then ran the update queries I was running before and received the same 127 error. The update queries were: update discussion_categories discussion_categories1, discussion_categories set discussion_categories1.parent_1 = discussion_categories.category_id where discussion_categories1.`parent_1_text` = discussion_categories.name; update discussion_categories discussion_categories1, discussion_categories set discussion_categories1.parent_2 = discussion_categories.category_id where discussion_categories1.`parent_2_text` = discussion_categories.name; update discussion_categories discussion_categories1, discussion_categories set discussion_categories1.parent_3 = discussion_categories.category_id where discussion_categories1.`parent_3_text` = discussion_categories.name; update discussion_categories discussion_categories1, discussion_categories set discussion_categories1.parent_4 = discussion_categories.category_id where discussion_categories1.`parent_4_text` = discussion_categories.name; So I shut the mysql server down, ran mysqlchk again and all the tables were okay. I had a backup of the database, so I just dropped the whole thing and imported from the backup. (The backup comes from another mysql server). The errors still persisted. The table in question only has 167 rows in it. I had a text file as well with the data in it, so I deleted all the data from the file and loaded the data with the load data infile command. I did get 1300 some warnings. Could the data being loaded in cause a table corruption? I'm going to go through the 169 rows being loaded to see if I can find out the problem or at least eliminate that problem from this problem. Also, could it be the backup is corrupted? Jonathan -Original Message- From: walt [mailto:[EMAIL PROTECTED] Sent: Monday, August 04, 2003 12:12 PM To: Jonathan Patton Cc: [EMAIL PROTECTED] Subject: Re: table error 127 Jonathan Patton wrote: I have mysql setup on two computers with identical databases. When I run a group of queries on the one computer I get back an error 127 which I checked on an it appears to be a table corruption error. On the other computer, the queries run fine. Since I had all the data for the table in question in a data file, I just dropped the table and recreated it. The error still appeared. Any suggestions on what to try next? The only other thing I can think of is to compare the mysql versions to see if I have an older version on the other computer. Jonathan, Have you tried running myisamchk on the database in question before running queries on it? walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dropping primary key/unique key
I have a problem understanding why MySQL is deleting a unique key instead of a primary key. from Documentation: DROP PRIMARY KEY drops the primary index. If no such index exists, it drops the first UNIQUE index in the table. When I do it then I get this: mysql desc uksample4; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | id| int(11) | | UNI | 0 | | | name | char(30) | YES | | NULL| | | tel | char(20) | | PRI | | | +---+--+--+-+-+---+ 3 rows in set (0.00 sec) mysql alter table uksample4 drop primary key ; Query OK, 0 rows affected (0.24 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql desc uksample4; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | id| int(11) | | | 0 | | | name | char(30) | YES | | NULL| | | tel | char(20) | | PRI | | | +---+--+--+-+-+---+ 3 rows in set (0.00 sec) It deletes the unique key (id) instead of he primary key (tel). Did I do something wrong ? MySQL 4.0.14 Your example worked fine for me. Could you provide a test case? I know that the example worked as decribed above, thank you for confirming it. :-) My Question is, why would it not drop the primary key, but the unique key instead ?? if there is no primary key MySQL uses the first unique key as primary key, so i think MySQL thinks there is no primary key cause your first field is an unique key, so MySQL drops the first unique key it finds, a bug? Which command would delete the primary key ? try to drop the index by its name -- Sebastian Mendel www.sebastianmendel.de www.tekkno4u.de www.nofetish.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication problem
Hi folks, trying to set up replication and I'm getting this problem which I can't see how to fix despite reading of the manual and google. I have set up a master and a slave according to the manual. The master runs 4.0.12 and the slave runs 4.0.14, they are both actually on the same machine (it's a long story, but it's what I need). I did LOAD DATA FROM MASTER on the slave which completed successfully, and then I tried SLAVE START: ERROR 1200: The server is not configured as slave, fix in config file or with CHANGE MASTER TO but.. it is! on the slave: mysql show slave status\G *** 1. row *** Master_Host: 127.0.0.1 Master_User: repl Master_Port: 3306 Connect_retry: 60 Master_Log_File: angora-bin.001 Read_Master_Log_Pos: 20102800 Relay_Log_File: angora-relay-bin.001 Relay_Log_Pos: 4 Relay_Master_Log_File: angora-bin.001 Slave_IO_Running: No Slave_SQL_Running: No Replicate_do_db: Replicate_ignore_db: Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 20102800 Relay_log_space: 8 1 row in set (0.00 sec) On the master: mysql show master status; ++--+--+--+ | File | Position | Binlog_do_db | Binlog_ignore_db | ++--+--+--+ | angora-bin.001 | 24954609 | | | ++--+--+--+ 1 row in set (0.01 sec) Any ideas what I missed? -- I remember the first time I made love. Perhaps it was not love exactly but I made it and it still works. -- The League Against Tedium -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem, Confused by Left Join.
You have not shown us anything that would indicate that your output is not correct. If you think something is missing you have to show us what is missing, and why you think it should not be. John Wards wrote: I have this query: SELECT * FROM news_category LEFT JOIN news_x_cat ON news_category.id = news_x_cat.cat_id WHERE ( news_x_cat.news_id = 9 OR news_x_cat.news_id IS NULL ) Which gives me this output: id title perm show news_id cat_id 1 About Us 1 1NULL NULL 2 Learn About Your Hair 1 1 NULL NULL 3 Press Room 0 0 9 3 4 Research News 0 0 9 4 Its Padding out with NULLs fine for the first 2 but missing out a few other records from news_category. What I want the query to do is display all the news_categorys if they are mentioned in news_x_cat or not and if they don't have any data with in news_x_cat I need this bit padded out with NULLs. Any ideas where I am going wrong? Cheers John Wards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching on Two Keys with OR?
On Tue, 5 Aug 2003, gerald_clark wrote: You are ORing on two different fields. The index cannot be used to check the value of z for an OR. ORing on two different fields is what I have been asking about :). Using a composite index was suggested, which strangely seems to work only when there are no other columns in the table. Why are you cross posting? Initially because I didn't know which list was appropriate, and later in response. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problem
On Wednesday 06 August 2003 11:13 am, Andy Smith wrote: On Wed, Aug 06, 2003 at 03:59:53PM +0100, Andy Smith wrote: On Wed, Aug 06, 2003 at 10:52:54AM -0400, walt wrote: Andy, I don't see log-slave-updates in your master setup. I see log-bin, but I think that only applies to updates done directly to the database (not replication updates). I'm not 100% sure about that, but it may be worth looking into. OK, so why would that prevent my _slave_ from even doing slave start? OK, so I added log-slave-updates to the master and it did not fix my problem. Thanks for your help though! Any other suggestions? Andy, I just noticed that you have a mix of port numbers. Can you try `netstat -an | grep 3306` from the command line and see if the master is indeed listening on that port? -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 If it's not broketweak it CONFIDENTIALITY NOTICE The information contained in this email may contain legally privileged and confidential information intended only for the use of the individual noted above. If you are not the intended recipient or employee or agent of the entity listed above, you are hereby notified that any reading, disclosure, distribution, or copying of this email communication in any way, or the taking of any action in relation to this communication, is strictly prohibited. If you have received this email in error, please immediately notify the sender and contact our Privacy Officer at (800) 782-5150 ext: 1601. If you were not the intended recipient, please delete it from your files. Thank you for your compliance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Connecting to MySQL from ASP
Depending on what driver of MySQL ODBC you are using.
The one below I use for v3.51:
strConnString = drver={MySQL ODBC 3.51
Driver};server=SERVER_IP;database=DB_NAME;user=UID;password=PWD;OPTION=4
The one below I used in the past for v2.50:
strConnString =
driver=MySQL;server=SERVER_IP;uid=UID;pwd=PWD;database=DB_NAME
Bill
-Original Message-
From: Ratmil Torres [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 06, 2003 8:11 AM
To: [EMAIL PROTECTED]
Subject: Connecting to MySQL from ASP
Hi. I am developing a web site using ASP. I need to connect to a MySQL
Server. I am using ODBC, so how should the connection string be? I have
already installed the ODBC driver for MySQL.
Thanks.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table size in mysql
* NEWMEDIAPLAN what variable values /mysql tuning you suggest for more than 2000 potential concurrent users and big tables. 2000 concurrent users is much, at least if you mean 2000 concurrent requests to the database, as opposed to 2000 concurrent users of a web site. It is hard to give you a usefull answer without knowing more about your application. The default setting for 'max_connections' is 100, but this can be raised to 500-1000 for Linux and Solaris, according to the manual: URL: http://www.mysql.com/doc/en/Too_many_connections.html If there are many tables in the database (highly normalized database), and each query requests multiple tables (joins), then you may run out of file handles, because each query need at least one filehandle for each table in the query, usually more. URL: http://www.mysql.com/doc/en/Table_cache.html This is probably a bigger problem with MyIsam tables compared to InnoDb tables, because InnoDb stores data and indexes for all tables in a single (or multiple) tablespace, and only the table format as a separate file, while MyIsam tables use three files for each table: the format file (*.frm), the data file (*.MYD) and the index file (*.MYI). In most cases there is a middle layer server (application server), and this server could use a pool of connections to serve the real clients, it could cache query results, it could cache and delay inserts, and/or it could use a round robin method of selecting from multiple replicated mysql server slaves. Your initial question suggested that you mostly wanted to write to this big table, as it was described as a log file. If you want 2000 people to simultaneously query and/or update the same table, it's a lot more heavy. I think you would need multiple redundant mysql servers and replication, or very heavy hardware, probably both. :) I still have doubts on mysql possibilities, and the correct setup. Need more info on your application. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Functions as default values
No functions as default values is a bummer, but timestamp will do he trick, so thanks for your help; much appreciated. Phil. -Original Message- From: Cybot [mailto:[EMAIL PROTECTED] Sent: 06 August 2003 15:37 To: [EMAIL PROTECTED] Subject: Re: Functions as default values I am trying to use a function as a default value for a column but do not seem to get the desired result. I want to use the NOW() function for a last_updated column, here is my code... CREATE TABLE test_table ( last_updated datetime NOT NULL default `NOW()` ) TYPE=MyISAM; This gives an error; CREATE TABLE test_table ( last_updated datetime NOT NULL default 'NOW()' ) TYPE=MyISAM; Now the table shows a default value of -00-00 00:00:00, when I add a new row the value of last_updated is also -00-00 00:00:00. I am using MySQL 3.23.37, can anyone help? funcion as default-value is not allowed AFAIK but timestamp will help you to get what you want! http://www.mysql.com/doc/en/DATETIME.html -- Sebastian Mendel www.sebastianmendel.de www.tekkno4u.de www.nofetish.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]
New Article on SET Datatype
Hi Everyone; As I was perusing the MySQL documentation, I noticed that there is a lack of documentation regarding the MySQL SET datatype and the queries used to manipulate it. In fact, the comments are longer than the documentation. A google search shows a similar lack up information regarding SET. I have therefore added a new article to my site for those interested in the MySQL SET datatype, which can be found at: http://www.vbmysql.com/articles/mysqlsetdatatype.html It is written for general MySQL users, and I would appreciate any feedback to ensure the information is clear and accurate. Thanks, Mike Hillyer www.vbmysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: table error 127
Walt, Since this is a one time data load, I made a copy of the table and named it discussion_categories_2 and then did a join with that table and the original one. The errors didn't appear then when I ran the query. The old query was: update discussion_categories discussion_categories1, discussion_categories set discussion_categories1.parent_1 = discussion_categories.category_id where discussion_categories1.`parent_1_text` = discussion_categories.name; and then new one is: update discussion_categories discussion_categories1, discussion_categories_2 set discussion_categories1.parent_1 = discussion_categories_2.category_id where discussion_categories1.`parent_1_text` = discussion_categories_2.name; So with the query joining the table on itself, the error 127 Table Handler error appeared, but when joined on the new table which was just like the original, they did not appear. The box having the problem is running: -- mysql Ver 12.21 Distrib 4.0.14, for Win95/Win98 (i32) Connection id: 5 Current database: Current user: [EMAIL PROTECTED] SSL:Not in use Server version: 4.0.14-max-debug The box that does not have the problem is running: Connection id: 1 Current database: Current user: [EMAIL PROTECTED] SSL:Not in use Server version: 4.0.12-max-debug Thanks for all your help. Jonathan -Original Message- From: walt [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 05, 2003 10:18 AM To: Jonathan Patton Cc: [EMAIL PROTECTED] Subject: Re: table error 127 On Tuesday 05 August 2003 08:58 am, Jonathan Patton wrote: Walt, Thanks for the suggestion. I ran myisamchk on the table and it said it was corrupted. So I ran myismachk on the table with the -r and it said the table was fixed. I then ran the update queries I was running before and received the same 127 error. The update queries were: update discussion_categories discussion_categories1, discussion_categories set discussion_categories1.parent_1 = discussion_categories.category_id where discussion_categories1.`parent_1_text` = discussion_categories.name; update discussion_categories discussion_categories1, discussion_categories set discussion_categories1.parent_2 = discussion_categories.category_id where discussion_categories1.`parent_2_text` = discussion_categories.name; update discussion_categories discussion_categories1, discussion_categories set discussion_categories1.parent_3 = discussion_categories.category_id where discussion_categories1.`parent_3_text` = discussion_categories.name; update discussion_categories discussion_categories1, discussion_categories set discussion_categories1.parent_4 = discussion_categories.category_id where discussion_categories1.`parent_4_text` = discussion_categories.name; So I shut the mysql server down, ran mysqlchk again and all the tables were okay. I had a backup of the database, so I just dropped the whole thing and imported from the backup. (The backup comes from another mysql server). The errors still persisted. The table in question only has 167 rows in it. I had a text file as well with the data in it, so I deleted all the data from the file and loaded the data with the load data infile command. I did get 1300 some warnings. Could the data being loaded in cause a table corruption? I'm going to go through the 169 rows being loaded to see if I can find out the problem or at least eliminate that problem from this problem. Also, could it be the backup is corrupted? Jonathan Jonathan, Have you tried running each query separately and then checking the table after each one? -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 If it's not broketweak it CONFIDENTIALITY NOTICE The information contained in this email may contain legally privileged and confidential information intended only for the use of the individual noted above. If you are not the intended recipient or employee or agent of the entity listed above, you are hereby notified that any reading, disclosure, distribution, or copying of this email communication in any way, or the taking of any action in relation to this communication, is strictly prohibited. If you have received this email in error, please immediately notify the sender and contact our Privacy Officer at (800) 782-5150 ext: 1601. If you were not the intended recipient, please delete it from your files. Thank you for your compliance. -- 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
Re: selecting count query (newbie alert)
Ryan A wrote: Hi, This is my first post here so forgive me if its too simple or not right. basically what i do is: SELECT COUNT(*) FROM table1 where cno=3; (and dumping it into a variable $theResult - I am using PHP) and it gives me the result, but now i need to do this in 5 tables... I can of course run 5 count statements but was wondering if I can do the whole thing in one statement? A friend of mine told me to try union but when i go to the documentation of union I cant understand much and it says UNION is implemented in MySQL 4.0.0 I have no idea what version i have. SHOW VARIABLES LIKE 'version' The 5 tables are pretty much the same and have the same cno field that i need and are named table1,table2,table3,table4,table5 SELECT COUNT(*) FROM table1 WHERE cno = 3 UNION SELECT COUNT(*) FROM table2 WHERE cno = 3 UNION SELECT COUNT(*) FROM table3 WHERE cno = 3 UNION SELECT COUNT(*) FROM table4 WHERE cno = 3 UNION SELECT COUNT(*) FROM table5 WHERE cno = 3 this will return 5 rows in result, f.e.: COUNT(*) 12 10 9 23 7 if you want all COUNTS as one row then you have to use f.e. MERGE table type (http://www.mysql.com/doc/en/MERGE.html) SELECT COUNT(*) FROM tablemerge WHERE cno = 3 this would return f.e: COUNT(*) 61 Can anybody help me please? Thanks in advance. cheers, -Ryan We will slaughter you all! - The Iraqi (Dis)information ministers site http://MrSahaf.com -- Sebastian Mendel www.sebastianmendel.de www.tekkno4u.de www.nofetish.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using a foreign character set in MYSQL
When I try copying and pasting it into the mysql client command-line, the data gets trashed. Not necessarily. May just be that the command-line window doesn't know to display Chinese unless you tell it to. What OS are you working on? For example, how do I insert the Chinese text from my source (a Word doc) into a MySQL table without corrupting it? Well, pasting it into the command-line window is okay if it's not a lot of data, but if it is a lot you probably want to save it as (tab-delimited?) text and import it. See the MySQL manual, sec. 6.4.9: http://www.mysql.com/doc/en/LOAD_DATA.html Once it's in, how do I get it back out into my application without corrupting it? I'm using PHP 4.3 for the Web site. The problem is less one of corrupting the text and more one of making sure whatever is getting the output know to display it as Chinese. However, concerning corrupting the text, you need to be aware of the escape character and three other special characters and treat them special: http://www.mysql.com/doc/en/String_syntax.html That's section 6.1.1.1 of the manual. The four characters are backslash, NUL, single quote, and double quote. If you're using BIG-5 and grabbing it a byte at a time, you'll need to treat both bytes as if they were single-byte characters, escaping those bytes whose values match the four special characters (0x5c, 0x00, 0x27, 0x22, I think). There aren't many of those, but they do show up sometimes. If I need to make changes to the data from the command-line client, how can I do it, especially if the query involves using a Chinese-language string? For example, update langdata set menutitle='SOME_MENU_TITLE_IN_CHINESE' where menutitle='SOME_OLD_MENU_TITLE_IN_CHINESE' Well, like I said. You need to make sure your command-line shell can talk to you in Chinese before you do that, or you won't be able to see what you're doing. If you're in MSW2k, you should be able to set up a new user account and set that account up to default to Chinese. Maybe. I do this with Japanese and it works. If you're in Mac OS X, you can also simply tell the system to give you Chinese in addition to your main language. Unfortunately, Mac OS X's shell windows don't fully deal with extended character sets yet, so you may find it preferable to use tab-delimited text files. (You may also be able to find command-line shell applications that work reasonably well with Chinese.) Linux or BSD will require special setup that's beyond my ability to explain in a post to this newsgroup. There are pages on the web that explain how, I think. Looked at the online manual but am sorry to say it didn't really help much. I tried starting the server with --character-set=big5 but it didn't seem to make much difference... MySQL has to work inside your system, so your system also has to know what character set you're going to use. Likewise, you'll have to tell people's browsers it's Chinese. And if you're using Big-5 with Java, you'll have to tell Java to convert between Unicode and BIG-5. HTH -- Joel Rees, programmer, Systems Group Altech Corporation (Alpsgiken), Osaka, Japan http://www.alpsgiken.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problem
On Wed, Aug 06, 2003 at 10:32:23AM -0400, walt wrote: Andy, Can you send a copy of your my.cnf file for both the slave and master database. Sure. Slave: [client] port=3307 socket=/data/mysql-backup/mysql.sock [mysqld] port=3307 socket=/data/mysql-backup/mysql.sock server-id=4 log-warnings master-host=127.0.0.1 master-user=repl master-password=removed master-port=3306 master: [mysqld] skip-name-resolve skip-innodb log-slow-queries log-bin master-host=another-host master-user=repl master-password=removed master-port=13306 server-id=3 replicate-do-db=liveservices tmpdir=/var/tmp (master is also a slave from another-host and is replicating the db liveservices, it should be noted that this replication is working fine and has been for a very long time) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: newbie needs help: can I order by before grouping?
I don't know of any way to sort before the grouping. That would be peculiar, since the GROUP will be eliminating potentially many of those rows. In your case, a simple solution should be: select jobnum, min(milestone), min(shipdate) from jobs group by jobnum order by 2, 3; I've always found it strange that mySQL lets us perform a GROUP BY while selecting arbitrary data from (some) row in the group. It seems vague (and I've never read the documentation on it) just what data it would choose. You are much better off picking the MIN, MAX, AVG, or something. Most DBMS's would require it. As a caution, the query I've shown above will potentially split information. If the row containint the min(milestone) for a certain jobnum is different from the row containing the min(shipdate), you'll get the information from those two rows. If you want to ensure you're seeing the shipdate from, for example, the minimum milestone for the job, then the query will be more complicated. Kevin -Original Message- From: Michael Winston [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 06, 2003 11:51 AM To: [EMAIL PROTECTED] Subject: newbie needs help: can I order by before grouping? I'm having trouble ordering my results before grouping them. I don't want to go into too much detail, so my simplified query without any grouping or ordering results in: ++---++ | jobnum | milestone | shipdate | ++---++ | 96-655 | 4 || | 96-655 | 2 || | 96-655 | 0 | 2003-12-04 | | 98-308 | 0 | 1973-05-11 | | 98-308 | 4 || | 98-316 | 0 | 1973-06-11 | | 98-316 | 4 || | 98-500 | 2 || | 98-327 | 4 || | 98-327 | 0 | 1973-08-11 | ++---++ when I add group by jobnum order by milestone I get: ++---++ | jobnum | milestone | shipdate | ++---++ | 98-308 | 0 | 1973-05-11 | | 98-316 | 0 | 1973-06-11 | | 96-655 | 4 || | 98-327 | 4 || ++---++ but what I really want is this: ++---++ | jobnum | milestone | shipdate | ++---++ | 98-308 | 0 | 1973-05-11 | | 98-316 | 0 | 1973-06-11 | | 96-655 | 0 | 2003-12-04 | | 98-327 | 0 | 1973-08-11 | | 98-500 | 2 || ++---++ Clearly, mysql is grouping first and then ordering. I suppose that's reasonable, but I really want to order the results internally first and then group so that the displayed row from each group is the smallest value (and then maybe do some more sorting on shipdate, but that's another issue). Is there a way to do this? Thanks, Michael -- 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]
Query Problem, Confused by Left Join.
I have this query: SELECT * FROM news_category LEFT JOIN news_x_cat ON news_category.id = news_x_cat.cat_id WHERE ( news_x_cat.news_id = 9 OR news_x_cat.news_id IS NULL ) Which gives me this output: id title perm show news_id cat_id 1 About Us 1 1NULL NULL 2 Learn About Your Hair 1 1 NULL NULL 3 Press Room 0 0 9 3 4 Research News 0 0 9 4 Its Padding out with NULLs fine for the first 2 but missing out a few other records from news_category. What I want the query to do is display all the news_categorys if they are mentioned in news_x_cat or not and if they don't have any data with in news_x_cat I need this bit padded out with NULLs. Any ideas where I am going wrong? Cheers John Wards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Lowering the ft_min_word_len
Hello,
I have a table with a FULLTEXT index on a column of type 'text'.
Searches on this table using MATCH() AGAINST() work fine for most
words. However, I needed to match against a 3 letter word. So I
lowered the ft_min_word_len to 3 in /etc/my.cnf. I then restarted
MySQL. I checked that the variable was set to 3 in the running mysqld.
But for some reason, I cannot fetch any results:
mysql select title_id from support_doc_articles where match(article)
against ('dns');
Empty set (0.00 sec)
It does not work IN BOOLEAN MODE either:
mysql select title_id from support_doc_articles where match(article)
against ('dns' IN BOOLEAN MODE);
Empty set (0.00 sec)
Actually, I just tried it again, searching for the 3 letter word 'key',
and it brought back results. Is 'dns' in the stopwords list? Is there
any way I can see what words are in there? Can I exclude words from the
stopword list without recompiling MySQL?
Thanks for any help.
--
Justin Hopper
UNIX Systems Engineer
Spry Hosting
http://www.spry.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: reindexing
I was under the impression that InnoDB tables took care of this for you. You only need to be concerned if you add/delete repeatedly from anywhere but the end rows of the table. I'd like to know if I'm wrong about this. Adam Nelson wrote: I just did a major insert of new data and now all my selects have slowed down. The table is innodb. Is there a way to reindex everything without having to drop anything. Otherwise, I suppose I will have to drop the indexes and remake them. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
column privilege problem
Hi, I am having problems granting column privileges. I want to grant the update privilege (only) to a column (called pass) in a table (called Acct). Here's what I get: mysql grant update pass on practicedb.Acct to [EMAIL PROTECTED]; ERROR 1064: You have an error in your SQL syntax near 'pass on practicedb.Acct to [EMAIL PROTECTED]' at line 1 I don't understand how the syntax is wrong. I think I followed the instructions in the manual. I don't have any problems granting table privileges--just column. Tiffany Landry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Message
I am just learning mysql and I'm trying to write a cgi to update the
database from a form. Also just learning Perl. I have the following script
started that is called by an HTML form. I get the follwing message when I
execute it. Where can I find the message descriptions.
DBI::st=HASH(0x1b31f28)
this is my script so far:
#!c:\perl\bin\perl
use DBI();
print Content-type:text/html\n\n;
#Connect to database members.
$database = members;
$table = members;
$dbh = DBI-connect(DBI:mysql:$database)or
dienice(Can't connect:$DBI::errstr);
$sth = $dbh-prepare(select
membername,address,city,state,zipcode,phonenumber from members)or
dienice(Can't prepare statement: ,$dbh-errstr);
print $sth;
exit;
I don't see where you're logging in to the database. You won't get
anything back from your queries until you do. Something like this:
# connect to database
$dbh = DBI-connect ($dsn, $user, $password, { RaiseError =
1,PrintError=0 })
or die Could not connect to server $dsn: $DBI::err
($DBI::errstr)\n;
Also don't forget to ...
$dbh-disconnect;
after you're all done.
Here's something you might want to add to the top of all your scripts.
BEGIN
{
open (STDERR,$0-err.txt);
print STDERR \n,scalar localtime,\n;
}
It prints the error file to $0 (the name of the current script) -err.txt
in the same directory as the script, so you don't have to chase it down
in the web server log files. Comment it out when the script is working
otherwise the log file just keeps going, and going, and
Also, I installed my perl in c:\usr\bin so I don't have to keep changing
the path every time I upload it to a *nix box.
--
/* All outgoing email scanned by AVG Antivirus /*
Amer Neely, Softouch Information Services
W: www.softouch.on.ca
E: [EMAIL PROTECTED]
V: 519.438.5887
Perl | PHP | MySQL | CGI programming for all data entry forms.
We make web sites work!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't set thread stack with ver 4.0.14
Kittiphum Worachat [EMAIL PROTECTED] wrote: I try to set thread stack with ver 4.0.14 it never success the stack size still 128K (I can do with the same command with ver. 4.0.12 it work) The command is put in my.cnf like this set-variable = thread_stack = 512K how to set with ver 4.0.14 [mysqld] thread_stack = 512K works like a charm. -- 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]
DB Performance - Celeron vs. P4
Currently, I have a Celeron 1.2 Ghz server with 512 RAM, and I'm considering moving to a P4 2 Ghz with the same amount of RAM. I have a few specific tables with several million rows of data, and it takes quite a long time to process that data on my current server. Does anyone have a good idea of the type of performance increase I'd see if I moved to a P4 server? I'm hoping to see a response like, Oh yeah - I moved to a P4 from a Celeron and operations that used to take 10 minutes now take 1 minute or less - all because MySQL has special options to take full advantage of the P4's power. Or something like that. fingers crossed - Jonathan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problem
On Wednesday 06 August 2003 11:26 am, Andy Smith wrote: Andy, I just noticed that you have a mix of port numbers. Can you try `netstat -an | grep 3306` from the command line and see if the master is indeed listening on that port? $ netstat -an | grep 3306 tcp0 0 0.0.0.0:33060.0.0.0:* LISTEN There is one more thing I can think of to check... Can you send me a copy of the master.info file. I've had to manually change it before after changing the master in the my.cnf file. I found out later that you could do CHANGE MASTER TO -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 If it's not broketweak it CONFIDENTIALITY NOTICE The information contained in this email may contain legally privileged and confidential information intended only for the use of the individual noted above. If you are not the intended recipient or employee or agent of the entity listed above, you are hereby notified that any reading, disclosure, distribution, or copying of this email communication in any way, or the taking of any action in relation to this communication, is strictly prohibited. If you have received this email in error, please immediately notify the sender and contact our Privacy Officer at (800) 782-5150 ext: 1601. If you were not the intended recipient, please delete it from your files. Thank you for your compliance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1005: Can't create table './db/table.frm' (errno: 150)
Hello, I found the key to solve this problem in: http://darkstar.ist.utl.pt/mysql/doc/en/InnoDB_foreign_key_constraints.html You'll probably need an INDEX for that new foreign key you are declaring in older versions this isn't neccesary but in latest ones it is a restriction. You can have more info about the error description if you have root access by typing mysql show innodb status; LATEST FOREIGN KEY ERROR 030807 1:27:10 Error in foreign key constraint of table database/table: There is no index in the table database/table where the columns appear as the first columns. Constraint: foreign key (key) references database.table(key) on delete cascade) type=innodb also if you type: shell perror 150 you obtain: 150 = Foreign key constraint is incorrectly formed I hope this to be enough... Aaron D. Tavío Medina _ Infórmate sobre las últimas noticias en MSN Actualidad. http://www.msn.es/Actualidad/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: column privilege problem--Solved
Hi Tiffany, The Grant format for the Column privilege is a bit confusing ,bit here it goes: GRANT privilege (column1,column2, etc...) ON dbname.tblname TO 'username'@'hostname' IDENTIFIED BY 'mypassword' WITH GRANT OPTION; f.e GRANT SELECT (student_id) ON training_db.student TO 'columnuser'@'localhost' IDENTIFIED BY 'columnuser'; Best regards Nils Valentin Tokyo/Japan Dathan Vance Pattishall wrote: FYI Note on this. Using column privs you take a performance penalty on reads / writes. ---Original Message- --From: Tiffany Wilkes [mailto:[EMAIL PROTECTED] --Sent: Tuesday, August 05, 2003 3:48 PM --To: mysql --Subject: column privilege problem--Solved -- -- I solved the problem--I needed ( ) around the column list. I think --the manual should make that more clear. -- -- Original Message --Subject: column privilege problem --Date: Tue, 05 Aug 2003 15:39:54 -0700 --From: Tiffany Wilkes [EMAIL PROTECTED] --To: [EMAIL PROTECTED] -- -- -- --Hi, -- --I am having problems granting column privileges. I want to grant the --update privilege (only) to a column (called pass) in a table (called --Acct). Here's what I get: -- --mysql grant update pass on practicedb.Acct to [EMAIL PROTECTED]; --ERROR 1064: You have an error in your SQL syntax near 'pass on --practicedb.Acct to [EMAIL PROTECTED]' at line 1 -- --I don't understand how the syntax is wrong. I think I followed the --instructions in the manual. I don't have any problems granting table --privileges--just column. -- --Tiffany Landry -- -- --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]
