how to add foreign key in alter command
Hi guys Please tell me the command syntax, how to add a colmmen foreign key in alter syntax thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: how to add foreign key in alter command
Date: Sun, 17 May 2009 14:25:55 +0800 From: nathan.vorbei.t...@gmail.com To: mysql@lists.mysql.com Subject: how to add foreign key in alter command Hi guys Please tell me the command syntax, how to add a colmmen foreign key in alter syntax thanks Hello Nathan, Try the syntax below: ALTER TABLE tbl ADD foreign key(x) references(x); N.B: Where (x) is your foreign key. Hope that helps. Greetings from Nigeria. Alugo Abdulazeez www.frangeovic.com _ Show them the way! Add maps and directions to your party invites. http://www.microsoft.com/windows/windowslive/products/events.aspx
RE: Confused about syntax for specific join with 3 tables
Date: Sat, 16 May 2009 15:39:56 -0700
From: davidmichaelk...@gmail.com
To: mysql@lists.mysql.com
Subject: Confused about syntax for specific join with 3 tables
I've been doing some experimenting with the data model from the MySQL
book (Addison Wesley). I have no trouble understanding joins between
two tables, but I'm finding it's a little confusing when 3 or more
tables are involved. I'm going to cite a particular set of tables and a
specific query. I would have assumed it would need to be one way, but
it actually requires a different approach, which I don't quite understand.
Here are the table creation scripts:
CREATE TABLE student
(
name VARCHAR(20) NOT NULL,
sex ENUM('F','M') NOT NULL,
student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (student_id)
) ENGINE = InnoDB;
CREATE TABLE grade_event
(
date DATE NOT NULL,
category ENUM('T','Q') NOT NULL,
event_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (event_id)
) ENGINE = InnoDB;
CREATE TABLE score
(
student_id INT UNSIGNED NOT NULL,
event_id INT UNSIGNED NOT NULL,
score INT NOT NULL,
score_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (score_id),
INDEX (student_id),
FOREIGN KEY (event_id) REFERENCES grade_event (event_id),
FOREIGN KEY (student_id) REFERENCES student (student_id)
) ENGINE = InnoDB;
So, the query I want to build will list the quiz (not test) scores for a
particular student.
If I were to construct this logically, I would think the query would
be this:
select score.score
from student left join score inner join grade_event
on student.student_id = score.student_id and grade_event.event_id =
score.event_id
where student.student_id = 1 and grade_event.category='Q';
I visualize it as student joining to score joining to grade_event.
Unfortunately, this query fails to parse with an unhelpful error message.
The query that works, with the joins out of the order I expected, is the
following:
select score.score
from student inner join grade_event left join score
on student.student_id = score.student_id and grade_event.event_id =
score.event_id
where student.student_id = 1 and grade_event.category='Q';
Can someone please go into detail of why what I first tried didn't work,
and why it needs to be the other way?
Hi David,
Well I could say it's probably because grade_event is a parent table while
score is a child table. And the parent joined first (you know, the deserved
respect) :)). Cheers.
Alugo Abdulazeez
www.frangeovic.com
_
Windows Live™: Keep your life in sync. Check it out!
http://windowslive.com/explore?ocid=TXT_TAGLM_WL_t1_allup_explore_012009
Problem setting up slow logging in my,ini file
I'm having a problem defining slow logging file in my.ini (Windows XP). I'm using MySQL 5.1.30. In the MySQL reference manual it says: As of MySQL 5.1.29, the --log-slow-queries option is deprecated and will be removed (along with the log_slow_queries system variable) in MySQL 7.0. Instead, use the --slow_query_log option to enable the slow query log and the --slow_query_log_file=file_name option to set the slow query log file name. So my.ini has: [mysqld] general_log=1 log-output=FILE general_log_file=LOG.TXT slow_query_log=1 slow_query_log_file=SLOWLOG.TXT After I restart MySQL, the variables have: Variable_nameValue --- -- log ON log_bin OFF log_output FILE log_queries_not_using_indexesOFF log_slow_queries ON log_warnings 1 slow_query_log ON slow_query_log_file OFF sql_log_bin ON sql_log_off OFF sql_log_update ON So why is the variable slow_query_log_file set to OFF because it is not boolean and should be a file name? Why isn't it set to the file name SLOWLOG.TXT? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem setting up slow logging in my,ini file
Try putting in a full existing pathname the server has write access to as opposed to just a file name. Walter Heck On Sun, May 17, 2009 at 12:13 PM, mos mo...@fastmail.fm wrote: I'm having a problem defining slow logging file in my.ini (Windows XP). I'm using MySQL 5.1.30. In the MySQL reference manual it says: As of MySQL 5.1.29, the --log-slow-queries option is deprecated and will be removed (along with the log_slow_queries system variable) in MySQL 7.0. Instead, use the --slow_query_log option to enable the slow query log and the --slow_query_log_file=file_name option to set the slow query log file name. So my.ini has: [mysqld] general_log=1 log-output=FILE general_log_file=LOG.TXT slow_query_log=1 slow_query_log_file=SLOWLOG.TXT After I restart MySQL, the variables have: Variable_name Value --- -- log ON log_bin OFF log_output FILE log_queries_not_using_indexes OFF log_slow_queries ON log_warnings 1 slow_query_log ON slow_query_log_file OFF sql_log_bin ON sql_log_off OFF sql_log_update ON So why is the variable slow_query_log_file set to OFF because it is not boolean and should be a file name? Why isn't it set to the file name SLOWLOG.TXT? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=li...@olindata.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: xtrabackup
Walt, I mentioned your question to Vadim Tkachenko who knows the most about it, and he suggested that it might be more appropriate to discuss on the percona-discuss...@googlegroups.com mailing list. Thanks, Baron On Fri, May 15, 2009 at 5:15 PM, Walt Weaver weaver1...@gmail.com wrote: Anyone using this? I'm looking to install it to see how it works. I read in some of the documentation that it could be used to set up a replication server and would automatically roll forward using binlogs. I've done rollforward using binlogs manually before but I was wondering what kind of mechanism xtrabackup uses to do this. I'm currently needing to restore a replication server and since there's no real hurry on it. I thought I'd give xtrabackup a try. Sounds like it could be a useful tool but the documentation I've found so far is a bit lacking as far as details are concerned. If anyone could give me some information, or point me to some good documentation, It would be much appreciated. Thanks, --Walt -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem setting up slow logging in my,ini file
At 01:22 PM 5/17/2009, Walter Heck - OlinData.com wrote: Try putting in a full existing pathname the server has write access to as opposed to just a file name. Walter Heck Walter, That didn't work either if I put it in the my.ini file. Without a path it should default the location to the mysql/data directory which is where I'd like it to be. The only way I got it to work was to set the global variable after MySQL was running. It doesn't not appear I can get to specify the slow log file from the my.ini file. Mike On Sun, May 17, 2009 at 12:13 PM, mos mo...@fastmail.fm wrote: I'm having a problem defining slow logging file in my.ini (Windows XP). I'm using MySQL 5.1.30. In the MySQL reference manual it says: As of MySQL 5.1.29, the --log-slow-queries option is deprecated and will be removed (along with the log_slow_queries system variable) in MySQL 7.0. Instead, use the --slow_query_log option to enable the slow query log and the --slow_query_log_file=file_name option to set the slow query log file name. So my.ini has: [mysqld] general_log=1 log-output=FILE general_log_file=LOG.TXT slow_query_log=1 slow_query_log_file=SLOWLOG.TXT After I restart MySQL, the variables have: Variable_name           Value ---  -- log                ON log_bin              OFF log_output            FILE log_queries_not_using_indexes   OFF log_slow_queries         ON log_warnings           1 slow_query_log          ON slow_query_log_file        OFF sql_log_bin            ON sql_log_off            OFF sql_log_update          ON So why is the variable slow_query_log_file set to OFF because it is not boolean and should be a file name?  Why isn't it set to the file name SLOWLOG.TXT? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:   http://lists.mysql.com/mysql?unsub=li...@olindata.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: CentOS 4.4 mysqld crash
I have opened a bug about this issue: http://bugs.mysql.com/bug.php?id=44914 Joel On Tue, May 12, 2009 at 10:55 PM, Joel Heenan jo...@planetjoel.com wrote: Mysql List, I have a crash that has occured a number of times on a production CentOS 4.4 machine. It occurs when using a product called Interspire Email Marketer and doing an import of some 30,000 email addresses. Unfortunately, I have not been able to reproduce the crash myself (I have tried numerous times). MySQL is version 4.1.22, latest for this version of CentOS. Should I open a bug report about this? Since it is production I can't do too much on the host itself and I have limited ability to reproduce. Here is what the crash looks like: Version: '4.1.22' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=402653184 read_buffer_size=1044480 max_used_connections=82 max_connections=500 threads_connected=44 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1927212 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x950dc9c8 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0x961626b4, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8139eec 0x4f6e67 0x831b884 0x81c22a7 0x81c3150 0x81b5f53 0x814f934 0x8151b3c 0x815255a 0x8152db5 0x815375b 0x4f5371 0x3f5ffe New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x9c20480 = SAVEPOINT interspire4a00f4b153ccf thd-thread_id=108208 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 090506 11:23:45 mysqld restarted Here is what the /etc/my.cnf file looks like: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 back_log = 75 skip-innodb max_connections = 500 key_buffer = 384M myisam_sort_buffer_size = 64M join_buffer_size = 1M read_buffer_size = 1M sort_buffer_size = 2M table_cache = 1800 thread_cache_size = 384 wait_timeout = 7200 connect_timeout = 10 tmp_table_size = 64M max_heap_table_size = 64M max_allowed_packet = 64M max_connect_errors = 1000 read_rnd_buffer_size = 524288 bulk_insert_buffer_size = 8M query_cache_limit = 4M query_cache_size =128M query_cache_type = 1 query_prealloc_size = 65536 query_alloc_block_size = 131072 default-storage-engine = MyISAM [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid nice = -5 open_files_limit = 8192 [myisamchk] tmpdir=/home/mysqltmp key_buffer = 64M sort_buffer = 64M read_buffer = 16M write_buffer = 16M [mysqldump] quick max_allowed_packet = 16M Thanks Joel
If condition in select query / insert /update
Hi all , Can u give one example query , Which contain the IF condition , Because here before am not used the IF and all , Thanks -- உங்கள் நண்பன் பரணி குமார் Regards B.S.Bharanikumar POST YOUR OPINION http://bharanikumariyerphp.site88.net/bharanikumar/
