Re: explicit row locking in InnoDB
hi, to explicitly lock a row (or rows), use : BEGIN TRAN select ... from ... where ... FOR UPDATE; update ... COMMIT TRAN; Mathias Selon Mohammed Sameer [EMAIL PROTECTED]: need some urgent help im using InnoBD database type...i need to explicitly lock a row and keep it locked until i explicitly unlock that row.. it would be great if u could provide me with sql statements as a solution for the above... thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL give up on a query after a certain length of time
Hi, in the conf one use minus -, in the command line it's generally underscore _. the variables should be network_timeout Mathias Selon Jacob Friis Larsen [EMAIL PROTECTED]: On 5/20/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: There is a variable called --network-timeout ! You can set it to 300 (5 minutes, etc.). SET SESSION network-timeout=120; ERROR 1193 (HY000): Unknown system variable 'network' How should I use network-timeout? Thanks, Jacob -- 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 give up on a query after a certain length of time
Hi, forget previous email, Variables are net_read_timeout and net_write_timeout : mysql show variables like '%timeout'; +--+---+ | Variable_name| Value | +--+---+ | connect_timeout | 5 | | delayed_insert_timeout | 300 | | innodb_lock_wait_timeout | 50| | interactive_timeout | 28800 | | net_read_timeout | 30| --- | net_write_timeout| 60| --- | slave_net_timeout| 3600 | | sync_replication_timeout | 0 | | wait_timeout | 28800 | +--+---+ 9 rows in set (0.00 sec) Mathias Selon [EMAIL PROTECTED]: Hi, in the conf one use minus -, in the command line it's generally underscore _. the variables should be network_timeout Mathias Selon Jacob Friis Larsen [EMAIL PROTECTED]: On 5/20/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: There is a variable called --network-timeout ! You can set it to 300 (5 minutes, etc.). SET SESSION network-timeout=120; ERROR 1193 (HY000): Unknown system variable 'network' How should I use network-timeout? Thanks, Jacob -- 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]
ANN: Database Workbench 2.7.1 released
Ladies, gentlemen, Upscene Productions is happy to announce the next version of the popular database development tool: Database Workbench 2.7.1 has been released today! Based on user feedback, there are several bugfixes since the 2.7.0 release. Download a trial at: http://www.upscene.com Features and fixes: http://www.upscene.com/news/20050523.htm Database Workbench supports: - Borland InterBase ( v4.x - v7.x ) - Firebird ( v1.x ) - MS SQL Server/MSDE ( v6.5, 7, 2000, MSDE 1 2 ) - MySQL 4, 4.1 - Oracle Database ( 8i, 9i, 10g ) If you experience any problems with this new version, don't hestitate and either go to the website and send a support email or email directly to [EMAIL PROTECTED] Enhancements Enhancements: - Added comment toolbar button/shortcut to Package Editor Fixes - Possible assertion error removed when closing database connection - Assertion error removed when creating new Domain, Exception, Rule, Package, Default - Duplicating Table/View didn't mark columns as editable - IB/Fb: fixed a rare error when exporting data resulting from select-able stored procedures. This would result into Access Violations or no data exported. - IB/Fb: trigger position clash detection didn't check timing or operations - IB/Fb: access violation fixed for Create SUID Procedures for tables without a primary key and unchecking output generator value - Fb: fixed debugging error with row_count, also appears in the tool tip evaluation now - MSSQL: executing a prepared statement with comments would reprepare it and not execute it - MSSQL: fixed an issue with BIT parameters - ODBC: Possible error with BLOB/widestring datatransfers fixed - ODBC: error messages were cut off at 255 characters - Oracle: removed access violation when switching between header/body in the Package Editor if there was no package - Oracle: adding/dropping columns for an existing tables wasn't reflected in the Database Navigator - Oracle: quick browse and Data tab on Table Editor had read-only resultsets Thank you for your support, Martijn Tonies Database Workbench - the database developer tool for professionals Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL give up on a query after a certain length of time
Variables are net_read_timeout and net_write_timeout : I don't think I can use net_read_timeout to timeout a query. Net Read Timeout: The time required in seconds to wait for more data from a connection before aborting the read. Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Access to MySQL from Linux command line
Hi All. Does anyone know of any tools to convert a MS Access file to MySQL from the Linux command line? Thanks. Andrew
Re: Access to MySQL from Linux command line
Hi Adam. I need to get all the data. Andrew Adam wrote: Drew, That's vague. Specifically what do you want from the Access database (e.g. schema, data, etc.)? A-
Re: Access to MySQL from Linux command line
Export as CSV. MySQL command line client. Run a script with LOAD DATA INFILE. Chris On 5/23/05, Andrew Dixon - MSO.net [EMAIL PROTECTED] wrote: Hi Adam. I need to get all the data. Andrew Adam wrote: Drew, That's vague. Specifically what do you want from the Access database (e.g. schema, data, etc.)? A- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
varchar to text
Hello, I created a table that uses a varchar(254) size field, and found out that the customers data is being truncated. It needs to be larger. Currently there are about 500 records (it is a description field) in the column. Upon reading the docs, it looks like I need to use a text type column. I have never done an alter with records in a table, specifically when it is client-data. How, or where, can I get information on how to alter the table to be a text field, or do I need to copy all the data to a tmp table, create a new table and copy all the data back to the new table with a text column? I have no experience with this area of sql. Thanks, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: varchar to text
Changing types to larger shouldn't be an issue. If you are changing to smaller, of course you are going to truncate the data more. You do not need to move the data out and then back in again unless you really want to. mysql ALTER TABLE tbl_name CHANGE `col1_old_name` `col1_new_name` text default null J.R. -Original Message- From: Scott Purcell [mailto:[EMAIL PROTECTED] Sent: Monday, May 23, 2005 9:37 AM To: mysql@lists.mysql.com Subject: varchar to text Hello, I created a table that uses a varchar(254) size field, and found out that the customers data is being truncated. It needs to be larger. Currently there are about 500 records (it is a description field) in the column. Upon reading the docs, it looks like I need to use a text type column. I have never done an alter with records in a table, specifically when it is client-data. How, or where, can I get information on how to alter the table to be a text field, or do I need to copy all the data to a tmp table, create a new table and copy all the data back to the new table with a text column? I have no experience with this area of sql. Thanks, Scott -- 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: varchar to text
You can safely change varchar to text, since they are the same data type. ALTER TABLE table_name CHANGE field_name field_name TEXT But, as always, make sure you have a recent backup. There is always the possibility that your computer will crash in the middle of the operation due to a stray galactic neutrino that happens to collide with a bit of your memory and cause a bit to flip. On May 23, 2005, at 9:37 AM, Scott Purcell wrote: Hello, I created a table that uses a varchar(254) size field, and found out that the customers data is being truncated. It needs to be larger. Currently there are about 500 records (it is a description field) in the column. Upon reading the docs, it looks like I need to use a text type column. I have never done an alter with records in a table, specifically when it is client-data. How, or where, can I get information on how to alter the table to be a text field, or do I need to copy all the data to a tmp table, create a new table and copy all the data back to the new table with a text column? I have no experience with this area of sql. Thanks, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: varchar to text
Hi, I we can, one should ask if it is a good solution ? text can't be indexed as a varchar. You will need full-text indexing, or a b-tree index on a length-limited : mysql create index titi on toto2(t); ERROR 1170 (42000): BLOB/TEXT column 't' used in key specification without a key length mysql create index titi on toto2(t(100)); Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0 Then queries have to changed for using the indexes and so on ... So study carefully before altering the table. Mathias Selon Brent Baisley [EMAIL PROTECTED]: You can safely change varchar to text, since they are the same data type. ALTER TABLE table_name CHANGE field_name field_name TEXT But, as always, make sure you have a recent backup. There is always the possibility that your computer will crash in the middle of the operation due to a stray galactic neutrino that happens to collide with a bit of your memory and cause a bit to flip. On May 23, 2005, at 9:37 AM, Scott Purcell wrote: Hello, I created a table that uses a varchar(254) size field, and found out that the customers data is being truncated. It needs to be larger. Currently there are about 500 records (it is a description field) in the column. Upon reading the docs, it looks like I need to use a text type column. I have never done an alter with records in a table, specifically when it is client-data. How, or where, can I get information on how to alter the table to be a text field, or do I need to copy all the data to a tmp table, create a new table and copy all the data back to the new table with a text column? I have no experience with this area of sql. Thanks, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- 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]
2 Joins in 1 Query
Hi, The following query produces the number of bookings per project grouped by week: SELECT WEEK(Booking_Start_Date) AS WEEK, SUM(IF(B.Project_ID = 23, 1,0)) AS `Project A`, SUM(IF(B.Project_ID = 42, 1,0)) AS `Project B` FROM Bookings B INNER JOIN Projects P USING (Project_ID) WHERE B.Booking_Type = Booking AND DATE_FORMAT(B.Booking_Start_Date, %Y-%m-%d) = '2005-01-01' AND DATE_FORMAT(B.Booking_Start_Date, %Y-%m-%d) = '2005-12-31' GROUP BY WEEK; The problem with this is that if I have no bookings for week 42 for example then that week is not shown in the result. To get round this I created a table called Weeks that contains all the week numbers for the year. However I am not sure how I can join Weeks to Bookings so that all the weeks show. Any healp would be greatly appreciated. TABLE DEF'S: mysql desc Bookings; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | Booking_ID | int(11) | | PRI | NULL| auto_increment | | Booking_Type| varchar(15) | | | Unavailability || | User_ID | int(11) | | | 0 || | Project_ID | int(11) | YES | | NULL|| | Rep_ID | int(11) | YES | | NULL|| | Practice_ID | int(11) | YES | | NULL|| | Booking_Creator_ID | int(11) | YES | | NULL|| | Booking_Creation_Date | datetime| YES | | NULL|| | Booking_Start_Date | datetime| | | -00-00 00:00:00 || | Booking_End_Date| datetime| | | -00-00 00:00:00 || | Booking_Completion_Date | date| YES | | NULL|| | Booking_Mileage | int(5) | YES | | NULL|| | Booking_Status | varchar(15) | | | Other || | Unavailability_ID | int(2) | YES | | NULL|| | Task_ID | int(11) | YES | | NULL|| | Work_Type_ID| int(2) | YES | | NULL|| | Additional_Notes| text| YES | | NULL|| +-+-+--+-+-++ 22 rows in set (0.00 sec) mysql desc Projects; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | Project_ID | int(11) | | PRI | NULL| auto_increment | | Project_Name | varchar(100) | | | | | | Client_ID | int(11) | | | 0 | | ++--+--+-+-++ 8 rows in set (0.00 sec) mysql desc Weeks; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | Week_ID | int(11) | | PRI | NULL| auto_increment | | Week_Number | int(11) | | | 0 || +-+-+--+-+-++ 2 rows in set (0.00 sec) mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Changing DB name
Is there a way to change the name of a database? What has happened is that our test db has now become the production db and so I want to rename the database. Chris Hood -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Wide eyes
Hi The company I work for is putting together a quote for a site, the client has wide eyes and is proposing 5,000,000 users and other large figures for elements which we intend to use MySQL for. So the questions: Is a table with say 5,000,000+ records possible? What are the things to look out for with this amount of data? Could the database be split over several database servers? Is there anywhere on the mySQL site about huge databases? Thanks in advance for any help. -- David Scott
RE: Wide eyes
[snip] Is a table with say 5,000,000+ records possible? What are the things to look out for with this amount of data? Could the database be split over several database servers? Is there anywhere on the mySQL site about huge databases? [/snip] Yes. We have several tables with well over 100 million records weighing as much as 115 Gb. Proper indexing. Sure, using clustering. Hmmm, not sure. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Wide eyes
Thankyou very much, at this stage we just wanted to know it *can* be done. Thanks again -- David Scott - Original Message - From: Jay Blanchard [EMAIL PROTECTED] To: Critters [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Monday, May 23, 2005 3:39 PM Subject: RE: Wide eyes [snip] Is a table with say 5,000,000+ records possible? What are the things to look out for with this amount of data? Could the database be split over several database servers? Is there anywhere on the mySQL site about huge databases? [/snip] Yes. We have several tables with well over 100 million records weighing as much as 115 Gb. Proper indexing. Sure, using clustering. Hmmm, not sure. -- 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: 2 Joins in 1 Query
shaun thornburgh mailto:[EMAIL PROTECTED] wrote: SELECT WEEK(Booking_Start_Date) AS WEEK, SUM(IF(B.Project_ID = 23, 1,0)) AS `Project A`, SUM(IF(B.Project_ID = 42, 1,0)) AS `Project B` FROM Bookings B INNER JOIN Projects P USING (Project_ID) WHERE B.Booking_Type = Booking AND DATE_FORMAT(B.Booking_Start_Date, %Y-%m-%d) = '2005-01-01' AND DATE_FORMAT(B.Booking_Start_Date, %Y-%m-%d) = '2005-12-31' GROUP BY WEEK; The problem with this is that if I have no bookings for week 42 for example then that week is not shown in the result. To get round this I created a table called Weeks that contains all the week numbers for the year. Try a LEFT JOIN: SELECT WEEK(Booking_Start_Date) AS WEEK, SUM(IF(B.Project_ID = 23, 1,0)) AS `Project A`, SUM(IF(B.Project_ID = 42, 1,0)) AS `Project B` FROM Bookings B LEFT JOIN Projects P ON (P.Project_ID = B.Project_ID) WHERE B.Booking_Type = Booking AND DATE_FORMAT(B.Booking_Start_Date, %Y-%m-%d) = '2005-01-01' AND DATE_FORMAT(B.Booking_Start_Date, %Y-%m-%d) = '2005-12-31' GROUP BY WEEK; Regards Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 2 Joins in 1 Query
Hi, Unfortunately that doesnt work, I need to LEFT JOIN Bookings to Weeks but keep the current join on Projects there as well, any ideas? From: Peter Normann [EMAIL PROTECTED] To: 'shaun thornburgh' [EMAIL PROTECTED],mysql@lists.mysql.com Subject: RE: 2 Joins in 1 Query Date: Mon, 23 May 2005 17:13:42 +0200 shaun thornburgh mailto:[EMAIL PROTECTED] wrote: SELECT WEEK(Booking_Start_Date) AS WEEK, SUM(IF(B.Project_ID = 23, 1,0)) AS `Project A`, SUM(IF(B.Project_ID = 42, 1,0)) AS `Project B` FROM Bookings B INNER JOIN Projects P USING (Project_ID) WHERE B.Booking_Type = Booking AND DATE_FORMAT(B.Booking_Start_Date, %Y-%m-%d) = '2005-01-01' AND DATE_FORMAT(B.Booking_Start_Date, %Y-%m-%d) = '2005-12-31' GROUP BY WEEK; The problem with this is that if I have no bookings for week 42 for example then that week is not shown in the result. To get round this I created a table called Weeks that contains all the week numbers for the year. Try a LEFT JOIN: SELECT WEEK(Booking_Start_Date) AS WEEK, SUM(IF(B.Project_ID = 23, 1,0)) AS `Project A`, SUM(IF(B.Project_ID = 42, 1,0)) AS `Project B` FROM Bookings B LEFT JOIN Projects P ON (P.Project_ID = B.Project_ID) WHERE B.Booking_Type = Booking AND DATE_FORMAT(B.Booking_Start_Date, %Y-%m-%d) = '2005-01-01' AND DATE_FORMAT(B.Booking_Start_Date, %Y-%m-%d) = '2005-12-31' GROUP BY WEEK; Regards Peter Normann -- 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]
complicated query | no Sub query
hi All, I need to create a query using no subqueries as I use 4.0.23 which does not support subqueries. I cannot upgrade for some compellimg reasons (the product does not support anything later than 4.0.23nt as of now). I also cannot use any thing that is native to mysql - in the sense that the query should be as generic/simple as possible so that I can run it against both DB2 or Sybase. Here is the problem.. I have this table: (modified to simplify) ++ | id_secr| name_rec_type | dt_aud_rec | ++ | TASKD1 | Risk Assessment | 2005-05-20 19:07:54 | | TASKD1 | Assigned | 2005-05-20 19:07:53 | | TASKD1 | Pending | 2005-05-20 12:10:50 | | TASKD2 | Closed | 2005-05-20 19:06:27 | | TASKD2 | Risk Assessment | 2005-05-20 19:06:04 | | TASKD2 | Pending | 2005-05-20 19:05:54 | | TASKD3 | Closed | 2005-05-20 16:40:14 | | TASKD3 | Risk Assessment | 2005-05-20 10:07:54 | | TASKD3 | Assigned | 2005-05-20 10:00:54 | | TASKD4 | Closed | 2005-05-20 10:34:13 | | TASKD4 | Risk Assessment | 2005-05-20 09:07:54 | | TASKD4 | Assigned | 2005-05-20 09:00:54 | | TASKD4 | Assigned | 2005-05-20 09:00:04 | | TASKD5 | Closed | 2005-05-20 15:33:13 | | TASKD5 | SERB Assessment | 2005-05-20 15:07:54 | | TASKD5 | Assigned | 2005-05-20 14:07:54 | | TASKD5 | Risk Assessment | 2005-05-20 13:07:54 | | TASKD5 | Risk Assessment | 2005-05-20 12:07:54 | | TASKD6 | Closed | 2005-05-20 14:18:28 | | TASKD6 | Risk Assessment | 2005-05-20 13:07:54 | | TASKD6 | Assigned | 2005-05-20 12:07:54 | | TASKD6 | Pending | 2005-05-20 11:07:54 | | TASKD6 | Pending | 2005-05-20 10:07:54 | | TASKD6 | Pending | 2005-05-20 09:07:54 | | TASKD6 | Pending | 2005-05-20 08:07:54 | | TASKD6 | Pending | 2005-05-20 07:07:54 | ++ what I need is to pull out data based on the latest two dates. I will be checking the status (name_rec_type) and if my status matches any one of the latest 2 name_rec_type I will do some processing. The only thing compounding this is that I cannot use any sub queries - I can do as many joins as necessary. Also the query need not be performance intensive as I dont think we will have more than 2000 rows at any time. Moreover I will have to ignore all rows (or wholes TASKD*'s) where the name_rec_type is closed anywhere. here is what I have come up so far - but it gives only the latest data: (and not the last 2 latest) select t1.id_secr_rqst, t2.name_rec_type, max(t1.dt_aud_rec) from isr2_aud_log t1, isr2_aud_log t2 where t1.id_secr_rqst = t2.id_secr_rqst and t1.name_rec_type='Exception Resource' group by t1.id_secr_rqst I need help. Thanks in advance. Anoop
RE: 2 Joins in 1 Query
shaun thornburgh mailto:[EMAIL PROTECTED] wrote: Unfortunately that doesnt work, I need to LEFT JOIN Bookings to Weeks but keep the current join on Projects there as well, any ideas? Sorry, Shaun. I misunderstood what you were trying to accomplish first. How about something along these lines (have no way of testing): SELECT W.Number AS WEEK, WEEK(Booking_Start_Date) AS MYWEEK, SUM(IF(B.Project_ID = 23, 1,0)) AS `Project A`, SUM(IF(B.Project_ID = 42, 1,0)) AS `Project B` FROM Week W, Bookings B INNER JOIN Projects P USING (Project_ID) WHERE B.Booking_Type = Booking AND DATE_FORMAT(B.Booking_Start_Date, %Y-%m-%d) = '2005-01-01' AND DATE_FORMAT(B.Booking_Start_Date, %Y-%m-%d) = '2005-12-31' HAVING WEEK = MYWEEK GROUP BY WEEK; Supposing you have 53 records in week with numbers ranging from 1 through 53. Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: complicated query | no Sub query
Hi Anoop Try: SELECT t1.id_secr_rqst, t2.name_rec_type, t1.dt_aud_rec FROM isr2_aud_log t1, isr2_aud_log t2 WHERE t1.id_secr_rqst = t2.id_secr_rqst AND t1.name_rec_type='Exception Resource' ORDER by t1.dt_aud_rec DESC LIMIT 2; Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Wide eyes
Critters wrote: Is a table with say 5,000,000+ records possible? Certainly. I've got some sideline app that's currently working it's way through about 15mill rows. I think it takes up about 3Gb diskspace for the moment. What are the things to look out for with this amount of data? Not much - good indexes and prooper querying. Could the database be split over several database servers? Possibly, but I don't see a need. -- /Per Jessen, Zürich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem connecting to 4.1 server
Hello. You may compile MySQL from source by yourself with enabled debugging, if an official binaries don't include it. See: http://dev.mysql.com/doc/mysql/en/windows-source-build.html I suggest you to forward your message to win32 list: http://lists.mysql.com/win32 Nicholas Watmough [EMAIL PROTECTED] wrote: Hi, I read the suggested section, but I'm not sure whether I can actually do any debugging with the pre-compiled Windows MySQL server. Is it possible to turn on debugging with the pre-compiled binaries on Windows? I'm also not sure what the difference is between libmysql.lib and mysqlclient.lib. I've been linking with the former, as the latter causes linking errors, but the manual reads that one is supposed to link with the latter to use the C API. Thanks -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: Query Issue on Large DB
Hello. records) takes a long time and drains memory to the point I have to reboot the system. Here is the status after a single large query is What version of MySQL do you use? I'm not familiar with msyslog, so what queries takes such long time? In what state does the weird queries spend their time? Please send complete output of SHOW STATUS and SHOW VARIABLES. See: http://dev.mysql.com/doc/mysql/en/show-processlist.html http://dev.mysql.com/doc/mysql/en/slow-query-log.html Jennifer Fountain [EMAIL PROTECTED] wrote: Hi, I am new to the list and mysql for that matter. I am trying to configure a centralized syslog server using mysql, msyslog and php. Works great until the DB grows to about 2GB. After the database gets over 2GB, running queries on a particular hostname (that has a lot of records) takes a long time and drains memory to the point I have to reboot the system. Here is the status after a single large query is ran: total used free sharedbuffers=20 cached=20 Mem: 38896163871504 18112 0 21336=20 3654652=20 -/+ buffers/cache: 1955163694100=20 Swap: 4194232 04194232=20 Here is a copy of my.cnf: [mysqld] datadir=3D/data socket=3D/var/lib/mysql/mysql.sock skip-locking set-variable=3D key_buffer_size=3D384M set-variable=3D max_allowed_packet=3D1M set-variable=3D table_cache=3D512 set-variable=3D sort_buffer=3D2M set-variable=3D record_buffer=3D2M set-variable=3D thread_cache=3D8 set-variable=3D read_rnd_buffer_size=3D2M # Try number of CPU's*2 for thread_concurrency set-variable=3D thread_concurrency=3D8 set-variable=3D myisam_sort_buffer_size=3D64M log-bin server-id =3D 1=20 [mysql.server] user=3Dmysql basedir=3D/var/lib [safe_mysqld] err-log=3D/var/log/mysqld.log pid-file=3D/var/run/mysqld/mysqld.pid [mysqldump] quick set-variable=3D max_allowed_packet=3D16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable=3D key_buffer=3D256M set-variable=3D sort_buffer=3D256M set-variable=3D read_buffer=3D2M set-variable=3D write_buffer=3D2M [myisamchk] set-variable=3D key_buffer=3D256M set-variable=3D sort_buffer=3D256M set-variable=3D read_buffer=3D2M set-variable=3D write_buffer=3D2M [mysqlhotcopy] interactive-timeout Any thoughts as to what I am missing? Thanks for any information! Kind Regards, Jennifer Fountain Systems Administrator/Security RB Distribution 3400 E Walnut Street Colmar, PA 18915=20 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: MySQL give up on a query after a certain length of time
Hello. Normally such different behavior for same queries is weird. Do you use an official binaries or from Debian? Jacob Friis Larsen [EMAIL PROTECTED] wrote: Check with SHOW PROCESSLIST in what state your query hangs. It hangs while sending data. I only ask for 7 rows, and normally this goes very fast. Have a nice weekend. Thanks, Jacob -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: Changing DB name
Hello. Similar question was asked before. Search in archives. For example see: http://lists.mysql.com/mysql/173781 [EMAIL PROTECTED] wrote: Is there a way to change the name of a database? What has happened is = that our test db has now become the production db and so I want to rename the database. Chris Hood=A0 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Here is your documents.
The message cannot be represented in 7-bit ASCII encoding and has been sent as a binary attachment. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2 Joins in 1 Query
In article [EMAIL PROTECTED], shaun thornburgh [EMAIL PROTECTED] writes: Hi, Unfortunately that doesnt work, I need to LEFT JOIN Bookings to Weeks but keep the current join on Projects there as well, any ideas? So what you would need is something like that: Weeks LEFT JOIN (Bookings INNER JOIN Projects) This could be converted to: Bookings INNER JOIN Projects RIGHT JOIN Weeks Unfortunately MySQL's RIGHT JOIN implementation has been broken for ages and won't get fixed until 5.x. Thus your only option is putting the results of the INNER JOIN into a temporary table and then use Weeks LEFT JOIN temp. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Here is your documents.
The message cannot be represented in 7-bit ASCII encoding and has been sent as a binary attachment. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2 Joins in 1 Query
shaun thornburgh [EMAIL PROTECTED] wrote on 05/23/2005 10:18:29 AM: Hi, The following query produces the number of bookings per project grouped by week: SELECT WEEK(Booking_Start_Date) AS WEEK, SUM(IF(B.Project_ID = 23, 1,0)) AS `Project A`, SUM(IF(B.Project_ID = 42, 1,0)) AS `Project B` FROM Bookings B INNER JOIN Projects P USING (Project_ID) WHERE B.Booking_Type = Booking AND DATE_FORMAT(B.Booking_Start_Date, %Y-%m-%d) = '2005-01-01' AND DATE_FORMAT(B.Booking_Start_Date, %Y-%m-%d) = '2005-12-31' GROUP BY WEEK; The problem with this is that if I have no bookings for week 42 for example then that week is not shown in the result. To get round this I created a table called Weeks that contains all the week numbers for the year. However I am not sure how I can join Weeks to Bookings so that all the weeks show. Any healp would be greatly appreciated. TABLE DEF'S: mysql desc Bookings; +-+-+-- +-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+-- +-+-++ | Booking_ID | int(11) | | PRI | NULL| auto_increment | | Booking_Type| varchar(15) | | | Unavailability || | User_ID | int(11) | | | 0 || | Project_ID | int(11) | YES | | NULL|| | Rep_ID | int(11) | YES | | NULL|| | Practice_ID | int(11) | YES | | NULL|| | Booking_Creator_ID | int(11) | YES | | NULL|| | Booking_Creation_Date | datetime| YES | | NULL|| | Booking_Start_Date | datetime| | | -00-00 00:00:00 || | Booking_End_Date| datetime| | | -00-00 00:00:00 || | Booking_Completion_Date | date| YES | | NULL|| | Booking_Mileage | int(5) | YES | | NULL|| | Booking_Status | varchar(15) | | | Other || | Unavailability_ID | int(2) | YES | | NULL|| | Task_ID | int(11) | YES | | NULL|| | Work_Type_ID| int(2) | YES | | NULL|| | Additional_Notes| text| YES | | NULL|| +-+-+-- +-+-++ 22 rows in set (0.00 sec) mysql desc Projects; ++--+--+-+- ++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+- ++ | Project_ID | int(11) | | PRI | NULL| auto_increment | | Project_Name | varchar(100) | | | | | | Client_ID | int(11) | | | 0 | | ++--+--+-+- ++ 8 rows in set (0.00 sec) mysql desc Weeks; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | Week_ID | int(11) | | PRI | NULL| auto_increment | | Week_Number | int(11) | | | 0 || +-+-+--+-+-++ 2 rows in set (0.00 sec) mysql I think you need just think about what you want and what may or may not exist as data, then you can figure out which JOINs are LEFT and which are INNER. You want one row for each week regardless of whether you have a Project or a Booking. That makes the Weeks table manditory. There may be weeks that do not have any Bookings. That makes Bookings the right side of a LEFT JOIN. Because the existence of a Projects is dependent on the existence of a Booking, it too is LEFT JOINED into the query. If you want
Re: complicated query | no Sub query
Thanks Peter - but I see two issues: 1. It returns data about only one id_secr_rqst - I want it to return data about every id_secr_rqst in the table. 2. Limit IMO is mysql specific (I hope I am wrong) is there something generic so I dont need to bother about which database I am running it against. Thanks, Anoop On 5/23/05, Peter Normann [EMAIL PROTECTED] wrote: Hi Anoop Try: SELECT t1.id_secr_rqst, t2.name_rec_type, t1.dt_aud_rec FROM isr2_aud_log t1, isr2_aud_log t2 WHERE t1.id_secr_rqst = t2.id_secr_rqst AND t1.name_rec_type='Exception Resource' ORDER by t1.dt_aud_rec DESC LIMIT 2; Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Thanks and best regards, Anoop
Re: 2 Joins in 1 Query
Cut and paste bites me again. The table reference portion (cut out accidentally) should have been: FROM Weeks w LEFT Bookings b on WEEK(b.Booking_Start_Date) = w.Week_Number LEFT JOIN Projects p ON p.Project_Id = b.Project_ID sorry all! Shawn [EMAIL PROTECTED] wrote on 05/23/2005 12:20:05 PM: shaun thornburgh [EMAIL PROTECTED] wrote on 05/23/2005 10:18:29 AM: Hi, The following query produces the number of bookings per project grouped by week: snip ... That means the table reference portion of your query should look like And that means your whole query translates to: snip Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: complicated query | no Sub query
Anoop kumar V mailto:[EMAIL PROTECTED] wrote: 1. It returns data about only one id_secr_rqst - I want it to return data about every id_secr_rqst in the table. So, if I understand you correctly (sorry, having a bad day), you want all records for the past two days? Assuming this, you could use something like: SELECT t1.id_secr_rqst, t2.name_rec_type, t1.dt_aud_rec FROM isr2_aud_log t1, isr2_aud_log t2 WHERE t1.id_secr_rqst = t2.id_secr_rqst AND t1.name_rec_type='Exception Resource' AND dt_aud_rec CURDATE() - 2; ORDER by t1.dt_aud_rec DESC; 2. Limit IMO is mysql specific (I hope I am wrong) is there something generic so I dont need to bother about which database I am running it against. As far as I know it is MySql specific. SQL Server's equivalent is SELECT TOP 2 or something. Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
vcc options not in documentation?
Having trouble building 5.0.x, 4.1.12 builds fine The very first program to link bombs, I'm thinking some dll is not registered, or a change in build directions between 4.1.x and 5.0.x the error is less than helpful, as I'm a dba not a programmer. Any sugestions? I can build 5.0.3, but not 5.0.6 or 5.0.7, I'm hoping 5.0.7 fixes more of the procedure bugs holding up our development switching from sql server to mysql attempting to build mysql-5.0.7-beta-nightly-20050521-win-src.zip I'm not sure if this is the right forum, or if I should submit a bug on this compile error. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
vcc options not in documentation?
Ooops, it would help if I put in the error message Configuration: mysql - Win32 Debug Linking... mysqlclient.lib(mf_pack.obj) : error LNK2001: unresolved external symbol _my_access ../client_debug/mysql.exe : fatal error LNK1120: 1 unresolved externals Error executing link.exe. mysqld-debug.exe - 2 error(s), 0 warning(s) Having trouble building 5.0.x, 4.1.12 builds fine The very first program to link bombs, I'm thinking some dll is not registered, or a change in build directions between 4.1.x and 5.0.x the error is less than helpful, as I'm a dba not a programmer. Any sugestions? I can build 5.0.3, but not 5.0.6 or 5.0.7, I'm hoping 5.0.7 fixes more of the procedure bugs holding up our development switching from sql server to mysql attempting to build mysql-5.0.7-beta-nightly-20050521-win-src.zip I'm not sure if this is the right forum, or if I should submit a bug on this compile error. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: complicated query | no Sub query
Anoop kumar V mailto:[EMAIL PROTECTED] wrote: well - actually it might not be the last 2 days - i just want 2 of the latest records for every task regardless of what date it is in the table. Okay, now I think I understand what you need - and if I am correct, this looks like one of the more exotic querys to me, but then again, I'm not like the SQL king around, but let me give it a shot: SELECT t1.id_secr_rqst, t2.name_rec_type, t1.dt_aud_rec, MAX(dt_aud_rec) Latest, MAX(dt_aud_rec) NoSoLatest FROM isr2_aud_log t1, isr2_aud_log t2 WHERE t1.id_secr_rqst = t2.id_secr_rqst AND t1.name_rec_type='Exception Resource' AND dt_aud_rec = Latest OR dt_aud_rec = NoSoLatest HAVING Latest NoSoLatest GROUP BY t1.id_secr_rqst ORDER by t1.dt_aud_rec DESC; I am not 100% sure about the syntax, but you might get the idea. Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: varchar to text
After some thought into this, I believe that I may actually need to eventually search this field, and may need an index on it. After talking to others at the office, they think that maybe I should try and create three varchar(254) fields and tie them together. Therefore I can have a larger description field, and still be able to index. Is this a bad or crazy idea? I have never heard of doing this, but I can via code, show the results from three tables and concat it together? Any ideas, thoughts I figure I need about avg: 1000 characters per description. Thanks, Scott -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, May 23, 2005 8:58 AM To: Brent Baisley Cc: Scott Purcell; mysql@lists.mysql.com Subject: Re: varchar to text Hi, I we can, one should ask if it is a good solution ? text can't be indexed as a varchar. You will need full-text indexing, or a b-tree index on a length-limited : mysql create index titi on toto2(t); ERROR 1170 (42000): BLOB/TEXT column 't' used in key specification without a key length mysql create index titi on toto2(t(100)); Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0 Then queries have to changed for using the indexes and so on ... So study carefully before altering the table. Mathias Selon Brent Baisley [EMAIL PROTECTED]: You can safely change varchar to text, since they are the same data type. ALTER TABLE table_name CHANGE field_name field_name TEXT But, as always, make sure you have a recent backup. There is always the possibility that your computer will crash in the middle of the operation due to a stray galactic neutrino that happens to collide with a bit of your memory and cause a bit to flip. On May 23, 2005, at 9:37 AM, Scott Purcell wrote: Hello, I created a table that uses a varchar(254) size field, and found out that the customers data is being truncated. It needs to be larger. Currently there are about 500 records (it is a description field) in the column. Upon reading the docs, it looks like I need to use a text type column. I have never done an alter with records in a table, specifically when it is client-data. How, or where, can I get information on how to alter the table to be a text field, or do I need to copy all the data to a tmp table, create a new table and copy all the data back to the new table with a text column? I have no experience with this area of sql. Thanks, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- 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: varchar to text
I think that is absolutely a bad idea. Not only are you splitting data that should logically be together, you will need to add three indexes and perform three searched to get at the data. You typical index only indexes from the start of the field, so even if you split the data, you can't do contained in searches that will use an index. Keep in all in one text field and use a full text index. If you need to index the first 20 characters or something for quick sorting, than also add a regular index on the field. But a full text index on one field is what you want to use. On May 23, 2005, at 2:02 PM, Scott Purcell wrote: After some thought into this, I believe that I may actually need to eventually search this field, and may need an index on it. After talking to others at the office, they think that maybe I should try and create three varchar(254) fields and tie them together. Therefore I can have a larger description field, and still be able to index. Is this a bad or crazy idea? I have never heard of doing this, but I can via code, show the results from three tables and concat it together? Any ideas, thoughts I figure I need about avg: 1000 characters per description. Thanks, Scott -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to implement views in mysql 4.12 version
Hello, I want a solution to implement the views in mysql 4.12 version. It is urgent. Earlier our database was oracle and our search of database is done using views. I want to implement the same in mysql 4.12 version. Does any one know how to do it. Please give me solutions at the earliest as mirgration from oracle to mysql is started and i need to meet the solution as soon as possible. Thanks in advance bye dayakar
Re: How to implement views in mysql 4.12 version
On Monday 23 May 2005 02:01 pm, Dayakar wrote: I want a solution to implement the views in mysql 4.12 version. It is urgent. Earlier our database was oracle and our search of database is done using views. I want to implement the same in mysql 4.12 version. You need version 5.0.4.. http://dev.mysql.com/doc/mysql/en/CREATE_VIEW.html Jeff pgpLkLDLqUMyZ.pgp Description: PGP signature
Re: How to implement views in mysql 4.12 version
At 0:31 +0530 5/24/05, Dayakar wrote: Hello, I want a solution to implement the views in mysql 4.12 version. It is urgent. Earlier our database was oracle and our search of database is done using views. I want to implement the same in mysql 4.12 version. Does any one know how to do it. Please give me solutions at the earliest as mirgration from oracle to mysql is started and i need to meet the solution as soon as possible. Views are implemented in MySQL 5.0, not MySQL 4.1. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to implement views in mysql 4.12 version
On Monday 23 May 2005 20:01, Dayakar wrote: Please give me solutions at the earliest as mirgration from oracle to mysql is started and i need to meet the solution as soon as possible. 4.1 does not support views. As documented in the online documentation: Views (including updatable views) are implemented in the 5.0 version of MySQL Server. Views are available in binary releases from 5.0.1 and up. See Chapter 21, Views. Migrating, and then reading the documentation and trying to learn how things work is usually a bad idea. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL give up on a query after a certain length of time
Normally such different behavior for same queries is weird. Do you use an official binaries or from Debian? I use those from Debian: http://packages.debian.org/testing/misc/mysql-server-4.1 Thanks, Jacob Jacob Friis Larsen [EMAIL PROTECTED] wrote: Check with SHOW PROCESSLIST in what state your query hangs. It hangs while sending data. I only ask for 7 rows, and normally this goes very fast. Have a nice weekend. Thanks, Jacob -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to implement views in mysql 4.12 version
Hi, Waiting for v5 for views, you can prepare them in inline view (subqueries): select ... from (select ...) Mathias Selon Duncan Hill [EMAIL PROTECTED]: On Monday 23 May 2005 20:01, Dayakar wrote: Please give me solutions at the earliest as mirgration from oracle to mysql is started and i need to meet the solution as soon as possible. 4.1 does not support views. As documented in the online documentation: Views (including updatable views) are implemented in the 5.0 version of MySQL Server. Views are available in binary releases from 5.0.1 and up. See Chapter 21, Views. Migrating, and then reading the documentation and trying to learn how things work is usually a bad idea. -- 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]
MYISAM MYI
I have a MYISAM table that has both the MYD and MYI files growing at similar rates. We have this same database installed in other locations and the MYI file stays static but the MYD grows. From the docs I can see that the MYI file contains the index information. There are three foreign keys in the table, but I can't see why this MYI file should ever grow in size. I have already run myisamchk.exe -r on the table and it said it fixed the index, but then started growing again. Has anyone experienced anything like this? Anyone have any thoughts? I'm using MYSQL Version 4.1.8; Thx, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MYISAM MYI
Are the the FK on your largest columns? So if you have a char(255) and it's index - for each row added you will have added an additional 255 bytes added to your index as well as your data file. If you want to optimize a table use OPTIMIZE TABLE or myisamchk -r -S -a // when the server is off DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Paul Beer [mailto:[EMAIL PROTECTED] Sent: Monday, May 23, 2005 1:57 PM To: mysql@lists.mysql.com Subject: MYISAM MYI I have a MYISAM table that has both the MYD and MYI files growing at similar rates. We have this same database installed in other locations and the MYI file stays static but the MYD grows. From the docs I can see that the MYI file contains the index information. There are three foreign keys in the table, but I can't see why this MYI file should ever grow in size. I have already run myisamchk.exe -r on the table and it said it fixed the index, but then started growing again. Has anyone experienced anything like this? Anyone have any thoughts? I'm using MYSQL Version 4.1.8; Thx, Paul -- 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]
db/mysql rookie request
MySQL guys: I am new to databases, MySQL, and anything outside of my world of UNIX system administration. A former colleague of mine set up MySQL on our backup server and I would like to interface with the database to get reports. I have read a little and understand a few things. I have taken the time to develop some SQL to get the data I would like to get. However, I want to do this with a Korn shell script that emails the report to a list of internal customers. Any help you can provide would be greatly appreciated. Thanks in advance, Bill Bill Jorgensen CSG Systems, Inc.
Problems with x86_64 mysql-standard-4.1.12
Hi, MySQL is not getting very far through make test on 64-bit Debian, MySQL 4.1.12. I've tried precompiled and self-compiled, and on two different machines, both of which have been in use for a long time and both of which run MySQL 4.0 (and its tests) without a problem. On one machine: ~/mysql-standard-4.1.12-unknown-linux-gnu-x86_64-glibc23/mysql-test: ./mysql-test-run Installing Test Databases Removing Stale Files Installing Master Databases running ../bin/mysqld --no-defaults --bootstrap --skip-grant-tables --basedir=.. --datadir=mysql-test/var/master-data --skip-innodb --skip-ndbcluster --skip-bdb Installing Slave Databases running ../bin/mysqld --no-defaults --bootstrap --skip-grant-tables --basedir=.. --datadir=mysql-test/var/slave-data --skip-innodb --skip-ndbcluster --skip-bdb Manager disabled, skipping manager start. Loading Standard Test Databases Starting Tests TESTRESULT --- alias [ pass ] alter_table[ pass ] analyse[ pass ] ansi [ pass ] archive[ pass ] and then it never comes back, presumably from the auto_increment test. If I run the auto_increment test alone (i.e., ./mysql-test-run auto_increment), it fails in this same way. When it's hung, mysqld isn't using any CPU. If I manually run the commands that constitute the auto_increment test on a running 4.1.12 server they complete, and the output appears normal to me. On another machine, make test gets as far as the delete test before hanging. The first machine doesn't successfully complete the delete test either, if run directly (i.e., ./mysql-test-run delete). The machines are running Debian amd64 (the standard archive), and are: Machine 1: Debian Sid, Athlon 3500+, 1GB ram. Kernel 2.6.12-rc4. Machine 2: Debian Sarge, Dual Opteron 248, 6GB ram. Production 4.0.x server, in use for six months. Kernel 2.6.11-ac7. In addition to failing the tests, I deployed the server on Machine 1 for a while and it failed quickly, with a simple insert hanging up and kill threadID being unable to kill it. (The thread's state was Killed, but it didn't go away and continued to block other threads from accessing the (MyISAM) table.) Any help would be appreciated, and please let me know if I can provide further information. Thanks, -- Pete Harlan [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to get the name of the last failed FK constraint
Hello, I have an InnoDB table running on MySQL 4.1.11 with multiple FK constraints. I'm accessing it via JDBC from Java. When an FK constraint fails with error 1216 I need to know which of the constraints failed. SHOW INNODB STATUS returns the following output ... CONSTRAINT `u_registration_ibfk_1` FOREIGN KEY (`DEVICE_ID`) REFERENCES `u_device` (`DEVICE_ID`) ... Is there a way of getting to the name of the last failed FK constraint without using SHOW INNODB STATUS? What I need is the 'u_registration_ibfk_1' from the above example. Any help is highly appreciated -- Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Any limit for MyODBC connections?
Hi, Is there any database login restriction for myODBC? meaning there is any upper limit of number of connections? 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.
Foreign keys - No action - Errors
I have some problem with Foreign Key settings. I use MySQL 4.12, MySQL Query Browser 4.16 and Windows XP. Restrict and Cascade is Ok but when I try to set No action it wont work. Sometimes there is no error message and it seams that the change is saved. But when I check there is no changes. When an error message shows its nr 1005. What is the conditions to set No action. Ok In the manual it says only that No action is taken in the child table when rows are deleted from the parent or values in the referenced columns in the parent table are updated. I read the articles on MySQL , a lot of books and the manual but I cant get any answer. Thanks for any answer wich will solve my problem. Sven Åke Johansson [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
