Re: how to use except operation (corrections for the return results)?
Better: select * from table as t1 left join table as t2 on t1.study=t2.study and not t2.keyword ='K2' where t1.keyword = 'K1' Santino At 23:23 +0100 8-11-2004, Santino wrote: Something like: select * from table as t1, table as t2 where t1.study=t2.study and t1.keyword = 'K1' and not t2.keyword ='K2' Santino At 11:57 -0800 8-11-2004, L a n a wrote: Hello, I've had a problem: statement SELECT data_id from table WHERE keyword = a NOT keyword = b returns SQL error I've got the following not working solutions to my problem: SELECT data_id FROM table WHERE keyword = a AND keyword (or !=) b SELECT data_id FROM table WHERE keyword = a AND NOT (or OR NOT) keyword = b To explain better what result I need to get, there is table Id Study keyword 1 AK1 2 AK2 3 BK1 4 BK2 5 CK1 6 Ck3 I need to return study C, because it has only K1 keyword. However, all the suggested solutions return me A, B, C in the following order: if I ask for K1/K2 then it returns studies A, B; if I ask for K2/K1 (order matters) then it returns A, B, C. How to solve the problem? I tried SELECT T.data_id from table T WHERE T.keyword = a EXCEPT SELECT T2.data_id from table T2 WHERE T2.keyword =b . But it gives SQL error. Any thoughts? Thank you, Lana -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary Upgrade for Cpanel?
Hello.
See:
http://dev.mysql.com/doc/mysql/en/Can_not_connect_to_server.html
btw new worked --new gave me this error
DBI connect('mysql:localhost','root',...) failed: Can't connect to local
MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at
/scripts/restartsrv_mysql line 74 mysql has failed, please contact the
sysadmin (result was Warning, no valid mysql.sock file found.mysql has
failed).
Thanks
Donny Lairson
President
[EMAIL PROTECTED] wrote:
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem connecting to MySQL server
Hello. See: http://dev.mysql.com/doc/mysql/en/Can_not_connect_to_server.html Schalk Neethling [EMAIL PROTECTED] wrote: Hey there I have upgraded the MySQL server on my Windows machine to 4.0.22 following the docs at: http://dev.mysql.com/doc/mysql/en/Windows_upgrading.html When I start the server using NET START MySQL the server starts with no problems and the following is written to my error log: 041108 14:24:28 InnoDB: Started MySQL: ready for connections. Version: '4.0.22-debug' socket: '' port: 3306 Source distribution When I stop the server using NET STOP MySQL the server stops without any problems and the following is written to the log: 041108 14:25:43 MySQL: Normal shutdown 041108 14:25:43 InnoDB: Starting shutdown... 041108 14:25:46 InnoDB: Shutdown completed 041108 14:25:46 MySQL: Shutdown Complete Now, here is the problem. When I have started the MySQL server and it is running I then go to c:\mysql\bin\ and execute the command: mysql The following message is then returned: ERROR 2003: Can't connect to MySQL server on 'localhost' (10061) Any ideas why this is happening? Any help on pointers to where I may find more answers will be appreciated. Thanks! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fast method needed to determine if a table is corrupt
Hello. I assume that MyISAM tables are checked thorougly, i.e. each record is read and compared to the table definition. How big your table is and what kind of storage it is on? If the time to check is comparable to the time needed to actually read the whole data from the storage - then it's probably the best time. You can ask in [EMAIL PROTECTED] then. Tim Murtaugh [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installation of system tables failed
Hello. Not enough info to make a conclusion. Did you look at: http://dev.mysql.com/doc/mysql/en/mysql_install_db.html http://dev.mysql.com/doc/mysql/en/FreeBSD.htm ? Maybe that will help. Vincent Blondel [EMAIL PROTECTED] wrote: Hello, I am trying to install the system db with mysql 4.1.7 on my FreeBSD-4.10 but I encounter some problems. You can find on my perso page how I compile mysql (subfolder fbsd-secure). This is what I did... (root) # mysql_install_db --user=mysql --datadir=/home/mysqld/ Installing all prepared tables Installation of system tables failed! Examine the logs in /home/mysqld/ for more information. You can also try to start the mysqld daemon with: /usr/local/libexec/mysqld --skip-grant You can use the command line tool /usr/local/bin/mysql to connect to the mysql database and look at the grant tables: shell /usr/local/bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in /home/mysqld/ that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com Please consult the MySQL manual section: 'Problems running mysql_install_db', and the manual section that describes problems on your OS. Another information source is the MySQL email archive. Please check all of the above before mailing us! And if you do mail us, you MUST use the /usr/local/bin/mysqlbug script! The only things I can see is that two directories have been created on /home/mysqld (mysql,test). /home/mysqld/mysql has lot of files but I don't see any log files giving to me the problem. So can somebody help me to solve this problem. Regards Vincent. Blondel, Vincent Registered LFS user : 7485 homepage : http://jlang.dyndns.org/ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary Upgrade for Cpanel?
Hello. Usually config file means my.cnf (my.ini on Windows). As I don't have the [mysqld-4.0] in the my.cnf just [mysqld] See: http://dev.mysql.com/doc/mysql/en/Option_files.html And how do I know that it is actually running? Start by checking whether there is a process named mysqld running on your server host. (Use ps xa | grep mysqld on Linux or the Task Manager on Windows.) If there is no such process, you should start the server. Thanks I see the switch now. Does the config file mean the My.cnf file or is there another one. As I don't have the [mysqld-4.0] in the my.cnf just [mysqld] And how do I know that it is actually running? I also am not clear on the new versus --new command could someone explain that. Some of the 4.1 behaviors can be tested in 4.0 before performing a full upgrade to 4.1. We have added to later MySQL 4.0 releases (from 4.0.12 on) a --new startup option for mysqld. See section 5.2.1 mysqld Command-Line Options. This option gives you the 4.1 behavior for the most critical changes. You can also enable these behaviors for a given client connection with the SET @@new=1 command, or turn them off if they are on with SET @@new=0. If you believe that some of the 4.1 changes will affect you, we recommend that before upgrading to 4.1, you download the latest MySQL 4.0 version and run it with the --new option by adding the following to your config file: [mysqld-4.0] new Thanks Donny Lairson President http://www.gunmuse.com 469 228 [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: connection problem with 4.1.7
P.V.Anthony wrote: Hi, I am having an intermitten connection problem with MySQL 4.1.7 . Here is the setup. Intel P4 with HT Fedora Core 1 kernel 2.4.27 smp MySQL version 4.1.7 (RPM install from mysql.org) Qmail with vpopmail using mysql (www.qmailtoaster.com) Sometimes I cannot login to qmail to check mail. Using thunderbird or even webmail. Only way to solve the problem is to restart MySQL. Then everything is ok. After maybe 2 hours the same problem again. I am concluding that this is a MySQL problem because other apps that use MySQL also shows problems. qmail vpopmail is using socket and my cms (WebGUI) is using tcp to connect. I have tried to check the logs at /var/lib/mysql. But do not see anything any error. I do see a warning like this, 041106 9:50:59 [Warning] Asked for 196608 thread stack, but got 126976 The strange thing is that I can still connect to MySQL using webmin. Is there any other thing I can do to find out what is causing the problem? Maybe I have made some silly mistake. Please advice and help. P.V.Anthony Problem solved. As usually it was my mistake. In the content management system that I used, I set the connection to the database to be presistent or pooling. Something like that. Once I set it back to normal, that is not to have persistent connections, all was solved. It seems that there was too many connections and it just did not allow any more connections. Sorry to have troubled everyone. It was my mistake. It was warned in the documentation in the CMS. I found the problem by using the following command. # mysqladmin -u root -ppassword processlist Thanks to everyone who helped. P.V.Anthony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Emulating timediff in 4.0 ?
TIMEDIFF(expr,expr2)
TIMEDIFF() returns the time between the start time expr and the
end time expr2. expr and expr2 are time or date-and-time expressions,
but both must be of the same type.
mysql SELECT TIMEDIFF('1997-12-31 23:59:59.01',
- '1997-12-30 01:01:01.02');
- '46:58:57.99'
Any fast tip in how to emulate this on mysql 4.0 ?
May save me hours of bitching :/
Thanks in advance.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to bring up local replication / socket-error
Hi, I'm trying to set up a replication client on the same server as the master is running. Master is port 3306 and its socket /mymaster/mysql.sock. I set up the repl-client to connect to localhost (tried the ip-address too) on port 3306. When I start the replication I get the following error: Error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)' errno: 2002 The question is how to tell the replication client that the right socket is located in /mysqlmaster. I tried to set a symbolic link from /tmp/mysql.sock to mysqlmaster.sock but only got a fatal error 1236: Could not open logfile.. Help would be appreciated Greetz from Germany Lutz Maibach P.S: I only try to get up this strange looking constellation cause I have to replicate a single database from the master to a customers server. The customer may not have a look at the other databases running on the master, but with normal replication he would get all the data into his relaylog. So I want to replicate the customers database to the local replication and the the customers server to replicate this local replication where only his data is stored in the binlog. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Read locks with InnoDB?
Hello, Basically, I have multiple MySQL clients that have to delete entries from a InnoDB table in a concurrent environment. What they do is: 1 - getting the first entry of the table that needs to by deleted, by executing a SELECT ... LIMIT 1 query, 2 - doing stuff with the query result, 3 - deleting the entry from the table if everything went right. However, let's say user A has executed the SELECT query and starts doing stuff. Before A has deleted the entry from the table, user B executes the SELECT query: he gets the same result as A, and starts doing the *same* stuff as A, which is something I need to avoid. How could I achieve this? Is it possible to have blocking SELECT queries, or to set read locks on one row (without locking all the table, to let others users purge the following entries)? Thanks for feedback. -- Julien ALLANOS Silicomp-AQL The contents of this email and any attachments are confidential. They are intended for the named recipient(s) only. If you have received this email in error please notify the system manager or the sender immediately and do not disclose the contents to anyone or make copies. * email scanned for viruses, vandals and malicious content * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Read locks with InnoDB?
Hello. Transactions are usually used for such purposes, just as 'select ... for update'. You may have interest in: http://dev.mysql.com/doc/mysql/en/InnoDB_locking_reads.html http://dev.mysql.com/doc/mysql/en/Transactional_Commands.html Julien ALLANOS [EMAIL PROTECTED] wrote: Hello, Basically, I have multiple MySQL clients that have to delete entries from= a InnoDB table in a concurrent environment. What they do is: 1 - getting the first entry of the table that needs to by deleted, by exe= cuting a SELECT ... LIMIT 1 query, 2 - doing stuff with the query result, 3 - deleting the entry from the table if everything went right. However, let's say user A has executed the SELECT query and starts doing stuff. Before A has deleted the entry from the table, user B executes th= e SELECT query: he gets the same result as A, and starts doing the *same* s= tuff as A, which is something I need to avoid. How could I achieve this? Is it possible to have blocking SELECT queries,= or to set read locks on one row (without locking all the table, to let others u= sers purge the following entries)? Thanks for feedback. --=20 Julien ALLANOS Silicomp-AQL The contents of this email and any attachments are confidential. They are intended for the named recipient(s) only. If you have received this email in error please notify the system manager or the sender immediately and do not disclose the contents to anyone or make copies. * email scanned for viruses, vandals and malicious content * -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to bring up local replication / socket-error
Hello. See: http://bugs.mysql.com/bug.php?id=3310 Lutz Maibach [EMAIL PROTECTED] wrote: Hi, I'm trying to set up a replication client on the same server as the master is running. Master is port 3306 and its socket /mymaster/mysql.sock. I set up the repl-client to connect to localhost (tried the ip-address too) on port 3306. When I start the replication I get the following error: Error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)' errno: 2002 The question is how to tell the replication client that the right socket is located in /mysqlmaster. I tried to set a symbolic link from /tmp/mysql.sock to mysqlmaster.sock but only got a fatal error 1236: Could not open logfile.. Help would be appreciated Greetz from Germany Lutz Maibach P.S: I only try to get up this strange looking constellation cause I have to replicate a single database from the master to a customers server. The customer may not have a look at the other databases running on the master, but with normal replication he would get all the data into his relaylog. So I want to replicate the customers database to the local replication and the the customers server to replicate this local replication where only his data is stored in the binlog. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Emulating timediff in 4.0 ?
A combination of UNIX_TIMESTAMP and SEC_TO_TIME should do
it:
SELECT
UNIX_TIMESTAMP('1997-12-31 23:59:59.01') as `date 1 in seconds`,
UNIX_TIMESTAMP('1997-12-30 01:01:01.02') as `date 2 in seconds`,
UNIX_TIMESTAMP('1997-12-31 23:59:59.01') -
UNIX_TIMESTAMP('1997-12-30 01:01:01.02') as `diff in seconds`,
SEC_TO_TIME(
UNIX_TIMESTAMP('1997-12-31 23:59:59.01') -
UNIX_TIMESTAMP('1997-12-30 01:01:01.02')) as `diff in hh:mm:ss`
date 1 in seconds: 883634399
date 2 in seconds: 883465261
diff in seconds: 169138
diff in hh:mm:ss: 46:58:58
This throws away the msecs, though. Also, negative diffs
work, surprisingly:
SELECT
UNIX_TIMESTAMP('1997-12-30 01:01:01.02') as `date 1 in seconds`,
UNIX_TIMESTAMP('1997-12-31 23:59:59.01') as `date 2 in seconds`,
UNIX_TIMESTAMP('1997-12-30 01:01:01.02') -
UNIX_TIMESTAMP('1997-12-31 23:59:59.01') as `diff in seconds`,
SEC_TO_TIME(
UNIX_TIMESTAMP('1997-12-30 01:01:01.02') -
UNIX_TIMESTAMP('1997-12-31 23:59:59.01')) as `diff in hh:mm:ss`
date 1 in seconds: 883465261
date 2 in seconds: 883634399
diff in seconds: -169138
diff in hh:mm:ss: -46:58:58
So you've got that going for you.
Eamon Daly
- Original Message -
From: listsql listsql [EMAIL PROTECTED]
To: mysql [EMAIL PROTECTED]
Sent: Tuesday, November 09, 2004 7:11 AM
Subject: Emulating timediff in 4.0 ?
TIMEDIFF(expr,expr2)
TIMEDIFF() returns the time between the start time expr and the
end time expr2. expr and expr2 are time or date-and-time expressions,
but both must be of the same type.
mysql SELECT TIMEDIFF('1997-12-31 23:59:59.01',
- '1997-12-30 01:01:01.02');
- '46:58:57.99'
Any fast tip in how to emulate this on mysql 4.0 ?
May save me hours of bitching :/
Thanks in advance.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Emulating timediff in 4.0 ?
Thanks Eamon,
I was trying with something like:
SELECT stamp,now()+0, if ((now() - stamp)6000, (now() - stamp)
/60,0) as waitminutes from smstablademotaxi
//I'm interested to get it only for the last 60 minutes
result:
+++-+
| stamp | now()+0| waitminutes |
+++-+
| 20041109180348 | 20041109180455 |1.78 |
| 20041109180350 | 20041109180455 |1.75 |
+++-+
Thanks for the feedback :)
On Tue, 9 Nov 2004 10:20:31 -0600, Eamon Daly [EMAIL PROTECTED] wrote:
A combination of UNIX_TIMESTAMP and SEC_TO_TIME should do
it:
SELECT
UNIX_TIMESTAMP('1997-12-31 23:59:59.01') as `date 1 in seconds`,
UNIX_TIMESTAMP('1997-12-30 01:01:01.02') as `date 2 in seconds`,
UNIX_TIMESTAMP('1997-12-31 23:59:59.01') -
UNIX_TIMESTAMP('1997-12-30 01:01:01.02') as `diff in seconds`,
SEC_TO_TIME(
UNIX_TIMESTAMP('1997-12-31 23:59:59.01') -
UNIX_TIMESTAMP('1997-12-30 01:01:01.02')) as `diff in hh:mm:ss`
date 1 in seconds: 883634399
date 2 in seconds: 883465261
diff in seconds: 169138
diff in hh:mm:ss: 46:58:58
This throws away the msecs, though. Also, negative diffs
work, surprisingly:
SELECT
UNIX_TIMESTAMP('1997-12-30 01:01:01.02') as `date 1 in seconds`,
UNIX_TIMESTAMP('1997-12-31 23:59:59.01') as `date 2 in seconds`,
UNIX_TIMESTAMP('1997-12-30 01:01:01.02') -
UNIX_TIMESTAMP('1997-12-31 23:59:59.01') as `diff in seconds`,
SEC_TO_TIME(
UNIX_TIMESTAMP('1997-12-30 01:01:01.02') -
UNIX_TIMESTAMP('1997-12-31 23:59:59.01')) as `diff in hh:mm:ss`
date 1 in seconds: 883465261
date 2 in seconds: 883634399
diff in seconds: -169138
diff in hh:mm:ss: -46:58:58
So you've got that going for you.
Eamon Daly
- Original Message -
From: listsql listsql [EMAIL PROTECTED]
To: mysql [EMAIL PROTECTED]
Sent: Tuesday, November 09, 2004 7:11 AM
Subject: Emulating timediff in 4.0 ?
TIMEDIFF(expr,expr2)
TIMEDIFF() returns the time between the start time expr and the
end time expr2. expr and expr2 are time or date-and-time expressions,
but both must be of the same type.
mysql SELECT TIMEDIFF('1997-12-31 23:59:59.01',
- '1997-12-30 01:01:01.02');
- '46:58:57.99'
Any fast tip in how to emulate this on mysql 4.0 ?
May save me hours of bitching :/
Thanks in advance.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
using mysqldump to export BLOB tables
Hi, I have been looking around (manual, google, etc.) to find out if I can use mysqldump with BLOB tables, but to no avail. Is it possible to do so, and if yes, is there any flag I need to give to mysqldump? Or can you point me to the manual page that describes that? The page http://dev.mysql.com/doc/mysql/en/mysqldump.html did not enlight me. Thanks! - Christian Caron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select for text within a field.
Shanta McBain mailto:[EMAIL PROTECTED] on Wednesday, October 20, 2004 9:29 AM said: Sorry to take so long to get back. I have been putting by bees to bed for the winter. Now I can program again. [% FOREACH link = DBI.query(SELECT * FROM url_tb WHERE products LIKE 'honey' AND (site_name = 'Apis' OR site_name = 'All') ORDER BY link_order )%] are you saying the products field has data like this in it? tacosburritoshoneycrackerscake ... and you want to search for 'honey'? do LIKE '%honey%'. No the data is stored honey pollen pollination but of course this should really be normalized and split into another table. True it should. In my windoz days I used Delphi and Access an this was a snap. But it was all done from the GUI. Now I have a minor problem in that I use a web application call Extropia and I have not got it down to modify the SQL in Perl as of yet. Extropia dose all SQL in a DataHandler's so that it is easy to port the basic application from system to system. IE. Change a few lines of code and it calls flat files, Change them again and they call MySQL, or postgres or Oracle. To learn the process of normalizing (linking tables with referential integrity etc) in MySQL I have chosen to do the work first in TTML then port to Perl ActionHandlers. The long way around it might be said but I have active sites that need immediate improvements. -- Thanks Shanta McBain Http://computersystemconsulting.ca Web hosting and Application Hosting. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql
MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problems
On Tuesday 09 November 2004 11:49 am, ian douglas wrote:
My bad, I wasn't running START SLAVE to get them going. Seems odd that
this has to be done manually.
I never do.. once its started, it should be fine.. Your just getting errors
making it stop.
Also, overnight, my slaves reported this error:
041108 19:06:42 [Note] Slave I/O thread: connected to master
'[EMAIL PROTECTED]:3306', replication started in log
'masterdb-bin.03' at position 7803960
041108 19:06:42 [ERROR] While trying to obtain the list of slaves from
the master 'masterdb.org:3306', user 'repl_user' got the following
error: 'Access denied; you need the REPLICATION SLAVE privilege for this
operation'
041108 19:06:42 [ERROR] Slave I/O thread exiting, read up to log
'masterdb-bin.03', position 7803960
This is what i have:
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.smelser.org' IDENTIFIED BY
PASSWORD 'taken out';
If a slave dies, is there any way to determine that state from an
external point of view (ie: via Perl/DBD) so we can write a script of
some kind to either connect to the slave and issue a START SLAVE or
send the last xx lines of the .err log file to our tech staff?
I do this for the err files. It only send them if there is any data.. I use
logrotate..
/var/log/mysql/mysql.err {
missingok
mail [EMAIL PROTECTED]
mailfirst
daily
notifempty
compress
rotate 2
}
/var/log/mysql/mysqld.err {
missingok
mail [EMAIL PROTECTED]
mailfirst
daily
notifempty
compress
rotate 2
}
Jeff
pgp7Axkk8RKjY.pgp
Description: PGP signature
[ANN] Webyog releases FREE edition of SQLyog
Hello, Webyog, the creator of SQLyog - the most popular GUI for MySQL has released SQLyog v4.0. Starting from v4.0, SQLyog is available in two editions: SQLyog and SQLyog Enterprise. SQLyog is FREE for personal and commercial use. SQLyog contains all features of SQLyog Enterprise - except the following Power Tools: * HTTP / SSH Tunneling - Manage MySQL even if your ISP disallows remote connections * Data Synchronization - Zero install MySQL Replication * Schema Synchronization - Keep test and production databases in sync * Notification Services - Send formatted resulsets over email at regular intervals * ODBC Import - Wizard driven painless migration to MySQL Please visit the following link for to view the feature comparison between SQLyog and SQLyog Enterprise. http://www.webyog.com/sqlyog/featurematrix.html Thanks for your attention! Webyog Support http://www.webyog.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[ANN] Webyog releases FREE edition of SQLyog
Hello, Webyog, the creator of SQLyog - the most popular GUI for MySQL has released SQLyog v4.0. Starting from v4.0, SQLyog is available in two editions: SQLyog and SQLyog Enterprise. SQLyog is FREE for personal and commercial use. SQLyog contains all features of SQLyog Enterprise - except the following Power Tools: * HTTP / SSH Tunneling - Manage MySQL even if your ISP disallows remote connections * Data Synchronization - Zero install MySQL Replication * Schema Synchronization - Keep test and production databases in sync * Notification Services - Send formatted resulsets over email at regular intervals * ODBC Import - Wizard driven painless migration to MySQL Please visit the following link for to view the feature comparison between SQLyog and SQLyog Enterprise. http://www.webyog.com/sqlyog/featurematrix.html Thanks for your attention! Webyog Support http://www.webyog.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trans.: Re: Read locks with InnoDB?
Date : Tue, 9 Nov 2004 18:35:40 +0100 De : Julien ALLANOS [EMAIL PROTECTED] Adresse de retour :Julien ALLANOS [EMAIL PROTECTED] Sujet : Re: Read locks with InnoDB? : Gleb Paharenko [EMAIL PROTECTED] Julien ALLANOS [EMAIL PROTECTED] wrote: Hello, Basically, I have multiple MySQL clients that have to delete entries from= a InnoDB table in a concurrent environment. What they do is: 1 - getting the first entry of the table that needs to by deleted, by exe= cuting a SELECT ... LIMIT 1 query, 2 - doing stuff with the query result, 3 - deleting the entry from the table if everything went right. However, let's say user A has executed the SELECT query and starts doing stuff. Before A has deleted the entry from the table, user B executes th= e SELECT query: he gets the same result as A, and starts doing the *same* s= tuff as A, which is something I need to avoid. How could I achieve this? Is it possible to have blocking SELECT queries,= or to set read locks on one row (without locking all the table, to let others u= sers purge the following entries)? Thanks for feedback. -- Julien ALLANOS Silicomp-AQL Selon Gleb Paharenko [EMAIL PROTECTED]: Hello. Transactions are usually used for such purposes, just as 'select ... for update'. You may have interest in: http://dev.mysql.com/doc/mysql/en/InnoDB_locking_reads.html http://dev.mysql.com/doc/mysql/en/Transactional_Commands.html Thanks, I've already read these pages. Here is a test example I've done: 1/ User A: SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; 2/ User B: SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; 3/ User A: START TRANSACTION; 4/ User B: START TRANSACTION; 5/ User A: SELECT * FROM MyTable LIMIT = 1; I got a one-row result. 6/ User B: SELECT * FROM MyTable LIMIT = 1; I got the same one-row result, and I need to get the next one instead, because this one is already being treated by user A! I really want to have a SELECT FOR DELETE thing: as soon as a row is read, it is deleted. Is this possible? -- Julien ALLANOS Silicomp-AQL The contents of this email and any attachments are confidential. They are intended for the named recipient(s) only. If you have received this email in error please notify the system manager or the sender immediately and do not disclose the contents to anyone or make copies. * email scanned for viruses, vandals and malicious content * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
data deleted in MySQL but table file (.MYD) remains same size
Hi there, I have a table with three columns, two varchar(12) identifiers and a longtext column with very long (some 50 MB) strings. The size of the chromosomes.MYD table was about 2.8 GB. The table was used transiently and I no longer needed to store the strings, so in the interest of freeing up space I did the following: mysql UPDATE chromosomes SET sequence = ; When I query the database I can see that the sequence field is a null string for each record. However, the size of the chromosomes.MYD file in the data directory has not changed (i.e. it is still about 2.8 GB). SHOW TABLE STATUS (in mysql) and df (at the shell prompt) both report the same value. How can I get the table size to correspond to the small amount of data that actually remains in the table? Thanks for your assistance. Best regards, Craig Cummings, Ph.D. Research Associate Relman Laboratory Stanford University School of Medicine Department of Microbiology and Immunology and VA Palo Alto Health Care System e-mail: [EMAIL PROTECTED] phone: 650-493-5000 x63249 fax:650-852-3291 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: data deleted in MySQL but table file (.MYD) remains same size
Craig Cummings wrote: Hi there, I have a table with three columns, two varchar(12) identifiers and a longtext column with very long (some 50 MB) strings. The size of the chromosomes.MYD table was about 2.8 GB. The table was used transiently and I no longer needed to store the strings, so in the interest of freeing up space I did the following: mysql UPDATE chromosomes SET sequence = ; When I query the database I can see that the sequence field is a null string for each record. However, the size of the chromosomes.MYD file in the data directory has not changed (i.e. it is still about 2.8 GB). SHOW TABLE STATUS (in mysql) and df (at the shell prompt) both report the same value. How can I get the table size to correspond to the small amount of data that actually remains in the table? Thanks for your assistance. Dump it, delete it, and reload it. Files never get smaller, only bigger. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: data deleted in MySQL but table file (.MYD) remains same size
At 13:06 -0600 11/9/04, gerald_clark wrote: Craig Cummings wrote: Hi there, I have a table with three columns, two varchar(12) identifiers and a longtext column with very long (some 50 MB) strings. The size of the chromosomes.MYD table was about 2.8 GB. The table was used transiently and I no longer needed to store the strings, so in the interest of freeing up space I did the following: mysql UPDATE chromosomes SET sequence = ; When I query the database I can see that the sequence field is a null string for each record. However, the size of the chromosomes.MYD file in the data directory has not changed (i.e. it is still about 2.8 GB). SHOW TABLE STATUS (in mysql) and df (at the shell prompt) both report the same value. How can I get the table size to correspond to the small amount of data that actually remains in the table? Thanks for your assistance. Dump it, delete it, and reload it. Files never get smaller, only bigger. For MyISAM tables, you could also use OPTIMIZE TABLE. -- 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]
Posting Question
I am asking before I post so I don't anger everyone... Is this list okay to post a specific question regarding multiple row inserts.. I am doing this in perl, and I need some help with the perl part... Thanks, Taylor
Alter table only on the master?
Hello, I want to convert a table from innodb to myisam on the _master only_, not on the slaves. Is there a secret 'do this alter table only on the master' command, or do I really have to do some voodoo-replication-queries-skipping on the slaves? Thanks, Harmen -- The Moon is Waning Crescent (10% of Full) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: data deleted in MySQL but table file (.MYD) remains same size
That did the trick. Thanks for the advice. -Craig On Tue, 9 Nov 2004, Paul DuBois wrote: At 13:06 -0600 11/9/04, gerald_clark wrote: Craig Cummings wrote: Hi there, I have a table with three columns, two varchar(12) identifiers and a longtext column with very long (some 50 MB) strings. The size of the chromosomes.MYD table was about 2.8 GB. The table was used transiently and I no longer needed to store the strings, so in the interest of freeing up space I did the following: mysql UPDATE chromosomes SET sequence = ; When I query the database I can see that the sequence field is a null string for each record. However, the size of the chromosomes.MYD file in the data directory has not changed (i.e. it is still about 2.8 GB). SHOW TABLE STATUS (in mysql) and df (at the shell prompt) both report the same value. How can I get the table size to correspond to the small amount of data that actually remains in the table? Thanks for your assistance. Dump it, delete it, and reload it. Files never get smaller, only bigger. For MyISAM tables, you could also use OPTIMIZE TABLE. -- 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]
InnoDB data files keep growing with innodb_file_per_table
Hello, I have a DB of about 100 tables, and MySQL is configured to keep them in separate files. Total size of the files is ~2GB. Most of the tables are of similar format and consist of many short fixed-length rows (~50 bytes/row). The database size is increased for several MB a day. Initial configuration had 200MB for InnoDB data files. Initial database size was ~1GB. Current database size is, as I said, ~2GB. Current size of idbdata files is 1.8GB. As you can see, idbdata files are growing faster than the database itself. There are no lengthy transactions. I tried playing with the new 'innodb_max_purge_lag' setting, but it only made things worse (the data files kept growing and I started to get many slow queries). So, why do the data files keep growing??? Using MySQL 4.1.7 on Windows XP (3.2GHz CPU, 1GB RAM). Relevant settings from my.cnf below: max_connections=50 query_cache_size=32M table_cache=768 tmp_table_size=52M log-bin=binlog max_binlog_size=256M max_allowed_packet=32M innodb_data_file_path=ibdata1:100M;ibdata2:100M:autoextend innodb_additional_mem_pool_size=8M innodb_flush_log_at_trx_commit=0 innodb_log_buffer_size=2M innodb_buffer_pool_size=128M innodb_log_file_size=64M innodb_thread_concurrency=8 innodb_file_per_table innodb_open_files=2048 Good luck, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Posting Question
Post it, I'll help. On Tue, 2004-11-09 at 19:21, Lewick, Taylor wrote: I am asking before I post so I don't anger everyone... Is this list okay to post a specific question regarding multiple row inserts.. I am doing this in perl, and I need some help with the perl part... Thanks, Taylor -- Mark D. Maunder [EMAIL PROTECTED] http://www.workzoo.com/ The Best jobs from the Best Job Sites. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select for text within a field.
On Tuesday 09 November 2004 09:21 am, Shanta McBain wrote: [% FOREACH link = DBI.query(SELECT * FROM url_tb WHERE products LIKE 'honey' AND (site_name = 'Apis' OR site_name = 'All') ORDER BY link_order )%] I used PHPMyAdmin to create the fallowing code which works in PHPMyAdmin. SELECT * FROM `address_book_tb` WHERE `category` = 'beekeeper' AND `products` LIKE '%Pollinator%' ORDER BY `company_name` ASC This returns the only record in the table that contains the right info. In TTML [% FOREACH link = DBI.query(SELECT * FROM address_book_tb WHERE category = 'beekeeper' AND products LIKE '%Pollinator%' ORDER BY company_name ASC )%] works [% FOREACH link = DBI.query(SELECT * FROM 'address_book_tb' WHERE 'category' = 'beekeeper' AND 'products' LIKE '%Pollinator%' ORDER BY 'company_name' ASC )%] This dose not work. Troughs a TTML error Seems TTML dose not like the single quotes around the table and field names. Now to learn to link tables on entry and to join them on select. From Perl and TTML. This will not be as easy. A note on the storage of data in the field it actually is stored like. Nuces,Wax,Pollen,Honey,Pollinator,Queens Seems the web app places the contents of the field into a , delimeted string. So it needs the %. The '_' one character or just 'word' will not work. -- Thanks Shanta McBain Http://computersystemconsulting.ca Web hosting and Application Hosting. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: data deleted in MySQL but table file (.MYD) remains same size
Why not TRUNCATE table... - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: gerald_clark [EMAIL PROTECTED]; Craig Cummings [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, November 09, 2004 11:21 AM Subject: Re: data deleted in MySQL but table file (.MYD) remains same size At 13:06 -0600 11/9/04, gerald_clark wrote: Craig Cummings wrote: Hi there, I have a table with three columns, two varchar(12) identifiers and a longtext column with very long (some 50 MB) strings. The size of the chromosomes.MYD table was about 2.8 GB. The table was used transiently and I no longer needed to store the strings, so in the interest of freeing up space I did the following: mysql UPDATE chromosomes SET sequence = ; When I query the database I can see that the sequence field is a null string for each record. However, the size of the chromosomes.MYD file in the data directory has not changed (i.e. it is still about 2.8 GB). SHOW TABLE STATUS (in mysql) and df (at the shell prompt) both report the same value. How can I get the table size to correspond to the small amount of data that actually remains in the table? Thanks for your assistance. Dump it, delete it, and reload it. Files never get smaller, only bigger. For MyISAM tables, you could also use OPTIMIZE TABLE. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: data deleted in MySQL but table file (.MYD) remains same size
At 11:53 -0800 11/9/04, Fredrick Bartlett wrote: Why not TRUNCATE table... If you want an empty table, yes. The goal below appears to be to retain the records after changing one of the columns after an empty string. - Original Message - From: Paul DuBois [EMAIL PROTECTED] At 13:06 -0600 11/9/04, gerald_clark wrote: Craig Cummings wrote: Hi there, I have a table with three columns, two varchar(12) identifiers and a longtext column with very long (some 50 MB) strings. The size of the chromosomes.MYD table was about 2.8 GB. The table was used transiently and I no longer needed to store the strings, so in the interest of freeing up space I did the following: mysql UPDATE chromosomes SET sequence = ; When I query the database I can see that the sequence field is a null string for each record. However, the size of the chromosomes.MYD file in the data directory has not changed (i.e. it is still about 2.8 GB). SHOW TABLE STATUS (in mysql) and df (at the shell prompt) both report the same value. How can I get the table size to correspond to the small amount of data that actually remains in the table? Thanks for your assistance. Dump it, delete it, and reload it. Files never get smaller, only bigger. For MyISAM tables, you could also use OPTIMIZE TABLE. -- 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]
error after installing mysql 4.1
After installing mysql 4.1 over an existing mysql 4.0.x, the service refuses to start with this complaint: Fatal error: Can't open privilege tables: File '\\pumpkin\f\mysql\share\charsets\?.conf' not found (Errcode: 22) This is windows, with mysql installed in a nondefault directory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subject Headings in Tables
Normalize, David. Normalize. Your Regions get their own table. If a country/state can belong to more than one region you will need another table to hold that association. I can't remember if this was ever recommended to you but I had a chance to re-read it recently and think this could help you to better understand the basic concepts of normalization. http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html Don't get caught up in the terminology he uses but pay close attention to how he organizes his sample data. Shawn Green Database Administrator Unimin Corporation - Spruce Pine David Blomstrom [EMAIL PROTECTED] wrote on 11/08/2004 09:30:41 PM: I have a series of database tables focusing on the world's nations. There are basic tables that give each nation's abbreviation and parent (e.g. Eurasia) and other tables that focus on information about the people, government, etc. Now I want to introduce some regional headers. For example, current displays might look something like this: Denmark Finland France Germany Portugal Spain Alaska Arizona Florida Montana Wyoming I want to somehow modify my MySQL tables and/or the PHP scripts I use to display data so that I have the option of displaying data just like above, OR like this: NORTHERN EUROPE Denmark Finland WESTERN EUROPE France Germany SOUTHERN EUROPE Portugal Spain FAR NORTH Alaska SOUTHWEST Arizona SOUTH Florida ROCKY MOUNTAINS Montana Wyoming You can see an example of my ultimate goal at http://www.geoworld.org/about/guide/world/eur/remote.php? order=2direction=0submit=Submit I'm designing this page to function as a pop-up window that stays open as people click from nation to nation. It uses a PHP script to order the nations alphabetically or by population or other topics. Eventually, I'd like to learn how to modify the switch so that it can display nations or states with headings - like ROCKY MOUNTAINS - then if you click another setting, the nations are rearranged and the headings aren't even visible. Anyway, I just wanted to ask for tips about incorporating these regional headings into my database. My first hunch is to simply add them to one of basics tables, like this: NAMEIDTYPE United States usa Nation Rocky Mountains rm Region Colorado co State Montana mt State That way, I can simply display the entire table as is - complete with the headins - or I can use PHP to block every row where TYPE = Region. Does this sound like sensible plan, or are there better options? Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB data files keep growing with innodb_file_per_table
John, please post what SHOW INNODB STATUS says. The probable reason is that there are long transactions, or that purge is falling behind. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ - Original Message - From: John B. Ivski [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, November 09, 2004 9:46 PM Subject: InnoDB data files keep growing with innodb_file_per_table Hello, I have a DB of about 100 tables, and MySQL is configured to keep them in separate files. Total size of the files is ~2GB. Most of the tables are of similar format and consist of many short fixed-length rows (~50 bytes/row). The database size is increased for several MB a day. Initial configuration had 200MB for InnoDB data files. Initial database size was ~1GB. Current database size is, as I said, ~2GB. Current size of idbdata files is 1.8GB. As you can see, idbdata files are growing faster than the database itself. There are no lengthy transactions. I tried playing with the new 'innodb_max_purge_lag' setting, but it only made things worse (the data files kept growing and I started to get many slow queries). So, why do the data files keep growing??? Using MySQL 4.1.7 on Windows XP (3.2GHz CPU, 1GB RAM). Relevant settings from my.cnf below: max_connections=50 query_cache_size=32M table_cache=768 tmp_table_size=52M log-bin=binlog max_binlog_size=256M max_allowed_packet=32M innodb_data_file_path=ibdata1:100M;ibdata2:100M:autoextend innodb_additional_mem_pool_size=8M innodb_flush_log_at_trx_commit=0 innodb_log_buffer_size=2M innodb_buffer_pool_size=128M innodb_log_file_size=64M innodb_thread_concurrency=8 innodb_file_per_table innodb_open_files=2048 Good luck, John -- 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: replication problems
If a slave dies, is there any way to determine that state from an
external point of view (ie: via Perl/DBD) so we can write a script of
some kind to either connect to the slave and issue a START SLAVE or
send the last xx lines of the .err log file to our tech staff?
I tried writing a Perl script to run a query of SHOW SLAVE STATUS and
parsing the 30-or-so fields that would otherwise return, but it's
complaining:
DBD::mysql::st execute failed: Access denied; you need the
SUPER,REPLICATION CLIENT privilege for this operation at ./checkslave.pl
line 19.
Snippet of code:
my $dbh_2 =
DBI-connect(dbi:mysql:ouofa:slave2.domain.org,systemcheck,mypasswd,
{ RaiseError = 1, AutoCommit = 0, PrintError = 0 } ) or die
(SLAVE2 MySQL database offline: $!) ;
my $cur_info = $dbh_2-prepare(SHOW SLAVE STATUS) ;
$cur_info-execute() ;
my @row = $cur_info-fetchrow_array() ;
... line 19 would be the -execute() call.
I did this:
GRANT SUPER,REPLICATION CLIENT to 'syscheck'@'mywebserver.domain.org'
IDENTIFIED BY 'mypasswd' ;
... on the slaves, but that didn't help me any.
How can I check a slave's status without having to manually log in to a
mysql client??
-id
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter table only on the master?
Hey, As I found out on the list, you want to SET SQL_LOG_BIN=0 in your session. The user making the changes needs have the SUPER privilege. http://dev.mysql.com/doc/mysql/en/SET_SQL_LOG_BIN.html On Tue, 9 Nov 2004 20:35:22 +0100, harm [EMAIL PROTECTED] wrote: Hello, I want to convert a table from innodb to myisam on the _master only_, not on the slaves. Is there a secret 'do this alter table only on the master' command, or do I really have to do some voodoo-replication-queries-skipping on the slaves? Thanks, Harmen -- The Moon is Waning Crescent (10% of Full) -- 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: replication problems
On Tuesday 09 November 2004 03:23 pm, ian douglas wrote: GRANT SUPER,REPLICATION CLIENT to 'syscheck'@'mywebserver.domain.org' IDENTIFIED BY 'mypasswd' ; I have no idea how this script is gonna help, but your having permission problems. The last message you wrote was permission problems as well.. There is a default login in mysql, I think your getting in as that, but not able to do anything.. Make your your host variables are right.. Check your logs to make sure your getting is as who your expecting to be.. (show processlist even). Jeff pgpa5Z4LVImkC.pgp Description: PGP signature
error importing from mysqldump output
I did a mysqldump from serverA, took that output and did the following as illustrated below to import into serverB. why did I get an error? Did mysqldump output the wrong SQL syntax? I would not think so, but I got this error which says so. I am using mysql-4.0.20 on both servers. I am importing with skip-grant-tables option, and no databases (including no mysql database). daemon0% mysqldump -S mysqld-daemon0.sock --master-data --all-databases daemin0-dump.sql daemon1% mysql -S /tmp/mysqld-daemon1.sock /tmp/daemon0-dump.sql ERROR 1064 at line 14071: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'order int(1) NOT NULL default '0', PRIMARY KEY (moduleID), CREATE TABLE modules ( moduleID varchar(10) NOT NULL default '', moduleName varchar(50) NOT NULL default '', order int(1) NOT NULL default '0', PRIMARY KEY (moduleID), UNIQUE KEY moduleID (moduleID) ) TYPE=MyISAM COMMENT='List of all Modules'; Would this possibly be a bug with mysqldump ? -RG -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subject Headings in Tables
OK; thanks! --- [EMAIL PROTECTED] wrote: Normalize, David. Normalize. Your Regions get their own table. If a country/state can belong to more than one region you will need another table to hold that association. I can't remember if this was ever recommended to you but I had a chance to re-read it recently and think this could help you to better understand the basic concepts of normalization. http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html Don't get caught up in the terminology he uses but pay close attention to how he organizes his sample data. Shawn Green Database Administrator Unimin Corporation - Spruce Pine David Blomstrom [EMAIL PROTECTED] wrote on 11/08/2004 09:30:41 PM: I have a series of database tables focusing on the world's nations. There are basic tables that give each nation's abbreviation and parent (e.g. Eurasia) and other tables that focus on information about the people, government, etc. Now I want to introduce some regional headers. For example, current displays might look something like this: Denmark Finland France Germany Portugal Spain Alaska Arizona Florida Montana Wyoming I want to somehow modify my MySQL tables and/or the PHP scripts I use to display data so that I have the option of displaying data just like above, OR like this: NORTHERN EUROPE Denmark Finland WESTERN EUROPE France Germany SOUTHERN EUROPE Portugal Spain FAR NORTH Alaska SOUTHWEST Arizona SOUTH Florida ROCKY MOUNTAINS Montana Wyoming You can see an example of my ultimate goal at http://www.geoworld.org/about/guide/world/eur/remote.php? order=2direction=0submit=Submit I'm designing this page to function as a pop-up window that stays open as people click from nation to nation. It uses a PHP script to order the nations alphabetically or by population or other topics. Eventually, I'd like to learn how to modify the switch so that it can display nations or states with headings - like ROCKY MOUNTAINS - then if you click another setting, the nations are rearranged and the headings aren't even visible. Anyway, I just wanted to ask for tips about incorporating these regional headings into my database. My first hunch is to simply add them to one of basics tables, like this: NAMEIDTYPE United States usa Nation Rocky Mountains rm Region Colorado co State Montana mt State That way, I can simply display the entire table as is - complete with the headins - or I can use PHP to block every row where TYPE = Region. Does this sound like sensible plan, or are there better options? Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UTF-8 compliance
What does this all entail? Can we use Under_Scores in table names. ThanksDonny LairsonPresidenthttp://www.gunmuse.com469 228 2183
Re: Subject Headings in Tables
Oops, I forgot my follow up question... I now have two tables: TABLE 1 Alabama Alaska Arizona Arkansas California Colorado Connecticut TABLE 2 Far North New England Pacific Coast South Southwest Rocky Mountains If I had put all of the above in one table, then it would be easy to display them like this: FAR NORTH Alaska NEW ENGLAND Connecticut But if I normalize my data, then I have to figure out how to make FAR NORTH from Table 2 display in a row just ahead of Alaska. How do you do that? Should I ask this on a PHP forum, or is this something people do with MySQL? Or can you use either one? Thanks. --- [EMAIL PROTECTED] wrote: Normalize, David. Normalize. Your Regions get their own table. If a country/state can belong to more than one region you will need another table to hold that association. I can't remember if this was ever recommended to you but I had a chance to re-read it recently and think this could help you to better understand the basic concepts of normalization. http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html Don't get caught up in the terminology he uses but pay close attention to how he organizes his sample data. Shawn Green Database Administrator Unimin Corporation - Spruce Pine David Blomstrom [EMAIL PROTECTED] wrote on 11/08/2004 09:30:41 PM: I have a series of database tables focusing on the world's nations. There are basic tables that give each nation's abbreviation and parent (e.g. Eurasia) and other tables that focus on information about the people, government, etc. Now I want to introduce some regional headers. For example, current displays might look something like this: Denmark Finland France Germany Portugal Spain Alaska Arizona Florida Montana Wyoming I want to somehow modify my MySQL tables and/or the PHP scripts I use to display data so that I have the option of displaying data just like above, OR like this: NORTHERN EUROPE Denmark Finland WESTERN EUROPE France Germany SOUTHERN EUROPE Portugal Spain FAR NORTH Alaska SOUTHWEST Arizona SOUTH Florida ROCKY MOUNTAINS Montana Wyoming You can see an example of my ultimate goal at http://www.geoworld.org/about/guide/world/eur/remote.php? order=2direction=0submit=Submit I'm designing this page to function as a pop-up window that stays open as people click from nation to nation. It uses a PHP script to order the nations alphabetically or by population or other topics. Eventually, I'd like to learn how to modify the switch so that it can display nations or states with headings - like ROCKY MOUNTAINS - then if you click another setting, the nations are rearranged and the headings aren't even visible. Anyway, I just wanted to ask for tips about incorporating these regional headings into my database. My first hunch is to simply add them to one of basics tables, like this: NAMEIDTYPE United States usa Nation Rocky Mountains rm Region Colorado co State Montana mt State That way, I can simply display the entire table as is - complete with the headins - or I can use PHP to block every row where TYPE = Region. Does this sound like sensible plan, or are there better options? Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Check out the new Yahoo! Front Page. www.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error importing from mysqldump output
Russell E Glaue wrote: snipped ERROR 1064 at line 14071: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'order int(1) NOT NULL default '0', PRIMARY KEY (moduleID), snipped Would this possibly be a bug with mysqldump ? -RG 'order' is a reserved word. I would rename that field, pronto! In my opinion the bug is not in mysqldump, but in mysql allowing you to use a fieldname that is a reserved word. And yes I know about the backticks that mysqlcc uses, but surely this causes more problems than it solves. See above example. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error importing from mysqldump output
Daniel Kasak wrote: Russell E Glaue wrote: snipped ERROR 1064 at line 14071: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'order int(1) NOT NULL default '0', PRIMARY KEY (moduleID), snipped Would this possibly be a bug with mysqldump ? -RG 'order' is a reserved word. I would rename that field, pronto! In my opinion the bug is not in mysqldump, but in mysql allowing you to use a fieldname that is a reserved word. And yes I know about the backticks that mysqlcc uses, but surely this causes more problems than it solves. See above example. So if a word is reserved, a table with that same spelling cannot exist? If this is so, then why did mysql allow it to be created? -RG -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error importing from mysqldump output
Russell E Glaue wrote: 'order' is a reserved word. I would rename that field, pronto! In my opinion the bug is not in mysqldump, but in mysql allowing you to use a fieldname that is a reserved word. And yes I know about the backticks that mysqlcc uses, but surely this causes more problems than it solves. See above example. So if a word is reserved, a table with that same spelling cannot exist? That's right. You can't ( you *shouldn't* be able to ) use reserved for table or field names. If this is so, then why did mysql allow it to be created? I really don't know. See my above comments. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Complex Query: Best way to write it?
Hi, I am trying to come up with the most optimal way to write a query to select out the following fields: test_username.username_name test_subprofile_details.subprofile_details_headline test_profile.profile_birthday test_profile_gender.profile_gender_name test_subprofile.subprofile_picture test_country_region.country_region_name test_country.country_name test_profile_orientation.profile_orientation_name (there could be multiple rows) With the following conditions: profile_orientation_id IN (1,2) profile_gender_id = 1 country_id = 1 site_id = 3 ORDER BY subprofile_last_update DESC LIMIT 30 I am using MySQL 4.1 so subqueries are a valid option. I know I could do many queries to get my result, but I'm trying to see how I could do this in 1 query if possible, or the least amount of queries possible otherwise. I assume complex joins and/or subqueries need to be used, but I'm not very experienced with them. Any help pointing me in the right direction, or giving me a solution (so then I could see how it was done for use in my future queries) would be greatly appreciated. Thanks in advance! Tables Involved: mysql desc test_username; +---+---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+---+--+-+-+---+ | uid | mediumint(8) unsigned | | PRI | 0 | | | username_name | varchar(20) | | UNI | | | +---+---+--+-+-+---+ mysql desc test_subprofile_details; ++---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++---+--+-+-+---+ | uid| mediumint(8) unsigned | | MUL | 0 | | | site_id| tinyint(1)| | MUL | 0 | | | subprofile_details_headline| varchar(255) | | | | | | subprofile_details_description | text | | | | | | subprofile_details_description_seeking | text | | | | | ++---+--+-+-+---+ mysql desc test_subprofile; ++---+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++---+--+-+-++ | subprofile_id | int(10) unsigned | | PRI | NULL| auto_increment | | uid| mediumint(8) unsigned | | MUL | 0 | | | site_id| tinyint(1)| | MUL | 0 | | | subprofile_status_id | tinyint(2)| | MUL | 0 | | | subprofile_last_update | int(10) unsigned | | MUL | 0 | | | subprofile_picture | tinyint(1)| | MUL | 0 | | | subprofile_voice | tinyint(1)| | MUL | 0 | | | subprofile_video | tinyint(1)| | MUL | 0 | | | subprofile_picture_gallery | tinyint(1)| | MUL | 0 | | | subprofile_visits | int(10) unsigned | | | 0 | | | subprofile_votes | mediumint(8) unsigned | | | 0 | | | subprofile_rating | tinyint(2)| | | 0 | | ++---+--+-+-++ mysql desc test_profile; +---+---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+---+--+-+-+---+ | uid | mediumint(8) unsigned | | PRI | 0 | | | profile_birthday | int(10) unsigned | | MUL | 0 | | | profile_gender_id | tinyint(1)| | MUL | 0 | | | profile_marital_status_id | tinyint(1)| | MUL | 0 | | | profile_body_type_id | tinyint(1)| | MUL | 0 | | | profile_height| tinyint(3)| | MUL | 0 | | | profile_zip | varchar(10) | | MUL | | | | country_id| tinyint(3) unsigned | |
Re: error importing from mysqldump output
At 10:33 +1100 11/10/04, Daniel Kasak wrote: Russell E Glaue wrote: 'order' is a reserved word. I would rename that field, pronto! In my opinion the bug is not in mysqldump, but in mysql allowing you to use a fieldname that is a reserved word. And yes I know about the backticks that mysqlcc uses, but surely this causes more problems than it solves. See above example. So if a word is reserved, a table with that same spelling cannot exist? That's right. You can't ( you *shouldn't* be able to ) use reserved for table or field names. If this is so, then why did mysql allow it to be created? I really don't know. See my above comments. order is indeed a reserved word, but it is easily possible to create a table that has order (or any other reserved word) as a column name if you quote it with identifier quoting characters (backticks): http://dev.mysql.com/doc/mysql/en/Legal_names.html As for mysqldump, if you use the command mysqldump --help, you get a list of alowable options. One of them is this: -Q, --quote-names Quote table and column names with a ` So if you add -Q or --quote-names to your mysqldump command, you should get dump output that can be imported without the problems that you are seeing. This option is on by default in MySQL 4.1, by the way, which helps forestall the issue. It remains off in 4.0 by default so as not to break existing 4.0 scripts that use mysqldump. -- 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: using mysqldump to export BLOB tables
At 12:06 -0500 11/9/04, Caron, Christian wrote: Hi, I have been looking around (manual, google, etc.) to find out if I can use mysqldump with BLOB tables, but to no avail. Is it possible to do so, and if yes, is there any flag I need to give to mysqldump? Or can you point me to the manual page that describes that? The page http://dev.mysql.com/doc/mysql/en/mysqldump.html did not enlight me. That's because there's nothing particularly remarkable about dumping BLOB values. Possibly you could have problems if they're really large. However, I take it from your message that you're trying to dump a table with BLOB columns and not having success? If so, it'd be a good idea to post a summary of what you're trying and what the results are. -- 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: replication problems
At 9:49 -0800 11/9/04, ian douglas wrote: Except that changes made on the master are not automatically picked up by the slaves. My bad, I wasn't running START SLAVE to get them going. Seems odd that this has to be done manually. Perhaps you have the --skip-slave-start option in an option file? http://dev.mysql.com/doc/mysql/en/Replication_Options.html Another possibility is that the slave threads are starting when the server starts, but the SQL thread stops because it encounters a replication error. -- 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]
Best Practices
I am working on a data warehousing solution involving mysql and have a question about best practices. We are standardized on mysql 4.1, and this is for a rather picky client. We have a relational transaction database that stores the results of customer calls and a dimensional reporting database used as a data mart by several applications. Each night, we run a process that aggregates the number of calls, the subjects of each call, and various other data to populate the reporting database. We would like to move to a real time solution, and are struggling with the best way to implment it. What we are considering is a solution where we mirror the transactional database and repopulate key tables in the reporting database every minute or few minutes. I am loathe to do this, mainly because it would add to our server load and could possibly lead to 'dirty reads' (i.e. where one table in the reporting database is populated with fresh data but others are not). At the same time, the client is demanding we implement something. Does anyone have any war stories or suggestions for how to accomplish this? Thank You, M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Practices
Have you thought about locking the reporting database for write? You could eliminate the dirty reads. If you are using InnoDB on the reporting tables, you could use a transaction for the update operation. That would accomplish the same thing. You could use replication to move the load to another server all together. On Tue, 9 Nov 2004 16:06:18 -0800 (PST), Michael Haggerty [EMAIL PROTECTED] wrote: I am working on a data warehousing solution involving mysql and have a question about best practices. We are standardized on mysql 4.1, and this is for a rather picky client. We have a relational transaction database that stores the results of customer calls and a dimensional reporting database used as a data mart by several applications. Each night, we run a process that aggregates the number of calls, the subjects of each call, and various other data to populate the reporting database. We would like to move to a real time solution, and are struggling with the best way to implment it. What we are considering is a solution where we mirror the transactional database and repopulate key tables in the reporting database every minute or few minutes. I am loathe to do this, mainly because it would add to our server load and could possibly lead to 'dirty reads' (i.e. where one table in the reporting database is populated with fresh data but others are not). At the same time, the client is demanding we implement something. Does anyone have any war stories or suggestions for how to accomplish this? Thank You, M -- 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: UTF-8 compliance
On Tue, 2004-11-09 at 23:17, [EMAIL PROTECTED] wrote: What does this all entail? You can use Arabic/Chinese etc characters Can we use Under_Scores in table names. Yes. This is not effected by UTF8 anyway. -- Edward A. Macnaghten http://www.edlsystems.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
union, intersct and except operation?
Hello,
I've had a problem trying to get working except operator in mysql:
statement SELECT study from table WHERE keyword = 'chemistry' NOT keyword
= 'computers' returns SQL error
I've got the following not working solutions to my problem:
SELECT [distinct] study FROM table WHERE keyword = 'chemistry' AND keyword
(or !=) 'computers'
SELECT study FROM table WHERE keyword = 'chemistry' AND NOT (or OR NOT)
keyword = 'computers'
To explain better what result I need to get, there is table
Id Study keyword
1 Achemistry
2 Acomputers
3 Bchemistry
4 Bcomputers
5 Cchemistry
I need to return study C, because it has only 'chemistry' keyword(not
'computers'). However, all the suggested solutions return me wrong results:
SELECT study FROM table WHERE keyword = 'chemistry' AND keyword
'computers'
returns A, B, C results
SELECT study FROM table WHERE keyword = 'computers' AND keyword
'chemistry'
returns A,B
SELECT study FROM table WHERE keyword = 'chemistry' AND NOT keyword =
'computers'
returns 0 results
I tried SELECT T.data_id from table T WHERE T.keyword = 'chemistry' EXCEPT
SELECT T2.data_id from table T2 WHERE T2.keyword =computers . But it gives
SQL error.
I, also, tried SELECT * from table as t1 left join table as t2 on
t1.study=t2.study and not t2.keyword ='computers' where t1.keyword =
'chemistry' - gives SQL error as well.
Is there a way to get exception (not) operator working in mysql at all,
does it mean that only Union (or) and Intersect (and) available?
I know that in fulltext search it's possible to do: sql = SELECT ...
MATCH...AGAINST ('+chemestry -omputers IN BOOLEAN MODE)
What about one field search?
Thank you,
Lana
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB data files keep growing with innodb_file_per_table
Heikki, Heikki Tuuri wrote: John, please post what SHOW INNODB STATUS says. The probable reason is that there are long transactions, or that purge is falling behind. Best regards, Heikki Tuuri Actually, I have no idea why purge was falling behind, since no row has ever been deleted from the database, and as far as I understand (and the docs say so) purge lag only concerns rows marked for deletion. Sorry, I forgot to attach 'SHOW INNODB STATUS' output to my first post, probably the main reason being that it never reported any errors or warnings, so it slipped off my mind ;) Here's some more info: The database accumulates price changes over time on foreign exchange market. 99% of all operations are single-row INSERTs/UPDATEs that happen 2-3 times/second. Once a day there're 'INSERT ... ON DUPLICATE KEY UPDATE' of several thousand rows, which are performed in 1000-row batches. All inserts use primary key and there're no auto_increment columns. SELECTs are rare (~once/hour) but can result in up to 500,000 rows returned. SELECTs use primary keys as well - there's no JOINs of any kind, only 'ORDER BY'. 'SHOW INNODB STATUS' output below. Good luck, Ivan P.S. Is there any way to see the structure/contents of idbdata files? It would probably shed some light on the subject... = 041110 5:12:05 INNODB MONITOR OUTPUT = Per second averages calculated from the last 14 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 33099, signal count 33069 Mutex spin waits 106616, rounds 265594, OS waits 2863 RW-shared spins 53566, OS waits 26538; RW-excl spins 2871, OS waits 2672 TRANSACTIONS Trx id counter 0 17800155 Purge done for trx's n:o 0 17800149 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 3696 MySQL thread id 534, query id 7111938 localhost 127.0.0.1 root show innodb status ---TRANSACTION 0 0, not started, OS thread id 896 MySQL thread id 511, query id 7080473 localhost 127.0.0.1 root ---TRANSACTION 0 17800153, not started, OS thread id 640 MySQL thread id 421, query id 7111935 localhost 127.0.0.1 mysql ---TRANSACTION 0 17800110, not started, OS thread id 2480 MySQL thread id 22, query id 7111918 localhost 127.0.0.1 mysql FILE I/O I/O thread 0 state: wait Windows aio (insert buffer thread) I/O thread 1 state: wait Windows aio (log thread) I/O thread 2 state: wait Windows aio (read thread) I/O thread 3 state: wait Windows aio (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 226645 OS file reads, 1093510 OS file writes, 599391 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 3.93 writes/s, 1.64 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 1, free list len 0, seg size 2, is empty Ibuf for space 0: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 553253, used cells 26, node heap has 1 buffer(s) 1.36 hash searches/s, 86.64 non-hash searches/s --- LOG --- Log sequence number 1 1826613855 Log flushed up to 1 1826613855 Last checkpoint at 1 1826612159 0 pending log writes, 0 pending chkp writes 125358 log i/o's done, 0.43 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 153403523; in additional pool allocated 918656 Buffer pool size 8192 Free buffers 0 Database pages 8191 Modified db pages 20 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 226501, created 8080, written 939024 0.00 reads/s, 0.00 creates/s, 3.43 writes/s Buffer pool hit rate 1000 / 1000 -- ROW OPERATIONS -- 0 queries inside InnoDB, 0 queries in queue Main thread id 956, state: sleeping Number of rows inserted 593305, updated 1606540, deleted 0, read 453171610 1.79 inserts/s, 4.00 updates/s, 0.00 deletes/s, 571.89 reads/s END OF INNODB MONITOR OUTPUT - Original Message - From: John B. Ivski [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, November 09, 2004 9:46 PM Subject: InnoDB data files keep growing with innodb_file_per_table Hello, I have a DB of about 100 tables, and MySQL is configured to keep them in separate files. Total size of the files is ~2GB. Most of the tables are of similar format and consist of many short fixed-length rows (~50 bytes/row). The database size is increased for several MB a day. Initial configuration had 200MB for InnoDB data files. Initial database size was ~1GB. Current database size is, as I said, ~2GB. Current size of idbdata files is 1.8GB. As you can see, idbdata files are growing faster than the database
Select Statement Query
I'm trying to do a select statement where: field targetApp equals the value acadreg and field accessed equals the value Y and filed enteredQue is like 2004-11-09 18% the whole enterqueue fieild is something like 2004-11-09 16:00:34 Here's what I'm trying to accomplish, I want to get all the records that equal Y and equal acadreg and the enteredQue is greater than 2004-11-09 18:00:00 which is 6pm. Here's what I tried: select * from queLog where accessed = Y and targetApp = acadreg and enteredQue like 2004-11-09 18%; It needs to display all the records that are greater than or equal to 6pm on Nov 9 2004 and of course, that equal equal accessed = y and targetApp = acadreg. I might have over explained this request, I'm sorry for that. -- Kory Wheatley Academic Computing Analyst Sr. Phone 282-3874 # Everything must point to him. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select Statement Query
How about SELECT * FROM queLog WHERE accessed = Y AND targetApp = acadreg AND enteredQue = '2004-11-09 18:00:00'; (You said greater than first, then you said greater than or equal to. I went with the latter.) Michael Kory Wheatley wrote: I'm trying to do a select statement where: field targetApp equals the value acadreg and field accessed equals the value Y and filed enteredQue is like 2004-11-09 18% the whole enterqueue fieild is something like 2004-11-09 16:00:34 Here's what I'm trying to accomplish, I want to get all the records that equal Y and equal acadreg and the enteredQue is greater than 2004-11-09 18:00:00 which is 6pm. Here's what I tried: select * from queLog where accessed = Y and targetApp = acadreg and enteredQue like 2004-11-09 18%; It needs to display all the records that are greater than or equal to 6pm on Nov 9 2004 and of course, that equal equal accessed = y and targetApp = acadreg. I might have over explained this request, I'm sorry for that. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Posting Question
On Tue, 9 Nov 2004 13:21:54 -0600, Lewick, Taylor [EMAIL PROTECTED] wrote: I am asking before I post so I don't anger everyone... Is this list okay to post a specific question regarding multiple row inserts.. I am doing this in perl, and I need some help with the perl part... There are a number of Perl lurkers (myself included) on this list so I'm sure someone wouldn't mind helping out. For future reference, perlmonks.org is a great place to post Perl specific questions if you feel your problem is a little too OT for a MySQL list. -- Kev. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysq query browser
hey guys i have tried to compile the tarball of the mysql query browser but i am getting problems with the .pc files , i had to look all over bugzilla's and the like to get a file called libxml-2.0-uninstalled.pc , which was one of the files i needed to put in the pkconfig directory so that pkg-config can pick it up, in the error message it actually doesnt state that it is the Exact file i need and it doesnt come with any package for some reason, now i need glibc-20.pc or the like to continue my install , can someone help me with it or can someone tell me where i can get it so i can try out the query browser, i am using SuSE 9.1. cheers -Z -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: union, intersct and except operation?
L a n a wrote:
Hello,
I've had a problem trying to get working except operator in mysql:
statement SELECT study from table WHERE keyword = 'chemistry' NOT
keyword = 'computers' returns SQL error
Of course. NOT is an operator, not a connector. That is, NOT keyword =
'computers' has the opposite boolean value of keyword = 'computers'. You
still need to connect it to the rest of your conditions with AND or OR.
I've got the following not working solutions to my problem:
SELECT [distinct] study FROM table WHERE keyword = 'chemistry' AND
keyword (or !=) 'computers'
SELECT study FROM table WHERE keyword = 'chemistry' AND NOT (or OR NOT)
keyword = 'computers'
Adding AND keyword != 'computers' to WHERE keyword = 'chemistry' is
pointless. Any row with keyword = 'chemistry' cannot have keyword =
'computers'.
To explain better what result I need to get, there is table
Id Study keyword
1 Achemistry
2 Acomputers
3 Bchemistry
4 Bcomputers
5 Cchemistry
I need to return study C, because it has only 'chemistry' keyword(not
'computers'). However, all the suggested solutions return me wrong results:
So, you want to choose a Study (not a row) based on looking at *all* the
rows with a particular Study value.
SELECT study FROM table WHERE keyword = 'chemistry' AND keyword
'computers'
returns A, B, C results
with Id = 1, 3, 5. That is, the rows with keyword = 'chemistry'.
SELECT study FROM table WHERE keyword = 'computers' AND keyword
'chemistry'
returns A,B
results in Ids 2 and 4, the rows with keyword = 'computers'.
SELECT study FROM table WHERE keyword = 'chemistry' AND NOT keyword =
'computers'
returns 0 results
Of course. keyword cannot have 2 different values **in the same row**.
Hence, no row matches.
I tried SELECT T.data_id from table T WHERE T.keyword = 'chemistry'
EXCEPT SELECT T2.data_id from table T2 WHERE T2.keyword =computers .
But it gives SQL error.
Right, this is not valid syntax.
I, also, tried SELECT * from table as t1 left join table as t2 on
t1.study=t2.study and not t2.keyword ='computers' where t1.keyword =
'chemistry' - gives SQL error as well.
I doubt that. This is valid syntax, though it doesn't do what you want.
It's close, though. See below.
Is there a way to get exception (not) operator working in mysql at all,
does it mean that only Union (or) and Intersect (and) available?
I know that in fulltext search it's possible to do: sql = SELECT ...
MATCH...AGAINST ('+chemistry -computers IN BOOLEAN MODE)
No, that selects a *row* which contains 'chemistry' but not 'computers' in
the fulltext-indexed columns. It does not compare values in one row to
values in another.
What about one field search?
Thank you,
Lana
One solution is similar to your above LEFT JOIN:
SELECT *
FROM table t1 LEFT JOIN table t2
ON t1.Study=t2.Study AND t2.keyword='computers'
WHERE t1.keyword='chemistry' AND t2.id is null;
That may seem a little strange, as we are asking for the opposite of what we
want on the right side of the join, but then we only take the rows from the
left which don't have a row on the right.
Another way to look at all the rows with a particular Study value would be
to GROUP BY Study and use aggregate functions. Something like
SELECT Study
FROM table
GROUP BY Study
HAVING SUM(IF(keyword='chemistry',1,0))
AND NOT SUM(IF(keyword='computers',1,0));
should do the trick.
If you like subqueries and have mysql 4.1, the following should also work:
SELECT * FROM table
WHERE keyword = 'chemistry'
AND Study NOT IN (SELECT Study FROM table t2
WHERE t2.keyword = 'computers');
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error After Upgrading 3.23.58 - 4.1.7
I recently upgraded MySQL from 3.23.58 to 4.1.7 on a Fedora Core 1 system.
After the upgrade, I ran into the following error with a previously
installed copy of Bugzilla. I think the core of the error is Client does
not support authentication protocol requested by server.
-[ERROR]-
DBI connect('host=localhost;database=bugz;port=3306','root',...) failed:
Client does not support authentication protocol requested by server;
consider upgrading MySQL client at
/usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi/DBI.pm line 586
DBI::__ANON__('undef','undef') called at
/usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi/DBI.pm line 637
DBI::connect('DBI','DBI:mysql:host=localhost;database=bugz;port=3306','','',
'HASH(0x9afe5a0)') called at Bugzilla/DB.pm line 146
Bugzilla::DB::_connect('DBI:mysql:host=localhost;database=bugz;port=3306')
called at Bugzilla/DB.pm line 138
Bugzilla::DB::connect_main() called at Bugzilla.pm line 143
Bugzilla::dbh('Bugzilla') called at Bugzilla/Auth/CGI.pm line 154
Bugzilla::Auth::CGI::login('Bugzilla::Auth::CGI',2) called at Bugzilla.pm
line 74
Bugzilla::login('Bugzilla',2) called at /www/htdocs/bugz/query.cgi line 60
at Bugzilla/DB.pm line 146
-
So far, my research shows that the issue may be with the Client. This is
where my knowledge is failing me as I am unsure of how to upgrade the
Client.
The install was done from source, as I have always found this to be the most
stable method of upgrading. Any assistance would be appreciated.
Thank You,
Jason Williard
Systems Administrator
PCSafe Inc.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: union, intersct and except operation?
SELECT DISTINCT study FROM table WHERE keyword='chemistry';
- Of course if it's 'chemistry' it IS ''computers' or anything else
'chemistry' 'computers'
so last part ot your SQL statement is obsolete
L a n a wrote:
Hello,
I've had a problem trying to get working except operator in mysql:
statement SELECT study from table WHERE keyword = 'chemistry' NOT
keyword = 'computers' returns SQL error
I've got the following not working solutions to my problem:
SELECT [distinct] study FROM table WHERE keyword = 'chemistry' AND
keyword (or !=) 'computers'
SELECT study FROM table WHERE keyword = 'chemistry' AND NOT (or OR NOT)
keyword = 'computers'
To explain better what result I need to get, there is table
Id Study keyword
1 Achemistry
2 Acomputers
3 Bchemistry
4 Bcomputers
5 Cchemistry
I need to return study C, because it has only 'chemistry' keyword(not
'computers'). However, all the suggested solutions return me wrong results:
SELECT study FROM table WHERE keyword = 'chemistry' AND keyword
'computers'
returns A, B, C results
SELECT study FROM table WHERE keyword = 'computers' AND keyword
'chemistry'
returns A,B
SELECT study FROM table WHERE keyword = 'chemistry' AND NOT keyword =
'computers'
returns 0 results
I tried SELECT T.data_id from table T WHERE T.keyword = 'chemistry'
EXCEPT SELECT T2.data_id from table T2 WHERE T2.keyword =computers .
But it gives SQL error.
I, also, tried SELECT * from table as t1 left join table as t2 on
t1.study=t2.study and not t2.keyword ='computers' where t1.keyword =
'chemistry' - gives SQL error as well.
Is there a way to get exception (not) operator working in mysql at all,
does it mean that only Union (or) and Intersect (and) available?
I know that in fulltext search it's possible to do: sql = SELECT ...
MATCH...AGAINST ('+chemestry -omputers IN BOOLEAN MODE)
What about one field search?
Thank you,
Lana
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error After Upgrading 3.23.58 - 4.1.7
Jason Williard wrote: I recently upgraded MySQL from 3.23.58 to 4.1.7 on a Fedora Core 1 system. After the upgrade, I ran into the following error with a previously installed copy of Bugzilla. I think the core of the error is Client does not support authentication protocol requested by server. [snip] So far, my research shows that the issue may be with the Client. This is where my knowledge is failing me as I am unsure of how to upgrade the Client. The install was done from source, as I have always found this to be the most stable method of upgrading. Any assistance would be appreciated. http://dev.mysql.com/doc/mysql/en/Old_client.html -- Registration for MX Europe 2005 is now open. http://www.mxeurope.org/go/registration Early bird discounts available. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
List annoyance
Why is this list reply to sender and not reply to list? Its annoying getting duplicated responses when someone replies to a message you send to the list. Its annoying getting single responses that are out of context and its annoying if responses go to the questioner personally, when the response may be of interest to other members of the list. Just a suggestion, but please make the list reply to list and not reply to sender. Thanks Stephen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
