MySQL 5.0, func stored in proc table
Hi, I compiled MySQL 5.0 from the source tree to test how stored procedures work. When I create a function, It is not stored in de mysql.func table, but in the mysql.proc table. Example of the function a create: create function fun(a int) returns int begin set a = a + 1; return a; end; Is this a bug or do I do something wrong? gr. Bob http://www.beheervisie.nl/disclaimer. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Enabling Transactions
Dan Anderson [EMAIL PROTECTED] wrote: I am having a problem in one of my scripts and I need to use transactions to fix it. When I looked it up on the mySQL documentation I found: If you are using transaction-safe tables (like InnoDB or BDB), you can put MySQL into non-autocommit mode with the following command: Does this mean that transactions will not work if I type in a CREATE TABLE command? CREATE TABLE just creates new tables in the database. If you want to turn off autocommit mode (to use transaction) you should execute SET AUTOCOMMIT=0. You can also start transaction with BEGIN, BEGIN WORK or START TRANSACTION commands: http://www.mysql.com/doc/en/COMMIT.html Are there any problems with switching to InnoDB or BDB? There are some restrictions on InnoDB and BDB tables. They are listed at: http://www.mysql.com/doc/en/BDB_restrictions.html http://www.mysql.com/doc/en/InnoDB_restrictions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL installation and starting problems on LynxOS
Hello, I tried installing MySQL on LynxOS, and it went fine. After installation I started mysqld and but was not able to communicate with it. When I ran mysqladmin it reported the following arjuna# /usr/local/mysql/bin/mysqladmin version /usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed error: 'Lost connection to MySQL server during query' arjuna# /usr/local/mysql/bin/mysqladmin version /usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed error: 'Lost connection to MySQL server during query' Then I tried telnet to 3306 port on which mysqld was listening arjuna# telnet arjuna 3306 Trying 192.168.0.21 ... Connected to arjuna. Escape character is '^]'. , 4.0.15a-log*VQtulA, Bad handshakeConnection closed by foreign host. arjuna# telnet arjuna 3306 Trying 192.168.0.21 ... Connected to arjuna. Escape character is '^]'. , 4.0.15a-logOVB8%N=, Bad handshakeConnection closed by foreign host. Server side log at time were arjuna# tail -f arjuna.err Version: '4.0.15a-log' socket: '/tmp/mysql.sock' port: 3306 Number of processes running now: 0 030930 12:47:06 mysqld restarted /usr/local/mysql/libexec/mysqld: File or directory doesn't exist 030930 12:47:06 mysqld ended 030930 12:47:37 mysqld started /usr/local/mysql/libexec/mysqld: ready for connections. Version: '4.0.15a-log' socket: '/tmp/mysql.sock' port: 3306 030930 12:55:28 Error on new connection socket: Socket operation on non-socket 030930 12:55:33 Error on new connection socket: Socket operation on non-socket Could some one help me to resolve this?? Regards Sp.Raja
loading data from a textfile into MySQL database
Greetings, I have a datafile called 'salary.txt', and it's a tab delimited file, the structure is like this: employee_nametabsalaryenter employee_nametabsalaryenter ... I have a table called 'salary', and it has the same structure as the 'salary.txt' --- employee_name salary as the columns; I use the command LOAD DATA INFILE salary.txt INTO TABLE salary; After doing that, I do the 'SELECT * FROM databname' to look at the data I loaded. Although the data are there and can be found out by the SELECT query, the layout seems NOT *tidy* at all comparing with other rows typed in by hand directly using the INSERT command. Please advise how to solve this problem. Do you think it's so messy to load data from a text file to a table? cheers, feng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Connect mySQL
Hi geniuses, i have 2 server A B both have mysql running, is there anyway if i want to use web application on A and connect mysql on B. I think i need some code example, thanks - Original Message - From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 30, 2003 4:50 PM Subject: Re: Enabling Transactions Dan Anderson [EMAIL PROTECTED] wrote: I am having a problem in one of my scripts and I need to use transactions to fix it. When I looked it up on the mySQL documentation I found: If you are using transaction-safe tables (like InnoDB or BDB), you can put MySQL into non-autocommit mode with the following command: Does this mean that transactions will not work if I type in a CREATE TABLE command? CREATE TABLE just creates new tables in the database. If you want to turn off autocommit mode (to use transaction) you should execute SET AUTOCOMMIT=0. You can also start transaction with BEGIN, BEGIN WORK or START TRANSACTION commands: http://www.mysql.com/doc/en/COMMIT.html Are there any problems with switching to InnoDB or BDB? There are some restrictions on InnoDB and BDB tables. They are listed at: http://www.mysql.com/doc/en/BDB_restrictions.html http://www.mysql.com/doc/en/InnoDB_restrictions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ___ This mail is protected by Silicon Communications S/B The information contained in this message maybe confidential and protected from disclosure. If you are not the intended recipient of this message, please delete this message immediately. You are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. ~~~This email has been scanned by our anti-virus system. For precaution, please make sure you scan every attachment in this email. Please use at your own risk. Thank you. :) Mailadmin~~~ ___ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB / Linux
Hi, Its RedHat 7.3 with a 2.4 kernel. Yes the filesystem is ext3, but if your recommending a different one then I'm open to suggestions. This box is purely for mysql so anything that will benefit the database is best. As I said the disk size is not too much of a problem but its really the memory one, under a large amount of connections the 2Gb limit will be exceeded and I run the risk of crashing the mysql process, this is what I really want to get around. Best Regards, Marvin. -Original Message- From: William R. Mussatto [mailto:[EMAIL PROTECTED] Sent: 30 September 2003 00:07 To: [EMAIL PROTECTED] Subject: Re: InnoDB / Linux Marvin Wright said: Hi, I'm in the process of setting up a new database server that will run on redhat linux. The machine will be dual processor with 4GB ram and about 16GB disk. The machine is going to be used purely with InnoDB tables and will have a few very large tables acting as cache data. The amount of data I want to store will be between 2 and 4 GB to start with but might grow larger. I've been reading alot on how to set up InnoDB and have come across the 2GB limit problem. There is actually 2 problems here. 1. From reading many articles Linux may or may not support files larger than 2GB. 2. There is a problem with glibc that a process may become unstable if a process allocates more than 2GB. The 1st one isn't a problem, I can have 2 data files of 2GB, but I would like to overcome this issue. The second is where I'm stuck on, the InnoDB configuration page gives a nice formula that you should use so that you can calculate how much memory you should use. It gives an example configuration but this exceeds the 2GB limit even with only 200 concurrent connections. I really need to get the connections to something like 1000 without going over the limit. What configuration can be used and how can this be achieved ? Additionally I have read that each linux thread has a stack of 2MB, this is taken into account in the formula, this can be changed as I understand by changing a #define somewhere and recompiling the kernel and then recompiling the mysql server. Any input would be greatly appreciated. Best Regards, Marvin Wright Depends on: your version of Linux, File system and processor. I believe that the basic 2 GB limit is gone in Linux 2.4 with ext2 or ext3 file system. You may have to turn on some flags or define an estimated table size to cause mysql to use large enough pointers, but I thought that InnoDB could use multiple extants each of which could be up to 2 GB. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GRANT update query: Updating host access entry for users but retaining existing passwords
Hi, I've got a MYSQL 3.23.x setup that has approx 4000 database and 4000 user accounts. 1 database per user. I created each userid from a script of the form CREATE DATABASE mdb_userid; GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX,RELOAD ON mdb_userid.* TO [EMAIL PROTECTED] IDENTIFIED BY 'passwd'; GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX,RELOAD ON mdb_userid.* TO [EMAIL PROTECTED] IDENTIFIED BY 'passwd'; FLUSH PRIVILEGES; (this is part of a much longer script that I pass thru to mysql and I'm using dummy/example userid,passwd and host entries in the above). I now need to grant additional access from a number of subnets for each of the users in the database to their own database. I wanted to use a statement of the form GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER,INDEX,RELOAD ON mdb_userid.* TO userid@'192.168.10.0/255.255.255.0' IDENTIFIED BY 'passwd'; But the issue is that I do not have the plain text versions of each of the users password; as many have changed over the years. So I can't use the latter format with IDENTIFIED BY as I want to retain existing passwds. If I just do the above without having an IDENTIFIED BY entry then the users get a blank passwd which is definetly a no-no in my environment. Ideally what I'd like is a quick and simple way to grant users access from the new subnets but to retain their existing passwords - and without me having to extract all their existing encrypted passwords and then inserts them in afterwards in the appropriate tables - I prefer using GRANT. Is there anyway I can what I'm after? many thanks Shin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Possible Commit Syntax Change for Improved TPS
Hi, I have been studying the basic limitation that the number of committed transactions per second possible in a relational databases is limited by the number of writed per second of the underlying hard disk, since each transaction requires at least the write-ahead log data to be flushed to disk on commit, thus limiting the number of durable transactions commits possible per second. Its often possible to increase the total transaction processing speed by turning off the compulsory disc syncing at each commit, which means that in the case of system failure some transactions may be lost *but* the database would still be consistent if the log is always written first. I observed that in in many applications there are some transactions that are more critical than others. I may have the same database instance managing website visitor accounting and financial transactions. I could tolerate the loss of a few transactions whose only job is to tell me a user has clicked a page on my website but would not dare risk this for any real (e.g. financials) work my web-based app may be doing! In the case of bulk inserts, also, or in some such special cases the database user or administrator might be able to code around the need for guaranteed *durability* on transaction commit as long as the database is consistent. So I want to ask, what is RDBMS have a 'COMMIT NOSYNC;' option? Then we can really improve transaction-per-second performance for a database that has lots of non-critical transactions while not jeopardising the durability of critical transactions in the (relatively improbable but quite possible) case of system failure, primarily through combining the log updates for several non-critical transactions. COMMIT [SYNC]; (guarantees atomic, consistent, durable transaction data write) COMMIT NOSYNC; -- (sacrifice durability of non-critical transaction for overall speed). So, the question is what people, especially those who have done RDBMS internals work, think about the implementation and the application of such options, especially in MySQL. Seun Osewa. SIZE does matter - The UK's biggest *Free* Web based mail - 10 MB Free mail.lycos.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DATE_ADD Dynamic Interval
Hi All,
I am trying to create a table with a list of records where a script runs
about once every 10 minutes that will update a certain field by an interval
set (by an enum) in that particular record.
My Script looks as follows:
UPDATE foo_table
SET NextDate=DATE_ADD(foo_table.NextDate,Period)
WHERE NextDate=NOW()
And the schema approximates:
CREATE TABLE `Records` (
`ID` bigint(20) NOT NULL auto_increment,
`Period` enum('INTERVAL 30 SECOND','INTERVAL 1 DAY','INTERVAL 1
WEEK','INTERVAL 2 WEEK','INTERVAL 1 MONTH','INTERVAL 2 MONTH','INTERVAL 3
MONTH','INTERVAL 1 YEAR','INTERVAL 2 YEAR') NOT NULL default 'INTERVAL 1
MONTH',
`NextDate` datetime NOT NULL default '-00-00 00:00:00',
PRIMARY KEY (`ID`)
) TYPE=InnoDB;
I have also considered trying something along the lines of:
UPDATE foo_table
SET NextDate=DATE_ADD(foo_table.NextDate,(SELECT foo_table.Period))
WHERE NextDate=NOW()
Has anybody tried anything like this before with any luck?
Regards,
Adam Carmichael
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Am I on a wrong list?
Hi, guys. I'm quite new to this mailing list. Does anyone know how many programmers are there using this mailing list? I sent my question (below) about 6hrs ago, but still haven't receive any response. Am I on a wrong list? Could someone suggest a better mailing list or forum to discuss MySQL questions? cheers, feng - Original Message - From: Wang Feng [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 30, 2003 7:11 PM Subject: loading data from a textfile into MySQL database table Greetings, I have a datafile called 'salary.txt', and it's a tab delimited file, the structure is like this: employee_nametabsalaryenter employee_nametabsalaryenter ... I have a table called 'salary', and it has the same structure as the 'salary.txt' --- employee_name salary as the columns; I use the command LOAD DATA INFILE salary.txt INTO TABLE salary; After doing that, I do the 'SELECT * FROM databname' to look at the data I loaded. Although the data are there and can be found out by the SELECT query, the layout seems NOT *tidy* at all comparing with other rows typed in by hand directly using the INSERT command. Please advise how to solve this problem. Do you think it's too messy to load data from a text file to a table? cheers, feng -- 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: Am I on a wrong list?
6.4.9 LOAD DATA INFILE Syntax LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name [FIELDS [TERMINATED BY '\t'] [[OPTIONALLY] ENCLOSED BY ''] [ESCAPED BY '\\' ] ] [LINES [STARTING BY ''] [TERMINATED BY '\n'] ] [IGNORE number LINES] [(col_name,...)] and farther down in that section, the relevant part to you is mentioned: Note: If you have generated the text file on a Windows system you may have to change the above to: LINES TERMINATED BY '\r\n' as Windows uses two characters as a line terminator. KL Wang Feng wrote: Hi, guys. I'm quite new to this mailing list. Does anyone know how many programmers are there using this mailing list? I sent my question (below) about 6hrs ago, but still haven't receive any response. Am I on a wrong list? Could someone suggest a better mailing list or forum to discuss MySQL questions? cheers, feng - Original Message - From: Wang Feng [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 30, 2003 7:11 PM Subject: loading data from a textfile into MySQL database table Greetings, I have a datafile called 'salary.txt', and it's a tab delimited file, the structure is like this: employee_nametabsalaryenter employee_nametabsalaryenter ... I have a table called 'salary', and it has the same structure as the 'salary.txt' --- employee_name salary as the columns; I use the command LOAD DATA INFILE salary.txt INTO TABLE salary; After doing that, I do the 'SELECT * FROM databname' to look at the data I loaded. Although the data are there and can be found out by the SELECT query, the layout seems NOT *tidy* at all comparing with other rows typed in by hand directly using the INSERT command. Please advise how to solve this problem. Do you think it's too messy to load data from a text file to a table? cheers, feng -- 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: Am I on a wrong list?
It seems I'm on the *right* list. :-) Thanks, Kelley. The '\r\n' solves the problem. Yes, Kelly, you are right, I'm using WinXP. Thanks again for that. cheers, feng - Original Message - From: Kelley Lingerfelt [EMAIL PROTECTED] To: Wang Feng [EMAIL PROTECTED]; Mysql List [EMAIL PROTECTED] Sent: Tuesday, September 30, 2003 10:24 PM Subject: Re: Am I on a wrong list? 6.4.9 LOAD DATA INFILE Syntax LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name [FIELDS [TERMINATED BY '\t'] [[OPTIONALLY] ENCLOSED BY ''] [ESCAPED BY '\\' ] ] [LINES [STARTING BY ''] [TERMINATED BY '\n'] ] [IGNORE number LINES] [(col_name,...)] and farther down in that section, the relevant part to you is mentioned: Note: If you have generated the text file on a Windows system you may have to change the above to: LINES TERMINATED BY '\r\n' as Windows uses two characters as a line terminator. KL Wang Feng wrote: Hi, guys. I'm quite new to this mailing list. Does anyone know how many programmers are there using this mailing list? I sent my question (below) about 6hrs ago, but still haven't receive any response. Am I on a wrong list? Could someone suggest a better mailing list or forum to discuss MySQL questions? cheers, feng - Original Message - From: Wang Feng [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 30, 2003 7:11 PM Subject: loading data from a textfile into MySQL database table Greetings, I have a datafile called 'salary.txt', and it's a tab delimited file, the structure is like this: employee_nametabsalaryenter employee_nametabsalaryenter ... I have a table called 'salary', and it has the same structure as the 'salary.txt' --- employee_name salary as the columns; I use the command LOAD DATA INFILE salary.txt INTO TABLE salary; After doing that, I do the 'SELECT * FROM databname' to look at the data I loaded. Although the data are there and can be found out by the SELECT query, the layout seems NOT *tidy* at all comparing with other rows typed in by hand directly using the INSERT command. Please advise how to solve this problem. Do you think it's too messy to load data from a text file to a table? cheers, feng -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: GRANT update query: Updating host access entry for users but retaining existing passwords
Shin, I've never tried this, so it's pure speculation, but I believe all of the grant information is contained in a regular table called user. You should be able to copy this information into a temporary table using select into, then perform regular updates to change the host information to match your new subnets. Then simply copy it back to the user table, which will add these records to the existing records, crucially with the same passwords. You might have to repeat this a few times to get all the subnets in. I'd certainly perform lots of testing away from the live system before I tried this for real. Also, hopefully someone else will comment on whether this will actually work, or whether their is a fatal flaw in the idea. Best regards, Andy -Original Message- From: Shin [mailto:[EMAIL PROTECTED] Sent: 30 September 2003 11:02 To: [EMAIL PROTECTED] Subject: GRANT update query: Updating host access entry for users but retaining existing passwords Hi, I've got a MYSQL 3.23.x setup that has approx 4000 database and 4000 user accounts. 1 database per user. I created each userid from a script of the form CREATE DATABASE mdb_userid; GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX,RELOAD ON mdb_userid.* TO [EMAIL PROTECTED] IDENTIFIED BY 'passwd'; GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX,RELOAD ON mdb_userid.* TO [EMAIL PROTECTED] IDENTIFIED BY 'passwd'; FLUSH PRIVILEGES; (this is part of a much longer script that I pass thru to mysql and I'm using dummy/example userid,passwd and host entries in the above). I now need to grant additional access from a number of subnets for each of the users in the database to their own database. I wanted to use a statement of the form GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER,INDEX,RELOAD ON mdb_userid.* TO userid@'192.168.10.0/255.255.255.0' IDENTIFIED BY 'passwd'; But the issue is that I do not have the plain text versions of each of the users password; as many have changed over the years. So I can't use the latter format with IDENTIFIED BY as I want to retain existing passwds. If I just do the above without having an IDENTIFIED BY entry then the users get a blank passwd which is definetly a no-no in my environment. Ideally what I'd like is a quick and simple way to grant users access from the new subnets but to retain their existing passwords - and without me having to extract all their existing encrypted passwords and then inserts them in afterwards in the appropriate tables - I prefer using GRANT. Is there anyway I can what I'm after? many thanks Shin -- 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: GRANT update query: Updating host access entry for users but retaining existing passwords
Hi, Thanks for the followup/reply. On Tue, Sep 30, 2003 at 01:56:11PM +0100, Andy Eastham wrote: I've never tried this, so it's pure speculation, but I believe all of the grant information is contained in a regular table called user. I had a look into this and it seems that some of the information is also held in the Db table as well - hopefully someone can correct me if I'm wrong. You should be able to copy this information into a temporary table using select into, then perform regular updates to change the host information to match your new subnets. Then simply copy it back to the user table, which will add these records to the existing records, crucially with the same passwords. You might have to repeat this a few times to get all the subnets in. I was kinda hoping I could avoid doing such hoops - but thanks for the suggestion. I will have a play on my test database. I'd certainly perform lots of testing away from the live system before I tried this for real. :-)) Rgds Shin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL -- SQL -- Oracle
Folks, this is funny. I've been using Oracle for several months, and started teaching myself MySQL since last weekend. I'm currently using the MySQL version 3.23.49 I found something really interesting: In Oracle, I have to create the Foreign Key in order to join 2 tables. It's quite different when I deal with MySQL. I can join two tables without creating the Foreign Key. - Is this Normal? Before learning MySQL, I thought both MySQL and Orcle use Structural Query Language, which should be the same. But, now I know I'm wrong. Also, when I insert data to a table in MySQL, the values can be enclosed by single quotes, like 'alan', In Oracle, NO WAY, you have to use double quotes, like alan. Any suggestions or advice? It seems I really should get a MySQL book. Which one is the best? cheers, feng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL -- SQL -- Oracle
typo :( correct: in Oracle, one have to use single quotes to enclose those values which are going to be inserted. but in MySQL, doubl quotes do the job well. - Original Message - From: Wang Feng [EMAIL PROTECTED] To: Mysql List [EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 12:04 AM Subject: MySQL -- SQL -- Oracle Folks, this is funny. I've been using Oracle for several months, and started teaching myself MySQL since last weekend. I'm currently using the MySQL version 3.23.49 I found something really interesting: In Oracle, I have to create the Foreign Key in order to join 2 tables. It's quite different when I deal with MySQL. I can join two tables without creating the Foreign Key. - Is this Normal? Before learning MySQL, I thought both MySQL and Orcle use Structural Query Language, which should be the same. But, now I know I'm wrong. Also, when I insert data to a table in MySQL, the values can be enclosed by single quotes, like 'alan', In Oracle, NO WAY, you have to use double quotes, like alan. Any suggestions or advice? It seems I really should get a MySQL book. Which one is the best? cheers, feng -- 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: MySQL -- SQL -- Oracle
I found something really interesting: In Oracle, I have to create the Foreign Key in order to join 2 tables. It's quite different when I deal with MySQL. I can join two tables without creating the Foreign Key. - Is this Normal? Before learning MySQL, I thought both MySQL and Orcle use Structural Query Language, which should be the same. But, now I know I'm wrong. That's because MySQL does not have foreign keys by default. You have to install InnoDB tables to use foreign keys, transactions etc. Kaarel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL -- SQL -- Oracle
Folks, this is funny. I've been using Oracle for several months, and started teaching myself MySQL since last weekend. I'm currently using the MySQL version 3.23.49 You might want to go get the latest if you can (4.0.14). I found something really interesting: In Oracle, I have to create the Foreign Key in order to join 2 tables. It's quite different when I deal with MySQL. I can join two tables without creating the Foreign Key. - Is this Normal? Before learning MySQL, I thought both MySQL and Orcle use Structural Query Language, which should be the same. But, now I know I'm wrong. You are using MyISAM tables - the default. There are a few issues with this table type: 1) Table level locking. Update a single row in a table locks the whole thing 2) No commit/rollback. Make a mistake updating a record, and you can't rollback 3) No foreign keys. At the end of your table definition, you just add TYPE=INNODB; for example, CREATE TABLE parent ( parent_prim_key INTEGER(10) NOT NULL PRIMARY KEY, ) TYPE=INNODB; CREATE TABLE child ( child_prim_key INTEGER(10) NOT NULL PRIMARY KEY, parent_prim_key INTEGER(10) NOT NULL, ... ) TYPE=INNODB; ALTER TABLE child ADD CONSTRAINT FOREIGN KEY (parent_prim_key) REFERENCES parent (parent_prim_key); Also, when I insert data to a table in MySQL, the values can be enclosed by single quotes, like 'alan', In Oracle, NO WAY, you have to use double quotes, like alan. Oracle is really fussy - ' is the only string-delimiter. If you have a ' in the middle of a string (for example, 'Barry's') then you need to escape the ' between the y and the s, and you end up with 'Barry''s', which is messy. MySQL recognizes a variety of wrappers around a string. If you are moving data from Oracle to MySQL, you can just wrap strings in and dump them into MySQL. It seems I really should get a MySQL book. Which one is the best? The docs are quite good for MySQL. Paul DuBois' MySQL, Second Edition is good if you want a bit more depth, plus insight into the programming interfaces. If you are doing an Oracle-MySQL migration, then the MySQL Cookbook will be helpful. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Approx. Release Date of 5.0?
Hello, when is the approx. (very rough) release date of MySQL 5.0? I searched the archive but found no satisfactory answer. I am not interested in an exact date or month, just a very rough estimate. Will it approx. be in a month, in a year or in 2 years? Thank you for your answers! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What are the effects of key_buffer on a dedicated slave
I haven't notice a gain from increasing the key_buffer on a dedicated slave. Let's take 3.23.5x for instance. Since there is only 1 thread for replication, a Serialized committal of data, I wouldn't imagine that key_buffer at higher levels say around 50% of system memory would give a performance boost. In fact raising sort_buffer, read_buffer, read_rnd_buffer would be more of a win to reduce lock times so committal of the data happens faster. Is this observation wrong? Does raising key_buffer on a slave to around 50% of the memory available make a difference? - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688
Re: Possible Commit Syntax Change for Improved TPS
On Tue, Sep 30, 2003 at 01:19:19PM +0100, Oluwaseun Osewa wrote:
Hi,
I have been studying the basic limitation that the number of
committed transactions per second possible in a relational databases
is limited by the number of writed per second of the underlying hard
disk, since each transaction requires at least the write-ahead log data
to be flushed to disk on commit, thus limiting the number of durable
transactions commits possible per second.
InnoDB already provides this flexability.
innodb_flush_log_at_trx_commit={0,1,2}
As described in the InnoDB and MySQL documentation.
It's server-wide, not per-connection or transaction, but it's there
today.
Jeremy
--
Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo!
[EMAIL PROTECTED] | http://jeremy.zawodny.com/
MySQL 4.0.15-Yahoo-SMP: up 16 days, processed 586,074,652 queries (405/sec. avg)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What are the effects of key_buffer on a dedicated slave
On Tue, Sep 30, 2003 at 10:10:29AM -0700, Dathan Vance Pattishall wrote: I haven't notice a gain from increasing the key_buffer on a dedicated slave. Let's take 3.23.5x for instance. Since there is only 1 thread for replication, a Serialized committal of data, I wouldn't imagine that key_buffer at higher levels say around 50% of system memory would give a performance boost. Is that all your slave is doing? Replicating from the master? Are there no other queries being run against it? Is this observation wrong? Does raising key_buffer on a slave to around 50% of the memory available make a difference? That depends, of course. If your key_buffer is already twice the size of all your MYI files, it won't make any difference. Without knowing the size of your index files, memory configuration, and the diversity of key values needed in a short amount of time, it's impossible to say. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 16 days, processed 586,089,594 queries (405/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0, func stored in proc table
On Tue, Sep 30, 2003 at 09:26:11AM +0200, Bob Brands wrote: Hi, I compiled MySQL 5.0 from the source tree to test how stored procedures work. When I create a function, It is not stored in de mysql.func table, but in the mysql.proc table. Example of the function a create: create function fun(a int) returns int begin set a = a + 1; return a; end; Is this a bug or do I do something wrong? What maks you think it's a bug, exactly? -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 16 days, processed 586,098,164 queries (405/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
copy database
HI List, Using version 4.0.15 I'm trying to copy a database. Copy db1 to (new) db2. So I created db2. And then tried the following and got the subsequent errors. # mysqldump --add-drop-table db1 | mysql db2 ERROR 1064 at line 399: You have an error in your SQL syntax near 'count(count), KEY dataID(dataID) ) TYPE=MyISAM' at line 15 # mysqldump --opt db1 | mysql db2 ERROR 1065 at line 23: Query was empty Any insight would be very helpful Thanks Michael
Re: mysql index chooser
On Mon, Sep 29, 2003 at 09:14:06PM -0700, Kevin wrote: I suspect it uses some algorithm to 'guess' the number of rows, and this usually gives a lower number to bigger indicies? Did ANALYZE table help at all? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 16 days, processed 586,152,545 queries (405/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What are the effects of key_buffer on a dedicated slave
---Original Message- --From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] --Sent: Tuesday, September 30, 2003 10:23 AM --To: Dathan Vance Pattishall --Cc: [EMAIL PROTECTED] --Subject: Re: What are the effects of key_buffer on a dedicated slave -- --That depends, of course. If your key_buffer is already twice the size --of all your MYI files, it won't make any difference. Without knowing --the size of your index files, memory configuration, and the diversity --of key values needed in a short amount of time, it's impossible to say. The total size of this dedicated slave's INDEX files are 6.2G (100 index files). I run a key buffer size around 1G on a box with about 4G of memory. For this case what would you recommend? Do you use a formula to determine memory configuration? Based on the types of queries used, I usually will try small tweak to the read and sort buffers. If the key_buffer should be twice the size of the total INDEX file size then I will have to rethink the table schema. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What are the effects of key_buffer on a dedicated slave [also]
---Original Message- --From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] --Sent: Tuesday, September 30, 2003 10:23 AM --To: Dathan Vance Pattishall --Cc: [EMAIL PROTECTED] --Subject: Re: What are the effects of key_buffer on a dedicated slave --Is that all your slave is doing? Replicating from the master? Are there --no other queries being run against it? -- It takes 50% of all reads from the application the db supports. --Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! --[EMAIL PROTECTED] | http://jeremy.zawodny.com/ -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: copy database
DePhillips, Michael P [EMAIL PROTECTED] wrote: Using version 4.0.15 I'm trying to copy a database. Copy db1 to (new) db2. So I created db2. And then tried the following and got the subsequent errors. # mysqldump --add-drop-table db1 | mysql db2 ERROR 1064 at line 399: You have an error in your SQL syntax near 'count(count), KEY dataID(dataID) ) TYPE=MyISAM' at line 15 Is MySQL server running in the ansi mode? If so, 'count' is treated as a reserved word. Use -Q option of mysqldump. # mysqldump --opt db1 | mysql db2 ERROR 1065 at line 23: Query was empty Check line 23 of the dump file. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What are the effects of key_buffer on a dedicated slave [also]
On Tue, Sep 30, 2003 at 11:07:59AM -0700, Dathan Vance Pattishall wrote: ---Original Message- --From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] --Sent: Tuesday, September 30, 2003 10:23 AM --To: Dathan Vance Pattishall --Cc: [EMAIL PROTECTED] --Subject: Re: What are the effects of key_buffer on a dedicated slave --Is that all your slave is doing? Replicating from the master? Are there --no other queries being run against it? -- It takes 50% of all reads from the application the db supports. The first thing I'd do is figure how how well utilized the key buffer is today. Either grab a copy of mytop (it does it for you), or look at SHOW STATUS to compute the percentage based on the ratio of key_read_requests to key_reads. If you're already hitting the buffer 99% of the time, there's little point in increasing the size. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 16 days, processed 586,536,970 queries (405/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What are the effects of key_buffer on a dedicated slave
In the last episode (Sep 30), Dathan Vance Pattishall said: I haven't notice a gain from increasing the key_buffer on a dedicated slave. Let's take 3.23.5x for instance. Since there is only 1 thread for replication, a Serialized committal of data, I wouldn't imagine that key_buffer at higher levels say around 50% of system memory would give a performance boost. In fact raising sort_buffer, read_buffer, read_rnd_buffer would be more of a win to reduce lock times so committal of the data happens faster. Is this observation wrong? Does raising key_buffer on a slave to around 50% of the memory available make a difference? A better question to ask might be what is my current index hit ratio, and is it low enough that I need to bump key_buffer? Run SHOW STATUS LIKE 'key%', and if your read hit ratio (1-key_reads/key_read_requests) is under 80% or so, you may benefit from increasing key_buffer. If key_reads is very close to key_blocks_used, you might want to shrink key_buffer because mysql has never had to throw away a cached index block. Note that the more memory you allocate to key_buffer, the less memory the OS has available to cache the table data istelf. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What are the effects of key_buffer on a dedicated slave [also]
---Original Message- --From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] --Sent: Tuesday, September 30, 2003 11:24 AM --To: Dathan Vance Pattishall --Cc: [EMAIL PROTECTED] --Subject: Re: What are the effects of key_buffer on a dedicated slave --[also] -- --On Tue, Sep 30, 2003 at 11:07:59AM -0700, Dathan Vance Pattishall wrote: -- -- ---Original Message- -- --From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] -- --Sent: Tuesday, September 30, 2003 10:23 AM -- --To: Dathan Vance Pattishall -- --Cc: [EMAIL PROTECTED] -- --Subject: Re: What are the effects of key_buffer on a dedicated slave -- -- -- --Is that all your slave is doing? Replicating from the master? Are -- there -- --no other queries being run against it? -- -- -- -- It takes 50% of all reads from the application the db supports. -- --The first thing I'd do is figure how how well utilized the key buffer --is today. Either grab a copy of mytop (it does it for you), or look --at SHOW STATUS to compute the percentage based on the ratio of --key_read_requests to key_reads. Yes, I use a custom mytop (sent my patches in to you). In fact I'm making a signed java applet to simulate mytop, just to be fancy ;) as well as not having to ssh into a central box that can reach all my servers. -- --If you're already hitting the buffer 99% of the time, there's little --point in increasing the size. I figured that but does it really matter on a dedicated slave handling some reads, since there can never be parallel writes? I may be missing the concept. My Goal is to speed up replication (3.23.57) so I can upgrade to 4.0.15 once the master / slave is in-sync and find out if the var key_buffer on a dedicated slave helps out significantly. -- --Jeremy --Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! --[EMAIL PROTECTED] | http://jeremy.zawodny.com/ -- --MySQL 4.0.15-Yahoo-SMP: up 16 days, processed 586,536,970 queries --(405/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
NOT problems
Here's my schema and data:
create table person (name char(5));
insert into person values ('Bob');
insert into person values ('Jane');
In mySQL 4.1-alpha, 4.0.15a, and 3.23.58, I get the
following results:
mysql SELECT * FROM person WHERE NOT name = 'Bob';
Empty set (0.00 sec)
mysql SELECT * FROM person WHERE NOT (name = 'Bob');
+--+
| name |
+--+
| Jane |
+--+
1 row in set (0.00 sec)
Why do I need the parentheses? They are not required
by the SQL specification. What is the first query
really answering?
Thanks in advance.
__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What are the effects of key_buffer on a dedicated slave
---Original Message- --From: Dan Nelson [mailto:[EMAIL PROTECTED] --Sent: Tuesday, September 30, 2003 11:35 AM --To: Dathan Vance Pattishall --Cc: [EMAIL PROTECTED] --Subject: Re: What are the effects of key_buffer on a dedicated slave -- --A better question to ask might be what is my current index hit ratio, --and is it low enough that I need to bump key_buffer? Okay yes, this makes sense. --from increasing key_buffer. If key_reads is very close to --key_blocks_used, you might want to shrink key_buffer because mysql has This is fantastic. I totally over looked this fact. Thanks for the insight. --never had to throw away a cached index block. Note that the more --memory you allocate to key_buffer, the less memory the OS has available --to cache the table data istelf. -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What are the effects of key_buffer on a dedicated slave [also]
On Tue, Sep 30, 2003 at 11:36:30AM -0700, Dathan Vance Pattishall wrote: Yes, I use a custom mytop (sent my patches in to you). In fact I'm making a signed java applet to simulate mytop, just to be fancy ;) as well as not having to ssh into a central box that can reach all my servers. Really? Which patch? Have I integrated it yet? :-) I figured that but does it really matter on a dedicated slave handling some reads, since there can never be parallel writes? I may be missing the concept. Yes, you're missing something, I think. The key_buffer is part of what makes indexes fast for MyISAM tables. By keeping recently used index blocks in RAM, it doesn't need to hit the disk. This helps no matter how many readers/writers you have. It's a cache. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 16 days, processed 586,742,904 queries (404/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NOT problems
At 11:37 -0700 9/30/03, Ed Smith wrote:
Here's my schema and data:
create table person (name char(5));
insert into person values ('Bob');
insert into person values ('Jane');
In mySQL 4.1-alpha, 4.0.15a, and 3.23.58, I get the
following results:
mysql SELECT * FROM person WHERE NOT name = 'Bob';
Empty set (0.00 sec)
mysql SELECT * FROM person WHERE NOT (name = 'Bob');
+--+
| name |
+--+
| Jane |
+--+
1 row in set (0.00 sec)
Why do I need the parentheses? They are not required
by the SQL specification. What is the first query
really answering?
In MySQL, NOT has higher precedence than =, so your first
query is equivalent to (NOT name) = 'Bob'.
Which will compare 1, 0, or NULL to 'Bob', depending on the
value of name.
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: copy database
In a message dated 9/30/03 1:33:07 PM Eastern Daylight Time, [EMAIL PROTECTED] writes: HI List, Using version 4.0.15 I'm trying to copy a database. Copy db1 to (new) db2. So I created db2. And then tried the following and got the subsequent errors. # mysqldump --add-drop-table db1 | mysql db2 ERROR 1064 at line 399: You have an error in your SQL syntax near 'count(count), KEY dataID(dataID) ) TYPE=MyISAM' at line 15 Why not just copy the [MySQL-data-dir]\DB1 to [MySQL-data-dir]\DB2? That works for me, even when I am moving/copying a database from a Windows machine to a Linux machine. Hope this helps. Tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't Access DB from MySQLCC
Randy Chrismon [EMAIL PROTECTED] wrote: Victoria Reznichenko wrote: MySQL 4.1 provides new password hashing mechanism. This error appears if you connect with pre-4.1 client to the server 4.1. ook at: http://www.mysql.com/doc/en/Password_hashing.html In the above section of the manual you can find description of possible scenarios for 4.1 server. Don't think this is the issue. If it were, I wouldn't be able to use MySQLCC on my own local database which is also 4.1.0 alpha, would I? Do you use password when you connect to the local 4.1 MySQL server? The only way that I can get the command line on my laptop to connect to my linux box is to include the protocol option (--protocol=TCP), even though the laptop is purely a 4.1.0 install. Can you connect without --protocol option using 4.1 mysql command-line client program? MySQLCC, apparently, does not accept the protocol command line parameter and it gives me the same error message I get when I don't use the protocol parameter on the command line. Of course, MySQLCC connects just fine to my other MySQL database which also happens to be a 4.0.15 setup on a Win2K desktop. So, I guess it's one of those things where it's both fish and fowl. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: copy database
At 14:54 -0400 9/30/03, [EMAIL PROTECTED] wrote: In a message dated 9/30/03 1:33:07 PM Eastern Daylight Time, [EMAIL PROTECTED] writes: HI List, Using version 4.0.15 I'm trying to copy a database. Copy db1 to (new) db2. So I created db2. And then tried the following and got the subsequent errors. # mysqldump --add-drop-table db1 | mysql db2 ERROR 1064 at line 399: You have an error in your SQL syntax near 'count(count), KEY dataID(dataID) ) TYPE=MyISAM' at line 15 Why not just copy the [MySQL-data-dir]\DB1 to [MySQL-data-dir]\DB2? That works for me, even when I am moving/copying a database from a Windows machine to a Linux machine. What if you have InnoDB tables? -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems with indexing a timestamp() field
Hello all, I am trying to set up a complete trraffic accounting using Ulogd-mysql. Since the whole idea of the project is to be able to select rows based on the timestamp value indexing is a must. The index files get created with no problem at all. However explain select refuses to use the index (see below): mysql select version(); +---+ | version() | +---+ | 4.0.14 | +---+ 1 row in set (0.00 sec) mysql describe ulog; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | pkt_time | timestamp(12) | YES | MUL | NULL | | | oob_prefix | varchar(32) | YES | | NULL | | | oob_mark | int(10) unsigned | YES | | NULL | | | oob_in | varchar(32) | YES | | NULL | | | oob_out | varchar(32) | YES | | NULL | | | ip_saddr | int(10) unsigned | YES | | NULL | | | ip_daddr | int(10) unsigned | YES | | NULL | | | ip_protocol | tinyint(3) unsigned | YES | | NULL | | | ip_tos | tinyint(3) unsigned | YES | | NULL | | | ip_ttl | tinyint(3) unsigned | YES | | NULL | | | ip_totlen | smallint(5) unsigned | YES | | NULL | | | tcp_sport | smallint(5) unsigned | YES | | NULL | | | tcp_dport | smallint(5) unsigned | YES | | NULL | | | tcp_urg | tinyint(4) | YES | | NULL | | | tcp_ack | tinyint(4) | YES | | NULL | | | tcp_psh | tinyint(4) | YES | | NULL | | | tcp_rst | tinyint(4) | YES | | NULL | | | tcp_syn | tinyint(4) | YES | | NULL | | | tcp_fin | tinyint(4) | YES | | NULL | | | udp_sport | smallint(5) unsigned | YES | | NULL | | | udp_dport | smallint(5) unsigned | YES | | NULL | | | icmp_type | tinyint(3) unsigned | YES | | NULL | | | icmp_code | tinyint(3) unsigned | YES | | NULL | | | icmp_echoid | smallint(5) unsigned | YES | | NULL | | +-+--+--+-+-+---+ 24 rows in set (0.00 sec) mysql show index from ulog; +---++--+--+-+---+-+--++--+--- -+-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+-+--++--+--- -+-+ | ulog | 1 | tim | 1 | pkt_time | A | 45070 | NULL | NULL | | BTREE | | +---++--+--+-+---+-+--++--+--- -+-+ 1 row in set (0.00 sec) Data is being stored inside no problem at all: mysql select * from ulog limit 3000,1; +--++--++-++---+-+++-- -+---+---+-+-+-+-+-+-+---+---+ ---+---+-+ | pkt_time | oob_prefix | oob_mark | oob_in | oob_out | ip_saddr | ip_daddr | ip_protocol | ip_tos | ip_ttl | ip_totlen | tcp_sport | tcp_dport | tcp_urg | tcp_ack | tcp_psh | tcp_rst | tcp_syn | tcp_fin | udp_sport | udp_dport | icmp_type | icmp_code | icmp_echoid | +--++--++-++---+-+++-- -+---+---+-+-+-+-+-+-+---+---+ ---+---+-+ | 030925023218 | rawin | 0 | eth0 | | 3645603842 | 217539170 | 6 | 0 | 44 | 80 | | 32784 | 0 | 1 | 1 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | +--++--++-++---+-+++-- -+---+---+-+-+-+-+-+-+---+---+ ---+---+-+ 1 row in set (0.02 sec) However for some reason MySQL under any circumstances does not want to take advantage of the index when doing time-sensitive selects, which takes forever to perform without an index: mysql explain select * from ulog where pkt_time=030925023218; +---+--+---+--+-+--++-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--++-+ | ulog | ALL | tim | NULL | NULL | NULL | 991541 | Using where | +---+--+---+--+-+--++-+ 1 row in set (0.00 sec) If I index ANY other filed the indexing works like a charm, but the whole idea of the excercise is to take advantage of the packet logging time. Anyone with fresh ideas is welcome. Peter -- MySQL
Re: Key_buffer_size
On Tue, Sep 30, 2003 at 08:17:20PM +0100, [EMAIL PROTECTED] wrote: Hi all :) I'm working with InnoDB tables only, and i read that the Key_buffer_size is only used for MyISAM tables. Is it true? If yes, i can put this variable to 0? Why not leave it at the default value? If MySQL doesn't need it, it won't use it. I'll bet $1000 that you have *some* MyISAM tables... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 16 days, processed 588,389,126 queries (405/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: copy database
In a message dated 9/30/03 3:13:59 PM Eastern Daylight Time, [EMAIL PROTECTED] writes: What if you have InnoDB tables? I don't. ;-) But I'd certainly like to know other strategies. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible Commit Syntax Change for Improved TPS
Hi Jeremy,
Hmmm ... I guess the meat of my suggestion is that it be made
available on a per-transactionbasis because in many applications
some transactions are more critical than others. So even on
systems where there are *some* transactions that need to be
flushed to log immediately we can still get some speedups by
delaying log writes for transactions that do not need this...
transactions that can afford to be lost if the server crashes.
And the sad thing is that though it may be simple to implement
I may not be in a position to do so for testing :-(
Seun Osewa
--- Original message ---
From: Jeremy Zawodny [EMAIL PROTECTED]
Date: Tue, 30 Sep 2003 10:17:04 -0700
Subject: Re: Possible Commit Syntax Change for Improved TPS
On Tue, Sep 30, 2003 at 01:19:19PM +0100, Oluwaseun Osewa wrote:
Hi,
I have been studying the basic limitation that the number of
committed transactions per second possible in a relational databases
is limited by the number of writed per second of the underlying hard
disk, since each transaction requires at least the write-ahead log data
to be flushed to disk on commit, thus limiting the number of durable
transactions commits possible per second.
InnoDB already provides this flexability.
innodb_flush_log_at_trx_commit={0,1,2}
As described in the InnoDB and MySQL documentation.
It's server-wide, not per-connection or transaction, but it's there
today.
Jeremy
--
Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo!
| http://jeremy.zawodny.com/
MySQL 4.0.15-Yahoo-SMP: up 16 days, processed 586,074,652 queries (405/sec. avg)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SIZE does matter - The UK's biggest *Free* Web based mail - 10 MB Free
mail.lycos.co.uk
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Key_buffer_size
Most likely your mysql database is still MyISAM, right? -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 30, 2003 12:35 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Key_buffer_size On Tue, Sep 30, 2003 at 08:17:20PM +0100, [EMAIL PROTECTED] wrote: Hi all :) I'm working with InnoDB tables only, and i read that the Key_buffer_size is only used for MyISAM tables. Is it true? If yes, i can put this variable to 0? Why not leave it at the default value? If MySQL doesn't need it, it won't use it. I'll bet $1000 that you have *some* MyISAM tables... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 16 days, processed 588,389,126 queries (405/sec. avg) -- 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: What are the effects of key_buffer on a dedicated slave [also]
---Original Message- --From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] --Sent: Tuesday, September 30, 2003 11:51 AM --To: Dathan Vance Pattishall --Cc: [EMAIL PROTECTED] --Subject: Re: What are the effects of key_buffer on a dedicated slave --[also] -- --On Tue, Sep 30, 2003 at 11:36:30AM -0700, Dathan Vance Pattishall wrote: -- -- Yes, I use a custom mytop (sent my patches in to you). In fact I'm -- making a signed java applet to simulate mytop, just to be fancy ;) as -- well as not having to ssh into a central box that can reach all my -- servers. -- --Really? Which patch? Have I integrated it yet? :-) Patch Contained SLAVE / Master Positions as well as which databases are ignored or slaved, and a full list of possible keys Command (E). I can resend another patch; I've made some changes since. -- --This helps no matter how many readers/writers you have. It's a cache. Cool, makes sense now. The key_buffer was to low. The RAID drives where being hit nearly on every SELECT and reducing resources for the dirty buffer from being flushed as often as it needed to be. Thanks Dathan -- --Jeremy --Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! --[EMAIL PROTECTED] | http://jeremy.zawodny.com/ -- --MySQL 4.0.15-Yahoo-SMP: up 16 days, processed 586,742,904 queries --(404/sec. avg) -- --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: Key_buffer_size
Mysql database have only innodb tables. I'm not using MyISAM. Quoting Misaochankun [EMAIL PROTECTED]: Most likely your mysql database is still MyISAM, right? -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 30, 2003 12:35 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Key_buffer_size On Tue, Sep 30, 2003 at 08:17:20PM +0100, [EMAIL PROTECTED] wrote: Hi all :) I'm working with InnoDB tables only, and i read that the Key_buffer_size is only used for MyISAM tables. Is it true? If yes, i can put this variable to 0? Why not leave it at the default value? If MySQL doesn't need it, it won't use it. I'll bet $1000 that you have *some* MyISAM tables... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 16 days, processed 588,389,126 queries (405/sec. avg) -- 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]
Maintenace Query corrupting indexes + perf question
I have two issues I am dealing with. I am a Web app administrator who got a database position foisted upon me and I am just getting my feet wet with MySql. Here is the problem: a query we run to truncate old data from a database is corrupting the indexes on that table. Here is the query: $EXECPATH/mysql --host=IP --user=user --password=pass --execute=delete from MESSAGES where to_days(now()) - to_days(timestamp) 2 and sent = 'Y' test When I run the query from command line, 100s (if not thousands) of the folowing errors get thrown on the screen: ERROR 1062 at line 2998: Duplicate entry '4730785' for key 1 What exactly is this denoting? The script was working when we were using MySql 3.x but 4 has broken it. The other issue is related to optimizing performance. I have the following numbers with query: SHOW STATUS LIKE 'key%' ++---+ | Variable_name | Value | ++---+ | Key_blocks_used| 7479 | | Key_read_requests | 74535 | | Key_reads | 1893305 | | Key_write_requests | 6325172 | | Key_writes | 6628208 | ++---+ Does this look like a candidate for increasing the key_buffers? Thanks, Ben Ricker Wellinx.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Key_buffer_size
The mysql database he is referring to is /var/lib/mysql/mysql where it holds the access rights for users, tables, columns, etc. That HAS to be MYISAM. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] --Sent: Tuesday, September 30, 2003 1:12 PM --To: Misaochankun --Cc: [EMAIL PROTECTED] --Subject: RE: Key_buffer_size -- -- --Mysql database have only innodb tables. I'm not using MyISAM. -- --Quoting Misaochankun [EMAIL PROTECTED]: -- -- Most likely your mysql database is still MyISAM, right? -- -- -Original Message- -- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] -- Sent: Tuesday, September 30, 2003 12:35 PM -- To: [EMAIL PROTECTED] -- Cc: [EMAIL PROTECTED] -- Subject: Re: Key_buffer_size -- -- On Tue, Sep 30, 2003 at 08:17:20PM +0100, [EMAIL PROTECTED] --wrote: -- -- -- Hi all :) -- -- I'm working with InnoDB tables only, and i read that the -- Key_buffer_size is only -- used for MyISAM tables. Is it true? -- If yes, i can put this variable to 0? -- -- Why not leave it at the default value? If MySQL doesn't need it, it -- won't -- use it. -- -- I'll bet $1000 that you have *some* MyISAM tables... -- -- Jeremy -- -- -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! -- [EMAIL PROTECTED] | http://jeremy.zawodny.com/ -- -- MySQL 4.0.15-Yahoo-SMP: up 16 days, processed 588,389,126 queries -- (405/sec. avg) -- -- -- -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Key_buffer_size
On Tue, Sep 30, 2003 at 09:11:59PM +0100, [EMAIL PROTECTED] wrote: Mysql database have only innodb tables. I'm not using MyISAM. Really? Unless I'm smoking crack, there used to be code in InnoDB that refused to convert the mysql.* tables to InnoDB. If that's changed, I'd love to know why. Besides, I really hate it when I lose $1000. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 16 days, processed 589,980,795 queries (404/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What are the effects of key_buffer on a dedicated slave [also]
On Tue, Sep 30, 2003 at 01:11:23PM -0700, Dathan Vance Pattishall wrote: ---Original Message- --From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] --Sent: Tuesday, September 30, 2003 11:51 AM --To: Dathan Vance Pattishall --Cc: [EMAIL PROTECTED] --Subject: Re: What are the effects of key_buffer on a dedicated slave --[also] -- --On Tue, Sep 30, 2003 at 11:36:30AM -0700, Dathan Vance Pattishall wrote: -- -- Yes, I use a custom mytop (sent my patches in to you). In fact I'm -- making a signed java applet to simulate mytop, just to be fancy ;) as -- well as not having to ssh into a central box that can reach all my -- servers. -- --Really? Which patch? Have I integrated it yet? :-) Patch Contained SLAVE / Master Positions as well as which databases are ignored or slaved, and a full list of possible keys Command (E). I can resend another patch; I've made some changes since. Please do, it sounds useful. I'd like to roll it into version 1.5 before I release it. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 16 days, processed 590,020,855 queries (404/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Key_buffer_size
Hi, I'm in the same boat here, I only have InnodDB tables except for the mysql MyISAM tables. So in theory would a value of something like 8mb be sufficient for those ? Cheers. Marvin. -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: 30 September 2003 22:13 To: [EMAIL PROTECTED] Cc: Misaochankun; [EMAIL PROTECTED] Subject: Re: Key_buffer_size On Tue, Sep 30, 2003 at 09:11:59PM +0100, [EMAIL PROTECTED] wrote: Mysql database have only innodb tables. I'm not using MyISAM. Really? Unless I'm smoking crack, there used to be code in InnoDB that refused to convert the mysql.* tables to InnoDB. If that's changed, I'd love to know why. Besides, I really hate it when I lose $1000. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 16 days, processed 589,980,795 queries (404/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Key_buffer_size
At 14:12 -0700 9/30/03, Jeremy Zawodny wrote: On Tue, Sep 30, 2003 at 09:11:59PM +0100, [EMAIL PROTECTED] wrote: Mysql database have only innodb tables. I'm not using MyISAM. Really? Unless I'm smoking crack, there used to be code in InnoDB that refused to convert the mysql.* tables to InnoDB. I thought so, too, but Heikki says this is true only for the user table. If that's changed, I'd love to know why. Besides, I really hate it when I lose $1000. Jeremy -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What are the effects of key_buffer on a dedicated slave [also]
Jeremy Zawodny said: On Tue, Sep 30, 2003 at 01:11:23PM -0700, Dathan Vance Pattishall wrote: ---Original Message- --From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] --Sent: Tuesday, September 30, 2003 11:51 AM --To: Dathan Vance Pattishall --Cc: [EMAIL PROTECTED] --Subject: Re: What are the effects of key_buffer on a dedicated slave --[also] -- --On Tue, Sep 30, 2003 at 11:36:30AM -0700, Dathan Vance Pattishall wrote: -- -- Yes, I use a custom mytop (sent my patches in to you). In fact I'm -- making a signed java applet to simulate mytop, just to be fancy ;) as -- well as not having to ssh into a central box that can reach all my -- servers. -- --Really? Which patch? Have I integrated it yet? :-) Patch Contained SLAVE / Master Positions as well as which databases are ignored or slaved, and a full list of possible keys Command (E). I can resend another patch; I've made some changes since. Please do, it sounds useful. I'd like to roll it into version 1.5 before I release it. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 16 days, processed 590,020,855 queries (404/sec. avg) Have you tried mytop with debian and 3.23.49 -- version in Debian stable? we keep getting core dumps. Does it have to be run as root? Sorry if OT William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with indexing a timestamp() field
It is not quite possible - there are several hundred packets per second - hence several hundred fields with equal timestamps. In order to use primary key all fields have to be unique... Peter On Tue, Sep 30, 2003 at 03:39:08PM -0700, James Kelty wrote: Maybe making it the PRIMARY KEY will help? -James Peter Rabbitson wrote: Hello all, I am trying to set up a complete trraffic accounting using Ulogd-mysql. Since the whole idea of the project is to be able to select rows based on the timestamp value indexing is a must. The index files get created with no problem at all. However explain select refuses to use the index (see below): mysql select version(); +---+ | version() | +---+ | 4.0.14 | +---+ 1 row in set (0.00 sec) mysql describe ulog; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | pkt_time | timestamp(12) | YES | MUL | NULL | | | oob_prefix | varchar(32) | YES | | NULL | | | oob_mark | int(10) unsigned | YES | | NULL | | | oob_in | varchar(32) | YES | | NULL | | | oob_out | varchar(32) | YES | | NULL | | | ip_saddr | int(10) unsigned | YES | | NULL | | | ip_daddr | int(10) unsigned | YES | | NULL | | | ip_protocol | tinyint(3) unsigned | YES | | NULL | | | ip_tos | tinyint(3) unsigned | YES | | NULL | | | ip_ttl | tinyint(3) unsigned | YES | | NULL | | | ip_totlen | smallint(5) unsigned | YES | | NULL | | | tcp_sport | smallint(5) unsigned | YES | | NULL | | | tcp_dport | smallint(5) unsigned | YES | | NULL | | | tcp_urg | tinyint(4) | YES | | NULL | | | tcp_ack | tinyint(4) | YES | | NULL | | | tcp_psh | tinyint(4) | YES | | NULL | | | tcp_rst | tinyint(4) | YES | | NULL | | | tcp_syn | tinyint(4) | YES | | NULL | | | tcp_fin | tinyint(4) | YES | | NULL | | | udp_sport | smallint(5) unsigned | YES | | NULL | | | udp_dport | smallint(5) unsigned | YES | | NULL | | | icmp_type | tinyint(3) unsigned | YES | | NULL | | | icmp_code | tinyint(3) unsigned | YES | | NULL | | | icmp_echoid | smallint(5) unsigned | YES | | NULL | | +-+--+--+-+-+---+ 24 rows in set (0.00 sec) mysql show index from ulog; +---++--+--+-+---+-+--++--+--- -+-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+-+--++--+--- -+-+ | ulog | 1 | tim | 1 | pkt_time | A | 45070 | NULL | NULL | | BTREE | | +---++--+--+-+---+-+--++--+--- -+-+ 1 row in set (0.00 sec) Data is being stored inside no problem at all: mysql select * from ulog limit 3000,1; +--++--++-++---+-+++-- -+---+---+-+-+-+-+-+-+---+---+ ---+---+-+ | pkt_time | oob_prefix | oob_mark | oob_in | oob_out | ip_saddr | ip_daddr | ip_protocol | ip_tos | ip_ttl | ip_totlen | tcp_sport | tcp_dport | tcp_urg | tcp_ack | tcp_psh | tcp_rst | tcp_syn | tcp_fin | udp_sport | udp_dport | icmp_type | icmp_code | icmp_echoid | +--++--++-++---+-+++-- -+---+---+-+-+-+-+-+-+---+---+ ---+---+-+ | 030925023218 | rawin | 0 | eth0 | | 3645603842 | 217539170 | 6 | 0 | 44 | 80 | | 32784 | 0 | 1 | 1 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | +--++--++-++---+-+++-- -+---+---+-+-+-+-+-+-+---+---+ ---+---+-+ 1 row in set (0.02 sec) However for some reason MySQL under any circumstances does not want to take advantage of the index when doing time-sensitive selects, which takes forever to perform without an index: mysql explain select * from ulog where pkt_time=030925023218; +---+--+---+--+-+--++-+ | table | type | possible_keys | key | key_len | ref | rows | Extra |
Re: What are the effects of key_buffer on a dedicated slave [also]
On Tue, Sep 30, 2003 at 02:47:35PM -0700, William R. Mussatto wrote: Have you tried mytop with debian and 3.23.49 -- version in Debian stable? we keep getting core dumps. Does it have to be run as root? Sorry if OT The only Debian Stable box I have is powered off most of the time anymore. But I can give it a shot. You might try running it under the Perl debugger to see where it cores. I suspect that it's a mysql client library problem, since that's what tends to bite people. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 16 days, processed 591,294,229 queries (403/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Approx. Release Date of 5.0?
Hi! I have a hobby of guessing release dates. 4.0.0 was released in October 2001. 4.1.0 was released in April 2003. That would give us an estimate that 5.0 would be released in October 2004. But I would rather guess March 2004, because people are so eagerly waiting for stored procedures, and they already worked in February 2003. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Corn Hulio [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, September 30, 2003 7:30 PM Subject: Approx. Release Date of 5.0? Hello, when is the approx. (very rough) release date of MySQL 5.0? I searched the archive but found no satisfactory answer. I am not interested in an exact date or month, just a very rough estimate. Will it approx. be in a month, in a year or in 2 years? Thank you for your answers! -- 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 Access DB from MySQLCC
Don't think this is the issue. If it were, I wouldn't be able to use MySQLCC on my own local database which is also 4.1.0 alpha, would I? Do you use password when you connect to the local 4.1 MySQL server? Yes. No password, no entry. Can you connect without --protocol option using 4.1 mysql command-line client program? No. If I leave off the --protocol option, I get the error message saying I should upgrade my client, even though both sides of the transaction are 4.1.0 alpha. Well, now that I read your response more carefully, no, I don't need the --protocol option if I am connecting to my local 4.1.0 database using the command line. I DO need it, however, to connect to the 4.1.0 database on the other machine. On the other hand, I do NOT need the option if I connect to a 4.0.15 database on yet another machine. With MySQLCC, I can connect to the local 4.1.0 database and the remote 4.0.15 database. Trying to connect to the 4.1.0 database yields the same upgrade-your-client error message I get with the command line if I leave off the ---protocol=TCP option. Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql 4.1.0 utf8 on linux : maybe known bug and current bk compile problem
Hi, I'm currently doing some utf8 tests with mysql, during these tests i think i've hit a bug already discussed on this list. http://marc.theaimsgroup.com/?l=mysqlm=105593058922219w=2 http://marc.theaimsgroup.com/?t=10557702591r=1w=2 these two bug report and responses suggest that it's because client session does not use utf8 character set. In my case i'm sure both client and server are using utf8. (status command report it) So the problem is : Using Mysql 4.1.0-alpha (recompiled rpm) with InnoDb Database and charset utf8 (both client session and server) any simple select query return this error : ERROR 1210: Wrong arguments to = Exemple : mysql status; -- mysql Ver 13.5 Distrib 4.1.0-alpha, for pc-linux (i686) Connection id: 8 Current database: agatechat Current user: [EMAIL PROTECTED] SSL:Not in use Current pager: stdout Using outfile: '' Server version: 4.1.0-alpha-debug-log Protocol version: 10 Connection: Localhost via UNIX socket Client characterset:utf8 Server characterset:utf8 UNIX socket:/var/lib/mysql/mysql.sock Uptime: 2 days 17 hours 4 min 34 sec Threads: 1 Questions: 79 Slow queries: 0 Opens: 36 Flush tables: 1 Open tables: 30 Queries per second avg: 0.000 -- mysql describe emprunteurs; ++-+---+--+-++---+ | Field | Type| Collation | Null | Key | Default| Extra | ++-+---+--+-++---+ | num| varchar(6) | utf8 | | PRI || | | cb | varchar(20) | utf8 | | || | | nom| varchar(25) | utf8 | | || | | prenom | varchar(20) | utf8 | | || | | pwd| varchar(10) | utf8 | | || | | email | varchar(30) | utf8 | | || | | groupe | varchar(6) | utf8 | | || | | lins | varchar(4) | utf8 | | || | ++-+---+--+-++---+ 8 rows in set (0.00 sec) mysql select nom from emprunteurs where nom = 'BOUHASSOUN' LIMIT 1; ERROR 1210: Wrong arguments to = Note that using 'SET CHARACTER SET utf8' before doesnt resolve this problem. Specifiying encoding before string in query works : mysql select nom from emprunteurs where nom = _utf8'BOUHASSOUN' LIMIT 1 ; ++ | nom| ++ | BOUHASSOUN | ++ 1 row in set (0.00 sec) So, what i would like to known is if i've missed something obvious. If not, is this an already known bug, corrected ? if yes where could i find a patch ? I've tryed current bk tree for 4.1.1 and hit another bug, related to compilation system. Everything compile fine, but the shared library are created with an incorrect name. For exemple : directory libmysql_r/.libs/ contains libmysqlclient_r.14.0.0 it should be libmysqlclient_r.so.14.0.0 But it's not only a filename problem : $ objdump -x libmysqlclient_r.14.0.0 | grep SONAME SONAME libmysqlclient_r.14 So the soname is wrong too, i think ld will not like this so i didn't install and test it. I've looked at Makefile.am/config.in/aclocal.m4 change since 4.1.0 (which compile and run fine on same system) but i didn't find anything looking wrong. But i'm not a libtool expert. Thanks in advance, Regards, Gilles Magnier. PS: Some additional informations about test platform : utf8 bug reproduced on : one RH9 and one rawhide, both up to date. MySQL 4.1.0-alpha recompiled from www.mysql.com source rpm. (only configure option changes : utf8 default charset) current bk 4.1.1 Compile problem found on rawhide box only (not tested in RH9) : gcc (GCC) 3.3.1 20030915 g++ (GCC) 3.3.1 20030915 ltmain.sh (GNU libtool) 1.5 (1.1220.2.1 2003/04/14 22:48:00) autoconf (GNU Autoconf) 2.57 automake (GNU automake) 1.7.7 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB speed problems
Hi all, Because I want to use transactions in the future I have converted all tables of a copy of our production database server (1800+, 512 MB RAM, Linux) to InnoDB format. No problem until now. First, let me show you settings in my.cnf: key_buffer= 16M table_cache = 128 sort_buffer_size = 1M read_buffer_size = 1M myisam_sort_buffer_size = 64M thread_cache = 8 thread_concurrency= 8 innodb_buffer_pool_size = 256M innodb_additional_mem_pool_size = 20 innodb_log_file_size = 64M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 Question: Is sort_buffer_size and read_buffer_size relevant to InnoDB? All these settings seem to be fine for me. With MyISAM I have used a key_buffer of 256M and sort_buffer_size of 4M which procuded very fast database accesses. mytop's output: MySQL on localhost (4.0.15-standard-log) up 0+23:14:39 [04:23:24] Queries: 5.7M qps: 72 Slow:34.0 Se/In/Up/De(%): 63/10/15/05 Cache Hits: 1005.2k Hits/s: 12.3 Ratio: 27.3% Key Efficiency: 100.0% Bps in/out: 8.0k/33.8k But now everything is slow, I don't know why. Even without load each query takes a bit longer. Shouldn't it be vice versa? Then I did some load testing: CPU usage and system load raised by 100 percent. That's not normal for me, does InnoDB need more power, more momory? While testing MySQL was able to handle all the queries but, well, not as fast as I would like to have it in productive environment. I have also noticed that some more complex queries (select with 4 joins and 2 orders) last much too long. With MyISAM everything was 0.5s but now I sometimes have a strange one that is listed for several houndred seconds (?!) in the process list. That's not normal, isn't it? Something strange is going on here and I do not have a clue what I could be. Playing around with the settings and raising InnoDB's pool size to 80% of memory didn't change anything. So, I'm not familiar with this great InnoDB thing, maybe you have some ideas. :) Thanks in advance! Matthias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can NOT drop the database
Not at all!! after I typed the 'drop database my_account_database', I got the following message: Query OK, 0 rows affected (0.00sec). and I do the 'show databases', that one is still there. cheers, feng - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 3:31 PM Subject: Re: can NOT drop the database no permissions errors ? Greetings, I have a database called 'my_account_database' and I tried to drop it by the command 'drop database my_account_database', it doesn't work. The database is still there, can not be dropped although the tables contained in the database have gone. I subsequently created another 2 databases and could drop them successfully. Tried the 'drop database my_account_database' again before sending this message, still doesn't work. So, what could be the problem? Have you seen this situation before? btw, I can't find out any command which can be used to simply rename a database, please advise!! cheers, feng -- 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]
