Re: converting numeric to date-time?
* Rajeev Prasad rp.ne...@yahoo.com [2014-09-01 17:55]: I have a column in a table which is epoch time including milliseconds. e.g. = 1409304102153 now i want to display all fields in the table but this field as: 2014-8-29 Fri 09:21:42: GMT (whatever comes in ) and i am not finding anything on web about how to do that. can anyone help please. ty. Rajeev I do not know how to do it directly in MySQL, but if you can dump the table and post-process, this may be helpful on Linux: The date(1) cmd can translate between formats, e.g. (taking the above value), date -d @1409304102.153 +%Y-%m-%d %a %H:%M:%S.%N 2014-08-29 Fri 04:21:42.15300 --Phil -- Philip Amadeo Saeli openSUSE, CentOS, RHEL psa...@zorodyne.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: function to limit value of integer
log(2, no_of_jobs + 1) will give 0 for 0 jobs, 1 for 1 job, 1.58 for 2 etc. etc. On 11 Feb 2011, at 14:04, Johan De Meersman wrote: How about the square root of the number of jobs, or some other root if you want another coefficient? That doesn't have the limiting behaviour a logarithmic function offers, though. On Fri, Feb 11, 2011 at 2:08 PM, Richard Reina gatorre...@gmail.com wrote: Hi Travis, This is very helpful thank you. However, is there a way to make it not be less than a 1. As it's written below someone with one job gets a zero and someone with no jobs gets a NULL. It would be great if someone with 1 job got a 1 and someone with zero jobs got a 0. Thanks again, Richard 2011/2/10 Travis Ard travis_...@hotmail.com Maybe some sort of logarithmic expression? select no_of_jobs, 10 * log(10, no_of_jobs) as job_weight from data; Of course, you'd have to tweak your coefficients to match the weighting system you want to use. -Travis -Original Message- From: Richard Reina [mailto:gatorre...@gmail.com] Sent: Thursday, February 10, 2011 3:07 PM To: mysql@lists.mysql.com Subject: function to limit value of integer Is there a function that can limit the value of an integer in a MySQL query? I am trying to write a query that scores someones experience. However, number of jobs can become overweighted in the the query below. If someone has done 10 jobs vs. 1 that's a big difference in experience. But someone who's done 100 vs. someone who's done 50 the difference in experience is not so great as they are both near the top of the learning curve. In essence number of jobs becomes less and less of a contributor as it increases. Is there a way to limit it's value as it increases? SELECT years_srvd + no_of_jobs AS EXPERIENCE Thanks, Richard -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- TTFN. Philip Riebold, p.rieb...@ucl.ac.uk /\ Media Services\ / University College London X ASCII Ribbon Campaign Windeyer Building, 46 Cleveland Street/ \ Against HTML Mail London, W1T 4JF +44 (0)20 7679 9259 (direct), 09259 (internal) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SQL book recommendation?
On 28 Oct 2010, at 18:45, Paul DuBois wrote: On Oct 26, 2010, at 6:31 AM, Philip Riebold wrote: On 26 Oct 2010, at 11:49, MikeB wrote: I'm finding the MySQL online manuals hard going in figuring out how to construct SQL queries. Can anyone perhaps recommend a good book that can shed light on the subject? Thanks. The book I've been using is 'MySQL, The definitive guide to using, programming, and administering MySQL 4.1 and 5.0' ISBN 0-672-32673-6 (there may be a more recent version). If that's my book, it sounds like the third edition. The fourth edition is more recent. http://www.kitebird.com/mysql-book/ Yes, I've just checked I have the third edition. I'll probably keep my copy (I'm not a particularly heavy user of MySQL) but would recommend anybody looking for a book to get the 4th edition rather than the 3rd -- TTFN. Philip Riebold, p.rieb...@ucl.ac.uk /\ Media Services\ / University College London X ASCII Ribbon Campaign Windeyer Building, 46 Cleveland Street/ \ Against HTML Mail London, W1T 4JF +44 (0)20 7679 9259 (direct), 09259 (internal) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SQL book recommendation?
On 26 Oct 2010, at 11:49, MikeB wrote: I'm finding the MySQL online manuals hard going in figuring out how to construct SQL queries. Can anyone perhaps recommend a good book that can shed light on the subject? Thanks. The book I've been using is 'MySQL, The definitive guide to using, programming, and administering MySQL 4.1 and 5.0' ISBN 0-672-32673-6 (there may be a more recent version). Well written, with a general introduction to SQL and (from my POV) very good sections on writing MySQL with C and PHP -- TTFN. Philip Riebold, p.rieb...@ucl.ac.uk /\ Media Services\ / University College London X ASCII Ribbon Campaign Windeyer Building, 46 Cleveland Street/ \ Against HTML Mail London, W1T 4JF +44 (0)20 7679 9259 (direct), 09259 (internal) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Reduce dataset but still show anomalies
On 20 Aug 2010, at 16:24, Bryan Cantwell wrote: Yes, but I DON'T want eh spikes smoothed out Display the max and min of each successive set of 10 (or 100 or 1000) elements from the data ? -- TTFN. Philip Riebold, p.rieb...@ucl.ac.uk -- 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 Encryption - Third-party tools
On Mon, 24 Aug 2009, Mike Scully wrote: Hello, all. =20 Can any of you share with me the names of any third-party tools or appliances that you are using to encrypt your MySQL databases? I am doing a search and would like to narrow down the initial search list. Thanks! =20 Mike I use ccrypt from http://ccrypt.sourceforge.net to encrypt databases before storing them on removable media for offsite storage. Platform is a Sun Ultra 45 running Solaris 10 and the command looks something like, mysqldump db_name | bzip2 | ccrypt -e -k keyfile | ... (Note the bzip2 in the pipeline. This reduces the size of the files by a factor of between 5-8.) It's certainly more than fast enough for my needs. I suspect most of the time is taken up by mysqldmp and writing the ouput to the external media. -- TTFN Philip Riebold, p.rieb...@ucl.ac.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Algorithm for resolving foreign key dependencies?
Hi, First of all, I apologise in advance for any mind-altering, or headache-inducing effects this question may have. I've spent the past two days trying to figure it out, and all I've got to show for it is a mostly-working recursive depth-first-search routine and an empty packet of painkillers. MySQL version: 5.0.67-0ubuntu6 I'm trying to write a code generator (in Python) that reads in a MySQL database, enumerates all the tables, then produces INSERT, DELETE and UPDATE code in PHP. The INSERT and UPDATE code generation was fairly easy, and works quite well. What I'm having trouble with is the DELETE code generator -- more specifically, resolving foreign key references. Basically, what I have is a tree built in memory, so I can go: tableinfo['thetable']['fieldname']['refs'] And get a complete list of all the tables (and the fields within that table) that reference 'fieldname' in 'thetable'. What I want is an answer to the question: If all my foreign keys were set to 'ON DELETE CASCADE', what would I need to do to delete row 'X' in table 'Y' without violating any foreign key constraints? Here's an example. Let's say I've got these tables: CREATE TABLE `Manufacturers` ( `idManufacturer` int(11) NOT NULL auto_increment, `name` varchar(255) NOT NULL, PRIMARY KEY (`idManufacturer`) ) ENGINE=InnoDB CREATE TABLE `Parts` ( `idPart` int(11) NOT NULL auto_increment, `idManufacturer` int(11) NOT NULL, `partnumber` int(11) NOT NULL, PRIMARY KEY (`idPart`), KEY `Parts_idManufacturer_FKIndex` (`idManufacturer`), CONSTRAINT `Parts_ibfk_1` FOREIGN KEY (`idManufacturer`) REFERENCES `Manufacturers` (`idManufacturer`) ) ENGINE=InnoDB And my database contains: Manufacturers: idManufacturername 123 Any Company Inc. Parts: idPart idManufacturer partnumber 1 123 12345 Now, let's say I want to do this: DELETE FROM Manufacturers WHERE idManufacturer=123 Because I have a part that references Manufacturer #123, I have to do this instead: DELETE FROM Parts WHERE idManufacturer=123 DELETE FROM Manufacturer WHERE idManufacturer=123 What I want is something I can feed the table definitions to, and the name of the table I want to delete a row from (in this case 'Manufacturers'), and generate a list of the DELETE commands that would allow me to delete that row while enforcing FK dependencies. I figure this is going to have to work something like mathematical expression evaluation -- build up a list of dependencies, then deal with the deepest dependency first. Catch being I can't see an obvious way to deal with generating the necessary DELETE commands without having to write a massive if recursion_level = 0 then generate_a_straight_delete else if recursion_level = 1 then... statement... Thanks, -- Phil. usene...@philpem.me.uk http://www.philpem.me.uk/ If mail bounces, replace 08 with the last two digits of the current year. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Algorithm for resolving foreign key dependencies?
Andy Shellam wrote: Am I missing something here? (It is late after a long day, I admit!) Only something I forgot to mention. All the foreign keys are set up as ON DELETE RESTRICT, meaning MySQL's response to a foreign key violation is to spit out an error message to the effect of I'm sorry, Dave, I can't let you do that. The problem is, the target platform doesn't use foreign keys for performance reasons. I want to use foreign keys in development as a bug-trapping method -- I'd rather see an FK violation error in development than get an angry email from a customer asking why there's a part listed that doesn't seem to have a manufacturer. The plan was to write a code-generator that would generate all the database code for me, then I could deal with the page templates and display logic myself (thus eliminating ~80% of the boring, repetitive work). I want the generated code to handle foreign keys itself, rather than relying on the database. As I said above, if foreign key constraints didn't slow things down markedly, I'd use them in production. Based on the (admittedly limited) testing I've done, application-side FK enforcement is considerably faster than using ON DELETE CASCADE and letting MySQL deal with the foreign keys. I don't like writing database code by hand (it all follows a standard template), so I figured I'd write a program to do it for me. Work smarter not harder and all that :) Thanks, -- Phil. usene...@philpem.me.uk http://www.philpem.me.uk/ If mail bounces, replace 08 with the last two digits of the current year. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem with GROUP BY
Date: Tue, 14 Oct 2008 16:55:11 +0300 From: Olexandr Melnyk [EMAIL PROTECTED] To: [EMAIL PROTECTED], mysql@lists.mysql.com Subject: Re: Problem with GROUP BY http://jan.kneschke.de/projects/mysql/groupwise-max 2008/10/14 Peter Brawley [EMAIL PROTECTED] Philip mysql SELECT number, MAX(event), name FROM info GROUP BY number; For discussion examples see Within-group aggregates at http://www.artfulsoftware.com/queries.php. Thank you both very much for your replies. Of course the solution is 'obvious' now I know the answer but as a relative newcomer to MySQL I had spent the best part of a day trying to find it. TTFN, Philip Riebold, [EMAIL PROTECTED] /\ Media Services\ / University College London X ASCII Ribbon Campaign Windeyer Building, 46 Cleveland Street/ \ Against HTML Mail London, W1T 4JF +44 (0)20 7679 9259 (switchboard), 09259 (internal) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with GROUP BY
I created a table with, CREATE TABLE info ( number INTEGER UNSIGNED, event INTEGER UNSIGNED, name VARCHAR(2000) NOT NULL, PRIMARY KEY (number, event) ); and populated it with data to produce this, ++---+---+ | number | event | name | ++---+---+ | 67 | 1 | Alice | | 67 | 2 | Bob | | 69 | 1 | Carol | | 70 | 1 | Alex | | 71 | 1 | David | | 72 | 1 | Bob | | 72 | 2 | Alice | | 72 | 3 | David | ++---+---+ What I want to produce is a table with rows from the original with only the maximum value of event for each corresponding number selected, like this +++---+ | number | event | name | +++---+ | 67 | 2 | Bob | | 69 | 1 | Carol | | 70 | 1 | Alex | | 71 | 1 | David | | 72 | 3 | David | +++---+ The closest I have managed to produce using GROUP BY is, mysql SELECT number, MAX(event), name FROM info GROUP BY number; +++---+ | number | MAX(event) | name | +++---+ | 67 | 2 | Alice | - should be Bob | 69 | 1 | Carol | | 70 | 1 | Alex | | 71 | 1 | David | | 72 | 3 | Bob | - should be David +++---+ I tried using a HAVING clause but got nowhere. Can anybody help please ? TTFN, Philip Riebold, [EMAIL PROTECTED] /\ Media Services\ / University College London X ASCII Ribbon Campaign Windeyer Building, 46 Cleveland Street/ \ Against HTML Mail London, W1T 4JF +44 (0)20 7679 9259 (switchboard), 09259 (internal) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Fwd: GRANT/REVOKE deny only 1 table in db from a user]
[take 2] -- Philip M. Gollucci ([EMAIL PROTECTED]) o:703.549.2050x206 Senior System Admin - Riderway, Inc. http://riderway.com / http://ridecharge.com 1024D/DB9B8C1C B90B FBC3 A3A1 C71A 8E70 3F8C 75B8 8FFB DB9B 8C1C Work like you don't need the money, love like you'll never get hurt, and dance like nobody's watching. ---BeginMessage--- -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 GRANT SELECT ON X.* TO 'Xread'@'172.28.1.%' IDENTIFIED BY 'X'; I need to change that slightly so that I add a more specific 'deny' REVOKE SELECT ON X.Y TO 'Xread'@'172.28.1.%' IDENTIFIED BY 'X' obviously this doesn't work as there is no grant defined like that. I'm trying to avoid doing something that I have to update everytime a table is added/deleted. - -- - Philip M. Gollucci ([EMAIL PROTECTED]) o:703.549.2050x206 Senior System Admin - Riderway, Inc. http://riderway.com / http://ridecharge.com 1024D/DB9B8C1C B90B FBC3 A3A1 C71A 8E70 3F8C 75B8 8FFB DB9B 8C1C Work like you don't need the money, love like you'll never get hurt, and dance like nobody's watching. -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.8 (FreeBSD) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFIKImmdbiP+9ubjBwRAnxnAJ0b+fQVKK1m5IK633do4PTOySuR+wCfffSv 7ZrfoKStq3SpsA3WubumVpY= =DQgk -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] ---End Message--- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GRANT/REVOKE deny only 1 table in db from a user
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 GRANT SELECT ON X.* TO 'Xread'@'172.28.1.%' IDENTIFIED BY 'X'; I need to change that slightly so that I add a more specific 'deny' REVOKE SELECT ON X.Y TO 'Xread'@'172.28.1.%' IDENTIFIED BY 'X' obviously this doesn't work as there is no grant defined like that. I'm trying to avoid doing something that I have to update everytime a table is added/deleted. - -- - Philip M. Gollucci ([EMAIL PROTECTED]) o:703.549.2050x206 Senior System Admin - Riderway, Inc. http://riderway.com / http://ridecharge.com 1024D/DB9B8C1C B90B FBC3 A3A1 C71A 8E70 3F8C 75B8 8FFB DB9B 8C1C Work like you don't need the money, love like you'll never get hurt, and dance like nobody's watching. -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.8 (FreeBSD) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFIKImmdbiP+9ubjBwRAnxnAJ0b+fQVKK1m5IK633do4PTOySuR+wCfffSv 7ZrfoKStq3SpsA3WubumVpY= =DQgk -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting my data first
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jake Conk wrote: | Hello, | | I have a table with 2 columns, file_id and owner_id. I want to select | all the files and order by file_id but I want the ones that belong to | me to show up first then everyone elses. Is this possible and how? | | This is what I'm trying to accomplish: | | SELECT * FROM whiles WHERE owner_id=my_id first THEN SELECT * FROM | files ORDER by file_id select * from files where owner_id = you union (select * from files where owner_id != you order by file_id) - -- - Philip M. Gollucci ([EMAIL PROTECTED]) o:703.549.2050x206 Senior System Admin - Riderway, Inc. http://riderway.com / http://ridecharge.com 1024D/DB9B8C1C B90B FBC3 A3A1 C71A 8E70 3F8C 75B8 8FFB DB9B 8C1C Work like you don't need the money, love like you'll never get hurt, and dance like nobody's watching. -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.8 (FreeBSD) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFIKI81dbiP+9ubjBwRAuOiAJ9e4kGv1qIUs7CbF5LGv30CaQfZgACdHXFM FKFSw66nKpVqcJDug776TBI= =YQxy -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Access limited after restore
Hi, all, I had a server crash a few weeks ago and had to restore my mySQL installation from backup after reinstalling Fedora. After the restore operation, I was no longer able to log into mySQL, either as root or as myself. I believe this may be because the UID in the recovered database is now different from the UID in my installation, although I'm having a hard time believing root will have a different UID after a standard installation. I've been living with this for a while because my daily operation doesn't require anything other than the ability to write to the DB through Wordpress, and that's working fine. However, today I attempted to delete a comment, and found that I could not. So, now I have a reason to try to fix this. Hence my questions: 1) Why would restoring from a backup wreck my ability to log into the database? 2) How can I go about reestablishing access to mySQL in a way that does not require blowing away all the data and starting over? Thanks. Phil W. Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Embedded versus 'normal' MySQL
Hello, I am using MySQL in a booking system I am working on. I decided to use an embedded server so downloaded 5.0.45 from source and compiled it with the embedded library enabled. It works fine except that disconnecting seems to take an exceptionally long time. Relevant lines from the log file are, connect_to_server() User50mS, System50mS, Real 100mS ... process_statement() User 0mS, System 0mS, Real20mS ... disconnect_from_server() User40mS, System 0mS, Real 2290mS The disconnect_from_server() function calls mysql_close_connection() and mysql_library_end(). Using the mysql command with a server daemon the 'quit;' command seems to execute immediately. (The platform is a SUN Ultra 45 running Solaris 10 and gcc version 3.4.6) So a couple of questions, Is a long disconnect time from an embedded server to be expected or am I doing something wrong ? I decided not to use a server daemon since I didn't want to risk it not being available when my program ran. Is this too pessimistic a view of server availability, under what circumstances would a running server become unavailable (ie. crash or refuse connections) ? TTFN, Philip Riebold, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: show slave staus
mysql show slave status returns a number of fields, with information on the status of the slave. is there a way to only return the field(s) i'm interested in... i thought that i had figured this out, but i can't recall, and it's been awhile since i've played with this! My slave server isn't nearby, but... show status like '%threads%'; will return just the results from show status that match on threads. Might work for slave status as well. -philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select rows containing identical values in two columns
I have a table that has a column with the id of the person that created the row. In another column in the same row there is a column with the id of the person that modified that row. Is there a way to write a SELECT statement that will return all the rows where the value in the creation column equals the value in the modification column? I don't want to specify a specific id in either of the columns. SELECT * FROM your_table WHERE created_by_id = updated_by_id; ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: short e-mail survey on load-balance technology used
1) Do you use a load-balancer for your MySQL Deployment? Yes. Well, up till just a little while ago... 2) What load-balancer (product name) do you use for your MySQL Deployment? We used to run MySQL's NDB Cluster behind a hardware load balancer (don't remember the name, but it's not that relevant). 3) Do you use the default mechanisms of the load-balancer to negotiate traffic to your MySQL deployment, or have you created your own custom mechanism for the load-balancer to use? Default. 4) (a) Other than your current load-balancer, have you tried to use any other load-balancers with success or failure? (b) Or is there another load-balancer you are looking into possibly using? (Some Examples: MySQL Proxy, Continuent, Sequoia (C-JDBC), Linux Virtual Server, F5 Networks BigIP, EddieDNS, or even Heartbeat, Pen, Python Director, Distributor) We tried Continuent about two years ago and had bad experiences with it. It was a three node setup and as long as we didn't write to it it was fine, but otherwise it would regularly hang. Frequently we had to reboot everything to get it unstuck. We never put it into production. But that was two years ago and I dont know anything about it since. 5) How do you primarily use the load balancer? 1) load-balance read-only SQL queries 2) load-balance read-write SQL queries 3) other? (like some custom setup) 2. 6) When it comes to scaling MySQL and the use of load-balancing, what do you feel is a technology that is missing that the MySQL community should create? (I.e. perhaps some new technical item in the MySQL database server software, or something on the load-balancing technology side) Something similar to pgpool that can automatically redirect writes to the master and reads to a pool of slaves. http://pgpool.projects.postgresql.org/ If that already exists and you know about it, link please! :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: short e-mail survey on load-balance technology used
pgpool is a connection pool server for PostgreSQL. pgpool runs between PostgreSQL's clients(front ends) and servers(back ends). Well there are a few solutions out there. The first comparable product for MySQL is MySQL Proxy. http://forge.mysql.com/wiki/MySQL_Proxy Almost, but the faq says it's not quite there (auto splitting reads/writes). http://forge.mysql.com/wiki/MySQL_Proxy_FAQ#In_load_balancing.2C_how_can_I_separate_reads_from_writes.3F Will be nice once it is though! -philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication and AUTO_INCREMENT; is it safe?
Reading How AUTO_INCREMENT Handling Works in InnoDB[1] makes me wonder how is it possible to replicate AUTO_INCREMENTed value to slaves. According to the doc, If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle in the InnoDB data dictionary contains a special counter called the auto-increment counter that is used in assigning new values for the column. This counter is stored only in main memory, not on disk. Let's say there are two server, A and B. A replicates its data to B, the slave. A and B has a table that looks like(column 'id' is auto_increment field) id value 1 a 2 b 3 c 4 d If After delete from table where id = 4 and restart mysqld on server B, insert into table (value) values(e) is executed on server A. Why would you delete data from the slave? In this case, because A's internal counter is 4, table on A would be 1 a 2 b 3 c 5 e But B's would be different because restarting mysqld flushed InnoDB's internal counter. 1 a 2 b 3 c 4 e Is this correct? or MySQL is smart enough to handle this problem? Thanks. [1]http://dev.mysql.com/doc/refman/4.1/en/innodb-auto-increment-handling.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Crystal Reports XI on W2k3 Server SP2 x64 (fwd)
[take 2] Nobody has any comments ? -- Forwarded message -- Date: Wed, 26 Sep 2007 12:19:21 -0400 (EDT) From: Philip M. Gollucci [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Crystal Reports XI on W2k3 Server SP2 x64 Hi All, I'm trying to get Crystal Reports to connect to a MySQL server. Windows 2003 Server R2 x64 Crystal Reports XI Professional Disabled DEP for crw32.exe mysql-connector-odbc-noinstall-3.51.20-winx64.zip FreeBSD 6.2-RELEASE-p7 x64 mysql-client-5.0.45 mysql-scripts-5.0.45 mysql-server-5.0.45 GRANT SELECT ON db.* TO 'user'@'apps.domain.tld' identified by 'x'; (note, this is not an authentication problem) 1) I created a SystemDSN via the ODBC Panel and it tests okay -- Crystal Reports can't see it -- this is a seperate PR I need to file with them. 2) I created a UserDSN which Crystal Reports can see BUT IM002:[Microsoft][ODBC Driver Manger] Data source name not found and no default driver specified I've also tried the full odbc connect string only to get the same error message. I filed the following PR with Business Objects (owns Crysal Reports) http://support.businessobjects.com/case_management/viewcase.asp?caseid=302861271; The long and the short is they want me to use 3.51.11! The big question, how do I get this version which is so old its no longer distributed. From googling, I have a hunch they are right but I can't fathom why. -- Philip M. Gollucci ([EMAIL PROTECTED]) 323.219.4708 Senior System Admin - Riderway, Inc. http://riderway.com 1024D/EC88A0BF 0DE5 C55C 6BF3 B235 2DAB B89E 1324 9B4F EC88 A0BF Work like you don't need the money, love like you'll never get hurt, and dance like nobody's watching. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Crystal Reports XI on W2k3 Server SP2 x64
Hi All, I'm trying to get Crystal Reports to connect to a MySQL server. Windows 2003 Server R2 x64 Crystal Reports XI Professional Disabled DEP for crw32.exe mysql-connector-odbc-noinstall-3.51.20-winx64.zip FreeBSD 6.2-RELEASE-p7 x64 mysql-client-5.0.45 mysql-scripts-5.0.45 mysql-server-5.0.45 GRANT SELECT ON db.* TO 'user'@'apps.domain.tld' identified by 'x'; (note, this is not an authentication problem) 1) I created a SystemDSN via the ODBC Panel and it tests okay -- Crystal Reports can't see it -- this is a seperate PR I need to file with them. 2) I created a UserDSN which Crystal Reports can see BUT IM002:[Microsoft][ODBC Driver Manger] Data source name not found and no default driver specified I've also tried the full odbc connect string only to get the same error message. I filed the following PR with Business Objects (owns Crysal Reports) http://support.businessobjects.com/case_management/viewcase.asp?caseid=302861271; The long and the short is they want me to use 3.51.11! The big question, how do I get this version which is so old its no longer distributed. From googling, I have a hunch they are right but I can't fathom why. -- Philip M. Gollucci ([EMAIL PROTECTED]) 323.219.4708 Senior System Admin - Riderway, Inc. http://riderway.com 1024D/EC88A0BF 0DE5 C55C 6BF3 B235 2DAB B89E 1324 9B4F EC88 A0BF Work like you don't need the money, love like you'll never get hurt, and dance like nobody's watching. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ordering dates
$result= mysql_query(SELECT date_format(date, '%d/%m/%Y') as date, title, id, display FROM news ORDER BY date DESC ); I have the query above the problem is oders them like so 30/05/2007 29/07/2007 25/0/2007 The order is taken by the first number. Is there any way to order them properly without a timestamp? You're ordering by date but previously you turn date into a string by calling date_format on it. Change the as date to something else and then the ordering will be chronologically descending. Like this: SELECT date_format(date, '%d/%m/%Y') as formatted_date, title, id, display FROM news ORDER BY date DESC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Geographic math problem
Not being very strong at math, I have a little problem that I'm not sure how to solve. Maybe someone can help me. Basically, given a point (latitude, longitude) and a radius (100 meters) (think circle), I need to compute an equivalent square: That is, two points that would correspond to two corners of the square. From: 51, -114100 meters To: 51.005, -114.005NE corner 49.995, -113.995SW corner Now, the above is not really accurate, of course, since the earth is spherical (well, at least most people think so), and I would like this computation to run in MySQL query, e.g.: UPDATE places SET ne_latitude = (*), ne_longitude = (*), sw_latitude = (*), sw_longitude = (*) In the above table, there are already three columns with the centre latitude and longitude and radius. Any ideas? Thanks. http://www.mathforum.com/library/drmath/view/51711.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to optimize this long query
Hello,
I have several tables storing item information, keyword (mainly for searching),
category and subcategory (also for searching). The query I am using now is:
SELECT i.*, it.*, ic.*, c.*, cs.*, s.*, st.* FROM item i
LEFT JOIN iteminfo it ON i.id = it.id
LEFT JOIN itemkeyword ik ON i.id = ik.id
LEFT JOIN state st ON it.state = st.id
LEFT JOIN itemcategory ic ON i.id = ic.id
LEFT JOIN subcategory s ON ic.sid = s.id
LEFT JOIN catsubcat cs ON cs.sid = s.id
LEFT JOIN category c ON c.id = cs.cid
WHERE (((UCASE(i.english) LIKE UCASE('% bank %') OR UCASE(i.desceng) LIKE
UCASE('% bank %') OR UCASE(s.english) LIKE UCASE('% bank %') OR
UCASE(c.english) LIKE UCASE('% bank %') OR UCASE(it.street1) LIKE UCASE('% bank
%') OR UCASE(it.street2) LIKE UCASE('% bank %') OR UCASE(it.garden) LIKE
UCASE('% bank %')))
OR ((UCASE(i.english) LIKE UCASE('bank %') OR UCASE(i.desceng) LIKE UCASE('bank
%') OR UCASE(s.english) LIKE UCASE('bank %') OR UCASE(c.english) LIKE
UCASE('bank %') OR UCASE(it.street1) LIKE UCASE('bank %') OR UCASE(it.street2)
LIKE UCASE('bank %') OR UCASE(it.garden) LIKE UCASE('bank %')))
OR ((UCASE(i.english) LIKE UCASE('% bank') OR UCASE(i.desceng) LIKE UCASE('%
bank') OR UCASE(s.english) LIKE UCASE('% bank') OR UCASE(c.english) LIKE
UCASE('% bank') OR UCASE(it.street1) LIKE UCASE('% bank') OR UCASE(it.street2)
LIKE UCASE('% bank') OR UCASE(it.garden) LIKE UCASE('% bank')))
OR (UCASE(ik.keyword) LIKE UCASE('%bank%')))
AND i.duedate 1182339575 GROUP BY it.iid ORDER BY i.credit DESC, it.hits
DESC, i.english LIMIT 0, 10;
You can drop all the UCASE's as LIKE is case insensitive. That should
help a little bit as it won't have to upper case all the fields in your
where clause.
Here is the EXPLAIN table:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE i ALL NULL NULL NULL NULL 1820 Using where; Using temporary; Using
filesort
1 SIMPLE it ref id id 8 item.i.id 19
1 SIMPLE ik ref id id 8 item.i.id 19
1 SIMPLE st eq_ref PRIMARY PRIMARY 2 item.it.state 1
1 SIMPLE ic ref id id 8 item.i.id 19
1 SIMPLE s eq_ref PRIMARY PRIMARY 4 item.ic.sid 1
1 SIMPLE cs ref sid sid 4 item.s.id 2
1 SIMPLE c eq_ref PRIMARY PRIMARY 3 item.cs.cid 1 Using where
Now I have the questions below:
1) Is it possible to shorten the WHERE clause?
2) Sometimes, the keyword that I use to search takes a long time ( over 6
seconds). What is the main problem causing this problem?
3) If I would like to sort the data by the `category.english` (if the keyword
found in category english name) and then following by the other criteria, how
do I write the ORDER BY clause?
Thank you very much for your help~
22-06-2007
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Integrity on large sites
Naz, Without going into detail about various projects I've seen, surfice it to say that I have wittnessed some true horrors. In defence however, the largest abomination I have ever witnessed was from an MS shop that had grown a database from a MS Access system upward and had then, bluntly bolted MySQL in to the mix so that they could expose it to the web (stop laughing ;P). It has however nothing to do with the specific database, just as you can write shoddy code in C++ or PHP, database abominations know no vendor boundaries. I think a large number of people reading this may agree when I say that commercial (you may read time money as the obvious subtexts) pressues to produce quick, cheap and working solutions are the real reason such things as documentation, proper requirements gathering and analysis, design and QA testing are the first against the wall when such pressures begin to bite or clients haggle on price. So, I'm afraid in concluesion Yes, you are being naive in thinking that everyone runs their DBs with RI in production. No they don't turn it off, they never build it in and if they do turn it off it's not for performance gains. The counter argument to that would be that it's fairly concievable that if you implemented a solution in a development enviroment with RI constraints, tested it carefully and completely, put it into production and perhaps ran it for a month or two then turned all the RI off that it would still hold water well enough to be a viable commercial solution. Not an argument I'd serious back but one you could make at any rate And finally Yes, it's a nightmare in such situations. Without whoring I should perhaps state at this juncture that my current employer does not produce such solutions. We have design and analysis procedures, a QA department, people with common-sense etc... to ensure that we avoid such things. Regards, Phil On 24/05/07, Naz Gassiep [EMAIL PROTECTED] wrote: I'm working in a project at the moment that is using MySQL, and people keep making assertions like this one: *Really* big sites don't ever have referential integrity. Or if the few spots they do (like with financial transactions) it's implemented on the application level (via, say, optimistic locking), never the database level. A large DB working with no RI would give me nightmares. Is it really true that large sites turn RI off to improve performance? Am I just being naive in thinking that everyone runs their DBs with RI in production? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Regards, Phil
Stuck with some SQL
Afternoon everyone, Sorry, don't you hate the fact that the tab key submits your emails on web clients!? Anyway please ignore that last partial email, I've got myself stuck with some SQL. What it boils down to is... SELECT wordgroup.Title AS `Keyword Group`, site.Site_name AS Site, questiontext.SanitisedQuestion AS Question, site.SiteID AS sid, question.QuestionID AS qid, COUNT(wordgroupanalysis.AnswerFK) AS Hits FROM wordgroup LEFT JOIN wordgroupanalysis ON wordgroupanalysis.WordGroupFK = wordgroup.WordGroupID LEFT JOIN answer ON answer.AnswerID = wordgroupanalysis.AnswerFK LEFT JOIN answertext ON answertext.AnswerTextID = answer.AnswerTextFK LEFT JOIN question ON question.QuestionID = answer.QuestionFK LEFT JOIN questiontext ON questiontext.QuestionTextID = question.QuestionTextFK LEFT JOIN individual ON individual.IndividualID = answer.PeopleFK LEFT JOIN site ON individual.SiteFK = site.SiteID WHERE Answer 'NULL' AND site.Site_name 'HQ' AND individual.Date_consumed BETWEEN 2006-01-01 AND 2007-05-01 GROUP BY wordgroup.Title, site.Site_name, questiontext.SanitisedQuestion ORDER BY NULL; ...returns me the number of hits (matches) of each keyword group against the text based responses to various questions in a Questionnaire that is sent out to different client sites, something like... +---+--++--++--+ | Keyword Group | Site | Question | sid | qid| Hits | +---+--++--++--+ | Drinks| SHERWOOD PINES | if you were less than satisfied with any of the above areas please provide further details | 378 |356 | 39 | | Drinks| BRACKNELL| if you were less than satisfied with any of the above areas please provide further details | 382 |431 | 75 | | Drinks| DEAN | if you were less than satisfied with any of the above areas please provide further details | 379 |356 | 44 | All well and fine but then I want to get the total number of answers to that question whether or not they matched against the keyword group so that I can make the hits into a percentage, I can get the total answers on an individual basis like so... SELECT COUNT(*) FROM answer LEFT JOIN individual ON individual.IndividualID= answer.PeopleFK WHERE answer.QuestionFK = 356 AND individual.SiteFK = 378 AND individual.Date_consumed BETWEEN 2006-01-01 AND 2007-05-01; ...but it has to be done all in one query (crapy Crystal Reports, don't ask). I've tried it as a subquery and as a function but it states that Select 2 was reduced during optimization when done as a function and seems to be trying to join every count against every row in the main table when run as a subquery and then grinds to a halt, I need it to run the subquery as an outer join I think onto the answer table but I can't get my head round it? Anyone know how I can do this? I'll personally have your babies! -- Regards, Phil
Stuck with some SQL
Afternoon everyone, Not been on here for a while, works been to hectic and this thing called life keeps getting in the way ;^) -- Regards, Phil
Re: INSERT ... ON DUPLICATE KEY UPDATE seems so close and yet so far...
Maybe this is some SQL standard implementation and that's why it is what it is, but to me it seems completely retarded that you have to explicitly call out the columns... http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html Why doesn't it work in a more convenient and sane way?! So instead of this cumbersome incantation that makes you want to rip out your hair and puch your cube-mate dead in the nose: insert into table1 (c1,c2,c3) values (v1,v2,v3) on duplicate key update c1=v1, c2=v2, c3=v3; Just allow a more sane and logical: insert into table1 (c1,c2,c3) values (v1,v2,v3) on duplicate key update; Because if you wanted that you'd use REPLACE which is mysql specific which is okay since it's mysql you're using I guess. And in my case, I have a stats table... I either want to insert a row with hits=1 or I want to hits=hits+1. So I need to specify what I want. And I don't want to change *any* of the other columns (such as the date for the hit or the id, etc.) ANyway... not saying they couldn't do it both ways, but there is a reason. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FullText Scoring With Two Databases
-BEGIN PGP SIGNED MESSAGE- Hash: SHA512 Neil, The short answer is probably merge the two databases manually and rebuild the index. I don't think there's any real answer otherwise. Given the old version of MySQL you aren't going to be able to do anything fancy like federating the two tables to into another database and making a view out of the two or anything. Actually the only other possible alternative is to do a myisam_ftdump - -s on both databases and get the Average Global Weight for each database and then use one to normalize the results from the other, reading... http://www.databasejournal.com/features/mysql/article.php/3512461 ...states the the query weight (final score) also depends on the local weight but that's okay (I think). So, er, right... To get the approximate query score for database 1 from a score retrieved in database 2 multiply it by the Average Global Weight of db1 divided by the Average Global Weight of db2. I think, it's a bit late ;^). Hence... The approximate database one Query Score = database two's Query Score * (database one's Average Global Weight / database two's Average Global Weight) A) It is only an approximation, IT IS NOT accurate. Your mileage will vary depending on the similarity of the contents of the two databases. B) Get a second opinion on that. Try it and let us know how well it works. Regards, Phil I've been using the MATCH() with FullText Scoring for quite a while now on one table. I now need to combine the data from another database. I have : Database1.Table1 with Database2.Table1 If I use the the FullText scoring using just one database/table it is OK, but when I query the database1 and then database2 the scoring is totally different because the data is different in each database. What would be the best way to overcome this problem. We are using a early version of mysql 3.28 and can't upgrade at this time. Thanks for any help. Neil _ Exclusive Ed Byrne daily comedy clips on MSN Video http://specials.uk.msn.com/edbyrne/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFF3jl4umMVa2pwPmkRCk62AJ9E30ogX1BqeKGiYqxcyWCpgjxH4QCfepxj qlTWplUZ3HsU3JsA4pm80Ls= =19pZ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: implementation of SELECT ... ORDER BY RAND() LIMIT 1
I have a question about the combination of RAND and LIMIT 1. If I have a query like: SELECT ... ORDER BY RAND() LIMIT 1 with the ... replaced with a normal query on one table. How is this implemented? Is this optimized for the fact that it only needs one entry? Try prefixing your query with EXPLAIN and see what it says it's going to do. Pretty sure it's going to look at *every* row in the table, compute a random value, sort it, then return the first one. So, for a table with a good number of rows, the above is going to be horrificly inefficient. It would be a lot faster to do something like: rowcount = select count(*) from table random_value = something between 0 and rowcount - 1 select ... LIMIT 1 OFFSET random_value -philip And what about when there is a combination of tables SELECT a.a, b.b FROM a,b WHERE a.b = b.id ORDER BY RAND() LIMIT 1 And in the case of SELECT a.a, b.b FROM a LEFT JOIN b ON a.b = b.id ORDER BY RAND() LIMIT 1 Some say that especially in the last two cases, it is faster to just retrieve the entire list and then select randomly. And what if the case is that the limit is larger than 1, but smaller than the entire table? I am asking because we have various of these queries in our code and serious issues with speed, and I was wondering whether I am assuming optimization in the mysql code where they don't actually exist. Any help on this would be much appreciated. Regards, Jos http://www.cantr.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: detecting the table type by sql?
Marten
In more recent version you can do a simple...
SELECT ENGINE FROM TABLES WHERE TABLE_SCHEMA = {database name} AND
TABLE_NAME = {table name};
...dunno how you'd do it on older versions exactly, you can do...
SHOW TABLE STATUS WHERE Name = BID_UNIQUE_IDS;
...but you can't select individual fields from that.
Regards,
Phil
Hello,
how can I get the type of a table (MyISAM, InnoDB, Berkeley DB) by a
given table name with sql?
Regards
Marten
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: detecting the table type by sql?
Marten
In more recent version you can do a simple...
SELECT ENGINE FROM TABLES WHERE TABLE_SCHEMA = {database name} AND
TABLE_NAME = {table name};
...dunno how you'd do it on older versions exactly, you can do...
SHOW TABLE STATUS WHERE Name = BID_UNIQUE_IDS;
...but you can't select individual fields from that.
Regards,
Phil
Hello,
how can I get the type of a table (MyISAM, InnoDB, Berkeley DB) by a
given table name with sql?
Regards
Marten
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext relevance and weighting....
Guys,
I doubt I'd qualify as an expert but here's my two pence worth ( ;^)
)I wrote a search engine a while back that relies heavily on full-text
searching and the three things I found that improved results were...
1) Precisely what Dan explains, doing extra biasing per field in the SQL
with whatever extra info you've got is best. I had fields like
seriesFK that were null if it was a one off TV episode so I used the
result of (isnull(seriesFK) + 1) * {some multiplier}) and similar to
shove a whole TV Series above it's episodes. Perhaps you'd want to bias
the small_desc field more than the large_desc etc...
2) Adding/removing stop words to the |ft_stopword_file| and changing the
word length as you've already done.
3) Fiddling with the search requests before they get put into the SQL, I
was dealing with various people's names as well so and I knew they were
so I've got a bit of PHP that doubles two word queries up but with one
swapped around, so if the user enters George Harrison they'd actually
get a search for George Harrison and Harrison, George.
Some of those you've done or are domain specific (i.e. the names),
sorry. ||Some other ideas I didn't get to try out were...
1) You can use the myisam_ftdump utility to dump the actual weightings
MySQL generates, load those stats back in and use it to generate a new
meta table then use that in combination with feedback about requested
search terms and followed links to make an engine that could to some
extent learn.
2) the soundex() function would allow you to handle spelling mistakes
and might be of some use in your problem to bias those results that
sound most like the term? See
http://dev.mysql.com/doc/refman/5.1/en/string-functions.html and search
for sondex.
The problem I found is that the clients (the people who are paying for
the search engine) knew, as expert in their subject what exact order
they'd like the results to come back in but you hit the problem that you
can't readily program that knowledge into a computer no matter how hard
you try or how fancy your algorithms get. I can't tell you the pain
Charlie Chaplin caused me, his real name is actually Charles but
without lots of extra contextual information to hand you can't program
that sort of knowledge and exception into a computer without spending
lots of time or money.
I wrote the search engine bit inside
http://ftvdb.bfi.org.uk/searches.php and they were happy with it once
we'd finished but I think this sort of area is somewhere you could spend
a lot of effort making little difference on small or medium sized
projects, there is a certain amount of tweaking that can be done but
eventually you'd need to move to a solution that is significantly more
technologically complex i.e. expensive time consuming before achieve
noticeably better results.
If you want I'd be happy to outline and expand on some ideas.
Regards,
Phil
It is more of an issue to prioritizing fields for relevance, and whether it
is possible to do this within a fulltext query, or whether it needs to be
done through multiple queries, and then outside php processing of those
query results
You should be able to do what you need by making your 'score'
expression something like this:
select *,
match(code) against ('ham*' in boolean mode) * 8 +
match(name) against ('ham*' in boolean mode) * 4 +
match(small_desc) against ('ham*' in boolean mode) * 2 +
match(large_desc) against ('ham*' in boolean mode)
as score from products where active='y' and site like '%,1,%' and
match(code,name,small_desc,large_desc) against ('ham*' IN BOOLEAN MODE)
order by score desc
This takes advantage of the fact that boolean mode matches always
return 1 or 0, so a record matching in the code field will sort
higher than a record with ham in all 3 of the others but not in
code.
Does anyone have any suggestions on how to solve the result
weighting problem? I have a client whose search results are
becoming more and more important, and the relevance demands on the
results are not entirely satisfactory...
The fields that are searched are code, name, small description and
large description, and are ranked in relevance in that order.
For example, a product with the name: Bone-In Serrano Ham should
ALWAYS outweigh the product with the name of Boneless Jamon
Iberico, even if the Jamon Iberico has the word ham in the
description 20 times more than the Serrano product...
The query that is being run is: select
*,match(code,name,small_desc,large_desc) against ('ham*') as score
from products where active='y' and site like '%,1,%' and
match(code,name,small_desc,large_desc) against ('ham*' IN BOOLEAN
MODE) order by score desc
It returns some good relevant matches, but then in the middle of
products names with ham in them, it returns one without
Does this require a complete logic switch, or is there a way to
build a query to do this?
Obviously the actual build of the query is more
Re: Easiest Way To Replicate DB
I am doing tests so I want to easy take my DB and make a full copy of it into a test db everytime I want to test something against the non-produciton version of DB. What is the easiest way to do this. So I have a DB called backlog and I want to copy it's structure and data into backlog_test with ease :-). Any sexy suggetions? :-) This is what I do... mysqldump -v --lock-tables=false backlog | mysql backlog_test Not practical if your dataset is huge, but if it's not that big, works great. -philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to pronounce MyISAM and InnoDB
Jan, In English I pronounce them as... My-eye-sam In-oh-dee-bee ...respectively. Regards, Phil 2007/1/7, js [EMAIL PROTECTED]: Hi list, Sorry for this silly question but I've been always had trouble pronouncing MyISAM and InnoDB. How do you pronunce them? Thanks in advance. When I'm speaking Dutch (which is most of the time) I say My-ee-sahm Inno-day-bay JP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL My program
Mohsen, Philip Mather wrote: Mohsen, First off, what version of MySQL are you running and on what platform? Now when i use --skip-grant-tables i can see my databases. Please help me... When you use MySQL via a shell I would assume you're logging in as root? You also say you can only see a test database, can you not see a mysql database there as well? I would suggest that somehow either the mysql database has been dropped/destroyed or perhaps just the db, host, tables_priv, and user tables of that database have been dropped/destroyed/messed up. Log in via shell and show us the result of the following commands... mysql use mysql; mysql show tables; mysql SELECT COUNT(*) FROM db; mysql SELECT COUNT(*) FROM user; If you can't see the database and/or the tables I'm talking about you may want to just check your backups are working and come back to us. Regards, Phil When i use ps ax |grep mysql i see following statements: 6173 ?S 0:00 /bin/sh /home/arman/mysql/bin/mysqld_safe --basedir=/home/arman/mysql/ --datadir=/home/arman/mysql/var/ --socket=/tmp/arman.sock --port=3310 --user=root --skip-grant-tables 6220 ?S 1:21 /home/arman/mysql/libexec/mysqld --basedir=/home/arman/mysql/ --datadir=/home/arman/mysql/var/ --user=root --pid-file=/home/arman/mysql/var//lnxsrv2.pid --skip-locking --port=3310 --socket=/tmp/arman.sock --skip-grant-tables Now when i use following command : [EMAIL PROTECTED] root]# /home/arman/mysql/bin/mysql --port=3310 --socket=/tmp/arman.sock --user=root -p then input password i see following message: Enter password: ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) So,I can't use use command in mysql shell. Yours,mohsen So you can't login in as root even? Can you not get into the database at all? I must admit that I've never used MySQL installed under a user's home directory, are you sure everything has the correct file permissions, ownership and security contexts (if you're using SELinux)? First off, backup the whole database and everything to do with it and keep a copy somewhere safe, if you've got any sort of over night/off site backup system grab a copy from that. Just make sure you can get back to this point before going any further. Secondly, can you think of anything you've done or maybe used (some graphical database manager or tool) that could have messed your database up? If you administer it via the command line who was the last person to change any privileges and/or type flush privileges;? After that there are four things I'd try but what sort of *N?X are you using and what version of MySQL is it? Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Count the number of specific rows
Servers24, Well this question may seem funny... No, a funny question would start something like Why did the nun cross the road?. ;^) The problem is with counting a user's contribution in my site. Suppose that each user that send an email will be stored in DB. Now I want to count number of times that a user has sent an email. I can simply use this : SELECT id FROM sent WHERE member_id= ... and the use count($result) to count the number, but I want a faster way, if Get MySQL to do the counting... SELECT COUNT(*) FROM sent WHERE member_id = ...; ...saying COUNT(*) is certainly no slower than saying COUNT(id) and the COUNT function is certainly faster than iterating over the result set using whatever language, also make sure you have indexed the member_id field as well. Alternatively you maybe able to incorporate a **|SQL_CALC_FOUND_ROWS |**into an existing query and then do a **| |**|FOUND_ROWS()| to optimize things (see http://dev.mysql.com/doc/refman/4.1/en/information-functions.html) or failing that run a nightly query to compile the stats into a new table containing just member_id and emails_contributed fields? Those would be the standard options. Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max size and row numbers
Olaf, Thanks for the detailed answer. So basically the limitations come from the OS and the file system used. What is the best file system to use for mysql (not considering the filesize limitations)? Thanks Olaf The best is probably ZFS if you really are intent on make things huge, http://en.wikipedia.org/wiki/Zettabyte_File_System but check out the very useful http://en.wikipedia.org/wiki/Comparison_of_file_systems page as well. Dunno about speed-wise, but you'd end up in an argument centering around RAID probably? Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Count the number of specific rows
Servers24, Hi Philip, Thank you very much for your help. Can you please tell me the differemce between COUNT(*) and COUNT(id) ? Thanks again. Actually sorry I was a bit misleading there. MySQL is optimized to calculate... SELECT COUNT(*) FROM aTable; ...but given the fact you've got a where condition that optimizations probably no longer applicable, so I'd stick with... SELECT COUNT(id) FROM sent WHERE member_id = ...; ...the real difference would be when you do a SELECT COUNT(DISTINCT id) instead of a SELECT COUNT(DISTINCT *) which are different questions but not relevant to your situation. Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max size and row numbers
Olaf, That's a very broad question depending upon your exact requirements to be honest. So long as you've considered file size limitations and you've opted for a decent RAID system there's not too much more to worry about, try and buy the fastest disks possible obviously i.e. 15,000 RPM ones that are specifically designed for RAID setups etc... Regards, Phil There is a RAID now an there will definitely one in the new setup. As far as the max file sizes from the file systems go I am not worried after all I read. Thanks for those links btw. Besides the size though, what should I pay attention to when selecting the file system Thanks Olaf On 12/29/06 11:31 AM, Philip Mather [EMAIL PROTECTED] wrote: Olaf, Thanks for the detailed answer. So basically the limitations come from the OS and the file system used. What is the best file system to use for mysql (not considering the filesize limitations)? Thanks Olaf The best is probably ZFS if you really are intent on make things huge, http://en.wikipedia.org/wiki/Zettabyte_File_System but check out the very useful http://en.wikipedia.org/wiki/Comparison_of_file_systems page as well. Dunno about speed-wise, but you'd end up in an argument centering around RAID probably? Regards, Phil - Olaf Stein DBA Center for Quantitative and Computational Biology Columbus Children's Research Institute 700 Children's Drive phone: 1-614-355-5685 cell: 1-614-843-0432 email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL My program
Mohsen, First off, what version of MySQL are you running and on what platform? Now when i use --skip-grant-tables i can see my databases. Please help me... When you use MySQL via a shell I would assume you're logging in as root? You also say you can only see a test database, can you not see a mysql database there as well? I would suggest that somehow either the mysql database has been dropped/destroyed or perhaps just the db, host, tables_priv, and user tables of that database have been dropped/destroyed/messed up. Log in via shell and show us the result of the following commands... mysql use mysql; mysql show tables; mysql SELECT COUNT(*) FROM db; mysql SELECT COUNT(*) FROM user; If you can't see the database and/or the tables I'm talking about you may want to just check your backups are working and come back to us. Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL My program
Mohsen, First off, what version of MySQL are you running and on what platform? Now when i use --skip-grant-tables i can see my databases. Please help me... When you use MySQL via a shell I would assume you're logging in as root? You also say you can only see a test database, can you not see a mysql database there as well? I would suggest that somehow either the mysql database has been dropped/destroyed or perhaps just the db, host, tables_priv, and user tables of that database have been dropped/destroyed/messed up. Log in via shell and show us the result of the following commands... mysql use mysql; mysql show tables; mysql SELECT COUNT(*) FROM db; mysql SELECT COUNT(*) FROM user; If you can't see the database and/or the tables I'm talking about you may want to just check your backups are working and come back to us. Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL My program
Mohsen, I'm not sure you're receiving any of this as you also seem to have a rather over-eager spam filter as well... Symantec Mail Security detected prohibited content in a message sent from your address (SYM:40763633734165155763) Subject of the message: Re: MySQL My program Recipient of the message: [EMAIL PROTECTED] [EMAIL PROTECTED];mysql@lists.mysql.com mysql@lists.mysql.com ...which is a bit odd as I sent the second message as plain-old-text and nothing else. Sorry for the double postings to the list by the way. shrug / Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number extraction from a string
Chris, On Thursday 07 December 2006 16:34, Ed Reed wrote: Thanks for the quick reply Chris. It's close but it's a little off. Your example also returns all instances that where the letter N exists in another words as well SELECT SUBSTRING(value,2) as value_num, value FROM num_test WHERE value like 'N%'; Though this wouldn't work if you had like Night or NIGHT. Is that going to be a choice? You'll need a regex, see these... http://dev.mysql.com/doc/refman/4.1/en/pattern-matching.html http://dev.mysql.com/doc/refman/4.1/en/string-comparison-functions.html http://dev.mysql.com/doc/refman/4.1/en/regexp.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Issues with MySQL x86_64 crashing
[EMAIL PROTECTED] wrote: Hi, How much memory do you have on your system ? (the current setting in your my.cnf could eat *a lot* of memory) min_memory_needed = global_buffers + (thread_buffers * max_connections) thread_buffers ---+- sort_buffer_size | 12M myisam_sort_buffer_size| 64M read_buffer_size | 12M join_buffer_size | 12M read_rnd_buffer_size | 12M ---+-- TOTAL | 112M global_buffers ---+-- key_buffer_size| 2048M So worst case we're talking 33 and a bit Gig, but late night worst case scenario? maybe a 1000 connections? So something like 15G, that's not that bad. I'd run mtop as someone suggested and see if some query is hammering it, maybe some other process on the machine is hogging or going IO bound? Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Any good free Case tools for MySQL 5.x?
mos, I'm looking for a free, perhaps open source, case tool for MySQL 5.x. (Older MySQL 4.0 case tools may not work because of the changes to pw security in 4.1 and later) I tried MySQL Workbench 1.1.5 alpha but I keep getting errors The following error occurred while launching the object editor. Error reading RoutineUce.MaxRightChar:Property MaxRightChar does not exist. It is still in alpha so I guess bugs are to be expected. So is there anything else out there that you'd recommend? I don't need something with a lot of bells and whistles. I'm primarily looking for something that can reverse engineer a database, create a diagram of it, make some changes, and update the database with the changes. http://fabforce.net/dbdesigner4/ ...is pretty funky and meets the requirements. Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Issues with MySQL x86_64 crashing
Kevin Old wrote: On 12/8/06, Philip Mather [EMAIL PROTECTED] wrote: So something like 15G, that's not that bad. I'd run mtop as someone suggested and see if some query is hammering it, maybe some other process on the machine is hogging or going IO bound? Thanks. We are watching the queries. The pattern we're seeing now is any large query that takes more than a few seconds to execute causes incoming queries to stack up and not execute, which causes the mysql load to go higher. We've seen a few times where mysql recovered after a large query started other queries to stack up. Keep in mind that we've been running some of these queries that are now having problems for over a year. We were running on the same hardware with the 386 version of mysql and performance was awesome only using 2GB RAM (the max mysql would allow us to use). Only after the switch to the x86_64 version are we seeing these problems. Tried an optimize or maybe a myisamchk |--check| or a |--analyze? Might not be the underlying cause but might reduce the occurrences of pile ups? Maybe there's a hardware issue when using the 64 bit code, any RAID involved? ||I was vaguely assuming it was a RedHat-a-like box of some description?| | shrug / Sounds like some other issue is just pushing MySQL over the edge, not bumping into any ulimits are you? Regards, Phil | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slightly off topic maybe but bear with me...
Has anyone here seen any software (preferably PHP and Open Source) that connects to MySQL and allows people to build/design/modify databases and tables? I'm not really looking for something like DBDesigner or phpMyAdmin although their close/similar to want I want, I'd like something more orientated towards allowing developers to create or modify databases within certain constraints and with predefined implementations of common data structures. Sorry for the vagueness my brief is a bit fuzzy at the moment. Something like a standalone implementation of Microsoft's SQL builder tool (with the little table based drop-downs) combined with DBDesigner, then some sort of table template and restriction features added in with the whole lot simplified down a bit to work via a web interface. Oh and the moon on a stick as well ;^). Seriously however just examples of other programs or interfaces (whatever database/OS/language) would be good so I can compare GUI's and implementations to start with. Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: better table structure: 30 records in 2 column table or one record in 30 columns table
[EMAIL PROTECTED],
hi,
i'm created a 'configuration' table:
create cofiguration (
config_key VARCHAR(25) NOT NULL PRIMARY KEY,
config_value VARCHAR(255) NOT NULL
) ENGINE=MyISAM;
I have about 30 records (store_name, store_owner, template_name,
website_width, owner_address, prodcuts_per_page,..).
now i wonder is maybe better solution to have a table with 30 columns and
one record:
create configuration (
store_owner VARCHAR(50) NOT NULL,
template VARCHAR(25) NOT NULL,
store_with INTEGER(4) NOT NULL,
thumb_width int(3) NOT NULL,
big_width int(4) NOT NULL,
products_per_page INT(2) NOT NULL,
stock ENUM('1','0'),
etc.
) ENGINE=MyISAM;
That's a very tough decision to make based solely on database
elegance, in probability your choice should be coupled to your
software's design.
It would be reasonable to assume that you're using some sort of object
orientated language and if so that you have a “store” object with getter
and setter methods for each of the configuration options which would
match the second table design neatly.
If however your software or system is complicated enough you may have
abstracted the concept of “configuration” out into it's own little
object/interface entirely which you may then subclass/implement in some
fashion depending on your language of choice. This approach would more
closely reflect the first table's design.
Finally and perhaps the decider that could influence your software's
design in the first place is database performance considerations. Taking
a pretty large scenario of say 5,000 stores with 100 configuration
options each you'd either end up with half a million records of about
three columns in table version one or only 5,000 records but with 100
columns in table version two.
Given the options I'd personally go with version one, half a million
records isn't a problem for any sensible hardware and it'll be easier to
administrate and code for in my humble opinion.
If I were to implement this sort of thing I'd do it this way...
CREATE TABLE `Configuration`
(
`Configuration_ID` int(11) NOT NULL auto_increment,
`Store_FK` int(11) NOT NULL,
`Parameter_name` CHAR(255) NOT NULL,
`Parameter_value` CHAR(255) NOT NULL,
`Date_deleted` timestamp NOT NULL default '-00-00 00:00:00',
`Date_modified`timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
`Date_created` timestamp NOT NULL default '-00-00 00:00:00',
PRIMARY KEY (`Configuration_ID`),
UNIQUE KEY `Store_parameter_ID` (`Store_FK`,`Parameter_name`),
KEY `Parameter_name` (`Parameter_name`),
KEY `Store_FK` (`Store_FK`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
Remember to set Date_created equal to NOW() when you insert (or make
yourself a trigger if your as paranoid as me) and there may also be
value in replacing Parameter_name with a foreign key and normalising it
off into it's own little table depending on how funky you'd like to get.
I should probably point out that I've glossed over/skipped whole degree
course programs worth of database and software design principles and
that my solution is my own opinion and could well be hideously over
engineered or otherwise seriously flawed but you know ;^).
Regards,
Phil
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: shutdown database but not mysqld
Dan,
In the last episode (Nov 22), Alfred Mak said:
Can I shutdown one of the databases in MySQL but not the whole mysqld
process (i.e. keeping the other databases still running) ?
shutdown would be the wrong word then :) How about revoking
permissions (either at the mysql or the filesystem level) ? You could
also flush tables, then rename the database directory somewhere out
mysql's data directory.
I have to admit I've always found it vaguely odd that there's no ability
to do this, is it on a feature request list somewhere? A simple
statement like shut {database} or close or stop maybe. Perhaps the
shut would temporarily deny permission to the database for all except
root, stop could simply lock up all the tables and close would
completely unmount the database in some fashion?
I doubt it would get used too often but I know exactly when it would be
useful...
silly developer UPDATE staff SET isActive = N; where ID = 451 LIMIT 1;
blind panic /
system admin SHUT stuffedDatabase;
...and yes I have seen something like that happen and no it wasn't me
typing the first statement. This sort of functionality will be more and
more important as people start using triggers and other sorts of mischief.
Regards,
Phil
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: shutdown database but not mysqld
Kieran,
Just make a backup of the database to a file using mysqldump and then
drop the database. If you need to recreate it again in the future, you
can use the mysqldump backup file.
Oh indeed, but if you got a xGB database that's not exactly going to
be quick. I'm thinking of instead of slapping your This site is
currently down for maintenance page up and recovering from your nightly
mysqldump you'd lock out one database and recover that whilst possibly
retaining some site functionality. Although if your that bothered you'd
just have two sync'ed databases loaded at all times on the same server
and a simple application level switch over script. In fact I've already
got all that but for a site that uses a snapshot of a larger Oracle
database, let's me swap snapshots over without bothering the users.
I suppose it's got limited uses and introduces the probably high
likelihood of some poor fool typing lock {databaseName} instead of
lock {tableName} which could be catastrophic in itself.
Forget I mentioned it ;^)
Regards,
Phil
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BC Dates
I can't find how to handle B.C. dates. I am not concerned about the Julian to Gregorian cutover issue, and I simply need to be able to represent dates such as the birth and death of Aristotle, 384-322 B.C.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: access full-text index
Leandro Guimarães Faria Corcete DUTRA wrote:
On Wed, 08 Nov 2006 18:51:20 -0800, Rares Vernica wrote:
Is it possible to access the Full-Text Index structures from SQL?
What do you mean exactly? SQL is not intended for physical structures.
I started writing a little PHP shell script to import the data from the
myisam_ftdump into a table, but unfortunately this a side project at
work and I have to effectively steal time to work on it so it's not
going very far at the moment. The table structure I was using was...
CREATE TABLE `SEARCH_STATS` (
`ID` int(11) NOT NULL auto_increment,
`Parent_Table` varchar(255) NOT NULL,
`Name` varchar(255) NOT NULL,
`Occurances` int(11) NOT NULL default '0',
`Search_Count` int(11) NOT NULL default '0',
`Global_Weight` float(10,7) NOT NULL default '0.000',
`Manual_Weight` float(10,7) NOT NULL default '1.000',
`Date_deleted` timestamp NOT NULL default '-00-00 00:00:00',
`Date_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on
update CURRENT_TIMESTAMP,
`Date_created` timestamp NOT NULL default '-00-00 00:00:00',
PRIMARY KEY (`ID`),
UNIQUE KEY `Name` (`Name`),
KEY `Occurances` (`Occurances`),
KEY `Search_Count` (`Search_Count`),
KEY `Global_Weight` (`Global_Weight`),
KEY `Manual_Weight` (`Manual_Weight`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
...and a rough bit of psuedo-code for you...
shell( myisam_ftdump /home/databases/mysql/{database}/{tables}4 -c
global_weights_and_counts.index );
shell( myisam_ftdump /home/databases/mysql/{database}/{tables}4 -d
local_weights.index );
$databaseWrapperObject_ = new DatabaseWrapper($includePath);
$handle = @fopen(global_weights_and_counts.index, r);
if ($handle)
{
while (!feof($handle))
{
$buffer = fgets($handle);
$data = explode( ,$buffer);
$sql = 'INSERT INTO SEARCH_STATS (Parent_Table, Name, Occurances, Global_Weight,
Date_created) VALUES (WD_NAMES, '.trim($data[20]).', '.$data[8].',
'.$data[19].', NOW())';
mysql_query($sql);
}
fclose($handle);
}
$handle = @fopen(local_weights.index, r);
if ($handle)
{
while (!feof($handle))
{
$buffer = fgets($handle);
$data = explode( ,$buffer);
//8, 19,20
// $sql = 'INSERT INTO SEARCH_STATS (Parent_Table, Name,
Occurances, Global_Weight, Date_created) VALUES (WD_NAME
// mysql_query($sql);
}
fclose($handle);
}
...that last loop needs sorting.
Regards,
Phil
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: £ (Great Britain Pound) breaks MySQL insert and update - disastrous overwrit e
Warren Young wrote:
Paul Warner wrote:
When a user enters text with a £ sign (Great Britain Pound) in the
browser and clicks enter, any insert or update statement apparently
gets truncated in mysql.
It's possible that somewhere along the line, the character is getting
translated to a multibyte Unicode format. (UTF-16 or -32, most
likely.) The hex value for the pound sign is 00A3, which includes a
null character, which the MySQL C API will interpret as end-of-string.
From my own experience I'd point out that if your using a terminal to
do something like SSH, that too needs to be set to the correct encoding
as well. Data being imported (be that any method, even cutting and
pasting) can also be an area of strife, I suffered greatly when when
producing a copious country and region listing system with everything in
the correct character sets for a website that had up until then been
ISO-{whatever}.
Regards,
Phil
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: £ (Great Britain Pound) breaks MySQL insert and update - disastrous overwrit e
Warren Young wrote:
Paul Warner wrote:
When a user enters text with a £ sign (Great Britain Pound) in the
browser and clicks enter, any insert or update statement apparently
gets truncated in mysql.
It's possible that somewhere along the line, the character is getting
translated to a multibyte Unicode format. (UTF-16 or -32, most
likely.) The hex value for the pound sign is 00A3, which includes a
null character, which the MySQL C API will interpret as end-of-string.
From my own experience I'd point out that if your using a terminal to
do something like SSH, that too needs to be set to the correct encoding
as well. Data being imported (be that any method, even cutting and
pasting) can also be an area of strife, I suffered greatly when when
producing a copious country and region listing system with everything in
the correct character sets for a website that had up until then been
ISO-{whatever}.
Regards,
Phil
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with query on 5.11
Jerry, Is 9640 a word by itself? A full-text search wouldn't find abc9640, No a full text search would find numbers pretending to be a word, the full text search has a fairly high level definition of a word. Try searching for 1960 over at http://ftvdb.bfi.org.uk/search.php. Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with query on 5.11
Jerry, Is 9640 a word by itself? A full-text search wouldn't find abc9640, No a full text search would find numbers pretending to be a word, the full text search has a fairly high level definition of a word. Try searching for 1960 over at http://ftvdb.bfi.org.uk/search.php. Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'Not a valid MySQL result resource' error
List,
Without getting into the specific problem the general debug path I'd
follow would be: -
1. Make sure you are actually connected to the database, you say it's
the admin area? Does the admin area login with different details? try
eching the result of a select NOW() right at the start of the admin area.
2. Make sure the SQL works, echo the $x just before it gets sent to the
database and then past it into the MySQL command line.
3. Make sure all the variables used in the query (not relevant to you)
and database connection ($db) are set and that you're using the right
result set ($r) in this case.
From the error I'd say you've got either 1 or 2, can't be much more
specific with the information I'm afraid. Are begdate and enddate
ambiguous?
Regards,
Phil
Hello,
I'm running f.a.m.p, f =freebsd 4.7 and mysql is 3.23.52.
Anyway, I inherited a website from someone else's server(I don't know
what they we're running) but the admin section of the website
generates this error iin the apache error log when trying to login( on
the screen just takes you back to login saying invalid:
snip
[Tue Oct 17 19:10:08 2006] [error] PHP Warning: mysql_query():
supplied argument is not a valid MySQL-Link resource in
/usr/local/apache/website/incoming/_includes/_page-specialeventsnav.php
on line 16
[Tue Oct 17 19:10:08 2006] [error] PHP Warning: mysql_fetch_array():
supplied argument is not a valid MySQL result resource in
/usr/local/apache/website/incoming/_includes/_page-specialeventsnav.php
on line 17
[Tue Oct 17 19:10:08 2006] [error] PHP Warning: mysql_num_rows():
supplied argument is not a valid MySQL result resource in
/usr/local/apache/website/incoming/_includes/_page-specialeventsnav.php
on line 20
end snip
code in question:
script language=javascript
!--
function focus(urlstring) {
window.open(urlstring,adFOCUS,width=700,height=580,scrollbars=yes,toolbar=no,location=no,resizable=no);
}
//--
/script
div id=mainnav
table width=180 border=0 cellpadding=0 cellspacing=0
trtd align='left'img src='_elements/spacer-blank.gif' height='1'
width='180' border='0'/td/tr
trtd align='center'font class='section'uSpecial
Events/u/fontbrbr/td/tr
trtd align=center
? $x = SELECT page_contents.title,page_contents.id FROM
page_contents,page_sections WHERE page_contents.pagename =
page_sections.pagename AND page_contents.display != 'N' AND
page_sections.publicurl = '/specialevents.php' AND begdate = Now()
AND enddate Now() ORDER BY rand() LIMIT 6;
$r = mysql_query($x,$db);
while ($re = mysql_fetch_array($r)) {
print a class=\sideoff\
href=\javascript:focus('http://www.website.com/viewevents.php?rid=$re[id]')\$re[title]/abrbr\n;
}
$rcount = mysql_num_rows($r);
if (($rcount == 6)) {
print a class=\sideoff\
href='http://www.website.com/specialevents.php'continued.../abrbr\n;
}
?
/td
/tr/table
/div
Any and all help is much appreciated, thanks.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How many databases does MySQL 5 support?
John, How many databases does a single instance of MySQL Server 5.x support? I suspect you'll get a bit of a shrug /, with a 64bit machine there's a limit of 4.2 billion rows per table and with an XFS file system 8EB per table, there's a join limit specified somewhere but I don't think there's an explicit limit coded in anywhere? Assuming you have no other hardware constraints beyond a 64bit processor (and that's an enormous assumption) I'd imagine you'd explode one of the tables in the information_schema database maybe. Having said all that I suspect that the effort to build a 128bit processor with working generic *n!x kernel, ANSI C compiler and file system (minus any 64bit constraints) would dwarf the work then needed to compile MySQL onto it. So for the time being I'd suggest that it's safe to assume you'll find your hardware's limits first. Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How many databases does MySQL 5 support?
Dan, Cheers for doing the translating, I'm one of those beardy types they keep locked in a dark room writing search engines so my English isn't spectacular ;^) As Rolando points out your file system may place a limit on the number of files or directories, but to my knowledge XFS has no such limit so your still left with 8 Exabytes (approximately one quintillion bytes) to store your data. By the way if you used Solaris 10 (he says looking under his desk) you could use ZFS which would give you 16 exabytes (and a 2^48 file limit), which is er, many. Back to the question at hand tho', the schema_information table is required to store information about each column (which looks like the most populous table in there), assuming a vaguely realistic 3 tables with 5 columns per database that would give you 160 million databases before you reached the 2.4 billion row limit of that table? Interestingly I can't file a file limit mentioned for XFS, if it's 2^24 that's only 16.7 million. How about we leave it at lots. What Philip is saying is that you might run into problems with one of the tables used to keep track of tables and databases, before you run into problems with any hard coded limit of MySQL itself. Regards, Philip (I better get back to real work) Mather ;) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to build a single temporary table from 3 tables on the fly
Kerry, It gives me a solution and some reading. No probs, here's some actual code that I hacked together on a 4.1-sommat-or-other database, an important thing to note is to be careful of any Unique keys selected from the three individual tables as they may no longer be unique of course once you've union them using the ALL method. CREATE TABLE Merged_names ( `ID` int(11) unsigned NOT NULL auto_increment, `Static_field` varchar(50) NOT NULL default '', `Parent_table` varchar(255) NOT NULL default '', `First_name` varchar(50) NOT NULL default '', `Last_name` varchar(30) NOT NULL default '', PRIMARY KEY (`ID`) ) ENGINE=MEMORY SELECT Static_field, Parent_table, First_name, Last_name FROM ( ( SELECT ID, Static Text AS Static_field, staff1 AS Parent_table, First_name, Last_name FROM staff1 LIMIT 10, 5 ) UNION ALL ( SELECT ID, Static Text AS Static_field, staff2 AS Parent_table, First_name, Last_name FROM staff2 LIMIT 20, 5 ) UNION ALL ( SELECT ID, Static Text AS Static_field, staff3 AS Parent_table, First_name, Last_name FROM staff3 LIMIT 30, 5 ) ) AS TMP; Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Regular Exp help
Ravi, Knight 4 to Pawn's 5! Sorry, being serious for a minute, you'd need more info to solve this problem. Your example implies that something without a number after it still counts for a value of 1, i.e. and 3 if I am searching for WT. Correct? What is the extent of the two letter combos? Are we talking just WT, BW, ME, SY, WE, OT and IT or are we talking about the whole space of AA to ZZ? You say the table is huge? 1 million records? 10 million? Just a rough ball park are these Var chars indexed? Just off the top of my head and without knowing what the actual context of the problem is I have to say it looks very difficult to do in pure SQL, not impossible just very difficult. Can you not use some glue code? Perl would be an ideal language, PHP would also be fine, any language would be better than SQL to be honest as it's just the wrong tool. I have a table with a varchar column that stores data in this pattern: numberone_white_spacestringone_white_spacehyphen_symbol The pattern may be repeated upto 5 times in same cell, but the string will be different in each case. Hence there will not be a value like this: 1 BW - 2.5 BW - 1 WT - 1 BW - 1 ME - 1 BW - 1 ME - 1.5 SY - 1 BW - 1 WT - 1 OT - 2.5 WE - 1 OT - 1 SY - 1 WT - 1 IT - 1 OT - I need a regular exp or any other query to fetch sum of numbers before a string. For example the result should be 2.5 if I am searching for SY and 3 if I am searching for WT. I tried a bit, but could not succeed. I am using 4.1.14-standard-log. The table is huge, hence the query should be streamlined enough. Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running a Staging and Development DB on the same server?
I'm setting up our one server for staging and development. I am need to set up MySQL to have 2 copies of the same database on the same server. Could anyone direct me to some documentation or tell me how to do this? Is there a reason you can't have one instance of the mysql server and simply have foo_staging and foo_development databases? Unless you are tweaking server parameters, this should work just fine... -philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query to find duplicate rows
Philip Hallstrom wrote: Hi all, a though query problem for me... I have a table with 2 rows that matter: url and id If url and id are the same in 2 rows, then that's no good (bad data). I need to find all the rows that are duplicates. I can't think of how to approach the sql for this.. any pointers? Select COUNT(*) as num_entries, url from table WHERE num_entries1 GROUP BY url Untested, but the concept should work for you. That would mark rows that have different id's, but the same url... No it wouldn't, he's grouping by the url. Yes, it would. As your example shows. Parent said If url and id are the same in 2 rows. In your example you have 1/google and 2/google and that is showing up in your final query. Which parent doesn't want.. unless I'm mis-reading his request... mysql create table a(a int, b text); Query OK, 0 rows affected (0.02 sec) mysql insert into a(a, b) values (1, 'google.com'); Query OK, 1 row affected (0.00 sec) mysql insert into a(a, b) values (2, 'google.com'); Query OK, 1 row affected (0.00 sec) mysql insert into a(a, b) values (3, 'yahoo.com'); Query OK, 1 row affected (0.00 sec) mysql select count(*) as num, b from a group by b; +-++ | num | b | +-++ | 2 | google.com | | 1 | yahoo.com | +-++ 2 rows in set (0.00 sec) The final query should be: mysql select count(*) as num, b from a group by b having count(*) 1; +-++ | num | b | +-++ | 2 | google.com | +-++ 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query to find duplicate rows
Hi all, a though query problem for me... I have a table with 2 rows that matter: url and id If url and id are the same in 2 rows, then that's no good (bad data). I need to find all the rows that are duplicates. I can't think of how to approach the sql for this.. any pointers? Select COUNT(*) as num_entries, url from table WHERE num_entries1 GROUP BY url Untested, but the concept should work for you. That would mark rows that have different id's, but the same url... SELECT t1.id, t2.id, t1.url FROM table t1, table t2 WHERE t1.id = t2.id AND t1.url = t2.url Expensive if you have a lot of rows, but should work I think... if my memory is right :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query cache question
What else would prevent queries from getting into the cache? Anything with NOW() in it or any of it's related functions... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mass E-mail Options
Again, I know this is not necessarily a MySQL specific question, and sorry for getting off target, but this is a pretty smart and experienced bunch, and I'm sure that someone out there will be able to help me. We have a web application in which we have a mass e-mail function. It allows the user to compose a simple text-only e-mail and send to everyone in our database (MySQL). Problem is that our e-mail server seems to be getting overloaded, and it just shuts down, causing an error. We're using ArgoSoft Mail server, which works very well for our normal needs. We do not want to change to Microsoft's Exchange Server. I hear it's expensive, and difficult to set up and get working properly. I was wondering if anyone knows of any alternative mass e-mail options. I don't want to use servers that spammers use, because first, and foremost, this is NOT spam, and second, some recipients may have these servers black listed. What other alternatives are there? http://www.lyris.com/products/mailengine/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mass E-mail Options
I would also try to unorder the email addresses, so that large groups of yahoo and aol etc emails do not hit the outbound queue at the same time. They tend to look down on that activity, and throttle you back, making it take even longer to get your messages out. You might put special-case conditons on specific domains, but actually, you're much better off ordering by domain because your server will end up sending _ONE_ message addressed to whatever number of recipients at that domain. But some providers will block based on number of recipients per message... so don't go crazy sending one message to 50,000 aol addresses, etc... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Zip Code Distance
This is not necessarily SQL Specific, but it will impact my MySQL application. Using the zip code, how can you determine what's in a (say) 50 mile radius. In other words, if I'm looking for schools within a 50 mile radius of my location (Zip Code 33881), How can I determine this by the zip code? I have seen lots of search engines that do this, but don't know how they determine the distance. I figured that I can use the first 4 digits as a match, then the first 3 digits which will be a little further away, but I don't know how accurate that would be. Does anyone have any ideas? Don't use the zip code itself. It might work for some areas, but certainly doesn't work in the western washington. It's just cut uptoo weird. We bought a zip code database from um... www.zipcodedownload.com which has a big list of cities, zips, and lat/long coordinates. Once you have the lat/long you can do the math easily... Not sure if they have a zip code only version... but the one we bought was like $30 and works just fine... -philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Cluster 5.0.24 (Import) Slow
Hi everybody I am running linuz AS-4 with 5.0.24 max version MySQL Cluster i am able to create all the table as ndb but when comming to the import i am not able to import 20 lakhs of record for a table.please help to solve the problem . 20 lakhs = 2 million rows? My memory is that cluster can only do operations in batches of about 30,000 rows at a time. So, if that import is using extended inserts (typical if it's a mysqldump output) it won't work. You need to insert them in batches of no more than 30,000 -philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incrementing using Max(Field) ?
On 8/15/06, Visolve DB TEAM [EMAIL PROTECTED] wrote: Hello William Try the below Query to insert next maximum value of the field into same table INSERT INTO Sample(id) SELECT MAX(id)+1 FROM Sample Wooww it works :D ... i didn't know that... great ! Wouldn't this cause a problem if more than one person were trying to insert a record in that table at the same time? Could that not cause the ID to be the same for 2 records Yes. It would. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design question
I want to design a database for lots of users. Each user will be managing their own messages. Does it make sense to create a table for each user after they've registered? Or should I just create one MESSAGES table and store messages there keyed off of their user_id? If I create a table for each user (I can potentially have hundreds of thousands of users), will MySQL be able to handle this? If I just have one table, I could potentially have millions of records in one table. Will MySQL be able to handle this? My gut feel is that MySQL will be fine with one table and millions of records. One table, with a user_id field. If you're worried about searching through millions of records, perhaps you could have archival tables that don't normally get searched and move messages from one to other after they get old... -p -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: db logic questions...
Bruce, i had initially thought that i could have the following tbl structure: UniversityTBL ( name ID auto_increment, ) In a generic kinda Best Practice, things I've picked up from various places way I'd recommend the following things: - 1) The first field always be the Primary Key and always call it 'ID'. 2) The second field onwards should contain your Foreign Key fields usually postfixed with FK, although ID is fine. 3) In an Object Orientated approach the attributes of the object come next so Name, post code whatever... 4) Lastly put a Soft_deleted enum (Y/N), Date_modified (CURRENT_TIMESTAMP default, and that auto update thingy) and Date_created timestamp on the end for auditing purposes 5) Index anything with ID or FK in it. 6) Don't bother with the TBL in the table name. 7) Choose and stick to a capitalization, pluralization and underscores for spaces policy, whatever floats your boat most is fine. my question though, is, is this approach the best. i'm also considering the Long and the short of it yes. the single tbl would be: tbl name parentID ID Don't for the love of all that's good! I've seen this carried to the extremes and it makes your nose bleed after a while, there are perfectly legitimate and good reasons for adopting this approach but it's probably a bad idea when the rows are going to represent different types of object. You won't be able to determine what type of object each record is, you'll end up with a gigantic blob of a table and it'll have a million different fields that don't apply to the 75% of the individual records. Given the complexity of your problem and the fact that your all most certainly using an OO language stick with representing each object with it's own table. this could work. That's what they said about the chocolate teapot. Don't, trust me it's not a good idea. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another question on Cardinality??
Brent, Given that... You really have to match cardinality with distribution of values. ...sounds like hard work (well you actually have to think about it) and... considering all the above cases, what should I conclude? should I have indexes on these three fields? Looking for a specific answer than a depend on situation kind of an answer. Frankly I'd ask Why shouldn't I be indexing these fields? not the other way around, you said to consider a table with only a thousand rows right? Unless I'm missing something an index would cost a trivial amount of disk space. If the real case is 10's of millions rows then compared to the data set it's still going to trivial anyway. Just index everything and throw some more hard drives at it. As someone else pointed out however there's plenty of comparative info all over the web let alone MySQL's own site, you could probably find some approximate numbers to play with out there. Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Monitoring Slow Queries
Though I understand very well that it has been discussed lots of time before but I don't have time to browse through the previous archives and dig out the stuff I need. So, guys, I would be thankful if you could give me your valuable advice that I need right now. I just need to know 1) What parameters I need to set in my.cnf to log slow queries so that they stick out conspicuously and get noticed, and 2) How I can find out from the log that MySQL creates as a result of 1) as to which queries are running slow. mysqlsla is kind of handy... http://hackmysql.com/mysqlsla mysqlsla analyzes general, slow, and raw MySQL statement logs. Formerly called mysqlprofile, the new name reflects what the script really does: combined MySQL Statement Log Analysis. mysqlsla can read multiple MySQL general and slow logs (and logs containing raw SQL statements), combine them, then run various analyses on all the queries. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date comparisons
I've found something that works (in MySQL 5, anyway), but I don't know whether it's accepted practice. If I want to find all records with a date in, say, March 2006, it works if I use datefield like '2006-03%' because it's a string. This seems kind of obvious and a lot tidier than doing datefield = '2006-03-01' and datefield = '2006-03-31', but are there pitfalls I should know about? Speed would be my first thought... I'd time them. I'd also prepend 'explain' as I'm guessing the first won't use an index and the second will (assuming you have indexes). -p -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: example when indexing hurts simple select?
Questions; 1. Is there a way to decrease random seeks? E.g. mysqld config parameters, increase some buffer/cache sizes? 2. Optimize table: is there a way to rearrange data so that random seeks are minimized? 3. If we have to live with large number of random seeks does anyone know how the underlying FS can be optimized? What is the EXPLAIN output of each? Have you tried analyze table x; optimize table x; On a 100GB, these might table a while. You probably want to LOCK the table before running them. Is it MyISAM or Innodb ? -- Philip M. Gollucci ([EMAIL PROTECTED]) 323.219.4708 Consultant / http://p6m7g8.net/Resume/resume.shtml Senior Software Engineer - TicketMaster - http://ticketmaster.com 1024D/A79997FA F357 0FDD 2301 6296 690F 6A47 D55A 7172 A799 97F It takes a minute to have a crush on someone, an hour to like someone, and a day to love someone, but it takes a lifetime to forget someone... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
pgpool for mysql? Or, a gateway that redirects select queries to a host of servers?
Hi all - I'm looking for a way to have a group of mysql servers that are all slaves to a single master. That part I can do. However, what I'd like is a way to put another server in front of the entire cluster and have that pretend to be the mysql database, except that it would simply redirect all update/insert/deletes to the master and all selects to one of the slaves (randomly or weighted or whatever). There's an app for postgres called pgpool that does this, but I haven't found anything for mysql yet. http://pgpool.projects.postgresql.org/ I'd rather not get into multi-master if I can help it and I don't think I need it. And I don't want to get into ndb cluster either... Yes, I'm picky :-) It's also not as simple as changing my front end as we're using Rails and from what I can tell Rails can't do this unless I break up all my models into ReadModel and WriteModel which defeats the whole purpose. Any ideas? Thanks! -philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: performance between InnoDB vs MyISAM
Just want to share and confirm my findings on a performance issue I've been experiencing. My database is strictly non-transactional, but it's got about 200,000 records in this particular table. The table has a primary index, and 2 integers - one for the date and the other for the time. Among the other fields there's a text field which usually stores a huge amount of text. One thing I notice, is that under MyISAM running the following sql: select id from mytable where id in (#subselect to extract a set of ids) order by mydate desc, mytime desc The time taken is really bad, like 90 secs. But in InnoDB it is usually 8 secs. The time difference is too crazy to ignore. Can anyone explain this? Is there something in InnoDB that creates the magic? Innodb clusters the table data around the primary key... which is what you're searching on. So your query is able to go right to the spot and read the whole row, as opposed to myisam which would need to look it up in the index to find the position in the row, then go read the table itself to get the row. among other reasons I suppose. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL: Unlimited Forums, Scalablity Issues, advice please?
Hi there, I am in the midst of creating a forums hosting site. 1. It has to be highly scalable, so I doubt a single MYSQL db with TONS of subforums that simulate full forums would do. 2. Also regarding scalablity, I hope to Add capacity as and when its needed. So i'll have one server running initially, and when it gets too crowded, i'll get two servers etc. 3. I will be providing a user with a dashboard that allows him to view all his subscribed posts across ALL forums. So lets say a user is a member of 25 forums, this dashboard view will allow the user to view all his posts across all the forums. Does anyone have advice that could point me in the right direction? I have solved the scalability issue WITHIN a forum (code can handle million + posts easy), but I havent solved the issue of scaling MULTIPLE separate forums. What about having a single write master with many read-only slaves? Then modify your code so that posts go to the master and everything else happens on the slaves? Also, does there exist any php package that helps ease the process of deciding which Server/database to connect to? For example, someone accesses FORUM A, so the script would automatically know to direct all queries to the DB in SERVER 1 etc, and if i try to access FORUM J, it would connect to SERVER 2 etc. I could easily hard code this, but I was thinking what if internal IP addresses change, or I decide to migrate a busy forum to a server of its own etc, so perhaps there is a better available packaged solution designed for this task. Create a table on a central server that contains this mapping. This server could also hold the login tables as well... Just a thought. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Support between MySQL and PHP
Thanks to everyone's help. Instead of downgrading MySQL, I went ahead
and upgraded to PHP5. After I did that, it seemed to fix the
connection issue I was having.
~Philip
On Jan 30, 2006, at 4:41 AM, James Harvard wrote:
MySQL versions = 4.1 use a new, more secure authentication
protocol. Probably the version of PHP you are using does not
support it.
Ah, here it is:
http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html
HTH,
James Harvard
At 1:18 pm -0600 29/1/06, Philip R. Thompson wrote:
I just installed MySQL5 and currently have PHP 4.3.11 installed. I
am wanting to connect to the mysql database on localhost, but I
get the following results:
--
? $link = mysql_connect('localhost', 'user', 'password'); ?
Client does not support authentication protocol requested by
server; consider upgrading MySQL client
--
Well, I have the lastest stable version of MySQL, so I did some more
research on what the problem might be. When I checked my
information for PHP using phpinfo(), it gave me the Client API
version for MySQL was 3.23.49. So, I'm thinking my version of PHP
cannot connect to my version of MySQL. I then considered if I
installed the MySQLi extension for PHP (supports versions of MySQL
4.1), would that help me? Or, if I just upgraded PHP to version 5,
would that help me?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Support between MySQL and PHP
Hi all.
I figured this question was suitable for both the MySQL list and the
PHP-General list. Here's what I'm running into.
I just installed MySQL5 and currently have PHP 4.3.11 installed. I
am wanting to connect to the mysql database on localhost, but I
get the following results:
--
? $link = mysql_connect('localhost', 'user', 'password'); ?
Client does not support authentication protocol requested by
server; consider upgrading MySQL client
--
Well, I have the lastest stable version of MySQL, so I did some more
research on what the problem might be. When I checked my
information for PHP using phpinfo(), it gave me the Client API
version for MySQL was 3.23.49. So, I'm thinking my version of PHP
cannot connect to my version of MySQL. I then considered if I
installed the MySQLi extension for PHP (supports versions of MySQL
4.1), would that help me? Or, if I just upgraded PHP to version 5,
would that help me?
Does anyone have any suggestions on the direction I should go?
Thanks in advance,
~Philip
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
my.cnf files
Hi all. I am having some troubles with what should be contained within my my.cnf file. Would a few of you be willing to show me what yours consists of? Thanks, ~Philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Troubles installing MySQL5 via Darwin Ports
On Jan 24, 2006, at 5:13 AM, James Harvard wrote: Is there a reason why you're using Darwin Ports and not the installer that MySQL offers? James Harvard Yes. I tried the installer multiple times and it would not work. I got so fed up with it that I decided to give DP a chance. So far, neither has impressed me - this just has to do with the installation, not the functionality of MySQL. I will truck on... ~Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Troubles installing MySQL5 via Darwin Ports
Hi all. Let me clarify my subject line. I *think* I actually got all the 'MySQL5 +server' files installed correctly using Darwin Ports. The problem is that I am not able to start my server. I think that 'my.cnf' may also be incorrect. Here's what's happening... - When I try to start the server --- [Claire:share/mysql5/mysql] chimi% sudo /opt/local/share/ mysql5/mysql/mysql.server start Password: /opt/local/share/mysql5/mysql/mysql.server: line 234: cd: /opt/ local/libexec/mysqld: No such file or directory Starting MySQL ERROR! Couldn't find MySQL manager or server [Claire:share/mysql5/mysql] chimi% my.cnf --- [mysqld] datadir=/opt/local/var/db/mysql5 socket=/tmp/mysql.sock port=3306 [mysql.server] user=mysql basedir=/opt/local/libexec/mysqld [client] socket=/opt/local/lib/mysql5/bin/mysql • First of all, I check to see if 'mysqld' existed in /opt/local/ libexec/ and it does, so I don't know why it can't find it. Any ideas? • Second what is wrong with my.cnf? I have searched the MySQL installation stuff and have not found anything that's useful. I have also searched archives on Darwin Ports and found some similar problems, but nothing that completely helps! Sorry for my *newbness*! Please help me! =D ~Philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help needed with complex Query
Hi, I'm trying hard to figure out how to perform a special query in mysql 4.0. I have one table widgets which has a column widget-id (int) and one column number_of_parts (int). And then I have another table part_mapping which has one column widget-id (int) and one column part_id (int). part_id is unique throughout the part_mapping table. The idea is that every widget consists of several unique parts. Now I want to select all widgets which are complete, this means where SELECT COUNT(1) FROM `part_mapping` WHERE widget-id = ... equals the number_of_parts of widget-id in table widgets. What I could do is simply loop over table widgets and execute a select count for every wiget. This would result in a huge number if queries needed form my client which is something I'd like to avoid. I pretty much have no idea how I can do this without nested queries (and to be frank not even how to do it with them) so I'd really appreciate any help! kind regards Philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help needed with complex Query
[EMAIL PROTECTED] wrote: I pretty much have no idea how I can do this without nested queries (and to be frank not even how to do it with them) so I'd really appreciate any help! kind regards Philip Try this as a starting point: snip I think you were having two mental problems: 1) how to GROUP BY across tables (creating a JOIN). and 2) How to use a HAVING clause. If you Refer To the Fine Manual (RTFM) you can get examples and more explanations of both processes. Thanks a lot, this did the trick! kind regards Philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: email notification
I doubt you would find this feature in any database. You would have to implement at the application code level. Basically whenever you insert into a table have the application framework send an e-mail. The log4j package provides this kind of functionality. See http://logging.apache.org/ And look for information on the SMTP appender. Cheers, Philip. -Original Message- From: Jayson [mailto:[EMAIL PROTECTED] Sent: May 31, 2005 6:59 AM To: mysql@lists.mysql.com Subject: email notification I'm looking for a feature in mysql where it will email me if ther are any changes in a particular database or table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: basic sql join question...
On May 30, 2005, at 1:28 AM, [EMAIL PROTECTED] wrote: you can just play : set @total:=0; select name,price,quantity, price*quantity as subtotal,@total:[EMAIL PROTECTED] from fruits; select @total as grand total; works great. thanks very, very much. - philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: basic sql join question...
On May 29, 2005, at 1:41 AM, Jochem van Dieten wrote: http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html already read that. the join in my example is more complicated than anything depicted on that page. please explain. - philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: basic sql join question...
http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html already read that. the join in my example is more complicated than anything depicted on that page. please explain. actually i should say that there are no examples of SUM() or AVG() -- or any of the other GROUP BY functions -- that are used with a join on that page. that's why i mentioned the join. sorry. i should have been more clear. - philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: basic sql join question...
The join is irrelevant. Your join returns a resultset and you can just pretent that resultset is a single table: SELECT field1, field2, field3 FROM (very complicated join) AS simpletable GROUP BY ... WITH ROLLUP Just copy-pate your join into this and fix the fieldnames. aaahhh okay, i'm close: mysql selectticket_details.quantity, product.name, product.price, (product.price * ticket_details.quantity) as subtotal from product, ticket_details where ticket_details.ticket = '9f2d7b86-213d-1028-88b7-09e76b61a517' AND ticket_details.product = product.id group by subtotal with rollup ; +--++---+--+ | quantity | name | price | subtotal | +--++---+--+ |1 | orange | 0.97 | 0.97 | |3 | pear | 1.09 | 3.27 | |3 | pear | 1.09 | NULL | +--++---+--+ the NULL is in the wrong column. where is my mistake? thanks, jochem. - philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: basic sql join question...
actually, i've decided this is sort of a moot point, since i can do this calculation in the client app. no sql required. thanks. - philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql and php
try looking at these two pages: http://dev.mysql.com/doc/mysql/en/access-denied.html http://dev.mysql.com/doc/mysql/en/connecting-disconnecting.html search them both for '2002' (multiple instances on one of the pages). there a couple suggestions for things to try. hth. - philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql and php
one of the posts at the bottom of one of those pages mentions that the permissions on the sock file might not be right. mine is: srwxrwxrwx 1 mysqlwheel0 29 May 06:41 mysql.sock you probably already know all this, but just in case... to get those permissions set up: stop mysqld chown mysql:wheel /tmp/mysql.sock chmod 4777 /tmp/mysql.sock restart mysqld - philip On May 29, 2005, at 12:33 PM, Philip George wrote: try looking at these two pages: http://dev.mysql.com/doc/mysql/en/access-denied.html http://dev.mysql.com/doc/mysql/en/connecting-disconnecting.html search them both for '2002' (multiple instances on one of the pages). there a couple suggestions for things to try. hth. - philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql and php
On May 29, 2005, at 11:13 AM, [EMAIL PROTECTED] wrote: ?php $server = localhost:/var/mysql/mysql.sock; $user=mysuperuser; $password=mypassword; $db = mysql; mysql_connect($server, $user, $password); mysql_select_db($db); echo mysql_error(); phpinfo(); ? I get these: Warning: mysql_connect(): Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (13) in /Library/WebServer/testpad/test.php on line 3 Warning: mysql_select_db(): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) in /Library/WebServer/testpad/test.php on line 3 Warning: mysql_select_db(): A link to the server could not be established in /Library/WebServer/testpad/test.php on line 3 Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) Checking mysqladmin shows this var/mysql/mysql.sock. also, i think it's possible that you'll need a leading slash in front of var/mysql/mysql.sock in mysqladmin. /var/mysql/mysql.sock so that it definitely matches what you've got in your mysql_connect() call. speaking of which, there are several interesting notes on this subject on the mysql_connect() page on the php site: http://us3.php.net/manual/en/function.mysql-connect.php but, my guess is that the missing leading slash in the socket path in mysqladmin is placing the sock file in some weird place that php won't find. also, make sure there's a mysql directory in /var of course. - philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql and php
mysql.sock is created by mysqld when it starts, and destroyed when it stops. haha. you're absolutely right. that doesn't make any sense. i wasn't thinking about the nature of socket files when i wrote that. now that i go back and read the post i was referring to, i think the poster was actually talking about the privileges of the enclosing folder, not the socket itself. my bad. but, regardless, i think it's a path problem anyway, as i mentioned earlier. pardon the misfire. running on zero sleep. - philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: basic sql join question...
On May 29, 2005, at 2:34 PM, [EMAIL PROTECTED] wrote: you can use mysql variables : set @total:=0; select name,price,quantity, price*quantity as subtotal,@total:[EMAIL PROTECTED] from fruits; ++---+--+-- +---+ | name | price | quantity | subtotal | @total:[EMAIL PROTECTED] | ++---+--+-- +---+ | orange | 1 |2 |2 | 2 | | banana | 1 |4 |4 | 6 | ++---+--+-- +---+ The total column will be incremented by subtotal in each row. actually, i need a grand total of the entire ticket: 1 orange x 0.97 = 0.97 + 3 pears x 1.09 = 3.27 _ 4.24 grand total i think you're right that variables can be used to do this, but i can't figure out how to get a grand total for the entire sale. but, i wouldn't scratch your head about it too much. i sort of gave up on the idea in favor of doing the calculation in the client code at runtime after selecting all the pertinent data. thanks though. :) - philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
proper varchar size for uuid storage
i see a lot of variation out there on this subject. some people are using varchar(64), some varchar(32), and some other non-base2 widths. what is the proper width? i count 36 characters below, including the four dashes. mysql select uuid(); +--+ | uuid() | +--+ | 8ce826a8-2122-1028-88b7-09e76b61a517 | +--+ 1 row in set (0.00 sec) are there times when the uuid() function produces output of a longer or shorter length than 36 characters? thanks. - philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
