mysql process 99.9 percent
hi do you have any idea why the mysql process is 99.9 percent? when i click one hyperlink it takes more than 1 minute to load the page. thank you very much. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.6/111 - Release Date: 9/23/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql process 99.9 percent
Joeffrey Betita wrote: do you have any idea why the mysql process is 99.9 percent? No. when i click one hyperlink it takes more than 1 minute to load the page. thank you very much. Maybe if you provided some details then someone would be able to help you. We're not psychic. What programming language/API? What MySQL version? What OS/platform? How much load is on your database from other users, if any? What is the query being executed when the process is at 99.9%? -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql process 99.9 percent
Hi Most readers in this list expect OS and MySQL server information etc before responding. In our experience this is usually caused by poor SQL query construction e.g. large result set e.g. x million rows and/or index choice, database table design and/or MySQL server setup. Turn-on logging, rerun selected queries with and without the explain syntax, look at the MySQL administrator process list to see which query is the problem whilst the CPU is 99.9 %. Good news is : 'it is solvable'. Mark Joeffrey Betita wrote: hi do you have any idea why the mysql process is 99.9 percent? when i click one hyperlink it takes more than 1 minute to load the page. thank you very much. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.6/111 - Release Date: 9/23/2005 -- Mark Carson Managing Integrated Product Intelligence/IPI CC CK 95/35630/23 Members : MH Carson and AG Carson EMail : [EMAIL PROTECTED]/[EMAIL PROTECTED] snailmail : P.O. Box 36095 Menlo Park 0102, South Africa Cell : +27 83 260 8515 Tel : +27 12 349 2786 Fax : +27 12 349 2787 This e-mail may contain PRIVILEGED AND/OR CONFIDENTIAL INFORMATION intended only for use of the addressee. If you are not the addressee, or the person responsible for delivering it to the person addressed, you may not copy or deliver this to anyone else. If you received this e-mail by mistake, please do not make use of it, nor disclose it's contents to anyone. Thank you for notifying us immediately by return e-mail or telephone. INFORMATION PROVIDED IN THIS ELECTRONIC MAIL IS PROVIDED AS IS WITHOUT WARRANTY REPRESENTATION OR CONDITION OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO CONDITIONS OR OTHER TERMS OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. THE USER ASSUMES THE ENTIRE RISK AS TO THE ACCURACY AND THE USE OF THIS DOCUMENT. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql process 99.9 percent
Joeffrey Betita wrote: hi Jasper the linux distribution is redhat 9, mysql-4.0.15a-log, apache-2.0.47, php-4.3.3 etc. just clicking one hyperlink and searching for any item. OK, would you mind providing the SQL query that's being executed when you click that hyperlink? You see, MySQL doesn't have anything to do with hyperlinks and searching (well, not searching at the user-level, anyway). It only knows about SQL queries. So if something's making it go slow, the culprit is an SQL query, not a hyperlink. Please always reply to the list, not just to me. -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Documenting and visualizing a database
Dan, The download pages are not set up yet, so try the below ftp link: ftp://ftp.mysql.com/pub/mysql/download/mysql-workbench-noinstall-1.0.2-alpha-win32.zip ftp://ftp.mysql.com/pub/mysql/download/mysql-workbench-noinstall-1.0.2-alpha-win32.zip.md5 HTH Raz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Documenting and visualizing a database
Dan, Forgot to say, re. MySQL Workbench - this may be a useful source of info: http://forums.mysql.com/list.php?113 Raz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A Complicated Report
Peter Brawley [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
Shaun,
We have a database that keeps track of days worked and days taken off by
staff. All days worked / taken off are held in a table called Bookings.
Staff work on Projects and each project will have various Work_Types,
days
taken off are not related to projects and are held in
Unavailability_Descriptions. I need to produce a capacity report to show
days worked vs time taken off per staff member per month for a particular
project i.e.
January February
John Smith
Work Type 1 12 ...
Work Type 2 5 ...
Work Type 3 5 ...
Sickness 1 ...
Holiday 2 ...
Total Days 19 ...
Capacity 106%
First, your query's easier to work with when its JOINs are explicit:
SELECT
CONCAT_WS(' ', U.User_Firstname, U.User_Lastname) AS Name,
B.Booking_Type,
WT.Work_Type,
SUM(IF(MONTHNAME(BD.Date) = 'January' 1, 0)) AS 'January',
SUM(IF(MONTHNAME(BD.Date) = 'February'', 1, 0)) AS 'February',
SUM(IF(MONTHNAME(BD.Date) = 'March', 1, 0)) AS 'March',
SUM(IF(MONTHNAME(BD.Date) = 'April', 1, 0)) AS 'April',
SUM(IF(MONTHNAME(BD.Date) = 'May', 1, 0)) AS 'May',
SUM(IF(MONTHNAME(BD.Date) = 'June', 1, 0)) AS 'June',
SUM(IF(MONTHNAME(BD.Date) = 'July', 1, 0)) AS 'July',
SUM(IF(MONTHNAME(BD.Date) = 'August', 1, 0)) AS 'August',
SUM(IF(MONTHNAME(BD.Date) = 'September', 1, 0)) AS 'September',
SUM(IF(MONTHNAME(BD.Date) = 'October', 1, 0)) AS 'October',
SUM(IF(MONTHNAME(BD.Date) = 'November', 1, 0)) AS 'November',
SUM(IF(MONTHNAME(BD.Date) = 'December', 1, 0)) AS 'December'
FROM Bookings AS B,
INNER JOIN Users AS U USING(User_ID)
INNER JOIN Booking_Dates AS BD USING(Booking_ID),
INNER JOIN Work_Types AS WT USING(Work_Type_Id)
INNER JOIN Projects AS P USING(Project_ID)
WHERE YEAR(BD.Date) = 2005
AND P.Project_ID = 32
GROUP BY
Name,
B.Booking_Type,
Work_Type
WITH ROLLUP;
Now, you say unavailability data does not relate to projects, but Bookings
columns user_id, project_id and unavailability_id encode just such a
relationship, don't they? Supposing that's so, it seems to me you could
add a join like
...
FROM Bookings AS B
...
LEFT JOIN unavailability_descriptions USING(unavailability_id)
...
and add SELECTs which sum the result of ISNULL() on the unavailability
data, or whatever other computation you need.
PB
http://www.artfulsoftware.com
-
Shaun wrote:
Hi,
We have a database that keeps track of days worked and days taken off by
staff. All days worked / taken off are held in a table called Bookings.
Staff work on Projects and each project will have various Work_Types, days
taken off are not related to projects and are held in
Unavailability_Descriptions. I need to produce a capacity report to show
days worked vs time taken off per staff member per month for a particular
project i.e.
January February
John Smith
Work Type 1 12 ...
Work Type 2 5 ...
Work Type 3 5 ...
Sickness 1 ...
Holiday 2 ...
Total Days 19 ...
Capacity 106%
Joe Bloggs
Work Type 1 5 ...
Work Type 2 6 ...
Work Type 3 9 ...
Sickness 1 ...
Holiday 1 ...
Total Days 18 ...
Capacity 100%
...
We say that staff have an average of 18 working days per month
availability. I have managed to show the Days worked in a month with the
following query but am having trouble adding the unavailability and
capacity:
SELECT
CONCAT_WS(' ', U.User_Firstname, U.User_Lastname) AS Name,
B.Booking_Type,
WT.Work_Type,
SUM(IF(MONTHNAME(BD.Date) = 'January' AND Year(BD.Date) = '2005', 1, 0))
AS 'January',
SUM(IF(MONTHNAME(BD.Date) = 'February' AND Year(BD.Date) = '2005', 1, 0))
AS 'February',
SUM(IF(MONTHNAME(BD.Date) = 'March' AND Year(BD.Date) = '2005', 1, 0)) AS
'March',
SUM(IF(MONTHNAME(BD.Date) = 'April' AND Year(BD.Date) = '2005', 1, 0)) AS
'April',
SUM(IF(MONTHNAME(BD.Date) = 'May' AND Year(BD.Date) = '2005', 1, 0)) AS
'May',
SUM(IF(MONTHNAME(BD.Date) = 'June' AND Year(BD.Date) = '2005', 1, 0)) AS
'June',
SUM(IF(MONTHNAME(BD.Date) = 'July' AND Year(BD.Date) = '2005', 1, 0)) AS
'July',
SUM(IF(MONTHNAME(BD.Date) = 'August' AND Year(BD.Date) = '2005', 1, 0)) AS
'August',
SUM(IF(MONTHNAME(BD.Date) = 'September' AND Year(BD.Date) = '2005', 1, 0))
AS 'September',
SUM(IF(MONTHNAME(BD.Date) = 'October' AND Year(BD.Date) = '2005', 1, 0))
AS 'October',
SUM(IF(MONTHNAME(BD.Date) = 'November' AND Year(BD.Date) = '2005', 1, 0))
AS 'November',
SUM(IF(MONTHNAME(BD.Date) = 'December' AND Year(BD.Date) = '2005', 1, 0))
AS 'December'
FROM Bookings B, Booking_Dates BD, Users U, Work_Types WT, Projects P
WHERE B.Booking_ID = BD.Booking_ID
AND B.User_ID = U.User_ID
AND B.Work_Type_ID = WT.Work_Type_ID
AND B.Project_ID = P.Project_ID
AND P.Project_ID = 32
AND P.Project_ID = WT.Project_ID
GROUP BY Name, B.Booking_Type, Work_Type WITH ROLLUP;
The problem with adding unavailability to the query is that it is not
related to a project but work types are, also i can't work out how to
how to format(x,d) right justified ?
Hi All, Does anyone know how to use the format() function in such a way that is displays numbers right justified. For example, see the following query, where I want no decimal places: SELECT 123456.789 AS X, FORMAT(123456.789, 0), FORMAT(123456.789, 0)+0; The 1st column is normally displayed as 123456.789 The 2nd column is displayed as string (left justified) as 123,457 BUT the 3rd column, using +0 to force right-justified, cuts off valid data and displays only 123 Cor
Re: how to format(x,d) right justified ?
C.R. Vegelin wrote: Hi All, Does anyone know how to use the format() function in such a way that is displays numbers right justified. For example, see the following query, where I want no decimal places: SELECT 123456.789 AS X, FORMAT(123456.789, 0), FORMAT(123456.789, 0)+0; The 1st column is normally displayed as 123456.789 The 2nd column is displayed as string (left justified) as 123,457 BUT the 3rd column, using +0 to force right-justified, cuts off valid data and displays only 123 Hi Cor, I would use the ROUND() function to do the rounding, as it returns values with the same type as the first argument. http://dev.mysql.com/doc/mysql/en/mathematical-functions.html Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Binary Logs not updating
Hi, Intend on the following replication scenario: A - B - C A is replicating to B no problem. B was copied to C. C was ordered to begin replicating from B, but saw no updates Checked on B, and although mysql 'show variables' states binary logging in 'ON', no updates are being written to the logs except the relay-log. Can someone please advise? Using InnoDB's hotbackup.pl file to perform the backups. Cheers, -- James Green -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Binary Logs not updating
Hi James, If you want to configure a relay replication server, i.e. make the replication to pass data from slave to another slave as A-B-C you need to start B with --log-bin and --log-slave-updates.So check that log-slave-updates has been included in your my.cnf file. You can read more about this http://www.mysql.com/news-and-events/newsletter/2003-11/a000270.html Thanks, Ravi On Monday 26 September 2005 17:01, James Green wrote: Hi, Intend on the following replication scenario: A - B - C A is replicating to B no problem. B was copied to C. C was ordered to begin replicating from B, but saw no updates Checked on B, and although mysql 'show variables' states binary logging in 'ON', no updates are being written to the logs except the relay-log. Can someone please advise? Using InnoDB's hotbackup.pl file to perform the backups. Cheers, -- James Green -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Exists BUG in IN ?
Hello Guys, I'm using the 5.0.12 version of MySQL PRO on AMD64 3000+ with 1 GB of ram and using the InnoDB Tables.. Here is my table: CREATE TABLE `svcs_filecontrol` ( `fc_id` int(10) unsigned NOT NULL auto_increment, `fc_us_id_lockby` int(10) unsigned default NULL, `fc_lbl_id` int(10) unsigned NOT NULL default '0', `fc_nome` varchar(255) NOT NULL default '', `fc_package` text NOT NULL, `fc_arquivo` longblob NOT NULL, `fc_versao` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`fc_id`), KEY `fc_us_id_lockby` (`fc_us_id_lockby`), KEY `fc_lbl_id` (`fc_lbl_id`), KEY `fc_nome` (`fc_nome`), KEY `fc_lbl_nome_pacote_versao` (`fc_lbl_id`,`fc_nome`,`fc_package`(500),`fc_versao`), KEY `fc_versao` (`fc_versao`), KEY `fc_pacote_nome` (`fc_package`(255),`fc_nome`), CONSTRAINT `svcs_filecontrol_ibfk_1` FOREIGN KEY (`fc_lbl_id`) REFERENCES `svcs_label` (`lbl_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 mysql select count(*) from svcs_filecontrol; +--+ | count(*) | +--+ | 1147 | +--+ 1 row in set (0.35 sec) mysql Here is the Select: mysql select f1.fc_package from svcs_filecontrol f1 where f1.fc_id in ( - *select max(f2.fc_id) from svcs_filecontrol f2 - where f2.fc_lbl_Id = 1 - group by f2.fc_package - order by f2.fc_versao desc*) - ; ++ | fc_package | ++ | br.com.escriba | | br.com.escriba.compartilhado.ejb | | br.com.escriba.compartilhado.ejb.interfaces| | br.com.escriba.components.actions | | br.com.escriba.components.config | | br.com.escriba.components.editor | | br.com.escriba.components.framework| | br.com.escriba.components.imageviewer | | br.com.escriba.components.interfacereport | | br.com.escriba.components.pesquisa | | br.com.escriba.components.table| | br.com.escriba.components.wordprocessor| | br.com.escriba.components.wordprocessor.multipage | | br.com.escriba.components.wordprocessor.rtf| | br.com.escriba.components.wordprocessor.rtf.app| | br.com.escriba.components.wordprocessor.rtf.reader | | br.com.escriba.components.wordprocessor.rtf.view | | br.com.escriba.components.wordprocessor.rtf.writer | | br.com.escriba.images.components | | br.com.escriba.testes | | br.com.escriba.util| | br.com.escriba.util.xml| | org.syntax.jedit | | org.syntax.jedit.tokenmarker | | br.com.escriba.compartilhado | | tests | | tests.compartilhado| | tests.compartilhado.ejb| | br.com.escriba.components | ++ 29 rows in set (7.77 sec) mysql The problem is ... 7.77 sec to a simple select :( , is a bug ? because if i run the SUB-SELECT separated of query , this run faster... look: mysql *select max(f2.fc_id) from svcs_filecontrol f2 where f2.fc_lbl_Id = 1 group by f2.fc_package order by f2.fc_versao desc;* +---+ | max(f2.fc_id) | +---+ | 243 | | 2 | | 235 | . +---+ 29 rows in set (0.00 sec) mysql The DESC of this command is: mysql desc select f1.fc_package from svcs_filecontrol f1 where f1.fc_id in (select max(f2.fc_id) from svcs_filecontrol f2 where f2.fc_lbl_Id = 1 group by f 2.fc_package order by f2.fc_versao desc); +++---+--+-+---+-+---+--+--+ | id | select_type| table | type | possible_keys | key | key_len | ref | rows | Extra| +++---+--+-+---+-+---+--+--+ | 1 | PRIMARY| f1| ALL | NULL| NULL | NULL| NULL | 2440 | Using where | | 2 | DEPENDENT SUBQUERY | f2| ref | fc_lbl_id,fc_lbl_nome_pacote_versao | fc_lbl_id | 4 | const | 435 | Using where; Using temporary; Using filesort | +++---+--+-+---+-+---+--+--+ 2 rows in set (0.00 sec) mysql Is a bug ? Tnks in advance... MySQL,InnoDB,hlppp --
create database link in MySQL
mysql，您好！ I can't find any information about - How to create database link in MySQL. Can YOU help me please. 致 礼！ 梁增辉 [EMAIL PROTECTED] 2005-09-26
create database link in MySQL
mysql，您好！ I can't find any information about - How to create database link in MySQL. Can YOU help me please. 致 礼！ 梁增辉 [EMAIL PROTECTED] 2005-09-26
Re: create database link in MySQL
Hi, You cannot. A future version of MySQL 5 will probably allow that, but not currently. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com I can't find any information about - How to create database link in MySQL. Can YOU help me please. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Documenting and visualizing a database
Daniel, MySQL AB recently purchased such a tool, DB Designer, rechristened it MySQL Workbench, just released an alpha version for Windows. You're kidding? I thought I'd seen the last of DB Designer. Where can we get it? I checked out the dev section of the website and looked under graphical clients, but it's nowhere to be found. Look about halfway down the page at http://forums.mysql.com/read.php?113,44108,44108#msg-44108. The ftp addr is ftp://ftp.mysql.com/pub/mysql/download/mysql-workbench-noinstall-1.0.1a-alpha-win32.zip. PB - Daniel Kasak wrote: Peter Brawley wrote: MySQL AB recently purchased such a tool, DB Designer, rechristened it MySQL Workbench, just released an alpha version for Windows. You're kidding? I thought I'd seen the last of DB Designer. Where can we get it? I checked out the dev section of the website and looked under graphical clients, but it's nowhere to be found. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.6/111 - Release Date: 9/23/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored Procedures and Functions
I don't have any experience with stored procedures and find the Documentation in the MYSQL manual a bit sketchy or maybe I am just miss reading it. Can any one point me to some documentation that will help with fully understanding Stored Procedures? What I am trying to migrate out of my program code is a procedure to do the following for update commands. 1) Determine that the ID Field and the Last Updated Timestamp Field is still the same as when the data was originally read. 2) If not the same then Raise an error back to the program so It can determine the action. 3) If the same then lock row and perform update. Future development of this could extend to remove more out of code to handle when the two don't match. The procedure there is 1) Compare Original Field Value to Current Value in Memory if the two don't match then 2) IF the Original Field Value and the Current Value Stored in Table Match then update Field IF not then raise error and prompt user for action. This may be more information than required, but some one out their might be doing similar things that they can point me in the direction of some more documentation or even better still a few Example scripts that I can pull apart and learn from. Regards, Justin Elward Blue Wave Software Pty Limited [EMAIL PROTECTED] Ph. +61 2 4320 6090 Fx. +61 2 4320 6092 --- DISCLAIMER: This message is proprietary to Blue Wave Software Pty Limited (BWS) and is intended solely for the use of the individual or individuals to whom it is addressed. It may contain privileged or confidential information and should not be circulated with out informing BWS prior or used for any purpose other than for what it is intended. If you have received this message in error, please notify the originator immediately. If you are not the intended recipient, you are notified that you are strictly prohibited from using, copying, altering, or disclosing the contents of this message. BWS accepts no responsibility (except where required under Australian law) for loss or damage arising from the use or misuse of the information transmitted by this email including damage from virus. ---
Re: create database link in MySQL
梁增辉 [EMAIL PROTECTED] wrote on 09/26/2005 09:24:11 AM: mysql，您好！ I can't find any information about - How to create database link in MySQL. Can YOU help me please. 致 礼！ 梁增辉 [EMAIL PROTECTED] 2005-09-26 What problem are you trying to solve that you think a database link will help with? I think you mean how do I make a connection from my programming language to MySQL? but I want to make sure. If that's the case, you need to tell us what language you are programming with and on what platform your application will run. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Regarding the cpu utilization of mysqld
Hi, When running a process which is a part of my apllication, mysqld.exe is using 90%-95% of cpu utilization. Here I used mysqld for starting the mysql server. For other processes in the application, CPU utilization is very minimum. I used optimization (for order by, select and like) in building the sql queries used by the process and modified the following parameters in the my.ini file as key_buffer = 64M max_allowed_packet = 1M sort_buffer_size = 4M read_buffer_size = 4M query_cache_size= 16M I am using 256MB RAM. Are my above modifications are correct/useful for minimizing the CPU utilization?. Even after this also mysqld is using around 85% of CPU. Are there any other ways for minimizing the cpu utilization for mysql server. Could you please suggest me the ways for decreasing the CPU utilization for mysqld.exe to minimum. Please help me in this. Thanks, Narasimha Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored procedures MS SQL Server to MySQL
On Sun, Sep 25, 2005 at 03:56:46PM -0500, Peter Brawley wrote: Jim, If you really want to return the result in a variable, declare a user var in the client, declare an OUT var in the SP, have the SP SELECT INTO it, and pass the user var to the SP in the call: SET @x=0; SET GLOBAL log_bin_trust_routine_creators = TRUE; DROP PROCEDURE IF EXISTS CountPhoneNumbers; DELIMITER | CREATE PROCEDURE CountPhoneNumbers ( OUT count INT ) BEGIN SELECT COUNT(*) INTO count FROM customer WHERE Phone IS NOT NULL; END; | DELIMITER ; CALL CountPhoneNumbers(@x); SELECT @x; Thanks Peter, Since she was teaching us about using a variable I am trying your second example. Thanks again, Jim Seymour -- I started using something better than the standard back when IBM advertised OS/2 Warp on TV. As Linux matured I made the transition from OS/2 v4 to Linux. You don't have to accept less than you deserve. Use the Power of the Penguin Registered Linux user #316735 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Regarding the cpu utilization of mysqld
[EMAIL PROTECTED] wrote on 09/26/2005 11:50:11 AM: Hi, When running a process which is a part of my apllication, mysqld.exe is using 90%-95% of cpu utilization. Here I used mysqld for starting the mysql server. For other processes in the application, CPU utilization is very minimum. I used optimization (for order by, select and like) in building the sql queries used by the process and modified the following parameters in the my.ini file as key_buffer = 64M max_allowed_packet = 1M sort_buffer_size = 4M read_buffer_size = 4M query_cache_size= 16M I am using 256MB RAM. Are my above modifications are correct/useful for minimizing the CPU utilization?. Even after this also mysqld is using around 85% of CPU. Are there any other ways for minimizing the cpu utilization for mysql server. Could you please suggest me the ways for decreasing the CPU utilization for mysqld.exe to minimum. Please help me in this. Thanks, Narasimha Is it conceivable that the MySQL server could actually be that busy? How many SQL statements are you processing per second? How much data is transferring into and out of your MySQL server? What are some of the queries appearing in your slow query log? What do the EXPLAINs if those queries tell you? Besides MySQL, what else does that server host? What operating system is on that server? How much memory have you allocated for MySQL usage (some OS's allow for per-appication memory tuning)? Depending on what else is going on or how much memory all of your other applications/daemons have taken up, MySQL could be spending all of your CPU time just paging data. 256MB is not very much memory to install for a database server, especially a shared database server. May I suggest that you add more RAM (at least upgrade to 1GB RAM, more if you can afford it). Shawn Green Database Administrator Unimin Corporation - Spruce Pine
LASSO TIPS for MySQL: 2.9 THE LOGIN ROUTINE
- Hi and Welcome to - LASSO TIPS FOR MYSQL: 2.9 I'm your host, M i l e s. First and foremost, a good place for you to start with Lasso is the following 5 things: The FIRST LASSO TIPS FOR MYSQL - http://www.listsearch.com/lassotalk.lasso?id=143312 The LAST LASSO TIPS FOR MYSQL - http://www.listsearch.com/lassotalk.lasso?id=149158 10 LASSO RESOURCES - http://www.listsearch.com/lassotalk.lasso?id=143018 THE LASSO RESOURCES ADDENDUM - http://www.listsearch.com/lassotalk.lasso?id=143417 OMNIPILOT RESOURCE LIST - http://www.omnipilot.com/index.html?section=Products%2fLasso% 2fResources%20for%20Beginners TODAYS TIP: The Login Routine Today's tip has a lot of information in it, and if you've been reading my posts over the last few weeks/months while Ive been on hiatus on the Lasso list you've seen me make the same post over and over againthe Login Routine! Because there's a lot of information to convey in today's tip, I'll try to keep the chit-chat short. However let me point out from the start that my solution to a LOGIN routine is NOT everyone's solution, it just happens to be my methodology to it. I have spent the better portion of 5 years working out this routine, and over the last year heavily writing it and rewriting it getting it to a point of stable functionality, which is the goal of any good developer - get it to work, get it to work correctly - you get paid. Now if only Doctors and Lawyers worked that way. Its still not perfect but it does work and it works well. My solution entails the use of SESSIONS, so if you haven't used sessions, you will now and you'll thank me for it later. Also this solution does not take into account being MULTIUSER aware, meaning types of users. For that, you'd have to rewrite this logic, completely. I know...Ive done it. With multiple login types you will need to rewrite the better portion of the validation and session logic. And this solution doesn't take that into consideration, but the roots of it are there...my current solution (which you're about to see) was actually stripped of this functionality so that you could see the basics. PART ONE: Login! Vee Don't Need NO Stinkin' Login! Like hell you don't. Nearly ever data driven website on the internet today has some kind of login routine that it requires in order to garner from the user who they are, what their user preferences are (or could be), and then redirect that user to their specific record or series of records that pertain to them and them alone. Because you don't want an end user to see anyone else's data, you want them to see ONLY their information...you have to provide some level of security, or at least make it appear as such. Let me say from the outset that this solution is database independent. Meaning you can use FileMaker for this, if you want, however you'd be better off with a SQL solution across the board for a variety of reasons, not the least of which is (separately) speed and security. ++ TO SEE THE REST OF THIS LASSO TIP ++ THE CURRENT LASSO TIP for MYSQL: http://www.listsearch.com/lassotalk.lasso?id=153613 THE LAST 5 LASSO TIPS for MYSQL: LTƒF 2.8: SQL and Lasso (pt2) - http://www.listsearch.com/ lassotalk.lasso?id=149158 LTƒF 2.7: SQL and Lasso (pt1) - http://www.listsearch.com/ lassotalk.lasso?id=148892 LTƒF 2.6: LASSO STUDIO for ECLIPSE PT 2. - http:// www.listsearch.com/lassotalk.lasso?id=148569 LTƒF 2.5: LASSO STUDIO for ECLIPSE PT 1. - http:// www.listsearch.com/lassotalk.lasso?id=148197 LTƒF 2.4: TEN WASCAWY TIDBITS - http://www.listsearch.com/ lassotalk.lasso?id=147152 M i l e s. ––– LASSO EVANGELIST FOR HIRE: GREAT RATES –– NEEDS WORK ––– -don't know what lasso is ? - http://www.omnipilot.com/ M i l e s [EMAIL PROTECTED] MagicMiles Software (415) 686 - 6164 http://www.lassoevangelist.com/ AIM/Yahoo/MSN: magikmiles Creating custom content management systems for yoga, dance, healing arts, now real estate starting at just $65.00 a month, incld: domain registration, web hosting, email and webmail, and access to TOOLBOXENGINE! A very robust tool. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
add a column if not exists
I want to check if a column exists in a table before I do an alter table to add it. How do I do this in mysql? Thanks. Claire __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedures and Functions
Justin, I don't have any experience with stored procedures and find the Documentation in the MYSQL manual a bit sketchy or maybe I am just miss reading it. Can any one point me to some documentation that will help with fully understanding Stored Procedures? What I am trying to migrate out of my program code is a procedure to do the following for update commands. 1) Determine that the ID Field and the Last Updated Timestamp Field is still the same as when the data was originally read. 2) If not the same then Raise an error back to the program so It can determine the action. 3) If the same then lock row and perform update. First, a quibble about the logic. Between the time a row is re-read for changes and the time you write-lock it, it is possible for another process to update the row. It would seem sounder to explicitly write-lock it up front, make your changes, then unlock it. But secondly, LOCK is not permitted in MySQL stored procs, so if you want to use MyISAM and LOCK, you have to issue the LOCK outside the sproc, eg SET GLOBAL log_bin_trust_routine_creators=TRUE; DROP PROCEDURE IF EXISTS UpdTime; LOCK TABLES test WRITE; DELIMITER | CREATE PROCEDURE UpdTime( IN readid INT, IN newtime TIMESTAMP ) BEGIN UPDATE test SET time=newtime WHERE id=readid; END; | DELIMITER ; UNLOCK TABLES; or more elegantly, convert the table to InnoDB and use a transaction to accomplish the same effect. PB http://www.artfulsoftware.com - Blue Wave Software wrote: I don't have any experience with stored procedures and find the Documentation in the MYSQL manual a bit sketchy or maybe I am just miss reading it. Can any one point me to some documentation that will help with fully understanding Stored Procedures? What I am trying to migrate out of my program code is a procedure to do the following for update commands. 1) Determine that the ID Field and the Last Updated Timestamp Field is still the same as when the data was originally read. 2) If not the same then Raise an error back to the program so It can determine the action. 3) If the same then lock row and perform update. Future development of this could extend to remove more out of code to handle when the two don't match. The procedure there is 1) Compare Original Field Value to Current Value in Memory if the two don't match then 2) IF the Original Field Value and the Current Value Stored in Table Match then update Field IF not then raise error and prompt user for action. This may be more information than required, but some one out their might be doing similar things that they can point me in the direction of some more documentation or even better still a few Example scripts that I can pull apart and learn from. Regards, Justin Elward Blue Wave Software Pty Limited [EMAIL PROTECTED] Ph. +61 2 4320 6090 Fx. +61 2 4320 6092 --- DISCLAIMER: This message is proprietary to Blue Wave Software Pty Limited (BWS) and is intended solely for the use of the individual or individuals to whom it is addressed. It may contain privileged or confidential information and should not be circulated with out informing BWS prior or used for any purpose other than for what it is intended. If you have received this message in error, please notify the originator immediately. If you are not the intended recipient, you are notified that you are strictly prohibited from using, copying, altering, or disclosing the contents of this message. BWS accepts no responsibility (except where required under Australian law) for loss or damage arising from the use or misuse of the information transmitted by this email including damage from virus." --- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.6/111 - Release Date: 9/23/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.6/111 - Release Date: 9/23/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exists BUG in IN ?
Dyego Souza Dantas Leal wrote: Hello Guys, I'm using the 5.0.12 version of MySQL PRO on AMD64 3000+ with 1 GB of ram and using the InnoDB Tables.. snip Here is the Select: mysql select f1.fc_package from svcs_filecontrol f1 where f1.fc_id in ( - *select max(f2.fc_id) from svcs_filecontrol f2 - where f2.fc_lbl_Id = 1 - group by f2.fc_package - order by f2.fc_versao desc*) - ; I assume you added the '*'s for emphasis? I fail to see how ORDER BY in the _subquery_ helps you here. In fact, I think it slows you down, with no effect on the output. Shouldn't this be ORDER BY f1.fc_versao DESC, in the main query? snip The problem is ... 7.77 sec to a simple select :( , is a bug ? because Not so simple, as it turns out. if i run the SUB-SELECT separated of query , this run faster... look: mysql *select max(f2.fc_id) from svcs_filecontrol f2 where f2.fc_lbl_Id = 1 group by f2.fc_package order by f2.fc_versao desc;* +---+ | max(f2.fc_id) | +---+ | 243 | | 2 | | 235 | . +---+ 29 rows in set (0.00 sec) The DESC of this command is: mysql desc select f1.fc_package from svcs_filecontrol f1 where f1.fc_id in (select max(f2.fc_id) from svcs_filecontrol f2 where f2.fc_lbl_Id = 1 group by f 2.fc_package order by f2.fc_versao desc); +++---+--+-+---+-+---+--+--+ | id | select_type| table | type | possible_keys | key | key_len | ref | rows | Extra| +++---+--+-+---+-+---+--+--+ | 1 | PRIMARY| f1| ALL | NULL| NULL | NULL| NULL | 2440 | Using where | | 2 | DEPENDENT SUBQUERY | f2| ref | fc_lbl_id,fc_lbl_nome_pacote_versao | fc_lbl_id | 4 | const | 435 | Using where; Using temporary; Using filesort | +++---+--+-+---+-+---+--+--+ 2 rows in set (0.00 sec) mysql Is a bug ? No (not exactly), it's a missing feature. The problem is that mysql thinks you have a dependent subquery, so your subquery is being run once for each row of your table. Ideally, I suppose the optimizer should notice that it can run the subquery once, then match rows against the IN list using the index, but it doesn't. Subqueries are relatively new in mysql. They work, but they often are not optimized well. Fixing that is on the TO-DO list, but seems to be a low priority. Joins, on the other hand, have been around a long time and are well-optimized. This seems to be a version of the groupwise-maximum problem http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html. If I understand your query, I think it's equivalent to SELECT f1.fc_package from svcs_filecontrol f1 WHERE f1.fc_id = ( SELECT max(f2.fc_id) FROM svcs_filecontrol f2 WHERE f1.fc_package = f2.fc_package AND f2.fc_lbl_Id = 1) ORDER BY f1.fc_versao DESC; which is how the manual solves this problem. I think that will still be a dependent subquery, though, so I'm not sure it will be any faster. A better bet is probably to do this in two steps. Get the ids using the inner query and store them in a temporary table, then join to the temporary table to get the rows you want. Something like: CREATE TEMPORARY TABLE max_ids SELECT MAX(fc_id) AS max_id FROM svcs_filecontrol WHERE fc_lbl_Id = 1 GROUP BY fc_package; SELECT f.fc_package FROM svcs_filecontrol f JOIN max_ids m ON f.fc_id = m.max_id ORDER BY f.fc_versao DESC; DROP TABLE max_ids; That may look like a pain, but it should certainly be fast. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Recover from plain text log
OK, I am ready to get flamed. I have done so much stuff wrong on this MySQL server that it finally came back to bite me. I've learned my lesson and hopefully someone here will find it in their heart to help me. I have a database which is updated quite often, running from a web server. There is no binary log. There is, however, a text query log going back to before my problem. The server had an I/O error and apparently corrupted the files for my database. I have a backup of the database from some time back, but need to recover the data since that time. I know there is a way to parse binary logs and recover from them, but is there a way to do the same for text logs? I think I can write a bash script to reformat the file into a series of queries, but I was hoping there was an easier way. Thanks so much, anyone willing to help. Todd Ellison -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: add a column if not exists
Claire, I want to check if a column exists in a table before I do an alter table to add it. How do I do this in mysql? Thanks. If you are using MySQL 5.0, query information_schema.columns (http://dev.mysql.com/doc/mysql/en/columns-table.html) for the table and column. Otherwise use SHOW COLUMNS FROM tablename. PB http://www.artfulsoftware.com - Claire Lee wrote: I want to check if a column exists in a table before I do an alter table to add it. How do I do this in mysql? Thanks. Claire __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.6/111 - Release Date: 9/23/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INDEX
How do I set Index to enforce that ONLY 1 QAID can own that order number, but nothing else? For example: QAID [order] ErrorType 11223 123456789 19 'ALLOWED TO HAVE different ErrorTypes as long as one QAID number uses it. 11223 123456789 19 'VIOLATED cannot have same ErrorTypes - UNIQUE INDEX (QAID,[order],ErrorType) 11223 123456789 15 11223 123456789 NULL 11240 123456789 14 'VIOLATED -- order was owned by QAID 11223, therefore can not used by different QAID -- Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html
Re: INDEX
Scott Hamm [EMAIL PROTECTED] wrote on 09/26/2005 01:59:52 PM: How do I set Index to enforce that ONLY 1 QAID can own that order number, but nothing else? For example: QAID [order] ErrorType 11223 123456789 19 'ALLOWED TO HAVE different ErrorTypes as long as one QAID number uses it. 11223 123456789 19 'VIOLATED cannot have same ErrorTypes - UNIQUE INDEX (QAID,[order],ErrorType) 11223 123456789 15 11223 123456789 NULL 11240 123456789 14 'VIOLATED -- order was owned by QAID 11223, therefore can not used by different QAID Please post the output from SHOW CREATE TABLE. That way I can see not only what your columns are actually called, I can also see what other keys have been defined on the table. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
mysql/php date functions..
hi... i've got a question/problem that i can't seem to figure out. it should be simple/straightforward. i'm creating a test tbl id int t1 timestamp when i do a 'select * from tbl' i get what looks like a default date/time format in the timestamp column. i do a, (from mysql) 'insert into tbl (id, t1) values (1, 33)' and it fails... i do a, 'insert into tbl (id, t1) values (1, now())' and it works... after i do the 'now()', i see what also looks like a date/time format in the timestamp column. any ideas/comments/thoughts as to what might be going on... thanks... ps. my goal is to create a quick/test php app where i can use the php 'time()' functin and insert it into the mysql tbl!!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql/php date functions..
bruce wrote: hi... i've got a question/problem that i can't seem to figure out. it should be simple/straightforward. i'm creating a test tbl id int t1 timestamp when i do a 'select * from tbl' i get what looks like a default date/time format in the timestamp column. i do a, (from mysql) 'insert into tbl (id, t1) values (1, 33)' and it fails... 33 is not a valid date/time. Did you try : 'insert into tbl (id) values (1)' ? i do a, 'insert into tbl (id, t1) values (1, now())' and it works... after i do the 'now()', i see what also looks like a date/time format in the timestamp column. any ideas/comments/thoughts as to what might be going on... Yes, it is working. thanks... ps. my goal is to create a quick/test php app where i can use the php 'time()' functin and insert it into the mysql tbl!!! Why? Timestamps update themselves. You don't need to insert them. You might try reading the manual for a description of how timestamp works. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Verify DML(Mysterious DROP TABLE command in production)
Guys, Our production database had a table that was suddently dropped a mystery. I need to find out if there is a way to find the user account that performed this function. No one is taking ownership and I need to know if there is a way to find out. Thanks in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.0.13-rc has been released
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, MySQL 5.0.13, a new version of the popular Open Source Database Management System, has been released. The Community Edition is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time - if you can't find this version on some mirror, please try again later or choose another download site. This is the first published release candidate (previously known as gamma release) in the 5.0 series. All attention will continue to be focused on fixing bugs and stabilizing 5.0 for the upcoming production release. We would like to encourage you to give this release a try! Your feedback is very important to us. Please refer to our bug database at http://bugs.mysql.com/ for more details about the individual bugs fixed in this version. News from the ChangeLog: Functionality added or changed * Better detection of connection timeout for replication servers on Windows allows elimination of extraneous Lost connection errors in the error log. (Bug #5588 (http://bugs.mysql.com/5588)) * The counters for the Key_read_requests, Key_reads, Key_write_requests, and Key_writes status variables were changed from unsigned long to unsigned longlong to accommodate larger variables without rollover. (Bug #12920 (http://bugs.mysql.com/12920)) * The restriction on the use of PREPARE, EXECUTE, and DEALLOCATE PREPARE within stored procedures was lifted. The restriction still applies to stored functions and triggers. (Bug #10975 (http://bugs.mysql.com/10975), Bug #7115 (http://bugs.mysql.com/7115), Bug #10605 (http://bugs.mysql.com/10605)) * A new command line argument was added to mysqld to ignore client character set information sent during handshake, and use server side settings instead, to reproduce 4.0 behaviour (Bug #9948 (http://bugs.mysql.com/9948)): mysqld --skip-character-set-client-handshake * OPTIMIZE TABLE and HANDLER now are prohibited in stored procedures and functions and in triggers. (Bug #12953 (http://bugs.mysql.com/12953), Bug #12995 (http://bugs.mysql.com/12995)) * InnoDB: The TRUNCATE TABLE statement for InnoDB tables always resets the counter for an AUTO_INCREMENT column now, regardless of whether there is a foreign key constraint on the table. (Beginning with 5.0.3, TRUNCATE TABLE reset the counter, but only if there was no such constraint.) (Bug #11946 (http://bugs.mysql.com/11946)) * The LEAST() and GREATEST() functions used to return NULL only if all arguments were NULL. Now they return NULL if any argument is NULL, the same as Oracle. (Bug #12791 (http://bugs.mysql.com/12791)) * Two new collations have been added for Esperanto: utf8_esperanto_ci and ucs2_esperanto_ci. * Reorder network startup to come after all other initialization, particularly storage engine startup which can take a long time. This also prevents MySQL from being run on a privileged port (any port under 1024) unless run as the root user. (Bug #11707 (http://bugs.mysql.com/11707)) * The Windows binary packages are now compiled with the Microsoft Visual Studio 2003 compiler instead of Microsoft Visual C++ 6.0 * The binaries compiled with the Intel icc compiler are now built using icc 9.0 instead of icc 8.1. You will have to install new versions of the Intel icc runtime libraries, which are available from here: (http://dev.mysql.com/downloads/os-linux.html) Bugs fixed * Within a stored procedure, fetching a large number of rows in a loop using a cursor could result in a server crash or an out of memory error. Also, values inserted within a stored procedure using a cursor were interpreted as latin1 even if character set variables had been set to a different character set. (Bug #6513 (http://bugs.mysql.com/6513), Bug #9819 (http://bugs.mysql.com/9819)) * For a server compiled with yaSSL, clients that used MySQL Connector/J were not able to establish SSH connections. (Bug #13029 (http://bugs.mysql.com/13029)) * When used in view definitions, DAYNAME(expr), DAYOFWEEK(expr), WEEKDAY(expr) were incorrectly treated as though the expression was TO_DAYS(expr) or TO_DAYS(TO_DAYS(expr)). (Bug #13000 (http://bugs.mysql.com/13000)) * Incorrect implicit nesting of joins caused the parser to fail on queries of the form SELECT ... FROM t1 JOIN t2 JOIN t3 ON t1.t1col = t3.t3col with an Unknown column 't1.t1col' in 'on clause' error. (Bug #12943 (http://bugs.mysql.com/12943)) * NDB: A cluster shutdown following the crash of a data node would fail to terminate the remaining node processes, even though ndb_mgm showed the shutdown request as having been completed. (Bug #10938
Re: MySQL 5.0.13-rc has been released
Is this a release candidate as the version in your message subject would imply, or is it an actual release, as your message states? Or does MySQL AB even bother to differentiate the two? - Original Message - From: Lenz Grimmer [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com; [EMAIL PROTECTED] Sent: Monday, September 26, 2005 1:51 PM Subject: MySQL 5.0.13-rc has been released MySQL 5.0.13, a new version of the popular Open Source Database Management System, has been released. The Community Edition is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 5.0.13-rc has been released
release candidate is just an alias for 'gamma version' of the s/w released from MySQL AB. What he said is that MySQL first gamma version has been released in the 5.0 series. Till now it is in beta phase. sujay -Original Message- From: Jim McAtee [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 27, 2005 2:11 AM To: Lenz Grimmer Cc: mysql@lists.mysql.com Subject: Re: MySQL 5.0.13-rc has been released Is this a release candidate as the version in your message subject would imply, or is it an actual release, as your message states? Or does MySQL AB even bother to differentiate the two? - Original Message - From: Lenz Grimmer [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com; [EMAIL PROTECTED] Sent: Monday, September 26, 2005 1:51 PM Subject: MySQL 5.0.13-rc has been released MySQL 5.0.13, a new version of the popular Open Source Database Management System, has been released. The Community Edition is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. -- 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: INDEX
Scott Hamm [EMAIL PROTECTED] wrote on 09/26/2005 02:21:38 PM: On 9/26/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Scott Hamm [EMAIL PROTECTED] wrote on 09/26/2005 01:59:52 PM: How do I set Index to enforce that ONLY 1 QAID can own that order number, but nothing else? For example: QAID [order] ErrorType 11223 123456789 19 'ALLOWED TO HAVE different ErrorTypes as long as one QAID number uses it. 11223 123456789 19 'VIOLATED cannot have same ErrorTypes - UNIQUE INDEX (QAID,[order],ErrorType) 11223 123456789 15 11223 123456789 NULL 11240 123456789 14 'VIOLATED -- order was owned by QAID 11223, therefore can not used by different QAID Please post the output from SHOW CREATE TABLE. That way I can see not only what your columns are actually called, I can also see what other keys have been defined on the table. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Scott's original table def, reformatted CREATE TABLE `qaerrors` ( `QAID` int(10) default NULL , `ErrorTypeID` int(10) default NULL , `Order` varchar(9) default NULL , `ID` int(10) NOT NULL default '0' , PRIMARY KEY (`ID`) , UNIQUE KEY `Index_2` (`Order`,`ErrorTypeID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 I am not sure that any DB can do what you want with just one table. Here's why. There are only 7 possible combinations of UNIQUE indexes you can create by using 3 columns from the same table. 3 of them are each column individually (which obviously won't work as a solution). One you listed, that leaves just these two to consider. With a UNIQUE(`QAID`,`Order`), you will be limited to only one row with the same (`QAID`,`Order`) pair. This will not work as you said that there can be multiples of a pair so long as each instance of a pair is matched with a different ErrorTypeID. It would prevent this: 11223 123456789 19 11223 123456789 15 because the same (`QAID`,`Order`) pair would appear twice. Not what you wanted. With UNIQUE(`QAID`, `ErrorTypeID`), the following pair of records would be allowed: 11223 123456789 19 11240 123456789 14 In this set of data the same `Order` is now assigned two different QAID values. Also what you didn't want. the last combination: UNIQUE(`QAID`,`Order`,`ErrorTypeID`), each triplet can only appear once but that still doesn't prevent the case of 11223 123456789 19 11240 123456789 14 So, indexes alone can't work. However, I believe a Foreign Key will do the trick. First, we need to create a table to hold the ownership information for any `Order` value. The UNIQUE index will prevent any `Order` value from being listed more than once which means that there can only be one possible `QAID` value for any `Order` value on this table. CREATE TABLE qaerrowner ( `QAID` int(10) default NULL , `Order` varchar(9) default NULL , UNIQUE (`Order`) , KEY(`QAID`,`Order`) )ENGINE=InnoDB; Now, we need to slap a constraint on `qaerrors` so that it is compelled to use only (`QAID`, `Order`) pairs that exist in qaerrowner: ALTER TABLE qaerrors ADD KEY (`QAID`,`Order`) , ADD CONSTRAINT FOREIGN KEY (`QAID`,`Order`) REFERENCES qaerrowner(`QAID`, `Order`); (According to http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html, this is valid syntax. However I don't have the time to test it with some live data. Now you see why I added the extra KEY() to qaerrowner. If I hadn't this definition would have failed.) The drawback is, you have to write to two tables whenever you want to create a `qaerrors` record. Once to identify the owner (use an INSERT IGNORE to `qaerrowner`) and a second time to log the actual error (another INSERT IGNORE, this time to `qaerrors`). Check the number of rows affected to determine if the record made it in or not. If you didn't affect any records, it was blocked by the FK. I know this may seem a bit convoluted but this is exactly the situation that FKs were developed to enforce. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: MySQL 5.0.13-rc has been released
Was this not clear enough or did you fail to read the entire message? Just curious. This is the first published release candidate (previously known as gamma release) in the 5.0 series. All attention will continue to be focused on fixing bugs and stabilizing 5.0 for the upcoming production release. Jeremiah Gowdy Senior Software Engineer FreedomVOICE Systems http://www.freedomvoice.com - Original Message - From: Jim McAtee [EMAIL PROTECTED] To: Lenz Grimmer [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, September 26, 2005 1:40 PM Subject: Re: MySQL 5.0.13-rc has been released Is this a release candidate as the version in your message subject would imply, or is it an actual release, as your message states? Or does MySQL AB even bother to differentiate the two? - Original Message - From: Lenz Grimmer [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com; [EMAIL PROTECTED] Sent: Monday, September 26, 2005 1:51 PM Subject: MySQL 5.0.13-rc has been released MySQL 5.0.13, a new version of the popular Open Source Database Management System, has been released. The Community Edition is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. -- 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]
Ram disk and solid state disks for data acceleration
Hey all, Any one with any experience with using solid state disks and or ram disks to store mysql data/tmp directoies/logs? How much performance gain do you think I would see? We have a very high write/read database using myisam tables. We are unable to migrate to other table types because of compatibility issues. The entire data base is currently about 6gb we expect it to grow to 16gb. Our application is very time sensitive. We are running on DL585's with 2-2.2ghz x86-64 cpu's and 10gb ram. Any help to keep our performance up would be appreciated. Thanks Byron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Verify DML(Mysterious DROP TABLE command in production)
Clyde Lewis [EMAIL PROTECTED] wrote on 09/26/2005 02:44:40 PM: Guys, Our production database had a table that was suddently dropped a mystery. I need to find out if there is a way to find the user account that performed this function. No one is taking ownership and I need to know if there is a way to find out. Thanks in advance Have you checked your binlogs? Each command is listed along with the user account that was used to execute it and a timestamp for proper replication. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: MySQL 5.0.13-rc has been released
Jim McAtee wrote: Is this a release candidate as the version in your message subject would imply, or is it an actual release, as your message states? Or does MySQL AB even bother to differentiate the two? - Original Message - From: Lenz Grimmer [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com; [EMAIL PROTECTED] Sent: Monday, September 26, 2005 1:51 PM Subject: MySQL 5.0.13-rc has been released MySQL 5.0.13, a new version of the popular Open Source Database Management System, has been released. The Community Edition is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Had you continued reading the message, you would have seen (first line, paragraph 3), This is the first published release candidate (previously known as gamma release) in the 5.0 series. In other words, the subject and message are in agreement. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: add a column if not exists
Hi, 2005/9/26, Peter Brawley [EMAIL PROTECTED]: Claire, I want to check if a column exists in a table before I do an alter table to add it. How do I do this in mysql? Thanks. other solution, do your query in all case and check for the return error (if any). mysql alter table board add message varchar(255) not null default ''; ERROR 1060 (42S21): Duplicate column name 'message' So, if you get back that error, the column already exists. So if it exists the table is left untouched, otherwise it does what you want. (yeah, Information_schema would be better, but 5.0 is in gamma) -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Verify DML(Mysterious DROP TABLE command in production)
I did check the binlog and saw that the command ran along with the timestamp. Is there a way to figure out who ran the command? [EMAIL PROTECTED] wrote: Clyde Lewis [EMAIL PROTECTED] wrote on 09/26/2005 02:44:40 PM: Guys, Our production database had a table that was suddently dropped a mystery. I need to find out if there is a way to find the user account that performed this function. No one is taking ownership and I need to know if there is a way to find out. Thanks in advance Have you checked your binlogs? Each command is listed along with the user account that was used to execute it and a timestamp for proper replication. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Avg row length is varying a lot from oracle to MySQL
Jeff wrote: Each row in the table takes around 600 bytes, taking every thing into consideration and assuming every field is used to its maximum bytes. But the major portion of this 600 bytes are composed of varchar's (100 + 150 + 50 + 16 + 50 + 20 + 9..) Out of these 400 bytes we generally use only 40 to 50 bytes. Most of them are reserved for future uses. So strictly speaking even including the space taken by the indexes, the avg length should not come more than 250 bytes. If you have a varchar(50) but usually only use 10 in those fields MySQL still counts the unused 40 for the total byte count of the row, so you must count them. An empty varchar(50) field still uses 50 bytes. No, it doesn't. Storage for a VARCHAR is L + 1, where L is length * bytes per char. See the manual for details http://dev.mysql.com/doc/mysql/en/storage-requirements.html. Also, I believe text and blob fields are always counted as 255 bytes regardless of your settings. No, it's L + 2. Ordered indexes are 10bytes per column (in the index) per row. I believe there is also some paging overhead so generally take your calculated row size and multiply by 1.1. Where do you see that in the manual? I'm under the impression that index size is related to the size of the column being indexed http://dev.mysql.com/doc/mysql/en/key-space.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sort Aborted errors after upgrade to 4.1.14
Hi All, I know there was an earlier post regarding sort aborted error after upgrading to 4.1.14 from 4.0.xxx, I am getting this error too, and it never occured to me before, I found out the query which is doing this, SELECT * FROM thread AS thread LEFT JOIN deletionlog AS deletionlog ON (deletionlog.primaryid = thread.threadid AND deletionlog.type = 'thread') WHERE forumid = 61 AND visible = 1 AND open 10 AND deletionlog.primaryid IS NULL ORDER BY lastpost DESC LIMIT 1 mysql error: Error writing file '/tmp/MYt96glR' (Errcode: 28) mysql error number: 3 The permissions on the /tmp have not changed and are set properly, the user mysql can create large files in the /tmp directory ( checked after su to mysql ) , I tried to increase the sort_buffer_size to 1M from 0.5M, surprisingly everything else works fine, did any of you guys have similar issues. Thanks Kishore Jalleda
Re: Avg row length is varying a lot from oracle to MySQL
Sujay Koduri wrote: we are converting our oracle DB to MySQL DB. One problem i see is that the abg row length in MySQL is much higher compared to that of Oracle. In oracle it is around 180 bytes and in MySQL it is around 686 bytes. So as a result, MySQL is taking more space to store the same number of records. Can someone please explain me if this is the intended behaviour or i am missing out something. I am also including the o/p of desc table_name of the same table on both the databases. How are you measuring the size of a row in mysql? What makes you think it is averaging 686 bytes? Sujay Koduri also wrote: Each row in the table takes around 600 bytes, taking every thing into consideration and assuming every field is used to its maximum bytes. But the major portion of this 600 bytes are composed of varchar's (100 + 150 + 50 + 16 + 50 + 20 + 9..) Out of these 400 bytes we generally use only 40 to 50 bytes. Most of them are reserved for future uses. So strictly speaking even including the space taken by the indexes, the avg length should not come more than 250 bytes. Umm, using about 50 bytes out of 400 in variable length columns saves about 350 bytes. 600 - 350 = 250, so you should expect about 250 bytes used _before_ indexes. On the other hand, you say it's only 180 in Oracle, so perhaps the estimate is off. OK, looking at your column definitions, I see 118 bytes worth of fixed-width columns, plus 11 to 436 bytes worth of varchar columns, yielding 129 bytes per row with empty varchars, 554 bytes per row with full varchars. With 40 to 50 chars used in the varchars, that would be around 180 bytes per row, just as in Oracle (not including any indexes). Of course, this is assuming you are using 1-byte chars. I can't imagine how that could take 686 bytes per row in mysql. It could just be a failure of my imagination, but you haven't yet shown us how you arrived at that number. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.13-rc has been released
Lenz Grimmer wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, MySQL 5.0.13, a new version of the popular Open Source Database Management System, has been released. The Community Edition is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Has the md5sum been updated for the tarball (tar.gz) download? I tried two sites and got the same size file, i.e,. 19209618 bytes, but the md5sum does not agree. Thanks, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Documenting and visualizing a database
Raz wrote: Dan, The download pages are not set up yet, so try the below ftp link: ftp://ftp.mysql.com/pub/mysql/download/mysql-workbench-noinstall-1.0.2-alpha-win32.zip ftp://ftp.mysql.com/pub/mysql/download/mysql-workbench-noinstall-1.0.2-alpha-win32.zip.md5 The ftp site doesn't allow directory listings. I don't suppose there's a Linux download yet? -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
money fields
What's a good (or best) field type for money or currency data. Mysql doesn't have a MONEY type for structure, so what would I have the best luck with? Thanks, Chris Chris Ripley [EMAIL PROTECTED] KOZE Radio __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: money fields
Chris Ripley wrote: What's a good (or best) field type for money or currency data. Mysql doesn't have a MONEY type for structure, so what would I have the best luck with? double -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: money fields
Daniel Kasak wrote: Chris Ripley wrote: What's a good (or best) field type for money or currency data. Mysql doesn't have a MONEY type for structure, so what would I have the best luck with? double No way. You should *never* use floating-point types for currency if you care about precision. You'll experience all sorts of weird rounding and comparison errors. See [1] for more information. DECIMAL would suit your needs better -- from [2]: The DECIMAL and NUMERIC types [...] are used to store values for which it is important to preserve exact precision, for example with monetary data. [1] http://en.wikipedia.org/wiki/Floating_point [2] http://dev.mysql.com/doc/mysql/en/numeric-types.html -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: money fields
Chris, What's a good (or best) field type for money or currency data. Mysql doesn't have a MONEY type for structure, so what would I have the best luck with? Someone suggested double, but with big sums that can give you rounding errors. Try DECIMAL. PB -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.6/111 - Release Date: 9/23/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: money fields
Jasper Bryant-Greene wrote: No way. You should *never* use floating-point types for currency if you care about precision. You'll experience all sorts of weird rounding and comparison errors. See [1] for more information. DECIMAL would suit your needs better -- from [2]: The DECIMAL and NUMERIC types [...] are used to store values for which it is important to preserve exact precision, for example with monetary data. [1] http://en.wikipedia.org/wiki/Floating_point [2] http://dev.mysql.com/doc/mysql/en/numeric-types.html I see. Back to the drawing board then. I'm *sure* I saw someone else recommend that ... many years ago now. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA INFILE Syntax Error
I am attempting to import a large file with data in this format: 1923158|GA|1996 Olympic Yachting Cauldron|park|Chatham|13|051 |320446N|0810502W|32.07944|- 81.08389Savannah With this command: LOAD DATA LOCAL INFILE 'C:\\PHP\\FL_DECI.txt' INTO TABLE locations2 FIELDS TERMINATED BY '|' LINES TERMINATED BY \r (@d,@d,LOCNAME,LOCTYPE,@d,STATE,COUNTY,@d,@d,LNG,LAT,@d,@d,@d,@d,@d,@d,@d,@= d); The @d is a dummy variable for information I do not need to import. The table structure looks like this: +-+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+-+--+-+-++ | ID | int(11) | | PRI | NULL | auto_increment | | STATE | int(11) | YES | | NULL | | | LOCNAME | varchar(25) | YES | | NULL | | | LOCTYPE | varchar(10) | YES | | NULL | | | COUNTY | int(11) | YES | | NULL | | | CTRLAT | float | YES | | NULL | | | CTRLNG | float | YES | | NULL | | +-+-+--+-+-++ And here is the error I get: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@d,@d ,LOCNAME,LOCTYPE,@d,STATE,COUNTY,@d,@d,LNG,LAT,@d,@d,@d,@d,@d,@d,@d,@d)' at line 5 This error is driving me nuts! Any help would be appreciated, as this is a rather large file. Jason
Re: LOAD DATA INFILE Syntax Error
http://mirror.tomato.it/mysql/doc/mysql/en/load-data.html Bob Cochran Jason Ferguson wrote: I am attempting to import a large file with data in this format: 1923158|GA|1996 Olympic Yachting Cauldron|park|Chatham|13|051 |320446N|0810502W|32.07944|- 81.08389Savannah With this command: LOAD DATA LOCAL INFILE 'C:\\PHP\\FL_DECI.txt' INTO TABLE locations2 FIELDS TERMINATED BY '|' LINES TERMINATED BY \r (@d,@d,LOCNAME,LOCTYPE,@d,STATE,COUNTY,@d,@d,LNG,LAT,@d,@d,@d,@d,@d,@d,@d,@= d); The @d is a dummy variable for information I do not need to import. The table structure looks like this: +-+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+-+--+-+-++ | ID | int(11) | | PRI | NULL | auto_increment | | STATE | int(11) | YES | | NULL | | | LOCNAME | varchar(25) | YES | | NULL | | | LOCTYPE | varchar(10) | YES | | NULL | | | COUNTY | int(11) | YES | | NULL | | | CTRLAT | float | YES | | NULL | | | CTRLNG | float | YES | | NULL | | +-+-+--+-+-++ And here is the error I get: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@d,@d ,LOCNAME,LOCTYPE,@d,STATE,COUNTY,@d,@d,LNG,LAT,@d,@d,@d,@d,@d,@d,@d,@d)' at line 5 This error is driving me nuts! Any help would be appreciated, as this is a rather large file. Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE Syntax Error
Robert L Cochran wrote: http://mirror.tomato.it/mysql/doc/mysql/en/load-data.html There's a lot to read there for one small paragraph, so from the above link: Before MySQL 5.0.3, the column list must contain only names of columns in the table being loaded, and the SET clause is not supported. As of MySQL 5.0.3, the column list can contain either column names or user variables, and the SET clause is supported. This enables you to assign input values to user variables, and then perform transformations on those values before assigning the result to columns. Bob: please don't top-post. Jasper Jason Ferguson wrote: I am attempting to import a large file with data in this format: 1923158|GA|1996 Olympic Yachting Cauldron|park|Chatham|13|051 |320446N|0810502W|32.07944|- 81.08389Savannah With this command: LOAD DATA LOCAL INFILE 'C:\\PHP\\FL_DECI.txt' INTO TABLE locations2 FIELDS TERMINATED BY '|' LINES TERMINATED BY \r (@d,@d,LOCNAME,LOCTYPE,@d,STATE,COUNTY,@d,@d,LNG,LAT,@d,@d,@d,@d,@d,@d,@d,@= d); The @d is a dummy variable for information I do not need to import. The table structure looks like this: +-+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+-+--+-+-++ | ID | int(11) | | PRI | NULL | auto_increment | | STATE | int(11) | YES | | NULL | | | LOCNAME | varchar(25) | YES | | NULL | | | LOCTYPE | varchar(10) | YES | | NULL | | | COUNTY | int(11) | YES | | NULL | | | CTRLAT | float | YES | | NULL | | | CTRLNG | float | YES | | NULL | | +-+-+--+-+-++ And here is the error I get: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@d,@d ,LOCNAME,LOCTYPE,@d,STATE,COUNTY,@d,@d,LNG,LAT,@d,@d,@d,@d,@d,@d,@d,@d)' at line 5 This error is driving me nuts! Any help would be appreciated, as this is a rather large file. Jason -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE Syntax Error
I've been over that page before posting, with no luck. It might be an obvious error in syntax, but I can't figure it out. Jason On 9/26/05, Robert L Cochran [EMAIL PROTECTED] wrote: http://mirror.tomato.it/mysql/doc/mysql/en/load-data.html Bob Cochran Jason Ferguson wrote: I am attempting to import a large file with data in this format: 1923158|GA|1996 Olympic Yachting Cauldron|park|Chatham|13|051 |320446N|0810502W|32.07944|- 81.08389Savannah With this command: LOAD DATA LOCAL INFILE 'C:\\PHP\\FL_DECI.txt' INTO TABLE locations2 FIELDS TERMINATED BY '|' LINES TERMINATED BY \r (@d,@d,LOCNAME,LOCTYPE,@d,STATE,COUNTY,@d,@d,LNG,LAT,@d,@d,@d,@d,@d,@d,@d,@= d); The @d is a dummy variable for information I do not need to import. The table structure looks like this: +-+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+-+--+-+-++ | ID | int(11) | | PRI | NULL | auto_increment | | STATE | int(11) | YES | | NULL | | | LOCNAME | varchar(25) | YES | | NULL | | | LOCTYPE | varchar(10) | YES | | NULL | | | COUNTY | int(11) | YES | | NULL | | | CTRLAT | float | YES | | NULL | | | CTRLNG | float | YES | | NULL | | +-+-+--+-+-++ And here is the error I get: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@d,@d ,LOCNAME,LOCTYPE,@d,STATE,COUNTY,@d,@d,LNG,LAT,@d,@d,@d,@d,@d,@d,@d,@d)' at line 5 This error is driving me nuts! Any help would be appreciated, as this is a rather large file. Jason
Re: LOAD DATA INFILE Syntax Error
Okay, now I get it. I was using the 4.1 series. Looks like an upgrade is in order. Jason On 9/26/05, Jasper Bryant-Greene [EMAIL PROTECTED] wrote: Robert L Cochran wrote: http://mirror.tomato.it/mysql/doc/mysql/en/load-data.html There's a lot to read there for one small paragraph, so from the above link: Before MySQL 5.0.3, the column list must contain only names of columns in the table being loaded, and the SET clause is not supported. As of MySQL 5.0.3, the column list can contain either column names or user variables, and the SET clause is supported. This enables you to assign input values to user variables, and then perform transformations on those values before assigning the result to columns. Bob: please don't top-post. Jasper Jason Ferguson wrote: I am attempting to import a large file with data in this format: 1923158|GA|1996 Olympic Yachting Cauldron|park|Chatham|13|051 |320446N|0810502W|32.07944|- 81.08389Savannah With this command: LOAD DATA LOCAL INFILE 'C:\\PHP\\FL_DECI.txt' INTO TABLE locations2 FIELDS TERMINATED BY '|' LINES TERMINATED BY \r (@d,@d,LOCNAME,LOCTYPE,@d,STATE,COUNTY,@d,@d,LNG,LAT,@d,@d,@d,@d,@d,@d,@d,@= d); The @d is a dummy variable for information I do not need to import. The table structure looks like this: +-+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+-+--+-+-++ | ID | int(11) | | PRI | NULL | auto_increment | | STATE | int(11) | YES | | NULL | | | LOCNAME | varchar(25) | YES | | NULL | | | LOCTYPE | varchar(10) | YES | | NULL | | | COUNTY | int(11) | YES | | NULL | | | CTRLAT | float | YES | | NULL | | | CTRLNG | float | YES | | NULL | | +-+-+--+-+-++ And here is the error I get: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@d,@d ,LOCNAME,LOCTYPE,@d,STATE,COUNTY,@d,@d,LNG,LAT,@d,@d,@d,@d,@d,@d,@d,@d)' at line 5 This error is driving me nuts! Any help would be appreciated, as this is a rather large file. Jason -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort Aborted errors after upgrade to 4.1.14
In the last episode (Sep 26), Kishore Jalleda said: I know there was an earlier post regarding sort aborted error after upgrading to 4.1.14 from 4.0.xxx, I am getting this error too, and it never occured to me before, I found out the query which is doing this, mysql error: Error writing file '/tmp/MYt96glR' (Errcode: 28) $ perror 28 OS error code 28: No space left on device You need to either grow /tmp, or set tmpdir to some other volume. -- 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: Lost connection to MySQL server during query when calling stored procedure
On Thursday, 22 September 2005 at 17:06:32 +1200, Jasper Bryant-Greene wrote: Jasper Bryant-Greene wrote: I have a stored procedure defined as follows: ... Sometimes when I execute this stored procedure ... I get the error Lost connection to MySQL server during query. I am using MySQL 5.0.12-beta-log on Gentoo Linux x86. This only seems to happen when accessing MySQL from PHP's MySQLi API, not when accessing it using the mysql command-line client. Is this a bug I should report to MySQL or to PHP? I'm not able to test other APIs at the moment. If the server dies, that's a MySQL problem. Take a look at http://bugs.mysql.com/ for information on how to report it. In particular, the server logs should give some information. Greg -- Greg Lehey, Senior Software Engineer MySQL AB, http://www.mysql.com/ Echunga, South Australia See complete headers for address and phone numbers. Are you MySQL certified? http://www.mysql.com/certification/ pgpjYgw1PlK8L.pgp Description: PGP signature
Re: mysql process 99.9 percent
On Monday, 26 September 2005 at 9:29:32 +0200, mark carson wrote: Joeffrey Betita wrote: hi do you have any idea why the mysql process is 99.9 percent? when i click one hyperlink it takes more than 1 minute to load the page. thank you very much. Most readers in this list expect OS and MySQL server information etc before responding. In our experience this is usually caused by poor SQL query construction e.g. large result set e.g. x million rows and/or index choice, database table design and/or MySQL server setup. Turn-on logging, rerun selected queries with and without the explain syntax, look at the MySQL administrator process list to see which query is the problem whilst the CPU is 99.9 %. FWIW, we have at least one case where the server gets itself into a loop for no obvious reason. See http://bugs.mysql.com/bug.php?id=414 and http://bugs.mysql.com/bug.php?id=12061 for more details. If the problem at hand is related to this bug, I'd be very interested in hearing from you, especially if you can reproduce the problem easily. Note that the submitter of the second-mentioned bug report has done a very good job of describing his problem. Only reports of similar quality would be of much help. If this isn't your (Joeffrey's) problem, it might give you an idea of the kind of information we're looking for. Greg -- Greg Lehey, Senior Software Engineer MySQL AB, http://www.mysql.com/ Echunga, South Australia See complete headers for address and phone numbers. Are you MySQL certified? http://www.mysql.com/certification/ pgpc37noUE7K3.pgp Description: PGP signature
varchar vs char speed improvement
Is there any doc looking at benchmarks of a database which is populated entirely with fixed length char compared to variable character lengths? I know using char is preferred over varchar when it comes to speed. Is there any available benchmarks available? Pointers where would be appreciated. -- Ow Mun Heng Gentoo/Linux on DELL D600 1.4Ghz 1.5GB RAM 98% Microsoft(tm) Free!! Neuromancer 10:38:25 up 2 days, 15:24, 6 users, load average: 0.71, 0.56, 0.35 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Another LOAD Infile Problem
Many thanks for the earlier response to why LOAD DATA INFILE wasnt working for me. However, another problem has appeared. In the file I am reading, 2 of the fields are SUPPOSED to be float values. However, in several places, they are set to UNKNOWN. This seems to cause LOAD to abort. Is there a way for me to tell it to ignore this problem and just use the default value for the column? Jason
Re: Another LOAD Infile Problem
Jason Ferguson wrote: Many thanks for the earlier response to why LOAD DATA INFILE wasnt working for me. However, another problem has appeared. In the file I am reading, 2 of the fields are SUPPOSED to be float values. However, in several places, they are set to UNKNOWN. This seems to cause LOAD to abort. Is there a way for me to tell it to ignore this problem and just use the default value for the column? How about just replace occurrences of the string UNKNOWN in the original file with NULL (the logical equivalent) or 0 (if you're using NOT NULL columns) before doing LOAD DATA INFILE? -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another LOAD Infile Problem
The data is split into about 60 files, average file size of 5 MB (varying from 1 to 10 MB). Since there are many files, I'm trying to minimize the required work (if there was just one consolidated file, no problem). Jason On 9/26/05, Jasper Bryant-Greene [EMAIL PROTECTED] wrote: Jason Ferguson wrote: Many thanks for the earlier response to why LOAD DATA INFILE wasnt working for me. However, another problem has appeared. In the file I am reading, 2 of the fields are SUPPOSED to be float values. However, in several places, they are set to UNKNOWN. This seems to cause LOAD to abort. Is there a way for me to tell it to ignore this problem and just use the default value for the column? How about just replace occurrences of the string UNKNOWN in the original file with NULL (the logical equivalent) or 0 (if you're using NOT NULL columns) before doing LOAD DATA INFILE? -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another LOAD Infile Problem
You'll have to edit your input file. There will always be instances where some field is quirky and you need to fix it/them/entire rows. Don't expect the input file to be perfect. I'd also suggest that you have a test database on a test machine that is devoted entirely to getting your tables set up correctly. It saves a lot of stress by giving you a platform to experiment on. Bob Cochran Jasper Bryant-Greene wrote: Jason Ferguson wrote: Many thanks for the earlier response to why LOAD DATA INFILE wasnt working for me. However, another problem has appeared. In the file I am reading, 2 of the fields are SUPPOSED to be float values. However, in several places, they are set to UNKNOWN. This seems to cause LOAD to abort. Is there a way for me to tell it to ignore this problem and just use the default value for the column? How about just replace occurrences of the string UNKNOWN in the original file with NULL (the logical equivalent) or 0 (if you're using NOT NULL columns) before doing LOAD DATA INFILE? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another LOAD Infile Problem
Then you are in for quite a lot of editing work. I've done it a lot myself. Don't expect your project to be easy. Look for automated ways to edit the data according to your needs and the actual table structure. Bob Cochran Jason Ferguson wrote: The data is split into about 60 files, average file size of 5 MB (varying from 1 to 10 MB). Since there are many files, I'm trying to minimize the required work (if there was just one consolidated file, no problem). Jason On 9/26/05, Jasper Bryant-Greene [EMAIL PROTECTED] wrote: Jason Ferguson wrote: Many thanks for the earlier response to why LOAD DATA INFILE wasnt working for me. However, another problem has appeared. In the file I am reading, 2 of the fields are SUPPOSED to be float values. However, in several places, they are set to UNKNOWN. This seems to cause LOAD to abort. Is there a way for me to tell it to ignore this problem and just use the default value for the column? How about just replace occurrences of the string UNKNOWN in the original file with NULL (the logical equivalent) or 0 (if you're using NOT NULL columns) before doing LOAD DATA INFILE? -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ -- 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]
Adding index to a replication slave
I have replication set up. Is it OK to alter one of the slaves and add several indexes ? It did seem to work, but I'd like to be sure. Thanks, Balazs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
