Re: Strange row counter issues
Hello Lay, On 2/22/2012 07:05, Lay András wrote: Hi! I have a table: CREATE TABLE IF NOT EXISTS `test` ( `id` int(11) NOT NULL auto_increment, `cucc` varchar(255) character set utf8 NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `test` (`id`, `cucc`) VALUES (1, 'egyszer'), (2, 'ketszer'), (3, 'ketszer'), (4, 'haromszor'), (5, 'haromszor'), (6, 'haromszor'), (7, 'negyszer'), (8, 'negyszer'), (9, 'negyszer'), (10, 'negyszer'); select * from test; ++---+ | id | cucc | ++---+ | 1 | egyszer | | 2 | ketszer | | 3 | ketszer | | 4 | haromszor | | 5 | haromszor | | 6 | haromszor | | 7 | negyszer | | 8 | negyszer | | 9 | negyszer | | 10 | negyszer | ++---+ 10 rows in set (0.00 sec) Under 5.0.x version this query works good, the cnt column is right: set @row=0;select @row:=@row+1 as cnt,cucc,count(id)hany from test group by cucc order by hany desc; +--+---+--+ | cnt | cucc | hany | +--+---+--+ |1 | negyszer |4 | |2 | haromszor |3 | |3 | ketszer |2 | |4 | egyszer |1 | +--+---+--+ 4 rows in set (0.00 sec) Under 5.1.x or 5.5.x the cnt column is bad: set @row=0;select @row:=@row+1 as cnt,cucc,count(id)hany from test group by cucc order by hany desc; +--+---+--+ | cnt | cucc | hany | +--+---+--+ |7 | negyszer |4 | |4 | haromszor |3 | |2 | ketszer |2 | |1 | egyszer |1 | +--+---+--+ 4 rows in set (0.00 sec) Documentation ( http://dev.mysql.com/doc/refman/5.5/en/user-variables.html ) says this, so not a bug: As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed. The order of evaluation for expressions involving user variables is undefined and may change based on the elements contained within a given statement; in addition, this order is not guaranteed to be the same between releases of the MySQL Server. Is there any other solution to emulate row counter, which works with the above query under 5.1 and 5.5 mysql version? You need to materialize your sorted results before applying your row counter. You can use an automatic temp table or a manual temporary table. Here is one way to do this using an automatic temp table: set @row=0;select @row:=@row+1 as cnt,cucc, hany FROM (SELECT cucc,count(id) hany from test group by cucc order by hany desc) as stats; The other option is to create the row counter in your application, and not within the database. But, that all depends on how you need the data, too. Any way you go, you must first create your results, then assign them row numbers as we have changed (hopefully improved) the efficiency of how we evaluated the original query which is why your row numbering system no longer works as you expected it to. Regards, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Upgrade 5.0 - 5.1 - long table names with invalid chars.
On 2/15/2012 22:16, Bobb Crosbie wrote: Hi Folks, I'm preparing an upgrade of a 5.0.51a database to 5.1.58 (Ubuntu 11.10) - Some of the table names contain invalid characters which mysql_upgrade (mysqlcheck) is attempting to escape by renaming the filename. However I'm having trouble with some tables with long names. For instance if I had a table in some_db called: A table with a really long name - and some invalid characters Internally this will be converted to #mysql50#A table with a really long name - and some invalid characters but it will be truncated to 64 characters: #mysql50#A table with a really long name - and some invalid char and I will get errors such as: Failed to RENAME TABLE `#mysql50#A table with a really long name - and some invalid char` TO `A table with a really long name - and some invalid char` Error: Can't find file: './some_db/A table with a really long name - and some invalid char.frm' (errno: 2) Any ideas or suggestions ? I'd prefer to keep with the ubuntu version of mysql if possible. Would also like to minimize the size of the downtime window. Would it be best to just dump/drop/re-import the tables/databases ? Many Thanks, - bobb The simple solution is to rename your tables to a) use shorter names and b) not use any illegal characters Are those odd characters and long names really a requirement to your design or are they there just for developer's convenience? -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Reading Schema From a MSSQL Dump on a Mac (or Linux)
Hello Stan, On 2/3/2012 20:06, Stan Iverson wrote: On Fri, Feb 3, 2012 at 11:11 AM, Johan De Meersmanvegiv...@tuxera.bewrote: - Original Message - From: Stan Iversoniversons...@gmail.com I have a Mac and a Linux server and I need to read the table schema for a database dump from MSSQL. Possible? How? Heeh. If you're talking about an SQL dump into a textfile, that should be doable - you might have to mess with some datatypes or so, but not impossible. If this is a binary dump, you're going to have to import it into an MS SQL server, and proceed from there. MySQL connector for ODBC is one route you could take. Yes, it's a binary file; however, MySQL Connector for ODBC only works in Windows and I'm using a Mac. If no other solutions, will try tomorrow on a friend's PC. TIA, Stan Are you sure that it only works for PC? If that's true, why do we have installation instructions for Macs and other non-Windows systems in the manual? http://dev.mysql.com/doc/refman/5.1/en/connector-odbc-installation.html -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysql won't start with service, but starts with mysqld_safe
On 2/4/2012 19:57, Larry Martell wrote: Just installed mysql on centos 6.2. When I try to start it with service I get: #service mysqld start MySQL Daemon failed to start. Starting mysqld: [FAILED] Nothing at all is written to the error log. But if I start it with mysqld_safe it comes up and works fine. Anyone know what could be going on here? -larry If the daemon is attempting to change users during startup, then you must be root when you start it. Otherwise, become the user `mysql` then start the daemon (service) under the proper credentials. http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_user -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: trick trigger
Hello John, On 1/11/2012 11:16, John G. Heim wrote: I am working on an app to allow a committee to schedule classes. The members of the committee can all update the database by changing the time or the instructor for a class. I have to write an app to warn them when they've scheduled an instructor for 2 classes at the same time or if they've scheduled any of a large list of classes at the same time. For example, they shouldn't schedule Calculus 212 at the same time as Physics 302 because a student might want to take both classes. And obviously, they shouldn't schedule Professor Higgenbothom to teach both Calculus 212 and Probability 278 at 10:00 AM on Monday, Wednesday, and Friday. The problem isn't actually writing mysql to select the conflicts. The problem is when and how to run the code. I could put it in a trigger but say someone assigns Dr. Higgy to teach Calc 212 at 10 AM MWF. They need to be able to see that he is now scheduled for another class if they look at Probability 278. Get the problem? An update to one record can necessitate an update to any number of other records. I'm just looking for basic suggestions on how you'd deal with this. Should I attempt to write a trigger that updates both Calc 212 and Physics 302 when either is changed? Am I going to create an infinate loop? I am thinking of telling the committee that it can't be done and they'll have to wait for the list of conflicts to be recalculated by a background process once an hour or so. My current database structure is that there is a link table for conflicts. If Calc 212 is scheduled at the same time as Physics 302, that is shown by there being 2 records in a conflicts table. The conflicts table would contain a record with the primary key for Calc 212, the pkey for Physics 302, and a code indicating that its a course conflict. There'd also be a record for Physics 302 indicating that it has a conflict with Calc 212. If Prof. Higgy is also scheduled to tach Calc 212 and Probability 278 at the same time, that would also create 2 records in the conflicts table. Like this: calc212 | phys302 | course_conflict phys302 | calc212 | courseConflict calc212 | prob278 | instructorConflict prob278 | calc212 | instructorConflict Then my web app can do a select for conflicts when displaying Calc 212, Probabbility 278, or Physics 302. But how to get that data into the table? I'm thinking of trying to write a trigger so that wen a class record is updated, the trigger deletes the conflicts records for the class if the id appears in either column 1 or column 2, re-calculate conflicts, and re-add the conflicts records. But if anybody has basic suggestions for a completely different approach, I'd like to hear them. This is all a matter of GUI design and application logic. For example, you could force the user to wait for some kind of database error before realizing that the data they just entered was invalid or you can pre-select conflict lists from the database and block out certain times and people as 'already used' before they make their selections. This requires your application to check with the database at certain events. Let's say you want to schedule a class for Higgy to teach Calc 212, well there are at least two lists, from your description, that you need to know before allowing the user to pick a date and time: 1) the list of all classes that Higgy is already teaching 2) the list of any other classes that might interfere with Calc 212 Some additional lists may also be useful * Any other Calc 212 sections already scheduled for other professors * Any 'no classes here' schedule preferences for Higgy * The list of teaching areas that may be available/unavailable in which your Calc 212 may be taught. These all need to be added to the logic present at the time the scheduler wants to make their choices so that they can avoid many un-necessary trips to the database for every schedule they want to create. Another thing to do is to temporarily block (not with a database-level transaction) access to both Higgy and Calc 212 to minimize the chance of conflicting with the changes made to the database by someone else also trying to enter scheduling information. Summary : * Get as much data as you can get before the request leaves the user. This frees up the database to handle just the data changes as they need to happen. Conflicts can still exist (always assume someone else may steal the room, for example) and those may need to be resolved through a different process. * Keep the business logic in your application, leave the data integrity rules to the database. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Common Pattern for parent-child INSERTs?
Hello Jan,
On 1/7/2012 00:58, Jan Steinman wrote:
Okay, I'm seeking enlightenment here.
In my trivial case, one can save storage by not having a record extension for
people without phones. Big deal.
In my real-world case, I have a contacts database with your typical name, address, phone, email, etc. info.
Then I have extensions for people who are particular type of contacts that have more information than the
general case. If I have several thousand records in my contacts database, but only ten in the dairy
customers database, I'm saving a ton of storage by not having every single record in the
general-purpose contacts database contain stuff like desired_milk_pickup_day or SET
dairy_products_of_interest.
But now I have a different extension, Volunteers, with extra fields like special_skills,
dietary_restrictions, etc. I don't want those fields in the general contact list. And there's another
extension, Advisory, that holds extra information for contacts who are on our advisory council.
In normalizing databases, I was taught to do exactly what I've done, separate
out the special cases and put them in a separate table. But as you note, that
creates a bit of a mess for INSERT while simplifying SELECT.
ON UPDATE CLAUSE does not help on INSERT, does it? I mean, how does it know the
auto-increment value of the parent record before it's been INSERTed?
It appears that anything I do must be wrapped in a transaction, or there's the
chance (however unlikely) that something will get in between the INSERT of the
parent and that of the child.
Once you have inserted the 'parent' row (the one to the Contacts table)
you know the ID of the parent. This cannot change and no other contacts
will be given the same ID. You include this ID with the other INSERT
commands you need for your 'child' rows.
You do have two options to handle rollback scenarios:
1) run with only InnoDB tables and wrap all of the related INSERTs with
a single transaction
2) use any tables you like and keep track of the auto_increment values
issued for each row you are INSERTING in your application, too. This
allows you to implement a manual rollback in the event of some kind of
problem.
Take, for example, your Volunteers example. This requires at least two
rows: one main row on the `contacts` table and another on the
`volunteers` table.
INSERT `contacts` (name, ...) VALUES ('Bob the Volunteer',...);
SET @contact_id = LAST_INSERT_ID();
INSERT `volunteers` (contact_id, skill, diet_restrict, ...) VALUES
(@contact_id, 'carpentry', 'hates fish', ...);
SET @volunteer_id = LAST_INSERT_ID();
...
If you don't want to track the ID values in user variables, you can
query them and draw them back into application-based variables. If you
want to track lists of values, you can add them to temporary tables to
build each list or query them into application-side arrays. At this
point how you handle those numbers is up to you.
Remember, though, that LAST_INSERT_ID() can only return one value. This
means that you cannot use it for batch processing reliably unless you
manually lock the table and guarantee a specific sequence of numbers.
The other option is to build an association table of (id, name) based on
the newly-inserted data (or something similar based on some unique
combination of identifiers in your original data instead of just 'name'
) so that each of your child rows can be assigned their proper parent id
values.
http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: mysql_secure_installation
Hello Ryan, On 12/18/2011 15:36, Ryan Dewhurst wrote: Hi, Does anyone know why what's done in 'mysql_secure_installation' [0] isnot part of the default mysql installation? [0] http://dev.mysql.com/doc/refman/5.0/en/mysql-secure-installation.html Thank you,Ryan Dewhurst P.S. I also asked this question on the forums:http://forums.mysql.com/read.php?30,506069,506069#msg-506069 The script simply automates the steps documented in our manual, here: http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html If you want to see the script in action: * repeat a fresh install * enable the General Query Log * run the script. The General Query Log stores a copy of every command sent to a MySQL server before the server even parses it. It's there as a diagnostic tool and should not be enabled on a production machine unless there is a specific need to do so. The steps of 'mysql_secure_installation' are not performed by default because many people want to just get to know MySQL before putting it into full production. This is most easily performed (especially in a classroom setting) with an unsecured installation. Also the steps to secure the installation can be leveraged as an excellent teaching tool for: a) How MySQL accounts are authenticated b) Where the account information is stored c) The different levels of authentication supported by MySQL. For those who don't want to read or learn, or for those who simply want to automate their installation, there is the script. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: SQL DATA
Hello All, On 12/5/2011 14:20, wrote: A procedure MODIFIES SQL DATA if all the modifying involves a temporary table, or only READS SQL DATA? Does it matter whether the temporary table is meant to outlast the procedure-call? Many of you are missing the big picture. This flag (along with most of the others you can use to describe a stored procedure) are meant to indicate to the replication system whether it needs to log the CALL to this procedure and in which format. Statements that only collect data (and don't even write to temporary tables) do not need to be written into the binary log. Any stored procedures that change a table, even if it's a temporary table, need to be marked as MODIFIES_SQL_DATA. The other commenters are correct: The contents of the stored procedure are not evaluated to see if you set the descriptive flags correctly. The database must trust you, the DBA, to do that properly. http://dev.mysql.com/doc/refman/5.5/en/replication-sbr-rbr.html http://dev.mysql.com/doc/refman/5.5/en/replication-rbr-safe-unsafe.html Regards, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Loading of large amounts of data
Hello Machiel, On 12/6/2011 01:40, Machiel Richards - Gmail wrote: Good day all I have someone who has asked me the following, however due to not having that many years of experience in these type of volumes, I am posting this as I know that someone will probably be able to answer it better than me. (This will also give me a learning opportunity to see what to do) _*Client Question: *_ Well let me describe the issue. 1.I require to load records into a MySQL database table - no problem so far ;-) 2.The table represents stock that will be being searched and transacted (i.e. sold, which involves changing flags on the record) by a live system. 3.The stock table will be big --millions or tens of millions of rows 4.Stock is uniquely identified by two fields -- a supplier ID (numeric) and a serial number (varchar) 5.Transaction volumes may also be very high 6.Stock must be available to the system 24/7 7.I will need to replenish the stock table from a file, one or more times a day -- potentially loading tens or hundreds of thousands of rows each time 8.The DB will be a master-slave: reporting and recon files off the slave, transactions off the master (and presumably replenishment into master) I can go into a lot more detail about the process I am using (using an ETL tool called Talend) ... but the essential question is around strategies for doing this kind of dynamic loading: 1.How to insert data (high volumes) into the live table without locking it and affecting transaction performance (Insert low_priority?) 2.How to speed up inserts, even when there are two unique key constraints. My observation is obvious -- that inserts get slower and slower as the table grows (date based partitions of some kind maybe?). 3.General principles/ strategies in dealing with situations like this. Can someone please assist. I can't give you precise details but I can point you in the right directions. Your requirements are well-formed but they tend to contradict each other. While there are no ways to completely remove the contradictions, there are ways to minimize their impacts. #5 High transaction volumes #6 Available 24x7 #1,#7 Bulk updates of 1+ records daily These three are in conflict. Database changes require indexes to be rebuilt. Index rebuilds can be fast (for small changes or small indexes) or take a noticeable length of time (larger changes or larger indexes or both). This means you may need to have two systems you flip-flop into place to minimize your downtime. The same problem was solved by graphics card manufacturers by creating multiple frame buffers. You can make your 'unavailability' time as short as possible by updating a passive copy of the data while it is not being pointed to by your application front-end then swapping the 'updated' set of data for the 'old' set of data by either altering the virtual IPs of your sets of instances or by redirecting which set your applications are pulling data from. #8 System will be master-slave My flip-flop idea implies that your system will have two sets of master-slave(s) one carrying the 'current' data and one used to build the 'new' set of data (with the imports). This also implies that your 'active' set will need to be replicating to your 'passive' set to keep it in sync between bulk updates. #2a Many records need to change in a day #3 There will be millions of records #2b Searches need to be fast These conflict with each other too. The more records you add to a table, the longer any indexed lookup will take. If you can't use the data in memory in the index then a trip to the disk will be necessary to retrieve the columns for your query. Combine this with the number of queries at any one time and divide that by the maximum number of random-access reads a physical disk can achieve and you may easily exceed the capacity of any one disk storage system to supply. This implies that you need to look at how to divide your storage among several independent devices at the same time. Options abound: sharding, partitioning, simple configuration changes (some tablespaces on one device, some on others). Or, you can look at pricing solid-state disks for your storage neeeds. Factoring in need #4, this suggests that a partitioning scheme based on (supplier, serial#) may be a good first design choice. So... After discussing the pain points of each of your requirements I have the following mental image of a system: a) two sets of master-slaves. The master of the passive set will be a 'slave' to the master of the active set. b) data on each set is using InnoDB c) data partitioned on the stock table based on (serial#, supplier) - I chose that order because I think it will give a better random spread among the partition tables and because I think it will be much more common to ask 'which suppliers have part XXX' than it will be to say 'what are all the parts that supplier YYY has'. As always, take this advice
Re: [MySQL] innodb_file_per_table / apple workers and logic
Hello all, On 11/30/2011 16:46, Reindl Harald wrote: Am 30.11.2011 19:13, schrieb Karen Abgarian: Hi inline there. On 30.11.2011, at 0:16, Reindl Harald wrote: Most people do not expect a gas tank to shrink once the gas is consumed...right? WHO THE FUCK is comparing computers with a gas tank? Well, I do. I even managed to do it without using foul language. what answer do you expect comparing a database with a gas tank while the gas tank is the hard-drive? if i take some gas out of the tank (hard-drive) i expect that there is space for new one Actually, the gas tank is a good analogy. There is limited volume in a vehicle which must contain the tank. In this analogy, the vehicle must have space for not just fuel but passengers, cargo, engine, transmission, etc. The fact that the tank may grow so large it displaces other items from the vehicle is appropriate to the original situation (no room left on disk). There are a lot of things in this life to be upset about. Empty gas tanks is one thing. But I would not spill all that frustration on the very first person I meet on the net. my frustration is people like you comparing a database with a gas tank while not understand that the gas tank is the underlying hard-disk if you stop make laughable comparison you will not get back frustration I am sorry if you didn't see the larger picture she was trying to present. Taking the logical part of what was said above, there existed a database that possibly was able to save the space by using files_per_table. Does this somehow mean that there are no other databases in the world? have i said this? a default which makes it unable to free no longer used space is dumb not more and not less There are expenses to maintaining separate files per table that you do not have for the larger, more inclusive tablespaces. Individual tablespaces can become so numerous that your system may run out of file handles to operate them all, for example. All of those file names may clog your directory/folder system making it much slower to randomly access any one file, as another example. While it is true that recovering unused space may be useful to restore disk space, it is also true that allocating and deallocating disk space is an expensive action. It is much more efficient in the long run to leave an expanded file in its larger state than it would be to constantly be shrinking it to a minimal size. Also, since the user required that much space at least once before (by their query or usage patterns) it is reasonable to assume that the same working space (or more) will be used again in the future. So this puts the 'design decision' squarely on the side of 'always increase, never decrease' in order to minimize the disk allocation costs associated with operating the InnoDB storage engine. There are other storage options (MyISAM, Archive, CSV, etc) in the event this behavior of InnoDB is more than you want to deal with. The default to NOT use individual tablespaces is related to the need to potentially adjust OS-level limits to handle the additional file volumes. If that is not a problem for you and your admins, more power to you. For desktop users, however, that may not be an option they can use. So the default remains at 0 until the support for it becomes much more common among Linux user accounts. Regards, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: cope with deadlock
On 11/17/2011 01:41, 王科选 wrote: hi, From this url: http://dev.mysql.com/doc/refman/5.5/en/innodb-deadlocks.html , mysql says If you are using locking reads (|SELECT ... FOR UPDATE| http://dev.mysql.com/doc/refman/5.5/en/select.htmlor|SELECT ... LOCK IN SHARE MODE|), try using a lower isolation level such as|READ COMMITTED| http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html#isolevel_read-committed. What's the reason? I have read some online material, but still don't get the point, can anyone explain why? The reason behind using less locking has to do with the reason for deadlocks in the first place. A deadlock happens when two consumers of a resource need access to the parts of that resource that the other consumer controls. An absurdly simple example of a deadlock: * There is a table of 1000 records * User A starts updating the table in incrementing order (1, 2, 3, ...) * User B starts updating the table in descending order (1000, 999, 998, ...) * The two transactions meet somewhere in the middle of the table. Because neither A nor B could complete its sequence of changes without access to the rows controlled by the other transaction, we have achieved a deadlock. One of the transactions will be rolled back to allow the other to continue. Deadlocking cannot be eliminated from any system that shares resources in a random-access method among multiple users. There are, however, many ways to reduce deadlocking: * Always access your resources in the same sequence. This means both table sequence and row sequence per table. * Only lock those resources that you absolutely need for your transaction. The fewer things you need to lock, the less likely it will be that another session will need to use them too. * Keep your locks for the least time possible. By reducing the duration of your locks, you are also reducing the chances that another session will need to use those resources at the same time you are using them. * Use shared locks instead of exclusive locks whenever possible. When it comes to transaction isolation in InnoDB, the less isolation you require, the more likely you are to generate a shared lock vs an exclusive lock. Regards, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Issue With Subqueries
Hi Mike, On 11/8/2011 20:46, Mike Seda wrote: All, Can anyone out there explain the result of the third statement provided below: mysql select count(distinct field1) from db1.table1; ++ | count(distinct field1) | ++ | 1063 | ++ 1 row in set (0.01 sec) mysql select count(distinct field1) from db2.table1; ++ | count(distinct field1) | ++ | 3516 | ++ 1 row in set (0.03 sec) mysql select count(distinct field1) from db2.table1 where field1 not in (select field1 from db1.table1); ++ | count(distinct field1) | ++ | 0 | ++ 1 row in set (0.08 sec) A colleague of mine is stating that the result should be much greater than 0. Please let me know what you think. Thanks In Advance, Mike Simple math (set theory) suggests that all of the values of field1 on db2.table1 contain only copies or duplicates of the field1 values in the rows in db1.table1. Try this: SELECT db2.field1, db1.field1 FROM db2.table1 LEFT JOIN db1.table1 ON db2.field1 = db1.field1 WHERE db1.field1 IS NULL; How many rows do you get back from that? -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Removing Double Quotes
On 11/3/2011 02:29, Adarsh Sharma wrote: Dear all, I need to remove the quotes in text columns in a mysql table. It has more than 5000 rows. In some rows there is values quoted with . I want to remove them. Below is the snapshot : *ID /URL Country Publication / Description ...and so on* 2474 http://www.times-standard.com/ United States Times-Standard California 1 2009-10-22 10:34:39 T F 7546609 0 T F T T T 2475 http://www.argentinastar.com/ Argentina Argentina Star 1 -00-00 00:00:00 0 0 0 0 2476 http://www.economist.com/countries/argentina/; Argentina Economist The site gives research tools to search articles by subjects and backgrounds. DD in the date tag is written in the following format : 3rd for 3 2 -00-00 00:00:00 0 0 0 N 0 2477 http://www.ambito.com/english/; Argentina Ambito.Com The date tage show on the right side main page 0 -00-00 00:00:00 T 0 0 0 N 0 2570 http://en.apa.az/; Apa 1 -00-00 00:00:00 0 0 0 0 2571 http://www.theazeritimes.com/; Caspian The Azeri Times last update on 18 Feb 2011 1 -00-00 00:00:00 0 0 0 0 Please let me know the efficient easiest way to remove it. Thanks The manual is your friend, please don't be afraid to use it. I believe the function you are looking for is REPLACE(). http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_replace -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What is wrong with this outer join?
On 10/19/2011 20:03, Dotan Cohen wrote:
...
Thank you Shawn. I very much appreciate your help, and I also
appreciate your employer's initiative to have such a position
monitoring the mailing list. Is that an Oracle-created position, or
did it exist at Sun as well?
MySQL has always encouraged its employees (developers, support,
documentation, marketing, ... anyone) to listen to and keep up with the
community feedback channels. This philosophy has existed since the
beginning of the lists and forums.
If I'm already talking with the MySQL Principal Technical Support
Engineer then I have to suggest that the MySQL manual include more
example code. I'm a read-the-manual kind of guy and the C# / PHP
manuals are usually enough to get me unstuck. The MySQL and Java (only
mentioned as it is another Sun/Oracle product) manuals usually do not
provide code examples and I must google for them from unreliable blogs
and forum postings. I personally find concise code examples much more
intuitive and informative than full-format [{(someOption |
anotherOption), somethingHere} rarelyUsedFeature] which I might or
might not mentally parse. I can gladly make more specific suggestions
if Oracle sees the idea as actionable.
We do! First though, are you referencing the online documentation or the
packaged documentation? The reason I ask is that the online
documentation does have some user contributions and comments to go along
with the text itself. That outside content is not included with the
packaged documentation.
I mention this as constructive criticism, take no offense! I'm only at
the beginning of my career and I don't claim to have the expertise or
experience to tell Oracle how to run their show, I only voice my
concern as a consumer of the product and one with an interest in
keeping the product and technology viable. I have nothing but
appreciation to Oracle for continuing to develop Java, MySQL and for
having the good sense to pass OOo onto the Apache foundation.
No offense taken. We are always trying to keep MySQL easy to install,
easy to operate, and easy to learn. All feedback is valid.
Regards,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: ERROR 1250 (42000): rejected view
Hello Hal�sz S�ndor, On 10/19/2011 17:50, wrote: I made this query a view, called MEMBERP, no problem: SELECT MemberID, ereStart, DateModified, MembershipExpires, MemberSince, Category, Boardster, GROUP_CONCAT(DISTINCT Salutation ORDER BY Rank) AS Salutation, GROUP_CONCAT(DISTINCT GivenName ORDER BY Rank) AS GivenName, GROUP_CONCAT(DISTINCT Surname ORDER BY Rank) AS Surname, Street, City, State, Zip, HomePhone, Comments, GROUP_CONCAT(DISTINCT WorkPhone ORDER BY Rank) AS WorkPhone, GROUP_CONCAT(DISTINCT CellPhone ORDER BY Rank) AS CellPhone, GROUP_CONCAT(DISTINCT Email ORDER BY Rank) AS eMail, MAX(Volunteer) AS Volunteer, MAX(ReceivesFlyer) AS ReceivesFlyer, Houmuch, Wherat FROM Nam RIGHT JOIN Address USING (MemberID) LEFT JOIN Paid USING (MemberID) GROUP BY MemberID ORDER BY Surname, GivenName There are tables Nam and Address, and Paid is a view. But when I try to use it for a table, there is trouble: mysql select * from memberp; ERROR 1250 (42000): Table 'nam' from one of the SELECTs cannot be used in field list If I leave this, GROUP_CONCAT(DISTINCT GivenName ORDER BY Rank) AS, out, there is no trouble. If I leave out any of the other like phrases, there is yet trouble. If I leave the ORDER BY ... out, there is no trouble. Only GivenName is derived from GROUP_CONCAT and is also a lesser field for ordering by. Why is that a problem? Perhaps it is the USING clause that is messing you up. The USING() clause needs to pick one source for MemberID and you appear to have two. Try converting to a more explicit ON clause instead. Also you are mixing LEFT and RIGHT joins in the same query. While technically not wrong, it's also not 'good form'. I suggest you alter the sequence of your table definitions to use either all LEFT or all RIGHT like this: FROM Address LEFT JOIN Nam ON ... LEFT JOIN Paid ON ... Regards, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What is wrong with this outer join?
Hello Dotan,
On 10/19/2011 09:57, Dotan Cohen wrote:
mysql select * from beers;
++---++
| ID | name | colour |
++---++
| 1 | carlsburg | 2 |
| 2 | tuburg| 1 |
| 3 | tuburg| 9 |
++---++
3 rows in set (0.00 sec)
mysql select * from colours;
+++
| id | colour |
+++
| 1 | red|
| 2 | green |
| 3 | blue |
+++
3 rows in set (0.00 sec)
mysql select * from beers inner join colours on beers.colour = colours.ID;
++---++++
| ID | name | colour | id | colour |
++---++++
| 1 | carlsburg | 2 | 2 | green |
| 2 | tuburg| 1 | 1 | red|
++---++++
2 rows in set (0.00 sec)
mysql select * from beers outer join colours on beers.colour = colours.ID;
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'outer join colours on beers.colour = colours.ID'
at line 1
So I've gone looking the fine manual, here:
http://dev.mysql.com/doc/refman/5.6/en/join.html
The manual references natural outer joins and requires curly brackets
and I'm frankly not making sense of it. Left, right, and inner joins
work as I expect them too, and fishing for examples in google doesn't
find anything unusual. How exactly am I erring?
Thanks!
This is a simple misunderstanding. From the page you quote, the syntax
patterns for an OUTER join are these:
| table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference
join_condition
| table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor
Notice that in the second, the [OUTER] is nested inside of [{LEFT|RIGHT}
[OUTER]] and in the first it follows the NON-OPTIONAL choice of
{LEFT|RIGHT). Neither one of these syntax patterns allows the keyword
OUTER to appear without either the LEFT or RIGHT keyword before it.
To make this crystal clear those patterns allow LEFT JOIN, RIGHT JOIN,
LEFT OUTER JOIN, or RIGHT OUTER JOIN but not just OUTER JOIN.
Regards,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What is wrong with this outer join?
On 10/19/2011 13:19, Dotan Cohen wrote: ... Thank you Shawn! I see that I am getting support right from the top! So far as I understand, an outer join should return all matched and unmatched rows (essentially all rows) from both tables. So it is not clear to me what is the difference between a right outer join and a left outer join, and how they differ from a regular outer join. But don't answer that, I'll google it and post back for the fine archives. What you are describing is a FULL OUTER JOIN. This is not supported, yet, in MySQL. We only support INNER, NATURAL, LEFT, and RIGHT. To simulate a FULL OUTER JOIN, you need to construct a UNION of a LEFT and a RIGHT like this: ( SELECT ... FROM basetable LEFT JOIN jointable ON basetable.PKID = jointable.base_id ) UNION ALL( SELECT ... FROM basetable RIGHT JOIN JOINtable ON basetable.PKID = jointable.base_id ... WHERE basetable.PKID is NULL ... ) The first half of the UNION finds all rows in basetable plus any rows where the jointable matches. The second half identifies only rows in jointable that have no match with a row in basetable. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: credit where due
On 10/19/2011 13:29, Michael Dykman wrote: While we have him online, I think we could all take a moment and be grateful for the contributions of Shawn Green. When I see the Oracle-bashing on this list, I am often reminded that we still have a hard-core MySQL developer who has survived the ride to Sun and again to Oracle who is still providing us with timely expert advice. Please, all of you, think twice before cutting up Oracle for their lack of MySQL support. Shawn has been plying this list forever doling out sound advice and I have never heard him complain as we as we indirectly besmirch him over and and over. Thank you Shawn. I am very humbled and honored. Thank you very kindly. We, the old teams from MySQL, have fought very hard to maintain our identities as the product itself has changed ownership. Some battles we won, some we did not. Our support and development teams are still predominately intact since before the Sun acquisition. We have had to learn how to use some new tools and adjust to different corporate philosophies but our dedication to our customers or the quality of the product has never waned. Certainly there have needed to be adjustments along the way. Some of them were painful (the loss of our old friend Eventum still haunts us today) some were easy (like having essentially one tier of support services for all customers). I don't get as much time to spend monitoring this list as I used to. I contributed to this list before I worked for MySQL and I will continue being part of this community as long as MySQL is part of my life. If you would like to join me and become part of the MySQL team in any capacity, we are always scouting for new talent in all geographical areas. Please do not post any responses or resumes to this list or send them to my personal account. I can no longer accept them (it's one of those policy changes I mentioned earlier). Instead please look for a listing that you may be interested in at http://www.oracle.com/us/corporate/careers/index.html and start the process there. Again, thank you very much. Humbly yours, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [MySql] CONNECT
On 10/13/2011 14:41, Grega Leskovšek wrote: What is the usage of connect keyword? I've tried to google what is the difference between CONNECT and USE but got no explanation. It seems everybody use USE, but am not clear why is the CONNECT used? mysql CONNECT test1pizza Connection id:9 Current database: test1pizza mysql use test1pizza Database changed Please help me ... Thanks in advance, Grega Leskovšek ♥♥♥ When the sun rises I receive and when it sets I forgive! ♥♥♥ ˜♥ - http://moj.skavt.net/gleskovs/- ♥ Always, Grega Leskovšek That's a client-side command for the mysql command line interface, not a MySQL-side command. You may have been looking in the wrong place: quoting http://dev.mysql.com/doc/refman/5.5/en/mysql-commands.html connect [db_name host_name]], \r [db_name host_name]] Reconnect to the server. The optional database name and host name arguments may be given to specify the default database or the host where the server is running. If omitted, the current values are used. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Community Support better than Official Support? (was: Can I Develop using Community Edition and Deploy onto Enterprise Edition??)
This comment has me intrigued: On 9/21/2011 17:50, John Daisley wrote: Partitioning is available in the community edition and has been for a while now. Support is the only real difference and since Oracle took over the support available in the community is usually faster and better than you get from Oracle. I work in MySQL Support and other than the tools that we were given to work with, very little should have changed in our attitude, our knowledge, or our level of professionalism (that I am aware of). Perhaps there are thinks that the other support providers are doing better? Please use this thread as a forum to which you can vent all of your complaints or concerns about MySQL support or to describe ways in which the other support systems are better. If it's policy changes, tell us. If it's response times, tell us. If it's our level of services, tell us. If you don't like the font on the web site, tell us. This is your chance to completely rip us a new one and to brag about your favorite service offerings at the same time. All opinions about any support providers are welcome. Thank you kindly, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Arrays
Hello Javad,
On 9/2/2011 05:51, javad bakhshi wrote:
Hi again,
Thanks for the tips. My problem is:
I have a Function in Mysql that has some arguments in the signature as
follows:
CREATE FUNCTION Myfunction( type TINYINT, sec SMALLINT, vid INTEGER,
way TINYINT, quid INTEGER, day TINYINT )
RETURNS CHAR(50)
BEGIN
DECLARE result CHAR(50);
DECLARE Temp DECIMAL(9,1);
SELECT Table1( vid, day, way) INTO Temp;
IF Temp IS NOT NULL THEN
SELECT CONCAT_WS(',',sec, 0, quid, Temp) into result;
ELSE
SELECT CONCAT_WS(',',sec, 0, quid, 0 ) into result;
END IF;
RETURN result;
END;
The problem seems to be solved by using CHAR but I really want to have a
sequence of Integers as the result not CHAR. The reason that I dont use
a table to insert the result into it and retrieve it later is that this
process takes more time that It should. Any thoughts?
Actually, that's not 'the problem' you are attempting to solve at all.
That is an attempt at a 'solution' to the problem.
If I stare very hard into my crystal ball and use all of my
psychic-SQL-fu, all I can tell is that you are attempting to build some
kind of comma-separated value. However, why you need this list and what
it is trying to solve is beyond me.
Please step back from the SQL end of things for a second and talk to us
about the actual problem you are trying to solve. Are you generating
nuclear launch codes? Are you indexing someone's genome? Are you trying
to build a web page? All of this SQL coding is designed to be a single
step in some larger process. If you share that larger purpose with us,
we may be able to suggest a more efficient approach than arrays to solve
your larger problem.
Regards,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Query Optimization
On 9/1/2011 09:42, Brandon Phelps wrote: On 09/01/2011 04:59 AM, Jochem van Dieten wrote: ... WHERE (open_dt = '2011-08-30 00:00:00' OR close_dt = '2011-08-30 00:00:00') AND (open_dt = '2011-08-30 12:36:53' OR close_dt = '2011-08-30 12:36:53') In that case your logic here simplifies to: WHERE open_dt = '2011-08-30 00:00:00' AND close_dt = '2011-08-30 12:36:53' Now add an index over open_dt and close_dt and see what happens. Jochem Jochem, I can't really use your WHERE logic because I also need to retrieve results where the open_dt time is out of the range specified. For example, a very large file download might span multiple days so given your logic if the connection was started 2 days ago and I want to pull 1 days worth of connections, I would miss that entry. Basically I want to SELECT all of the records that were opened OR closed during the specified time period, ie. if any activity happened between my start and end dates, I need to see that record. Any other ideas? I believe Jochem was on the right track but he got his dates reversed. Let's try a little ASCII art to show the situation. I will setup a query window with two markers (s) and (e). Events will be marked by || markers showing their durations. a) (s) (e) b) |---| c) |---| d)|---| e) || f) |---| g) |---| To describe these situations: a) is the window for which you want to query (s) is the starting time and (e) is the ending time for the date range you are interested in. b) the event starts and stops before your window exists. It won't be part of your results. c) the event starts before the window but ends within the window - include this d) the event starts and ends within the window - include this e) the event starts before the window and ends after the window - include this f) the event starts inside the window but ends beyond the window - include this. g) the event starts and ends beyond the window - exclude this. In order to get every event in the range of c-f, here is what you need for a WHERE clause WHERE start = (ending time) and end = (starting time) Try that and let us know the results. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Arrays
On 8/27/2011 11:18, wrote: 2011/08/26 13:58 -0700, javad bakhshi Thanks guys for the help. but my problem seems to stand unsolved. Right, no arrays. Nothing is left but table. I used a temporary table, but note that MySQL also does not let table be returned, or passed in. The table-name will be *sigh* global. If the table remains an active part of the connection, you always have access to it. It is possible to pass the name of the table into a stored procedure and use a prepared statement to do nearly anything you want to do with the data. What is it you are trying to do with this array in the first place? It sounds as though trying to process this data using the set-oriented functions of the SQL language is causing you more of a problem than the fact that the language doesn't have an array data type. There are normally many ways to solve any problem. Perhaps if you shared the problem you are trying to solve, you can see how many different ways the members of the list can solve it without resorting to an array? -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Reg...My Hung MYSQL instance
Hello Shafi, On 8/25/2011 02:02, Shafi AHMED wrote: Thank you everyone who have responded back... The issue is fixed now after increasing the max connections param I disagree. I believe you only reduced the symptom of the problem. The real problem was you had too many open connections. The solution is to figure out why each of your connections had been open for so long and why you needed so many. * Were those idle connections sitting around doing nothing? - close them * Were they taking forever to finish their business? - write better queries or improve your data structures. Then close them. Allowing more connections to be made at one time can only push your system harder. Each connection requires some resources to check its status. There must be buffers for sending and receiving data. Also, if there are any connection-specific MySQL objects created on a connection that never closes, then those objects will continue to take up resources as well (user variables, prepared statements, temporary tables) . Basically, you need to get your connections under control in order to solve your problem. Raising the limit was probably a temporary fix, at best. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: hand replication
On 8/15/2011 09:06, wrote: 2011/08/10 08:16 +0200, Johan De Meersman Yes, the MySQL binary log can be read (and thus, re-executed) by the mysqlbinlog utility. Yes, but what is the best means of picking up the changes from the instance where there were changes to the instance that is a copy? Is it best to copy the log and that so use msqlbinlog? Or is it better so to use msqlbinlog that it makes SQL statements that I copy to the other instance? No TCP/IP here, only a flash drive. If you are going to pretend to be the MySQL replication system, it wouldn't hurt you to understand the process before you start. First, read the replication chapter in the manual. It will describe the theory behind replication. Next, you need to realize that you will be replacing both the SLAVE IO thread and the SLAVE SQL thread with your process. The SLAVE IO thread you replace when you get the statements the slave needs to replicate onto the flashdrive. You can do that two different ways: 1) extract the statements from the binary log. 2) get the master to sent you the statements just as if you were a slave. mysqlbinlog will do either - (again, read the manual on how to use the tool) Once you have collected the statements you need the slave to apply (and put them on your flash drive), now it's your turn to replay those statements on the slave. The easiest tool for that will probably be the mysql client (a command-line tool). This is where you become the SLAVE SQL thread. Beyond that, all you really need to keep up with is the binary log position you replicated last (again, pretending to be the SLAVE IO thread). Best of luck! what you are doing is definitely labor intensive. Regards, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: very large import
On 8/3/2011 20:36, Nuno Tavares wrote: The following page has some nice interesting stuff, assuming you have a reasonable configuration in place (innodb_buffer_pool, etc[1]) http://download.oracle.com/docs/cd/E17952_01/refman-5.5-en/optimizing-innodb-bulk-data-loading.html ... The same content is also available here: http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html It may be an easier address to reach. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Which is better
On 8/2/2011 02:41, Adarsh Sharma wrote: Dear all, Just want to know which join is better for querying data faster. I have 2 tables A ( 70 GB ) B ( 7 MB ) A has 10 columns B has 3 columns.Indexes exist on both tables's ids. select p.* from table A p, B q where p.id=q.id or select p.* from table B q , A p where q.id=p.id Thanks There is no difference in performance. The optimizer will change the sequence it uses to read the tables according to its own rules. If you had used STRAIGHT JOIN to force a particular execution path, the it would normally be faster to read the smaller table first. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How select all records exact x days ago ?
On 7/31/2011 13:18, yavuz maslak wrote: I don't want all records during 5 days ( 24*5days ) . Only I need records at 5 days ago ( for instance 24 hours on 26 th July 2011) ? How can I do that ? Show us your table definition (the CREATE TABLE ... form, please), tell us which column you want to base your time comparison on, and someone will show you an example. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL refusing to accept passwords
On 7/22/2011 18:48, Tim Thorburn wrote:
On 7/22/2011 5:02 PM, Shawn Green (MySQL) wrote:
On 7/21/2011 22:45, Tim Thorburn wrote:
Hello,
For those keeping score, this will be the second time in the past few
months I've come upon this problem. To recap, this is happening on a
development laptop running Win7 64-bit Ultimate and MySQL 5.5.13. This
morning, all was working well. This evening, I launched MySQL Workbench
5.2.34 CE to work on a table. When I attempt to access the server from
within Workbench, I'm now prompted with a window asking for my password.
Of course, my password is not accepted when I enter it - I'm presented
with error #2000.
Just to confirm, mysql --version returns:
mysql Ver 14.14 Distrib 5.5.13, for Win64 (x86)
This is not an upgrade, after my last mishap, I once again formatted the
laptop with a fresh install of Windows 7 Ultimate 64-bit as well as
MySQL 5.5.13. I've begun digging through bugs.mysql.com, but I'm not
seeing any standing out. I should point out that this problem also
breaks any sites running on this dev laptop as all passwords are no
longer accepted.
Any thoughts on what may be causing this? It seems to be happening every
month or so at this point.
Thanks in advance,
-Tim Thorburn
* Check your binary logs, someone may actually be changing your
passwords.
http://dev.mysql.com/doc/refman/5.5/en/mysqlbinlog.html
* Be careful with what you backup/restore. You may accidentally revert
your tables to a condition before you set the password.
* mysql.exe is the command-line client. While it would be unusual to
have a client utility that is of a different version than your server,
the actual command to determine the version of the MySQL database
server would be
mysqld --version
* did you attempt to login using mysql to see if the passwords really
were different? Remember, the account 'root' for a new installation is
not created without a password. If you had restored a very old copy of
that table, that might have been your situation.
* are you aware of the lost password reset instructions in the manual?
http://dev.mysql.com/doc/refman/5.5/en/resetting-permissions.html
Hi Shawn,
Thanks for the reply. As this has happened before, and because I'm on a
deadline, I ended up stopping the MySQL service with NET STOP MYSQL,
then started MySQL with the skip-grant-tables option so that I could log
in as root and make a backup via mysqldump. After this, I uninstalled
MySQL from Control Panel, then proceeded to delete C:\Program
Files\MySQL and C:\ProgramData\MySQL directories before rebooting to do
a clean install of MySQL 5.5.14.
...
Your approach was very heavy-handed. To me what you describe can be
compared to building a whole new car just because you broke your key off
in the lock.
Next time, query the `mysql`.`user` table and look at which users exist
and what their password hashes are. To gain access you may still need to
bypass the locks by using --skip-grant-tables. After you find (or don't
find) the accounts you want to use, check their passwords. If you know
the plaintext password of an account, compare its hash to the one on the
table -
SELECT PASSWORD('passwordgoeshere');
If the two match then username/password may not be the problem. It may
be the machine from which you are attempting to login. MySQL
authentication requires three parts to align to provide access: user
name, password, and a host value (or pattern). You can be the correct
user, using the correct password but you may not be allowed (by the host
pattern) to login from the machine from which you are attempting to login.
Please do audit your old tables and see what you can discover.
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL refusing to accept passwords
On 7/21/2011 22:45, Tim Thorburn wrote: Hello, For those keeping score, this will be the second time in the past few months I've come upon this problem. To recap, this is happening on a development laptop running Win7 64-bit Ultimate and MySQL 5.5.13. This morning, all was working well. This evening, I launched MySQL Workbench 5.2.34 CE to work on a table. When I attempt to access the server from within Workbench, I'm now prompted with a window asking for my password. Of course, my password is not accepted when I enter it - I'm presented with error #2000. Just to confirm, mysql --version returns: mysql Ver 14.14 Distrib 5.5.13, for Win64 (x86) This is not an upgrade, after my last mishap, I once again formatted the laptop with a fresh install of Windows 7 Ultimate 64-bit as well as MySQL 5.5.13. I've begun digging through bugs.mysql.com, but I'm not seeing any standing out. I should point out that this problem also breaks any sites running on this dev laptop as all passwords are no longer accepted. Any thoughts on what may be causing this? It seems to be happening every month or so at this point. Thanks in advance, -Tim Thorburn * Check your binary logs, someone may actually be changing your passwords. http://dev.mysql.com/doc/refman/5.5/en/mysqlbinlog.html * Be careful with what you backup/restore. You may accidentally revert your tables to a condition before you set the password. * mysql.exe is the command-line client. While it would be unusual to have a client utility that is of a different version than your server, the actual command to determine the version of the MySQL database server would be mysqld --version * did you attempt to login using mysql to see if the passwords really were different? Remember, the account 'root' for a new installation is not created without a password. If you had restored a very old copy of that table, that might have been your situation. * are you aware of the lost password reset instructions in the manual? http://dev.mysql.com/doc/refman/5.5/en/resetting-permissions.html -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL refusing to accept passwords
On 7/22/2011 17:02, Shawn Green (MySQL) wrote: ... quick correction ... * ...the account 'root' for a new installation is* created without a password. ... I originally said 'is not'. Sorry for the confusion -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: getting procedure code via mysqldump
On 3/29/2011 19:09, John G. Heim wrote: I would like to use mysqldump to get a copy of the code for a stored procedure in a format that is similar to the code I used to create it. The problem is that I'm blind and I have to listen to the code to debug it. I think I have a file containing the code that I used to create the stored procedure but I want to make absolutely sure. This is what I've tried: mysqldump --p --routines --no-create-info --no-data --no-create-db --skip-opt --skip-comments --compatible=ansi --result=routines.sql database My problem is that generates a file with a lot of lines I don't understand. for example: /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `TIMETABLE_SYNC`() That appears to be the line to create the stored procedure 'timetable_sync'. But what's with all the other stuff on that line? Can i get rid of it? As Claudio mentioned, those are version-sensitive comments. In order for a MySQL server to not ignore the comment, it must be a version equal to or greater than the value tagged in the comment. For example, stored procedures did not exist before version 5.0.3 . So all of the stored procedure will be enclosed with comments that look like /*!50003 */ We enhanced the security of the stored procedures themselves by adding the DEFINER= option to the definition. We did this in version 5.0.20. That is why that part of the stored procedure was dumped using the comment tags /*!50020 */ Unfortunately, I have no way at this time to separate the version-specific comments from the rest of the dump. Perhaps someone better than I at using grep, sed, or awk could produce a script to strip those comments and share with the list? Yours, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- 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 with slow query
On 3/10/2011 12:32, Jim McNeely wrote: Rhino, Thanks for the help and time! Actually, I thought the same thing, but what's weird is that is the only thing that doesn't slow it down. If I take out all of the join clauses EXCEPT that one the query runs virtually instantaneously. for some reason it will use the index in that case and it works. If I take out everything like this: SELECT a.IdAppt, a.IdPatient, p.NameLast, p.NameFirst, p.NameMI from Appt_ a LEFT JOIN patient_ p ON a.IdPatient = p.IdPatient WHERE a.ApptDate= '2009-03-01'; It is still utterly slow. EXPLAIN looks like this: ++-+---+---+---+--+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+---+--+-+--++-+ | 1 | SIMPLE | a | range | apptdate | apptdate | 4 | NULL | 296166 | Using where | | 1 | SIMPLE | p | ALL | NULL | NULL | NULL| NULL | 262465 | | ++-+---+---+---+--+-+--++-+ But, very good try. I thought this might be it as well. ... snip ... According to this report, there are no indexes on the `patient_` table that include the column `IdPatient` as the first column. Fix that and this query should be much faster. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- 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 with slow query
On 3/10/2011 13:12, Jim McNeely wrote: Shawn, This is the first thing that I though as well, but here is a portion from the show create table for patient_: PRIMARY KEY (`zzk`), KEY `IdPatient` (`IdPatient`), KEY `SSN` (`SSN`), KEY `IdLastword` (`IdLastword`), KEY `DOB` (`DateOfBirth`), KEY `NameFirst` (`NameFirst`), KEY `NameLast` (`NameLast`) This extremely simple join is still massively slow. Jim On Mar 10, 2011, at 10:00 AM, Shawn Green (MySQL) wrote: On 3/10/2011 12:32, Jim McNeely wrote: Rhino, Thanks for the help and time! Actually, I thought the same thing, but what's weird is that is the only thing that doesn't slow it down. If I take out all of the join clauses EXCEPT that one the query runs virtually instantaneously. for some reason it will use the index in that case and it works. If I take out everything like this: SELECT a.IdAppt, a.IdPatient, p.NameLast, p.NameFirst, p.NameMI from Appt_ a LEFT JOIN patient_ p ON a.IdPatient = p.IdPatient WHERE a.ApptDate= '2009-03-01'; 1) Verify that the indexes on `patient_` haven't been disabled SHOW INDEXES FROM `patient_`; http://dev.mysql.com/doc/refman/5.5/en/show-index.html 2) Verify that the data types of `Appt_`.`IdPatient` and `patient_`.`IdPatient` are not incompatible. (for example: one is varchar, the other int) Thanks, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to protect primary key value on a web page?
On 3/10/2011 12:10, mos wrote: I want to bounce some ideas off of MySQL developers that use it for web development. Maybe I'm a little paranoid, but when dealing with the Internet, I want to make my web app as secure as possible. I'm hoping some of you can offer me some ideas in this respect. I am building a web application that uses MySQL 5.5 with Innodb tables and I don't want the user to see the actual primary key value on the web page. The primary key could be the cust_id, bill_id etc and is usually auto increment. This primary key can appear in the url and will be used to pull up a record and display it on the web page. ... You could follow some of the basic security designs already in use. 1) use https:// 2) Don't worry about the URLs, worry about authenticating the requesting user to the session to the data. Only allow the users access to what they are supposed to have access to in the quantities they are allowed to view it. 3) You could include the session identifier as part of the URL. Once the session expires, that URL is now dead. One of your worries was a BOT coming along and scraping off all of your public files. That's pretty easy to catch if you actively monitor usage patterns. Another way of doing that is to have two unique identifiers for each data object, one is the sequential private number, the other is the non-incremental (random or hash) value that you can expose via URL. It's not really securing anything but it is making it harder for random successes. If they fail to randomly find a valid value enough times, you lock out that IP address. However this really isn't a great topic for a database list as most of solution to your problems reside in how you design your application. Yours, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- 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 with slow query
Hi Jim, On 3/9/2011 17:57, Jim McNeely wrote: I am trying to set up an export query which is executing very slowly, and I was hoping I could get some help. Here is the query: SELECT a.IdAppt, a.IdPatient, p.NameLast, p.NameFirst, p.NameMI, a.IdProcedure, a.ProcName, CAST(CONCAT(a.ApptDate, , a.ApptTimeOut) AS CHAR) ApptDateTime, a.ApptLenMin Duration, a.Room Primary_Resource_ID, 'R' as Primary_Resource_Type, t.Note, c.IdPractAffil DoctorID, af.PractName DoctorName, a.ICD9code ICD9, '??' Diagnosis_free_test from Appt_ a LEFT JOIN (patient_ p, today_ t, Copy_ c, Affil_ af) ON (a.IdPatient = p.IdPatient AND a.IdPatientDate = t.IdPatientDate AND CONCAT(a.IdAppt, '0') = c.IdApptType AND a.IdPriCarePhy = af.IdAffil) WHERE a.ApptDate= '2009-03-01'; p.IdPatient, t.IdPatientDate, c.IdApptType, and af.IdAffil are all indexed. Also I selectively took out join parameters until there was nothing but a join on the patient table, and it was still slow, but when I took that out, the query was extremely fast. What might I be doing wrong? Thanks, Jim McNeely The performance problem is with your Cartesian product. I think you meant to write: from Appt_ a LEFT JOIN patient_ p ON a.IdPatient = p.IdPatient LEFT JOIN today_ t ON a.IdPatientDate = t.IdPatientDate LEFT JOIN Copy_ c ON CONCAT(a.IdAppt, '0') = c.IdApptType LEFT JOIN Affil_ af ON a.IdPriCarePhy = af.IdAffil As of 5.0.12, the comma operator for table joins was demoted in the 'order of precedence' for query execution. That means that MySQL became more complaint with the SQL standard but it also means that using a comma-join instead of an explicit ANSI join can result in a Cartesian product more frequently. Try my style and compare how it works. If both styles are similarly slow, collect the EXPLAIN plan for this query and share with the list. Yours, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: auto_increment by more than 1
On 2/23/2011 12:41, Jim McNeely wrote: Is there a way to set the auto-increment for a particular table to increase by some number more than one, like maybe 10? Thanks in advance, Jim McNeely The manual is your friend. Don't be afraid of it :) http://dev.mysql.com/doc/refman/5.5/en/replication-options-master.html#sysvar_auto_increment_increment -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: ERROR 1005 (HY000): (errno: 150) details for show create table and innodb status given
Hello Hari, You already posted the best answer we could provide :) On 2/22/2011 13:00, hari jayaram wrote: Hi I am getting a Foreign key error . ... I have attached the create table syntax for both the parent and child tables and the innodb status below. ... mysql show innodb status; +-- -- -- + | Status... | LATEST FOREIGN KEY ERROR 110222 12:54:53 Error in foreign key constraint of table bioscreencast_lap/#sql-1515_130f: FOREIGN KEY (id) REFERENCES parent(id) ON DELETE NO ACTION ON UPDATE NO ACTION: Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html for correct foreign key definition. To rephrase, a little: Columns must be indexed before they can participate in Foreign Keys. See the link you provided for more details. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Finding Data in One of Two Tables
On 2/2/2011 04:23, Hal Vaughan wrote: I'm using this query in a Perl program: SELECT Distinct x.Search FROM $source.Searches AS x LEFT JOIN searching.Status AS s ON x.Search=s.Search AND s.Source='$source' WHERE x.RedoTime'$now' AND s.Search IS NULL This program runs other programs that do internet searches. I have different sources (stored in $source, of course), and source has it's own DB with a table, Searches. Each row of searches describes a different search that can be done and each search has a name, which is stored in the Searches column (within the Searches table, so, yes, I use that name for a table and a column). When a search is being executed, an entry is placed in searching.Status, with one row in that table showing the status of the search. So if I have a source named alpha and searches named one and two and the system is executing the search one, not only is there a row in alpha.Searches describing one in depth, but there is a row in searching.Status describing the progress with one. When each search is done, the RedoTime is set so it's easy to see when it needs to be executed again. What I want to do is get a list of searches in the Searches table (within the source's DB) that are NOT listed in Status and where the RedoTime is before $now (the current time). From what I've read, the query above should do it, but I have this nagging feeling I've done something wrong. Will that query pick up all rows in $source.Searches that have a RedoTime before $now that are NOT also listed in searching.Status? Thanks for any help on this! Maybe today isn't my day. I can't distinguish between what you are looking for and what you do not want to find. WANTED: a list of searches in the Searches table (within the source's DB) that are NOT listed in Status and where the RedoTime is before $now (the current time). NOT WANTED: rows in $source.Searches that have a RedoTime before $now that are NOT also listed in searching.Status I can't seem to spot the difference. Maybe if you phrased it differently or provided two or three sample rows for each table I could understand your distinction? -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN The Customer Support Center Will Retire February 11, 2011 Find out what you need to know about the migration to My Oracle Support: http://www.oracle.com/us/support/mos-mysql-297243.html -- 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 with Date in Where Clause
On 1/31/2011 15:12, Phillip Baker wrote: Greetings All, I am looking for a little help in setting a where clause. I have a dateAdded field that is a DATETIME field. I am looking to pull records from Midnight to midnight the previous day. I thought just passing the date (without time) would get it but I keep getting an empty record set. So looking for something that works a bit better. Any suggestions? Blessed Be Phillip Never ascribe to malice what can be explained by incompetence -- Hanlon's Razor All of the datetime values for yesterday actually exist as a range of datetime values between midnight that morning (inclusive) and midnight the next morning (not part of the search). So your WHERE clause needs to resemble ... WHERE dtcolumn = '2011-01-21 00:00:00' and dtcolumn '2011-01-22 00:00:00' This pattern has the added advantage of not eliminating the possibility of using an INDEX on the dtcolumn column by wrapping it inside a function. Yours, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB and rsync
On 1/25/2011 10:45, Robinson, Eric wrote: There is a very good reason: it is the phenomenon of row drift. The master and slave can appear to be in good sync, but often it is not actually the case. ... sounds interesting; have you got any document explaining this phenomenon? AFAIK, the things that (silently) break replication are: - non-deterministic functions in statement-based replication - hand-made updates on the slave db is this enough to justify a *daily* resync?! I'm definitely no expert on this. All I know is that we used to frequently experience situations where queries to the slaves would return different recordsets than the same queries to the masters. Yet by all other indications the servers were in sync. All the replication threads were running and the row counts were identical, but the data in the rows was sometimes different. I asked about this in the list and the answers I got back were that the phenomenon was called row drift and was fairly well known and not always easy (or sometimes even possible) to eliminate because of bad programming practices in some off-the-shelf applications. At that time, the consensus in the list was that it was not safe to trust replication slaves for backup purposes. That's when I came up with the idea of doing an rsync every night, which creates a slave that is 100% reliable for using as a backup source and also eliminates problems with row-drift. Since we started using that technique, we don't get calls from users complaining that their reports are showing bogus totals and such. I suspect that your queries were not as deterministic as you thought they were. Do you have a sample of a query that produced different results between the master and the slave? We shouldn't need the results, just the query. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB and rsync
On 1/25/2011 09:00, Robinson, Eric wrote: ... I'm starting to worry that you may be right. I know FLUSH TABLES WITH READ LOCK does not work as expected with InnoDB, but is there really no way to put InnoDB into a state where all changes have been flushed to disk and it is safe to rsync the directory? Is stopping the service really the only way? (And even if I stop the service, is rsync totally safe with InnoDB?) You need to quiesce the InnoDb background threads. One technique is mentioned here: http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html Look for the section talking about clean backups. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tables
On 1/21/2011 14:21, Kendall Gifford wrote: Hello everyone, I've got a database on an old Fedora Core 4 server running MySQL 4 (mysql-server.x86_64 4.1.12-2.FC4.1). The database in question has just two (InnoDB) tables: messages (approx 2.5 million records) recipients (approx 6.5 million records) These track information about email messages. Each message has many recipient records. The structure of the two tables (omitting irrelevant data fields) are as follows: +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | sent_at | datetime | | MUL | -00-00 00:00:00 || | . OTHER FIELDS OMITTED FOR BREVITY ... | +-+--+--+-+-++ +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | message_id | int(10) unsigned | | MUL | 0 || | employee_id | int(10) unsigned | YES | MUL | NULL || | . OTHER FIELDS OMITTED FOR BREVITY ... | +-+--+--+-+-++ I have the following query that is just too slow: SELECT messages.* FROM messages INNER JOIN recipients ON recipients.message_id = messages.id WHERE recipients.employee_id = X GROUP BY messages.id ORDER BY sent_at DESC LIMIT 0, 25; This takes about 44 seconds on average. The query explanation is as follows: ++-+++--+--+-+-++--+ | id | select_type | table | type | possible_keys| key | key_len | ref | rows | Extra| ++-+++--+--+-+-++--+ | 1 | SIMPLE | recipients | ref| messages_fk,employee_idx | employee_idx | 5 | const | 222640 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | messages | eq_ref | PRIMARY | PRIMARY | 4 | email_archive.recipients.message_id | 1 | | ++-+++--+--+-+-++--+ I've been doing some searching on the web and have no idea if/how this can be sped up. Most searches these days reference MySQL 5.x which I'm just not sure how much applies. I'm hoping that there is something obvious that I'm missing, or that one of you experts knows what I might be able to change to speed this query up. Anyhow, thanks in advance for even so much as reading my message, let alone replying :). You need to get rid of the GROUP BY to make this go faster. You can do that by running two queries, one to pick the list of unique recipients.message_id values that match your where condition then another to actually retrieve the message data. Something like this CREATE TEMPORARY TABLE tmpMessages (message_id INT UNSIGNED, PRIMARY KEY (message_id)) ENGINE=MEMORY; INSERT IGNORE tmpMessages SELECT message_id FROM recipients WHERE employee_id = X; SELECT messages.* FROM messages INNER JOIN tmpMessages ON tmpMessages.message_id = messages.id ORDER BY sent_at DESC LIMIT 0, 25; By pre-selecting a limited set of message_id values from the recipients table, you seriously reduce the number of rows that need to be scanned. Also, the INSERT IGNORE technique is faster than the GROUP BY because it uses an index to identify any duplicates instead of a scan of all previous unique values. Please let us all know if this is faster enough. (and don't forget to drop the temp table once you are through using it) -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: running a mysql query inside a loop of another without a sync error
Hello Delan,
On 1/19/2011 21:54, Delan Azabani wrote:
Hi all,
I'm using MySQL with C in a CGI application. I hope this is the right
list to ask for help.
If I have this simplified code:
MYSQL_RES *res;
MYSQL_ROW row;
mysql_query(mysql, some select query);
res = mysql_use_result(mysql);
while (row = mysql_fetch_row(res)) {
MYSQL_RES *res2;
MYSQL_ROW row2;
mysql_query(mysql, some other select query using an id from the
first);
res2 = mysql_use_result(mysql);
/* ... */
mysql_free_result(res2);
}
mysql_free_result(res);
Whenever I run the second query, inside the loop, I get the nasty
'commands out of sync' error. How can I run a select query while in a
loop fetching rows from another select query? Or, do I have to fetch all
the rows completely first and store them in memory (which wouldn't be
very 'nice' to do)?
If someone could help me with this problem, it would be greatly appreciated.
The mysql object you are using for your connection can only have one
active query or result on it at a time. To have two sets of results
working, you need a second independent connection to the MySQL server
http://dev.mysql.com/doc/refman/5.5/en/c-api-data-structures.html
###
MYSQL
This structure represents a handle to one database connection. It is
used for almost all MySQL functions. You should not try to make a copy
of a MYSQL structure. There is no guarantee that such a copy will be
usable.
###
http://dev.mysql.com/doc/refman/5.5/en/mysql-real-connect.html
###
The first parameter should be the address of an existing MYSQL
structure. Before calling mysql_real_connect() you must call
mysql_init() to initialize the MYSQL structure. You can change a lot of
connect options with the mysql_options() call. See Section 22.9.3.49,
“mysql_options()”.
###
http://dev.mysql.com/doc/refman/5.5/en/threaded-clients.html
###
Two threads can't send a query to the MySQL server at the same time on
the same connection. In particular, you have to ensure that between
calls to mysql_query() and mysql_store_result() no other thread is using
the same connection.
###
This same rule applies to attempting to process more than one query on
the same connection. You must complete the first query before starting
the second or you must open a separate connection to handle the second
query.
Yours,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Incorrect key file for table
On 1/15/2011 02:07, Jørn Dahl-Stamnes wrote: On Saturday 15 January 2011 00:28, Johnny Withers wrote: The result of your query without the join probably exceeded your tmp_table_size variable. When this occurs, MySQL quit writing the temp table to disk thus producing an incorrect table file. (I think). Yes, part of this was my fault (the missing join) but I still wonder why the server processed the query. Would it not be more appropriate with an error message saying that this query contain an error? The error was only logical. The SQL syntax was fine. The MySQL server has no other way to gauge the accuracy of what you intended the query to be. for example, this is perfectly legal SQL: SELECT * FROM table1, table2; Even if you had used explicit JOIN operators, the use of an ON clause is still optional. Here is my same example rewritten to use the JOIN operator. SELECT * FROM table1 INNER JOIN table2; Again, this is a perfectly legal statement, even if it may not make logical sense in the context of your application or data to leave out the joining criteria. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Which row in which table has been accessed at which time?
Hi listers I have a mysql web application. in this application it would be fine to be able to track the database entries i have visited, because often later on i grat my head: which entry did i see this already in? So i would need a way to find out which entries in which table i have visited lately. i first created a last_access table column and updated it before selecting the table entry, but alas, this way i also updated the last_update entry of the table which has on update current_timestamp. it can't be done this way. i also looked for an on select event in mysql, but i was not sucessful. also, the show status command was not helpful. the complicated way would be to create a special table and make entries into it whenever i access entries in different tables. when googling around i found, it is even not easy to find out, which tables have been accessed in general. if, now, i want to know even which row in a particular table has been accessed at which time, the problem gets even more difficult. does anybody have a solution to this, which is easier than the complicated way mentionned earlier? thanks for any hints. suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Which row in which table has been accessed at which time?
On 1/12/2011 10:26, mysql wrote: Hi listers I have a mysql web application. in this application it would be fine to be able to track the database entries i have visited, because often later on i grat my head: which entry did i see this already in? So i would need a way to find out which entries in which table i have visited lately. i first created a last_access table column and updated it before selecting the table entry, but alas, this way i also updated the last_update entry of the table which has on update current_timestamp. it can't be done this way. i also looked for an on select event in mysql, but i was not sucessful. also, the show status command was not helpful. the complicated way would be to create a special table and make entries into it whenever i access entries in different tables. when googling around i found, it is even not easy to find out, which tables have been accessed in general. if, now, i want to know even which row in a particular table has been accessed at which time, the problem gets even more difficult. does anybody have a solution to this, which is easier than the complicated way mentionned earlier? thanks for any hints. suomi It may be possible for some kind of client program to keep track of which queries you executed but it is not practical at all for any database system to record every access to every row if you expect any sort of reasonable performance. Some very high-security situations can be configured to do that but it *seriously* degrades performance to do that much extra logging. This is also not a behavior that MySQL can provide without some major custom engineering. The closest sort of log we can provide to you for that kind of tracing is the General Query Log http://dev.mysql.com/doc/refman/5.5/en/query-log.html Yours, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: how to generate a data set then join with in on fly?
On 1/10/2011 18:51, Ryan Liu wrote: Hi, In MySQL, is that possible to generate a data set join with it on fly (without create an temporary table)? e.g. for a report used by a graphic tool, it requires data in all dates, even it is null. Can I select vacationT.* left join ( all dates d in the past 3 years) on vacationT.`date` = d ? Thanks, Ryan Sorry, no. To do the report I think you are describing will require you to have a table of all dates. Also the date table needs to be on the LEFT side of the LEFT JOIN to be included even if there aren't any matches. SELECT FROM master_date_table LEFT JOIN vacationT ... Or, you can accept the partial list of dates actually stored in the database as accurate and fill in any missing dates when you render it in your report (inside the application). It may be much easier to fill-in those dates when you format the report, have you checked? -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Trying to Generate Dynamic Sequence IDs as Stored Function or Stored Procedure.
On 1/4/2011 23:23, James Dekker wrote: Peter, Thanks for the response! Unfortunately, that worked but a new error arose: check the manual that corresponds to your MySQL server version for the right syntax to use near 'ID = (select max(CONVERT(id_field, signed)) from my_table_t)' at line 1 Is there a better way to generate incremented sequence IDs? Can this be done in a stored function? Is there a particular reason why you cannot use an auto_increment column to atomically create your sequence number? -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Trying to Generate Dynamic Sequence IDs as Stored Function or Stored Procedure.
On 1/5/2011 13:31, James Dekker wrote: Because some sequence tables contain one to many cardinality and MySQL tables can only have one auto_increment column... Is there a way to do what I am trying to do (obtain max sequence id, set it to its corresponding table, and then increment by one) in a stored function? Maybe some variation of this will help? http://stackoverflow.com/questions/805808/emulating-a-transaction-safe-sequence-in-mysql I don't understand the need for a SEQUENCE. In my history, if there is some kind of object identifier you want to use, then an auto_increment field on the row that defines the object itself is sufficient. Then all child elements of that object can include the autogenerated ID value from their parent object as you create them alongside of any unique identifiers they may require. http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id Have you also explored the use of auto_increment columns as part of a multiple-column index on MyISAM tables as described here? http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Close connetion
On 1/4/2011 14:47, Rafael Valenzuela wrote: Hi everyone: I've a problem , this is error http://pastebin.com/eCEqLQ9b , i've looking in for google and documentation of mysql and nothing. there any way to close connections with any command of mysql, i've modification the timeout and connexion number Any user with the SUPER privilege should be able to use the KILL command to tell a MySQL client connection to self-terminate. Note, MySQL does not have a way to force-close a connection; the must see that it was flagged to stop and clean up after itself. Stopping a long-running command may require a rollback. Rollbacks may be up to 30x slower to unwind than the command itself required to make the pending changes. Be aware of this as you evaluate your options. http://dev.mysql.com/doc/refman/5.1/en/kill.html http://dev.mysql.com/doc/refman/5.1/en/innodb-tuning.html -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Moving data between two servers with SQL
On 1/4/2011 15:53, Hank wrote: Hello, I have a background process that runs as a combination of PHPMySQL. The end results are records in a table on server #1 (but not the entire table, just a small subset of the table needs to move). What's the easiest/cleanest way of moving those records to an identical table on another server? In Oracle, we used to be able to set up connection profiles and move data between servers with SQL, but I'm guessing that's not easy to do with MySQL. I'd prefer not to use mysql command line client commands to save the data as an OS file and then import that into the other server using another mysql command line client command. I'd like to find something cleaner than that. I'm using 5.5.8. thanks, -Hank Have you looked at the FEDERATED storage engine? http://dev.mysql.com/doc/refman/5.5/en/federated-storage-engine.html -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: This just seems to slow
On 1/3/2011 10:41, Jerry Schwartz wrote: -Original Message- From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Sunday, January 02, 2011 11:49 PM ... Also delete your INDEX / KEYs and add them at the very end instead. [JS] Wouldn't it take as long to build the indices? I guess it probably wouldn't. It will not. MySQL does not grow or edit its index files incrementally, it computes a fresh on-disk index image for every change. Right now, you are doing a complete index rebuild for every row you add. If you add up the total work you are saving (index 121000 rows, index 121001 rows, index 121002 rows,...) then you can see a big improvement by waiting to put the indexes on the table at the very end of the process. http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html Yours, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Access to MySQL
Hi Jerry, On 12/17/2010 09:34, Jerry Schwartz wrote: -Original Message- From: Jo�o C�ndido de Souza Neto [mailto:j...@consultorweb.cnt.br] Sent: Friday, December 17, 2010 6:11 AM To: mysql@lists.mysql.com Subject: Re: Access to MySQL What about this? date_format(now(), %Y/%m/%d) [JS] I don't think you can use anything but a constant as a default value. You are correct with one exception that was already mentioned earlier: the TIMESTAMP storage type. from http://dev.mysql.com/doc/refman/5.1/en/create-table.html ### The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column. See Section 10.3.1.1, “TIMESTAMP Properties”. ### However, nothing says you can't use a function or other computation in a TRIGGER to set the default value to an empty column of a new row to whatever you wanted it to be. http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html I know it's a workaround but it will keep the default value management out of your application and inside the database. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Unable to add users with MySQL 5.5.8 under Windows
On 12/16/2010 03:53, Tim Thorburn wrote: On 12/16/2010 3:42 AM, Tim Thorburn wrote: I should mention this is Windows 7 Ultimate 64-bit. After rebooting I installed MySQL 5.5.8 for Win64 using the downloaded MSI file. Once MySQL was installed, I downloaded and installed the current version of Workbench (5.2.31a). When I run Workbench, I see that the MySQL server is indeed running - I'll also mention that this is my first time using Workbench, until now I had been using the older MySQL Administrator GUI, however it stopped working with 5.5.8. So, back in Workbench, I goto Manage Security under the Server Administration heading, choosing the default Local instance: MySQL server to connect to. Next I click Accounts and finally Add Account at the bottom of this screen. Under Details for Account newu...@% I add in the user name I want, type the password twice, I've tried leaving Limit Connectivity to Hosts Matching as % and localhost. Once I'm finished adding the user, I click Apply - however I'm presented with the following error message: /Unhandled exception: Error adding account accountn...@%:/ I've tried adding a user in MySQL Administrator, as I am more familiar with it. It provides a different error: /Error while storing the user information. The user might have been deleted. Please refersh the user list./ A command prompt pop-up appears with this error stating the following: /** Message: save user: error adding new user entry To add, I was able to successfully add a user to MySQL 5.5.8 with phpMyAdmin - would this point to a bug in the GUI tools? Thanks again Yes, that would be a problem localized to the GUI tool. Please visit http://bugs.mysql.com/ and either open a new bug describing your problems or add your details to any existing bugs that may already match your description. The team that manages that tool may ask for additional information so please do respond or we will close the bug as No Feedback. Warmly, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Parallel Inserts
On 12/13/2010 21:32, Andy wrote: Greetings everyone. I am in a situation where I need to do parallel inserts into MySQL database from inside my Perl program. Basically, I have several million records to insert into the database, and hence I would rather do them in parallel than doing them one at a time. I looked around but did not find any information on doing this. Does MySQL not support parallel reads/writes? Thanks in advance. Andy Actually, you would be better off using the LOAD DATA INFILE... command for your bulk loading needs. The more contention you create for the same data and the more times you force an index rebuild, the slower it will move. Single-threaded, bulk loading is what works best for MySQL. Some light reading to help you along: http://dev.mysql.com/doc/refman/5.1/en/load-data.html http://dev.mysql.com/doc/refman/5.1/en/non-select-optimization.html http://dev.mysql.com/doc/refman/5.1/en/csv-storage-engine.html -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: migrating a split replication
On 12/8/2010 22:50, Robert Citek wrote: Greetings to all, Can I migrate slave databases between slave servers? Imagine the following scenario: I have one master database server with 10 databases. I also have two slave database servers, one replicating 5 of the 10 databases, the other replicating the other 5 databases. Can I migrate one of the replicated databases from one slave to the other, resulting in one slave having 6 databases and the other having 4? I'm using the term migrate, but is there a more appropriate term? The docs mention various replication strategies[1], including splitting out different databases to different slaves. In the extreme case, I would like to do the opposite, consolidate databases among slaves, with the final state being all 10 databases on one slave and none on the second. Thanks in advance for your help, especially pointers to any references. [1] http://dev.mysql.com/doc/refman/5.0/en/replication-solutions.html Regards, - Robert The trick to moving replicated tables between boxes is to get both slaves to the same replication coordinates. Stop replication on one wait 5 minutes then stop it on the other. Check the binary log coordinates between the two. For the one that's looking at the older data, use a START SLAVE UNTIL ... command to get them both to the same binlog position. http://dev.mysql.com/doc/refman/5.1/en/start-slave.html Now, the copy of the data on one slave should be in an identical state to the copy of the data on the other slave (if it were replicating the table). Move the table(s) or database(s) to the other slave then undo any --replicate-* filters you may have that prevented replicating that information on the new box before the move. At this point, you probably need to copy the same --replicate-* rule you are removing from the new box to the old box so that it will stop processing commands for the data are trying to move. http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html After all looks good, START SLAVE on both machines and observe SHOW SLAVE STATUS to ensure that they are both catching up to the master. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL restore failing
On 12/3/2010 03:40, Machiel Richards wrote: I checked now and saw that they have already attempted a restore previously and hence the original table was dropped and recreated. I found some links on the internet stating that after restoring the dump file to the new version, the proc table should be dumped using the new version and reloaded. ... snip ... Any ideas? The easiest way to migrate between major versions is to dump logical contents of the system data tables (the entire MySQL database) separately from the rest of the data. It changes very slowly so there is no risk of being out of sync with the rest of the data. For example, instead of dumping the user tables (user, db, privs-table, etc...) and restoring them as raw data on the new system, you should get the SHOW GRANTS reports for each of your users. http://dev.mysql.com/doc/refman/5.1/en/show-grants.html Instead of dumping the raw data in the `proc` table, use the --routines option of mysqldump instead to write out the stored procedures as SQL statements. http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_routines Did you remember to dump your triggers (which are database-specific) using the --events option so that they were recreated with your production data tables? The tables in the `mysql` database can and usually do change sizes and definitions between major versions. The utility mysql_upgrade will modify the table definitions to match the current version after you restore your old-version tables but if you want to try to avoid that step, you can use my techniques. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Lowest non-zero number
On 12/3/2010 11:50, Mark Goodge wrote: Given a table containing a range of INT values, is there any easy way to select from it the lowest non-zero number? Obviously, MAX(column) will return the highest, but MIN(column) will return 0 if any row contains a 0, which isn't what I want. Any clues? Mark SELECT MIN(column) FROM table WHERE column0 ? -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Lowest non-zero number
On 12/3/2010 12:16, Mark Goodge wrote: On 03/12/2010 16:56, Paul Halliday wrote: On Fri, Dec 3, 2010 at 12:50 PM, Mark Goodgem...@good-stuff.co.uk wrote: Given a table containing a range of INT values, is there any easy way to select from it the lowest non-zero number? SELECT number FROM table WHERE number 0 ORDER BY number ASC LIMIT 1; Sorry, I should have said that I need to do this as part of a query which returns other data as well, including data from the rows which have a 0 in this column. So I can't exclude them with the WHERE clause. What I'm actually doing is something like this: SELECT name, AVG(score) as average, count(score) as taken FROM tests GROUP BY name and I want to extend it to something like this: SELECT name, AVG(score) as average, COUNT(score) as attempts, SUM(score = 0) as failed, SUM(score 0) as passed, MAX(score) as best_pass, . as lowest_pass FROM tests GROUP BY name and I need an expression to use in there to get the lowest non-zero value as lowest_pass. Does that make sense? And, if so, is there any easy way to do it? Mark Try this: MIN(if(score=0,NULL,score)) as lowest_pass That should either give you a null or a score. There is always the possibility that someone never had a score above zero. This should handle it. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: export db to oracle
On 11/16/2010 15:14, Sydney Puente wrote: Hello, How can I export a mysql 5.0.45 db to Oracle? mysql is going to stau but I need to pass the data to oracle, just so the data can be transfered. I have carried out a mysql dump. This seems fine.create table etc. about 20 MB in total. Any ideas? It is on Redhat if that makes a difference. I suggest you also look at the syntax for SELECT INTO OUTFILE, too. Dumps are usually scripts of SQL statements that Oracle may not read appropriately. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: a query not using index
On 11/8/2010 10:47 PM, wroxdb wrote: Hello, I have a query below: mysql select * from ip_test where 3061579775 between startNum and endNum; +++-+--+--++ | startNum | endNum | country | province | city | isp| +++-+--+--++ | 3061514240 | 3061579775 | 中国| 河南 | | 联通 | +++-+--+--++ the desc shows it isn't using the index: mysql desc select * from ip_test where 3061579775 between startNum and endNum; ++-+-+--+-+--+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+--+-+--+-+--++-+ | 1 | SIMPLE | ip_test | ALL | startNum,endNum | NULL | NULL | NULL | 396528 | Using where | ++-+-+--+-+--+-+--++-+ 1 row in set (0.01 sec) the table structure is: CREATE TABLE `ip_test` ( `startNum` double(20,0) default NULL, `endNum` double(20,0) default NULL, `country` varchar(50) NOT NULL default '', `province` varchar(50) NOT NULL default '', `city` varchar(50) NOT NULL default '', `isp` varchar(100) default NULL, KEY `startNum` (`startNum`), KEY `endNum` (`endNum`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 please help, thanks in advance. Have you tried a combined index of (startnum,endnum) instead of two single-column indexes? You may still run into problems, though, because ranged searches are usually performed as WHERE column_A BETWEEN X AND Y and not as WHERE X BETWEEN column_A and column_B and the optimizer has been designed to evaluate the first pattern but not the second. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Query Help
On 10/27/2010 6:55 AM, Nuno Mendes wrote: I have 3 tables: (1) Companies, (2) locations and (3) employees: CREATE TABLE `companies` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(75) NOT NULL, UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `locations ` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(75) NOT NULL, `company_id` int(11) NOT NULL, UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `employees` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(5) NOT NULL, `location_id` int(11) NOT NULL, UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 How do I retrieve list of all companies with total number of locations and total number of employees? The query bellow is the closest I could get to what I want but it's not quite there. SELECT companies.name, Count(locations.id) AS locations_count, Count(employees.id) AS employees_count FROM companies LEFT JOIN locations ON (companies.id = locations.company_id) LEFT JOIN employees ON (locations.id = employees .locations_id) GROUP BY companies.id Thank you! Nuno Mendes Have you looked at the WITH ROLLUP query modifier? http://dev.mysql.com/doc/refman/5.1/en/group-by-modifiers.html -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql's system variables
On 10/23/2010 12:44 PM, Claudio Nanni wrote: Just a little note: sometimes, if you are in command line session, you will not see the change of the dynamic variable unless you logout and in again, so in case you change the value of a dynamic variable but still the show variables shows you the old value, dont panic! exit mysql client and enter again. It also makes a difference which version of SHOW VARIABLES you are using: SHOW GLOBAL VARIABLES or SHOW SESSION VARIABLES Changes to global settings only apply to NEW sessions. Existing sessions can modify their personal settings without affecting the global defaults. Please read this if you are still confused: http://dev.mysql.com/doc/refman/5.1/en/using-system-variables.html http://dev.mysql.com/doc/refman/5.1/en/show-variables.html http://dev.mysql.com/doc/refman/5.1/en/set-option.html -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Percent of match in condition
On 10/21/2010 9:57 AM, Ali A.F.N wrote: Hi All, I have a table with different fileds and almost the type of all them are smallint. I want to search on some fields with OR condition. I want to know is there possibility to know how many fileds matched exactly? then I can say how many percent match are available. select * from my_table where sex_id = 1 or country_id = 120 or education_id I mean if in my table there are some records with sex_id = 1 or country_id = 120 then I got 2 (2 match) then I can say 66% percent match. Thank you, This is where having a name-value pair in your MySQL can help. Your data appears to belong to a dating site but it could easily belong to a product catalog or many other types of data sets. In this example,I want to compute product matches to see how close they are to my search criteria. In rough symbolic terms, this is one layout that can help. item table = id product_name (other fields) item qualities table = quality_id item_id quality_name quality_value An index on (quality_name, quality_value, item_id) also comes in very handy right about now. Let's say you wanted to look up all of the items that have color=blue, size=90cm, and flavor=peach (I really cannot imagine what this product may be, but after all this is only an example) So, with a big wide table, you would need to either do something like SELECT ... FROM old_style WHERE color='blue' and size='90cm' and flavor='peach'; But that would only find you an exact match. For partial matches, you would need to construct all sorts of partial queries. like ... WHERE color='blue' ... WHERE color='blue' and size='90cm' ... WHERE color=size='90cm' ... WHERE color=size='90cm' and flavor='peach' ... and compare the results. Using the new tables, you construct 3 union queries in your code and cache the results in a temporary table: CREATE TEMPORARY TABLE tmp_relevance ENGINE=MEMORY (SELECT item_id FROM item_qualities WHERE color='blue') UNION ALL (SELECT item_id FROM item_qualities WHERE size='90cm') UNION ALL (SELECT item_id FROM item_qualities WHERE flavor='peach'); Then you count up how often each item_id was matched: SELECT item_id, count(*) as frequency FROM tmp_relevance GROUP BY item_id; Could even modify that last step to check for a threshold of matching (say only those that match at least half of the terms you are looking for) with something that looks like this SELECT item_id, count(*) as frequency FROM tmp_relevance GROUP BY item_id HAVING frequency/(#of terms in the search) =0.5 ; Of course, you know the value of (# of terms in the search) because that's how many union queries you needed to run. You can improve on this technique in many ways. Here is one from the top of my head: Instead of returning only an item_id in the first query, you can also return a quality rating. Let's say you were looking for something sized 90cm and you only have 88cm pieces in stock, that may return a match quality code of 1-(abs(90-88)/90) You can combine that in the query against tmp_relevance to generate scores for near matches and not just exact partial matches. Does this give you some ideas to build on? -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump: Got error: 1449: The user specified as a definer ('root'@'%') does not exist when using LOCK TABLES
Hellpo Krishna,
On 10/19/2010 8:40 AM, Krishna Chandra Prajapati wrote:
Hi Pradhan,
Obviously, it should fail. Since you have deleted the root user which is
used by mysqldump for making connection to mysql server for taking backup
Not true. The utility mysqldump is just a client like any other program
and can authenticate with the MySQL instance (the database daemon) as
any valid user.
Krishna
CGI.COM
On Tue, Oct 19, 2010 at 11:06 AM, Tanmay Pradhantanma...@gmail.com wrote:
Hi,
I am using the following version of MySQL on my Mac OS X Server 10.5.8:
*** Ver 14.14 Distrib 5.4.1-beta, for apple-darwin9.5.0 (i386) using
readline 5.1 ***
In order to restrict root account login from localhost only, I did the
following:
mysql DELETE FROM user WHERE user = 'root' AND host = '%';
mysql FLUSH PRIVILEGES;
After this,
mysqldump failed with the following error:
$ /usr/local/mysql/bin/mysqldump -h localhost -u root ABC_DATABASE
abc.dump
mysqldump: Got error: 1449: The user specified as a definer
('root'@'%') does not exist when using LOCK TABLES
To fix this, you need to reset the DEFINER for a TRIGGER defined within
the database so that it is defined as a valid user account.
http://dev.mysql.com/doc/refman/5.1/en/triggers.html
Even following cmd failed:
$ /usr/local/mysql/bin/mysqldump -h host_name -u user1 -p
ABC_DATABASE abc.dump
mysqldump: Got error: 1045: Access denied for user 'user1'@'IP
Address' (using password: YES) when using LOCK TABLES
In order to issue the LOCK TABLES command, a user needs certain privileges:
http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html
Can anybody advise as how to make mysqldump work while restricting
root login access from localhost only?
Securing MySQL is fairly easy. Check out this guide in the manual for
details:
http://dev.mysql.com/doc/refman/5.1/en/security.html
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Incremental Backup Script
On 10/13/2010 9:18 AM, kranthi wrote: Hi Please be send sample incremental backup script (bash Shell script Easy to understand) Thanks Regards, Kranthikiran I think you missed the points of the previous replies. MySQL does not do incremental backups the the same way that other RDBMS products you may be familiar with. You can take full backups (all of the tables and all of the data) and partial backups (some of the tables or some of the data). With those, you can combine the contents of the Binary Log Files to provide yourself with the ability to perform a point-in-time-recovery (PITR). Which combination of backup techniques (and there are multiple techniques) you use depends on your hardware, software, and operational requirements. Please read the fine manual for more details: http://dev.mysql.com/doc/refman/5.1/en/backup-and-recovery.html -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Primary key not unique on InnoDB table
On 10/13/2010 10:37 AM, Tompkins Neil wrote: I've the following table. But why isn't the primary key unique, e.g. preventing duplicates if entered ? CREATE TABLE `players_master` ( `players_id` bigint(20) NOT NULL AUTO_INCREMENT, `default_teams_id` bigint(20) NOT NULL, `first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, `dob` date NOT NULL, `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL, `retirement_date` date DEFAULT NULL, `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL, `estimated_value` double NOT NULL DEFAULT '0', `contract_wage` double NOT NULL DEFAULT '0', `rating` int(11) NOT NULL, PRIMARY KEY (`players_id`,`default_teams_id`), KEY `FK_players_master_countries_id` (`countries_id`), KEY `FK_players_master_positions_id` (`positions_id`), KEY `IDX_first_name` (`first_name`), KEY `IDX_known_as` (`known_as`), KEY `IDX_second_name` (`second_name`), KEY `IDX_dob` (`dob`), KEY `IDX_estimated_value` (`estimated_value`), KEY `IDX_contract_wage` (`contract_wage`), KEY `IDX_rating` (`rating`), KEY `FK_players_master_teams_id` (`default_teams_id`), CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY (`countries_id`) REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY (`positions_id`) REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`) REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci I'm confused, I thought primary keys were always unique ? Cheers Neil I see no reason why this won't work. Show us some duplicate data and I may be able to explain how to fix your definition. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Primary key not unique on InnoDB table
On 10/13/2010 11:37 AM, Tompkins Neil wrote: Shawn, sorry my error, I didn't realise I had two fields as the primary key That's misinformation. You can have multiple fields as a primary key. Show us what you think is duplicate data and I may be able to help you fix your definition -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Design advice
On 10/8/2010 3:31 PM, Neil Tompkins wrote: Hi Shawn Thanks for your response. In your experience do you think I should still retain the data used to generate the computed totals ? Or just compute the totals and disregard the data used ? In my experience, the details matter. Also in my experience, as soon as you designate some bit of data as useless it will somehow become critical that you find it again. You should probably keep that lowest-level detail data somewhere safe even if you never plan to need it for direct statistics reporting after you use it to generate the first level or two of time-based summary tables. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Design advice
Hi Neil, On 10/5/2010 5:07 AM, Tompkins Neil wrote: Hi I have a number of tables of which I use to compute totals. For example I have table : players_master rec_id players_name teams_id rating I can easily compute totals for the field rating. However, at the end of a set period within my application, the values in the rating field are changed. As a result my computed totals would then be incorrect. Is the best way to overcome this problem to either compute the total and store as a total value (which wouldn't change in the future), or to store the rating values in a different table altogether and compute when required. If you need table information please let me know and I can send this. Many databases designed for rapid, time-based reporting do exactly as you propose: build a table just to hold the aggregate of a time-interval of values. Here's a rough example. Let's say that you run a web site and you want to track your traffic levels. Every second you may have thousands of hits, every hour hundreds of thousands of hits, and by the end of the week you may have hundreds of millions of individual data points to report on. To compute monthly stats, you are looking at a huge volume (billions) of data points unless you start aggregating. Lets say you build tables like: stats_hour, stats_day, stats_week, and stats_month. Every hour, you would take the last hour's worth of traffic and condense those values into the stats_hour table. At the end of the day, you take the previous 24 entries from stats_hour and compute a stats_day entry. Each level up aggregates the data from the level below. Does that give you an idea about how other people may have solved a similar problem? -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL DB Version
On 10/4/2010 12:32 PM, Tompkins Neil wrote: Account Number : uk600724 Dear Sir/Madam, The MySQL database version which you have supplied to us is version 5.0.77. However, it would appear that we require version to be at least 5.1.43. How can we get our MySQL db upgraded to this version or greater ? Regards Neil Tompkins If you are an official MySQL customer, log in your request to the support system at https://support.mysql.com/ If you need help with your account, email our administrative staff at support-feedb...@mysql.com Warmest regards, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: multiple aliases
On 9/27/2010 9:10 AM, Ramsey, Robert L wrote: I have a query with three subselects, all referencing the same table. I'd like to be able to combine them into one with aliases. Here's what I have now: select letter_codename, (select greek from letter_otherlanguages where letter ='A') as greek, (select french from letter_otherlanguages where letter ='A') as french, (select german from letter_otherlanguages where letter ='A') as german from intl_codes where letter='A'; I'd like to replace it with: select letter_codename, (select greek, french, german from letter_otherlanguages where letter ='A') as (greek, french, german) from intl_codes where letter='A'; Don't get hung up on the tables and structures, this is just a simple example. :) I want to use the three subselects because if I use a left join, the processing time goes from .4 to 5 seconds. Is this possible? Thanks! This should work - SELECT ic.letter_codename, lo.greek greek, lo.french french, lo.german german from intl_codes ic LEFT JOIN letter_otherlanguages lo on lo.letter = ic.letter WHERE ic.letter='A'; There should also be an index on both tables where `letter` is the leftmost element. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Access denied with mysqladmin
On 9/24/2010 4:11 AM, Ma Xiaoming wrote: Dear all, I have installed the MySQL version 5.1.50 with complete installation. After the installation process is finished and the configuration is done, when I run 'mysqladmin' with option 'version' in prompt, I got the following error message: -- C:\Program Files\MySQL\MySQL Server 5.1\binmysqladmin version mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'ODBC'@'localhost' (using password: NO)' -- Why do I get this result? Thanks. Best Regards Xiaoming You forgot to use -- before the option version. Try this instead mysqladmin --version Let us know your results. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: numbering the result set rows for insertion into another table
Hello Hank, On 9/18/2010 9:35 PM, Hank wrote: I have the following pseudo code running on mysql 4.x: set @cnt:=0; insert ignore into dest_table select t1.field1, t1.field2, t1.field3, t2.field1, t1.field3, t2.ts, @cnt:=...@cnt+1 from table1 as t1 left join table2 as t2 using (field1, field2) order by t2.ts; This works perfectly to sequentially number the result set rows inserted into dest_table in order of t2.ts (a timestamp field). In my upgrade to mysql 5.1.14-community, the numbers returned by @cnt are not in order... they trend upward from 0 to the number of records inserted, but they're far from in order... so somehow mysql is inserting the rows in some strange order. How can I fix this so it works in both mysql 4.x and 5.x? I am not sure you can fix this to work properly in a single statement for 5.1.14. The order of operations appears out of sequence to what you need. When executing an SQL statement, there are several stages to the processing. 1)gather rows and filter on matches (FROM ... and JOIN ...) 2)filter the results of 1 (WHERE) 3)apply any GROUP BY 4)filter the results of 3 (HAVING) 5)sort the results (ORDER BY) 6)window the results (LIMIT) It appears that computation of your @cnt variable is performed BEFORE the ORDER BY and not after the ORDER BY. This is completely in line with how the SQL Standard says a query should operate. What if you wanted to ORDER BY on the @cnt column and we did not compute it until after that stage of processing? That would break standards compatibility. To make this work the way you want, you need to create a temporary table with the results of your query sorted the way you want them. Then, query that temporary table and add your column of sequential numbers to the first results. There may possibly be a saving grace for you, though. 5.1.14 was a very early release in the 5.1 series. It is possible that someone else noticed the same problem and a later version may be operating as you want. We are currently releasing 5.1.50 which contains 34 rounds of bugfixes above and beyond your current 5.1.14. I suggest you upgrade and try again. Even if this does not fix the behavior to act as you want, the upgrade will at least remove your exposure to hundreds of identified bugs. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Update record count
On 9/16/2010 5:12 PM, Jerry Schwartz wrote:
I should be able to figure this out, but I'm puzzled. Here's a simplified
example:
UPDATE a JOIN b ON a.kb = b.kb JOIN c ON b.kc = c.kc
SET a.f1 = NOW(),
b.f2 = NOW()
WHERE c.f3 IN ('x', 'y', 'z')
AND b.f4 = 'yen';
It seems to me that if there are 3 rows found in `c` that match a total of 10
rows in `a` that each, in turn, matches 1 row in `b`, then the total number of
qualifying would be 10 + 10 - 20.
That should also be the number of rows changed.
Somehow the numbers reported by MySQL don't seem to match up in my real case,
even though the results seem to be what I want. The numbers reported were way
too high, and I don't understand it.
I can supply more information, if necessary, but have I gone off the rails
somehow?
Look at this like the database sees the problem:
SELECT a.f1, b.f2, c.f3, b.f4
FROM a JOIN b ON a.kb = b.kb JOIN c ON b.kc = c.kc
WHERE c.f3 IN ('x', 'y', 'z')
AND b.f4 = 'yen';
What you should be able to notice:
* Each matching row from a is combined with each matching row from b
* Each combination of (a,b) rows is combined with each matching row from c
So if 10 rows of A match your conditions, 1 row from B match your
conditions, and 10 rows from C match your conditions, then this query
produces 10*1*10 total row combinations.
That should explain why your numbers are higher than expected.
Regards,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replaying the mysqld.log file from production onto QA???
Hello Johan, On 9/10/2010 7:47 AM, Johan De Meersman wrote: On Thu, Sep 9, 2010 at 4:12 PM, Nunzio Daveri nunziodav...@yahoo.comwrote: So.. I am trying to mimic replaying production like queries so joins, temp tables etc... are stuff I am trying to test as well. Just doing a dump and import is no more than export and importing, I also want to test selects, updates :-) Thanks for replying :-) Then you'll have to first activate full logging on your production server. This may take quite some diskpace, and IOs, so keep the logfiles on separate spindles. Extracting the queries from the log shouldn't be all that hard; but there's too little timing information in there to do a time-true replay - and I don't know of any app that does that, either. He already did! Those are the logs he needs to replay. He has the logs already but needs tools to extract the commands and repeat them as a load test. Do you have any techniques you can share? -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: hard disk crash: how to discover the db?
On 9/10/2010 10:01 AM, george larson wrote: Uwe Brauer wrote: ... The only one I know of, for my environment, is /etc/my.cnf. I believe that it can be located elsewhere but you could just use 'find' to find it. I've broken my dev. MySQL many, many times and that's the only file I know about outside of my data directory. :) I don't have any good ideas about discerning precisely what version of MySQL was running, though. The error log will have the version information. Each successful startup includes something similar to 100910 7:50:30 [Note] mysqld: ready for connections. Version: '5.1.48-enterprise-gpl-advanced' socket: '' port: 3306 MySQL Enterprise Server - Advanced Edition (GPL) For more information on how to locate the error log: http://dev.mysql.com/doc/refman/5.1/en/error-log.html -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Fwd: Query SUM help
On 9/9/2010 3:57 AM, Tompkins Neil wrote: Any help would be really appreciated ? -- Forwarded message -- From: Tompkins Neil neil.tompk...@googlemail.com Date: Wed, Sep 8, 2010 at 5:30 PM Subject: Query SUM help To: [MySQL] mysql@lists.mysql.com Hi I've the following query : SELECT total_team_rating, my_teams_id FROM (SELECT players.teams_id AS my_teams_id, SUM(players_master.rating) AS total_team_rating FROM players INNER JOIN players_master ON players.players_id = players_master.players_id WHERE players.worlds_id = 1 AND players.red_cards = 0 AND players.injury_duration_remaining = 0 GROUP BY players.teams_id) s1 ORDER BY s1.total_team_rating DESC This gives me the total of players_master.rating for each players.teams_id. However, I'm wanting to only base the players_master.rating on the top 11 records in the players table for each team. How can I modify my query to achieve this ? Thanks Neil The meat of your problem is the top 11 players part. The SQL language operates on sets, not sequences. In order to find the top 11 you need to somehow identify them explicitly so that you can process them as a set of values. Do you have a column on your `players` table that sequences (from 1..n) the players in the order you want them ranked? If not, you will need to add that data to your `players` table (or build a temporary table with that information in it), then pick the top 11, then work on their SUM()-s. Can you not just filter out the top 11 in your client code from the query that includes all players totals? -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Conditional join of tow tables
Hi listers mysql show global variables like version; +---++ | Variable_name | Value | +---++ | version | 5.1.46 | +---++ 1 row in set (0.02 sec) mysql Following problem: Two tables which must be joined differently depending on the contents of the second table, the first table esentially contains a date field named datum. the second table is as follows: mysql describe schulung; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | sdat | date | YES | | NULL|| | tag | text | YES | MUL | NULL|| | szeit| time | YES | | NULL|| | speziell | text | YES | | NULL|| | id | int(10) unsigned | NO | PRI | NULL| auto_increment | +--+--+--+-+-++ 5 rows in set (0.00 sec) mysql Now, if the second table in the sdat field contains a value which is equivalent to the datum field in the first table (datum = sdat), then this join must be taken and nothing else. Otherwise the more general join via the tag field must be taken (dayname(datum) = tag). I tried to program this using not exists in the on clause of a join inner join schulung on (if not exists (select sdat from schulung where sdat = datum) then (datum = sdat)) else dayname(datum) = tag) but I got an ERROR 1064 near 'not exists (select sdat ' Probably, I have to re-structure the entire statement to an other form using other constructs? Has anyone had similar problems? How did you solve it then? Thank you very much. suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Conditional join of tow tables
Hi Travis Thank you for the hint. i yesterday found the following hint, which I then followed. select t1.datum, t2.sdat. t3.tag from table as t1 left outer join table2 as t2 on t1.datum = t2.sdat left outer join table2 as t3 on dayname(t1.datum) = t3.tag Note: it does not work with inner joins, you have to take outer joins. suomi On 2010-09-07 21:21, Travis Ard wrote: Does this work? select * from t1 join t2 on (t1.datum = t2.sdat or dayname(t1.datum) = t2.tag); -Travis -Original Message- From: mysql [mailto:my...@ayni.com] Sent: Tuesday, September 07, 2010 1:43 AM To: mysql@lists.mysql.com Subject: Conditional join of tow tables Hi listers mysql show global variables like version; +---++ | Variable_name | Value | +---++ | version | 5.1.46 | +---++ 1 row in set (0.02 sec) mysql Following problem: Two tables which must be joined differently depending on the contents of the second table, the first table esentially contains a date field named datum. the second table is as follows: mysql describe schulung; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | sdat | date | YES | | NULL|| | tag | text | YES | MUL | NULL|| | szeit| time | YES | | NULL|| | speziell | text | YES | | NULL|| | id | int(10) unsigned | NO | PRI | NULL| auto_increment | +--+--+--+-+-++ 5 rows in set (0.00 sec) mysql Now, if the second table in the sdat field contains a value which is equivalent to the datum field in the first table (datum = sdat), then this join must be taken and nothing else. Otherwise the more general join via the tag field must be taken (dayname(datum) = tag). I tried to program this using not exists in the on clause of a join inner join schulung on (if not exists (select sdat from schulung where sdat = datum) then (datum = sdat)) else dayname(datum) = tag) but I got an ERROR 1064 near 'not exists (select sdat ' Probably, I have to re-structure the entire statement to an other form using other constructs? Has anyone had similar problems? How did you solve it then? Thank you very much. suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: AW: Dup Key Error Messages
On 9/6/2010 9:10 AM, Thorsten Heymann wrote:
No, I think you misunderstood me. I have these keys (in this example)
- PRIMARY (id)
- UNIQUE (ip_addr)
- UNIQUE (print_name)
And when of of the UNIQUEs is duplicated, I'll wshow user a message WHAT key is
a doublette.
Actually, that was not what you posted above. In your original example
you have one primary key and one unique key. That unique key is based on
the combination of values {`ip_addr`,`print_name`}. This means that all
of these values pairs are unique
'192.168.1.1','queue1'
'192.168.1.1','queue2'
'192.168.2.1','queue1'
'192.168.2.1','queue2'
If I assume that each row was assigned a sequential ID value, then these
are rows 1..4 . If I attempted to modify row 1 like this
UPDATE `device` SET `print_name` = 'queue_2' where ID = 1;
Then I would hit a conflict. The combination of
{ip_addr='192.168.1.1', `print_name='queue2'}
already exists in the row where the ID value is 2.
Even if I parsed the error entire message, it would not tell me which
element of this UNIQUE key I violated, only that the combination already
exists and which constraint is protecting that combination.
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Performance problems on MySQL
On 9/3/2010 3:15 PM, Johnny Withers wrote: It seems that when your index is PRIMARY on InnoDB tables, it's magic and is part of the data thereby it is not included in the index_length field. I have never noticed this. I don't think adding a new index will make a difference. You could try moving your log files to a different disk array than where your data is. If you have binary and query logging enabled, it's probably a good idea. Johnny is correct. The PRIMARY KEY to an InnoDB table is indeed part of the data: http://dev.mysql.com/doc/refman/5.1/en/innodb-index-types.html That explains why there is no length to this index. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- 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 about VIEWS in 5.1.x
On 9/3/2010 6:23 AM, Jangita wrote: On 02/09/2010 8:30 p, Hank wrote: Simple question about views: I have a view such as: create view combo as select * from table1 union select * from table2; ... (I've also tried UNION ALL with the same results). ... Hank, I think mysql is selecting ALL the records from both tables then applying the where clause to all the data from table 1 and table 2 (I think - guys correct me if I'm wrong) ... Jangita is correct. Read the bottom of http://dev.mysql.com/doc/refman/5.1/en/view-algorithms.html -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Does innodb have a temp table space?
On 9/2/2010 1:39 PM, neutron wrote: Hello Johan, Thanks for the reply. On Thu, Sep 2, 2010 at 3:34 AM, Johan De Meersman vegiv...@tuxera.be wrote: I suspect he is talking about the Temp Tablespace concept from Oracle, which is different from a temporary table or a memory table. MySQL will allocate a memory table for sort operation and the like, up until that table exceeds a preset limit, at which point it will automatically (and costly !) be converted to a disk table. == How to define the memory table limit? When the temp table is converted to a disk table, where is this disk table stored? In the same shared tablespace file if I don't use innodb_file_per_table? The automatically-converted tables produced by the system as part of SQL command processing start off as MEMORY tables unless they contain data that the MEMORY storage engine does not support. If they do contain unsupported data types or if they exceed the size of the smaller of --max-heap-table-size or --tmp-table-size, then the table is converted to a MYISAM table. The folder for temporary tables is controlled by the --tmpdir parameter. This behavior and the configuration variables I discussed are covered in more detail in these links: http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html http://dev.mysql.com/doc/refman/5.1/en/temporary-files.html http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_tmpdir http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_max_heap_table_size http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_tmp_table_size -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Symlinks not working when pointing to another table.
On 9/2/2010 3:31 PM, Julien Lory wrote: Hello, I've done lot of researches and tests but can't find any answer. I need to share one table between two db, those two db are in the same path ( /var/lib/mysql/db1 db2 ). I created symbolic links for db2 pointing to the table in db1. When I query the table from db2 I get this error : 'ERROR 1030 (HY000): Got error 140 from storage engine' I'm sure it was working in previous version of MySQL, but from the last version it's no longer working. This is how it looks : test-lan:/var/lib/mysql/test3# ls -alh drwx-- 2 mysql mysql 4.0K 2010-08-30 13:28 . drwxr-xr-x 6 mysql mysql 4.0K 2010-08-30 13:29 .. lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.frm - /var/lib/mysql/test/blbl.frm lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.MYD - /var/lib/mysql/test/blbl.MYD lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.MYI - /var/lib/mysql/test/blbl.MYI -rw-rw 1 mysql mysql 65 2010-08-30 13:24 db.opt I really need those symlinks, is there a way to make them working like before ? ( old MySQL-server is fine ) You cannot share one set of files (one table) between two different MySQL instances. That also means that you cannot share a table between the same instance by pretending it's something else by a symlink. The storage engines are designed with the premise that they have exclusive domain over the files they manage. Sharing is not part of their code. If you need to expose a table from within a different database, you must use a view. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Database design help
On 9/1/2010 11:47 AM, Tompkins Neil wrote: I do have a tabled which contains both the managers_id and teams_id for the current teams managed. I think by adding the managers_id alongside the fixture_result table will then allow me to find which points the manager has accumulated alongside which fixtures and teams. Cheers Neil On Wed, Sep 1, 2010 at 4:43 PM, Jerry Schwartz je...@gii.co.jp wrote: I strongly suggest that you make a separate table for the manager - team relationship, so you can keep a history. Put a date-stamp in there. This might come in handy as you get further into your design. I ran into this problem when one of our sales reps moved from one office to another, and took their sales history with them! That was a mess to unscramble. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: Neil Tompkins [mailto:neil.tompk...@googlemail.com] Sent: Tuesday, August 31, 2010 3:48 PM To: mysql@lists.mysql.com Subject: Database design help Hi I've a soccer application consisting of managers, teams players and fixtures/results. Basically each manager will get points for each game which will depend on the result. What would be the best table design bearing in mind that a manager can move to a different club. My thought was to have a field in the fixtures/results table for the manager points but i think that I will also need a users field so that I can remember which points belong to which manager. Is this the correct approach?? I think you are definitely on the right track. Each score does not belong to just a manager or to a team but to a manger/team combination. Should the manager switch teams, those results need to remain associated to both entities not just the manager. here's a possible record shape: manager_id, team_id, game_id, ... summary details about the game ... This way each result is associated with the correct combination of entities (a manager and a team) and not just one or the other. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
collation problems
Hi listers mysql server here is mysql-server-5.1.48-2.fc13.x86_64 this morning i created a message with a literal string in chinese in it. the messages in the application i used are stored in a mysql database, when you submit them, like in a sent folder. With this chinese literal in it, i, however, got ERROR 1271 (HY000): Illegal mix of collations for operation 'concat' when i sent the message. without the chinese literal, the message was stored in the mysql db correctly. i, then, changed the connection names to utf8 and collation to utf8_bin and then the session parameters looked like: mysql set names utf8; Query OK, 0 rows affected (0.00 sec) mysql set collation_connection = utf8_bin; Query OK, 0 rows affected (0.00 sec) mysql show session variables like character_set%; +--++ | Variable_name| Value | +--++ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results| utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--++ 8 rows in set (0.00 sec) mysql show session variables like collation%; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | utf8_bin | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--+---+ 3 rows in set (0.00 sec) mysql i thought, if you set the collation_connection to utf8_bin, you may send fairly everything to mysql. but still, when i tried to concat the new string (including the chinese characers) i got: mysql update suomi_contacts2 set history = concat(now(), ' ', '' ,'concerne: utf-8-bin collation for chinese charset',' ', 'Hoi Suomixer,\r\n\r\nIf you\'re careful enough, nothing bad or good will ever happen to you.\r\n 葛斯克 愛德華 / 台北市八德路四段\r\n\r\n\r\n \r\nsuomi\r\n', ' ', '--- ', history) where counter = 1127; ERROR 1271 (HY000): Illegal mix of collations for operation 'concat' Question: What did i miss? what do i have to change? Thanks in advance suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: collation problems
Hi Ananda table structure is: mysql show full columns from suomi_contacts2; +--+--+---+--+-+---+-+-+-+ | Field| Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +--+--+---+--+-+---+-+-+-+ | name | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | firm | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | title| text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | phone| text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | phone_std| text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | fax | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | mail | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | standard_mail| text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | comment | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | status | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | url | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | businesscategory | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | address | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | addon| text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | givenname| text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | history | longtext | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | favorit | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | last_update | timestamp| NULL | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | select,insert,update,references | | | task_link| int(11) | NULL | YES | | NULL | | select,insert,update,references | | | counter | int(10) unsigned | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | | +--+--+---+--+-+---+-+-+-+ 20 rows in set (0.00 sec) mysql suomi On 2010-08-31 14:52, Ananda Kumar wrote: can u please list out the table structure...as collation can also be set at column level regards anandkl On Tue, Aug 31, 2010 at 6:00 PM, mysql my...@ayni.com mailto:my...@ayni.com wrote: Hi listers mysql server here is mysql-server-5.1.48-2.fc13.x86_64 this morning i created a message with a literal string in chinese in it. the messages in the application i used are stored in a mysql database, when you submit them, like in a sent folder. With this chinese literal in it, i, however, got ERROR 1271 (HY000): Illegal mix of collations for operation 'concat' when i sent the message. without the chinese literal, the message was stored in the mysql db correctly. i, then, changed the connection names to utf8 and collation to utf8_bin
Re: collation problems
On 2010-08-31 15:17, Ananda Kumar wrote: desc suomi_contacts2; mysql desc suomi_contacts2; +--+--+--+-+---+-+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+---+-+ | name | text | YES | | NULL | | | firm | text | YES | | NULL | | | title| text | YES | | NULL | | | phone| text | YES | | NULL | | | phone_std| text | YES | | NULL | | | fax | text | YES | | NULL | | | mail | text | YES | | NULL | | | standard_mail| text | YES | | NULL | | | comment | text | YES | | NULL | | | status | text | YES | | NULL | | | url | text | YES | | NULL | | | businesscategory | text | YES | | NULL | | | address | text | YES | | NULL | | | addon| text | YES | | NULL | | | givenname| text | YES | | NULL | | | history | longtext | YES | | NULL | | | favorit | text | YES | | NULL | | | last_update | timestamp| NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | task_link| int(11) | YES | | NULL | | | counter | int(10) unsigned | NO | PRI | NULL | auto_increment | +--+--+--+-+---+-+ 20 rows in set (0.00 sec) mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: collation problems
Hi Ananda not sofar. But if you recommend it, i will give it a try. thanks so much. suomi On 2010-08-31 15:41, Ananda Kumar wrote: did u try changing the collation for history column to UTF8 and try the update. 2010/8/31 mysql my...@ayni.com mailto:my...@ayni.com On 2010-08-31 15:17, Ananda Kumar wrote: desc suomi_contacts2; mysql desc suomi_contacts2; +--+--+--+-+---+-+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+---+-+ | name | text | YES | | NULL | | | firm | text | YES | | NULL | | | title| text | YES | | NULL | | | phone| text | YES | | NULL | | | phone_std| text | YES | | NULL | | | fax | text | YES | | NULL | | | mail | text | YES | | NULL | | | standard_mail| text | YES | | NULL | | | comment | text | YES | | NULL | | | status | text | YES | | NULL | | | url | text | YES | | NULL | | | businesscategory | text | YES | | NULL | | | address | text | YES | | NULL | | | addon| text | YES | | NULL | | | givenname| text | YES | | NULL | | | history | longtext | YES | | NULL | | | favorit | text | YES | | NULL | | | last_update | timestamp| NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | task_link| int(11) | YES | | NULL | | | counter | int(10) unsigned | NO | PRI | NULL | auto_increment | +--+--+--+-+---+-+ 20 rows in set (0.00 sec) mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=anan...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
[SOLVED] Re: collation problems
Hi Ananda that worked fine: mysql alter table suomi_contacts2 modify history longtext character set utf8 collate utf8_bin; Query OK, 6327 rows affected (0.34 sec) Records: 6327 Duplicates: 0 Warnings: 0 mysql mysql update suomi_contacts2 set history = concat(now(), ' ', '' ,'concerne: utf-8-bin collation for chinese charset',' ', 'Hoi Suomixer,\r\n\r\nIf you\'re careful enough, nothing bad or good will ever happen to you.\r\n 葛斯克 愛德華 / 台北市八德路四段\r\n\r\n\r\n\r \nsuomi\r\n044 280 22 44\r\n079 239 29 01\r\n', ' ', '--- ', history) where counter = 1127; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql Thank you very much suomi On 2010-08-31 15:41, Ananda Kumar wrote: did u try changing the collation for history column to UTF8 and try the update. 2010/8/31 mysql my...@ayni.com mailto:my...@ayni.com On 2010-08-31 15:17, Ananda Kumar wrote: desc suomi_contacts2; mysql desc suomi_contacts2; +--+--+--+-+---+-+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+---+-+ | name | text | YES | | NULL | | | firm | text | YES | | NULL | | | title| text | YES | | NULL | | | phone| text | YES | | NULL | | | phone_std| text | YES | | NULL | | | fax | text | YES | | NULL | | | mail | text | YES | | NULL | | | standard_mail| text | YES | | NULL | | | comment | text | YES | | NULL | | | status | text | YES | | NULL | | | url | text | YES | | NULL | | | businesscategory | text | YES | | NULL | | | address | text | YES | | NULL | | | addon| text | YES | | NULL | | | givenname| text | YES | | NULL | | | history | longtext | YES | | NULL | | | favorit | text | YES | | NULL | | | last_update | timestamp| NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | task_link| int(11) | YES | | NULL | | | counter | int(10) unsigned | NO | PRI | NULL | auto_increment | +--+--+--+-+---+-+ 20 rows in set (0.00 sec) mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=anan...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: master-slave replication sync problems.
Hello List, On 8/26/2010 3:00 PM, Daevid Vincent wrote: ssh to the slave mysql -uroot -pPASSWORD -P3306 -hlocalhost show slave status\G If the Slave IO is NOT Running, but SQL is, then simply try to restart the slave... *** 1. row *** Slave_IO_State: Master_Host: 10.10.10.45 Master_User: slave Master_Port: 3306 ... Slave_IO_Running: No Slave_SQL_Running: Yes start slave; show slave status\G Otherwise if it's a replication issue such as this, then you must skip over the bad SQL queries one at a time till the slave syncs. *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 10.10.10.41 Master_User: slave Master_Port: 3306 ... Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: agis_core_2008 Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1061 Last_Error: Error 'Duplicate key name 'id_operator'' on query. The SQL statement will give you an idea of where the master and slave went askew. If these are recent commands you did you can guess as to how much to increment the SKIP_COUNTER below, otherwise, you just have to do it one at a time until they sync again. mysql stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; show slave status\G Repeat the above statements over and over until you see two YES rows. *** 1. row *** Slave_IO_Running: Yes Slave_SQL_Running: Yes -Original Message- From: Norman Khine [mailto:nor...@khine.net] Sent: Thursday, August 26, 2010 6:05 AM To: mysql@lists.mysql.com Subject: master-slave replication sync problems. hello, i have a working master-slave replication, the problem i find is that if i restart the MASTER there is a difference in the MASTER_LOG_FILE and MASTER_LOG_POS on the SLAVE. what is the correct way to keep the two slaves in sync even after i restart the server. the way i do it now is to: [MASTER] mysql show master status; +--+--+--+ --+ | mysql-bin.10 | 13405429 | upgrade,tracker,bugs | mysql,information_schema | +--+--+--+ --+ 1 row in set (0.00 sec) [SLAVE] mysql stop slave; mysql change master to MASTER_HOST='master.domain.com', MASTER_USER='replicator', MASTER_PASSWORD='xxx', MASTER_LOG_FILE='mysql-bin.10', MASTER_LOG_POS=13301215; mysql start slave; is this correct or is there a better way to do this? thanks To me, it appears that many of you are not fully versed in the theory of operations for how MySQL replication actually functions. Granted, there are two formats for replication (ROW and STATEMENT) but the general process remains the same. Here is a nutshell summary of the process. ** on the master ** m1) The MySQL master is instructed to change some data. m2) The data is changed and the results are committed to disk. If you rollback the changes before you commit them, then there is nothing to replicate. Only the InnoDB engine supports this type of rollback. m3) The change committed in step 2 is written to the binary log (repeat from step m1 until the Master is shutdown) ** on the slave - the IO thread ** (assuming that the slave is already configured with compatible data, a starting position, and the proper credentials to act as a slave) o1) The SLAVE IO thread requests information from the master's binary logs. This information is identified by a file name and a byte offset from the start of that file o2) The SLAVE IO thread copies all available information from the master's binary logs into a local copy of those logs known as the relay logs. (repeat from o1 until the SLAVE IO thread is stopped(by error or by command) or the slave is shutdown) ** on the slave - the SQL thread ** s1) Once an unapplied change has been completely buffered into the relay logs, the SLAVE SQL thread attempts to apply the change to the slave's data. s2) If LOG SLAVE UPDATES is enabled, copy the applied change (using the correct format) into the slave's binary log. (repeat from s1 until the SLAVE SQL thread is stopped (by error or by command) or the slave is shutdown) ** As you can tell by this very simplified process description, there is no attempt to rectify one dataset to the other. Replication operates under the principle that if you perform identical changes to identical sets of data, you will end up with identical end results. Various replication filters can omit certain changes
Re: Seems like an easy query, but isn't to me. Help?
On 8/19/2010 8:45 PM, George Larson wrote: I hope I've come to right place, and I'm asking in the right way -- please accept my apologies if not. We have some dates missing and I need to populate those fields with dates from the record just before them. I've gotten this far: SELECT UUid, MIN(DDenteredDate) minDate FROM UUtable JOIN DDdetail on DDid = UUid WHERE UUdate IS NULL GROUP BY UUid; I can make this a sub-query and get the UUid of the record that I want to copy UUdate from: SELECT sub.UUid-1 as previous, sub.* FROM ( SELECT UUid, MIN(DDenteredDate) minDate FROM UUtable JOIN DDdetail on DDid = UUid WHERE UUdate IS NULL GROUP BY UUid; ) as sub; In this case, the field 'previous' is the UUid that I want to copy the UUdate from and sub.UUid is where I want to copy to. Does that even make sense? As you discovered, the SQL language is not an ordinal, procedural language. It is a SET-oriented language. The sequence of rows in any one set of results completely depends on either how those rows were isolated from the table(s) on which they reside (random) or by an ORDER BY or similar secondary processing step. Without an ORDER BY, it is perfectly legal for the same query to return the same set of rows in completely different sequences for queries that are executed one immediately after the other. If you want to say the record just before when referring to SQL data and have it mean anything, you must be specific about how you are sequencing your rows. Only then do the concepts of before and after have any meaning. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Server has gone away
On 8/19/2010 11:07 AM, jitendra ranjan wrote: Here is few lines from log: 100703 22:12:48 mysqld ended 100703 22:23:39 mysqld started 100703 22:23:40 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295 100703 22:23:40 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295 100703 22:23:40 [Warning] option 'thread_cache_size': unsigned value 33554432 adjusted to 16384 100703 22:23:41 InnoDB: Started; log sequence number 0 44054 100703 22:23:41 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=mysqld-relay-bin' to avoid this problem. 100703 22:23:41 [ERROR] Failed to open the relay log '/var/run/mysqld/mysqld-relay-bin.01' (relay_log_pos 4) 100703 22:23:41 [ERROR] Could not find target log during relay log initialization 100703 22:23:41 [ERROR] Failed to initialize the master info structure 100703 22:23:41 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.0.77-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution 100710 22:28:32 [Note] /usr/libexec/mysqld: Normal shutdown 100710 22:28:34 InnoDB: Starting shutdown... 100710 22:28:36 InnoDB: Shutdown completed; log sequence number 0 44054 100710 22:28:36 [Note] /usr/libexec/mysqld: Shutdown complete 100710 22:28:36 mysqld ended 100711 01:42:09 mysqld started 100711 1:42:10 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295 100711 1:42:10 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295 100711 1:42:10 [Warning] option 'thread_cache_size': unsigned value 33554432 adjusted to 16384 100711 1:42:10 InnoDB: Started; log sequence number 0 44054 100711 1:42:11 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=mysqld-relay-bin' to avoid this problem. 100711 1:42:11 [ERROR] Failed to open the relay log '/var/run/mysqld/mysqld-relay-bin.01' (relay_log_pos 4) 100711 1:42:11 [ERROR] Could not find target log during relay log initialization 100711 1:42:11 [ERROR] Failed to initialize the master info structure 100711 1:42:11 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.0.77-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution 100726 9:37:14 [Warning] Warning: Enabling keys got errno 137 on reachout.#sql-d4d_23af19, retrying 100804 10:48:04 [ERROR] /usr/libexec/mysqld: Can't find file: './reachout/tbl_customer_reachout_new.frm' (errno: 13) 100804 10:48:04 [ERROR] /usr/libexec/mysqld: Can't find file: './reachout/tbl_customer_reachout_new.frm' (errno: 13) 100804 10:48:05 [ERROR] /usr/libexec/mysqld: Can't find file: './reachout/tbl_customer_reachout_new.frm' (errno: 13) 100804 10:48:05 [ERROR] /usr/libexec/mysqld: Can't find file: './reachout/tbl_customer_reachout_new.frm' (errno: 13) 100804 10:54:17 [ERROR] /usr/libexec/mysqld: Can't find file: './reachout/tbl_customer_reachout_new.frm' (errno: 13) 100804 10:54:17 [ERROR] /usr/libexec/mysqld: Can't find file: './reachout/tbl_customer_reachout_new.frm' (errno: 13) 100804 10:54:20 [ERROR] /usr/libexec/mysqld: Can't find file: './reachout/tbl_customer_reachout_new.frm' (errno: 13) 100804 10:54:20 [ERROR] /usr/libexec/mysqld: Can't find file: './reachout/tbl_customer_reachout_new.frm' (errno: 13) 100804 10:54:34 [ERROR] /usr/libexec/mysqld: Can't find file: './reachout/tbl_customer_reachout_new.frm' (errno: 13) 100804 10:54:34 [ERROR] /usr/libexec/mysqld: Can't find file: './reachout/tbl_customer_reachout_new.frm' (errno: 13) 100813 19:04:51 [Note] /usr/libexec/mysqld: Normal shutdown 100813 19:04:54 InnoDB: Starting shutdown... 100813 19:04:59 InnoDB: Shutdown completed; log sequence number 0 44054 100813 19:04:59 [Note] /usr/libexec/mysqld: Shutdown complete 100813 19:04:59 mysqld ended 100813 19:07:46 mysqld started 100813 19:07:46 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295 100813 19:07:46 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295 100813 19:07:46 [Warning] option 'thread_cache_size': unsigned value 33554432 adjusted to 16384 100813 19:07:46 InnoDB: Started; log sequence number 0 44054 100813 19:07:47 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=mysqld-relay-bin' to avoid this problem. 100813 19:07:47 [ERROR] Failed to open the relay log '/var/run/mysqld/mysqld-relay-bin.01' (relay_log_pos 4) 100813 19:07:47 [ERROR] Could not find target log during relay log initialization 100813 19:07:47 [ERROR] Failed to initialize the master info structure 100813 19:07
Re: How to use SSL? (SSL is enabled but not used)
On 8/17/2010 6:02 PM, Anders Kaseorg wrote: On Wed, 2010-08-11 at 14:23 -0400, Shawn Green (MySQL) wrote: On 8/9/2010 5:27 PM, Yves Goergen wrote: What's that supposed to mean? If there's no way to force the connection into SSL, it is entirely useless. Anyone on the wire could simply pretend that the server doesn't support SSL and so deny the encryption and the client wouldn't even care... If you don't want to require SSL on the local connections then don't set the flag on the @localhost account. If you want the SSL required on the other connections, then set it on the @'...' version of the account that the remote users login through. Excuse me, but isn’t Yves exactly right here? None of the client-side options (I tried --ssl, --ssl-ca=…, --ssl-verify-server-cert, --ssl-key=…, --ssl-cipher=…) can currently be used to force an SSL connection to be used. And requiring SSL from the server side does nothing to stop man-in-the-middle attacks. (Suppose Bob the SQL server grants some privileges to Alice the user with SSL required. Now Alice can log in with her password over SSL and gets denied over non-SSL. Great. But now Mallory comes along and intercepts a connection from Alice intended for Bob. Even if Bob would have claimed that he requires SSL, nothing stops Mallory from claiming that she doesn’t require SSL. Because Alice cannot force the use of SSL from the client side, Alice will make a successful unencrypted connection to Mallory. Then Mallory can accept the connection, ignoring Alice’s authentication, and steal Alice’s data; or Mallory can make a separate SSL connection to Bob, forward Alice’s authentication over it, then take over and issue evil commands to Bob.) This same issue was reported back in 2004 and ignored: http://bugs.mysql.com/bug.php?id=3138 I think this is a serious security problem that demands more attention than dismissal as documented behavior. To solve it, there needs to be a way to force the use of SSL from the client side. Anders If the server specifies REQUIRES SSL then that client cannot connect without going through the full SSL validation process. This means that Mallory would need to present the same security credentials that Alice has in order to qualify as a secure user (the same certs, same password, login from the correct host, etc). Your redirect has pointed out to me what I missed in Yves's first post. In order for the client to require an SSL connection, you have to designate a certificate for it to use for the connection. From the same page but a few lines above the line he quoted ## This option is not sufficient in itself to cause an SSL connection to be used. You must also specify the --ssl-ca option, and possibly the --ssl-cert and --ssl-key options. ## So you can see that using just the --ssl option by itself is not specific enough to designate which certificate a client is designated to use. You can specify these options either on the command line or in the configuration file used by the client (put them in the [mysql] section). In your simulated MITM attack, if Alice was using the specific --ssl-* options on her connection attempt, then Mallory would need to hold the server-side equivalents in order to pose as the server. Since the likelihood of this is small (except in the case of a physical hack of the server's cert files which no amount of digital manipulation can really avoid) then Mallory would be unable to pose as Bob and the intercept would fail. Does this help close the security hole you perceived in our SSL implementation? -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to use SSL? (SSL is enabled but not used)
On 8/17/2010 6:13 PM, Yves Goergen wrote: ... snip ... (Oh look, the MySQL guy already has an oracle.com e-mail address...) And for a for about two years before that, I had a sun.com email address, too. MySQL has not been an independent company for quite a while. Google it if you don't believe me. Sun made many headlines when they bought MySQL for one billion (10) US Dollars. I still work for MySQL (the combined products) even if there no longer is a MySQL, Inc. (the company). -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to use SSL? (SSL is enabled but not used)
On 8/18/2010 2:22 PM, Anders Kaseorg wrote: On Wed, 18 Aug 2010, Shawn Green (MySQL) wrote: If the server specifies REQUIRES SSL then that client cannot connect without going through the full SSL validation process. This means that Mallory would need to present the same security credentials that Alice has in order to qualify as a secure user (the same certs, same password, login from the correct host, etc). Mallory got the username and hashed password from Alice over the unencrypted connection, and we assume that Mallory, like any good MITM, has the ability to intercept and forge traffic for arbitrary hosts. So this attack goes through against anyone using passwords over SSL. This already constitutes a vulnerability. Setting up client certificates does help to prevent this form of attack where Mallory tries to issue evil commands to Bob. It does not, however, prevent the attack where Mallory ignores Bob, and uses only the unencrypted connection to steal data from Alice or poison her with false data. This also constitutes a vulnerability, which, as far as I can see, cannot be prevented in any way with the current MySQL software. Your redirect has pointed out to me what I missed in Yves's first post. In order for the client to require an SSL connection, you have to designate a certificate for it to use for the connection. No, that doesn’t work either! Against a server with SSL disabled: $ mysql --ssl --ssl-verify-server-cert \ --ssl-ca=/etc/ssl/certs/ca-certificates.crt \ --ssl-cert=Private/andersk.pem \ --ssl-key=Private/andersk.pem \ -h MY-SERVER Welcome to the MySQL monitor. Commands end with ; or \g. … mysql \s -- mysql Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (x86_64) using readline 6.1 … SSL:Not in use From the same page but a few lines above the line he quoted ## This option is not sufficient in itself to cause an SSL connection to be used. You must also specify the --ssl-ca option, and possibly the --ssl-cert and --ssl-key options. ## This documentation appears to be wrong. Anders Excellent logic. I have updated bug #3138 with a private comment to explain your presentation of the vulnerability. http://bugs.mysql.com/bug.php?id=3138 -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: idle query
On 8/12/2010 2:32 PM, Mike Spreitzer wrote: I also find that if I have both tables in MyISAM and use STRAIGHT_JOIN to force the better query plan (enumerate the longer table, for each longer table row use the shorter table's index to pick out the one right matching row from the shorter table) then the server has low I/O utilization but the CPU utilization is about as high as can be expected for a single query running on a 16-CPU machine. Why should this thing be CPU-bound? Here is the query: create table fp2 (p VARCHAR(200) NOT NULL, rcd DATETIME NOT NULL, rcms SMALLINT NOT NULL, msgid BIGINT NOT NULL, q VARCHAR(200) NOT NULL, scd DATETIME NOT NULL, scms SMALLINT NOT NULL, lat DECIMAL(14,3), INDEX p(p), INDEX q(q) ) AS SELECT fldrcv.p, fldrcv.cd as rcd, fldrcv.cms as rcms, fldrcv.msgid as msgid, fldrcv.q, fldsnd.cd as scd, fldsnd.cms as scms, TIMESTAMPDIFF(SECOND, fldsnd.cd, fldrcv.cd) + (fldrcv.cms-fldsnd.cms)/1000 as lat FROM fldrcv STRAIGHT_JOIN fldsnd ON fldrcv.q=fldsnd.p AND fldrcv.qboot=fldsnd.pboot AND fldrcv.msgid=fldsnd.msgid; and here is some `iostat -x 5` output that shows a total of less than 50% I/O utilization and about 15/16 CPU utilization: ... You are doing a lot of index work which requires a lot of memory manipulation. You are populating two on the new table while using at least one to build your data. I believe it's that random accesss memory work that's chewing up a big chunk of your CPU time. Does it work better if you delay the index creation of your temporary table until after the table is populated? CREATE TABLE fp2 ... SELECT ... ; ALTER TABLE fp2 ADD KEY p(p),KEY q(q); -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to use SSL? (SSL is enabled but not used)
On 8/9/2010 5:27 PM, Yves Goergen wrote: Does anybody know how to use SSL-secured connections to a MySQL server? Has anybody done that at all? In the manual I have now found the following statement: http://dev.mysql.com/doc/refman/5.1/en/ssl-options.html Note that use of --ssl does not require an SSL connection. For example, if the server or client is compiled without SSL support, a normal unencrypted connection is used. What's that supposed to mean? If there's no way to force the connection into SSL, it is entirely useless. Anyone on the wire could simply pretend that the server doesn't support SSL and so deny the encryption and the client wouldn't even care... I don't want to use REQUIRE SSL for an account that is regularly used locally and doesn't need SSL. SSL should really be selected by the client per connection when connecting from some other untrusted network. The whole SSL thing looks pretty unfinished like that. If you don't want to require SSL on the local connections then don't set the flag on the @localhost account. If you want the SSL required on the other connections, then set it on the @'...' version of the account that the remote users login through. All MySQL accounts are tripartate. They consist of: 1) a login (user), 2) a host designation (or wildcard pattern), and 3) a password. Use that host portion of the account to make the distinction between local and remote logins. More about how the MySQL authentication works is available in the manual: http://dev.mysql.com/doc/refman/5.1/en/user-account-management.html -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
