RE: PgSQL vs MySQL
As a DBA I have a few questions about what you said here. You have worked with both PostgreSQL and MySQL, and yet you say that MySQL is 'signifigantly' faster than Oracle? Can you PROVE that? And how is it faster to chase down data problems when MySQL has no native constraints in it data design? Wouldn't you think that since MySQL is 'simpler' to set up and configure that it just lends itself to poor design principles and that you will constantly be fighting with it after a point? Or migrating to something else? Can't argue with the third point at all. In all of this, isn't it really InnoDB that you like, and not MySQL specifically? -James -Original Message- From: David Griffiths [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 10:28 AM To: [EMAIL PROTECTED]; Mark Warner; [EMAIL PROTECTED] Subject: Re: PgSQL vs MySQL As a DBA and someone who has worked both with PostgresQL and MySQL, I think I can answer this knowingly. First, MySQL is significantly faster than PostgresQL and Oracle. Second, MySQL is also a simpler database to set up and configure. Third, the documentation is better, and there are far more third party books out there. Fourth, MySQL has a more impressive list of customers. Yes, there are some large PostgresQL customers (the .org domain system?), but none like Yahoo and Slashdot. MySQL does not have triggers, stored procedures or views yet. Sub-selects should be out in six months. After fighting with PostgresQL to try to get it to use indexes, rewriting tonnes of queries, and still getting poor performance, I gave up on it. I prefer MySQL with InnoDB. Some of the gotcha's are valid, and others can be found in any database. Forewarned is forearmed. David. - Original Message - From: [EMAIL PROTECTED] To: Mark Warner [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 6:51 AM Subject: Re: PgSQL vs MySQL What advantages, besides ease of setup, does MySQL hold over PostgreSQL? It would seem, to me, that the two are close competitors (both in quality, and performance). Are you sure about quality? Check out: http://sql-info.de/mysql/gotchas.html You can check out postgresql's on the same site but they are significally less critical. After reviewing this and talking to some other people i'm switching over to postgresql. I'm sure there is a place with mysql but I don't think i'd trust it for anything critical unless you very confident your developers know what they are doing. -- 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: Can't create a new thread (errno 11)
Check the max_connections variable Mysql show variables liks '%connection%'; -James -Original Message- From: Christof Egner [mailto:[EMAIL PROTECTED] Sent: Friday, February 20, 2004 9:03 AM To: [EMAIL PROTECTED] Subject: Can't create a new thread (errno 11) Hello, I'm having problems with a 4.0.18 MySQL Server. Since this afternoon I get the error Can't create a new thread (errno 11) when MySQL has forked about 200-300 threads. What does it mean? I'm not running out of RAM or disk space. Best Regards, Christof -- 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]
Locking
How is 'next-key' locking correctly advertised as 'row-level' locking? I don't actually see that InnoDB has row-level locking at all. Am I totally wrong on that? -James
RE: Locking
Does it have exclusive and shared? -James -Original Message- From: Chris Nolan [mailto:[EMAIL PROTECTED] Sent: Monday, February 16, 2004 4:52 AM To: James Kelty Cc: [EMAIL PROTECTED] Subject: Re: Locking Hi! Next-key locking essentially doesn't work on rows - it works on indexes. It ensures that phantom reads can't happen. InnoDB does indeed do row-locking. In fact, it has one of the most efficient representations of locks of any relational database. Regards, Chris On Mon, 2004-02-16 at 23:43, James Kelty wrote: How is 'next-key' locking correctly advertised as 'row-level' locking? I don't actually see that InnoDB has row-level locking at all. Am I totally wrong on that? -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 3.23 redhat 9 server install
Try /usr/bin/safe_mysqld The mysqld_safe is 4.0.x version. Also, try reading the installtion instructions and check out the manual online at mysql.com -James -Original Message- From: Claritinet.com [mailto:[EMAIL PROTECTED] Sent: Sunday, February 15, 2004 11:28 AM To: [EMAIL PROTECTED] Subject: 3.23 redhat 9 server install hi, i have installed the redhat 9 server with php, apache 2, perl and of course mysql 3.23.54. my problem is i can't access it or start it or anything. so i tried to reinstall and it says its already there. find /usr/ -name mysql_safe tried this and i get nothing tried this start mysql server again bash: start: command not found tried this rpm -ivh MySQL-client-3.23.58-1.i386.rpm get this file /usr/bin/mysqladmin from install of MySQL-client-3.23.58-1 conflict s with file from package mysql-server-3.23.54a-11 tried this and get ./scripts/mysql_install_db bash: ./scripts/mysql_install_db: No such file or directory mysql is installed i just cant start it or seem to do anything. does anyone have any ideas regards blair -- 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]
Massive memory utiliazation
Hello, We have currently tuned MySQL for a high rate of traffic. But, now we are seeing issues with memory usage. It reaches about 2GB and the server becomed wildly unstable. Below is our my.cnf file. Can anyone point out any glarring errors? We are running this on a Dell 2650 with Red Had Advanced Server v2.1 with Kernel 2.4.9-e.25smp and Hyper threading. Thanks a lot! [client] port= 3306 # The MySQL server [mysqld] datadir = /var/lib/mysql port= 3306 skip-locking set-variable= max_connections=800 set-variable= key_buffer=1500M set-variable= max_allowed_packet=1M set-variable= table_cache=16384 set-variable= sort_buffer=256k set-variable= record_buffer=256k set-variable= record_rnd_buffer=256k set-variable= thread_cache=64 set-variable= thread_concurrency=32 set-variable= myisam_sort_buffer_size=64M set-variable= interactive_timeout=300 set-variable= open_files_limit=6 set-variable= wait_timeout=300 set-variable= long_query_time=5 set-variable= tmp_table_size=16M server-id = 0 # Adding bin log for PIT recovery log-bin #set-variable = bdb_cache_size=768M #set-variable = bdb_max_lock=10 log-slow-queries=/var/log/slowqueries.log [safe_mysqld] open-files-limit=6 [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash [isamchk] set-variable= key_buffer=512M set-variable= sort_buffer=512M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=512M set-variable= sort_buffer=512M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout
Daylight Savings Time
Hello, We had a strange instance happen here with one of our MySQL servers. We have multiple MySQL installations, and one of the MySQL servers had a problem with daylight savings time. Basically, when we ran a select now(); MySQL reported time that was two hours ahead of the actual time. The system /bin/date command was correct however. We restarted the MySQL server and the time was corrected. I have looked at 'most' of our other servers, and none of them (so far) has had this issue. I am just wondering if anyone else has seen this problem? -James signature.asc Description: This is a digitally signed message part
Re: Here is a strange one...
This is the output from check table ArpMon; mysql check table ArpMon; +--+---+--+-+ | Table| Op| Msg_type | Msg_text| +--+---+--+-+ | arpdb.ArpMon | check | warning | 0 clients is using or hasn't closed the table properly | | arpdb.ArpMon | check | warning | Size of datafile is: 1796436 Should be: 1796176 | | arpdb.ArpMon | check | error| Keypointers and record positions doesn't match | | arpdb.ArpMon | check | warning | Found 27507 parts Should be: 27498 parts | | arpdb.ArpMon | check | error| Corrupt | +--+---+--+-+ 5 rows in set (0.85 sec) -James On Wed, 2003-09-03 at 18:41, Paul DuBois wrote: At 16:49 -0700 9/3/03, James Kelty wrote: So, we have this table: ArpMon that looks like this: -- mysql describe ArpMon; ++---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++---+--+-+-+---+ | am_mac | varchar(20) | | PRI | | | | am_ip | varchar(255) | | PRI | | | | am_rtr | varchar(255) | YES | | NULL| | | am_if | int(10) | YES | | NULL| | | am_ts | timestamp(14) | YES | | NULL| | ++---+--+-+-+---+ When I do a count(*) on this table, we get this result: mysql select count(*) from ArpMon; +--+ | count(*) | +--+ |27498 | +--+ 1 row in set (0.01 sec) Ok, that's good. But! If I run select * from ArpMon, I only get 111 entries That's odd. and if I run select count(am_rtr) from ArpMon, I get 111 entries as well as That could, if you have only 111 non-NULL am_rtr values. select am_rtr from ArpMon. But! It I run select am_mac from ArpMon, I get the 27498 entries. What's up with that? Can someone help me to figure this one out? What does CHECK TABLE ArpMon tell you? -James James Kelty E-Commerce / Financial Systems Administrator Portland State University 503.725.9152 [EMAIL PROTECTED] -- James Kelty E-Commerce / Financial Systems Administrator Portland State University 503.725.9152 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Killing Queries...
I was wondering of there is a way to kill multiple processes in MySQL rather than one at a time? -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Here is a strange one...
So, we have this table: ArpMon that looks like this: -- mysql describe ArpMon; ++---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++---+--+-+-+---+ | am_mac | varchar(20) | | PRI | | | | am_ip | varchar(255) | | PRI | | | | am_rtr | varchar(255) | YES | | NULL| | | am_if | int(10) | YES | | NULL| | | am_ts | timestamp(14) | YES | | NULL| | ++---+--+-+-+---+ When I do a count(*) on this table, we get this result: mysql select count(*) from ArpMon; +--+ | count(*) | +--+ |27498 | +--+ 1 row in set (0.01 sec) Ok, that's good. But! If I run select * from ArpMon, I only get 111 entries and if I run select count(am_rtr) from ArpMon, I get 111 entries as well as select am_rtr from ArpMon. But! It I run select am_mac from ArpMon, I get the 27498 entries. What's up with that? Can someone help me to figure this one out? -James James Kelty E-Commerce / Financial Systems Administrator Portland State University 503.725.9152 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Many Read and Writes...
So, we have a webmail application that uses a mysql server for holding it's session information. Obviously this causes many reads,updates,inserts, and deletes to happen. Here are my lock statistics. | Table_locks_immediate| 73099 | | Table_locks_waited | 32187 | This ratio seems REALLY bad to me. I'm wondering if there is anything I can do to tune the server a little. I have 4G of memory on my system, but MySQL only seems to be using about 64M. If giving it more would help with this a little, how do I do that? I have almost a 30 second avg on queries right now, and it's basically making my webmail stuff crap out. Should I just move the sessions away from the database? -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Many Read and Writes...
Well, there isn't a my.cnf file, so other that setting the max_connections with the -O option, it's whatever is default for 3.23.56. -James On Tue, 2003-09-02 at 10:50, Jeremy Zawodny wrote: On Tue, Sep 02, 2003 at 10:37:34AM -0700, James Kelty wrote: So, we have a webmail application that uses a mysql server for holding it's session information. Obviously this causes many reads,updates,inserts, and deletes to happen. Here are my lock statistics. | Table_locks_immediate| 73099 | | Table_locks_waited | 32187 | This ratio seems REALLY bad to me. It's not good. Especially if that's a small number of tables. I'm wondering if there is anything I can do to tune the server a little. I have 4G of memory on my system, but MySQL only seems to be using about 64M. If giving it more would help with this a little, how do I do that? What's your my.cnf file look like? How lagre are the data and index files? Jeremy -- James Kelty E-Commerce / Financial Systems Administrator Portland State University 503.725.9152 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Many Read and Writes...
Whoa, ok. Sorry. I didn't read the questions about the data and index files. I'm, uh, not exactly sure how to tell that, can you give me a hint there as well? *look sheepishly around*... -James On Tue, 2003-09-02 at 11:06, Jeremy Zawodny wrote: On Tue, Sep 02, 2003 at 10:58:03AM -0700, James Kelty wrote: Well, there isn't a my.cnf file, so other that setting the max_connections with the -O option, it's whatever is default for 3.23.56. Since you didn't answer the other questions, I'm going to do some guessing here... I'll guess that you have a lot of data and that MySQL hasn't allocated enough memory for its key buffer. That means it hitting the disk more often than it needs to and is slowing things down. I'd suggest setting up a my.cnf file with a larger key buffer to test performance. You might look at mytop, since it'll show your key buffer hit percentage: http://jeremy.zawodny.com/mysql/mytop/ See the sample my.cnf files that come with MySQL. One of them will likely be a good starting point for you. Jeremy On Tue, 2003-09-02 at 10:50, Jeremy Zawodny wrote: On Tue, Sep 02, 2003 at 10:37:34AM -0700, James Kelty wrote: So, we have a webmail application that uses a mysql server for holding it's session information. Obviously this causes many reads,updates,inserts, and deletes to happen. Here are my lock statistics. | Table_locks_immediate| 73099 | | Table_locks_waited | 32187 | This ratio seems REALLY bad to me. It's not good. Especially if that's a small number of tables. I'm wondering if there is anything I can do to tune the server a little. I have 4G of memory on my system, but MySQL only seems to be using about 64M. If giving it more would help with this a little, how do I do that? What's your my.cnf file look like? How lagre are the data and index files? Jeremy -- James Kelty E-Commerce / Financial Systems Administrator Portland State University 503.725.9152 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 32 days, processed 1,373,779,139 queries (496/sec. avg) -- James Kelty E-Commerce / Financial Systems Administrator Portland State University 503.725.9152 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Many Read and Writes...
Ahhh! Ok, yeah the index file was 1.0k and the data file was 8.6M. -James On Tue, 2003-09-02 at 12:17, Jeremy Zawodny wrote: On Tue, Sep 02, 2003 at 11:23:57AM -0700, James Kelty wrote: Whoa, ok. Sorry. I didn't read the questions about the data and index files. I'm, uh, not exactly sure how to tell that, can you give me a hint there as well? *look sheepishly around*... Sure. First you need to figure out where MySQL is storing your data files. You can find the value of datadir in the output of SHOW VARIABLES. In that directory, you'll see a sub-directory for each database. And each MyIAM table is composed of three files: table.MYI - indexes table.MYD - data table.frm - table definition Find out how large your various indexs are by doing something like: du -sk *.MYI Jeremy -- James Kelty E-Commerce / Financial Systems Administrator Portland State University 503.725.9152 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slave server and InnoDB
sql,query Hello. Is there an option for the slave server to NOT stop when an error inducing query is run on the master? For instance, if someone tries in insert a duplicate entry, and the master shows the error, but updates the binlog, how can I keep the slave from seeing the same error, and NOT stopping itself? Thanks! -James - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
JDBC vs. Command Line Access
sql,query Ok, this is a little weird. From system A I can access the Database on System B via the mysql command line with the -h option. I can insert into a table, create a table, and drop tables. But! Through the JDBC interface using the same user/pass, I get an access denied message. I believe that my permissions are ok. Can anyone point me in another direction to turn? MySQL Version 2.32.51 Thanks! -James - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: JDBC vs. Command Line Access
Thanks to those on the list that looked into it. Apparently the developer was trying to do an insert on a read only connection. Damn. Thanks again! -James On Wed, 2002-10-02 at 11:26, James Kelty wrote: sql,query Ok, this is a little weird. From system A I can access the Database on System B via the mysql command line with the -h option. I can insert into a table, create a table, and drop tables. But! Through the JDBC interface using the same user/pass, I get an access denied message. I believe that my permissions are ok. Can anyone point me in another direction to turn? MySQL Version 2.32.51 Thanks! -James - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Stopping Running Queries...
mysql,query Hello, Maybe I'm an idiot, but I can't find any documentation on how to stop running queries interactively. The only thing I can come up with is to stop and restart the server. I don't want to do that. Basically, if a user does something stupid, and sparks off a query that is hogging resources, I'd like to be able to kill that query so everyone else can get on with life. How is this, if it can be done, accomplished? Thanks! -James - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication Problem
Hello, I have this replication problem. I am getting this error on the slave: 020816 16:30:49 Slave: Failed reading log event, reconnecting to retry, log 'FIRST' position 4 020816 16:30:49 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'FIRST' at position 4 020816 16:30:49 Error reading packet from server: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) (server_errno=1045) But I have verified through the command line that this user has the ability to contact the master, use the database, and that the password in /etc/my.cnf if correct. Any other reason that this is failing? -James sql query James Kelty Director of Operations Everbase Systems, LLC 624 A Street Ashland, OR 97520 [EMAIL PROTECTED] 541.488.0801 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Replication Problem
Apparently I had list File permissions with my user. Thanks anyway. -James -Original Message- From: James Kelty [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 11:06 AM To: [EMAIL PROTECTED] Subject: Replication Problem Hello, I have this replication problem. I am getting this error on the slave: 020816 16:30:49 Slave: Failed reading log event, reconnecting to retry, log 'FIRST' position 4 020816 16:30:49 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'FIRST' at position 4 020816 16:30:49 Error reading packet from server: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) (server_errno=1045) But I have verified through the command line that this user has the ability to contact the master, use the database, and that the password in /etc/my.cnf if correct. Any other reason that this is failing? -James sql query James Kelty Director of Operations Everbase Systems, LLC 624 A Street Ashland, OR 97520 [EMAIL PROTECTED] 541.488.0801 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Server Error
Hello, Does anyone know, off the top of their head, what server_errno=1159 on a SLAVE means? Replication is working, but I just want to know what that is. Thanks. -James sql query James Kelty Director of Operations Everbase Systems, LLC 624 A Street Ashland, OR 97520 [EMAIL PROTECTED] 541.488.0801 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Server Error
Thanks alot! -James -Original Message- From: Oscar Rylin [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 3:05 PM To: 'James Kelty'; [EMAIL PROTECTED] Subject: RE: Server Error http://www.geocrawler.com/archives/3/8/2002/6/0/9054036/ / Oscar Rylin -Original Message- From: James Kelty [mailto:[EMAIL PROTECTED]] Sent: den 16 augusti 2002 22:57 To: [EMAIL PROTECTED] Subject: Server Error Hello, Does anyone know, off the top of their head, what server_errno=1159 on a SLAVE means? Replication is working, but I just want to know what that is. Thanks. -James sql query James Kelty Director of Operations Everbase Systems, LLC 624 A Street Ashland, OR 97520 [EMAIL PROTECTED] 541.488.0801 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql Table Case
Hello, Is there a compile option or startup option to make mysql ignore table name case? So that the tables security and Security would be seen as the same table? -James James Kelty Director of Operations Everbase Systems, LLC 624 A Street Ashland, OR 97520 [EMAIL PROTECTED] 541.488.0801 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Mysql Table Case
Well, I guess that ISN'T really the case. http://www.mysql.com/doc/en/Name_case_sensitivity.html Sorry that I asked the question at all, but thanks for the replies. -James -Original Message- From: Nilesh Shah [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 15, 2002 10:20 AM To: James Kelty; [EMAIL PROTECTED] Subject: RE: Mysql Table Case Answer is No. Case-sensitivity of table names depends on operating system you are running mysql on.MySQL stores table definitions in TABLENAME.* files. So it will always be case-sensitive on *nix and case-insensitive on windows. Nilesh -Original Message- From: James Kelty [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 15, 2002 12:59 PM To: [EMAIL PROTECTED] Subject: Mysql Table Case Hello, Is there a compile option or startup option to make mysql ignore table name case? So that the tables security and Security would be seen as the same table? -James James Kelty Director of Operations Everbase Systems, LLC 624 A Street Ashland, OR 97520 [EMAIL PROTECTED] 541.488.0801 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication w/ InnoDB
Ok, ok, now that I know that I 'can' replicate InnoDB tables, it's time I got it to ACTUALLY replicate InnoDB tables, eh? This is my /etc/my.cnf file from the master server: [mysqld] log-bin=/storage/mysql/replication.log server-id=1 default-table-type=innodb innodb_data_home_dir=/storage/innodb set-variable = innodb_mirrored_log_groups=1 # # innodb_data_file_path=indata1:2000M;indata2: data5:2000M;indata6:2000M # # set-variable = innodb_buffer_pool_size=128M set-variable = innodb_additional_mem_pool_si innodb_log_group_home_dir = /storage/innodbl # # innodb_log_arch_dir = /storage/innodblogs innodb_log_archive = 0 set-variable = innodb_log_files_in_group=3 # # set-variable = innodb_log_file_size=100M set-variable = innodb_log_buffer_size=16M # # # innodb_flush_log_at_trx_commit=1 set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 [mysql.server] user=mysql basedir=/usr/local/mysql [safe_mysqld] err-log=/storage/mysql/mysql_error_log pid-file=/storage/mysql/mysqld.pid And Here is my /etc/my.cnf file from the SLAVE: [mysqld] server-id=2 master-host=192.168.10.49 master-user=repl master-password=replicate master-connect-retry=5 master-info-file=master.info replicate-do-db=everbase log-slave-updates #skip-slave-start default-table-type=innodb innodb_flush_log_at_trx_commit=0 innodb_data_home_dir=/database/MySQL/innodb set-variable = innodb_mirrored_log_groups=1 # # innodb_data_file_path=indata1:2000M:autoextend:max # # set-variable = innodb_buffer_pool_size=128M set-variable = innodb_additional_mem_pool_size=80M innodb_log_group_home_dir = /database/MySQL/innodb # # innodb_log_arch_dir = /database/MySQL/innodblogs innodb_log_archive = 0 set-variable = innodb_log_files_in_group=3 # # set-variable = innodb_log_file_size=100M set-variable = innodb_log_buffer_size=16M # # # innodb_flush_log_at_trx_commit=1 set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 I have select, and file permissions for the 'repl' user, and when I try the sql query LOAD TABLE Users FROM MASTER on the slave, all I get is the replicated table is created, but NO data comes accross, and I get the 1189 Net Read Error on the slave. BUT! When I change the type of table from InnoDB to MyISAM on the MASTER SERVER, the above LOAD TABLE works REALLY well. Not sure why this is happening. Plus, NONE of the changes I make to the table after altering it to the MyISAM type, replicate? What am I missing. I would really like the replication to work well with InnoDB types instead of MyISAM. Please, can someone point me in the right direction? Thanks! -James - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication , InnoDB, 3.23.51
Hello! I found a small thread from 2 years ago that said that replication was restricted to MyISAM table types. Is this true? Can I not replicate InnoDB table types? The reason I ask is that I am getting a the 1189: Net Read Error when I run: LOAD TABLE name FROM MASTER; Is there a work around for this at all? Replication is REALLY key here, as is InnoDB. (We need the foreign keys, and transaction support, as WELL as replication). Thanks a lot to anyone who replies! -James P.S. I am STILL looking in the doc's, but I just thought that I would ask! Thanks again! sql query (to satify spam filter) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Error 150 on 3.23.51
Thanks for the response! Actually, we did notice the on delete null issue, and we even found another. Basically, we have been using PostgreSQL (yikes!), and it just wasn't as 'smart' as MySQL w/ InnoDB. We could actually create foreign keys on tables that didn't even exist yet! But! MySQL to the rescue! It errored out (as you would expect). Thanks for all the help! -James -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sent: Saturday, July 13, 2002 1:11 AM To: [EMAIL PROTECTED] Subject: Re: Error 150 on 3.23.51 James, - Original Message - From: James Kelty [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Saturday, July 13, 2002 12:43 AM Subject: Error 150 on 3.23.51 Hello, We have compiled in InnoDB support for MySQL 3.23.51. We would LOVE to take advantage of the transactions and foreign key assignments that it provides. We are having an issue, with the following SQL, however: All of the alter table statements are ending with this error: ERROR 1005: Can't create table './everbase/#sql-955_b.frm' (errno: 150) I found references to a bug that was fixed in .51, but I can't find anything as to what is going on. We think we have the syntax right, but some help would be appreciated. Thanks! -James P.S. I also included my my.cnf file as well and the bottom. create table FailedPaymentContact ( FailedPaymentContactGenKey int AUTO_INCREMENT PRIMARY KEY, ClearingHouseCreditCardFKey int NOT NULL, INDEX iFailedPaymentContact_ClearingHouseCreditCardFKey(ClearingHouseCreditCardFKe y), ContactDate timestamp NOT NULL, ValidContactTypeFKey int NOT NULL, INDEX iFailedPaymentContact_ValidContactTypeFKey(ValidContactTypeFKey), EmployeeFKey varchar(50) NOT NULL, INDEX iFailedPaymentContact_EmployeeFKey(EmployeeFKey) ) TYPE = InnoDB; ALTER TABLE FailedPaymentContact ADD CONSTRAINT FK_FailedPaymentContact_N400038 FOREIGN KEY ( ClearingHouseCreditCardFKey ) REFERENCES ClearingHouseCreditCard (ClearingHouseCreditCardGenKey) ON DELETE SET NULL ON UPDATE CASCADE ; you have specified ClearingHouseCreditCardFKey int NOT NULL but want it to be set NULL on the delete of the parent row: ON DELETE SET NULL. At least this will produce error 150. InnoDB does not support ON UPDATE CASCADE. Are there ever updates of referenced keys? Usually referenced keys are primary keys, and primary key updates are rare. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Error 150 on 3.23.51
Hello, We have compiled in InnoDB support for MySQL 3.23.51. We would LOVE to take advantage of the transactions and foreign key assignments that it provides. We are having an issue, with the following SQL, however: All of the alter table statements are ending with this error: ERROR 1005: Can't create table './everbase/#sql-955_b.frm' (errno: 150) I found references to a bug that was fixed in .51, but I can't find anything as to what is going on. We think we have the syntax right, but some help would be appreciated. Thanks! -James P.S. I also included my my.cnf file as well and the bottom. create table FailedPaymentContact ( FailedPaymentContactGenKey int AUTO_INCREMENT PRIMARY KEY, ClearingHouseCreditCardFKey int NOT NULL, INDEX iFailedPaymentContact_ClearingHouseCreditCardFKey(ClearingHouseCreditCardFKe y), ContactDate timestamp NOT NULL, ValidContactTypeFKey int NOT NULL, INDEX iFailedPaymentContact_ValidContactTypeFKey(ValidContactTypeFKey), EmployeeFKey varchar(50) NOT NULL, INDEX iFailedPaymentContact_EmployeeFKey(EmployeeFKey) ) TYPE = InnoDB; ALTER TABLE FailedPaymentContact ADD CONSTRAINT FK_FailedPaymentContact_N400038 FOREIGN KEY ( ClearingHouseCreditCardFKey ) REFERENCES ClearingHouseCreditCard (ClearingHouseCreditCardGenKey) ON DELETE SET NULL ON UPDATE CASCADE ; ALTER TABLE FailedPaymentContact ADD CONSTRAINT FK_FailedPaymentContact_N400041 FOREIGN KEY ( EmployeeFKey ) REFERENCES Employee (EmployeeID) ON DELETE SET NULL ON UPDATE CASCADE ; ALTER TABLE FailedPaymentContact ADD CONSTRAINT FK_FailedPaymentContact_N40004A FOREIGN KEY ( ValidContactTypeFKey ) REFERENCES ValidContactType (ContactTypeID) ON DELETE SET NULL ON UPDATE CASCADE ; MY.CNF [mysql@warbaby innodblogs]$ cat /etc/my.cnf [mysqld] log-bin=/storage/mysql/ server-id=1 default-table-type=innodb innodb_data_home_dir=/storage/innodb set-variable = innodb_mirrored_log_groups=1 # # innodb_data_file_path=indata1:2000M;indata2:2000M;indata3:2000M;indata4:2000 M;indata5:2000M;indata6:2000M # # set-variable = innodb_buffer_pool_size=128M set-variable = innodb_additional_mem_pool_size=80M innodb_log_group_home_dir = /storage/innodblogs # # innodb_log_arch_dir = /storage/innodblogs innodb_log_archive = 0 set-variable = innodb_log_files_in_group=3 # # set-variable = innodb_log_file_size=100M set-variable = innodb_log_buffer_size=16M # # # innodb_flush_log_at_trx_commit=1 set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
