database/table size
Hi, there! I would like to know whether mysql has built-in capabilities/config options in order to limid a database size or a table size. I want a table to grow up to a limit and, when reached, for a new row to be inserted the oldest one be deleted. Has mysql got this functionality built-in? If not, what other approaches could I use, triggers ...? Kinf regards. Prueba el Nuevo Correo Terra; Seguro, Rápido, Fiable. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
setting the data directory.
Hi All, I am trying to find out how to change the location of the data files. I have not yet found a generic entry for the my.conf file to set a data and log location. Can I move the exiting data directory form the mySQL tree and put a soft link to a new location on another partition? thanks, Scott Johnson [EMAIL PROTECTED] Tel.: (514) 917-4922 Fax: (514) 673-0011 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: setting the data directory.
Hi, There's a mysqld startup option --datadir=/path/to/data which you can use to alter your data directory. Simply adjust your startup scripts to suite. Regards, -Adam On 1/31/06, Scott Johnson [EMAIL PROTECTED] wrote: Hi All, I am trying to find out how to change the location of the data files. I have not yet found a generic entry for the my.conf file to set a data and log location. Can I move the exiting data directory form the mySQL tree and put a soft link to a new location on another partition? thanks, Scott Johnson [EMAIL PROTECTED] Tel.: (514) 917-4922 Fax: (514) 673-0011 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Adam Alkins http://www.rasadam.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: setting the data directory.
Adam Alkins wrote: Hi, There's a mysqld startup option --datadir=/path/to/data which you can use to alter your data directory. Simply adjust your startup scripts to suite. Regards, -Adam On 1/31/06, Scott Johnson [EMAIL PROTECTED] wrote: Hi All, I am trying to find out how to change the location of the data files. I have not yet found a generic entry for the my.conf file to set a data and log location. Can I move the exiting data directory form the mySQL tree and put a soft link to a new location on another partition? thanks, Scott Johnson [EMAIL PROTECTED] Tel.: (514) 917-4922 Fax: (514) 673-0011 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Adam Alkins http://www.rasadam.com Hi Scott and Adam, You don't even have to modify the startup scripts, just add datadir=/path/to/mysql/stuff to the my.cnf file and it will point to there. Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout
Re: Performance of MEMORY/HEAP-tables compared to mysql-cluster?
Hi, I am currently experiencing trouble getting my new mysql 5-servers running as slaves on my old 4.1.13-master. Looks like I'll have to dump the whole 30GB-database and import it on the new servers :( At this moment I do no see any oppurtunity to do this before the weekend since the longest time I can block any of our production systems is only 2-3 hours between midnight and 2am :( I am still curious if Innodb could handle the load of my updates on the heavy-traffic-tables since its disk-bound and does transactions. What I would probably need is an in-memory-table without any kind of locking - at least not table-locks! But there is no such engine in mysql. When a cluster can handle that (although it has the transaction-overhead) it would probably be perfect for since it even adds high availability in a very easy way... Jan Jan Kirchhoff schrieb: sheeri kritzer schrieb: No problem: Firstly, how are you measuring your updates on a single table? I took a few binary logs, grepped out for things that changed the table, counting the lines (using wc) and then dividing by the # of seconds the binary logs covered. The average for one table was 108 updates per second. I'm very intrigued as to how you came up with 2-300 updates per second for one table. . . did you do it that way? If not, how did you do it? (We are a VERY heavily trafficked site, having 18,000 people online and active, and that accounts for the 108 updates per second. So if you have more traffic than that. . .wow!) Thanks for your hardware/database information. I will look at that close tomorrow since I want to go home for today - it's already 9 pm over here... I need beer ;) We are not running a webservice here (actually we do, too, but thats on other systems). This is part of our database with data of major stock exchanges worldwide that we deliver realtime data for. Currently that are around 900,000 quotes, during trading hours they change all the time... We have much more updates than selects on the main database. Our Application that receives the datastream writes blocks (INSERT ... ON DUPLICATE KEY UPDATE...) with all records that changed since the last write. It gives me debug output like [timestamp] Wrote 19427 rows in 6 queries every 30 seconds - and that are numbers that I can rely on. Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to login to MYSQL as root without knowing the passward
Well under Linux I re-installed apache which overwrote the directory and deleted the pid of a currently running apache! My workaround was to do: $ netstat -l -t -p as root user from the command line. This gave me the running daemon processes that were listening to tcp ports, and their associated pid numbers. I was then able to stop the running apache with: $ kill 12345, where 12345 was the process number of the apache. There may be a similar command under windows that will enable you to get the PID of the mysql you want to kill. HTH Keith In theory, theory and practice are the same; In practice they are not. On Mon, 30 Jan 2006, Dan Trainor wrote: To: sol beach [EMAIL PROTECTED], mysql@lists.mysql.com From: Dan Trainor [EMAIL PROTECTED] Subject: Re: How to login to MYSQL as root without knowing the passward sol beach wrote: Sounds reasonable, but how do I shutdown down MYSQL so I can restart with --skip-grant-tables option? On 1/30/06, *Dan Trainor* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: sol beach wrote: I've been asked to assist the folks who own the data in the database, but folks who used to maintain it are no longer with the company. This installation resides on a Windows 2003 server to which I have local admin rights. What is the most painless way to get root access to this database? I am more than willing to RTFM, if anyone will point me at which FM to read. TIA Hi - I've always used the startup option of '--skip-grant-tables'. This may or may not be the correct way to do so, but we'll see what kind of feedback we get. I do this, then update the Password field in the mysql.user table. HTH -dant Hi - Stop the service? Kill the process? That's up to you. hth -dant -- 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: charset
Hello. In case mysql4 is mysql4.0 there should be a server system variable default_character_set. If it is 4.1 see: http://dev.mysql.com/doc/refman/4.1/en/charset-syntax.html http://dev.mysql.com/doc/refman/4.1/en/charset-connection.html OKAN ARI wrote: How can I set the characterset to latin5 for mysq4 in linux? OKAN -- 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: upgrade to mysql 5.x?
Hello. Most probably you have to upgrade when you need one of the new features of MySQL 5.0. See: http://dev.mysql.com/doc/refman/5.0/en/mysql-5-0-nutshell.html suomi wrote: Hi listers i have been looking around quite a bit for info on - when do i need to upgrade to mysql 5.x (because such and such functionality is not available in 4.1.x) - when is it recommended to upgrade to mysql 5.x (because such and such functionality is better implemented) - when will i fail to upgrade to mysql 5.x (because such and such database/type/funtion is not supported in mysql 5.x) - which are the pre-requisites to upgrade? - do i need to send my database through any batch when upgrading (such as when upgrading to 4.1: the grant tables) could anybody shed some light on these issues? suomi -- 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]
Trans.: RE: [SPAM] - Trans.: Re: Finding the row number satisfying a conditon in a result set - Bayesian Filter detected spam
I keep forgeting to reply all. Sorry - Message transféré de Jacques Brignon [EMAIL PROTECTED] - Date : Tue, 31 Jan 2006 13:33:22 +0100 De : Jacques Brignon [EMAIL PROTECTED] Adresse de retour :Jacques Brignon [EMAIL PROTECTED] Sujet : RE: [SPAM] - Trans.: Re: Finding the row number satisfying a conditon in a result set - Bayesian Filter detected spam À : Gordon Bruce [EMAIL PROTECTED] Thanks. That looks OK from the point of view of generating the row number. But when this is done if my result set is tens of thousands row long, how do I find the row number of the record satisfying a conditon (like name = something) without storing the result of the query in a temp table and query that table to find the row and its number? -- Jacques Brignon Selon Gordon Bruce [EMAIL PROTECTED]: You can use a user variable [EMAIL PROTECTED] in the sample below} to number the rows in the result set. mysql set @row:=0; Query OK, 0 rows affected (0.00 sec) mysql select @row:[EMAIL PROTECTED], city_Name from citiesw limit 10; +--+-+ | @row:[EMAIL PROTECTED] | city_Name | +--+-+ |1 | !fajji !fasan | |2 | 'aadeissa | |3 | 'abas | |4 | 'abas | |5 | 'abasabad | |6 | 'abd al qader | |7 | 'abdullah kalay | |8 | 'abdullah kalay | |9 | 'abruyeh| | 10 | 'adel bagrou| +--+-+ 10 rows in set (0.00 sec) -Original Message- From: Jacques Brignon [mailto:[EMAIL PROTECTED] Sent: Monday, January 30, 2006 9:19 AM To: mysql@lists.mysql.com Subject: [SPAM] - Trans.: Re: Finding the row number satisfying a conditon in a result set - Bayesian Filter detected spam Oops! forgoten to include the list in the relply -- Jacques Brignon - Message transféré de Jacques Brignon [EMAIL PROTECTED] - Date : Mon, 30 Jan 2006 16:16:53 +0100 De : Jacques Brignon [EMAIL PROTECTED] Adresse de retour :Jacques Brignon [EMAIL PROTECTED] Sujet : Re: Finding the row number satisfying a conditon in a result set À : Jake Peavy [EMAIL PROTECTED] Selon Jake Peavy [EMAIL PROTECTED]: On 1/30/06, Jacques Brignon [EMAIL PROTECTED] wrote: I would like some advice on the various and best ways of finding the rank of the row which satisfies a given condition in a rsult set. Let's assume that the result set includes a field containing an identifier from one of the table used in the query and that not two rows have the same value for this identifier but that the result set does not contains all the sequential values for this identifier and/or the values are not sorted in any predictable order. The brute force method is to loop through all the rows of the result set, until the number is found to get the rank of the row. That does not seem very clever and it can be very time consuming if the set has a lot of rows. use ORDER BY with a LIMIT of 1 your subject line needs work though - a row number has no meaning in a relational database. -jp Thanks for the tip, I am going to think to it as I do not see right away how this solves the problem. I agree with your comment, This is precisely because the result row number is not in the database that I need to find it. The problem I am trying to solve is the following: A query returns a result set with a number of rows, lets say 15000 as an example. I have an application wich displays those 10 by 10 with arrows based navigation capabilities (first page, previous page, next page, last page). I also have a search capability and I need to find in which set of 10 results the row I search for will be diplayed in order to show directly the appropriate page and to know what is the rank of this row in the result set or in the page to show the searched result row selected. As an example the row having a customer id of 125, would have the row # 563 in the result set (not orderd by customer id but by some other criterion like name) and would therefore be displayed in the page showing result rows 561 to 570 When I say row I do not mean a row in any table but a row in the result set produced by the query which can touch several tables. None of the fields of the result set contains the row number, it is just the number of time I have to loop through the result set to get the row in the set which matches my criterion. I hope this makes my question clearer. I am sure this is a pretty common problem, but I have not yet figured out the clever way to tackle it! -- Jacques Brignon - Fin du message transféré - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] - Fin du message
Sudden Mysql Crashes - table damage?
HI MySQL 5.0 (windows XP) I have a problem where i think some of my tables have somehow been corrupted, if i try to run a check table command against some tables the mysql service crashes and i have to restart it. I am not sure how this damage has occurred but has anyone got any suggestions of what to apart from dropping the tables? everything i have done so far has caused the service to crash which makes it pretty hard to find out what is wrong?!?!?!?! Any help very much appreciated Ade -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trans.: Re: Trans.: Re: Finding the row number satisfying a conditon in a result set
- Message transféré de Jacques Brignon [EMAIL PROTECTED] - Date : Tue, 31 Jan 2006 13:52:16 +0100 De : Jacques Brignon [EMAIL PROTECTED] Adresse de retour :Jacques Brignon [EMAIL PROTECTED] Sujet : Re: Trans.: Re: Finding the row number satisfying a conditon in a result set À : [EMAIL PROTECTED] Thanks Shawn for the detailed answer, What I am currently doing is basically what you propose, I do a full query to retreive the row numbers of the subset I want to display and of the selected record if any in that subset, then I use another query with LIMIT to get those rows for display. What I am trying to do is to improve the performance by limiting the number of queries and by identifying the most efficient way of finding the row number of the search record. I am currently using brute force by loopiong through the result set until I find the record. The proposal of storing the set in a temp table should improve that, allowing to retrieve the row by a query on that table which we can expect to be faster. So As you correctly describe, what I need is to allow the user to scroll through the set, and as you correctly describe, I am therefore usiong your option 2 doing one query to locate the rows and one with limit to get those to be displayed and of course I am hitting performance issues. I also noticed that all the queries using limit do not run at the same speed, the more you get close to the end of the data set the more it takes time. I uderstand the approach number 3 using a temp table, I am also intersted in your approach number 1 but I am not sure to understand what you mean and how you do that using the PHP MySql function libray. Do you mean passing all the rows of the result at once to the client application and storing them in memory (an array)? If the result set is big, couldn't we hit some limits or experience other performance issues? I see how to get in PHP the values of one row of the result set, how do you get all the rows at once other than looping through the result set and getting one row after the other? -- Jacques Brignon Selon [EMAIL PROTECTED]: Jacques Brignon [EMAIL PROTECTED] wrote on 01/30/2006 10:18:59 AM: Oops! forgoten to include the list in the relply -- Jacques Brignon - Message transféré de Jacques Brignon [EMAIL PROTECTED] - Date : Mon, 30 Jan 2006 16:16:53 +0100 De : Jacques Brignon [EMAIL PROTECTED] Adresse de retour :Jacques Brignon [EMAIL PROTECTED] Sujet : Re: Finding the row number satisfying a conditon in a result set À : Jake Peavy [EMAIL PROTECTED] Selon Jake Peavy [EMAIL PROTECTED]: On 1/30/06, Jacques Brignon [EMAIL PROTECTED] wrote: I would like some advice on the various and best ways of finding the rank of the row which satisfies a given condition in a rsult set. Let's assume that the result set includes a field containing an identifier from one of the table used in the query and that not two rows have the same value for this identifier but that the result set does not contains all the sequential values for this identifier and/or the values are not sorted in any predictable order. The brute force method is to loop through all the rows of the result set, until the number is found to get the rank of the row. That does not seem very clever and it can be very time consuming if the set has a lot of rows. use ORDER BY with a LIMIT of 1 your subject line needs work though - a row number has no meaning in a relational database. -jp Thanks for the tip, I am going to think to it as I do not see right away how this solves the problem. I agree with your comment, This is precisely because the result row number is not in the database that I need to find it. The problem I am trying to solve is the following: A query returns a result set with a number of rows, lets say 15000 as an example. I have an application wich displays those 10 by 10 with arrows based navigation capabilities (first page, previous page, next page, last page). I also have a search capability and I need to find in which set of 10 results the row I search for will be diplayed in order to show directly the appropriate page and to know what is the rank of this row in the result set or in the page to show the searched result row selected. As an example the row having a customer id of 125, would have the row # 563 in the result set (not orderd by customer id but by some other criterion like name) and would therefore be displayed in the page showing result rows 561 to 570 When I say row I do not mean a row in any table but a row in the result set produced by the query which can touch several tables. None of the fields of the result set contains the row number, it is just the number of time I have to loop through the result set to get the row in
Re: Help on Unknown field error
pedro mpa [EMAIL PROTECTED] wrote on 01/31/2006 12:49:48 AM: Greetings. I need help on the following query. I get an error like Unknown/Invalid column total_price [...] when I try filter by total_price. How can I do this correctly? SELECT receipts.*, (SELECT SUM(receipt_itens.price) FROM receipt_itens WHERE receipt_itens.id_receipt=receipts.id) AS total_price FROM receipts WHERE total_price = .$minprice. AND total_price = .$maxprice. ORDER BY receipts.date DESC Thanks in advance. Pedro. You don't need a subquery to get these results. You can also use a regular low-tech JOIN: CREATE TEMPORARY TABLE tmpDateTotals SELECT receipts.date , sum(receipt_itens.price) total_price FROM receipts LEFT JOIN receipt_itens ON receipts.id = receipt_itens.id_receipt HAVING total_price = .$minprice. AND total_price = .$maxprice. GROUP BY receipts.date; SELECT receipts.*, dt.total_price FROM receipts INNER JOIN tmpDateTotals dt ON dt.date = receipts.date ORDER BY receipts.date desc; DROP TEMPORARY TABLE tmpDateTotals; The problem with your original query was that you were trying to compare the results of a calculation in your WHERE clause. Results do not exist when WHERE clauses are evaluated but they do by the time the HAVING clauses are checked. The column`total_price` only exists _after_ the SUM() function is computed which happens _after_ the restrictions of any ON and WHERE clauses are applied to your source data. The HAVING clause is specifically designed to handle this type of comparison. I will bet dollars to doughnuts that your subquery version of this query will execute 5 to 10 times slower than my version without the subquery. Can you please try both and report your results? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Fw: Trans.: Re: Finding the row number satisfying a conditon in a result set
Oops - I too forgot the list! - Forwarded by Shawn Green/Unimin on 01/31/2006 09:19 AM - Shawn Green/Unimin 01/31/2006 09:06 AM To Jacques Brignon [EMAIL PROTECTED] cc Subject Re: Trans.: Re: Finding the row number satisfying a conditon in a result set Thank you for your response! :-) How to implement option 1 depends on your client-side environment. If you have an application that runs completely client-side then your results are already client-side when you ask for them and you don't have to worry about copying the data to the client in an array. If you are developing a web site then things change a bit. It is possible using DHTML(XHTML, or whatever they are calling it this week) to send all of the data to the client in the form of the HTML to create an array (usually a javascript array) within the browser page used to view the data. Then client-side scripting is used to scroll through the results (by creating and recreating a TABLE) and show the user just the pages you want them to see. A variant on this is to have a data browser page surrounding a data retrieval page (inside an IFRAME) and you manipulate the inner page from the outer page by controlling the scrolling in code (a variant of this theme would be to have the data frame hidden and you use client-side script to pick just portions of it for display.) Another way to speed this up would be to cache the results server-side in a session-level variable or in a static table that is uniquely identified within the session. Then as the user browses through the data, you don't need to run the original query multiple times to get to the particular subset of records you want to show. You can take it straight from your cache on the web server. A fourth option could be to use a client-server protocol like SOAP to actually query the database from the client interactively. However, this would still cause the database to execute your main query every time you wanted just a page of data. You already identified the need to minimize trips into the database. You just need to workout the best way for your application's design how to do that. Odds are, it's going to involve the temporary storage of your main query somewhere (a cache of the results). It may also require the building of an index array or two. Look up the quicksort and binary search algorithms if you take this route. They are very efficient and I have used them before on large sets of data with good performance results. I am sorry I can't be more specific but there are many approaches to this technique and I am not sure which one will work best for your situation. Let me know if I can help in any way. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jacques Brignon [EMAIL PROTECTED] wrote on 01/31/2006 07:52:16 AM: Thanks Shawn for the detailed answer, What I am currently doing is basically what you propose, I do a full query to retreive the row numbers of the subset I want to display and of the selected record if any in that subset, then I use another query with LIMIT toget those rows for display. What I am trying to do is to improve the performance by limiting thenumber of queries and by identifying the most efficient way of finding the rownumber of the search record. I am currently using brute force by loopiong through the result set until I find the record. The proposal of storing the set in a temp table should improve that, allowing to retrieve the row by a query on that table which we can expect to be faster. So As you correctly describe, what I need is to allow the user to scroll through the set, and as you correctly describe, I am therefore usiong your option 2 doing one query to locate the rows and one with limit to get those to be displayed and of course I am hitting performance issues. I also noticed that all the queries using limit do not run at the same speed, the more you get close to the end of the data set the more it takes time. I uderstand the approach number 3 using a temp table, I am also intersted in your approach number 1 but I am not sure to understand what you mean and how you do that using the PHP MySql function libray. Do you mean passing all the rows of the result at once to the client application and storing them in memory (an array)? If the result set is big, couldn't we hit some limits or experience other performance issues? I see how to getin PHP the values of one row of the result set, how do you get all the rows at once other than looping through the result set and getting one row after the other? -- Jacques Brignon Selon [EMAIL PROTECTED]: Jacques Brignon [EMAIL PROTECTED] wrote on 01/30/2006 10:18:59 AM: Oops! forgoten to include the list in the relply -- Jacques Brignon - Message transféré de Jacques Brignon [EMAIL PROTECTED] - Date : Mon, 30 Jan 2006 16:16:53 +0100 De :
Re: Sudden Mysql Crashes - table damage?
First of all I would check for file system corruption. IIRC there is a command line tool for repairing MyISAM tables, so you can use that without needing the MySQL database server. I'm sure there are full details in the manual: http://dev.mysql.com/doc/refman/5.0/en/ Good luck! James Harvard MySQL 5.0 (windows XP) I have a problem where i think some of my tables have somehow been corrupted, if i try to run a check table command against some tables the mysql service crashes and i have to restart it. I am not sure how this damage has occurred but has anyone got any suggestions of what to apart from dropping the tables? everything i have done so far has caused the service to crash which makes it pretty hard to find out what is wrong?!?!?!?! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Type conversion from Mysql to Oracle
Hi, folks I am trying to convert some Mysql database into Oracle. But their types are not exactly match. Could any one give a hint? Thanks in advance! Xiaobo -- Faculty of Computer Science Dalhousie University Halifax, Nova Scotia Canada -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help Understanding Document Syntax
First and foremost, thank you very much Michael for correcting my mistakes; I _was_ a bit sloppy in my reading of the syntax for the statements and that caused some unnecessary errors in my reply to Scott. However, your corrections are not _quite_ right even now. See below where I explain this. -- Rhino - Original Message - From: Michael Stassen [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Cc: Scott Purcell [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Tuesday, January 31, 2006 1:18 AM Subject: Re: Help Understanding Document Syntax Rhino wrote: The 'symbol' you are referring to, in the foreign key clause of the CREATE TABLE statement, is simply an opportunity for you to choose a name for the foreign key of the table; if you don't choose a name, MySQL will generate a default name for you. Therefore, if you do this: CREATE TABLE Foo ... constraint (bar) foreign key(workdept) references Sample.department on delete cascade ... That's not quite right. There should be no parentheses around the symbol, but you do need parentheses around the referenced column. The syntax is [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) [reference_definition] reference_definition: REFERENCES tbl_name [(index_col_name,...)] so you should have CONSTRAINT bar FOREIGN KEY (workdept) REFERENCES Sample (department) ON DELETE CASCADE snip I _think_ you are saying that you want the combination of values in two of the columns of your table to be unique so that no two rows of the same table can have that same combination of values in those two columns. I know how to do this in DB2, my main database, so I looked up the syntax to do the same thing in MySQL and came up with this small example: = use tmp; create table Purcell01 (empno smallint not null, fname char(10) not null, lname char(10) not null, primary key(empno) constraint(uk) unique index ukix btree (fname, lname)) Type=INNODB; For the record, unique constraints don't require InnoDB. Thanks for mentioning that. I didn't know one way or the other whether unique keys required INNODB; I know that _foreign_ keys are only supported in INNODB so I pretty much always use INNODB tables for everything I do in MySQL. It's useful to know that INNODB is not necessary to support unique keys. snip Unfortunately, I get a syntax error when I try this in my copy of MySQL, which is only 4.0.15. I'm guessing that the UNIQUE clause isn't recognized in MySQL 4.0.15 and that the statement will work in 5.1.x but there may be some problem with my syntax. I can't find an explicit example of a multicolumn unique constraint in the manual so maybe someone else reading this thread can identify any errors in the syntax if this doesn't work for you. UNIQUE constraints have been in mysql a long time (at least since 3.23, I believe). You have parentheses in the wrong place again. The syntax is [CONSTRAINT [symbol]] UNIQUE [INDEX] [index_name] [index_type] (index_col_name,...) so the correct definition would be CONSTRAINT uk UNIQUE INDEX ukix (fname, lname) or simply UNIQUE ukix (fname, lname) Strangely enough, both of those formulations of the UNIQUE clause fail for me with the same error as the mistaken version I first proposed in my note to Scott. This is the current version of my DROP/CREATE: drop table if exists Purcell01; create table if not exists Purcell01 (empno smallint not null, fname char(10) not null, lname char(10) not null, primary key(empno) -- constraint uk unique index ukix (fname, lname) -- unique ukix (fname, lname) ) Type=INNODB; If I run it exactly as shown, with both versions of the UNIQUE clause commented, it works fine. But if I uncomment either version of the UNIQUE clause, it fails with the same error I mentioned in my previous note. I've also tried 'unique(fname, lname)' and that also fails on the same error. Any idea why every formulation of the UNIQUE clause I try fails? If UNIQUE has been supported since Version 3.x, then I'm out of ideas The other thing you wanted was for a bad row, like the last row in my Inserts, to simply be ignored if it violates the unique constraint. In DB2, that isn't an option: the insert simply fails due to the violation of the uniqueness. However, it _appears_ that MySQL has a different policy. Apparently, you can add an IGNORE clause to an INSERT or UPDATE statement to make it ignore a uniqueness violation. As I read the article on the INSERT statement, you would want an INSERT to look like this if you wanted a row that violated uniqueness to be ignored: INSERT IGNORE INTO PURCELL01 VALUES(5, 'Fred', 'Flintstone'); The UPDATE statement appears to be the same idea; UPDATE IGNORE set fname = 'Fred', lname = 'Flintstone' where empno = 4; To be clear, attempting to insert a row which violates a unique constraint,
Re: Type conversion from Mysql to Oracle
Hi, I am trying to convert some Mysql database into Oracle. But their types are not exactly match. Could any one give a hint? What types do you need to convert? 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Type conversion from Mysql to Oracle
Xiaobo Chen [EMAIL PROTECTED] wrote on 01/31/2006 09:45:00 AM: Hi, folks I am trying to convert some Mysql database into Oracle. But their types are not exactly match. Could any one give a hint? Thanks in advance! Xiaobo -- Faculty of Computer Science Dalhousie University Halifax, Nova Scotia Canada Here are links to nearly everything there is to know about MySQL data types: http://dev.mysql.com/doc/refman/4.1/en/data-types.html http://dev.mysql.com/doc/refman/5.0/en/data-types.html Since I know next to nothing about Oracle data types, I am unqualified to help you to draw any comparisons between the system you are migrating from and whichever version of MySQL you may be migrating into. If you had been more specific about the versions you are using, the techniques you are using to migrate the data, the errors you are having and the results you expected, you could have received a much more detailed answer. Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Help Understanding Document Syntax
Could your error be as simple as forgetting to separate ALL of your indexes/constraints with commas? CREATE TABLE ( ... PRIMARY KEY (...) , UNIQUE(...) , INDEX (...), KEY(...), FOREIGN KEY (...) REFERENCES othertable (other column) ) ENGINE=INNODB ... ; It's hard to tell exactly but that's my first impression based on the code snippets in the previous responses. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rhino [EMAIL PROTECTED] wrote on 01/31/2006 09:49:17 AM: First and foremost, thank you very much Michael for correcting my mistakes; I _was_ a bit sloppy in my reading of the syntax for the statements and that caused some unnecessary errors in my reply to Scott. However, your corrections are not _quite_ right even now. See below where I explain this. -- Rhino - Original Message - From: Michael Stassen [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Cc: Scott Purcell [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Tuesday, January 31, 2006 1:18 AM Subject: Re: Help Understanding Document Syntax Rhino wrote: The 'symbol' you are referring to, in the foreign key clause of the CREATE TABLE statement, is simply an opportunity for you to choose a name for the foreign key of the table; if you don't choose a name, MySQL will generate a default name for you. Therefore, if you do this: CREATE TABLE Foo ... constraint (bar) foreign key(workdept) references Sample.department on delete cascade ... That's not quite right. There should be no parentheses around the symbol, but you do need parentheses around the referenced column. The syntax is [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) [reference_definition] reference_definition: REFERENCES tbl_name [(index_col_name,...)] so you should have CONSTRAINT bar FOREIGN KEY (workdept) REFERENCES Sample (department) ON DELETE CASCADE snip I _think_ you are saying that you want the combination of values in two of the columns of your table to be unique so that no two rows of the same table can have that same combination of values in those two columns. I know how to do this in DB2, my main database, so I looked up the syntax to do the same thing in MySQL and came up with this small example: = use tmp; create table Purcell01 (empno smallint not null, fname char(10) not null, lname char(10) not null, primary key(empno) constraint(uk) unique index ukix btree (fname, lname)) Type=INNODB; For the record, unique constraints don't require InnoDB. Thanks for mentioning that. I didn't know one way or the other whether unique keys required INNODB; I know that _foreign_ keys are only supported in INNODB so I pretty much always use INNODB tables for everything I do in MySQL. It's useful to know that INNODB is not necessary to support unique keys. snip Unfortunately, I get a syntax error when I try this in my copy of MySQL, which is only 4.0.15. I'm guessing that the UNIQUE clause isn't recognized in MySQL 4.0.15 and that the statement will work in 5.1.x but there may be some problem with my syntax. I can't find an explicit example of a multicolumn unique constraint in the manual so maybe someone else reading this thread can identify any errors in the syntax if this doesn't work for you. UNIQUE constraints have been in mysql a long time (at least since 3.23, I believe). You have parentheses in the wrong place again. The syntax is [CONSTRAINT [symbol]] UNIQUE [INDEX] [index_name] [index_type] (index_col_name,...) so the correct definition would be CONSTRAINT uk UNIQUE INDEX ukix (fname, lname) or simply UNIQUE ukix (fname, lname) Strangely enough, both of those formulations of the UNIQUE clause fail for me with the same error as the mistaken version I first proposed in my note to Scott. This is the current version of my DROP/CREATE: drop table if exists Purcell01; create table if not exists Purcell01 (empno smallint not null, fname char(10) not null, lname char(10) not null, primary key(empno) -- constraint uk unique index ukix (fname, lname) -- unique ukix (fname, lname) ) Type=INNODB; If I run it exactly as shown, with both versions of the UNIQUE clause commented, it works fine. But if I uncomment either version of the UNIQUE clause, it fails with the same error I mentioned in my previous note. I've also tried 'unique(fname, lname)' and that also fails on the same error. Any idea why every formulation of the UNIQUE clause I try fails? If UNIQUE has been supported since Version 3.x, then I'm out of ideas The other thing you wanted was for a bad row, like the last row in my Inserts, to simply be ignored if it
Re: Type conversion from Mysql to Oracle
Hello. In case you want to automate the process of migration have a look here: http://dev.mysql.com/doc/migration-toolkit/en/index.html There is a specific forum as well: http://forums.mysql.com/list.php?61 Xiaobo Chen wrote: Hi, folks I am trying to convert some Mysql database into Oracle. But their types are not exactly match. Could any one give a hint? Thanks in advance! Xiaobo -- 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: Help Understanding Document Syntax
Rhino wrote: First and foremost, thank you very much Michael for correcting my mistakes; I _was_ a bit sloppy in my reading of the syntax for the statements and that caused some unnecessary errors in my reply to Scott. However, your corrections are not _quite_ right even now. See below where I explain this. Strange. It was late when I answered, so I actually tested each statement before posting in an attempt to prevent that. snip Strangely enough, both of those formulations of the UNIQUE clause fail for me with the same error as the mistaken version I first proposed in my note to Scott. This is the current version of my DROP/CREATE: drop table if exists Purcell01; create table if not exists Purcell01 (empno smallint not null, fname char(10) not null, lname char(10) not null, primary key(empno) -- constraint uk unique index ukix (fname, lname) -- unique ukix (fname, lname) ) Type=INNODB; If I run it exactly as shown, with both versions of the UNIQUE clause commented, it works fine. But if I uncomment either version of the UNIQUE clause, it fails with the same error I mentioned in my previous note. I've also tried 'unique(fname, lname)' and that also fails on the same error. Did you add the comma after primary key(empno) when uncommenting one of the unique constraint lines? That's the only thing I can see. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sudden Mysql Crashes - table damage?
On 1/31/06, Adrian Bruce [EMAIL PROTECTED] wrote: HI MySQL 5.0 (windows XP) I have a problem where i think some of my tables have somehow been corrupted, if i try to run a check table command against some tables the mysql service crashes and i have to restart it. I am not sure how this damage has occurred but has anyone got any suggestions of what to apart from dropping the tables? everything i have done so far has caused the service to crash which makes it pretty hard to find out what is wrong?!?!?!?! Ade, What ahppens when you perform a mysqldump of the database that contains the corrupts table(s)? Is the process bailing part way through? I've seen people recover from certain corruption issues by dumping the database and recreating from scratch. At any rate, you should take a back-up of either the MyISAM data and .frm files (use BACKUP TABLE) or a mysqldump before starting any repair procedure. Regards, Imran -- http://www.ImranChaudhry.info MySQL Database Management Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sudden Mysql Crashes - table damage?
Thanks for the reply yep mysqldump also causes the service to crash. i have a few innodb tables mixed in amongst the myisam, it seems that it is the innodb tables that may be causing the problems but i am not sure why ??? Imran Chaudhry wrote: On 1/31/06, Adrian Bruce [EMAIL PROTECTED] wrote: HI MySQL 5.0 (windows XP) I have a problem where i think some of my tables have somehow been corrupted, if i try to run a check table command against some tables the mysql service crashes and i have to restart it. I am not sure how this damage has occurred but has anyone got any suggestions of what to apart from dropping the tables? everything i have done so far has caused the service to crash which makes it pretty hard to find out what is wrong?!?!?!?! Ade, What ahppens when you perform a mysqldump of the database that contains the corrupts table(s)? Is the process bailing part way through? I've seen people recover from certain corruption issues by dumping the database and recreating from scratch. At any rate, you should take a back-up of either the MyISAM data and .frm files (use BACKUP TABLE) or a mysqldump before starting any repair procedure. Regards, Imran -- http://www.ImranChaudhry.info MySQL Database Management Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
a character problem
Hello I use mysql 4.1 and php4.3.10 and phpmyadmin2.6 and apache-2.0.53 on FreeBSD5.3 I add any text as news with phpadmin on web. it works well. But I have a problem. I see the line as ( '' test ) on web (internet explorer) When I add a text which contains ( ' test ) character with phpmyadmin, Thats to say I see double ' character What shall I do ?
Re: Type conversion from Mysql to Oracle
Hi, folks
First thank all you who replied to my question. I should be more specific
with my question.
In mysql database table, there is some thing like in the 'create table
syntax':
fieldname enum('yes','no') default null
and
fieldname datetime default null
I have no idea about the first one. For the second, I am not familiar with
the 'DATE' type in Oracle and its formatting. In the Mysql table, the
format is like '-MM-DD HH:MN:SS'.
What I am doing is trying to duplicate all those tables from Mysql
database into the Oracle database and I am trying to do that by writing a
small java program. The issue I am facing now is that I need to get
correctly the 'create table syntax' for Oracle and trying to keep the
formats of each column as close as possible to that in the Mysql table.
I couldn't find a FREE tool to do this from Mysql to Oracle (not the other
direction). So I decided to write it up.
Thanks again.
Xiaobo
Hi,
I am trying to convert some Mysql database into Oracle. But their types
are not exactly match. Could any one give a hint?
What types do you need to convert?
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Faculty of Computer Science
Dalhousie University
Halifax, Nova Scotia
Canada
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Relocation error during link
All, Is there another list I can post this question to? I'm stuck in the water with Solaris. I have my application working correctly on HP 64bit and AIX 64bit, but having the below trouble on Solaris. I thought it might have been a bad MySQL download so we reinstalled the client libraries and the problem persists. I didn't have this problem with the 4.1.7 client, it only occurs with the 5.0 client and only on Solaris 64bit. Any help would be greatly appreciated. Thanks. Barbara -Original Message- From: Barbara Deaton [mailto:[EMAIL PROTECTED] Sent: Monday, January 30, 2006 4:00 PM To: mysql@lists.mysql.com Subject: Relocation error during link All, I've tried searching through the manual and couldn't find anything. I've compiled a simple program and when I link the object file to the mysql client libraries I'm getting a fatal relocation error: ld: fatal: relocation error: R_SPARC_H44: file /usr/local/dbi/mysql/5.0/lib/libmysqlclient.a(client.o): symbol my_pipe_sig_handler: relocations based on the ABS44 coding model can not be used in building a shared object My program is compiled by doing: /opt/compilers/FD7/SUNWspro/bin/cc -K PIC -xtarget=ultra3 -xarch=v9a -Xa -dalign -I /usr/local/dbi/mysql/5.0/include -c child.c My link line is: $ld -64 -G -o libmysqlclient_sun s64myl.o -L/usr/local/mysql/5.0/lib -lmysqlclient -lz I'm running on 64 bit solaris: SunOS 5.9 Generic_112233-08 sun4u sparc SUNW,Sun-Fire-15000 Does anyone have suggestions as to what I am doing wrong? Can you point me to some doc on linking client applications on Solaris? The pages I did find didn't mention this error. Thanks for your time and information. Barbara Deaton -- 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]
Insert performance
I am using mysql5.0 on Hp-UX. IT took about 14 hours to insert 1.7 millin records. How do I make my insert run faster.The table has three foreign key references and the referencing columns are indexed . Is that impacting the insert statement performance. Thanks for the help Vinay
Help on Unknown field error
-Mensagem original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] pedro mpa [EMAIL PROTECTED] wrote on 01/31/2006 12:49:48 AM: Greetings. I need help on the following query. I get an error like Unknown/Invalid column total_price [...] when I try filter by total_price. How can I do this correctly? SELECT receipts.*, (SELECT SUM(receipt_itens.price) FROM receipt_itens WHERE receipt_itens.id_receipt=receipts.id) AS total_price FROM receipts WHERE total_price = .$minprice. AND total_price = .$maxprice. ORDER BY receipts.date DESC Thanks in advance. Pedro. You don't need a subquery to get these results. You can also use a regular low-tech JOIN: CREATE TEMPORARY TABLE tmpDateTotals SELECT receipts.date , sum(receipt_itens.price) total_price FROM receipts LEFT JOIN receipt_itens ON receipts.id = receipt_itens.id_receipt HAVING total_price = .$minprice. AND total_price = .$maxprice. GROUP BY receipts.date; SELECT receipts.*, dt.total_price FROM receipts INNER JOIN tmpDateTotals dt ON dt.date = receipts.date ORDER BY receipts.date desc; DROP TEMPORARY TABLE tmpDateTotals; The problem with your original query was that you were trying to compare the results of a calculation in your WHERE clause. Results do not exist when WHERE clauses are evaluated but they do by the time the HAVING clauses are checked. The column`total_price` only exists _after_ the SUM() function is computed which happens _after_ the restrictions of any ON and WHERE clauses are applied to your source data. The HAVING clause is specifically designed to handle this type of comparison. I will bet dollars to doughnuts that your subquery version of this query will execute 5 to 10 times slower than my version without the subquery. Can you please try both and report your results? Thank you for your good explanation. The query I wrote is an example of a more complex query I am building in which I use a lot of subquerys in detriment of JOIN clause. When I finish rebuilding the query I will compare performance and post it. I didn't know that subquerys are much slower than JOINS. Thank you. Pedro. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Type conversion from Mysql to Oracle
Hi,
First thank all you who replied to my question. I should be more specific
with my question.
In mysql database table, there is some thing like in the 'create table
syntax':
fieldname enum('yes','no') default null
and
fieldname datetime default null
I have no idea about the first one.
ENUM is a MySQL specific type. I suggest you use a single
char in Oracle: CHAR(1) CHECK (VALUE IN ('Y', 'N') )
or a 3-char, value in 'YES', 'NO'.
For the second, I am not familiar with
the 'DATE' type in Oracle and its formatting. In the Mysql table, the
format is like '-MM-DD HH:MN:SS'.
Formatting is - largely - a client side issue.
What I am doing is trying to duplicate all those tables from Mysql
database into the Oracle database and I am trying to do that by writing a
small java program. The issue I am facing now is that I need to get
correctly the 'create table syntax' for Oracle and trying to keep the
formats of each column as close as possible to that in the Mysql table.
I couldn't find a FREE tool to do this from Mysql to Oracle (not the other
direction). So I decided to write it up.
Well, our database developer IDE Database Workbench can do
a lot of this, for you - www.upscene.com - but it doesn't come for
free.
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert performance
the indexes would significantly slow down the inserts, also are you doing the insert from a sql script, if so you couldtry this Wrapping your very long insert script in as follows set autocommit = 0 ; begin ; [[thousands of inserts here]] commit ; If you use the *mysqldump* option --opt, you get dump files that are fast to import into an InnoDB table, even without wrapping them with the SET AUTOCOMMIT and COMMIT statements you could also try this SET FOREIGN_KEY_CHECKS=0; *... import operation ...* SET FOREIGN_KEY_CHECKS=1; This would speed up your table inserts by turning the foreign key checks for the duration of the import session... you could also may be tune your blk_insert_buffer_size Kishore Jalleda On 1/31/06, Vinay [EMAIL PROTECTED] wrote: I am using mysql5.0 on Hp-UX. IT took about 14 hours to insert 1.7 millin records. How do I make my insert run faster.The table has three foreign key references and the referencing columns are indexed . Is that impacting the insert statement performance. Thanks for the help Vinay
Re: Insert performance
Vinay [EMAIL PROTECTED] wrote on 01/31/2006 11:42:51 AM: I am using mysql5.0 on Hp-UX. IT took about 14 hours to insert 1.7 millin records. How do I make my insert run faster.The table has three foreign key references and the referencing columns are indexed . Is that impacting the insert statement performance. Thanks for the help Vinay Without a doubt, you indexes are slowing you down. MySQL offers the ability to disable both real-time indexing and real-time foreign key checking. If this is the parent table of several child tables, you should be able to safely disable both during your import. Also, you should try to use the largest extended insert command possible. Trying to parse and execute 1.7 million individual INSERT statements is a real pain and you are just flailing your transaction coordinator (open a tx, do a single insert, commit the tx). With the extended insert format you get batches of rows per transaction (open a tx, insert several thousand rows, commit the tx). Check out: extended INSERT syntax: http://dev.mysql.com/doc/refman/5.0/en/insert.html ALTER TABLE ... ENABLE KEYS/DISABLE KEYS : http://dev.mysql.com/doc/refman/5.0/en/alter-table.html SET FOREIGN_KEY_CHECKS=... : http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html http://dev.mysql.com/doc/refman/5.0/en/set-option.html Speed of INSERTs: http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Insert performance
Indexes slow down inserts, updates (if the indexed column is being updated), and deletes. If this is a data-refresh, consider dropping the indexes, importing the data, and then indexing the table. You haven't mentioned how you are getting the data into the database? Is this a bulk-load? Insert statements? Some piece of code selecting from one database and inserting into MySQL 5.0? If you are using mysqldump to get data out as insert statements, use the extended-insert, as it's much much faster. David Vinay wrote: I am using mysql5.0 on Hp-UX. IT took about 14 hours to insert 1.7 millin records. How do I make my insert run faster.The table has three foreign key references and the referencing columns are indexed . Is that impacting the insert statement performance. Thanks for the help Vinay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert performance
I am using mysql5.0 on Hp-UX. IT took about 14 hours to insert 1.7 millin records. How do I make my insert run faster.The table has three foreign key references and the referencing columns are indexed . Is that impacting the insert statement performance. Just thought I'd add a little to the good advice already given to you. I find an easy way to derive the extended insert syntax is to perform a mysqldump of a small table. From 4.1 onwards this wrapped the table dump in the extended insert syntax. Regards, Imran -- http://www.ImranChaudhry.info MySQL Database Management Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert performance
Imran Chaudhry [EMAIL PROTECTED] wrote on 01/31/2006 12:44:17 PM: I am using mysql5.0 on Hp-UX. IT took about 14 hours to insert 1.7 millin records. How do I make my insert run faster.The table has three foreign key references and the referencing columns are indexed . Is that impacting the insert statement performance. Just thought I'd add a little to the good advice already given to you. I find an easy way to derive the extended insert syntax is to perform a mysqldump of a small table. From 4.1 onwards this wrapped the table dump in the extended insert syntax. Regards, Imran -- http://www.ImranChaudhry.info MySQL Database Management Design Services The only drawback to doing that for really large tables is you have to provide mysqldump with the --max_allowed_packet value for the server you want to send the data to. If you don't, mysqldump will make a single HUMONGOUS extended insert statement that will be refused by the destination server. I found this out the hard way a long time ago. For example: If on server A your max_allowed_packet value is 4193280... serverAshow variables like 'max%'; +++ | Variable_name | Value | +++ | max_allowed_packet | 4193280| | max_binlog_cache_size | 4294967295 | | max_binlog_size| 1073741824 | | max_connect_errors | 10 | | max_connections| 100| | max_delayed_threads| 20 | | max_error_count| 64 | | max_heap_table_size| 16777216 | | max_insert_delayed_threads | 20 | | max_join_size | 4294967295 | | max_length_for_sort_data | 1024 | | max_relay_log_size | 0 | | max_seeks_for_key | 4294967295 | | max_sort_length| 1024 | | max_tmp_tables | 32 | | max_user_connections | 0 | | max_write_lock_count | 4294967295 | +++ 17 rows in set (0.00 sec) ...and the data you want to load comes from server B then you have to tell mysqldump to use the value 4193280 or it will make just one large extended INSERT statement per table, regardless of table size \mysql\bin mysqldump -u login -p -h serverB --max_allowed_packet=4193280 sourcedatabasename Of course there are other options you need to worry about too (like quoting and compressing) but you get the picture. You can read about them in the fine manual or check the --help option of mysqldump. mysqldump --help Shawn Green Database Administrator Unimin Corporation - Spruce Pine
a character problem
Hello I use mysql 4.1 and php4.3.10 and phpmyadmin2.6 and apache-2.0.53 on FreeBSD5.3 I add any text as data with phpadmin on web. it works well. But I have a problem. As example if I add a text to mysql table with phpadmin. My text contains as below; Halid 's car is expensive I will see above the sentence as Halid ''s car is expensive on web (internet explorer) That's to say I see double ' character What shall I do ? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Testing Concurrent Insert on InnoDB
Hello. It seems that the problem is in the t_y_id auto_increment field. InnoDB puts special AUTO-INC table lock, and prevent other threads from inserting into Y. See: http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-column.html Ady Wicaksono wrote: Below is the SQL to create table Y CREATE TABLE `Y` ( `t_y_id` int(11) NOT NULL auto_increment, `t_y_time` timestamp NOT NULL default CURRENT_TIMESTAMP, `t_y_dest` varchar(16) NOT NULL default '', `t_y_msg` varchar(160) NOT NULL default '', `t_y_status` tinyint(2) NOT NULL default '0', `t_y_type` varchar(16) NOT NULL default '', `t_y_trx_id` varchar(40) NOT NULL default '', `t_y_trx_date` varchar(33) NOT NULL default '', `t_y_serviceid` varchar(20) NOT NULL default '', `t_y_pin` varchar(15) NOT NULL default '', `t_y_key` varchar(20) NOT NULL default '', `t_y_ans` varchar(160) NOT NULL default '', `in_sms_message_id` varchar(22) NOT NULL default '', `in_sms_time` datetime NOT NULL default '-00-00 00:00:00', `t_y_city` varchar(50) NOT NULL default '', PRIMARY KEY (`t_y_id`), KEY `t_idx01` (`t_y_type`(3)), KEY `t_idx02` (`t_y_key`(3)), KEY `t_idx03` (`t_y_ans`(8)), KEY `t_idx04` (`t_y_dest`(7)), KEY `t_idx05` (`t_y_dest`(13),`t_y_key`(15),`t_y_ans`(10)), KEY `t_idx06` (`t_y_time`), KEY `t_idx07` (`t_y_time`,`t_y_key`(6)), KEY `t_idx08` (`t_y_trx_id`(8)), KEY `t_idx09` (`t_y_trx_id`(10),`t_y_dest`(6)), KEY `t_idx10` (`t_y_status`,`t_y_type`(3)), KEY `t_idx11` (`in_sms_time`), KEY `t_idx12` (`t_y_time`,`t_y_type`(3)), KEY `t_idx13` (`t_y_city`(7)) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -- 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]
Error: server is running,with the --read-only option
Hi,
I get these errors:
[31-Jan-2006 17:25:59] PHP Fatal error: The MySQL server is running
with the --read-only option so it cannot execute this statement
query: INSERT INTO sessions (sid, uid, hostname, timestamp) VALUES
('22177a73e9b93b88e376c2226d000f7b', 0, '68.142.250.172', 1138724759) in
/data/.../database.mysql.inc on line 66
[31-Jan-2006 18:29:02] PHP Fatal error: The MySQL server is running
with the --read-only option so it cannot execute this statement
query: INSERT INTO sessions (sid, uid, hostname, timestamp) VALUES
('bf2de39170b0e02952cf1ab9cf7af6b0', 0, '68.142.250.46', 1138728542) in
/data/.../database.mysql.inc on line 66
The database is NOT read-only. I get this error every 10-60 minutes, but
the MySQL server used continously (on this particular web page, there
are ~30.000 hits a day). It's a MASTER server in replication. I don't
know about any LOCK situation (but I think if it would be LOCK, then the
error would be about LOCK).
Any ideas, why this happens? Google gave me no results.
Bye,
Andras
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NOT IN vs IS NULL
Devananda, OK, get the diescription in ordinary language right, and the query ought to write itself. Your schema is like this? ... and you want the paytable rows (i) for which there is an offers row matching paytable.offer_id, and (ii) for which there is no pab row where pab.login_id=paytable.login_id and pab.advertiser_id=offer_id? Would that be ... SELECT pt.offer_id, pt.login_id, o.advertiser_id FROM paytable AS pt INNER JOIN offers AS o USING (offer_id) LEFT JOIN publisher_advertiser_blocks AS pab1 ON pt.login_id=pab1.login_id LEFT JOIN publisher_advertiser_blocks AS pab2 ON o.advertiser_id=pab2.advertiser_id WHERE pab1.login_id IS NULL OR pab2.advertiser_id IS NULL; PB No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.25/247 - Release Date: 1/31/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sudden Mysql Crashes - table damage?
On Jan 31, 2006, at 11:01 AM, Adrian Bruce wrote: Thanks for the reply yep mysqldump also causes the service to crash. i have a few innodb tables mixed in amongst the myisam, it seems that it is the innodb tables that may be causing the problems but i am not sure why ??? If it's InnoDB crashing it's usually pretty good at logging some error. What does your .err file say? That's going to determine how much you can save if it's InnoDB. --Ware Imran Chaudhry wrote: On 1/31/06, Adrian Bruce [EMAIL PROTECTED] wrote: HI MySQL 5.0 (windows XP) I have a problem where i think some of my tables have somehow been corrupted, if i try to run a check table command against some tables the mysql service crashes and i have to restart it. I am not sure how this damage has occurred but has anyone got any suggestions of what to apart from dropping the tables? everything i have done so far has caused the service to crash which makes it pretty hard to find out what is wrong?!?!?!?! Ade, What ahppens when you perform a mysqldump of the database that contains the corrupts table(s)? Is the process bailing part way through? I've seen people recover from certain corruption issues by dumping the database and recreating from scratch. At any rate, you should take a back-up of either the MyISAM data and .frm files (use BACKUP TABLE) or a mysqldump before starting any repair procedure. Regards, Imran -- http://www.ImranChaudhry.info MySQL Database Management Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
5.0.18-max-log as a slave of a 4.1.13-standard-log master problem - slave hangs
I've been trying to get my new mysql-5.0.18-servers running as slaves of our
production systems to check if all our applications work fine with mysql 5 and
to do some tests and tuning on the new servers.
The old servers are all P4s, 3GB RAM running debian-linux, 2.4-kernel and
official mysql 4.1.13-standard-log binaries:
d1 is the master, d2 and d3 are slaves.
my new servers are dual-opterons, 6 GB RAM, running debian-linux with a
2.6.15-SMP-kernel, official mysql 5.0.18-max-log-binary. their names are d4 and
d5. I am currently trying to get d4 running as a slave of d1. d5 should later
become a slave of d4.
The old servers only have myisam and memory-tables, innodb is disabled. The new
ones had innodb and mysql-cluster enabled (datanodes running on the same
servers, management-node running on d3) since I wanted to do some testing with
the different engines, but I disabled both temporarily without any change in
this weird problem:
No matter if I do a copy of the /var/lib/mysql of d1 (and dump the contents of the memory-tables) while a flush tables with read lock is active and copy that to d4 (and doing a change master to... on d4 afterwards) or if I do a mysqldump --master-data=1:
The replication runs for maybe a minute or two and then hangs. show slave status says everything is OK but a
replicated replace hangs in the processlist and nothing happens. CPU-load goes down to zero. Even after 2 hours
nothing changed, a slave stop hangs, too, when I kill the replicated replace-process nothing happens and
I can't stop the mysql server and have to kill it with killall -9 mysqld in the shell :(
At first I thought this was a problem with a temporary table, but after having
reloaded a new dump a few times I had the same problem with really simple
inserts/updates like:
A new dump, everything works for a few minutes, then this query hangs:
| 4 | system user | | nachrichten | Connect | 11164 | update |replace into nachrichten.x_symbole (symbol,syscode,nachrichten_id) values('KUN','de','99949')
(taken directly from show processlist)
Info about the simple table:
CREATE TABLE `x_symbole` (
`symbol` char(20) NOT NULL default '',
`syscode` char(6) NOT NULL default '',
`nachrichten_id` int(11) NOT NULL default '0',
PRIMARY KEY (`symbol`,`syscode`,`nachrichten_id`),
KEY `nachrichten_id` (`nachrichten_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
I have to kill the mysqld with killall -9 mysqld, do a mysqladmin shutdown
again and then restart mysql and issue the query in the mysql-shell: it works!
Then I issue a start slave, everything works again for a minute or two and
hangs with some different query.
I go nuts with this! I spent so much time with this problem and did not get any further and I have absolutely no idea what the problem is. nothing in the error log.
Can anybody suggest something that might help? I have no idea whats wrong!
regards
Jan
d4:
mysql show variables;
+-++
| Variable_name | Value
|
+-++
| auto_increment_increment| 1
|
| auto_increment_offset | 1
|
| automatic_sp_privileges | ON
|
| back_log| 50
|
| basedir |
/usr/local/mysql-max-5.0.18-linux-x86_64-glibc23/ |
| binlog_cache_size | 32768
|
| bulk_insert_buffer_size | 15728640
|
| character_set_client| latin1
|
| character_set_connection| latin1
|
| character_set_database | latin1
|
| character_set_results | latin1
|
| character_set_server| latin1
|
| character_set_system| utf8
|
| character_sets_dir |
/usr/local/mysql-max-5.0.18-linux-x86_64-glibc23/share/mysql/charsets/ |
| collation_connection| latin1_swedish_ci
Re: MySQL ignores foreign key relationships between tables?
Paul DuBois wrote: At 20:41 -0500 1/30/06, Ferindo Middleton Jr wrote: Paul DuBois wrote: At 18:03 -0500 1/29/06, Ferindo Middleton Jr wrote: Ferindo Middleton Jr wrote: Ferindo Middleton Jr wrote: Paul DuBois wrote: At 17:56 -0500 1/28/06, Ferindo Middleton Jr wrote: I have two tables, registration schedules, that look like this: CREATE TABLE registration ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, schedule_id INTEGER REFERENCES schedules(id), ); CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, ); The registration table above references the the schedules table via the schedule_id. Why does MySQL allow a row created in the schedules table be DELETED if it has a matching schedule_id in the registration table. These two tables share a relationship based on registration.schedule_id schedules.id. I've tried this same syntax in PostgreSQL and it doesn't allow the schedules.id record to be deleted without first removing any records in the registration table which carry a matching schedule_id record. Isn't that the point of a relational database?- TO CHECK RELATIONSHIPS between tables and enforce that those relationships aren't broken? I find it disappointing that MySQL ignores this relationship. Add ENGINE = InnoDB to the end of your table definitions. Foreign keys are supported only for InnoDB tables in MySQL. I am using InnoDB. I use MySQL Administrator and InnoDB is what it says all my tables are already using so it must have chosen that by default or something. Does this mean that I shouldn't have been able to delete records from my schedules table above that had a foreign key in the registration table? Thanks. Ferindo Paul, I discovered that this foreign key constraint wasn't present in these tables anymore due to my own action. You see, I had backed up my database before using MySQL Administrator, not knowing that is was backing up such tables constructs as foreign keys, etc. So the database I'm looking at today isn't the same database I originally created with the same constraints... I'm going to stop using MySQL Administrator... using it seems somewhat misleading and it made me think that the tables sand the constraints I made on them were still present. Thanks. Ferindo I take it back. I imported the data in my database above without using the MySQL Administrator backup utility and first re-CREATEing the db tables in my database But still MySQL still allows for cross-referenced records between my schedules table and the registration table schedule id field to be deleted. Why do you think this is happening. Is this yet another feature that MySQL doesn't really support yet? Is MySQL totally ignoring the REFERENCES part of the schedule_id field from my registration table above. I've determined that I'm using InnoDB so why isn't it working? Ferindo Looking at: http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html I see no examples that are missing FOREIGN KEY. What happens if you change your table definition to change: schedule_id INTEGER REFERENCES schedules(id) To: schedule_id INTEGER, FOREIGN KEY (schedule_id) REFERENCES schedules(id) Also, SERIAL is an alias for a BIGINT type, so you'll probably need to make schedule_id a BIGINT. Thanks Paul, However, I tried changing my table definition in a test db like you suggest above to say: schedule_id BIGINT, FOREIGN KEY (schedule_id) REFERENCES schedules(id) ... but I get this error message still which seems to imply that I'm not forming this foreign key constraint correctly: ERROR 1005 (HY000): Can't create table '.\test\registration.frm' (errno: 150) any ideas what I'm doing wrong? I think so. But first, a tip: When you get an error like that from InnoDB, try SHOW ENGINE INNODB STATUS (or just SHOW INNODB STATUS in older versions of MySQL). Part of the output of this statement will likely contain more detail about the error. In this case, the error is my fault. :-) The referenced column and the referencing column must have the same data type, and I said that SERIAL was an alias for a BIGINT column. But it's really a BIGINT UNSIGNED column, so schedule_id has to be BIGINT UNSIGNED as well. And since SERIAL is also NOT NULL, you might as well make schedule_id NOT NULL, too. These definitions worked for me: CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_dateDATE NOT NULL ) ENGINE = InnoDB; CREATE TABLE registration ( idSERIAL NOT
innodb questions
Hi, I recently converted some of my tables to innodb from myisam. I don't need transactions or rollback capability, I switched because I needed row-level locking. These are large tables with many rows and lots of INSERTS and UPDATES. Since changing these tables, I've noticed some large files in my /var/lib/mysql directory. This is on my test server and I'm running gentoo linux. The files in this directory look like: /var/lib/mysql/gentoo1-bin.01 (1 Gig in size) /var/lib/mysql/gentoo1-bin.01 (1 Gig in size) /var/lib/mysql/gentoo1-bin.01 (1 Gig in size) /var/lib/mysql/gentoo1-bin.01 (1 Gig in size) ... /var/lib/mysql/gentoo1-bin.60 (1 Gig in size) /var/lib/mysql/ibdata1(10.0 Gig in size) Since I'm new to innodb tables, I assume that's where these files came from. Based on these file sizes, I need to get rid of them to save some disk space. My questions are: 1. Do I need them? 2. Can I delete them? 3. Can I limit the size of the ibdata1 file? Is it too late to resize it? 4. What can I set to reduce the size of these files? My innodb variables are: Variable Session value Global value innodb additional mem pool size33554432 33554432 innodb autoextend increment8 8 innodb buffer pool awe mem mb 0 0 innodb buffer pool size 536870912 536870912 innodb data file path ibdata1:10M:autoextend ibdata1:10M:autoextend innodb data home dir innodb fast shutdown ON ON innodb file io threads4 4 innodb file per table OFF OFF innodb flush log at trx commit 1 1 innodb flush method innodb force recovery 0 0 innodb lock wait timeout50 50 innodb locks unsafe for binlogOFF OFF innodb log arch dir innodb log archive OFF OFF innodb log buffer size8388608 8388608 innodb log file size5242880 5242880 innodb log files in group 2 2 innodb log group home dir./ ./ innodb max dirty pages pct90 90 innodb max purge lag 0 0 innodb mirrored log groups 1 1 innodb open files300 300 innodb table locksON ON innodb thread concurrency 8 8 Please let me know if I need to post any more variables. Thanks, Grant - Yahoo! Autos. Looking for a sweet ride? Get pricing, reviews, more on new and used cars.
Re: Testing Concurrent Insert on InnoDB
Hi Gleb Thanx a lot for explanation, make sense. I haven't try with INSERT INTO Y (t_y_time,and so on) SELECT (t_x_time, ... and so on) FROM X excluding t_y_id The table should not be locked, right :D Thx Gleb Paharenko wrote: Hello. It seems that the problem is in the t_y_id auto_increment field. InnoDB puts special AUTO-INC table lock, and prevent other threads from inserting into Y. See: http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-column.html Ady Wicaksono wrote: Below is the SQL to create table Y CREATE TABLE `Y` ( `t_y_id` int(11) NOT NULL auto_increment, `t_y_time` timestamp NOT NULL default CURRENT_TIMESTAMP, `t_y_dest` varchar(16) NOT NULL default '', `t_y_msg` varchar(160) NOT NULL default '', `t_y_status` tinyint(2) NOT NULL default '0', `t_y_type` varchar(16) NOT NULL default '', `t_y_trx_id` varchar(40) NOT NULL default '', `t_y_trx_date` varchar(33) NOT NULL default '', `t_y_serviceid` varchar(20) NOT NULL default '', `t_y_pin` varchar(15) NOT NULL default '', `t_y_key` varchar(20) NOT NULL default '', `t_y_ans` varchar(160) NOT NULL default '', `in_sms_message_id` varchar(22) NOT NULL default '', `in_sms_time` datetime NOT NULL default '-00-00 00:00:00', `t_y_city` varchar(50) NOT NULL default '', PRIMARY KEY (`t_y_id`), KEY `t_idx01` (`t_y_type`(3)), KEY `t_idx02` (`t_y_key`(3)), KEY `t_idx03` (`t_y_ans`(8)), KEY `t_idx04` (`t_y_dest`(7)), KEY `t_idx05` (`t_y_dest`(13),`t_y_key`(15),`t_y_ans`(10)), KEY `t_idx06` (`t_y_time`), KEY `t_idx07` (`t_y_time`,`t_y_key`(6)), KEY `t_idx08` (`t_y_trx_id`(8)), KEY `t_idx09` (`t_y_trx_id`(10),`t_y_dest`(6)), KEY `t_idx10` (`t_y_status`,`t_y_type`(3)), KEY `t_idx11` (`in_sms_time`), KEY `t_idx12` (`t_y_time`,`t_y_type`(3)), KEY `t_idx13` (`t_y_city`(7)) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error: server is running,with the --read-only option
Hi
If you really sure that your MySQL is not in read only option, better
you give us the my.cnf configuration
also mount result for all filesystem partition so we sure that the data
MySQL is not mounted read-only
Thx
BÁRTHÁZI András wrote:
Hi,
I get these errors:
[31-Jan-2006 17:25:59] PHP Fatal error: The MySQL server is running
with the --read-only option so it cannot execute this statement
query: INSERT INTO sessions (sid, uid, hostname, timestamp) VALUES
('22177a73e9b93b88e376c2226d000f7b', 0, '68.142.250.172', 1138724759) in
/data/.../database.mysql.inc on line 66
[31-Jan-2006 18:29:02] PHP Fatal error: The MySQL server is running
with the --read-only option so it cannot execute this statement
query: INSERT INTO sessions (sid, uid, hostname, timestamp) VALUES
('bf2de39170b0e02952cf1ab9cf7af6b0', 0, '68.142.250.46', 1138728542) in
/data/.../database.mysql.inc on line 66
The database is NOT read-only. I get this error every 10-60 minutes,
but the MySQL server used continously (on this particular web page,
there are ~30.000 hits a day). It's a MASTER server in replication. I
don't know about any LOCK situation (but I think if it would be LOCK,
then the error would be about LOCK).
Any ideas, why this happens? Google gave me no results.
Bye,
Andras
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert performance
Hi you could try: 1. disable autocommit while inserting those records, then commit after finished. 2. You could try insert multiple column like this insert into tableX values (a,b,c), (c,d,e), (e,f,g) ... CMIIW Vinay wrote: I am using mysql5.0 on Hp-UX. IT took about 14 hours to insert 1.7 millin records. How do I make my insert run faster.The table has three foreign key references and the referencing columns are indexed . Is that impacting the insert statement performance. Thanks for the help Vinay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NOT IN vs IS NULL
Devananda, Definitely not. The first LEFT JOIN, with the clauses "ON pt.login_id=pab1.login_id ... WHERE pab1.login_id IS NULL", does not make sense. ?!? It's a standard JOIN replacement for NOT IN (...). There is not a 1to1 relationship, These joins neither find nor assume 1:1 relationships. and pab1.login_id will rarely, if ever, be NULL in this query. If you LEFT JOIN table a to table b using column c WHERE b.c IS NULL, you get the rows in b which have no matching c values in a, which is logically equivalent to c in a and NOT IN b. This query ... SELECT DISTINCT pt.offer_id FROM paytable AS pt INNER JOIN offers AS o USING (offer_id) LEFT JOIN publisher_advertiser_blocks AS pab1 ON pt.login_id=pab1.login_id LEFT JOIN publisher_advertiser_blocks AS pab2 ON o.advertiser_id=pab2.advertiser_id WHERE pt.login_id=1 AND pab1.login_id IS NULL OR pab2.advertiser_id IS NULL; gives the same result as yours does, on the data you provided. PB - Devananda wrote: Peter Brawley wrote: Devananda, OK, get the diescription in ordinary language right, and the query ought to write itself. Your schema is like this? ... and you want the paytable rows (i) for which there is an offers row matching paytable.offer_id, and (ii) for which there is no pab row where pab.login_id=paytable.login_id and pab.advertiser_id=offer_id? Not quite. I want all the offer_id's which are stored in paytable ... (i) for a specified login_id (ii) that do not belong to advertiser_id's which that login_id is blocked from. Would that be ... SELECT pt.offer_id, pt.login_id, o.advertiser_id FROM paytable AS pt INNER JOIN offers AS o USING (offer_id) LEFT JOIN publisher_advertiser_blocks AS pab1 ON pt.login_id=pab1.login_id LEFT JOIN publisher_advertiser_blocks AS pab2 ON o.advertiser_id=pab2.advertiser_id WHERE pab1.login_id IS NULL OR pab2.advertiser_id IS NULL; Definitely not. The first LEFT JOIN, with the clauses "ON pt.login_id=pab1.login_id ... WHERE pab1.login_id IS NULL", does not make sense. There is not a 1to1 relationship, and pab1.login_id will rarely, if ever, be NULL in this query. For example, let's say login_id (1) has a hundred rows in paytable, each belonging to a different offer_id (1) ... (100), and of those, 10 belong to advertiser_id (1), 5 belong to advertiser_id (2), and the rest to (3). If pab (login_id, advertiser_id) contains the rows (1,1),(1,2) then the query should return to me 85 rows from paytable. Here's example data: INSERT INTO login_data (login_id) VALUES (1); INSERT INTO advertiser_data (advertiser_id) VALUES (1), (2), (3); INSERT INTO offers (offer_id, advertiser_id) VALUES (1, 1), (2, 1), ..., (11, 2), (12, 2), ..., (16, 3), ..., (100,3); INSERT INTO paytable (offer_id, login_id) VALUES (1, 1), (2, 1), ..., (100,1); INSERT INTO pab (login_id, advertiser_id) VALUES (1,1), (1,2); In this case, I would want all the rows in paytable: (i) for login_id (1), (ii) that do not belong to advertiser_id's (1) or (2). In SQL, this could be ... SELECT offer_id FROM paytable WHERE login_id=1 AND offer_id NOT IN ( SELECT offer_id FROM offers LEFT JOIN publisher_advertiser_blocks AS pab USING (advertiser_id) WHERE pab.login_id=1 ); ... but I'd like to get away from the NOT IN (...) clause. Maybe I am stuck using it? I apologize if I've been unable to adequately explain the way these tables relate to each other; honestly, I have had a bit of trouble wrapping my brain around it all, myself. Thanks again for all the suggestions, Devananda PB No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.25/247 - Release Date: 1/31/2006 -- Devananda vdv http://devananda-vdv.blogspot.com/ http://mycat.sourceforge.net/ No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.25/247 - Release Date: 1/31/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.25/247 - Release Date: 1/31/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
回复： Re: 回复： Re: 回 复： Re: MySQL 4.1 and PHP 4.4
--- Gleb Paharenko [EMAIL PROTECTED]写道: Hello. I usually put this statement after mysql_connect(). It is difficult for me to say is your site is Ok, because my knowledge is limited only to English, Russian, Ukrainian and elementary German. Check that you can get the correct data from mysql command line client, that all character_set_xxx is utf8 and the character set of the page is utf8 (your browser doesn't perform any conversion). Thank you for your follow up. I have found the problem not MySQL or PHP related. It might be a Cpanel problem. If you generate a MySQL 4.1 dump though Cpanel, your MySQL will be exported in latin1 character set, this way the data in utf8 is damaged and you get a corrupt database dump. This only happens with MySQL 4.1. When Cpanel is used with MySQL 4.0, there is no problem. SO this might be Cpanel not following up with changes of MySQL. I have restored a old database dump, which is generated from MySQL 4.0, importing it into MySQL 4.1 is painless and error free. My site is now readable again http://www.cnads.org/ Thanks for all the people helping me getting through this. Thanks Gleb! Lionel ___ 雅虎1G免费邮箱百分百防垃圾信 http://cn.mail.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb questions
On Jan 31, 2006, at 9:54 PM, Grant Giddens wrote: Since changing these tables, I've noticed some large files in my / var/lib/mysql directory. This is on my test server and I'm running gentoo linux. The files in this directory look like: /var/lib/mysql/gentoo1-bin.01 (1 Gig in size) /var/lib/mysql/gentoo1-bin.01 (1 Gig in size) /var/lib/mysql/gentoo1-bin.01 (1 Gig in size) /var/lib/mysql/gentoo1-bin.01 (1 Gig in size) ... /var/lib/mysql/gentoo1-bin.60 (1 Gig in size) These all look like binary log files, they aren't exclusive to InnoDB. You must have enabled binary logging in your my.cnf file as well. This is covered pretty extensively in the manual. Basically they are used for replication and recovery. In the latter you can take a point in time snapshot from a known binary log position and then if need be apply the logs (which is basically re-running the queries in them) to that to get back to where you databases was at any point in time. If you don't need any of these functions you can either get rid of the files or turn off binary logging. However, you probably don't want to just delete them from the file system. Have a look at the 'show master logs' and 'purge master logs' commands. This will delete the files and keep mysqld's internal index of the binary logs accurate. /var/lib/mysql/ibdata1(10.0 Gig in size) This is your InnoDB table space, you need it. You also need your ib_logfile0 etc... files. 3. Can I limit the size of the ibdata1 file? Only by limiting data in your tables. Also, in the shared table space (which you are using) you can't shrink this file. You can switch to innodb_file_per_table (see InnoDB manual) so that when you drop a table you save it's space (but not on deletion of individual records). However, even doing this you cannot delete your ibdata1 file or any shared tablespace files. You can recreate your entire tablespace (see below), but even then you'll need a (small) shared ibdata file. Is it too late to resize it? Yes, but you could use mysqldump to dump all data to text files, delete (or move) the tablespace, redefine it and then re-import. 4. What can I set to reduce the size of these files? Use innodb_file_per_table turn off binary loggin if you don't need it make sure index and field types are appropriate My innodb variables are: These came through poorly spaced, but I think it would help a lot to read the InnoDB sections of the manual (it's pretty manageable in size). InnoDB is really fantastic for certain applications, including heavy write load to large tables with concurrent reads. We've used it for several years on several billion records with 170 qps 50% of which is writes. There's no way to do this in MySQL other than InnoDB, realistically. That said, it has it's own learning curve. It's really an entirely new database engine, so there's lots to learn even if you've used mysql/myisam for years. In particular the tablespace layout and dependency on files other than ones linked to a particular table is a little daunting. --Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OPTIMIZE fails on very large table
Hello. We had an INNoDB table of very large size of 47 GB. Recently we deleted a lot of rows from the table in order to reduce the size. The number of rows now stands at 164,000+, however, the disk usage remains the same. I tried OPTIMIZEing the table; but the optimize fails after running for about an hour and a half. We have 15 GB of free space left in /var/lib/mysql/ and about 14GB in /tmp/ The MySQL server is running on a 2*3.00 GHz linux box of 4 GB ram and 140 GB HDD. How will we able to regain the free space? Comments/suggestions/flamings solicited. Regards, Rithish.
Connection problem after long idle periods
I am currently using MySQL 5.0.15 with mysql-connector-java-3.1.11. I can access the database from my JSPs with no problem except for one small issue. After a long delay (usually overnight), when someone first tries to access the database, I would get the following error: Communications link failure due to underlying exception: ** BEGIN NESTED EXCEPTION ** java.net.SocketException MESSAGE: Broken pipe STACKTRACE: java.net.SocketException: Broken pipe at java.net.SocketOutputStream.socketWrite0(Native Method) at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92) at java.net.SocketOutputStream.write(SocketOutputStream.java:136) at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:65) at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:123) at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2690) at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2619) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1552) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666) at com.mysql.jdbc.Connection.execSQL(Connection.java:2978) at com.mysql.jdbc.Connection.execSQL(Connection.java:2902) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:933) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1027) at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92) at org.apache.jsp.processLogin_jsp._jspService(org.apache.jsp.processLogin_jsp:81) at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97) at javax.servlet.http.HttpServlet.service(HttpServlet.java:802) at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322) at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:291) at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241) at javax.servlet.http.HttpServlet.service(HttpServlet.java:802) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744) at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527) at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80) at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684) at java.lang.Thread.run(Thread.java:595) ** END NESTED EXCEPTION ** Last packet sent to the server was 0 ms ago. But afterwards, on the second and subsequent attempts, the database runs fine. It is only the first try after a long idle period that I get this error message. I am not sure how to fix this. I am guessing that the database connection is down after a long idle period and have to recreate a connection first after the first try. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
