re: replication newbie questions
Hi! > "Ed" == Ed L writes: Ed> Mysql newbie here, looking for some help configuring 5.0.45 master-slave Ed> replication. Here's my scenario... Ed> We have a heavily loaded 30gb 5.0.45 DB we need to replicate via Ed> master-slave configuration to a new, beefier server running same mysql Ed> 5.0.45, and then cutover to the new server. Due to extreme SAN Ed> congestion and a grossly overloaded master server, our DB dumps take 5.5 Ed> hours. But we cannot afford that much downtime or locking during the Ed> replication transition; we can manage 10-15 minutes, but more is very Ed> problematic. Ed> I understand that "FLUSH TABLES WITH READ LOCK" will lock the tables for Ed> the duration of the 5.5 hour dump. Is this true? Yes. Ed> If so, we'd like to dump/initialize/sync slave WITHOUT any locking Ed> anything the master for more than a few seconds if at all possible. Ed> Will this give us the dump we need? Ed> mysqldump --single-transaction --master-data --all-databases You can do a dump without locking by using the xtrabackup tool. This however assumes you are using InnoDB as the storage engine. The other option is to use file system snapshots, if your file system supports that. In this case you only have to do the FLUSH TABLES ... for the duration of the snapshot. Regards, Monty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: InnoDB vs. other storage engines
Hi! > "Manuel" == Manuel Arostegui writes: Manuel> 2012/9/19 Mark Haney >> I hope this doesn't end in some kind of flame war. I'm looking to >> optimize my tables (and performance in general) of the DB my web app is >> using. I'm tweaking things a little at a time, but I'm curious as to what >> the rest of the MySQL list thinks about changing my storage engine from >> InnoDB to something else so I can optimize the tables on a regular basis. >> >> Is it worth the effort? Any caveats? Manuel> Hi Mark, Manuel> I would depend on what your workload would be. Mostly writes, mostly reads, Manuel> how many writes/reads do you expect etc. Manuel> The best approach, from my point of view, would be, firstly, tune your Manuel> MySQL server (if you've not done it yet) before getting into engine/tables Manuel> optimizations which can be more complicated. InnoDB is a great engine, but not suitable for everything. Depending on your usage, moving some tables to another engine may help. Here is some suggestions (in no particular order): - If you want to have small footprint but don't need commit, foreign keys or explicite rollback then ARIA is an option. http://kb.askmonty.org/en/aria-formerly-known-as-maria/ - Duplicating some data in the MEMORY engine may also be beneficially. - If your problem is a lot of write, then you should take a look at Tokutek. It's an engine that is optimized for a lot of inserts. http://www.tokutek.com/products/tokudb-for-mysql/ - If you want to utilize a lot of computers to analyze BIG data then ScaleDB (http://www.scaledb.com) or InfiniDB (http://infinidb.org/) may be an option. Good luck and please post/blog about your experiences! Regards, Monty Creator of MySQL and MariaDB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
re: ignore-db-dir
Hi! > "Noel" == Noel Butler writes: Noel> Shaun, Noel> Is this option planned for backport into 5.5.x ? Another option is to provide a patch and suggest to have it included in MariaDB 5.5. You can of course also consider to sponsor this so that we can do this for you... Regards, Monty Creator of MySQL and MariaDB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Exporting to CSV. Error Code: 2013. Lost connection to MySQL server during query
Hi! > "Fred" == Fred G writes: Fred> Thanks Dhaval. Putting the join condition before INTO outfile doesn't seem Fred> to work, either. Fred> When I try to use the same outfile name 'test123.csv' I get Error Code: Fred> 1086 File 'test123.csv' already exists. But then when I try to find the Fred> csv file on my computer, there is a folder with that name, but weird files Fred> in it, none of which are a csv-- and certainly not in the location that I Fred> thought it would be (the same directory that the .sql query is in). It's the mysqld server that is writing the .csv file. This means that the path is related to the mysql data directory and not to where your .sql file is. When using select into outfile it's always best to give a full path! Fred> Additionally, when I try to identify a different path, such as 'C:\\' etc, Fred> I get an error. This error is: Error Code: 1. Can't create/write to file Fred> "C:\test123.csv"(Errocde: 2). This probably means that you don't have write access to C:\ Fred> I tried running the query outputting to a different named .csv file, but it Fred> is still just "running..." and seems like it was like yesterday where after Fred> 10 minutes I will get the Error that the MySQL connection was lost. The reason that your connection is lost are ether: - There is timeout in the client you are using (The server never gives a timeout for running queries). - The mysqld server died (not likely but possible). - Some process in your system is killing quries that runs too long. One way to quickly check that things are working are by adding LIMIT 1 to the query. Fred> Does anyone have an idea of what is going on? >>> The query without exporting the file works fine, in about 12 sec/77 sec. >>> I >>> read online how to export MySQL queries into csv's, and I'm not sure what >>> I >>> am doing wrong. I keep getting the error: >>> Error Code: 2013. Lost connection to MySQL server during query, where the >>> duration/fetch values are 600.547 sec (~10 minutes). What is the exact error message? Which client are you using to do the query? It's strange that the query works fine when you are not using select into outfile. What MySQL version are you using Regards, Monty Creator of MySQL and MariaDB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
re: mysqlcheck 5.5 -a -o
Hi! > "Noel" == Noel Butler writes: Noel> In all previous versions we were able to run -Aao --auto-repair Noel> Anyone know WHY, in 5.5, -a and -o , must be run separately, this Noel> is kind of silly I think, requiring two passes now. I checked the 5.0 code and even there you could not run 'a' and 'o' separately. mysqlcheck always used the later option. However as optimize for most engines that supports it does an automatic analyze, you can solve your problem by just removing the 'a' option above. Regards, Monty PS: Have you tried out MariaDB yet? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
re: 5.1.x review
Hi! > "Elim" == Elim PDT writes: Elim> There are so many versions of 5.1, Is there some review or Elim> recommendations for a stable one? thanks Just download the latest MySQL 5.1 or MariaDB 5.1 release for your platform. Both should be stable enough for your usage. Regards, Monty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Help keep the Internet free
Hi! We have just launched a worldwide, multilingual petition at http://helpmysql.org to get signatures to show the regulators in the EU and other places that it's important that MySQL continues to be available and developed as a strong Open Source product for all database needs. If you care about the future of MySQL as an Open Source product, please go and sign the petition and tell others about it! We are also searching for volunteers that can help us with gathering names for the petition. If you have a bit a spare time and think that MySQL is worth saving, please join us on the #helpmysql IRC channel on Freenode and help us with spreading the world and gather names! You can make a real difference, wherever you are in the world! The more names we are able to get, the higher the chance is that we can keep MySQL free and available for all! Don't be fooled by the empty promises Oracle have given about the future of MySQL. I examined them in my previous blog post at http://monty-says.blogspot.com/2009/12/oracle-gives-only-empty-promises-fo r.html and showed that Oracle is not really promising anything; Not even that the Open Source version of MySQL will be developed further. Thanks to everyone that helped us with the previous campaign. It was of great help, but probably not enough, which is why we had to start this new campaign to gather even more names. This time we will be able to use the names to influence decisions worldwide (not just in the EU) and we will get more public attention. This will be far more powerful. During the previous campaign there was a lot of discussions in different medias about my incentives for driving it. I have tried to answer most of these concerns in my latest blogpost: http://monty-says.blogspot.com/2009/12/help-keep-internet-free.html Help us keep the Infrastructure of the Internet free! Regards, Monty Creator of MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help saving MySQL
Hi! > "Facundo" == Facundo Garat writes: Facundo> i don't really thinks this is about open source or not. Agree, this has to do about competition and that Oracle, the leader in revenue for databases are trying to buy MySQL, the leader in users just to kill it off. Facundo> MySQL became more closed when Sun bought it and while i think that Facundo> Oracle will try to get this even more closed IMO it's time to fork it as Facundo> a really open source project. Monty could lead this. The are a lot of Facundo> "forks" around that make MySQL even better that it is from SUN. I have already done a fork, MariaDB. This was done to engage the community that MySQL AB and Sun has been neglecting into MySQL development. The problem with a fork of an infrastructure program like GPL is that it can only survive if the owner of the copyright has good intentions, like Sun has. With Oracle as a copyright owner, there is very little chance for a fork to survice. I have written an analyse of this at: http://monty-says.blogspot.com/2009/10/importance-of-license-model-of-mysql-or.html The promises that Oracle has done regarding MySQL are not enough to protect MySQL from being killed as an Open Source project; When you analyse them, they are actually promising very little now and nothing after 5 years! It's still not too late to save MySQL and everyone that is using MySQL can help making a real difference. Please visit http://monty-says.blogspot.com/2009/12/help-saving-mysql.html and write a message to EC! Regards, Monty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Help saving MySQL
llowed to participate in development - Patches from users (like Google) that would have increased performance was not implemented/released until after Oracle announced it was acquiring Sun. - Oracle started working on InnoDB+, a better 'closed source' version of InnoDB - In the end Sun had to fork InnoDB, just to be able to improve performance. It's true that development did continue, but this was more to be able to continue using InnoDB as a pressure on MySQL Ab. Note that Oracle's development on the Linux kernel is not comparable with MySQL, because: - Oracle is using Linux as the main platform for their primary database product (and thus a better Linux makes Oracles platform better) - The GPL code in the kernel is not affecting what is running on top on it (because of an exception in Linux). Because we don't have access to a database of MySQL customers and users the only way we can get the word out is to use the MySQL and Open Source community. I would never have resorted to this if Oracle would not have broken the well established rules in anticompetitive merger cases and try to influence the EC by actively mobilising the customers. This is very critical to this AS SOON AS POSSIBLE as EC, depending on what Oracle is doing, needs to make a decision either on Monday (2009-12-14) or within two weeks. Becasue of the strict deadline, every email counts! What I want to ask you to do (until 2009-12-19): - Forward this email to everyone that you know is using MySQL or Open Source/free software and to all email list where you know there are people present that use or care about MySQL and open source (please check first that this email hasn't been sent there before) - Alternatively send emails with information about this and tell them to read http://monty-says.blogspot.com/2009/12/help-saving-mysql.html - Add links on your web site to http://monty-says.blogspot.com/2009/12/help-saving-mysql.html with the text "We are using MySQL, help save it", for the duration of the next two week. - Blog about this (feel free to include this text or just link to my blog) - Call by phone (don't contact by email, this is urgent) your boss or VP and ask him to read this email and send a letter to the EC commission ASAP! - If you don't have anyone to contact above, send an email to the EC! As we want the EC to get a correct picture of the situation, we want you to first fill in the upper part and then choose one of the proposed texts belove that best matches your view of the situation. Feel free to supply your own text and additional information if you think this will help the EC to reach a better understanding of how MySQL is used. Send this to: comp-merger-regis...@ec.europa.eu If you have extra time to help, fill in the following, if not, just skip to the main text. Name: Title: Company: Size of company: How many MySQL installations: Total data stored in MySQL (megabyte): For what type of applications is MySQL used: Should this email be kept confidential by EC: Yes/No Copy or use one of the below texts as a base for your answer: a) I don't trust that Oracle will take good care of MySQL and MySQL should be divested to another company or foundation that have everything to gain by developing and promoting MySQL. One should also in the future be able to combine MySQL with closed source application (either by exceptions, a more permissive license or be able to dual license MySQL under favourable terms) b) I think that Oracle could be a good steward of MySQL, but I would need EC to have legally binding guarantees from Oracle that: - All of MySQL will continue to be fully Open Source/free software in the future (no closed source modules) - That development will be done in community friendly way. - The manual should be released under a permissive license (so that one can fork it, the same way one can fork the server) - That MySQL should be released under a more permissive license to ensure that forks can truly compete with Oracle if Oracle is not a good steward after all. Alternatively: - One should be able to always buy low priced commercial licenses for MySQL. There should also be mechanism so that if Oracle is not doing what is expected of it, forks should be able to compete with Oracle c) I trust Oracle and I suggest that EC will approve the deal unconditionally. Let us prove to Oracle and EC that the Open Source community is a true force and we take good care of our citizens and we prefer to work with companies that does the same! The future of MySQL is in your hands! Thanks for the help! Michael Widenius Creator of MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
re: Question!
Hi! > "Jarikre" == Jarikre Efemena writes: Jarikre> Dear sir, Jarikre> Jarikre> I am young web developer using PHP Script in designing interactive website. I desire to include Mysql database on my websites. Jarikre> Jarikre> Please, how do I import, upload/export Mysql database to a website server after creating a Mysql user account and a particular database on my local machine? Jarikre> Jarikre> I will be very grateful if comprehensive response is granted to my question. Just copy the files in your data directory or use 'mysqldump'. Regards, Monty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: WL#946 and Changing time literal format
Hi! >>>>> "Konstantin" == Konstantin Osipov writes: Konstantin> * Michael Widenius [09/01/30 14:53]: >> Its more important that we don't break things for current users than >> try to be concerned about possible wrong usage that no one seams to do >> or find important enough to complain about. Konstantin> Monty, I disagree with this statement. Our current users use the Konstantin> current versions of the server. It's a separate question of what Konstantin> support we're willing to give them and for how long. Konstantin> In the new versions we should hold high the expectations of new Konstantin> users, and they are about standard compliance, and also about ease Konstantin> of migration. Sorry, but the above is not true. We have asked user over and over again what they think about the standard and they have said it's not critical or even important to them.; What is important that we don't break their old applications! When going forward, we must prioritize old user to new ones! The old ones are our current or customers in the near future. If we make them unhappy we don't have a business anymore. The new users will mainly listen to old user if they should use MySQL or not. If we make the old ones angry, we don't get new users. Konstantin> sql_modes are not a solution since they make the server code a Konstantin> mess, and won't let us make everyone happy anyway. I disagree that it makes the code messy. The code depends on how you implement them. sql_modes are there to help people easier switch to a newer server and gives them time to upgrade their old applications over time. When you have an application with million of code, it will take time to find and fix all issues. Seeing able to resolve things when things are found to break by simply using a sql_mode may save the day for them. It's important that you see the usage of MySQL from theu user point of view; Saying that something is complex and we will not do it, will not satisfy a user that needs it. Konstantin> MySQL server needs a vision. Sticking to expectations of existing Konstantin> users is looking back into (not-so) glorious past. Our existing users is the second biggest user base for any database. We reached this level as MySQL has worked to their expectations. Trying to do things differently, like other companies have tried, will just lead to failures. Konstanting> Trying to make everybody happy is infeasible. Konstantin> Our only option is to move forward Konstantin> to meet expectations of our modern adopters, and they are largely Konstantin> more intelligent, with past database experience, so the standard Konstantin> compliance is high on their list. On what do you base your observation ? It's not what our users have been telling us on MySQL conferences. People are using MySQL because it's different and can satisfy their needs. Standards are useful, but not important for our current or future users. Getting the job done and not having downtime, even when upgrading, that is important! Konstantin> What's worse, is that while we're fighting internally when to make Konstantin> an incompatible change and when not, our change management process Konstantin> is a mess. That's another issue, but it's not any reason to abound features that some of our users may depend on. Konstantin> We introduce incompatible changes in every major release, so Konstantin> people are forced to migrate their applications manually again and Konstantin> again. And yet we can't plan our changes in a way that a bulk Konstantin> incompatible changes in a certain area are done at once, forcing Konstantin> people to look into the problem once only, rather than on every Konstantin> upgrade. That is a problem with our development processes, has nothing to do with sql modes. Konstantin> It's a pity we can't shift our focus and mental efforts from Konstantin> developing a shared understanding what incompatible changes are Konstantin> right and called for, to developing the best way of making Konstantin> changes. Just focusing on one area doesn't solve any problems. What is needed is to have a good understanding of all aspect of the problem. I agree that we need to change things. I disagree that doing incompatible changes without planning and carefull thinking about how this will affect our user base is the right way to go. Regards, Monty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: WL#946 and Changing time literal format
Hi! >>>>> "Bernt" == Bernt M Johnsen writes: >>>>>>>>>>>>> Michael Widenius wrote (2009-01-24 02:07:54): >> As Dmitri pointed out, we shouldn't deprecate '.' as substitute for >> dates. >> >> Another things is that we should stop making decisions about >> incompatible changes without listening to the MySQL users. They know >> more than we how MySQL is used and they are directly affected of any >> incompatible change we force upon them. Bernt> Ok. And what is the users' verdict? Do they want a "helpful" best Bernt> effort interpretation of time and dates or do they want a well-defined Bernt> standardized portable scheme which reduces the possibilities of bugs? Normally they don't want their existing applications to break. Bernt> And what does this "helpfullness" lead to? In Norway it is common to Bernt> write dates as DD.MM.YY. So a buggy program that accepts 01.02.03 (for Bernt> the date 2003-02-01) whould be able to insert it into MySQL without Bernt> errors, but when retrieved the value is 2001-02-03. I don't think the Bernt> user/programmer is happy with that. They will quickly notice this and fix their time order. In reality we haven't got many complains from Norway about this, so I assume they are smart enough in Norway to not run into this problem. Its more important that we don't break things for current users than try to be concerned about possible wrong usage that no one seams to do or find important enough to complain about. Bernt> We have a Norwgeian word for this "helpfullness": "bjørnetjeneste", but Bernt> I'm not sure what the english idiom would be. Regards, Monty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: WL#946 and Changing time literal format
Hi! > "Roy" == Roy Lyseng writes: >> The question here is how PostgreSQL and ANSI does this and also what >> is the logical interpretation of the number. Roy> ISO 9075 (ANSI SQL) is very strict about this. It only allows TIME Roy> literals with 3 or 4 digit groups, and it only allows the ':' separator Roy> (except after the seconds part). There is no possibility for ambiguity, Roy> as the first number is always interpreted as an hour field. Roy> This is a literal format that is seen only by the SQL programmer, so Roy> there is no need for extensions. Date values provided by end users need Roy> to go through localization features, so that could be a different story. What is more important than ANSI is how our users are using TIME now and how they want to use it in the future. There is nothing wrong in making things easier for the end user by using a relaxed way to read in time constants. We don't want to break working applications that are already used to use our relaxed time format to read data. Regards, Monty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Monty left sun...
Hi! > "David" == David Giragosian <[EMAIL PROTECTED]> writes: David> Thanks. Just finished the blog, and I think I'm getting the drift. Don't belive everything you read :) I am still fully committed to work on MySQL AND the Maria engine. It's true that I haven't been as happy at Sun as I could be, but this is related to the MySQL organization, not to Sun itself. The main complaint I have about Sun is that they have not been moving fast enough to fix things that are obviously faulty, like our GA release criteria as can bee seen with the latest GA labeling. Regards, Monty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Server 5.1.30 has been released
Hi! > "David" == David Giragosian <[EMAIL PROTECTED]> writes: David> On 12/1/08, Daevid Vincent <[EMAIL PROTECTED]> wrote: >> >> Monty Widenius (MySQL co-founder who recently left Sun)... David> What's the news on this? I haven't left Sun. When MySQL was acquired by Sun, I did move out from the MySQL development organization to Sun's CTO lab where I am still today. I am still working on MySQL related stuff, like the Maria Storage Engine. Being part of another organization gives me a bit more freedom to do things I couldn't do before and also to speak my mind about the MySQL product and the MySQL development organization. Regards, Monty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Server 5.1.30 has been released
Hi! > "Daevid" == Daevid Vincent <[EMAIL PROTECTED]> writes: Daevid> Monty Widenius (MySQL co-founder who recently left Sun), on the other Daevid> hand, doesn't cast a very favorable light on 5.1 for production use. Daevid> Good read: Daevid> "If you plan to use any of the new features of MySQL 5.1, regard Daevid> these as if they would be of beta quality." Daevid> http://monty-says.blogspot.com/2008/11/oops-we-did-it-again-mysql-51-released.html To make things clear: I think that MySQL 5.1 is (much) better version of 5.0. It's *labeling* 5.1 as GA that I am not agreeing with. If you use any of the new features, you should use them with a bit of caution. They work for a lot of people but, as can be seen in the bug reports, there are still some rough edges that shouldn't be in a GA releases. When you have 10+ millions of users, it's not good enough to be 99 % good. Regards, Monty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.1 declared "GA"
Hi! If you want to know what to expect from MySQL 5.1 GA and when to start using it, please check my blog at: http://monty-says.blogspot.com/ Regards, Monty Creator of MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[4]: SELECT DISTINCT with ORDER BY implementation
Hi! > "Andrew" == Andrew Aksyonoff <[EMAIL PROTECTED]> writes: Andrew> Hello Sergey, Andrew> Monday, September 15, 2008, 10:41:31 PM, you wrote: >>> in MySQL but in general case it can't assume any order and will have >>> to re-sort the sub-select result by outer GROUP BY instead of inner >>> ORDER BY. If that sorting is stable, this should work, but can we rely SP> Yes. This is documented behavior: SP> http://dev.mysql.com/doc/refman/5.0/en/select.html : SP> "If you use GROUP BY, output rows are sorted according to the GROUP BY SP> columns as if you had an ORDER BY for the same columns. To avoid the SP> overhead of sorting that GROUP BY produces, add ORDER BY NULL: Andrew> Well, this snippet documents how the *grouped* rows will be ordered, Andrew> but the question is about the properties of specific sorting algorithm Andrew> that is internally used to implement GROUP BY. Andrew> I'm not sure if I'm clear enough so let me provide an example. Assume Andrew> that the inner SELECT produces the following: Andrew> id=1, sortkey=123, groupkey=33 Andrew> id=2, sortkey=124, groupkey=33 Andrew> id=3, sortkey=125, groupkey=11 Andrew> id=4, sortkey=126, groupkey=11 Andrew> id=5, sortkey=127, groupkey=22 Andrew> id=6, sortkey=128, groupkey=22 Andrew> I suppose that 'GROUP BY groupkey' will have to sort the incoming Andrew> rows by groupkey, then go over it sequentially, keeping only the Andrew> first encountered row for every given groupkey. That is one algoritm, but MySQL has others. Andrew> However if the specific sorting algorithm is not stable it *might* Andrew> change the order and produce something like that for temporary Andrew> sorted set: It's not stable; MySQL is using several different technics to calculate GROUP BY and may thus return the rows in any order within the group by. Andrew> id=4, sortkey=126, groupkey=11 Andrew> id=3, sortkey=125, groupkey=11 Andrew> id=6, sortkey=128, groupkey=22 Andrew> id=5, sortkey=127, groupkey=22 Andrew> id=1, sortkey=123, groupkey=33 Andrew> id=2, sortkey=124, groupkey=33 Andrew> And put id=4 instead of id=3 into the result set. Andrew> So the question is a bit more subtle :) It's whether the algorithm Andrew> that GROUP BY (and possibly everything else) uses stable or not. Andrew> I'd bet a quarter that it is but just want to make sure :) In general with SQL: Don't assume any order of rows if you don't explicitely specify a sort order. You can send the quarter to 'the well being of dolphin fund'. Regards, Monty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help requested to provide bug reports for 5.1
Hi! > "Mark" == Mark Callaghan <[EMAIL PROTECTED]> writes: Mark> Can MySQL share the results of performance testing that has been done to Mark> compare 5.1 with 5.0? I would really like to see them too :) I will ask around and see if I can find what was done and what the results where. Regards, Monty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help requested to provide bug reports for 5.1
Hi! This is a request to all MySQL users to help mysql developers, by providing information, so that we can help you, by providing a more stable MySQL server for your needs. As you may know, MySQL 5.1 has been in state of release candidate (RC) for some time. The last RC was announced as the last RC and is supposed to be followed by a GA release. The GA release is planned to be the exact same code as the last GA, only with the label changed. The question we, who are developing and supporting the MYSQL server have been asking ourselves is, "Are really now in shape to do a proper GA release?". We would like you as a MySQL User to help us out with deciding this. We don't want to repeat the mistake we did with MySQL 5.0 GA and then again with MySQL 5.1 RC, by releasing a MySQL 5.1 GA too early. Our external criteria for General Availability (GA) or Production release can be found here: http://dev.mysql.com/doc/refman/5.1/en/choosing-version.html What you may not know is that we have as part of our internal GA criteria, a requirement that we should not have any serious bugs, crashing or wrong result bugs, that affects a notable amount of users. The criteria states that it is ok to postpone fixes for bugs that have a low impact (ie affects few users). This has the following implications: - Bugs for which we don't have many user/customer reports for are not likely to get fixed. (In the worst case not even in 6.0 !) - It's ok to go out with bugs in new feature in the GA as long as we don't have many users/customers that have reported problems with these features. In other words, if you have an issue with a serious bug that exists in 5.1 that you *really* would like to have fixed soon now is your chance to influence our development! Note that it's ok, and we want, you to also report bugs after we release the MySQL 5.1 as GA. The more users/customers commenting on a bug the more chance it will be fixed! What I would like for people to do: - Report every single bug that you encounter or know about in 5.1 into our bugs system. http://bugs.mysql.com/ - If the bug is already reported, please add a comment to the bug report that it affects you too. - If you are a MySQL support customer, add either a note to the bug report that you are a customer and the bug affects you or send a request as a customer directly to the MySQL support personal (they are happy to take your reports!) - If you have reported a bug a long time ago that has not been fixed and this is still important for you to get fixed, please reopen the bug/add a note in the bugs database that the bug is still relevant for you! Note that you should also do this for bugs that you know of in MySQL 5.0 that are still open for MySQL 5.1 (or any bugs that are labelled to be fixed in a future release). Bugs that are in earlier version are also very likely to also be in 5.1 if the bugs database doesn't say otherwise. Lets make the MySQL server bugs life's harder by giving us more information about which bugs are really important for us to fix. More information will help us make better decisions about how and when a bug should be fixed. You can find the above text also at: http://monty-says.blogspot.com/ Regards, Monty Founder of MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Urgent problem
Hi! > "Peter" == Peter Lauri <[EMAIL PROTECTED]> writes: Peter> Best group member, Peter> My computer stopped working. And I was able to install Windows on another Peter> partition and now I face one problem. I was running Windows with MySQL 4.1. Peter> I have all C: working, and can access all files. Peter> The first question: Can I recover that data from MySQL? Peter> The second question: How can I do that? (All attempts to repair Windows has Peter> failed, so that option is gone) Peter> I have gone thru the manual but all information there seems to be related to Peter> if the database crashes. And some options are there when you do make Peter> backups, but I do not have the backups. Peter> A "desperate" soul... In principle you can just copy the tables from one partition/computer to another and start using them. (The MySQL version doesn't have to exactly the same, as long as the new one is newer than the old one). To fix this: - Install MySQL on your new partition (computer) - Copy all files in the MySQL data directory (normally C:\mysql\data) to your new partition. If the path is different, you may have to create a my.cnf file that includes the --basedir and --datadir options to your new path. - Restart MySQL. - Check/repair your tables by using from the command line: C:\mysql\bin\mysqlcheck --all-databases --auto-repair Regards, Monty CTO of MySQL AB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help us at MySQL user conference
Hi! I assume most of you know that we will have our annual MySQL User conference at April 14-15 in Orlando. During the conference we will give a lot of information about new MYSQL features that are already implemented in MySQL 4.1 and 5.0 and also about the new features we plan to implement in future MySQL versions. On the conference there will be a lot of MySQL developers around with intimate knowledge of all MySQL server code. I would like you to talk with them and tell us what features of MySQL are important to you. By knowing your needs, we will be able to better satisfy them! For example: - What features you find important and why. - What features you would like us to implement in the near / far future. - Talk with MySQL developers about the problems you have faced with MySQL - Ideas of how to we could do things better. - How to solve some specific problem you are facing with MySQL or some other database. - What you would need from MySQL to be able to move your current application to MySQL - Cool ideas / functions that you would like to see implemented. - New table handlers that you would like to implement / use. If you have a tough problem, put it on your laptop or on a CD/DVD and ask some of the MySQL developers to help you with it during the conference! If you can't come to the conference, please ensure that someone else you know comes and talks with our developers so that we can get your feedback/ideas to improve MySQL. The MySQL developers that will attend the conference, hold talks and be available for questions during the whole conference (and beer/vodka during the evenings) are: - Brian Aker, Director of Architecture (Slashdot Database Architect) UDF, Storage Engines, Apache, replication, Perl, using MySQL under high load. - Sergei Golubchik, Sr Software Engineer MyISAM, Merge Tables, Full Text Search - Lars Thalmann, Software Engineer MySQL Cluster, Replication - Mikael Ronstrom, Sr Software Architect MySQL Cluster, NDB API - Konstantin Osipov, Software Engineer Cursors, Prepared Statements - Peter Gulutzan, Software Architect SQL Standards, Stored Procedures - Alexander Byelkin, Software Engineer Sub-queries, Query Cache, Views - Mark Matthews, Software Dev Manager Connector/J, JDBC, Java - Jim Winstead, Lead Web Developer PHP, Apache, MySQL.com webmaster - Alfredo Kojima, Software Engineer MySQL Adminstrator, Windowmaker, & other MySQL GUI tools - Mike Zinner, Software Engineer MySQL Adminstrator, Workbench, & other MySQL GUI tools - Peter Zaitsev, Benchmarks Specialist Optimizations, Benchmarks - Alexey "Holyfoot" Botchkov, Software Developer Embedded Library, GIS Support - Heikki Tuuri, Innodb Author Innodb Storage Engine - David Axmark, Co-Founder MySQL Community - Carsten Pedersen, MySQL certification - Monty Widenius, Founder and CTO Main author of MySQL; Knows most things that are worth knowing about MySQL, MyODBC and some more. You can find more information on our conference including a complete list of sessions, labs, workshops and speakers at: http://www.mysql.com/uc2004 Hope to see a lot of you there! Regards, Monty CTO of MySQL AB PS: The conference is not only for developers. It's perfectly ok to bring your boss with you to get him convinced that MySQL is a safe bet:) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Urgent: Prepared Statements C API hangup the Server - Help Urgent....
hi! >>>>> "A" == Arunachalam <[EMAIL PROTECTED]> writes: A> Hai MySQLians!!! A> I have established the Successful Connection to MySQL from COBOL. A> Now from COBOL I can able to query the MySQL and get back the A> result sets using C APIs under MySQL 4.0.17-max both in windows A> and Linux. Note that my Client is in Windows platform... A> Its not enough for my application, I'm in need to use Prepared statements A> for further SQL processing... A> As per my searching I have found the right C API in libmysql.lib from A> MySQL 5.0.0, (i have doubt: Is any eariler version 4.0.xx provides this???) A> C API routines are: mysql_prepare(), mysql_bind_param(), mysql_execute(), A> mysql_stmt_affected_rows(), mysql_fetch(), mysql_stmt_close()... You should probablty use MySQL 4.1 instead of MySQL 5.0 for testing this as we make more often releases for 4.1 than for 5.0 (until 4.1 is production ready). A> I have installed MySQL 5.0.0-alpha for Windows and I have properly linked A> thelibmysql.lib into my COBOL compiler... To verify the Parameter Passing A> mechanism to the API routines i have taken the sample C code from MySQL A> manual (URL: * http://www.mysql.com/doc/en/mysql_execute.html * ) A> When I compiled this sample coding using VC++, it ask for *typelib.h* header A> file, it is not given with insatllation pack, under the Mysql folder A> (C:/MySQL/Include), I have searched on the net and include it accordingly. Thanks for notifying us about this. typelib.h should be included in the MySQL folder and we will do that for next MySQL windows release. (4.1.2) A> After that i have made changes in some variable names becaz of it follows A> other header files variable name as such. What changes did you have to make ? Including typelib.h should be enough. I will ask our windows team to verify that the example works ok with VC++ A> Upon all the changes I can able to execute succcessfully the coding and it A> does the function properly on the MySQL server database. A> The PROBLEM I met is, Once this sample code executed and inserted data to A> the Database the server get's hanged - why??? This shouldn't happen. Can you please describe exactly what you mean with a 'hang' ? - Does the connection in your test program stop respoding ? - Can you connect to mysqld with other clients ? - Does the MySQL server take all cpu In the tests directory of the MySQL source tree there is a test program 'client_test' that shows in detail how to use prepared statements with MySQL. A> (Note that If I didn't run this coding or my COBOL coding the Server remain A> function properly) A> Kindly suggest me the solution or quote me the things i forgot to do... In the released 5.0 server there is some known bugs in the prepared statement handling for some specific prepared statements. We have fixed these bugs in 4.1.2 which should be available quite soon (we are just now closing the last open bugs in 4.1 to be able to make a new release ASAP). To get any problems you have fixed, you could try to make a standalone test case in C that we could try to repeate. Another option is to follow the instructions in the MySQL manual of how to debug MySQL and give us a stack trace of where mysqld is hanging. Another option is to use the debug version of mysqld and start it with --debug. The trace file that is written (in C:\ or /tmp) should give us a good idea of where the bug could be. Regards, Monty -- Michael Widenius <[EMAIL PROTECTED]> MySQL AB, CTO Helsinki, Finland Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.0.0 has been released
Hi, MySQL 5.0.0, a new version of the popular Open Source/Free Software Database Management System, has been released. It is now available in source and binary form for a number of platforms from our download pages at http://www.mysql.com/downloads/ and mirror sites. Do to an unfortunate build error in the last minute we can't provide -max binaries for 5.0.0 at this time. Sorry about that. Note that not all mirror sites may be up to date at this point in time - if you can't find this version on some mirror, please try again later or choose another download site. This is the first Alpha development release of the 5.0 tree, adding many new features (see below). As this code is currently labeled "Alpha", we do not recommend that this version be used in production environments yet! It does however pass our test suite on all our build platforms and all old features should be resonable stable. However, we encourage you to test and evaluate it and, more importantly, report any bugs or observations to our bug tracking database at http://bugs.mysql.com/. Please note, that for us to resolve a bug report, a reproducible test is required. See "How to report a bug" at http://bugs.mysql.com/how-to-report.php for more details before filing a bug report. We appreciate your support! The most prominent new feature of MySQL 5.0 is probably Basic support for stored procedures (SQL-99 style). However, there are several additional enhancements, which are planned to be implemented before MySQL 5.0 reaches beta status. Our development plan is to continue to add new features to 5.0 at least until 4.1 reaches 'gamma' status after which we will move new development to 5.1. Our man goal is to have more major releases with shorter time intervals to get out new stable features faster. THe MySQL 4.1 branch seams to be relatively stable and we will, if we don't find any new unexpected hard bugs that will require a new design decisions, make a beta release of 4.1 in January followed by a gamma release ASAP. Merry Christmas, Lenz Grimmer & Michael Widenius - -- Lenz Grimmer <[EMAIL PROTECTED]> Senior Production Engineer MySQL GmbH, http://www.mysql.de/ Hamburg, Germany Michael Widenius <[EMAIL PROTECTED]> MySQL AB, CTO Helsinki, Finland -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Testing Problems
Hi! >>>>> "Chris" == Chris Man <[EMAIL PROTECTED]> writes: Chris> Hi, Chris> We have just installed MySQL 3.23.53 on Solaris 9. We were going through the Chris> testing stages and it had produced an error. Chris> Pasted below is the error that we received and also attached are the logs. Chris> Installing Test Databases Chris> Removing Stale Files Chris> Installing Master Databases Chris> Installing Slave Databases Chris> Starting MySQL daemon Chris> Loading Standard Test Databases Chris> Starting Tests Chris> --- r/rpl15.reject Tue Dec 16 18:46:27 2003 Chris> *** Chris> *** 1,7 Chris> File PositionBinlog_do_dbBinlog_ignore_db Chris> master-bin.001 73 Chris> Master_HostMaster_User Master_Port Connect_retry Log_File Chris> Pos Slave_Running Replicate_do_db Replicate_ignore_db Chris> Last_errno Last_error Skip_counter Chris> !0 0 0 No 0 Chris> 0 Chris> Master_HostMaster_User Master_Port Connect_retry Log_File Chris> Pos Slave_Running Replicate_do_db Replicate_ignore_db Chris> Last_errno Last_error Skip_counter Chris> 127.0.0.1 test999860 4 No Chris> 00 Chris> Master_HostMaster_User Master_Port Connect_retry Log_File Chris> Pos Slave_Running Replicate_do_db Replicate_ignore_db Chris> Last_errno Last_error Skip_counter Chris> --- 1,7 Chris> File PositionBinlog_do_dbBinlog_ignore_db Chris> master-bin.001 73 Chris> Master_HostMaster_User Master_Port Connect_retry Log_File Chris> Pos Slave_Running Replicate_do_db Replicate_ignore_db Chris> Last_errno Last_error Skip_counter Chris> ! 127.0.0.1 root93061 4 No Chris> 00 Chris> Master_HostMaster_User Master_Port Connect_retry Log_File Chris> Pos Slave_Running Replicate_do_db Replicate_ignore_db Chris> Last_errno Last_error Skip_counter Chris> 127.0.0.1 test999860 4 No Chris> 00 Chris> Master_HostMaster_User Master_Port Connect_retry Log_File Chris> Pos Slave_Running Replicate_do_db Replicate_ignore_db Chris> Last_errno Last_error Skip_counter The above is a timing error that only influences the test suite. you can safely skip this. In MySQL 4.0, we have done some changes to the test suite to avoid the above problem. Regards, Monty -- Michael Widenius <[EMAIL PROTECTED]> MySQL AB, CTO Helsinki, Finland Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: printing reports
Hi! >>>>> "Haydies" == Haydies <[EMAIL PROTECTED]> writes: Haydies> Access is not a Database, its a kiddies toy. Haydies> You can just as easlily make your reports using crystal reports or any thing Haydies> else like that if you really want to, or PHP/ASP Haydies> Now, please, for suggesting there is any use what so ever in the world for Haydies> access, please take your self out back and shoot your self before the Haydies> problem gets any worse Sorry for the late reply, but I had to jump in after noticing this thread. The MySQL email lists is NOT a list where we flame other people for either the tools or databases they use! Instead we should try to be helpful to everyone and concentrate to solving problems! If a suggestion is not in your liking, then you should reply with a solution that you think would suite the original email better. If you want to compare products, then you should do it with verifiable facts, not with believes! If you say something is 'unusable for everything', then you have to accept that you are wrong if there is a single user that is happy with the product! About Access I can only say that it's very useful tool for a lot of people and there is a lot of places where it makes perfect sense to use Access. I don't claim it's a perfect reporting tool for all kind of reports, but depending on the requirements it may be the right tool for a specific problem/user combination. (I will not go into details as this is not important for this discussion; I am sure anyone with a little goodwill can come up with a scenario where Access would be a reasonable solution). I don't claim that Access is the right tool for the original poster (probably not as he is in this list). I just want that everyone that is trying to be helpful on this list should get the treatment and respect that he/she deserves. Respectfully, Monty -- Michael Widenius <[EMAIL PROTECTED]> MySQL AB, CTO Helsinki, Finland Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
add this to a wish list please.
Hi! > "Dathan" == Dathan Vance Pattishall <[EMAIL PROTECTED]> writes: Dathan> Sometimes I get this error on a slave... for unknown reasons Dathan> Error 'Incorrect key file for table: 'hasit'. Try to repair it' on query Dathan> 'REPLACE INTO hasit Dathan> If there is a wish list could the error report 'db.table'? Good idea, I will try to fix this in 4.0 ASAP Dathan> Also why doesn't mysql automatically fix the error itself when the error Dathan> has been encountered? It will, if you start mysqld with --myisam-recover[=option-list] (There is many ways to recover and we didn't want to do an automatic recover that may loose some records, without the user explicitely ask for it. Dathan> And if anyone can let me know why these errors occur even when the table Dathan> has been repaired within a month and the server has not ever been forced Dathan> down that would help a DBA OUT! To be able to answer that, we would need some way to repeat the problem. Please refer to the MySQL manual of how to make a repeatable test case by using the binary log. Note that we found and fixed one bug that could cause table corruption in MySQL 4.0.15 for dynamic size tables (with BLOB or varchars) under some conditions. Without more information, the only thing I can recommend you is to try out 4.0.15 (or newer). Regards, Monty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql-standard-4.1.0-alpha-pc-linux-i686.tar.gz changed since April 12 2003?
Hi! >>>>> "terence" == terence <[EMAIL PROTECTED]> writes: terence> Dear All, terence> Has the alpha release mysql-standard-4.1.0-alpha-pc-linux-i686.tar.gz terence> changed since April 12 2003? Just want to save myself a few hours terence> downloading... terence> I am putting it into production shortly, and was wondering too if there's terence> any news on the stable release of the same? terence> Thanks terence> Terence We are just now working on getting 4.0.15 out, which should happen any day now. After this is done, we will start doing release testing of 4.1.1, which can take anywhere between 2-6 weeks. (Just now things looks good, but one never knows what is going to show up) -- Michael Widenius <[EMAIL PROTECTED]> MySQL AB, CTO Helsinki, Finland Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
do long delete/update and view progress?
Hi! >>>>> "Bennett" == Bennett Haselton <[EMAIL PROTECTED]> writes: Bennett> Is there any way to do a long update/delete on a table such that you can Bennett> view the progress as the command runs, so that long before it's over, you Bennett> have some idea of what the total running time will be? Bennett> The way I did it was to write a perl script that takes the total range of Bennett> values for the primary key field in the table, divides that range into Bennett> chunks, and then runs the update/delete command on each chunk at a time, Bennett> printing out when it's done that chunk. But that's a bit of a kludge. Is Bennett> there a built-in way? For the moment this is not possible. We do have plans to add in MySQL 5.x a way for the server to give the client progress notifications for slow commands which could be used for progess bars etc, but this is still at least 3-5 months in the future. -- Michael Widenius <[EMAIL PROTECTED]> MySQL AB, CTO Helsinki, Finland Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
URGENT: Problems compiling mysql 3 and 4 on IRIX indy 6.5 IP22 mips
Hi! >>>>> "Jason" == Jason Buchanan <[EMAIL PROTECTED]> writes: Jason> hi, Jason> I'm having a hard time getting mysql 4 and mysql 3 to compile on IRIX 6.5... Jason> Does anyone have a solution to this? Jason> Below is an example of compiling mysql 4.0.13 (looks practically Jason> identical for mysql 3.23.56): Jason> g++ -DUNDEF_THREADS_HACK -I. -I. -I.. -I./../include -I../include -I./.. Jason> -I.. -I.. -O -DDBUG_OFF -D_BOOL -c -o mysql.o `test -f mysql.cc || Jason> echo './'`mysql.cc Jason> In file included from Jason> /usr/local/lib/gcc-lib/mips-sgi-irix6.5/2.95.3/include/curses.h:5, Jason> from mysql.cc:54: Jason> /usr/include/curses.h:112: syntax error before `,' Jason> /usr/include/curses.h:143: syntax error before `;' This problem is covered in the Irix section of the MySQL online manual. Please check it for details how to solve it. -- Michael Widenius <[EMAIL PROTECTED]> MySQL AB, CTO Helsinki, Finland Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
All Tables MyISAM and still InnoDB Datafiles?
Hi! >>>>> "Stephan" == Stephan Amann <[EMAIL PROTECTED]> writes: Stephan> Hi all! Stephan> I've downloaded the binary distribution Stephan> mysql-standard-4.0.9-gamma-sun-solaris2.9-sparc-64bit.tar.gz from Stephan> www.mysql.org. Stephan> I have a couple of databases on this installation, with all tables of Stephan> type MyISAM. However, on first start of mysqld the error log contains: Stephan> 030226 12:39:48 mysqld started Stephan> InnoDB: The first specified data file ./ibdata1 did not exist: Stephan> InnoDB: a new database to be created! Stephan> The InnoDB files are created in the standard data directory; I did not Stephan> configure any InnoDB Option, since we are not currently using InnoDB. Stephan> What's wrong? Why do I get InnoDB datafiles (ib_arch_log_00, Stephan> ib_logfile0, ib_logfile1, ibdata1) with only MyISAM Tables? In MySQL 4.0 InnoDB is included as standard. As MySQL can't by default know if you are going to use Innodb tables or not it creates on startup the necessary files so that you can use Innodb tables. These files will not be used if you aren't using InnoDB tables. If you are only using MyISAM tables and don't wont to have the above files (and save a bit of memory), you can start mysqld with the --skip-innodb option or put this into your my.cnf file. Regards, Monty -- MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyISAM rebuilding questions
Hi! >>>>> "Wynne" == Wynne Crisman <[EMAIL PROTECTED]> writes: Wynne> I am trying to determine what would cause my needing to rebuild an Wynne> MyISAM table in MySQL 4.0.x? You should not have to rebuild any tables just to upgrade to MySQL 4.0 from 3.23. The MyISAM format has not changed in any way between releases. Wynne> I also would like to know how I can minimize the time necessary to get Wynne> the MyISAM table up and running under MySQL 4.0.x? zero time for tables without fulltext. Some time for tables with fulltext indexes. Wynne> I have read the http://www.mysql.com/newsletter/2002-12/a91.html Wynne> document which states that tables with lots of data can be difficult to Wynne> rebuild and would be better served using InnoDB, but InnoDB does not Wynne> support fulltext searches. I don't know of any problems in rebuilding an MyISAM table: Just do: OPTIMIZE TABLE table_name; or ALTER TABLE table_name type=myisam; It's true that MySQL 4.0 has better fulltext capabilities and to get these activated for a table you need to rebuild the tables that has fulltext support. The old tables will however work as before even if you don't rebuild them (but in this case you will probably not be able to use boolan search on them until you have rebuilt them). Note that fulltext rebuilds are MUCH faster in 4.0 so it should not take long to do the rebuilds. Wynne> Thank you for any assistence, Wynne> Wynne Crisman Wynne> patternWare Systems, Inc. Regards, Monty -- MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
building MySQL on Alpha-Dec-OSF4 with gcc
Hi! >>>>> "Ralf" == Ralf Hupfer <[EMAIL PROTECTED]> writes: Ralf> Hi, Ralf> I do face problems building MySQL 3.22.32 with gcc 3.2.2 on an Alpha- Ralf> DEC-OSF4 workstation. MySQL 3.22 ? Ralf> CC="cc -pthread" CXX="cxx -pthread -O" ./configure --with-named-t Ralf> hread-libs="-lpthread -lmach -lexc -lc" --with-low-memory Ralf> Everything run's smoothly, till mysqld is to be build. The linker produces: Ralf> __cxa_pure_virtual Ralf> operator delete[](void*) Ralf> operator new[](unsigned long) Ralf> operator delete(void*) Ralf> operator new(unsigned long) Ralf> collect2: ld returned 1 exit status Ralf> gnumake[3]: *** [mysqld] Error 1 The above happens becasue gcc 3.2.x has the code for the operators new and delete in a external library that is not included by the linker if you link c++ code with gcc. Fixes: - Use cxx to link mysqld. - Use MySQL 4.0 or 3.23, both of which has a fix to enable you to link c++ code with 'gcc'. (This is done by declaring our own delete and new operators when you are compiling c++ code with gcc). The advantage of compiling c++ code with gcc instead of cxx are: - Smaller executable as there are no c++ libraries included. - No chance of getting exception handling into the c++ code, which usually doesn't work with threads. I saw a later email from you that you had not got 3.23 to compile. Please try 4.0 instead; MySQL 4.0 is know to compile without any problems on a lot of platforms and I am think that I have myself compiled MySQL 4.0 on DEC-OSF4 not that long ago. Regards, Monty -- MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
comment with "foreign key" text causes innodb CREATE TABLE failure
Hi! >>>>> "Brandon" == Brandon Bird <[EMAIL PROTECTED]> writes: Brandon> From: [EMAIL PROTECTED] Brandon> To: [EMAIL PROTECTED] Brandon> Subject: comment with "foreign key" text causes innodb CREATE TABLE Brandon> failure Brandon> Description: Brandon> If an innodb table is created with a comment with the words "foreign Brandon> key", the table creation fails. Just a short note about this. Currently the InnoDB table handler scans the CREATE statement and handles the foreign key definition infernally in InnoDB. In 4.1 MySQL will handle the parsing of the foreign key definition and will store the foreign key definitions in the normal table definition file. This will fix these kind of problems once and for all. Regards, Monty -- MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
High list latency?
Hi! >>>>> "Jeremy" == Jeremy Zawodny <[EMAIL PROTECTED]> writes: Jeremy> Is anyone else seeing it take 4-8 hours for mail to get thru this list Jeremy> recently? Normally emails should be delivered quickly; We do however have a problem in that if there is some problems with the Internet line for some hours then emails will 'pile up' and it will take many hours for them to be sorted out. We are looking into getting more bandwith for our email machine in the near future. Regards, Monty -- MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL 4.0.8 is released
Hi, MySQL 4.0.8, a new version of the world's most popular Open Source Database, has been released. It is now available in source and binary form for a number of platforms from our download pages at http://www.mysql.com/downloads/ and mirror sites. Note that all mirrors may not be up to date; If you can't find this version on some mirror, please retry later or try another mirror. This is a new gamma release, fixing some recently discovered bugs. If this release doesn't have any major problems, we will release 4.0.9 as a stable release. >From the ChangeLog: --- Functionality added or changed: * Default `max_packet_length' for libmysqld.c is now 1024*1024*1024. * One can now specify `max_allowed_packet' in a file ready by `mysql_options(MYSQL_READ_DEFAULT_FILE)'. for clients. * When sending a too big packet to the server with the not compressed protocol, the client now gets an error message instead of a lost connection. * We now send big queries/result rows in bigger hunks, which should give a small speed improvement. * Fixed some bugs with the compressed protocol for rows > 16M. Bugs fixed: * Fixed bug in `ALTER TABLE' with BDB tables. * Fixed core dump bug in `QUOTE()' function. * Fixed a bug in handling communication packets bigger than 16M. Unfortunately this required a protocol change; If you upgrade the server to 4.0.8 and above and have clients that uses packets >= 255*255*255 bytes (=16581375) you must also upgrade your clients to at least 4.0.8. If you don't upgrade, the clients will hang when sending a big packet. * Fixed bug when sending blobs longer than 16M to client. * Fixed bug in `GROUP BY' when used on BLOB column with `NULL' values. * Fixed a bug in handling `NULL's in `CASE' ... WHEN ... InnoDB changes: * InnoDB now supports also FOREIGN KEY (...) REFERENCES ...(...) [ON UPDATE CASCADE | ON UPDATE SET NULL | ON UPDATE RESTRICT | ON UPDATE NO ACTION]. * Tables and indexes now reserve 4 % less space in the tablespace. Also existing tables reserve less space. By upgrading to 4.0.8 you will see more free space in "InnoDB free" in SHOW TABLE STATUS. * Fixed bugs: updating the PRIMARY KEY of a row would generate a foreign key error on all FOREIGN KEYs which referenced secondary keys of the row to be updated. Also, if a referencing FOREIGN KEY constraint only referenced the first columns in an index, and there were more columns in that index, updating the additional columns generated a foreign key error. * Fixed a bug: if an index contains some column twice, and that column is updated, the table will become corrupt. From now on InnoDB prevents creation of such indexes. * Fixed a bug: removed superfluous error 149 and 150 printouts from the .err log when a locking SELECT caused a deadlock or a lock wait timeout. Regards, Monty -- MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com : - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
What does this mean? : Warning: thr_alarm queue is full
Hi! >>>>> "Chung" == Chung Ha-nyung <[EMAIL PROTECTED]> writes: Chung> query. Chung> I happened to find out that the lots of following error messages are Chung> stored Chung> error log file: Chung> Warning: thr_alarm queue is full Chung> What's this? Is it serious problem? The above is a very strange error; Basicly something that should never happen. It means that somehow you have more threads wanting to request for a alarm (used to timeout connections) than was registered when mysqld started. This should be impossible as we init the alarms with the following code in mysqld.cc init_thr_alarm(max_connections+max_insert_delayed_threads) In other words, we reserve as many alarms as we can have connections + insert delay threads; As a thread can only have one alarm and and no other threads than a connection or insert delay thread uses alarms I can't understand how you can get this error. Note that even if the alarm call fails, mysqld will work normally with the exception that a long lived connection will not time out. If you cando a new bug report, preferably with the 'mysqlbug' script, that includes the following information, I could try to dig deeper into this problem. Synopsis: Severity: <[ non-critical | serious | critical ] (one line)> Priority: <[ low | medium | high ] (one line)> Class: <[ sw-bug | doc-bug | change-request | support ] (one line)> Release: Operating system: Regards, Monty -- MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL Security Flaw - Dropped Databases
Hi! Mark> Hi, Gary> I'd like to add to the "security flaw" thread with my own experience. Gary> I have been hosting MySQL databases for over 2 years and on a few occasions Gary> have had user databases disappear. Gary> Last month one of my admin databases was dropped. The only user Gary> who has Gary> access to that database is root (me) and even after double checking all my Gary> scripts/code and database/table permissions I was unable to determine how it Gary> was done. I was able to track down the culprit and asked him how he did it. Gary> He replied: Gary>"When use MySQL-Front(version 2.5) as client to connect to 4.x version Gary> MySQL server,any users(even without any granted rights) can drop any Gary> databases. I guess there is a horrible security hole exist in MySQL 4.x Gary> version." Gary> I don't really understand this client side exploit, nevertheless, the Gary> database WAS dropped and that is how he told me he did it. Is this a red Gary> herring (false lead)? If it is true, is this exploit being addressed? I checked this up and notice that your user is right. In MySQL we have two different ways to drop a database: With 'DROP DATABASE database_name'. Through the depricated client function 'mysql_drop_db()'. The first case works correct but in the second case the grant check is not done. I tracked this down to a merge I did between the 4.0 and 4.1 code in September last year :( The reson you could not repeat this is that MySQLFront() uses the old mysql_drop_db() call while you probably tested this with some client which uses the new way to drop a database. I will fix this at once for next 4.0 release. Thanks for reporting this! Regards, Monty -- MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL AB has reached a settlement with NuSphere Corporation
Hi! MySQL AB is pleased to announce that it has settled its lawsuit with Progress Software Corporation and NuSphere Corporation. For more information, see the joint press release at http://www.mysql.com/press/release_2002_14.html Regards, Monty -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
What if binary log's suffix number exceeds 999?
Hi! >>>>> "Chung" == Chung Ha-nyung <[EMAIL PROTECTED]> writes: Chung> Dear, Chung> By default, binary log file uses 3-digit number suffix as its counter. Chung> Then, without reseting master logs suffix number may reach 999. If so, Chung> what happens? Chung> next suffix number is 1000 or 001? Otherwise error occurres? Next used suffix is 1000 and there should not be any problems when going from .999 to .1000 Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: What is relay-bin log?
Hi! >>>>> "Chung" == Chung Ha-nyung <[EMAIL PROTECTED]> writes: Chung> If all works correctly, can I delete replication logs by "RESET SLAVE" Chung> safely? Chung> I'd like to configure two servers with two-master scheme, each is the Chung> master of Chung> another. relay logs should automaticly be deleted and you should never have to do RESET SLAVE to delete them. RESET SLAVE should only be used when you want the slave to not be a part of the replication anymore. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: What is relay-bin log?
Hi! >>>>> "Chung" == Chung Ha-nyung <[EMAIL PROTECTED]> writes: Chung> It is seems that "RESET SLAVE" query sholud not be done while Chung> replication is working. Slave forget its replication position and cannot Chung> continue replication since it tries to do the queries already executed. Chung> Do I need to do "SLAVE STOP" before "RESET SLAVE"? Or any Chung> other means to delete replication logs during replication? Which log files is it that you want to delete ? (Please give an example for this) If you have a problem that old relay logs are not deleted, try to upgrade to MySQL 4.0.4 ; We recently fixed a bug in this code. If you want to delete all old binary log files, then the command is RESET MASTER. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Where's safe_mysqld?
Hi! >>>>> "Chung" == Chung Ha-nyung <[EMAIL PROTECTED]> writes: Chung> Dear, Chung> I installed MySQL 4.0.4 from source tarball and I found that Chung> "safe_mysqld" is Chung> mentioned here and there in the manual but I can't where it, Chung> safe_mysqld, is Chung> located. All that I found with `find . -name "*safe"*"' command is Chung> mysqld_safe Chung> script. If you read the 'Upgrading from 3.23' section in the MySQL manual, you will find that we renamed safe_mysqld to mysqld_safe in 4.0 The reason for this was that it's easier for the MySQL users if all MySQL commands starts with my... Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Retrieving Table Schema
Hi! >>>>> "Michael" == Michael J Fuhrman <[EMAIL PROTECTED]> writes: Michael> Hello All, Michael> Suggestions Feedback Requested: Michael> I'm looking for a solution that will not lock a table, yet allow me to Michael> retrieve the: Michael>field name, field type as integer, field size, and other attributes Michael>of each column from a table. The ways to do this are: - SELECT * from table_name where 0; - SHOW COLUMNS from table_name; - SHOW CREATE TABLE from table_name. Michael> The Issue: Michael> An important part of the test is to be able to retrieve the table's schema. Michael> I can't explain why without releasing proprietary corporate information, all Michael> I can say is that it's needed. What I can tell you is that when I retrieve Michael> it with a mixed case table name, it prevents me from deleting the table. We just fixed a bug in MySQL when using mixed table names. This is fixed in the upcoming 3.23.53 and 4.0.4 MySQL versions. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Retrieving Table Schema
Hi! > "Michael" == Michael J Fuhrman <[EMAIL PROTECTED]> writes: Michael> Hello All, Michael> Suggestions Feedback Requested: Michael> I'm looking for a solution that will not lock a table, yet allow me to Michael> retrieve the: Michael>field name, field type as integer, field size, and other attributes Michael>of each column from a table. The ways to do this are: - SELECT * from table_name where 0; - SHOW COLUMNS from table_name; - SHOW CREATE TABLE from table_name. Michael> The Issue: Michael> An important part of the test is to be able to retrieve the table's schema. Michael> I can't explain why without releasing proprietary corporate information, all Michael> I can say is that it's needed. What I can tell you is that when I retrieve Michael> it with a mixed case table name, it prevents me from deleting the table. We just fixed a bug in MySQL when using mixed table names. This is fixed in the upcoming 3.23.53 and 4.0.4 MySQL versions. Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
optimizer bug in selecting fields that don´t belong to the index used by mysql/innodb
Hi! >>>>> "rafarife" == rafarife <[EMAIL PROTECTED]> writes: rafarife> Description: rafarife> Hi Heikki, rafarife> I'm Lourdes and I work with Rafa in the MySQL project. rafarife> We have done what you have told us. We have run SHOW STATUS before rafarife> the query and we have obtained the next result: Handler_read_next = 0 rafarife> Handler_read_next = 1929 The above shows that MySQL had to read 1929 rows to satsify the query. rafarife> After doing this, we decided to create a new table like in your example with rafarife> the same records. Then, we execute the query: rafarife> The table definition is: rafarife> CREATE TABLE `clientes` ( In cases like this that are data dependent, it's not enough for us to have the create table definition, we also need the data itself. rafarife> How-To-Repeat: rafarife>Select Cod,Nom,Nif,Nombre_comercial from Clientes Where (Nom = 'GARCIA MANCILLA,S.L' And Cod>'061642') rafarife> Or Nom>'GARCIA MANCILLA,S.L' Order by Nom ASC, Cod ASC Limit 50; To be able to find and fix the problem, we would need a copy the tables involved used by your query. Can you please do the following: - Make a mysqldump of your tables and make a README file of the above. - Tar and gzip or zip the files. - ftp the file to ftp://www.tcx.se/pub/mysql/secrent - Send an email to [EMAIL PROTECTED] that you have uploaded a test case. If you can do the above, we will try to fix the problem for next MySQL release. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
optimizer bug in selecting fields that don´t belong to the index used by mysql
Hi! > "rafarife" == rafarife <[EMAIL PROTECTED]> writes: rafarife> Description: rafarife> Hello, rafarife> I am working with mysqld-max-nt under windows 2000 and unsing InnoDb rafarife> tables. rafarife> I have the following table: rafarife>I don´t understand why this select: rafarife>Select Cod,Nom,Nif,Nombre_comercial from Clientes Where (Nom = 'GARCIA MANCILLA,S.L' And Cod>'061642') rafarife> Or Nom>'GARCIA MANCILLA,S.L' Order by Nom ASC, Cod ASC Limit 50; rafarife>(Mysql uses the unique index: Nombre) What is the output from EXPLAIN for this query ? Do you get a similar problem with MyISAM tables ? Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
"BUG": 'strend' function in libmysql needs to be private
Hi! >>>>> "Ben" == Ben Goodwin <[EMAIL PROTECTED]> writes: Ben> There are probably other functions that need to be made private, too.. but Ben> this one's directly affecting me. Ben> I'm writing a shared library that loads libmysqlclient; When Solaris 'csh' Ben> uses said library, there's a clash between csh (and/or one of the libs it Ben> uses) and MySQL's 'strend' function. 'strend' will not return proper values Ben> under these circumstances, preventing me from even connecting to the server Ben> on intel, and, worse, core-dumping under sparc. Can you take a peek at Ben> this? I'll provide more info if necessary. strend() is a function we use a lot in the MySQL client code and is thus included in the libmysqlclient library. I can't see how csh could affect this in any way as csh doesn't have anything directly to do with shared libraries. (Except of course if you are trying to load a shared library inside csh).using a patched csh Which public library is it that has a conflicting function ? (I have used Solaris a lot and never seen this problem before) What is it that core dumps; csh or your application ? Does your application work if you are running 'sh' ? Ben> FWIW I renamed the function to _strend, recompiled/installed, and my Ben> problems went away... Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AES_DECRYPT(garabage, key) problem (4.0.4 bk snapshot)
Hi! >>>>> "Sergey" == Sergey S Kostyliov <[EMAIL PROTECTED]> writes: >> Description: Sergey> Server hang when trying to test field=AES_DECRYPT(filed, key) Sergey> in case when filed contain garbage. >> How-To-Repeat: Sergey> CREATE TABLE atest( Sergey> id int(11) NOT NULL auto_increment, Sergey> tmp text NOT NULL, Sergey> KEY id (id) Sergey> ) TYPE=MyISAM; Sergey> INSERT INTO atest VALUES (1, 'a545f661efdd1fb66fdee3aab79945bf'); Sergey> SELECT * FROM atest WHERE tmp=AES_DECRYPT(tmp,"password"); Thanks for the test case. This is now fixed in the upcoming 4.0.4 release. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: optimizer bug in selecting fields that don´t belong to the index used by mysql/innodb
Hi! > "Heikki" == Heikki Tuuri <[EMAIL PROTECTED]> writes: Heikki> Rafa, Heikki> OR's are generally difficult to optimize. Heikki, this is true if you are using OR on different keys. MySQL can fully optimise the query when using OR on different key parts of the same key (which is the case for the used query:) Select Cod,Nom,Nif,Nombre_comercial from Clientes Where (Nom = 'GARCIA MANCILLA,S.L' And Cod>'061642') Or Nom>'GARCIA MANCILLA,S.L' Order by Nom ASC, Cod ASC Limit 50; In this case MySQL should start scanning the table from the key [(GARCIA MANCILLA),(061642)[ and continue until it has found 50 rows. Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
optimizer bug in selecting fields that don´t belong to the index used by mysql/innodb
Hi! >>>>> "r" == rafarife <[EMAIL PROTECTED]> writes: r>And now, the explain selects: r> 1) Slow query r> explain Select Cod,Nom,Nif,Nombre_comercial from Clientes r> Where (Nom = 'GARCIA MANCILLA,S.L' And Cod>'061642') r> Or Nom>'GARCIA MANCILLA,S.L' Order by Nom ASC, Cod ASC Limit 50; r> tabletype possible keys keykey_len refrowsExtra r> - - ------ ---- r> Clientes index PRIMARY,Nombre Nombre 464039where used The above explains what happens. For some strange reason MySQL uses a full index scan above instead of a 'range' query. This really looks like a bug in the optimiser. To solve this we would however need a copy of your tables to be able to repeat this. Can you please ftp them to ftp://support.mysql.com/pub/mysql/secret so that we can try to fix ASAP ? Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL and Service Pack 3
Hi! > "Miguel" == Miguel Angel Solórzano <[EMAIL PROTECTED]> writes: >> Hello, >> >> I have a very strange problem with all of the Windows versions of MySQL. >> I upgraded to Service Pack 3 for Windows 2000 Server and now MySQL will >> not work period. Has anyone ran into this problem and or know of a fix? Miguel> Some users had reported before this issue, for someone the remove and Miguel> re-install the MySQL service had resolved the issue. If the above doesn't help, can you please run the mysqld-debug binary from a command window with the options: mysqld --debug --standalone in a separate windows. (Note that this will not run in the background) You can now try to connect to MySQL from another command window with 'mysql' If the above crashes or hangs, the file: c:\mysqld.trace should contain information of where it hangs. If this happens, please ftp this log and a readme file describing the problem to ftp://support.mysql.com/pub/mysql/secret and we will take a look at this. Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: LOAD DATA, \N, and auto_increment
Hi!
> "Benjamin" == Benjamin Pflugmann <[EMAIL PROTECTED]> writes:
Benjamin> How-to-repeat:
shell> cat >/tmp/in < 1
Benjamin> 2
Benjamin> \N
Benjamin> \N
Benjamin> 10
Benjamin> \N
Benjamin> EOF
shell> chmod a+r /tmp/in
mysql> CREATE TABLE ntest2 ( a INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY );
mysql> LOAD DATA INFILE '/tmp/in' INTO TABLE ntest2;
Benjamin> Query OK, 6 rows affected (0.00 sec)
Benjamin> Records: 6 Deleted: 0 Skipped: 0 Warnings: 3
mysql> SELECT * FROM ntest2;
Benjamin> ++
Benjamin> | a |
Benjamin> ++
Benjamin> | 1 |
Benjamin> | 2 |
Benjamin> | 3 |
Benjamin> | 4 |
Benjamin> | 10 |
Benjamin> | 11 |
Benjamin> ++
Benjamin> 6 rows in set (0.00 sec)
Benjamin> As said above, the shown output differs from the expected in the
Benjamin> "Warnings: 3" message. There should be 0 warnings.
Here is a patch for this (will be in 4.0.3)
((/my/mysql-4.0)) bk diffs -c sql/sql_load.cc
= sql/sql_load.cc 1.36 vs edited =
*** /tmp/sql_load.cc-1.36-13800 Tue Jul 23 18:31:17 2002
--- edited/sql/sql_load.cc Tue Aug 20 11:50:37 2002
***
*** 430,436
{
if (field->type() == FIELD_TYPE_TIMESTAMP)
((Field_timestamp*) field)->set_time();
! else
thd->cuted_fields++;
}
continue;
--- 430,436
{
if (field->type() == FIELD_TYPE_TIMESTAMP)
((Field_timestamp*) field)->set_time();
! else if (field != table->next_number_field)
thd->cuted_fields++;
}
continue;
Regards,
Monty
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Lost connections on Freebsd
Hi! >>>>> "Chris" == Chris Knipe <[EMAIL PROTECTED]> writes: Chris> Lo Victoria Chris> FreeBSD 4.6-STABLE (Master), MySQL 3.23.51 Chris> Windows 2000 Professional (Slave):, MySQL 3.23.49-nt Chris> I've recompiled the master about three times now, and it still crashed. Chris> Eventually, it came to such a extend that the moment I open port 3306 MySQL Chris> would crash. I can just use telnet 3306, or open a netcat on port Chris> 3306, and MySQL would die. Can you try to configure MYSQL with CFLAGS=-g and run the server under gdb. If we could get a full back trace where it fails, it could help us find out what's wrong. Chris> So far, the only way I found to keep the FreeBSD Port up, was to recompile Chris> it with debug support. I so far have 90 minutes uptime on the Master with Chris> debug support compiled in, but it's needless to say not really the desired Chris> result. How did you configure MySQL in this case ? Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL ERROR 1133 MEANING?
Hi! >>>>> "duo" == duo fu <[EMAIL PROTECTED]> writes: duo> Hi friends: duo> I try to grant privileges to a user named 'glen' but not succeed. Here is duo> the script: mysql> select * from user where User='glen'; duo> | localhost | glen |... mysql> grant all privileges on test.* to 'glen'@'localhost' identified by duo> 'tidbs1'; duo> ERROR 1133: Can't find any matching row in the user table I agree that the above is very strange. The above error means that MySQL could not find an entry with hostname=localhost and user='glen' in the privilege cache. Which MySQL version are you using ? Could you please check if the above command works after 'flush privileges'. If not, could you upload the mysql privilege tables (or a copy of the tables that shows this problem) to: ftp://support.mysql.com/pub/mysql/secret and we will try to repeat this. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Feature Request: --log-update to add 'drop table' after 'create temporarytable'
Hi! >>>>> "Daniel" == Daniel Kasak <[EMAIL PROTECTED]> writes: Daniel> Would it be possible to add a 'drop table' command when a connection Daniel> which has made a temporary table has been dropped, to make the logs Daniel> match up with what actually happens? Thanks! The later MySQL 3.23 and 4.0.3 version already logs drop table into the MySQL binary log. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem in deleting multi table
Hi! >>>>> "Minjie" == Minjie Qiu <[EMAIL PROTECTED]> writes: Minjie> Hello, Minjie> I'm working on a purge utility to purge 3 tables: call_record, Minjie> modem_call_rec and makrup. Those 3 tables are related with foreign key Minjie> recid, so I used the following sql to purge data that is older than 30 days: Minjie> delete call_record, modem_call_rec, markup from call_record, Minjie> modem_call_rec, markup where to_days(now())-to_days(call_record.setup)>=30, Minjie> and call_record.recid=modem_call_rec.recid and call_record.recid=markup.recid; Minjie> The result is, it works well with limited number of records. But when the Minjie> number of records increases, for example to 500 records per table, Minjie> call_record table is purged correctly, but the other two tables Minjie> (modem_call_rec and markup) are not purged at all. Minjie> The mysql manual mentioned that delete may not work when deleting tables Minjie> with correlated columns. Is that the reason why the above query does not work? No, this should not be the case here. I don't see any reason for why the above should not work. Can you produce a test case for this so that we can look into it? If yes, send it to ftp:://support.mysql.com/pub/mysql/secret and we will try to fix this. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
compiling 4.0.2
Hi! >>>>> "Marko" == Marko Djukic <[EMAIL PROTECTED]> writes: Marko> Anyone know what this syntax error is about? trying to compile the 4.0.2 on a freebsd server... Marko> Making all in sql Marko> gmake[2]: Entering directory `/usr/home/oblo/xfer/mysql-4.0.2/sql' Marko> byacc -d sql_yacc.yy && mv y.tab.c sql_yacc.cc Marko> byacc: e - line 75 of "sql_yacc.yy", syntax error Marko> %pure_parser/* We have threads */ Marko> ^ The reason for the above is that you are using byacc instead of 'bison'. Fix: Execute in the MySQL directory: cd sql bison -d sql_yacc.yy && mv y.tab.c sql_yacc.cc cd .. make all Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ERROR 1030: Got error 22 from table handler
Hi! >>>>> "grant" == grant <[EMAIL PROTECTED]> writes: >> Description: grant> when running a "insert data infile" command on a 500MB text file, I grant> get the error "ERROR 1030: Got error 22 from table handler" from grant> mysql. I did a listing of the database files and it looks like this: grant> -rw-rw 1 root root 2147397632 Jul 3 09:46 finddata.MYD grant> -rw-rw 1 root root 266409984 Jul 3 10:01 finddata.MYI grant> -rw-rw 1 root root8866 Jul 3 08:45 finddata.frm grant> I thought that Solaris 2.6 didn't have a problem with files over 2GB. >> How-To-Repeat: grant> Generate a large database, up to the 2GB limit. It should blow up on you. >> Fix: grant> unknown On Solaris 2.6 it depends on how you create the file system and how MySQL is compiled if you can create files bigger than 2G. You can verify the max file limit supported internally by MySQL by creating a file with MAX_ROWS=1 and then do a SHOW TABLE STATUS on the table. If the max data file length > 2G then the limit is on the file system, in which case you probably have to do re-format your disk to get it to support larger files. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Load problems with 3.23.51
Hi! > "Steven" == Steven Roussey <[EMAIL PROTECTED]> writes: Steven> I tried the skip-name-resolve and it had no effect. So there goes my Steven> hypothesis. Steven> Here are the results from test: Steven> Benchmark DBD suite: 2.14 Steven> Date of test:2002-06-24 11:19:19 Steven> Running tests on:Linux 2.4.16-0.13smp i686 Steven> Arguments: Steven> Comments: Steven> Limits from: Steven> Server version: MySQL 3.23.51 log Steven> TOTALS 2098.00 436.22 126.65 562.87 The above looks quite ok. On a Intel Xeon 2M cache, 4x700 Mhz, 2G, key_buffer=16M, gcc 3.1 machine I get: TOTALS 3399.00 664.19 179.00 843.19 (This is a newer benchmark version with some new tests, but still comparable) So at least there is nothing strange with the basic MySQL queries on the machine. Now there are two different possible problems: - A problem with the thread library that causes a problem when using many threads. - Some query / specific feature that you use that is slower like before. (For example a query that doesn't use indexes anymore). Could you by any change check by using the slow query log if there is some specific query that is causing problems ? Another option is to run the 3.23.51 server for a while and when you get a load problem do 'mysqladmin proc ext'. This command should show us if MySQL is using more table scans than usual. Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
[sql_big_tables option changes results when set for some queries]
Hi! >>>>> "rhuddleston" == rhuddleston <[EMAIL PROTECTED]> writes: >> Description: rhuddleston>The following query results in a different # of rows if the sql_big_tables is set to 1: rhuddleston> select count(incidents.ref_no) from incidents, labels where incidents.ref_no like '020531%' group by labels.label, incidents.ref_no; rhuddleston> The query seems unusual because it was a lot more complicated but I broke it down for better readability. rhuddleston> incidents.refno is [varchar(15) binary] eg '020531-31' rhuddleston> labels.label is [blob] eg 'Pricing' rhuddleston> Note: The results are only different if there is a large # of results from the query >> How-To-Repeat: rhuddleston> create two tables with the above attributes and of large size. Then run the query with sql_big_tables set to 1 and when it's not set. Sorry, but the above description is not good enough for us to be able to repeat the problem :( In general things like this is very data dependent and it's very hard to repeat bugs without a repeatable test case. Could out make a repeatable test case base on of our benchmark programs in the sql-bench directory ? (test-insert should be a good candidate for this). If you can do a test case, we will do our best to fix this in next MySQL release (if it's not already fixed). Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Argh! I am still getting the error...
Hi! >>>>> "Michael" == Michael Jessop <[EMAIL PROTECTED]> writes: Michael> _mysql_exceptions.OperationalError: (1114, "The table '#sql700_2_0' is full") Michael> ...I reinstalled MySQL with the Innodb backend, and configured it (per the nice example at mysql.org) to fit on my 10GB disk with 128 mb ram, I start the server with the --big-tables option and *I* still get this message. I can't believe it is true. The query performs a union of one small table (700 records) to the left join of the same small table to a large table of 700,000 records (to eliminate certain records). Criminy, Microsoft Access can handle this, so I have to believe the error lies somewhere other than MySQL itself. But can anyone please point me in the right direction?! This is a bit frustrating. Michael> insert into merged_works Michael> select works.* Michael> from works Michael> union all Michael> select worksets.* Michael> from worksets Michael> left join works Michael> on worksets.wrk_inst = works.wrk_inst Michael> where works.wrk_inst is null; The problem here is that UNION's doesn't use to the --big-tables option but assumes all result sets should fit into the in-memory temporary table. (This is fixed in the 4.0.2 source tree). Simple fix: Use 2 commands to do the insert. insert into merged_works select works.* from works; insert into merged_works select worksets.* from worksets left join works on worksets.wrk_inst = works.wrk_inst where works.wrk_inst is null; Another option is to enlarge the value 'tmp_table_size' to be big enough to hold the temporary set. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Load problems with 3.23.51
Hi! > "Jeremy" == Jeremy Zawodny <[EMAIL PROTECTED]> writes: Jeremy> On Sat, Jun 22, 2002 at 05:25:59PM -0700, Steven Roussey wrote: >> Hi all, >> >> I have MySQL 3.23.47 running on our sever. I skipped 48 through 50 and >> tried 51. No dice. It does not handle load, CPU and the load average go >> through the roof. I'm using Red Hat Linux 7.2 and the official mysql >> binaries. It appears to be slow to connect, causing 0.5 to 1.0 second >> delay on connection. Using persistent connections from PHP does not make >> much of a difference. I thought it might be the hostname lookup changes >> so I chose skip-grant-tables. This doesn't actually skip the hostname >> lookup though and had no effect. >> >> Most queries are shorter than 1 second so this problem causes >> catastrophic problems by making queries last a multiple times longer, >> which make the number of concurrent queries jump exponentially. This is >> a bad thing. And sadly makes 3.23.51 unusable. >> >> Does anyone else note these types of issues? Jeremy> As another data point for you, I've got 3.23.51 running on our master Jeremy> quite well. The difference is that I built it from source (to get a Jeremy> critical InnoDB patch). I don't recall which compiler the MySQL folks Jeremy> used (and which glibc), but my source build used Debian Woody's gcc Jeremy> 2.95.4. We are using gcc 2.95.3 and a patched glibc, the later one that we used in many builds before. This is the first email I got that 3.23.51 would be slow. Steven, could you try to run the MySQL benchmark suite on your machine and post me the results ? cd sql-bench perl run-all-tests --log The file I am interested in is the summary file named 'output/RUN-*' Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL dump/recovery probable bug
Hi! >>>>> "Georg" == Georg Richter <[EMAIL PROTECTED]> writes: Georg> On Friday, 21. June 2002 15:43, Stefano Incontri wrote: Georg> Hi, >> >> I found the following problem, which I consider a bug, but maybe I'm >> missing something to make it work correctly. Georg> This is not a bug. Why do you use a reserved word (LOAD) for an index name?! Georg> Rename it and everything should be ok. Georg> See also: http://www.mysql.com/doc/R/e/Reserved_words.html Another option is to use the mysqld option --quote-names. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re[2]: counting rows ...
Hi! > "Sinisa" == Sinisa Milivojevic <[EMAIL PROTECTED]> writes: Sinisa> Victoria Reznichenko writes: >> Hi! >> SM> Are you using our binaries ?? >> >> Yes. >> SM> If yes, send me a dump of the table and exact queries ... >> >> It's a strange... >> >> Seems, troubles occur when there is a fulltext index and the text >> column is NOT NULL. Besides, when I do CREATE .. SELECT, new table works fine ... >> The number of rows that appear in SELECT 8 FROM table_name doesn't >> depend on number of rows in the table, i.e. I have 2 table with 3 rows >> in each table: Sinisa> Thank you for your bug report, which helped us fix a bug. Sinisa> Here is a patch : The above patch works in this case but has some unwanted side effects in some special (not normal) cases. I have now fixed that MySQL 4.0.2 will have a better fix for this case. Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Spams on MySQL mailing lists
Hi! I was going through some of the old emails on the MySQL mailing list and noticed the many threads that discussed spams. (There seemed to be more emails discussing spams than true spams, but this is a separate issue). We here at MySQL AB do a lot of work to avoid spam and we are working on new better methods to do this. Previously Sasha was in charge of tuning the spam filters for the email list, but as he has lately been working full time on new replication features he has not had time to actively work on improving the current filter and keeping it up to date. We hope however to soon have a new person working on this to improve the quality of the MySQL public mailing lists. We have several times considering making the email lists subscribers only, but as I have posted previously, we believe this is not a good solution as it will make it much harder for new MySQL users to get help for their problems. (According from previous experience, a big part of the new MySQL users are not going to subscribe to a mailing list just to post a problem). My personal opinion is to go with the solution where a new, not subscribed poster would have to confirm his email the first time would be a good enough solution for us at this point. (Like on the PHP lists) Regards, Monty CTO of MySQL AB - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Fwd: A column of the SELECT-part of a query returns allways NULL in some cases.
Hi!
>>>>> "Sinisa" == Sinisa Milivojevic <[EMAIL PROTECTED]> writes:
Sinisa> Victoria Reznichenko writes:
>> Hi!
>> Sinisa, i tested the following example on 4.0.1 and got the same
>> result. If I don't use ORDER BY in the SELECT statement all worked
>> fine:
mysql> SELECT u.gender AS gender, count(DISTINCT u.id) AS dist_count, (count(DISTINCT
u.id)/5*100) AS percentage from users_table u, log_table l where l.user_id = u.id
GROUP BY u.gender order by percentage;
>> ++++
>> | gender | dist_count | percentage |
>> ++++
>> | NULL | 1 | 20.00 |
>> | NULL | 3 | 60.00 |
>> ++++
>> 2 rows in set (0.01 sec)
>>
Sinisa> Thank you for your bug report which helped us fix a bug. A fix will
Sinisa> appear in 4.0.2.
Sinisa> This is a temporary fix patch :
And here is the final one (I will push this in the 4.0 tree later today):
= sql/sql_select.cc 1.170 vs edited =
*** /tmp/sql_select.cc-1.170-8799 Sat Jun 8 20:02:47 2002
--- edited/sql/sql_select.ccSun Jun 9 15:13:44 2002
***
*** 3676,3682
if (blob_count == 0)
{
/* We need to ensure that first byte is not 0 for the delete link */
! if (hidden_null_count)
hidden_null_count++;
else
null_count++;
--- 3676,3682
if (blob_count == 0)
{
/* We need to ensure that first byte is not 0 for the delete link */
! if (param->hidden_field_count)
hidden_null_count++;
else
null_count++;
Regards,
Monty
--
For technical support contracts, goto https://order.mysql.com/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ www.mysql.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: MySQL 3.23.44 not using indexes
Hi! >>>>> "Luciano" == Luciano Barcaro <[EMAIL PROTECTED]> writes: Luciano> Hi Monty, Luciano> Michael Widenius wrote: >>> Hi! Jeremy> Is that supposed to happen in 4.0.x? I don't recall having seen a Jeremy> commit that affected the key cache yet. >>> Sanja has been trying to do improvements to the key cache code, but we >>> are still not satisfied with this. He just went back to the sub >>> select code, so we can't just now give a definite answer when we have >>> a new key cache. >>> >>> (I am myself totally concentrating on getting 4.0.2-beta out; When >>> this is done I hope to have time to take a look a the key cache problem) >>> Luciano> 4.0.2 will be labelled beta ? Yes; 4.0.1 has in practiced work very well for a lot of users. With 4.0.2-beta we are shifting new development (except bug fixes) to the 4.1 tree to ensure that 4.0 will stabilize quickly. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Grant Tables Creation
Hi! >>>>> "Glenn" == Glenn Hancock <[EMAIL PROTECTED]> writes: Glenn> I also have had trouble with the script that creates my user access tables. Glenn> I am assuming those tables should get created in the ./var/mysql folder. Glenn> Its my fault but I removed those tables just to see if they had anything to Glenn> do with me not being able to use the mysql database server. I figured it Glenn> was ok because I thought they had been created by the script but now they Glenn> are no longer their and subsequent runs of the script are not creating them. Glenn> Am I missing something? Yes, mysql_install_db should recreate everything. Please check that your MySQL server is down before running the script. If things still don't work, please check the hostname.err file in the data directory for the reson why things didn't work. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB, possible bug?
Hi! > "Heikki" == Heikki Tuuri <[EMAIL PROTECTED]> writes: Heikki> Andrei, Heikki> this is probably not a bug in InnoDB. In theory, 4000 random disk seeks Heikki> would use more time than scanning the whole table of 700 000 rows. Heikki> The optimizer was tuned .48 (not yet in 4.0.1) to favor index searches over Heikki> table scans. That may solve the problem here. Heikki> On the other hand, the fact that MySQL refuses to use the index specified in Heikki> the USE INDEX clause may be a bug. I have forwarded this email to MySQL Heikki> developers. USE INDEX ... only tells MySQL that it should only consider using one of the named index to resolve the query. MySQL is however still free to use a table scan if finds the given index not suitable for resolving the query. Heikki> Best regards, Heikki> Heikki Tuuri Heikki> Innobase Oy Heikki> --- Heikki> Order technical MySQL/InnoDB support at https://order.mysql.com/ Heikki> See http://www.innodb.com for the online manual and latest news on InnoDB Heikki> - Original Message - Heikki> From: "Andrei Cojocaru" <[EMAIL PROTECTED]> Heikki> To: "Heikki Tuuri" <[EMAIL PROTECTED]>; "Mysql List" Heikki> <[EMAIL PROTECTED]> Heikki> Sent: Wednesday, May 22, 2002 3:16 AM Heikki> Subject: Re: InnoDB, possible bug? >> I am using mysql 4.0.1-alpha on Linux 2.4.18, the info you requested is: >> select count(*) from newsentries10 where playerid=28575 and type=2; >> +--+ >> | count(*) | >> +--+ >> | 4218 | >> +--+ >> 1 row in set (13.81 sec) >> mysql> select count(*) from newsentries10 where playerid=28575 and type=2; >> +--+ >> | count(*) | >> +--+ >> | 3705 | >> +--+ Do you know why the result differs in this case ? mysql> explain select straight_join >> pn.timestamp,ne.viewpoint,pn.id,pn.type,ne.type,ne.newsid,ne.hidestamp Heikki> from >> newsentries10 ne, pnews pn where ne.playerid=28575 and ne.type=2 and >> pn.newsid=ne.newsid; Heikki> +---++---+-+-+---+-- >> --++ >> | table | type | possible_keys | key | key_len | ref | >> rows | Extra | >> Heikki> +---++---+-+-+---+-- >> --++ >> | ne| ALL| list_news,delete_news | NULL|NULL | NULL | >> 774878 | where used | >> | pn| eq_ref | PRIMARY | PRIMARY | 4 | ne.newsid | >> 1 || >> Heikki> +---++---+-+-+---+-- > show index from newsentries10; >> > > | newsentries10 | 1 | list_news |1 | playerid >> | >> > A >> > > | 0 | NULL | NULL | | >> > > | newsentries10 | 1 | list_news |2 | type Heikki, something is a bit strange here. In this case MySQL will ask the InnoDB table handler of how many rows matches the key range (ne.playerid,ne.type) [28575, 2] In this case, InnoDB should return about 4000 rows, but it appears that it returns 77 rows. "Andrei", could you upload a copy of the tables to ftp://support.mysql.com/pub/mysql/secret so that Heikki could test this ? Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL 3.23.44 not using indexes
Hi! >>>>> "Jeremy" == Jeremy Zawodny <[EMAIL PROTECTED]> writes: Jeremy> On Fri, May 17, 2002 at 11:41:49AM +0300, Michael Widenius wrote: Jeremy> [snip] Jeremy> That reminds me. A while back (9 months, maybe), I seem to remember Jeremy> you discussion a re-write of the key cache so that it would use a more Jeremy> locking strategy. Jeremy> Is that supposed to happen in 4.0.x? I don't recall having seen a Jeremy> commit that affected the key cache yet. Sanja has been trying to do improvements to the key cache code, but we are still not satisfied with this. He just went back to the sub select code, so we can't just now give a definite answer when we have a new key cache. (I am myself totally concentrating on getting 4.0.2-beta out; When this is done I hope to have time to take a look a the key cache problem) Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Possible Bug in UPdATE in MySQL 4.0.1 alpha
Hi! > "Michael" == Michael B Venezia <[EMAIL PROTECTED]> writes: >> Description: Michael>Possible Bug in UPDATE in MySQL 4.0.1 Michael> Attempting backtrace. You can use the following information to find out Michael> where mysqld died. If you see no messages after this, something went Michael> terribly wrong... Michael> Stack range sanity check OK, backtrace follows: Michael> 0x807db7f Michael> 0x823d64a Michael> 0x8204447 Michael> 0x821bbd6 Michael> 0x820bb41 Michael> 0x80d082f Michael> 0x80b0479 Michael> 0x8086de7 Michael> 0x808a262 Michael> 0x8084e57 Michael> 0x808a694 Michael> 0x8084296 Michael> Stack trace seems successful - bottom reached Michael> Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow Michael> instructions on how to resolve the stack trace. Resolved Michael> stack trace is much more helpful in diagnosing the problem, so please do Michael> resolve it Michael, could you please read the above instructions and try to resolve the backtrace for us? >> How-To-Repeat: Michael>Did this query on a database called 'medical' below text Michael>UPDATE `Physical Examination Report` SET `History of Present Michael> Illness`='moo\r\nfoo\r\nboo.' WHERE `ID Code of Appointment`=27 Any chance you could ftp a copy of the 'Physical Examination Report' table to ftp://support.mysql.com/pub/mysql/secret so that we could try to repeat the problem ? Just having the table definition formats is not enough to repeat a problem like this! Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
db name alias proxy
Hi! >>>>> "Grigory" == Grigory Kolesov <[EMAIL PROTECTED]> writes: Grigory> 1) Endiannes. I figured out that the size of packet is stored at the Grigory> beginning of packet as little endian unsigned int. Grigory> Is it so in all cases, or it just happens because both client and server Grigory> run on little endian machines? The packets are always sent as little endians, independent of the architecture of the server/client. Grigory> 2) Currently I rewrite only packets which contain: Grigory> +\x02+dbname Grigory> ++\x00+dbname (connection init) Grigory> +\x03+"\s*use\s*"+dbname. Grigory> I am not sure whether it is always correct. The above command will take care of the 'USE database' commands, but not be able to handle SQL commands where you directly refer to the database. Grigory> 3) Does anyone need such tool, should I post it on web somewhere? You could always add it to the MySQL portal section at: http://www.mysql.com/portal/software/html/index.html Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Bug in fulltext search in latest mysql-4.0.2 source
Hi! >>>>> "Vadim" == Vadim P <[EMAIL PROTECTED]> writes: Vadim> Sergei Golubchik wrote: >>> rebuild your indexes (the proper way to do it is to issue >>> REPAIR table_name USE_FRM Vadim> Sergei, would ALTER TABLE.. DROP INDEX.. ADD FULLTEXT... do the same trick? Yes, this would do the same thing as REPAIR, but a bit slower. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: RE: ECS Error with 4.0.07 opening DB (still) again -EMEA2490
Hi!
>>>>> "venu" == venu <[EMAIL PROTECTED]> writes:
venu> MySQL/MyODBC does accept connection timeout. Here is the code
venu> snippet from driver:
venu> case SQL_ATTR_CONNECTION_TIMEOUT:
venu> DBUG_RETURN(mysql_options(&dbc->mysql, MYSQL_OPT_CONNECT_TIMEOUT,
venu> (const char *)((SQLUINTEGER)ValuePtr)));
venu> break;
venu> So, driver does set correctly. Make a note that, the value '0' means
venu> default in ODBC, and that means no timedout.
Venu, the above code is slightly wrong:
To be 100 % correct, it should be:
case SQL_ATTR_CONNECTION_TIMEOUT:
{
uint timeout_argument= *(SQLUINTEGER*) ValuePtr;
DBUG_RETURN(mysql_options(&dbc->mysql, MYSQL_OPT_CONNECT_TIMEOUT,
(const char *) &timeout_argument));
}
on the other hand, the old code should work as SQL_UINTEGER should be
of type uint.
If the old code crashes, it means that ValuePtr is not pointing at a
valid uint address, in which case the bug is in the application or the
driver manager.
Regards,
Monty
--
For technical support contracts, goto https://order.mysql.com/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ www.mysql.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL 3.23.44 not using indexes
Hi!
>>>>> "Myk" == Myk Melez <[EMAIL PROTECTED]> writes:
Myk> Michael Widenius wrote:
>> You can check if the index are ok with CHECK TABLE; If this says the
>> index is ok, they are not corrupted.
>>
Myk> Apparently this doesn't work in all cases. "CHECK TABLE" told me
Myk> everything was fine, but dumping the data to a dump file via mysqldump
Myk> and then re-importing it into a new database fixed the problem, so there
Myk> must have been some undetected corruption in the database.
The only thing that a dump is that he index file gets distributed in a
different manner. This make affect the range optimizer in some range
cases.
Any chance you could give us access to the original 'not working' table
so that we can check this? CHECK TABLE should be able to find all
possible problems with the table; If not, I would like to know why it
failed.
>> The above shows that MySQL could use the index but decides to not use
>> this because it concludes that a table scan will be faster.
>>
>> To verify this can you send us the result for the following queries:
>>
>> SELECT count(*) FROM bugs WHERE bug_status IN ('NEW', 'ASSIGNED', 'REOPENED');
>> SELECT count(*) FROM bugs;
Myk> The results were about 25,000 and 131,000, respectively.
In a case like this (where so many rows are covered by the index) it's
not self evident that a table scan is not faster than an index scan.
Especially with many users using the key cache at the same time, a
table scan is likely to use less resources from the system.
What was the time difference in executing the query when MySQL used
the index and when it did the table scan ?
Regards,
Monty
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [BUG] CONCAT_WS + GROUP BY display problem
Hi!
> "Benjamin" == Benjamin Pflugmann <[EMAIL PROTECTED]> writes:
>> In order to get predictable result you need to use:
>>
>> select concat_ws('.',oct1,oct2,oct3,oct4) as IP, count(*) as c from
>> ips group by IP [having c > 1];
Benjamin> I have tested this and it doesn't fix the problem.
Benjamin> What follows is a complete test case and the output from running it on
Benjamin> my system. Please note, that it has the additional (but not necessary)
Benjamin> property that each group contains only one row, so there can not be
Benjamin> any ambiguity which values will be used.
Benjamin> SELECT VERSION();
Benjamin> DROP TABLE IF EXISTS group_by_bug;
Benjamin> CREATE TABLE group_by_bug ( number INT NOT NULL, alpha CHAR(6) NOT NULL );
Benjamin> INSERT INTO group_by_bug VALUES (1413006,'idlfmv'),
Benjamin> (1413065,'smpsfz'),(1413127,'sljrhx'),(1413304,'qerfnd');
Benjamin> /* like the original exmaple */
Benjamin> SELECT number, alpha, CONCAT_WS('<>',number,alpha) AS new
Benjamin> FROM group_by_bug GROUP BY number;
Thanks for the clear example.
Here is a patch for this (it will be in 3.23.51 and 4.0.2):
= sql/item_strfunc.cc 1.26 vs edited =
*** /tmp/item_strfunc.cc-1.26-31001 Tue Apr 2 17:42:01 2002
--- edited/sql/item_strfunc.cc Fri May 17 10:33:33 2002
***
*** 313,319
void Item_func_concat_ws::fix_length_and_dec()
{
! max_length=0;
for (uint i=0 ; i < arg_count ; i++)
max_length+=args[i]->max_length;
if (max_length > MAX_BLOB_WIDTH)
--- 313,319
void Item_func_concat_ws::fix_length_and_dec()
{
! max_length=separator->max_length*(arg_count-1);
for (uint i=0 ; i < arg_count ; i++)
max_length+=args[i]->max_length;
if (max_length > MAX_BLOB_WIDTH)
Regards,
Monty
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Slave crashes with SEGV on master shutdown
Hi! Going through my email backlog... Just a short update on this. >>>>> "Sasha" == Sasha Pachev <[EMAIL PROTECTED]> writes: Sasha> On Monday 29 April 2002 04:39 am, David Harper wrote: >> >Description: >> I'm running the pre-compiled Compaq Alpha (OSF1) version of MySQL 3.23.49 >> with master/slave replication. >> I built mysqld from the source code with the --with-debug option specified >> to the configure script. Then I duplicated the slave server crash and found >> that the problem is in the code which tried to re-connect to the master. >> Specifically, the SEGV fault occurs within call to gethostbyname_r. Here is >> the debugger traceback: >> #8 0x1202953e0 in __gethostbyname_r(0x2000199, 0x2f3b418, Sasha> 0x2f3b318, 0x2f3b418, 0x0, 0x1) in Sasha> At this point, I have two theories: Sasha> a) There is something wrong with your gethostbyname_r function We have been able to verify that this is the case and we will have a workaround for this in 3.23.51 and MYSQL 4.0.2 Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Solved (Re: 4.0.2 Replication still buggy...)
Hi! > "Sasha" == Sasha Pachev <[EMAIL PROTECTED]> writes: Sasha> On Wednesday 01 May 2002 11:34 pm, Jeremy Zawodny wrote: >> After even more tracking stuff down, I managed to isolate it to a >> change made in MySQL 4.0.1. ?The query in question was doing: >> >> ? INSERT INTO ... SELECT ... >> >> rather than >> >> ? INSERT IGNORE INTO ... SELECT ... >> >> MySQL 3.23.xx lets the first sneak by without errors even if there are >> duplicates. ?4.0.2 does not. ?It stops. ?Since our master is 3.23 and >> this particular slave is 4.0.2, the two didn't agree. Sasha> That would be a bug in 4.0.2. Can you provide a test case for it? Actually, this is not a bug. If you issue an INSERT INTO ... SELECT on 4.0.2, it should stop on errors. It was a bug that it didn't do this before. Fix: Always use INSERT IGNORE ... on the 3.23 master that you want to replicate. Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: problem with libmysqlclient_r.so
Hi! > "Andrey" == Andrey Kotrekhov <[EMAIL PROTECTED]> writes: Andrey> -LДобрый день.-A Andrey> Are mysql know about this!!! >> >> Sorry but if your OS doesn't support gethostbyname_r(), then >> mysql_real_connect() is not thread safe (I have just updated our >> documentation about this) Andrey> Thank you. I have now also patched the 3.23.51 source to ensure that gethostbyname() is called in a thread-safe manner. >> For example, have you called mysql_thread_init() before calling >> mysql_real_connect() in all your threads ? >> Andrey> Hmm... I use mysql_init() in each thread but: Try calling 'my_thread_init()' instead. Andrey> 8.4.4.4 my_thread_init() Andrey> . Andrey> This is automatically called by my_init() and mysql_connect(). Andrey> ~ The above should work if you all your threads calls the above functions. If you have a thread that calls mysql_xxx_query() directly, this will die if you don't call my_thread_init() first. Andrey> And in manual: Andrey> To get a threaded client where you can interrupt the client from other Andrey> threads and set timeouts when talking with the MySQL server, you should Andrey> use the -lmysys, -lstring, and -ldbug libraries... Andrey> -lstring - where can I find it? Andrey> Is it -lmystrings ? Yes; I have now updated the manual with this. Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: problem with libmysqlclient_r.so
Hi! > "Michael" == Michael Chang <[EMAIL PROTECTED]> writes: >> For example, have you called mysql_thread_init() before calling >> mysql_real_connect() in all your threads ? Michael> However, to my understanding, mysql_thread_init() is not needed if Michael> only one database connection is used, and that database connection Michael> is established when the program starts, before any threads have been Michael> created. Sorry, but this in not true. You have to call mysql_thread_init() for each thread that is not calling mysql_real_connect() This is needed to setup some thread local variables MySQL needs to make things threadsafe. Michael> That is, if you call mysql_real_connect() at the top Michael> of main(), for example, and you let the connection persist for the Michael> lifetime of the program, then mysql_thread_init() is not necessary, It is necessary Michael> and the only thing that you must worry about is to make sure that Michael> critical sections (like between mysql_real_query() and Michael> mysql_store_result()) are sandwiched in-between a mutex lock. Michael> If the above is incorrect, please let me know. Please read the manual section about how to make a thread safe client. This explains in detail what you have to do! Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: problem with libmysqlclient_r.so
Hi! >>>>> "Sinisa" == Sinisa Milivojevic <[EMAIL PROTECTED]> writes: >> I am not agree. I take _another program_ which work fine during ~2 year >> with previouse mysql version (mysql-3.23.44 for example). >> And I send dump from this program. >> >> Why I interested libmysqlclient_r? >> I have a program, which try to connect to mysql twice. >> But the second connection freeze in mysql_real_connect. >> I use libmysqlclient.so (mysql-3.23.42 & FreeBSD-4.4RC) >> I try to upgrade to mysql-3.23.49 and use mysqlclient_r on test >> computer and have many problems (I write about their before). >> But now I attach to working process which freese and see: >> (gdb) bt >> #0 0x18237970 in _thread_kern_sched () from /usr/lib/libc_r.so.4 >> #1 0x182382d2 in _thread_kern_sched_state () from /usr/lib/libc_r.so.4 >> #2 0x1823b791 in _thread_fd_lock_debug () from /usr/lib/libc_r.so.4 >> #3 0x1826df86 in _close () from /usr/lib/libc_r.so.4 >> #4 0x1823eb72 in _res_close () from /usr/lib/libc_r.so.4 >> #5 0x1823dc49 in res_send () from /usr/lib/libc_r.so.4 >> #6 0x182418ad in res_query () from /usr/lib/libc_r.so.4 >> #7 0x18241d88 in res_querydomain () from /usr/lib/libc_r.so.4 >> #8 0x18241aca in res_search () from /usr/lib/libc_r.so.4 >> #9 0x1824d692 in _gethostbydnsname () from /usr/lib/libc_r.so.4 >> #10 0x1824c14c in gethostbyname2 () from /usr/lib/libc_r.so.4 >> #11 0x1824c0c9 in gethostbyname () from /usr/lib/libc_r.so.4 >> ~~~ >> Under FreeBSD gethostbyname is not thread-safe >> And there is no thread-safe gethostbyname_r >> (function exist but not thread-safe) >> >> Are mysql know about this!!! Just a note about this problem: We will add a wrapper for gethostbyname() in 3.23.51 and the 4.0.2 that will fix things for OS where gethostbyname is not threadsafe. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Pre-release of MySQL 3.23.50
Hi! Trying to catch up with some old emails... >>>>> "Ireneusz" == Ireneusz Piasecki <[EMAIL PROTECTED]> writes: Ireneusz> Hi. I'm running on linux box 6.2 with glibc 2.1 and i compiled from src.rpm Ireneusz> mysql. Ireneusz> The compiler is gcc 2.95.2. Ireneusz> So, the new version of mysql 3.23.50 can i complie with it (2.95.2) or i Ireneusz> must upgrade to gcc 3.0.4 ??? You can use either gcc 2.95.x or gcc 3.0.x to compile MySQL. We are just trying out gcc 3.0.4 ourselves as we have to switch compiler from egcs 2.91.66 on our build machine because we want to use a newer glibc library. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL 3.23.44 not using indexes
Hi!
>>>>> "Myk" == Myk Melez <[EMAIL PROTECTED]> writes:
Myk> Steven Roussey wrote:
>> Are the tables defined the same on both servers?
>>
Myk> Yes, the tables are defined exactly the same.
>> Is the data the same?
>>
Myk> Just about. The data on the working server is a copy of the database on
Myk> the broken server. For previous tests I used a several-weeks-old copy
Myk> of the data, but for today's tests I re-copied the data over, so it
Myk> represents almost the same exact data set.
>> Is the query the same? Are both analyzed?
>>
Myk> Yes, I analyzed the tables on both databases before running the explain
Myk> queries, and the explain queries are exactly the same. The only
Myk> difference I can come up with is that the database on the working server
Myk> was dumped from the broken server via mysqldump and then imported into
Myk> the working server, while the database on the broken server was created
Myk> via table creation statements years ago and has since been updated from
Myk> the ISAM to the MyISAM table type and the database server from version
Myk> 3.22.30 to 3.23.44.
Myk> Is it possible that indexes were somehow corrupted in the process and
Myk> need to be rebuilt from scratch? Does it make sense to dump the data on
Myk> the broken server and re-import it into a different database on that server?
You can check if the index are ok with CHECK TABLE; If this says the
index is ok, they are not corrupted.
You can optimize the index layout by using OPTIMIZE TABLE.
But back to the original problem:
mysql> EXPLAIN SELECT bug_id FROM bugs WHERE bug_status IN ('NEW',
'ASSIGNED', 'REOPENED');
+---+--+---+--+-+--+++
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+---+--+---+--+-+--+++
| bugs | ALL | bug_status| NULL |NULL | NULL | 139425 | where
used |
+---+--+---+--+-+--+++
The above shows that MySQL could use the index but decides to not use
this because it concludes that a table scan will be faster.
To verify this can you send us the result for the following queries:
SELECT count(*) FROM bugs WHERE bug_status IN ('NEW', 'ASSIGNED', 'REOPENED');
SELECT count(*) FROM bugs;
If the first query returns more than 30 % of the second, then MySQL
works as expected in this case.
Regards,
Monty
--
For technical support contracts, goto https://order.mysql.com/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ www.mysql.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: checksum errors
Hi! >>>>> "Melvyn" == Melvyn Sopacua <[EMAIL PROTECTED]> writes: Melvyn> I don't get the checksum errors anymore - after I changed the table def. Melvyn> However - myisamchk is not doing a good job with the large keys (restored Melvyn> them by LOAD TABLE hardware FROM MASTER which produced the correct size), Melvyn> plus keys become corrupted when a replication packet is not received correctly, Melvyn> plus: mysql> SHOW VARIABLES LIKE '%ft%'; Melvyn> Empty set (0.07 sec) Can you create an example that shows that you how get a corrupted table when a replication packet fails ? This is something that should not be possible to happen. If failed packet doesn't have anything to do with a crashed table. On the other hand, you may got a crashed table on the slave and the replication stops because of this; This is however another issue. Melvyn> The myisamchck is certainly reproducable, as is the missing ft_ variables, Melvyn> resulting in no way to set ft_min_word_len. myisamchk in 4.0 supports the ft_min_word_len variable. If you change ft_min_word_len, you are probably better of with using REPAIR TABLE than myisamchk to repair a table. Melvyn> Should I re-report those bugs, so they are easier to process for you? Yes, if you can produce a full test case. Melvyn> Today I think I found something interesting: Melvyn> Some tables kept crashing, with incorrect keyfiles. Melvyn> Verifying the CREATE TABLE statement, there's was 1 correlation: Melvyn> The tables all had a second index on the primary key (for what reason? I don't Melvyn> know - legacy probably - I think the primary key, was dual-fielded at some Melvyn> point). I don't think this is the problem; There is no requirements inside MySQL to have the primary index as the first index. Melvyn> Combining that with the other table that kept crashing there seems to be a Melvyn> problem, Melvyn> with the situation, where one field is indexed more than one time. I would be really interested in having a test case that shows how to crash MySQL. Without a test case, I can't really give you any good advises of what could be the problem. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
BDB wrapped transaction id's
Hi! >>>>> "Robert" == Robert Cross <[EMAIL PROTECTED]> writes: Robert> Anyone seen a message like: Robert> 020509 0:05:30 bdb: txn_begin: transaction ID wrapped. Exit the database environment Robert> and restart the application as if application failure had occurred Robert> Now for the important followup question - what does this mean, and how do I stop it Robert> happening again? This means something went awfully wrong inside the bdb handler. Unfortunately the only way to get this fixed is if you can do a repeatable case where this happens on a clean database :( Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysqlimpor and fixed length files.
Hi! >>>>> "Ramon" == Ramon Arias <[EMAIL PROTECTED]> writes: Ramon> I haven't done the big file yet... I did a smaller one that was 50 gigs, and Ramon> it loaded fine after a few hours. (about 3) in a pc with tons of ram and Ramon> scsi stripped drives. However each index take about 3-5 hours to create. For Ramon> some reason it seems faster to add the indexes after importing the data. I Ramon> don't understand why MySQL wants to copy the entire database over every time Ramon> I add an index... It seems to me that it spends most of the time and Ramon> resources coping the file over and relatively little processing time Ramon> creating the actual index. Hmmm... I wonder whether there is a to streamline Ramon> this process. The ALTER TABLE in MySQL is very flexible as it has a lot of non-standard but useful options. One drawback is that currently it's always doing a total reconstructions of the data and index. We plan to fix this in 4.1 (Maybe earlier if someone would like to sponsor this project). In other words, if you plan to create a lot of INDEX, you should do this with a single ALTER TABLE command. If you do a LOAD DATA INFILE into an empty file, MySQL will delay not unique index creation until all data is loaded. To get more speed for index creation, you can set the myisam_sort_buffer_size variable to a high value. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql_real_query and escaping a semicolon
Hi!
> "Anna" == Anna Fowles-Winkler <[EMAIL PROTECTED]> writes:
Anna> I removed the semicolon from the end of the query string, and I still get the
Anna> same syntax error:
Anna> You have an error in your SQL syntax near ''.1' at line 10
Anna> This is what the query looks like:
Anna> INSERT INTO micromodels
Anna> (name, revision, dynamic, m_class, novars, equation, min_0, min_1,
Anna> min_2, min_3, min_4, min_5, min_6, min_7, min_8, max_0, max_1,
Anna> max_2, max_3, max_4, max_5, max_6, max_7, max_8, except_0,
Anna> except_1, except_2, except_3, except_4, except_5, except_6,
Anna> except_7, except_8, prompt_0, prompt_1, prompt_2, prompt_3, prompt_4,
Anna> prompt_5, prompt_6, prompt_7, prompt_8, allow_0, allow_1, allow_2,
Anna> allow_3, allow_4, allow_5, allow_6)
Anna>VALUES ('Eye Movement
Anna>
Time',1,NULL,'per',0,'.1;',0,0,0,0,0,0,0,0,0,1,1,1,1,1,1,1,1,1,-1,-1,-1,-1,-1,-1,-1,-1,-1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,0,0,0,0,1)
The MySQL server should not have any problems with the above query.
The ';' is something that is parsed by the command line client 'mysql'
and not something that the server really cares about.
Exactly how did you try to submit the above line ?
Can you show us the exact code where you stored the above query in a
string and sent it to the server ?
Please check with a debugger or by starting mysqld with --log and
examining the log file, that the server really got the above query!
Regards,
Monty
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to make the Win target from source?
Hi! > "Dennis" == Dennis George <[EMAIL PROTECTED]> writes: Dennis> I downloaded mysql-3.23.49-win-src.zip. Dennis> There are various files missing (e.g. Makefile) that would enable compiling with gcc. Dennis> Has anyone out there made mysql from source on Win platform Dennis> with gcc? I had been trying to compile my own client but was getting Dennis> a lot of undefines at link time - the instructions said that mysql Dennis> should be remade from source to solve this problem. I am stuck trying Dennis> to do that. If you want to use cygwin and gcc you should download the standard MySQL source distribution instead of the windows source distribution. You should however be aware of that the end result will not be as fast os as reliable as if you would use VC++, because MySQL is heavily dependent of a good thread implementation. Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How does DISTINCT really work ?
Hi! >>>>> "Fournier" == Fournier Jocelyn <[Presence-PC]" <[EMAIL PROTECTED]>> writes: Fournier> Hi, Fournier> So I assume in this case the only way to have distinct results is to use Fournier> "GROUP BY topic" clause ? If you do this, you can't still use 'ORDER BY date' as 'date' is not part of the GROUP BY columns. >> > mysql> SELECT DISTINCT topic FROM >> > forum.searchmainpresencepc4,forum.searchjoinpresencepc4 WHERE >> > searchmainpresencepc4.numreponse=searchjoinpresencepc4.numreponse AND >> > (mot='les') ORDER BY date DESC LIMIT 0,40; The main problem is what you try to do is not legal in SQL. What you probably want to do is the following: SELECT DISTINCT topic,max(date) as d FROM forum.searchmainpresencepc4,forum.searchjoinpresencepc4 WHERE searchmainpresencepc4.numreponse=searchjoinpresencepc4.numreponse AND (mot='les') group by topic ORDER BY d DESC LIMIT 0,40; And just ignore the d column from the result Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
column restrict:fraction < 10 ???
Hi!
> "ydbxmhc" == ydbxmhc writes:
ydbxmhc> Good day, everyone, and thank you for your time!
ydbxmhc> I am not a member of the mailing list (I had to swear them off as
ydbxmhc> addictive =o) but am posting this question in the hope that some
ydbxmhc> generous soul will assist me anyway.
ydbxmhc> Is there a way to
mysql> create table xmp (
-> this real(5,4)
-> );
ydbxmhc> Query OK, 0 rows affected (0.01 sec)
ydbxmhc> so that
mysql> desc xmp;
ydbxmhc> doesn't come back with
ydbxmhc> +---+-+
ydbxmhc> | this | double(6,4) |
ydbxmhc> +---+-+
There was a bug in MySQL where it accidently converted double(X+1,X)
t double(X+2,X) because MySQL thought that the first version was an
illegal type (as it was in MySQL 3.23)
(For example double(3,3) is an illegal type and is converted to
double(4,3))
Here is a patch that fixes this (This will be in 3.23.51 and 4.0.2)
((/my/mysql-3.23)) bk diffs -c sql/sql_parse.cc
= sql/sql_parse.cc 1.143 vs edited =
*** /tmp/sql_parse.cc-1.143-728 Tue Apr 16 16:29:04 2002
--- edited/sql/sql_parse.cc Fri Apr 26 13:44:05 2002
***
*** 2432,2440
uint sign_len=type_modifier & UNSIGNED_FLAG ? 0 : 1;
if (new_field->length && new_field->decimals &&
! new_field->length < new_field->decimals+2 &&
new_field->decimals != NOT_FIXED_DEC)
! new_field->length=new_field->decimals+2; /* purecov: inspected */
switch (type) {
case FIELD_TYPE_TINY:
--- 2432,2440
uint sign_len=type_modifier & UNSIGNED_FLAG ? 0 : 1;
if (new_field->length && new_field->decimals &&
! new_field->length < new_field->decimals+1 &&
new_field->decimals != NOT_FIXED_DEC)
! new_field->length=new_field->decimals+1; /* purecov: inspected */
switch (type) {
case FIELD_TYPE_TINY:
Regards,
Monty
PS: In MySQL 4.1 MySQL will also remember that you used real() instead
of reporting double()
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SQL Bugs/Missing INFO
Hi! >>>>> "Trent" == Trent Creekmore <[EMAIL PROTECTED]> writes: Trent> In the MySQL manual, the error codes are not available. This is a serious problem. I have an error that keeps coming up and no way to solve it. Trent> The error code is 1064. I believe for the syntax, but a THOROUGH check indicated there is NO error in syntax, so it must be a bug in the MySQL server. THis is not stricly needed as you can always get the full error text by using the mysql_error() call. The errors are defined in: Docs/mysqld_error.txt 1064 is the error given for syntax errors and ind I am not aware of any bugs that would cause this error. Are you 100 % sure that you don't have an syntax error or are using some feature that MySQL doesn't support in your SQL command ? Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: memory leaks in libmysqlclient.
Hi! > "domi" == domi <[EMAIL PROTECTED]> writes: domi> Hi ! domi> Thank You for the response ! domi> so I don't think it's solaris-problem. domi> Ofcource it can still be "my code" but I have not found domi> the problem... domi> So I took a chance and passed this question to list. domi> Maybe somebody else have had the same problem?? domi> Maybe libmysqlclient is not designed to be used in a domi> daemon process?? domi> It definitely is something wrong somewhere. domi> 40 - 50 MB in a week is not normal, or what You think ?? domi> I'will test hoard library asap, lets see what happens ! The MySQL client library has been tested under purify and we have not found any memory problems with it. One thing you can do is to recompile MySQL with --with-debug=full In this case the client library will include the safe_malloc library, which you can ask to report all memory used by the client library which has not been freed. (Just call the TERMINATE() function in your library from time to time). Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: problem with libmysqlclient_r.so
Hi! >>>>> "Andrey" == Andrey Kotrekhov <[EMAIL PROTECTED]> writes: Andrey> #12 0x180e7cdc in mysql_real_connect () Andrey>from /usr/local/mysql/lib/libmysqlclient.so.10 Andrey> #13 0x1812f93c in MysqlConnection::real_connect (this=0x80c9c18, Andrey> db=0x80fe8b0 "zzz", host=0x80fb040 "xxx.xxx.xxx", Andrey> user=0x80fe910 "xxx", passwd=0x80c26aa "", port=3306, compress=1, Andrey> connect_timeout=60, socket_name=0x80c26be "", client_flag=0) Andrey> at connection.cc:52 Andrey> #14 0x808384f in QueueModule::Run (this=0x80c9c00) at QModule.cc:166 Andrey> #15 0x8068a8b in start_Module (ptr=0x80c9c00) at Module.cc:23 Andrey> #16 0x181fce73 in _thread_start () from /usr/lib/libc_r.so.4 Andrey> #17 0x0 in ?? () Andrey> Under FreeBSD gethostbyname is not thread-safe Andrey> And there is no thread-safe gethostbyname_r Andrey> (function exist but not thread-safe) Andrey> Are mysql know about this!!! Sorry but if your OS doesn't support gethostbyname_r(), then mysql_real_connect() is not thread safe (I have just updated our documentation about this) Another issue is that if your client dies in the dbug_ code, this means that you have probably not followed the instructions in the manual section: 'How to Make a Threaded Client' For example, have you called mysql_thread_init() before calling mysql_real_connect() in all your threads ? Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Fw: user problem
Hi! >>>>> "kennonward" == kennonward <[EMAIL PROTECTED]> writes: kennonward> I have mysql installed on a linux platform. I have no problem running every thing as root. When I try to run mysql as a user other than root, I entered command use mysql. I get error 1044:access denied for user:@localhost to database 'mysql'. kennonward> is there a file that I need to change the permissions on or change the group in a var or usr subdirectory. This is described in detail in the 'Access denied' section in the MySQL manual. You have to do a GRANT command to tell MySQL which other users you want to give access to the MySQL database. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: table lock - which connection id own's it
Hi! >>>>> "Lopez" == Lopez David > writes: Lopez> If no row is returned, then the following happens: Lopez> 1) lock table ... Lopez> 2) select ... Lopez> 3) if no row is returned, Lopez> 4)insert ... Lopez> 5)use LAST_INSERT_ID() to get the value of the key Lopez> 6) else get the key-value pair Lopez> 7) unlock table Lopez> 8) put key-value pair in RAM hash Lopez> 9) insert row in main table (using new key-value pair) Why not instead do as follows: (This assumes you have an unique index on the thing you are selecting on) select ... if no row is returned, insert ... if insert works without a duplicate key error: use LAST_INSERT_ID() to get the value of the key else select to find the row some other thread inserted put key-value pair in RAM hash insert row in main table (using new key-value pair) Lopez> Hope that clears the insertion process on the hash tables. I'm Lopez> glad that if a connection is lost in steps 2-6, mysql would Lopez> automatically unlock the tables. That will satisfy the db Lopez> specification nicely. The architecture for our db came Lopez> from the optimization chapter in the manual. Thanks! If the connection dies on the client side, the server will notice it as once. The thing you will not notice if a client goes into an endless loop and will not release the connection to the MySQL server. I will add to our TODO to add to SHOW OPEN TABLES a list of threads that has locks on the table (or maybe a SHOW LOCKS command) to better cover this case in the future. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
table lock - which connection id own's it
Hi! >>>>> "Lopez" == Lopez David > writes: Lopez> AntiSpam - mysql, sql, query Lopez> Version: 3.23.49a, MyISAM, NT, Solaris Lopez> My app is 150 daemons writing logs to mysql. The main Lopez> table is of fixed size but depends on four other tables Lopez> for foreign keys (hash tables). These tables are uploaded Lopez> once to the daemon at start-up. Occasionally, a new entry Lopez> must be written into these hash tables. The procedure is Lopez> to lock the table, insert entry, get the key (auto-increment Lopez> field) and release the lock. A better solution is to use LAST_INSERT_ID() and not use any locks at all. Lopez> But what if the connection dies during the update process. Lopez> If this happens, how can I tell which connection id has the Lopez> lock so I can kill it? If a connection dies, the server will automaticly delete all temporary tables and all table locks. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Pre-release of MySQL 3.23.50
Hi! 3.23.50 is basicly just a bug fix release compare do 3.23.49a There is however two things one should be aware of regarding 3.23.50 (both only affecting our Linux x86 binaries (normal and RPM's): - We have switched to a new updated glib library, because we found a critical memory corruption bug (introduced by us) in the old glibc library that we used to compile 3.23.49. (older MySQL binaries are not affected by this problem) - We have changed compiler to gcc 3.0.4 (because the old gcc compiler we used couldn't compile with an alternative glibc library). As 3.23 is a stable release version, we don't want to release 3.23.50 to the general public right away, as we want to be sure that the new gcc version works in practice as good as it has worked on our internal machines. You can now find 3.23.50 at the following link: http://www.mysql.com/downloads/mysql-3.23-pre.html If we don't get any fatal bug reports by the end of the week for this version, we will then move it to our standard download page. If you find anything wrong/strange with this release compared to older 3.23 releases, please send a full bug report to [EMAIL PROTECTED] and we will fix it ASAP! Changes in release 3.23.50 -- * Fixed problem with `crash-me' and gcc 3.0.4. * Fixed that `@@unknown_variable' doesn't hang server. * Added `@@VERSION' as a synonym for `VERSION()'. * `SHOW VARIABLES LIKE 'xxx'' is now case insensitive. * Fixed timeout for `GET_LOCK()' on HPUX with DCE threads. * Fixed memory allocation bug in the glibc library used to build Linux binaries, which caused mysqld to die in 'free()'. * Fixed `SIGINT' and `SIGQUIT' problems in `mysql'. * Fixed bug in character table converts when used with big ( > 64K) strings. * `InnoDB' now retains foreign key constraints through `ALTER TABLE' and `CREATE/DROP INDEX'. * `InnoDB' now allows foreign key constraints to be added through the `ALTER TABLE' syntax. * `InnoDB' tables can now be set to automatically grow in size (autoextend). * Fixed some buffer overflow problems when reading startup parameters. * Because of problems on shutdown we have now disabled named pipes on windows by default. One can enable named pipes by starting mysqld with `--enable-named-pipe'. * Fixed bug when using `WHERE key_column = 'J' or key_column='j''. * Fixed core-dump bug when using `--log-bin' with `LOAD DATA INFILE' without an active database. * Fixed bug in `RENAME TABLE' when used with `lower_case_table_names=1' (default on Windows). * Fixed unlikely core-dump bug when using `DROP TABLE' on a table that was in use by a thread that also used queries on only temporary tables. * Fixed problem with `SHOW CREATE TABLE' and `PRIMARY KEY' when using 32 indexes. * Fixed that one can use `SET PASSWORD' for the anonymous user. * Fixed core-dump bug when reading client groups from option files using `mysql_options()'. * Memory leak (16 bytes per every *corrupted* table) closed. * Fixed binary builds to use `--enable-local-infile'. * Update source to work with new `bison' version. * Updated shell scripts to new agree with new POSIX standard. * Fixed bug where `DATE_FORMAT()' returned empty string when used with `GROUP BY'. Regards, Monty CTO of MySQL AB - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to speed up mods to large table?
Hi! >>>>> "Nick" == Nick Arnett <[EMAIL PROTECTED]> writes: Nick> I haven't been able to dig up any specific information about how to get Nick> maximum performance when making changes to large tables. I have a table Nick> that's close to 4 GB, which I'm altering to come up with the best trade-off Nick> between performance and speed. Dropping a column or an index seems Nick> exceedingly slow and I'm wondering what parameters matter for this. I'm Nick> already using the highest-performance drive I can lay my hands on. What Nick> else matters? MySQL currently almost always recreates the full index and data tables when you do an alter table (to be fixed in 4.1). To speed this up for MyISAM tables, you can set up the myisam_sort_buffer_size to a much larger value. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Syntax error with merge tables
Hi! >>>>> "Eric" == Eric Thelin <[EMAIL PROTECTED]> writes: Eric> On Sat, 20 Apr 2002 [EMAIL PROTECTED] wrote: >> I always get a syntax error when using INSERT_METHOD in creating merge >> tables. If I leave off the INSERT_METHOD part it works fine. I have >> tried this on many versions in the past and now on 3.23.49a. I am >> running on Mandrake Linux 8.1 but have seen this same error on other >> linux distros. It is my understanding from reading the manual that the >> following should work but it doesn't. >> CREATE TABLE mytable ( >> aINTEGER NOT NULL PRIMARY KEY, >> bCHAR(18) NOT NULL >> ) TYPE=MERGE UNION=(mytable0, mytable1, mytable2) INSERT_METHOD=LAST; >> >> Since the .MRG files are plain text could someone send me what the >> INSERT_METHOD part is supposed to look like so I can do it manually if >> needed. Thanks. The problem is that INSERT_METHOD only works in MySQL 4.0, not in 3.23 If you need this feature, then you should download the MySQL 4.0.1 distribution and try this out. The MySQL 4.0 series has been out a while now and has in practice proven to be be quite stable.. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
