SQL Scripts web site
Hello, This is an announcement to all MySQL developers. I have opened my web site www.SQL-Scripts.com up to MySQL . This means that you can now search for scripts for MySQl and other database system on this site. www.SQL-Scripts.Com is targeted at becoming the number on search engine for database scripts on the internet, we hope that you will all visit this site. We are also on the look out for any and all scripts for MySQL. Having a quick search on the Internet I could find many scripts at all, compared to Oracle or MS SQL Server. If you have any general use script (things like list table, view, backup system) anything that others might like to know, no matter how simple they are, please lodge them on the site. Registration is free. In addition to scripts there are many documents, news feeds and articles. Please help to make this site great, as you know (as with MySQL) it takes a community to make these things geat. Sorry for the intruption Webmaster at www.SQL-Scripts.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help! recover from running mysql_fix_privilege_tables on mysql4.0
Rusma Mulyadi [EMAIL PROTECTED] wrote: While trying to fix some problems with the user access privileges on mysql4.0 alpha, I run the mysql_fix_privilege_tables script. http://dev.mysql.com/doc/mysql/en/Upgrading-grant-tables.html As the result, I can't get to the database, even using the root login. I tried to recover root password using the information in the following page without luck http://dev.mysql.com/doc/mysql/en/Resetting_permissions.html Please, be more detailed. Did you get any error when you try to set password or you reset password successfully but still can't connect? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What's the best way to align master and slave before starting replication?
Hello colleagues, I want to set up a replication schema on MySQL (ISAM tables). I then have to align the databases before starting the replication. I would like to NOT stop the database to do the alignment because the application needs a lot of time to startup (1 hour) and I may have to start and stop the replication several times during the application life (don't care about binary logs, they are always enabled!) My database is composed by several pieces ... What is the best way to perform the alignment including the costs Vs. benefits? * tar cvf of the datafiles? Is it absolutely needed to shutdown the database? * mysqlhotcopy? can I keep ON the application? * mysqldump? How can I to lock all the database structures? what's about the application transactions? are they frozen on the COMMIT until the tables are unlocked? Thanks Enrico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
making mysql work with c++
hi... i was interested in making mysql work with c++... how would i go about doing this? to be more exact i use borland builder 6 and i have databases that i want to use with it and dont know how to use it with c++... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: making mysql work with c++
You can start by downloading the official C++ API. Get it from MySQL Developer Zone http://dev.mysql.com/downloads/other/plusplus/index.html Ciao! Andy B wrote: hi... i was interested in making mysql work with c++... how would i go about doing this? to be more exact i use borland builder 6 and i have databases that i want to use with it and dont know how to use it with c++... Questo messaggio ed ogni suo allegato sono confidenziali e possono essere riservati o, comunque, protetti dall'essere diffusi. Se il ricevente non il destinatario diretto del presente messaggio, pregato di contattare l'originario mittente e di cancellare questo messaggio ed ogni suo allegato dal sistema di posta. Se il ricevente non il destinatario diretto del presente messaggio, sono vietati l'uso, la riproduzione e la stampa di questo messaggio e di ogni suo allegato, nonch la diffusione del loro contenuto a qualsiasi altro soggetto This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, please contact the sender and delete this message and any attachment from your system. If you are not the intended recipient you must not use, copy or print this message or attachment or disclose the contents to any other person. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign keys help
- snip - Because your tables are not InnoDB. Check if InnoDB is enabled: SHOW VARIABLES LIKE have_innodb; - snip - no, InnoDB is not enabled. how can i enable it? the referece manual show a my.cnf configuration for a machine with at least 2gb of ram and 60 of hard disk. how can i adapt this configuration for an home usage? is this a sufficient condition to emerge innodb tables? tnx a lot -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User variables + SUM + GROUP BY = strange behavior
well, it seems to be fine without SUM and GROUP BY... E.g., SELECT @a:=Charge, @b:=Cost, @[EMAIL PROTECTED] as Margin ... produces expected results. Emmett Bishop wrote: Vadim, if I'm not mistaken, you can't set a variable then use it in the same statement. See http://dev.mysql.com/doc/mysql/en/Variables.html A little ways down the page... The general rule is to never assign and use the same variable in the same statement. -- Tripp --- Vadim P. [EMAIL PROTECTED] wrote: Sorry, the message got garbled, here is a more digestible look: -Original Message- Hello all, Could anyone comment on User Variable behavior in the example below? Thanks, Vadim. = mysql SELECT -LEFT(CallTime,10) AS CallDate, -@a := SUM(Charge), -@b := SUM(Cost), -@a - @b, -@a, -@b - FROM Calls - GROUP by CallDate - ORDER BY CallDate DESC; ++--++-++- | CallDate | @a:= SUM(Charge) | @b:= SUM(Cost) | @a - @b | @a | @b ++--++-++- ... | 2004-03-01 | 621.059 |249.310 | 30.882 | 39.512 | 8.63 | 2004-02-29 | 54.620 | 17.660 | 30.882 | 39.512 | 8.63 | 2004-02-28 | 205.581 | 17.460 | 30.882 | 39.512 | 8.63 | 2004-02-27 | 622.282 |248.920 | 30.882 | 39.512 | 8.63 | 2004-02-26 | 607.274 |277.100 | 30.882 | 39.512 | 8.63 | 2004-02-25 | 709.698 |308.580 | 30.882 | 39.512 | 8.63 | 2004-02-24 | 783.210 |298.560 | 30.882 | 39.512 | 8.63 | 2004-02-23 | 799.764 |252.890 | 30.882 | 39.512 | 8.63 ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to LOCK all the database??
Hello colleagues. How can I global lock a database? Global means that no user/process should be able to access it; I want to keep the database in read only for a certain period, NOT ONLY for the current session. thanks Enrico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to LOCK all the database??
Hello colleagues. Hi, How can I global lock a database? Global means that no user/process should be able to access it; I want to keep the database in read only for a certain period, NOT ONLY for the current session. Have a look to Grant and User Privileges. You can create a read only user, put him only select priv, and then use this user for read only ... Or modify an existing user to only set select privilege to him, do you work, and after give him back write access ... http://dev.mysql.com/doc/mysql/en/GRANT.html Hope this help. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP + MySQL Not Playing Nice Any More
On Fri, Apr 16, 2004 at 12:07:39AM -0400, Mark Susol | Ultimate Creative Media wrote the following: On 4/15/04 11:46 PM, Joseph A. Nagy, Jr. [EMAIL PROTECTED] wrote: So I'm using PHP and MySQL to serve up a game and all is going well until today. The first problem came when for some reason the game was sending apache as the username to access the db (which is not what I have in the dbconnect file) and output some errors. I checked the page it was complaining about and all was good so I use phpmyadmin to login and admin my db but now even phpmyadmin won't let me in and I know I haven't touched the config file since I first set it up. Does MySQL do this very often or is it a PHP error and not a MySQL one and if this is a PHP error where do I look to fix it? This seems more like a change was made in your hosting environment, unrelated to php or mysql. I've seen this happen when sites were moved into safe moded environments. That's odd since I run my own server and I know I haven't made any such changes. -- Joseph A. Nagy, Jr. http://joseph-a-nagy-jr.homelinux.org Political Activist Extraordinaire Peace, Life, Liberty The only fallacy is the inaction on our part to stave off the worst of horrors, the stripping of personal freedom. -- Joseph A. Nagy, Jr. January 2004 pgp0.pgp Description: PGP signature
Re: foreign keys help
saiph [EMAIL PROTECTED] wrote: - snip - Because your tables are not InnoDB. Check if InnoDB is enabled: SHOW VARIABLES LIKE have_innodb; - snip - no, InnoDB is not enabled. how can i enable it? What version of MySQL do you use? 3.23.xx or 4.0.x? If you use 3.23 you should install MySQL-Max binaries or if you install from source distribution configure MySQL with --have-innodb option. More info you can find at: http://dev.mysql.com/doc/mysql/en/InnoDB_in_MySQL_3.23.html the referece manual show a my.cnf configuration for a machine with at least 2gb of ram and 60 of hard disk. how can i adapt this configuration for an home usage? For home usage you can use default values. is this a sufficient condition to emerge innodb tables? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fw: Mysql 4.0.18 Bug error Message
Dear Sir, OS : Win XP Professional, Mysql 4.0.18 Category :WinMySQLAdmin 1.4 Description: I've encountered bug error problem using Mysql 4.0.18 ver when in Dos Command prompt, using D:/mysql/bin/mysql --user root-- password give error message ERROR 2003 : Can't connect to Mysql server on 'local host' (10061) after I've install and configured Apache and PHP software. Since Mysql database server cannot function, I can't use the osCommerce template . Apache Web server can work when I test http://localhost as well as PHP software when test run http://localhost/test.php but not for Mysql 4.0.18 software, can't start the service. After having changed the setting in my.ini in WinMySQLAdmin 1.4, I returned to my computer to find that there MySQL service is notstarting. Since I did nothing else at all with MySQL administrator, restart the MySQL service using Start -- Administrative Tools -- Services doesn't start anymore on local computer, giving Error#2: The system can't find the file specified. Trying to bring up MySQL Administrator gives error 2003: Can't connect to MySQL server on 'localhost'(10061) Please help to troubleshoot and solve the bug error in Mysql 4.0.18, using Apache web server and PHP 1.3.29 software else can't use osCommerce to do E-Commerce project. Check error log (.err file in the MySQL data dir). Is there any error message? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
row locking
hi all, i have select statements in my java code which are followed by updates in my Innodb table. the desired behaviour shud have been check and then update but because of tomcat accepting connections the above becomes check check update update i dont want to set the table locking, as it will make the system slower how can i set exclusive row locking until the update is finished? Any pointers are appreciated. -Regards Sahil Aggarwal
Re: User variables + SUM + GROUP BY = strange behavior
Not sure on how exactly variables work in MySQL but I do know that according to ANSI SQL group bys are done before other things in the query. So your query would perform the group by then it would do the actual select. This could be one reason for strange results. Thanks, Andrew From: Vadim P. [EMAIL PROTECTED] To: Emmett Bishop [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: User variables + SUM + GROUP BY = strange behavior Date: Fri, 16 Apr 2004 05:50:12 -0400 well, it seems to be fine without SUM and GROUP BY... E.g., SELECT @a:=Charge, @b:=Cost, @[EMAIL PROTECTED] as Margin ... produces expected results. Emmett Bishop wrote: Vadim, if I'm not mistaken, you can't set a variable then use it in the same statement. See http://dev.mysql.com/doc/mysql/en/Variables.html A little ways down the page... The general rule is to never assign and use the same variable in the same statement. -- Tripp --- Vadim P. [EMAIL PROTECTED] wrote: Sorry, the message got garbled, here is a more digestible look: -Original Message- Hello all, Could anyone comment on User Variable behavior in the example below? Thanks, Vadim. = mysql SELECT -LEFT(CallTime,10) AS CallDate, -@a := SUM(Charge), -@b := SUM(Cost), -@a - @b, -@a, -@b - FROM Calls - GROUP by CallDate - ORDER BY CallDate DESC; ++--++-++- | CallDate | @a:= SUM(Charge) | @b:= SUM(Cost) | @a - @b | @a | @b ++--++-++- ... | 2004-03-01 | 621.059 |249.310 | 30.882 | 39.512 | 8.63 | 2004-02-29 | 54.620 | 17.660 | 30.882 | 39.512 | 8.63 | 2004-02-28 | 205.581 | 17.460 | 30.882 | 39.512 | 8.63 | 2004-02-27 | 622.282 |248.920 | 30.882 | 39.512 | 8.63 | 2004-02-26 | 607.274 |277.100 | 30.882 | 39.512 | 8.63 | 2004-02-25 | 709.698 |308.580 | 30.882 | 39.512 | 8.63 | 2004-02-24 | 783.210 |298.560 | 30.882 | 39.512 | 8.63 | 2004-02-23 | 799.764 |252.890 | 30.882 | 39.512 | 8.63 ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Get rid of annoying pop-up ads with the new MSN Toolbar FREE! http://toolbar.msn.com/go/onm00200414ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help with indexing !
Using mysql 3.23.53, i have a table with approx 1.000.000 records, and only 3 columns. One of these (called 'value') is usually between 50 and 150 characters, but occasionally jumps to a current maximum of 570 characters, so I figured I had to go for the TEXT type. now I tried to add an index on this column. Using length 150 does not work : mysql just bails out pretending everything went fine, but there's no index to be seen. Using length 100 does create the index, but afterwards any query on that column will return 0 results. So the index is bogus (although it is 27MB !) I'm a bit reluctant to upgrade to 4.x, since I'm afraid other stuff will break. can anyone help me out here ? thanks ! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: row locking
Are you running the queries in parallel or serially? Are you using the same connection? Are you issuing Low Priority Updates? -Original Message- From: Sahil Aggarwal To: [EMAIL PROTECTED] Sent: 4/16/04 8:27 AM Subject: row locking hi all, i have select statements in my java code which are followed by updates in my Innodb table. the desired behaviour shud have been check and then update but because of tomcat accepting connections the above becomes check check update update i dont want to set the table locking, as it will make the system slower how can i set exclusive row locking until the update is finished? Any pointers are appreciated. -Regards Sahil Aggarwal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help with indexing !
Why not try to create a full text index on the column? -Original Message- From: jeroen clarysse To: [EMAIL PROTECTED] Sent: 4/16/04 9:00 AM Subject: Need help with indexing ! Using mysql 3.23.53, i have a table with approx 1.000.000 records, and only 3 columns. One of these (called 'value') is usually between 50 and 150 characters, but occasionally jumps to a current maximum of 570 characters, so I figured I had to go for the TEXT type. now I tried to add an index on this column. Using length 150 does not work : mysql just bails out pretending everything went fine, but there's no index to be seen. Using length 100 does create the index, but afterwards any query on that column will return 0 results. So the index is bogus (although it is 27MB !) I'm a bit reluctant to upgrade to 4.x, since I'm afraid other stuff will break. can anyone help me out here ? thanks ! -- 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: Multi-User Issues
Hey, Maybe just whip something up and let us see. If there is more interest after that then maybe you could do the latter. Regards, Justin Palmer -Original Message- From: Joshua J. Kugler [mailto:[EMAIL PROTECTED] Sent: Thursday, April 15, 2004 4:54 PM To: [EMAIL PROTECTED] Subject: Re: Multi-User Issues Just to get a general feel for interest: Should I just whip up something quick and dirty and post to the mailing list, or should I work up a nice page or two and put it on a web site? Anyone else interested? Warnring: to work up something, it might be a week or two as school is getting really busy right now, but I'd love to do it, as I've used MySQL in multi-user environments. j- k- On Thursday 15 April 2004 03:05 pm, Justin Palmer said something like: Hi Joshua, I would love to here more about multi-user issues (like record locking). I searched the archives by the title and by your name with no luck. If you don't feel like going into detail, could you point out some good links to learn more about the subject. Regards, Justin Palmer -Original Message- From: Joshua J. Kugler [mailto:[EMAIL PROTECTED] Sent: Thursday, April 15, 2004 3:27 PM To: [EMAIL PROTECTED] Subject: Re: Learning curve Mike - You didn't indicate your department, so I'm not sure what your background is. Your message, overall, is a bit scary, as any university that far behind right now would be worrisome. I'm not exactly sure what you're asking for (as you didn't ouline your requirements), but I would first take a look on sites like sourceforge or freshmeat for systems that already do what you want. I'm sure the kind of record keeping you do has been done before. But as to your main quesiton, it is very doable. You just need to keep in mind multi-user issue like record locking. Search the archives for messages by me about record locking for an elegant way to do it via a flag field. If you can't find it, let me know, and I'll type it up again. j- k- On Thursday 15 April 2004 02:06 pm, Mike T. Caskey said something like: Hi all! I'm wondering if anyone can help me find out how much time/training is needed to accomplish my task using MySQL. My background: I'm fresh to the world of MySQL and databases in general. I do have some fundamental knowledge in the area of programming and databases, but nothing too in-depth. My story: I work for a University that is seemingly falling behind the technical times. My department is using MS Access as the primary software for handling data, but we're still mainly hard-copy for our records-management. Obviously, there are problems with keeping hard-copy for everything. I was buried in paperwork for a short while before I decided to create simple databases/forms using OpenOffice.org, since it was so easy. Someone in management noticed the consistency emerging from my office and inquired. When I told them about my databases, they decided everyone in the department could benefit from them and assigned the project of making this available to all. My problem: My databases are single-user systems for use in OpenOffice.org and would be difficult to roll them out to my entire team. I don't want to install OO.o on everyones computer and I don't want to learn MS Access as it is known for being a temporary solution. So I need something that can keep up with the times and can be rolled out easily (web interface?). I also need to be able to append scanned images to records (PDF or JPEG?). This is all pretty complex and I'm definitely not technically equipped to create this just yet. MySQL?: I believe a good question would be whether or not MySQL would be a good solution for this. What do you think? Also, how long would it take me to learn the necessary information? Lastly, how long would it take to develop such a system? I appreciate your time and information! Thanks, Mike T. Caskey -- Joshua J. Kugler Fairbanks, Alaska Computer Consultant--Systems Designer .--- --- ... ..- .--.- ..- --. .-.. . .-. [EMAIL PROTECTED] ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Joshua J. Kugler Fairbanks, Alaska Computer Consultant--Systems Designer .--- --- ... ..- .--.- ..- --. .-.. . .-. [EMAIL PROTECTED] ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General
Re: MySQL 5.0.0-alpha-max-debug running on localhost as ODBC@localhost
Marvin Cummings [EMAIL PROTECTED] wrote: This appears when I attempt to open phpMyAdmin. It doesn't matter what account I set in the config.inc.php file, I continue to get this error. How do I change this so that another account logs in to the localhost? I unfortunately had to reboot my server and this became the result. I notice that I can log in to MySQLCC and the command line as root without a problem. What error do you get? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: repeated/duplicated query problem
Which logs are you referring to? For you testing purposes, if you have not already, I would enable the general log and see what is happening on the MySQL side. -Original Message- From: Josh Endries To: [EMAIL PROTECTED] Sent: 4/15/04 9:27 PM Subject: repeated/duplicated query problem Hello fellow listoers, Long time reader first time poster. :P I just recently installed a MySQL server to back-end a mail system (PHP webmail, SMTP, and IMAP servers). Everything is working pretty well, but I have a reallt odd problem I can't difure out. I've asked a few times in #Mysql on freenode.net and noone has seen it before; hoepfully someone here can help me troubleshoot it or fix it. First some info: Pentium 4 3.20 GHz w/HT (2 logical CPUs) 1GB RAM FreeBSD 4.9 MySQL 4.0.18 (via ports) When I was setting up the whitelist/blacklist stuff in webmail, I noticed it was adding two records for every one entry on the list (when I added [EMAIL PROTECTED], two would be put in the db). Deleting them also removed two, so I didn't notice at first. After screwing up some things and discovering a huge table with lots of duplicates, I started watching the logs. After spending probably hours playing with loops and other PHP things I noticed that, at the end of each script I ran, the last (and only the last) query got repeated. If I was adding a user to the whitelist, the final INSERT query was sent/processed two times. I hacked around this (had to get it up and running :( sigh) by adding a null 'SELECT '' from table... query, which was sone twice. Since this was the last query, the real queries were only done once, so its a patch but not a fix. I watched as I checked with my IMAP clients (Mozilla and PHP) and the password SELECT query was repeated. I watched as I sent myself some email and some of the queries from the SMTP server were repeated. Luckily these are all SELECTs; I do any INSERTs, UPDATEs, and MODIFYs manually right now, except for spam settings mentioned above, but this will change in the future. Basically I'm saying it isn't a PHP issue, or a script looping issue, and seems to me like a MySQL (and/or FreeBSD) issue. I guess it could be a client issue, but it's odd that every application accessing the server has the same problem. Maybe a FreeBSD MySQL client/library port problem. Based on talks in #mysql, I watched the logs when using the mysql client (both localhost socket file and over TCP like the other servers), and mysql only ran the commands once -- no duplications. I'm pretty stumped as to what the problem is and what to do about it, or even how to diagnose the problem. I'm not sure if it duplicates the last query on a per-connection basis, or something else...but it only duplicated the last query in the PHP scripts (probably on a per-connection basis). Thanks, Josh -- 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]
multi column index and used key_len
Hello,
I reached a point I can't go any further with forcing mysql to use the
index as much as needed. I am facing a multi column index of which only
the first column is used.
Let me explain...
This works fine:
create table A ( a mediumint not null, b date not null, index (a,b));
Populate the table with, say 5000 records.
select count(*)
from A
where a in (1,2,3)
and b in ('2004-05-01', '2004-05-02');
The explain command will tell you that de index will be used with
key_len of 6 (3 bytes for mediumint and 3 for date).
Now the next step. Suppose the values of column a that we are looking
for can be found in tabel B.
create table B (a mediumint not null primary key);
populate this tabel with 10 rows or so.
Lets do a join, e.g.:
select count(*)
from A, B
where A.a = B.a
and b in ('2004-05-01', '2004-05-02');
And now the index is used with key_len = 3 (i.e. only on column a)
Until now I found three ways to let mysql use the index with key_len = 6
(i.e. both columns):
1. in cases there is only one date: and b in ('2004-05-01')
2. delete rows from A until there are 1500 left or so.
3. avoid the join and pass value a via where a in (...,...,...)
The third one is very annoying, since extra queries are needed and
string concatenation in the application is needed. The other 2 are
simply not possible because the resultset becomes irrelevant in that
case ;-)
In reality I use a table with about 7 million rows. And then it really
makes a difference when the index on both columns is used or not!
I used version 4.0.18 and tested with standard setting of mysqd as well
as with these modifications:
key_buffer=64M
table_cache=256
sort_buffer=4M
read_buffer_size=1M
Any suggestion how to deal with this?
Thanks in advance!
Herald
P.S. When needed I can give example dumps to reproduce the problem.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
rename database
Experts: Is it possible to rename existing database ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Foreign Keys
hi, I am new to mysql. I am trying to create tables with foreign key constraints. but the constraintsdoesnt seem to be showing any effect on the table. I am able to add any info in the foreign key table witout the same info in the main table. what is the problem?? Liza -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Keys
Hi, I am new to mysql. I am trying to create tables with foreign key constraints. but the constraintsdoesnt seem to be showing any effect on the table. I am able to add any info in the foreign key table witout the same info in the main table. what is the problem?? 1) are you running the InnoDB version of MySQL? 2) are you using InnoDB tables? If (1) and (2) are not satisfied, MySQL will parse your SQL and totally ignore everything that has to with Foreign Keys. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: rename database
You can rename it at the filesystem level. -Original Message- From: Chen, Jenny To: '[EMAIL PROTECTED]' Sent: 4/16/04 10:18 AM Subject: rename database Experts: Is it possible to rename existing database ? -- 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: Foreign Keys
Can you please post your DDL? -Original Message- From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 4/16/04 10:22 AM Subject: Foreign Keys hi, I am new to mysql. I am trying to create tables with foreign key constraints. but the constraintsdoesnt seem to be showing any effect on the table. I am able to add any info in the foreign key table witout the same info in the main table. what is the problem?? Liza -- 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: rename database
Thanks that's easy. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Friday, April 16, 2004 10:28 AM To: 'Chen, Jenny '; ''[EMAIL PROTECTED]' ' Subject: RE: rename database You can rename it at the filesystem level. -Original Message- From: Chen, Jenny To: '[EMAIL PROTECTED]' Sent: 4/16/04 10:18 AM Subject: rename database Experts: Is it possible to rename existing database ? -- 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: Re: help! recover from running mysql_fix_privilege_tables on mysql4.0
I get an error when trying to reset password. I start the db using: mysqld_safe --skip-grant-tables and reset the pwd using the mysqladmin command... below is the error message. mysqladmin: unable to change password; error: 'Can't find any matching row in the user table' thanks, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: rename database
At 10:28 -0500 4/16/04, Victor Pendleton wrote: You can rename it at the filesystem level. What if you have InnoDB or BDB tables? -Original Message- From: Chen, Jenny To: '[EMAIL PROTECTED]' Sent: 4/16/04 10:18 AM Subject: rename database Experts: Is it possible to rename existing database ? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: about flush logs
When I do a FLUSH LOGS my bin log does increment and a new one is created. My query log does not behave this way, however. Just the bin log. It creates files with the .00x extension where x is an incremental number. Lou - Original Message - From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, April 14, 2004 8:09 AM Subject: Re: about flush logs Tang, Grace H [EMAIL PROTECTED] wrote: In my box, mysql server version is 4.0.18. I tried flush logs. Nothing happened in the mysql data directory. All the log files were not replaced. Does flush logs rename the old log files and create new log files? FLUSH LOGS closes and reopens all logs. In doesn't rename anything. For update log if you didn't specify extention, FLUSH LOGS creates new log file. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: rename database
Oversight on my part. This will not work for those table types. -Original Message- From: Paul DuBois To: Victor Pendleton; 'Chen, Jenny '; ''[EMAIL PROTECTED]' ' Sent: 4/16/04 10:43 AM Subject: RE: rename database At 10:28 -0500 4/16/04, Victor Pendleton wrote: You can rename it at the filesystem level. What if you have InnoDB or BDB tables? -Original Message- From: Chen, Jenny To: '[EMAIL PROTECTED]' Sent: 4/16/04 10:18 AM Subject: rename database Experts: Is it possible to rename existing database ? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: rename database
Paul, Do you know if there will be an ALTER DATABASE RENAME curName TO newName implementation? -Original Message- From: Victor Pendleton To: 'Paul DuBois '; Victor Pendleton; ''Chen, Jenny ' '; '''[EMAIL PROTECTED]' ' ' Sent: 4/16/04 10:55 AM Subject: RE: rename database Oversight on my part. This will not work for those table types. -Original Message- From: Paul DuBois To: Victor Pendleton; 'Chen, Jenny '; ''[EMAIL PROTECTED]' ' Sent: 4/16/04 10:43 AM Subject: RE: rename database At 10:28 -0500 4/16/04, Victor Pendleton wrote: You can rename it at the filesystem level. What if you have InnoDB or BDB tables? -Original Message- From: Chen, Jenny To: '[EMAIL PROTECTED]' Sent: 4/16/04 10:18 AM Subject: rename database Experts: Is it possible to rename existing database ? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: row locking
At 18:57 +0530 4/16/04, Sahil Aggarwal wrote: hi all, i have select statements in my java code which are followed by updates in my Innodb table. the desired behaviour shud have been check and then update but because of tomcat accepting connections the above becomes check check update update i dont want to set the table locking, as it will make the system slower how can i set exclusive row locking until the update is finished? Have you tried using SELECT ... FOR UPDATE? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: rename database
At 11:03 -0500 4/16/04, Victor Pendleton wrote: Paul, Do you know if there will be an ALTER DATABASE RENAME curName TO newName implementation? I don't know. I agree it would be nice, though somewhat tricky. One workaround is to use mysqldump to dump the database, create the new database, reload the dump file into the new database, then drop the old database. That is, in effect, a database rename, although (I suspect) it can cause problems if you have foreign key relationships that refer to the table names in the original database. Another approach is to create the new database, and then, for each table in the original database, use RENAME TABLE orig_db.t TO new_db.t to move the table from one database to the other. Then drop the old database. -Original Message- From: Victor Pendleton To: 'Paul DuBois '; Victor Pendleton; ''Chen, Jenny ' '; '''[EMAIL PROTECTED]' ' ' Sent: 4/16/04 10:55 AM Subject: RE: rename database Oversight on my part. This will not work for those table types. -Original Message- From: Paul DuBois To: Victor Pendleton; 'Chen, Jenny '; ''[EMAIL PROTECTED]' ' Sent: 4/16/04 10:43 AM Subject: RE: rename database At 10:28 -0500 4/16/04, Victor Pendleton wrote: You can rename it at the filesystem level. What if you have InnoDB or BDB tables? -Original Message- From: Chen, Jenny To: '[EMAIL PROTECTED]' Sent: 4/16/04 10:18 AM Subject: rename database Experts: Is it possible to rename existing database ? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AW: command not found: trying to start mysql
I ran ./bin/safe_mysqld and got: [1] 3328 root # Starting mysqld daemon with databases from /usr/local/mysql/data and the cursor hangs on the next line down ... thanks, don Try ls to see whether safe_mysqld is in the directory and the run: Safe_mysqld Note! You have to be root to run the mysqld demon HTH Babs
Re:Foreign Key
at the mysql prompt i typed show variable like have_innodb; and the value was yes. I saw this in the mailing list and tried it. I am not familiar with Innodb . Do i have to install the Innodb version of Mysql? I have mysql 4.0 version. Liz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query with or without quotes
Just by curiosity is there any difference between this 2 queries select * from users where users_id=10 and select * from users where users_id='10' assuming that the users_id column is of type integer primary key (if it would be varchat I know there is a big difference) Looking for a job!? Use the smart search engine!! Find a Job from Millions WorldWide... http://search.jobsgrabber.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AW: command not found: trying to start mysql
At 9:30 -0700 4/16/04, Don Dachner wrote: I ran ./bin/safe_mysqld and got: [1] 3328 root # Starting mysqld daemon with databases from /usr/local/mysql/data and the cursor hangs on the next line down ... Your shell printed root #. safe_mysqld (running in the background) printed the Starting... messages, followed by a newline. That puts the cursor on the next line. But your shell is still waiting for a new command. Just type it in. Or hit Enter a few times to see that the shell is waiting for input. thanks, don Try ls to see whether safe_mysqld is in the directory and the run: Safe_mysqld Note! You have to be root to run the mysqld demon HTH Babs -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sub-select Inner Joins
Hi Here's a complicated bit sql that I'm trying to Post from MSAccess to MySql I searched UseNet and read that in MySQL you cannot have a sub-select, but I'm lost as to how to split this into two selects? Any help gratefully received!!! SELECT distinct p.ProjectID ,p.ProjectName FROM tbl_project AS p INNER JOIN ( ( (tbl_UnitType AS ut INNER JOIN tbl_RateType AS r ON ut.RateType = r.RateType) INNER JOIN (tbl_SubProject AS sp INNER JOIN (Select * FROM tbl_ProjectResource WHERE UserID = '#rptUserID#' AND Deleted = 0 AND ((ActivityDate) Between #datDateFrom# And #datDateTo#) ) AS pr ON sp.SubProjectID = pr.SubProjectID) ON ut.Unit = pr.Unit ) INNER JOIN tbl_RateTypeCategoryCharge AS rt ON pr.RateTypeCategoryChargeID = rt.RateTypeCategoryChargeID ) ON p.ProjectID = sp.ProjectID WHERE ut.reportDisplay = 1 zzapper (vim, cygwin, wiki zsh) -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AW: command not found: trying to start mysql
Ok, that worked. Thanks. I guess now my only question is is there something wrong with my environment since I have to type: ./bin/mysql -u root instead of mysql -u root? Thanks, Don At 9:30 -0700 4/16/04, Don Dachner wrote: I ran ./bin/safe_mysqld and got: [1] 3328 root # Starting mysqld daemon with databases from /usr/local/mysql/data and the cursor hangs on the next line down ... Your shell printed root #. safe_mysqld (running in the background) printed the Starting... messages, followed by a newline. That puts the cursor on the next line. But your shell is still waiting for a new command. Just type it in. Or hit Enter a few times to see that the shell is waiting for input.
Re:Foreign Key
At 12:44 -0400 4/16/04, [EMAIL PROTECTED] wrote: at the mysql prompt i typed show variable like have_innodb; and the value was yes. I saw this in the mailing list and tried it. I am not familiar with Innodb . Do i have to install the Innodb version of Mysql? I have mysql 4.0 version. That means the InnoDB storage engine is present and enabled, so you should be able to use InnoDB tables. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AW: command not found: trying to start mysql
At 10:01 -0700 4/16/04, Don Dachner wrote: Ok, that worked. Thanks. I guess now my only question is is there something wrong with my environment since I have to type: ./bin/mysql -u root instead of mysql -u root? I don't know about wrong, but that's certainly less convenient than it needs to be. Add the directory that contains the MySQL client programs to your PATH variable. If you're not sure how to do that, read this: http://www.kitebird.com/mysql-cookbook/path.pdf Thanks, Don At 9:30 -0700 4/16/04, Don Dachner wrote: I ran ./bin/safe_mysqld and got: [1] 3328 root # Starting mysqld daemon with databases from /usr/local/mysql/data and the cursor hangs on the next line down ... Your shell printed root #. safe_mysqld (running in the background) printed the Starting... messages, followed by a newline. That puts the cursor on the next line. But your shell is still waiting for a new command. Just type it in. Or hit Enter a few times to see that the shell is waiting for input. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
HIGH_PRIORITY with UNION
Hello, I am using HIGH_PRIORITY in my SELECTs to force queries to take predcedence over updating due to replication. I have recently implemented UNION in some of my queries to optimize queries like WHERE table1.column1=something OR table1.column2=somethingelse. Anyway, I first tried formatting my UNION query like (SELECT HIGH_PRIORITY ...) UNION (SELECT HIGH_PRIORITY ...) but the server complained about the placement of HIGH_PRIORITY. I was finally able to get it to accept the query by only specifiying HIGH_PRIORITY in the first part of the UNION, like (SELECT HIGH_PRIORITY ...) UNION (SELECT ...), but it appears that my searches are not taking precedence as they should, and as non-UNION queries do. Could there be another explanation for why they are not taking precedence, or is there another way to specify HIGH_PRIORITY in UNION queries to make both sub-queries high-priority? Thanks a lot! Chad Attermann [EMAIL PROTECTED]
Search in Longtext fields
Hi, Is it possible to make a search for a word in a longtext column from a InnoDB database? Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
update statistics question.
IF i run update statistics on MyISAM tables, will those tables lock while the statistics are being updated. This is the first time i think i need to update statistics on a production server, and i dont want to lock out my users for any time. There are about ten tables i'll need to run statistics on, each having between 1.5 million and 25 million rows. Any advice on how to best accomplish this would be great. thanks much, sean peters [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: repeated/duplicated query problem
Victor Pendleton wrote: Which logs are you referring to? For you testing purposes, if you have not already, I would enable the general log and see what is happening on the MySQL side. Thanks for the response. Sorry, I'm talking about the general log, that is how I noticed that there were multiple queries getting processed. This is my my.cnf in case it has any obvious problems: [client] ssl-ca=/etc/ssl/CA.cer ssl-cert=/etc/ssl/server.crt ssl-key=/etc/ssl/server.key [mysqld] ssl-ca=/etc/ssl/CA.cer ssl-cert=/etc/ssl/server.crt ssl-key=/etc/ssl/server.key log log-slow-queries long_query_time=1 skip-name-resolve So far we have no long queries...so 1 is okay, and the problem existed before that anyway. skip-name-resolve is there to fix the CPU problem. Everything uses really simple queries. I use SSL usually but the servers don't (haven't tried yet). Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: rename database
Thanks for the information. -Original Message- From: Paul DuBois To: Victor Pendleton; '''Chen, Jenny ' ' '; [EMAIL PROTECTED]' ' ' ' Sent: 4/16/04 11:18 AM Subject: RE: rename database At 11:03 -0500 4/16/04, Victor Pendleton wrote: Paul, Do you know if there will be an ALTER DATABASE RENAME curName TO newName implementation? I don't know. I agree it would be nice, though somewhat tricky. One workaround is to use mysqldump to dump the database, create the new database, reload the dump file into the new database, then drop the old database. That is, in effect, a database rename, although (I suspect) it can cause problems if you have foreign key relationships that refer to the table names in the original database. Another approach is to create the new database, and then, for each table in the original database, use RENAME TABLE orig_db.t TO new_db.t to move the table from one database to the other. Then drop the old database. -Original Message- From: Victor Pendleton To: 'Paul DuBois '; Victor Pendleton; ''Chen, Jenny ' '; '''[EMAIL PROTECTED]' ' ' Sent: 4/16/04 10:55 AM Subject: RE: rename database Oversight on my part. This will not work for those table types. -Original Message- From: Paul DuBois To: Victor Pendleton; 'Chen, Jenny '; ''[EMAIL PROTECTED]' ' Sent: 4/16/04 10:43 AM Subject: RE: rename database At 10:28 -0500 4/16/04, Victor Pendleton wrote: You can rename it at the filesystem level. What if you have InnoDB or BDB tables? -Original Message- From: Chen, Jenny To: '[EMAIL PROTECTED]' Sent: 4/16/04 10:18 AM Subject: rename database Experts: Is it possible to rename existing database ? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key
I creates 2 tables create table test1(Ser int(5) primary key,age int(2)) type=InnoDB; create table test2(Serno int(5) references test1(Ser),name varchar(20)) type=InnoDB; I inserted a value in test2 and it accepted. Isnt that wrong? shouldnt it accept only if there is a vlue in the first table? I didnt enter any records in the first table. Liza -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Foreign Key
Try creating your tables as such: CREATE TABLE test1(ser INT(5) NOT NULL, age INT(2), PRIMARY KEY (ser) ) TYPE=InnoDB; CREATE TABLE test1(serno INT(5) NOT NULL, name VARCHAR(20), KEY 'idx_serno' (serno), CONSTRAINT FOREIGN KEY(serno) REFERENCES `test1` (ser) ) TYPE=InnoDB; -Original Message- From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 4/16/04 12:26 PM Subject: Re: Foreign Key I creates 2 tables create table test1(Ser int(5) primary key,age int(2)) type=InnoDB; create table test2(Serno int(5) references test1(Ser),name varchar(20)) type=InnoDB; I inserted a value in test2 and it accepted. Isnt that wrong? shouldnt it accept only if there is a vlue in the first table? I didnt enter any records in the first table. Liza -- 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]
BUG and workaround (Was: Table lock problem on INSERT with FULLTEXT index?)
This problem is completely repeatable, I'm not the only one having it, and I've found a (temporary) workaround. I'm not sure if it affects other machines than AMD64, but it certainly affects them. I've tested on two now. If you start MySQL with skip-concurrent-insert, the problem completely disappears. I'm going to try to add it to MySQL's bug database, but as I've never used theirs before, I'm hoping some MySQL people might see this email and do a little investigating as well. More info: Even on my passive slave, which doesn't have any clients connected to it other than myself, the problem exists. (3 threads total, the two slave threads, and my connection) As the INSERT gets passed from the Master to the Slave, the Slave hangs on it indefinitely (I let it sit for 8000 seconds), despite the fact that no other clients or threads are accessing that, or any other, table. Previously, I had thought it was related to a race condition where some SELECT was hitting the table right before the INSERT or something, but since there are no SELECTs occurring on my slave, that can't be the case. Again, this is 4.0.18-max on AMD64. Only INSERTs ... UPDATE and DELETE work fine, but INSERT hangs. Thanks, Don Jaroslav Kocourek wrote: FWIW, I'm still having this problem. I've completely dropped the table and re-built it from the ground up. It's a bizarre problem... The table is totally simple. A primary key, and then three varchar fields. The FULLTEXT index spans the 3 varchar fields. There are only 6500+ rows, so it's pretty tiny. A mysqldump of the table is only 442K. Has no-one else seen anything like this? I can't imagine I'm the only one. Thanks, Don - Hi, I have the same problem, did you find some solution ? Thank Jaroslav Kocourek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: repeated/duplicated query problem
On Thu, 2004-04-15 at 22:27, Josh Endries wrote: When I was setting up the whitelist/blacklist stuff in webmail, I noticed it was adding two records for every one entry on the list (when I added [EMAIL PROTECTED], two would be put in the db). Deleting them also removed two, so I didn't notice at first. After screwing up some things and discovering a huge table with lots of duplicates, I started watching the logs. After spending probably hours playing with loops and other PHP things I noticed that, at the end of each script I ran, the last (and only the last) query got repeated. If I was adding a user to the whitelist, the final INSERT query was sent/processed two times. I hacked around this (had to get it up and running :( sigh) by adding a null 'SELECT '' from table... query, which was sone twice. Since this was the last query, the real queries were only done once, so its a patch but not a fix. I'm not very familiar with PHP. But I have had experiences with Perl CGIs multiply-executing under certain configurations if the program does not explicitly exit(); (as opposed to just having no more commands). Just a shot in the dark, but you never know. :) -- Sean Quinlan [EMAIL PROTECTED] signature.asc Description: This is a digitally signed message part
Re: AW: command not found: trying to start mysql
Thanks for the help! Worked perfectly. Don Paul DuBois [EMAIL PROTECTED] wrote: At 10:01 -0700 4/16/04, Don Dachner wrote: Ok, that worked. Thanks. I guess now my only question is is there something wrong with my environment since I have to type: ./bin/mysql -u root instead of mysql -u root? I don't know about wrong, but that's certainly less convenient than it needs to be. Add the directory that contains the MySQL client programs to your PATH variable. If you're not sure how to do that, read this: http://www.kitebird.com/mysql-cookbook/path.pdf Thanks, Don At 9:30 -0700 4/16/04, Don Dachner wrote: I ran ./bin/safe_mysqld and got: [1] 3328 root # Starting mysqld daemon with databases from /usr/local/mysql/data and the cursor hangs on the next line down ... Your shell printed root #. safe_mysqld (running in the background) printed the Starting... messages, followed by a newline. That puts the cursor on the next line. But your shell is still waiting for a new command. Just type it in. Or hit Enter a few times to see that the shell is waiting for input. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com
Re: BUG and workaround (Was: Table lock problem on INSERT with FULLTEXT index?)
Success with the bug entry. Nice system, too. http://bugs.mysql.com/bug.php?id=3483 Thanks, Don Don MacAskill wrote: This problem is completely repeatable, I'm not the only one having it, and I've found a (temporary) workaround. I'm not sure if it affects other machines than AMD64, but it certainly affects them. I've tested on two now. If you start MySQL with skip-concurrent-insert, the problem completely disappears. I'm going to try to add it to MySQL's bug database, but as I've never used theirs before, I'm hoping some MySQL people might see this email and do a little investigating as well. More info: Even on my passive slave, which doesn't have any clients connected to it other than myself, the problem exists. (3 threads total, the two slave threads, and my connection) As the INSERT gets passed from the Master to the Slave, the Slave hangs on it indefinitely (I let it sit for 8000 seconds), despite the fact that no other clients or threads are accessing that, or any other, table. Previously, I had thought it was related to a race condition where some SELECT was hitting the table right before the INSERT or something, but since there are no SELECTs occurring on my slave, that can't be the case. Again, this is 4.0.18-max on AMD64. Only INSERTs ... UPDATE and DELETE work fine, but INSERT hangs. Thanks, Don Jaroslav Kocourek wrote: FWIW, I'm still having this problem. I've completely dropped the table and re-built it from the ground up. It's a bizarre problem... The table is totally simple. A primary key, and then three varchar fields. The FULLTEXT index spans the 3 varchar fields. There are only 6500+ rows, so it's pretty tiny. A mysqldump of the table is only 442K. Has no-one else seen anything like this? I can't imagine I'm the only one. Thanks, Don - Hi, I have the same problem, did you find some solution ? Thank Jaroslav Kocourek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Workaround for ORDER BY DESC
I have 2 tables and 1 query. The problem is when I implement ORDER BY p.date DESC it hits the wall. I understand that MySQL is not the best at ORDER BY DESC so I am after some tips on possible workarounds to avoid using ORDER BY DESC. The site will list classifieds ads so I need to display them from newest to oldest using a timestamp. INDEXES Also as I have been playing with indexes for so long now still trying to understand them. I still have mixed signals to the way they work. When MySQL performs a query can it only use 1 index at a time or can it use several individual indexes. Advice I have been given is to place a seperate index on each column. If I was to perform the following query: EXPLAIN SELECT p.* FROM p_cat c, p_ad p WHERE p.cat = c.id AND c.lft BETWEEN 4 AND 5 ORDER BY p.date DESC LIMIT 0,30; then I would expect it to use the cat_date index on table p_ad as it can only use 1 index but if I am to believe others I should place a seperate index each on p_ad.cat and p_ad.date and it could use both in the same query. I look forward to some facts on this issue as I cant seem to catch on. Sorry to go on a bit but this is doing my head in. I look forward to your help.. Cheers Steven. 2 Tables CREATE TABLE `p_ad` ( `id` int(11) NOT NULL auto_increment, `cat` mediumint(9) NOT NULL default '0', `title` varchar(50) default NULL, `description` text, `location` varchar(50) default NULL, `pcode` varchar(8) default NULL, `pcode_id` smallint(4) default NULL, `ph` varchar(50) default NULL, `email` varchar(50) default NULL, `user_id` int(11) NOT NULL default '0', `date` timestamp(14) NOT NULL, `price` decimal(10,2) default NULL, `email_priv` tinyint(1) default '0', PRIMARY KEY (`id`), KEY `cat_pc_date` (`cat`,`pcode_id`,`date`), KEY `c_p_d` (`cat`,`pcode`,`date`), KEY `user` (`user_id`), KEY `cat_date` (`cat`,`date`) ) TYPE=MyISAM; CREATE TABLE `p_cat` ( `id` mediumint(9) NOT NULL auto_increment, `name` varchar(50) NOT NULL default '', `parent` mediumint(11) default '0', `lft` mediumint(11) NOT NULL default '0', `rgt` mediumint(11) NOT NULL default '0', PRIMARY KEY (`id`), KEY `LFT` (`lft`), KEY `PARENT` (`parent`) ) TYPE=MyISAM; Query as follows: EXPLAIN SELECT p.* FROM p_cat c, p_ad p WHERE p.cat = c.id AND c.lft BETWEEN 4 AND 5 ORDER BY p.date DESC LIMIT 0,30; +---+---++--+-+--+---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+---++--+-+--+---+-+ | p | ALL | cat_pc_date,c_p_d,cat_date | NULL |NULL | NULL | 60002 | Using temporary; Using filesort | | c | range | PRIMARY,LFT | LFT| 3 | NULL | 1 | Using where | +---+---++--+-+--+---+-+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multi column index and used key_len
Two small corrections:
ad point 1.
b in ('2004-05-01') behaves different from b = '2004-05-01'. The
last one is better: key_len = 6, instead of 3.
mysql explain select count(*) from A, B where A.a=B.a and b ='2004-05-01';
+---+---+---+-+-+---+--+--+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+---+---+---+-+-+---+--+--+
| B | index | PRIMARY | PRIMARY | 3 | NULL | 63 | Using index
|
| A | ref | a | a | 6 | B.a,const |1 | Using where;
Using index |
+---+---+---+-+-+---+--+--+
2 rows in set (0.00 sec)
mysql explain select count(*) from A, B where A.a=B.a and b in('2004-05-01');
+---+---+---+-+-+--+--+--+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+---+---+---+-+-+--+--+--+
| B | index | PRIMARY | PRIMARY | 3 | NULL | 63 | Using index
|
| A | ref | a | a | 3 | B.a | 182 | Using where; Using
index |
+---+---+---+-+-+--+--+--+
2 rows in set (0.00 sec)
ad point 2.
deleting rows can help, but the case I saw had 113 rows in B, not 10.
Then explain says it will use key_len=6 but I don't think it will use
the index at all, since number of rows in explain output is same as in
the whole table...
mysql explain select count(*) from A, B where A.a=B.a and (b ='2004-05-01' or
b='2004-05-02');
+---++---+-+-+--+--+--+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+---++---+-+-+--+--+--+
| A | index | a | a | 6 | NULL | 2545 | Using where;
Using index |
| B | eq_ref | PRIMARY | PRIMARY | 3 | A.a |1 | Using index
|
+---++---+-+-+--+--+--+
2 rows in set (0.00 sec)
mysql select count(*) from A;
+--+
| count(*) |
+--+
| 2545 |
+--+
1 row in set (0.01 sec)
mysql select count(*) from B;
+--+
| count(*) |
+--+
| 113 |
+--+
1 row in set (0.00 sec)
Hey! Some good news: I think I have found a work around: put the days
also in a (maybe temporary) table and the full key is used:
create table C (b date not null primary key);
insert into C values ('2004-05-01', '2004-05-02');
explain select count(*) from A, B, C where A.a=B.a and A.b=C.b ;
+---+---+---+-+-+-+--+-+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+---+---+---+-+-+-+--+-+
| B | index | PRIMARY | PRIMARY | 3 | NULL| 63 | Using index |
| C | index | PRIMARY | PRIMARY | 3 | NULL|2 | Using index |
| A | ref | a | a | 6 | B.a,C.b |1 | Using index |
+---+---+---+-+-+-+--+-+
3 rows in set (0.01 sec)
It looks silly to me, but is happens to work.
Herald
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign keys help
alea mysql -V mysql Ver 12.22 Distrib 4.0.17, for pc-linux-gnu (i386) but mysql SHOW VARIABLES LIKE have_innodb; +---+---+ | Variable_name | Value | +---+---+ | have_innodb | NO| +---+---+ 1 row in set (0.09 sec) why? the gentoo ebuild configure mysql with innodb support: where i m getting wrong? how can i see a yes working 'value'? tnx again -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql/innodb configuration
I would like to optimize the configuration settings for this beast of a machine, here are the specs: Quad Xeon 3ghz (4x2 = 8 cpus), 512 cache 16 gigs ram running Redhat Enterprise 3.0 AS All tables are InnoDB. I read this warning in the MySQL documentation: *Warning:* On GNU/Linux x86, you must be careful not to set memory usage too high. |glibc| will allow the process heap to grow over thread stacks, which will crash your server. But at the same time it says: # Set buffer pool size to 50-80% of your computer's memory, # but make sure on Linux x86 total memory usage is 2GB Does this mean that MySQL wont make use of the 16gb it has total ? I had to set the value to 1G to make it even start up. What other parameters can I tweak in the conf for maximum performance ? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Query Question
I have a simple table where one of the columns is named queue_time and is defined as a timestamp-type. I would like to query this table for all rows where the queue_time equals the current date. I an a newbie and have been wrestling with the docs for a solution. You help will be appreciated. Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Query Question
This works for Oracle, give it a try, use any format you want for the MM/DD/YY area. select to_char(queue_time, 'MM/DD/YY'); Scott Purcell -Original Message- From: Dirk Bremer (NISC) [mailto:[EMAIL PROTECTED] Sent: Friday, April 16, 2004 2:55 PM To: [EMAIL PROTECTED] Subject: SQL Query Question I have a simple table where one of the columns is named queue_time and is defined as a timestamp-type. I would like to query this table for all rows where the queue_time equals the current date. I an a newbie and have been wrestling with the docs for a solution. You help will be appreciated. Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc -- 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: SQL Query Question
WHERE queue_time = Now() + 0 Are you wanting just the date or the datetime? -Original Message- From: Dirk Bremer (NISC) To: [EMAIL PROTECTED] Sent: 4/16/04 2:54 PM Subject: SQL Query Question I have a simple table where one of the columns is named queue_time and is defined as a timestamp-type. I would like to query this table for all rows where the queue_time equals the current date. I an a newbie and have been wrestling with the docs for a solution. You help will be appreciated. Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc -- 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: Facing problem with connection on LAN
you are probably missing privileges for accessing the database remotely... try this page: http://dev.mysql.com/doc/mysql/en/GRANT.html it's like: grant all priviledges on dbname.* for [EMAIL PROTECTED] identified by 'password'; good luck dan shatam bhattacharya wrote: Hi all, I have to implement a project for my college placement information system. I chose mysql with php on IIS. I am newbie with mysql. The design is allmost over but I am facing a critical problem. my ip is 172.31.65.21 (hostname is ginie) on the college LAN. When I try to access the database server (mysql-5.0.0-alpha-nt) which is installed on my system I get the following error C:\mysql\binmysql -h 172.31.65.21 -u root -p Enter password: ERROR 1130 (): #HY000Host 'ginie' is not allowed to connect to this MySQL server Allthough it connects well with localhost. Can anyone tell me what can be the problem. Is there a problem with the server coniguration. Please help as my mid term project is at stake. any help and suggestions would be higly appreciated, shatam Yahoo! India Matrimony: Find your partner online. http://yahoo.shaadi.com/india-matrimony/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Workaround for ORDER BY DESC
That is excellent. I was not expecting such a good response. So with the info you now provide I am right in saying that on each table MySQL will only use 1 index, the one that the table analyzer chooses to be the most suited. If I was to have a separate index, 1 on cat and 1 on date it couldnt use both together, I would have to have a combined one of (cat, date). Please tell me I am understanding this correctly. So for my query I would set up indexes as follows: SELECT p.* FROM p_cat c, p_ad p WHERE p.cat = c.id AND c.lft BETWEEN 4 AND 5 ORDER BY p.date DESC LIMIT 0,30; c = PRIMARY (c.id) INDEX (c.lft) p = PRIMARY (p.id) INDEX (p.cat, p.date) That performs well only if I remove the ORDER BY DESC part. Now I have optimized my table with your explanation of indexes as soon as I add ORDER BY DESC it goes straight back to using filesort, using temporary with no index chosen for the p table. Any ideas to optimize the ORDER BY DESC is warmly welcome... And thanks again for your detailed response. Cheers Steve sean c peters wrote: (note: all terminology is made up, and not necessarily standard, but the concepts should be fine) It may help to think about indexes as if you were accessing physical records, such as in a library. For instance, if you want to find all the Kurt Vonnegut, Jr books, and there is an index by author, just by looking at the index, you would get a list of all the books. Now imagine trying to find only the Kurt Vonnegut, Jr books published between 1970 and 1982 for instance. Just by looking at the author index, you will not be able to do this. The author index would again tell you all the Kurt Vonnegut, Jr books, and then you'd need to look at each of those books information to determine the publication year. So lets say that in addition to the Author index, we have an index that references books by their publication year. By looking at the published year index, you could get all the books published between 1970 and 1982, but you would not be able to tell who the author is, except by looking at the proper info for all of the books the index told you were published between 1970 and 1982. That probably isnt very helpful. If you look at the author index and get all the vonnegut books, and look at the published year index and get all books published between 1970 and 1982, you could take the common members of both those sets (set intersection) and that would be the books you want. But again, this a a time consuming process. But, you can create an index on multiple columns, for instance (author, published_year). This index looks something like this (i made up the dates, i dont know when each was published) ... Von Neuman, 1942- Qunatum Theory Vonnegut,1965 - Slaughterhouse 5 Vonnegut,1979 - Cats Cradle Vonnegut,1999 - God Bless You, Dr Kevorkian Vonnegut,2000 - Timequake Vonden, 1922- Something ... So with this index, you can look up by author, and then directly by year, instead of having to actually examine each record. Indexes like like are really the concatenation of the columns involved, in the order specified. Everything said above is for doing a query on only 1 table, but most of the concepts transfer to more complicated queries. When querying multiple tables, more stuff is going on. I'll try to explain in terms of your sample query. SELECT p.* FROM p_cat c, p_ad p WHERE p.cat = c.id AND c.lft BETWEEN 4 AND 5 ORDER BY p.date DESC LIMIT 0,30; Generally the query optimizer will choose one of the WHERE conditions, (or a few if there is an appropriate multi-column index) to create an initial match set. I would guess that the query optimizer chooses to use the index c.lft, if it exists. By using this index, we'd get all the rows in table 'c' where c.lft is BETWEEN 4 AND 5, as specified. (the initial results set) Eventually all the WHERE (and HAVING) conditions will be satisfied, but the rest will require examining the actual rows that the initial result set determined as possible matches. At some point, in a multi table query, the rows in the tables must be joined. To do this, the join conditions must be satisfied between the two tables. The rows in the initial result set (one table), will try to join with rows in a second table as specified. For your query, the only thing - to get all the proper rows (not necessarily in your order) is to join the rows from 'c' in the initial result set with rows from 'p'. They are joined via p.cat = c.id From the c rows, all the c.id's are available. If there is an index on p.cat, the proper rows cans be looked up through the index, if not, the table will need to be scanned for each c.id to join with p.cat If there were other where conditions, such as 'p.blah = 7', each joined row would need to be examined to determine if the value of p.blah is appropriate or not. So, to summarize, to get a reduced match set, an index may be used. For each table join,
Re: SQL Query Question
- Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Dirk Bremer (NISC) ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, April 16, 2004 15:06 Subject: RE: SQL Query Question WHERE queue_time = Now() + 0 Are you wanting just the date or the datetime? -Original Message- From: Dirk Bremer (NISC) To: [EMAIL PROTECTED] Sent: 4/16/04 2:54 PM Subject: SQL Query Question I have a simple table where one of the columns is named queue_time and is defined as a timestamp-type. I would like to query this table for all rows where the queue_time equals the current date. I an a newbie and have been wrestling with the docs for a solution. You help will be appreciated. Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc Victor, I just want to match the date, not the time, i.e. all of the rows for the current date regardless of the time they were entered. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Query Question
If your data is stored in the following format 2004-04-16 00:00:00 you can do WHERE queue_time = CURRENT_DATE() + 0 You will also be able to take advantage of an index. Else, if you data is kept in the datetime format, 2004-04-16 15:53:27 one option is to do WHERE DATE_FORMAT(queue_time, '%Y%m%d') = CURRENT_DATE() + 0 ...no index usage though -Original Message- From: Dirk Bremer (NISC) To: [EMAIL PROTECTED] Sent: 4/16/04 3:25 PM Subject: Re: SQL Query Question - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Dirk Bremer (NISC) ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, April 16, 2004 15:06 Subject: RE: SQL Query Question WHERE queue_time = Now() + 0 Are you wanting just the date or the datetime? -Original Message- From: Dirk Bremer (NISC) To: [EMAIL PROTECTED] Sent: 4/16/04 2:54 PM Subject: SQL Query Question I have a simple table where one of the columns is named queue_time and is defined as a timestamp-type. I would like to query this table for all rows where the queue_time equals the current date. I an a newbie and have been wrestling with the docs for a solution. You help will be appreciated. Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc Victor, I just want to match the date, not the time, i.e. all of the rows for the current date regardless of the time they were entered. Thanks! -- 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: SQL Query Question
- Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Dirk Bremer (NISC) ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, April 16, 2004 15:57 Subject: RE: SQL Query Question If your data is stored in the following format 2004-04-16 00:00:00 you can do WHERE queue_time = CURRENT_DATE() + 0 You will also be able to take advantage of an index. Else, if you data is kept in the datetime format, 2004-04-16 15:53:27 one option is to do WHERE DATE_FORMAT(queue_time, '%Y%m%d') = CURRENT_DATE() + 0 ...no index usage though Victor, The data defined as a timestamp, i.e. a number rather than a string, so it has MMDDHHMMSS values. So it looks like I'll need to do some type of substring on it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP + MySQL Not Playing Nice Any More
The only thing that changed in the environment was the creation of a script that temporarily introduces a variable ($MP3) to be created and then accessed by the script in question but the script doesn't touch mysql at all. What could have changed so drastically as to kill access to the db so completely? -- Joseph A. Nagy, Jr. http://joseph-a-nagy-jr.homelinux.org Political Activist Extraordinaire Peace, Life, Liberty The only fallacy is the inaction on our part to stave off the worst of horrors, the stripping of personal freedom. -- Joseph A. Nagy, Jr. January 2004 pgp0.pgp Description: PGP signature
Group by for datetime
I have a table: CREATE TABLE `moviehits` ( `title` varchar(5) NOT NULL default '', `movie` varchar(4) NOT NULL default '', `hit_date` datetime NOT NULL default '-00-00 00:00:00', `ip` varchar(15) NOT NULL default '', `listing_id` int(10) unsigned NOT NULL default '0' ) TYPE=MyISAM; That I would like to grab stats by title, something along the following: select count(movie),hit_date,movie from moviehits where hit_date between '2004-04-01' and '2004-04-31' and title='33329' group by hit_date Of course, when I do the group by for the hit_date, it does not group hits by day because of the time element. Is there a way to use the existing database structure to get hits by date using a group by clause of some sort? select count(movie),hit_date,movie from moviehits where hit_date between '2004-04-01' and '2004-04-31' and title='33329' group by date_format(hit_date,%Y-$m-%d) Or something along those lines? Tia! -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple. - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
On Fri, 2004-04-16 at 14:09, Dirk Bremer (NISC) wrote: - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Dirk Bremer (NISC) ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, April 16, 2004 15:57 Subject: RE: SQL Query Question If your data is stored in the following format 2004-04-16 00:00:00 you can do WHERE queue_time = CURRENT_DATE() + 0 You will also be able to take advantage of an index. Else, if you data is kept in the datetime format, 2004-04-16 15:53:27 one option is to do WHERE DATE_FORMAT(queue_time, '%Y%m%d') = CURRENT_DATE() + 0 ...no index usage though Victor, The data defined as a timestamp, i.e. a number rather than a string, so it has MMDDHHMMSS values. So it looks like I'll need to do some type of substring on it. You could keep any index you have and do it this way: SELECT a,b FROM x WHERE queue_time BETWEEN date_format(curdate(), %Y%m%e00) AND date_format(curdate(), %Y%m%e235959); -- |- Garth Webb -| |- [EMAIL PROTECTED] -| signature.asc Description: This is a digitally signed message part
Re: Proces table entries
Hi, They might be threads of the same [mysqld] proces. Can you tell me how I can verify that? Additional information is that the system is not in production yet, therefore there is no load yet. My main problem is that the system is very slow and that I found out (via ps command) that the 10 unix processes of mysqld take up a lot of memory. As a result, all memory is used and any queries directly result in swapping. The responsetime of a website is about 4 seconds. My hope is that eliminating unnessary [mysqld] unix-processess will free-up memory and result in less swapping. One of the things I tried to do is to set max_connections to 4 and restarting mysql. After doing so, still 10 unix mysqld processess popped up in the process table. Any help would be really appreciated ! Rob
Re: PHP + MySQL Not Playing Nice Any More
Okay, I got phpmyadmin back but http://logd-test.joseph-a-nagy-jr.homelinux.org still is having problems (click the link to see the problem) and I've gone through and commented out the lines it refers to but then all I get is a blank page. So I copy a fresh, untouched copy of dbwrapper.php to / for the vhost and it still wants to try and use [EMAIL PROTECTED] instead of what is defined in my dbconnect file. I'm out of idea's and am no longer sure this is a mysql problem but I don't know where else to go. ): -- Joseph A. Nagy, Jr. http://joseph-a-nagy-jr.homelinux.org Political Activist Extraordinaire Peace, Life, Liberty The only fallacy is the inaction on our part to stave off the worst of horrors, the stripping of personal freedom. -- Joseph A. Nagy, Jr. January 2004 pgp0.pgp Description: PGP signature
Re: Group by for datetime
At 17:22 -0400 4/16/04, Mike Morton wrote: I have a table: CREATE TABLE `moviehits` ( `title` varchar(5) NOT NULL default '', `movie` varchar(4) NOT NULL default '', `hit_date` datetime NOT NULL default '-00-00 00:00:00', `ip` varchar(15) NOT NULL default '', `listing_id` int(10) unsigned NOT NULL default '0' ) TYPE=MyISAM; That I would like to grab stats by title, something along the following: select count(movie),hit_date,movie from moviehits where hit_date between '2004-04-01' and '2004-04-31' and title='33329' group by hit_date Of course, when I do the group by for the hit_date, it does not group hits by day because of the time element. Is there a way to use the existing database structure to get hits by date using a group by clause of some sort? This query will group by the date part of the hit_date column, showing the number of records per day in the table: SELECT FROM_DAYS(TO_DAYS(hit_date)) AS day, COUNT(*) FROM moviehits GROUP BY day; The trick is that FROM_DAYS(TO_DAYS(x)), where x is a DATETIME or TIMESTAMP, strips off the time to produce just the date part. You can adapt it to your own situation. However, if you're grouping by the day, but trying to count each movie per day, you may want to group by day and movie. select count(movie),hit_date,movie from moviehits where hit_date between '2004-04-01' and '2004-04-31' and title='33329' group by date_format(hit_date,%Y-$m-%d) Or something along those lines? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Proces table entries
At 23:54 +0200 4/16/04, Rob Schuurman wrote: Hi, They might be threads of the same [mysqld] proces. Can you tell me how I can verify that? It depends on your operating system and the process-reporting tools. For example, on Linux, ps typically reports each thread as a separate entry. Mac OS X does not. If you're seeing a bunch of mysqld processes, all with the same memory use, you're likely seeing threads. This doesn't mean you're using n times that much memory (for n processes). For example, if I launch mysqld on my Gentoo Linux system, I see 10 mysqld processes. But only a single server is running. Do you have your server parameters for buffer sizes set to some incredibly high values? That might be the cause of your problems. Additional information is that the system is not in production yet, therefore there is no load yet. My main problem is that the system is very slow and that I found out (via ps command) that the 10 unix processes of mysqld take up a lot of memory. As a result, all memory is used and any queries directly result in swapping. The responsetime of a website is about 4 seconds. My hope is that eliminating unnessary [mysqld] unix-processess will free-up memory and result in less swapping. One of the things I tried to do is to set max_connections to 4 and restarting mysql. After doing so, still 10 unix mysqld processess popped up in the process table. Any help would be really appreciated ! Rob -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can not get an answer here - how to force the index block size to be the same?
At 20:25 -0700 4/15/04, Haitao Jiang wrote: Paul Because I want to use the load index into cache... to pre-load the indexes into cache. But I can not use this feature since the block size of indexes are different (1024 vs. 2048), and the load index into cache.. complains about it. Any idea? Thanks a lot! I'm afraid that's pretty vague. What does your table structure look like? What does your LOAD INDEX statement look like? What is the result of the statement? Haitao --- Paul DuBois [EMAIL PROTECTED] wrote: At 17:34 -0700 4/15/04, Haitao Jiang wrote: It seems to be either a hard question or stupid question:). Is there anyway in Version 4.1.1 I can force all the indexes to have same block size? Say 2048? If it is plain impossible without changing the source code, please let me know. Why do you care about this? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Locking tables
At 0:03 -0400 4/16/04, Mark Susol|Ultimate Creative Media wrote: Did I really ask that tough a question? Anyone? I'm not sure you're asking an answerable question. Consider this requirement that you give below: So what do I need to do before running any backup scripts to ensure the tables will not be corrupted during any kind of shell operation? Do I have I'm not sure I know of any precaution that can prevent you from corrupting your tables during *any kind of shell operation*. It's usually possible to cause a violation of table integrity from the shell if you're determined to do so. (I know that may not be quite the question you intended to ask, but it's what you actually *did* ask.) The general answer to your question, if you're willing to cooperate with the server, is to lock the tables from within the server so that no other clients can modify them, and use FLUSH TABLES to flush any changes to disk. While the lock remains in place, copy the table files. Then unlock the tables. Remember that the client that locks the table *must remain connected* while you copy the table files, because any locks are released automatically when the client connection ends. Have a look at the mysqlhotcopy source for any idea of how it uses this approach. Essentially, what it does is open a connection to the server, tells the server to lock the tables, and then while the tables are locked, goes behind the server's back to directly copy table files. (This is why mysqlhotcopy must be run on the server host. It's also (I believe) why it doesn't work on WIndows: Windows file locking semantics do not allow you to copy a file while the server has it locked.) By the way, it's difficult to see how automysqlbackup could corrupt any tables. A quick look through it seems to indicate that it only uses mysqldump to perform backups. On 4/15/04 7:38 PM, Mark Susol | Ultimate Creative Media [EMAIL PROTECTED] wrote: I've found a nice shell script to use to backup my server's MySQL databases. https://sourceforge.net/projects/automysqlbackup/ However, when I tried this earlier today it resulted in a corrupt table. Now the table in question is one I've had issues with for other reasons, but it has over 2 mil records in it when only partially built. What I need to do is put a lock on the database/tables before the script is run. The script allows for a pre post shell script to run. So what do I need to do before running any backup scripts to ensure the tables will not be corrupted during any kind of shell operation? Do I have to stop services like mysqld or httpd before hand? Does anyone use MySQL 4.0.18 and the hot copy instead? I'm using 4.0.17-max -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql 4.1.1 slow login
At 15:18 +0700 4/15/04, Hendro S. wrote: Hi all, I have win2k server machine, if this machine online, i could login to MySQL 4.1.1 on RH 9 is about 1 second, but if this machine offline connection establish in about 10 seconds, then i try connect to mysql 4.0.17 in SuSe machine its done in about 1 second. I connect to mysql server from my win2k machine with MySQL Front. So I uninstall MySQL 4.1.1 and reinstall 4.0.17, and connection done in 1 second again even my Win2K server machine offline. Can anyone explain this behavior of MySQL 4.1.1? It's difficult to know just what you want explained. You're varying a lot of independent variables (MySQL version, OS, and machine online/offline), and, as far as I can tell, you're not varying them systematically. What is the question that you are asking? If it's why are connections slow when the machine is offline, it's probably a problem of DNS being slow when the machine is offline. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Complicated Inner Joins
At 12:43 +0100 4/15/04, zzapper wrote: Here's another rotter! I searched UseNet and read that in MySQL you cannot have a sub-select, That's old information. Subqueries are supported as of MySQL 4.1. You'll probably need some quotes around the values in your BETWEEN expression, though. but I'm lost as to how to split this into two selects? SELECT distinct p.ProjectID ,p.ProjectName FROM tbl_project AS p INNER JOIN ( ( (tbl_UnitType AS ut INNER JOIN tbl_RateType AS r ON ut.RateType = r.RateType) INNER JOIN (tbl_SubProject AS sp INNER JOIN (Select * FROM tbl_ProjectResource WHERE UserID = '#rptUserID#' AND Deleted = 0 AND ((ActivityDate) Between #datDateFrom# And #datDateTo#) ) AS pr ON sp.SubProjectID = pr.SubProjectID) ON ut.Unit = pr.Unit ) INNER JOIN tbl_RateTypeCategoryCharge AS rt ON pr.RateTypeCategoryChargeID = rt.RateTypeCategoryChargeID ) ON p.ProjectID = sp.ProjectID WHERE ut.reportDisplay = 1 zzapper (vim, cygwin, wiki zsh) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query about MySQL and Access Permissions to MySQL Database FILES
I am talking a PHP+MySQL course at my local community college and since this is the first time the course is being offered there are some teething problems with the curriculum. I am posting this query at the request of the instructor. We are using a Linux Server and each Student has their own set of directories on the Server. The MySQL Databases are created on local PCs and then FTP'ed to the user's directory on the server. The files get User=RW and Group/World=R permissions due to the FTP being used having no way to set some other set of default Permissions and no way to update them once uploaded. The Databases are made known to MySQL by using a Softlink in the MySQL data folder that points to the actual copy in the user's directory. We would like to avoid the need to constantly go in and update/correct the permissions to G/W=RW after each upload of new copies of the Database Folder or 3 Files that comprise a database. Now that the background has been covered, here is my question. Since to gain access to a Database from the PHP Code, a mysql login request is required, it seems to me that this login can provide the MySQL Server the information needed to switch to the respective user's UID when accessing the database for update (as opposed to just read) purposes (thus getting RW Permission to the files). Does MySQL have the setuid authority so it can do so (and if so, does there code exist there to do so)? If not, is there some other way to allow Update Access to the respective databases based on the Login UserID other than go Group/World Writable on the 3 files that comprise the database? Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can not get an answer here - how to force the index block size to be the same?
Paul I wanted to pre load keys into cache which is larger than the total index size: --- Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 99 to server version: 4.1.1-alpha-Max Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql load index into cache MuzeTable; +-+--+--+--- + | Table | Op | Msg_type | Msg_text | +-+--+--+--- + | mysql.MuzeTable | preload_keys | error| Indexes use different block sizes | | mysql.MuzeTable | preload_keys | status | Operation failed | +-+--+--+--- + 2 rows in set (0.00 sec) -- I check the blocksize of the indexes, some of them are 1024, some are 2048. Any idea how to solve this? Thanks a lot! Haitao --- Paul DuBois [EMAIL PROTECTED] wrote: At 20:25 -0700 4/15/04, Haitao Jiang wrote: Paul Because I want to use the load index into cache... to pre-load the indexes into cache. But I can not use this feature since the block size of indexes are different (1024 vs. 2048), and the load index into cache.. complains about it. Any idea? Thanks a lot! I'm afraid that's pretty vague. What does your table structure look like? What does your LOAD INDEX statement look like? What is the result of the statement? Haitao --- Paul DuBois [EMAIL PROTECTED] wrote: At 17:34 -0700 4/15/04, Haitao Jiang wrote: It seems to be either a hard question or stupid question:). Is there anyway in Version 4.1.1 I can force all the indexes to have same block size? Say 2048? If it is plain impossible without changing the source code, please let me know. Why do you care about this? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP + MySQL Not Playing Nice Any More[Solved]
snip The PHP page that was being reported at being in error wasn't the one in error. Sorry for wasting everyone's bandwidth and time. -- Joseph A. Nagy, Jr. http://joseph-a-nagy-jr.homelinux.org Political Activist Extraordinaire Peace, Life, Liberty The only fallacy is the inaction on our part to stave off the worst of horrors, the stripping of personal freedom. -- Joseph A. Nagy, Jr. January 2004 pgp0.pgp Description: PGP signature
