MySQL ODBC UTF8 Problem
Hi all, I’m working in a project that use MySQL. The data is Vietnamese. So, I have used MS Access 2003 to test displaying this data. I have configured ODBC Initial Statement Option as “set names utf8”, and use MS Acccess link to the database. But, the data can’t display correctly. I’m using WinXP Pro, MS Access 2003 (font Arial), MySQL 5.0.19 (already set default charser to utf8), MySQL ODBC 3.51.12. Could someone please give me some ideas? Thank in advance, Hung
adding a field and settign the value
I am using phpmyadmin and I want to add a new field and then set the defualt value to all the values to a. How do I do a mass insert and not just create a big empty table row? The filed is called AREA it is a varchar of LENGTH 5 it should be NOT NULL and have a default value of a This is fine when I create the field and add new entries but the old ones have a nothing in them. Thanks, Ross
Re: adding a field and settign the value
--- [EMAIL PROTECTED] wrote: I am using phpmyadmin and I want to add a new field and then set the defualt value to all the values to a. How do I do a mass insert and not just create a big empty table row? The filed is called AREA it is a varchar of LENGTH 5 it should be NOT NULL and have a default value of a This is fine when I create the field and add new entries but the old ones have a nothing in them. Thanks, Ross Two and a half questions: a) do you have a DEFAULT value for your field? If you do then the next time you insert a null to that field (or omit it from an insert statement) it will gain that value. CREATE TABLE ... ... , AREA VARCHAR(5) DEFAULT 'a' ... ) b) have you tried this to reset your null values? UPDATE yourtablename SET area='a' where area is NULL or area=''; b.5) Are you trying to ADD a column or manage an existing one. Your post was not very clear Shawn Green Database Administrator Unimin Corporation - Spruce Pine __ 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]
describe table : improvement
Hello I run MySql 4.0.24 [ the release for the Debian stable Linux ]. when I do a describe a_table, it displays the 6 following columns : Field - Type - Null - Key - Default - Extra It would be nice if I could have a 7th column for a comment that could be used to describe the meaning of a field. Extra is to be used for other info. I could make a table especially for this purpose : create table comment (field varchar, comment varchar); but this will end into inconsistency at last [ 2 times the same field ]... Any ideas ? cheers. = Gilles Missonnier IAP - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multi Threaded on RedHat ES 4
I recently changed from SuSE ES 8 to RedHat ES 4 running MySQL 4.0.26 (I will be upgrading to at least 4.1 after busy season). I compiled my own version this time and when I start the server I see only one mysqld process. I am used to seeing hundreds as my max connect is set to 1000. This is for a very busy website. Did I miss a compile option? Is this an OS thing? I am putting this thing live and we have a couple of very busy weekends coming up. Any help would be appreciated. Thanks in adv! Ed -- Ed Pauley II Internet Software Developer Bloodstock Research Information Services 859.223. 800.354.9206 ext. 297 [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi Threaded on RedHat ES 4
Ed Pauley II wrote: I recently changed from SuSE ES 8 to RedHat ES 4 running MySQL 4.0.26 (I will be upgrading to at least 4.1 after busy season). I compiled my own version this time and when I start the server I see only one mysqld process. I am used to seeing hundreds as my max connect is set to 1000. This is for a very busy website. Did I miss a compile option? Is this an OS thing? I am putting this thing live and we have a couple of very busy weekends coming up. Any help would be appreciated. Thanks in adv! Ed Normally it should start instances on its own for every virtual host. Probably you don't have it compiled as V-Host compatible? -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi Threaded on RedHat ES 4
I think this article explains what I am seeing but I don't know how to determine which threading I am using. I just know it is not user threads in a separate process. http://dev.mysql.com/doc/refman/4.1/en/thread-packages.html Ed Pauley II wrote: I recently changed from SuSE ES 8 to RedHat ES 4 running MySQL 4.0.26 (I will be upgrading to at least 4.1 after busy season). I compiled my own version this time and when I start the server I see only one mysqld process. I am used to seeing hundreds as my max connect is set to 1000. This is for a very busy website. Did I miss a compile option? Is this an OS thing? I am putting this thing live and we have a couple of very busy weekends coming up. Any help would be appreciated. Thanks in adv! Ed -- Ed Pauley II Internet Software Developer Bloodstock Research Information Services 859.223. 800.354.9206 ext. 297 [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi Threaded on RedHat ES 4
Ed Pauley II wrote: I recently changed from SuSE ES 8 to RedHat ES 4 running MySQL 4.0.26 (I will be upgrading to at least 4.1 after busy season). I compiled my own version this time and when I start the server I see only one mysqld process. I am used to seeing hundreds as my max connect is set to 1000. This is for a very busy website. Did I miss a compile option? Is this an OS thing? I am putting this thing live and we have a couple of very busy weekends coming up. Any help would be appreciated. Thanks in adv! Ed man ps now look at the 'H' not '-H' option. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Loop Trigger
Hi all. I am creating a trigger that update the some table witch call it. Ex: create table t1 ( id int, name varchar(50), c int default 0, father int, primary key(id), index (father), foreign key (father) references t1(id) on update restrict on delete restrict ); create trigger tg_t1 before update on t1 for each row begin update t1 set c=c+1 where father=NEW.id; end; Something like it... I want to update some colums witch records are referencing by the father record. But when I update the mysql returns: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger Why I cant make a trigger that update the some table that invoke the trigger??? How can I build it? Thanks. Lucas Vendramin
mysqldumps from java program
Hi Everybody, I need a suggestion regarding mysqldump. My problem is my application is creatiing around 500Mb of data per day. As i want my application run 24*7*365. I need a mechanisem where i can move, previous day's data to another location(i.e) at any given time i just want to store one or two days data only in my current DB. So i planned to make this by using mysqldump, as u know it will create files which we can upload where ever we need. Is this is a good idea, or we have another better mechanisem? I am planning to automate this using Java. Is we have any prebiuild tools for this?
Re: mysqldumps from java program
Is it possible to setup replication so you would have another server to do backups on? Replicate the data, do whatever you want to the spare, and then delete the data from the production server. On 4/19/06, balaraju mandala [EMAIL PROTECTED] wrote: Hi Everybody, I need a suggestion regarding mysqldump. My problem is my application is creatiing around 500Mb of data per day. As i want my application run 24*7*365. I need a mechanisem where i can move, previous day's data to another location(i.e) at any given time i just want to store one or two days data only in my current DB. So i planned to make this by using mysqldump, as u know it will create files which we can upload where ever we need. Is this is a good idea, or we have another better mechanisem? I am planning to automate this using Java. Is we have any prebiuild tools for this?
[Fwd: Re: mysqldumps from java program]
-- Ed Pauley II Internet Software Developer Bloodstock Research Information Services 859.223. 800.354.9206 ext. 297 [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com ---BeginMessage--- If I am not mistaken deleting from the production server would delete the data on the slave in a replication environment. William Fong wrote: Is it possible to setup replication so you would have another server to do backups on? Replicate the data, do whatever you want to the spare, and then delete the data from the production server. On 4/19/06, balaraju mandala [EMAIL PROTECTED] wrote: Hi Everybody, I need a suggestion regarding mysqldump. My problem is my application is creatiing around 500Mb of data per day. As i want my application run 24*7*365. I need a mechanisem where i can move, previous day's data to another location(i.e) at any given time i just want to store one or two days data only in my current DB. So i planned to make this by using mysqldump, as u know it will create files which we can upload where ever we need. Is this is a good idea, or we have another better mechanisem? I am planning to automate this using Java. Is we have any prebiuild tools for this? -- Ed Pauley II Internet Software Developer Bloodstock Research Information Services 859.223. 800.354.9206 ext. 297 [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com ---End Message--- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Fwd: Re: mysqldumps from java program]
If I am not mistaken deleting from the production server would delete the data on the slave in a replication environment. Not if you disable the binlog of the query that will delete data... -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import .frm .myd .myi to Mysql
HI, Take look at your my.cnf and obtain the location of your mysql data directory. Login into mysql create the database into which you want to import the tables. What this effectively does is create a directory under your mysql data directory. Now copy the .myd, .myi and .frm files to the directory. I am not sure whether you need to restart the server. Try accessing the tables without restart, if not successful restart the server :) eg. if the data directory is /usr/var/mysql If you created a database called xyz then you have to copy the .myd, .myi and .frm files to the directory /usr/var/mysql/xyz Hope this helps. Thanx Alex On 4/18/06, hicham [EMAIL PROTECTED] wrote: On 4/18/06, Dominik Klein [EMAIL PROTECTED] wrote: hicham schrieb: Hello I'm new bie user of mysql, I need to create a database and import some frm .myd .myi files to that database , also if you can point me to some easy to start tutorial for how to create user account in mysql , create a database , actually, I have a php / mysql application with a database which comes in a bench of files with .frm , .myd and .myi , what are theses files ? and I don't know how to get these files imported to tha mysql db . Thanks hicham -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Fwd: Re: mysqldumps from java program]
out of curiosity, how do you do that? Daniel da Veiga wrote: If I am not mistaken deleting from the production server would delete the data on the slave in a replication environment. Not if you disable the binlog of the query that will delete data... -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- Ed Pauley II Internet Software Developer Bloodstock Research Information Services 859.223. 800.354.9206 ext. 297 [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Fwd: Re: mysqldumps from java program]
On 4/19/06, Ed Pauley II [EMAIL PROTECTED] wrote:
out of curiosity, how do you do that?
Daniel da Veiga wrote:
If I am not mistaken deleting from the production server would delete
the data on the slave in a replication environment.
Don't get me wrong, try not to top-post, it makes the message harder to read...
SET SQL_LOG_BIN = {0|1}
Disables or enables binary logging for the current connection
(SQL_LOG_BIN is a session variable) if the client connects using an
account that has the SUPER privilege. The statement is ignored if the
client does not have that privilege.
So, if you DELETE data with an account that has the SUPER privilege,
you just issue this SET command before any statment and it won't log
your subsequent queries.
BTW, it was quoted fromt he MySQL Manual.
--
Daniel da Veiga
Computer Operator - RS - Brazil
-BEGIN GEEK CODE BLOCK-
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
--END GEEK CODE BLOCK--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Fwd: Re: mysqldumps from java program]
Daniel da Veiga wrote:
On 4/19/06, Ed Pauley II [EMAIL PROTECTED] wrote:
out of curiosity, how do you do that?
Daniel da Veiga wrote:
If I am not mistaken deleting from the production server would delete
the data on the slave in a replication environment.
Don't get me wrong, try not to top-post, it makes the message harder to read...
SET SQL_LOG_BIN = {0|1}
Disables or enables binary logging for the current connection
(SQL_LOG_BIN is a session variable) if the client connects using an
account that has the SUPER privilege. The statement is ignored if the
client does not have that privilege.
So, if you DELETE data with an account that has the SUPER privilege,
you just issue this SET command before any statment and it won't log
your subsequent queries.
BTW, it was quoted fromt he MySQL Manual.
--
Daniel da Veiga
Computer Operator - RS - Brazil
-BEGIN GEEK CODE BLOCK-
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
--END GEEK CODE BLOCK--
I just found it myself. Thanks for the detailed reply.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ORDER BY making recordset non-updatable
Heya. I am in the process of modifying a program to access data from a MySQL database instead of a SQL Server database. I have a view that is referenced as follows (through use of a data environment command): Select * from vwMyView where id = ? If I run this command, I get the data that I would expect, and I am able to update the data that I would expect to update (there are a few joins in the view so there are a couple fields that I understand that I cannot update). My problem is, if I add an ORDER BY statement at the end of this command, the recordset still returns data, but it becomes non-updatable. I would include my SQL, but unfortunately it is on a classified machine. I have verified the SQL numerous times and it works fine in every way except when I use ORDER BY. The SQL structure (though slightly modified for mySQL) also worked fine in SQL Server. Is this a known issue? Is there something that I could possibly be missing? I apologize for the lack of actual code, but I appreciate any insight! Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlmanager logging?
MySQL 5.0.20 I've got two instances running with mysqlmanager. I'm not getting any logging of any sort. mysqlmanager --help shows: - - log /var/lib/mysql/mysqlmanager.log pid-file /tmp/manager.pid socket/tmp/manager.sock bind-address (No default value) port 2273 password-file /etc/mysqlmanager.passwd default-mysqld-path /usr/sbin/mysqld monitoring-interval 10 run-as-serviceFALSE user (No default value) wait-timeout 28800 I've also tried to set the --log option in the [manager] section of /etc/my.cnf and restarted the server, to no avail. Do I have to run-as-service to get logging? Thanks for any help you can offer. Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Loop Trigger
Hi. Anyone know how to create a trigger that update the table that invoked it? -- Hi all. I am creating a trigger that update the some table witch call it. Ex: create table t1 ( id int, name varchar(50), c int default 0, father int, primary key(id), index (father), foreign key (father) references t1(id) on update restrict on delete restrict ); create trigger tg_t1 before update on t1 for each row begin update t1 set c=c+1 where father=NEW.id; end; Something like it... I want to update some colums witch records are referencing by the father record. But when I update the mysql returns: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger Why I cant make a trigger that update the some table that invoke the trigger??? How can I build it? Thanks. Lucas Vendramin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reply / Return Address of this List
Jay Blanchard wrote: [snip] could actually tell me a reason why it is better this way? [/snip] Here is the answer; http://www.unicom.com/pw/reply-to-harmful.html Interesting. I like the general tone of this is the only right thing, and we respect the opinion of anyone who differs, even though it makes them a blithering idiot. XD Also, one of the examples amusingly states you need to write down the address, delete the- Hold on. Copy/paste, anyone? The line near the end is the best: One day I accidentally sent a private, personal reply out over one of my own damn lists. Wherein it is once again proven that all 'logical, rational' decisions are merely subjective reactions to emotional trauma ,-). Also read this; http://www.caliburn.nl/topposting.html I've always been a fan of http://www.faqs.org/faqs/usenet/emily-postnews/ myself. However - it is clear, if nothing else in this thread is, that this is not likely to change anytime soon; that there are, in fact, valid technical and RFCish reasons for this being the case; that more importantly, the people who run the list are vehemently, if not rabidly, against the idea of arbitrarily screwing with message headers; and that this is not a position you can really find *too* much fault with, in the end. So can we stop flaming each other over mail practices and recommence flaming each other over dba practices instead? :D -- Rob Munsch Solutions For Progress IT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Run Apache/PHP/MySQL from CD?
I have no idea if this is possible or not but is there a way to run Apache, PHP, and MySQL from a CD? I'd like it to be possible to run it on Windows, Mac OSX and *nix. If it is possible could someone point me in the right direction? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Run Apache/PHP/MySQL from CD?
[snip] I have no idea if this is possible or not but is there a way to run Apache, PHP, and MySQL from a CD? I'd like it to be possible to run it on Windows, Mac OSX and *nix. If it is possible could someone point me in the right direction? [/snip] You'd have to have CD's for each OS on which you'd like to run. You can test this by putting the Apache executable (or one of the other executables) on a CD and trying to run it. http://www.google.com/search?hl=enq=run+apache+from+CD -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Run Apache/PHP/MySQL from CD?
[snip] I have no idea if this is possible or not but is there a way to run Apache, PHP, and MySQL from a CD? I'd like it to be possible to run it on Windows, Mac OSX and *nix. If it is possible could someone point me in the right direction? [/snip] Yippee, cross-posting! http://www.google.com/search?hl=enlr=q=run+MySQL+from+CD -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: describe table : improvement
It is: SHOW FULL COLUMNS FROM a_table You will get 2 extra columns: - Privileges (showing the privileges of the user for that column) - Comment (showing a per column comment) When creating a table you can add a comment using COMMENT keyword: CREATE TABLE a_table ( a_column CHAR(30) CHARSET utf8 COMMENT 'Some comment' ); Is this... what you needed ? -- Gabriel PREDA Senior Web Developer
Re: describe table : improvement
Hi, Gabriel PREDA wrote: It is: SHOW FULL COLUMNS FROM a_table You will get 2 extra columns: - Privileges (showing the privileges of the user for that column) - Comment (showing a per column comment) As well as Collation (the columns collation). When creating a table you can add a comment using COMMENT keyword: CREATE TABLE a_table ( a_column CHAR(30) CHARSET utf8 COMMENT 'Some comment' ); Is this... what you needed ? -- Gabriel PREDA Senior Web Developer Best regards Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: best use of index and missing values
2006/4/14, Philippe Poelvoorde [EMAIL PROTECTED]: Hi, I have one table : CREATE TABLE `ressources_summary` ( `res_type` tinyint(3) unsigned NOT NULL default '0', `res_id` int(10) unsigned NOT NULL default '0', `comment_count` smallint(5) unsigned NOT NULL default '0', `comment_last_timestamp` timestamp NOT NULL default '-00-00 00:00:00', `comment_last_user_id` int(11) NOT NULL default '0', PRIMARY KEY (`res_type`,`res_id`) ) ENGINE=MyISAM it gather summary on comments for any ressources (poll, articles, forums, admin). A ressource is uniquely identify by (res_type, res_id), the others columns are self-explanatory (I hope). I'm wondering about the efficiency of storing a comment_count of 0 (and user_id=0, timestamp=0). There is comments on 20% of the ressources. What is the cost of looking for a row that does not exists in a table ? Would it be more efficient to only store summary for comment_count greater than 0 and knowing missing rows have a count of zero, or to store everything ? I went for the version without all zeros. It dramatically reduce the number of rows, and overall performances are better with this summary table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Run Apache/PHP/MySQL from CD?
Well I have wondered about this as well. I guess you could do this, but if you want the MySQL databases to be updateable, then as the CD-ROM is read-only, the databases would have to remain somewhere on the hard drive. If you put the databases on the CD-ROM this would make them read-only and not updateable. It might even be possible to squeeze a minimal Linux distro onto the CD-ROM as well, and make it self-bootable and just running the Linux kernel, Apache, php and MySQL. Regards Keith Roberts In theory, theory and practice are the same; in practice they are not. On Wed, 19 Apr 2006, Jay Blanchard wrote: To: Jay Paulson [EMAIL PROTECTED], php-general@lists.php.net, mysql@lists.mysql.com From: Jay Blanchard [EMAIL PROTECTED] Subject: RE: Run Apache/PHP/MySQL from CD? [snip] I have no idea if this is possible or not but is there a way to run Apache, PHP, and MySQL from a CD? I'd like it to be possible to run it on Windows, Mac OSX and *nix. If it is possible could someone point me in the right direction? [/snip] You'd have to have CD's for each OS on which you'd like to run. You can test this by putting the Apache executable (or one of the other executables) on a CD and trying to run it. http://www.google.com/search?hl=enq=run+apache+from+CD -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Run Apache/PHP/MySQL from CD?
Yes -- try www.dwebpro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Per query DB stats... ideally for InnoDB
Is there any way to retrieve per SQL query stats from MySQL? Specifically for my need, the ability to determine the amount of system resources required to perform the query, ie CPU, disk usage, etc... I poked through the docs did some net searching, but couldn't find anything that I could use. I chatted with someone who thought that InnoDB had had some code added to it to start down this path, but that it wasn't exposed to the user level at all. An alternative would be a good method of determining the resource cost of a query though an examination of the explain data. Thanks! - Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Per query DB stats... ideally for InnoDB
Is this of any use at all?
From the 5.0.18 manual:
13.5.4.7. SHOW ENGINE Syntax
SHOW ENGINE engine_name {LOGS | STATUS }
SHOW ENGINE displays log or status information about
storage engines. The following statements currently are
supported:
snip
SHOW ENGINE INNODB STATUS
(or SHOW INNODB STATUS)
Both return alot of info on the InnoDB storage engine. Not
sure how to relate this to each SQL query though.
may be of interest too:
13.5.4.16. SHOW PROCESSLIST Syntax
SHOW [FULL] PROCESSLIST
SHOW PROCESSLIST shows you which threads are running. You
can also get this information using the mysqladmin
processlist statement. If you have the SUPER privilege, you
can see all threads. Otherwise, you can see only your own
threads (that is, threads associated with the MySQL account
that you are using). See Section 13.5.5.3, KILL Syntax. If
you do not use the FULL keyword, only the first 100
characters of each statement are shown in the Info field.
This statement is very useful if you get the too many
connections error message and want to find out what is going
on. MySQL reserves one extra connection to be used by
accounts that have the SUPER privilege, to ensure that
administrators should always be able to connect and check
the system (assuming that you are not giving this privilege
to all your users).
Regards
Keith
In theory, theory and practice are the same;
in practice they are not.
On Wed, 19 Apr 2006, Samuel Ziegler wrote:
To: mysql@lists.mysql.com
From: Samuel Ziegler [EMAIL PROTECTED]
Subject: Per query DB stats... ideally for InnoDB
Is there any way to retrieve per SQL query stats from MySQL?
Specifically for my need, the ability to determine the amount of system
resources required to perform the query, ie CPU, disk usage, etc...
I poked through the docs did some net searching, but couldn't find
anything that I could use.
I chatted with someone who thought that InnoDB had had some code added
to it to start down this path, but that it wasn't exposed to the user
level at all.
An alternative would be a good method of determining the resource cost
of a query though an examination of the explain data.
Thanks!
- Sam
--
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: Per query DB stats... ideally for InnoDB
Those functions do give useful information, but as you point out, the
trick is associating that information with a specific query. For my
use, there will most likely be other SQL activity going on at the same
time which makes doing a 'SHOW ENGINE ...' before and after the query
not very useful.
Thanks for the pointers, however.
- Sam
On Wed, 2006-04-19 at 23:30 +0100, [EMAIL PROTECTED] wrote:
Is this of any use at all?
From the 5.0.18 manual:
13.5.4.7. SHOW ENGINE Syntax
SHOW ENGINE engine_name {LOGS | STATUS }
SHOW ENGINE displays log or status information about
storage engines. The following statements currently are
supported:
snip
SHOW ENGINE INNODB STATUS
(or SHOW INNODB STATUS)
Both return alot of info on the InnoDB storage engine. Not
sure how to relate this to each SQL query though.
may be of interest too:
13.5.4.16. SHOW PROCESSLIST Syntax
SHOW [FULL] PROCESSLIST
SHOW PROCESSLIST shows you which threads are running. You
can also get this information using the mysqladmin
processlist statement. If you have the SUPER privilege, you
can see all threads. Otherwise, you can see only your own
threads (that is, threads associated with the MySQL account
that you are using). See Section 13.5.5.3, KILL Syntax. If
you do not use the FULL keyword, only the first 100
characters of each statement are shown in the Info field.
This statement is very useful if you get the too many
connections error message and want to find out what is going
on. MySQL reserves one extra connection to be used by
accounts that have the SUPER privilege, to ensure that
administrators should always be able to connect and check
the system (assuming that you are not giving this privilege
to all your users).
Regards
Keith
In theory, theory and practice are the same;
in practice they are not.
On Wed, 19 Apr 2006, Samuel Ziegler wrote:
To: mysql@lists.mysql.com
From: Samuel Ziegler [EMAIL PROTECTED]
Subject: Per query DB stats... ideally for InnoDB
Is there any way to retrieve per SQL query stats from MySQL?
Specifically for my need, the ability to determine the amount of system
resources required to perform the query, ie CPU, disk usage, etc...
I poked through the docs did some net searching, but couldn't find
anything that I could use.
I chatted with someone who thought that InnoDB had had some code added
to it to start down this path, but that it wasn't exposed to the user
level at all.
An alternative would be a good method of determining the resource cost
of a query though an examination of the explain data.
Thanks!
- Sam
--
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: Per query DB stats... ideally for InnoDB
Have you taken a look at this Samuel?
http://www.mysql.com/products/tools/administrator/index.html
Check out the demo in the TRH corner.
May be of some use to you.
Regards
Keith
In theory, theory and practice are the same;
in practice they are not.
On Wed, 19 Apr 2006, Samuel Ziegler wrote:
To: [EMAIL PROTECTED]
From: Samuel Ziegler [EMAIL PROTECTED]
Subject: Re: Per query DB stats... ideally for InnoDB
Those functions do give useful information, but as you point out, the
trick is associating that information with a specific query. For my
use, there will most likely be other SQL activity going on at the same
time which makes doing a 'SHOW ENGINE ...' before and after the query
not very useful.
Thanks for the pointers, however.
- Sam
On Wed, 2006-04-19 at 23:30 +0100, [EMAIL PROTECTED] wrote:
Is this of any use at all?
From the 5.0.18 manual:
13.5.4.7. SHOW ENGINE Syntax
SHOW ENGINE engine_name {LOGS | STATUS }
SHOW ENGINE displays log or status information about
storage engines. The following statements currently are
supported:
snip
SHOW ENGINE INNODB STATUS
(or SHOW INNODB STATUS)
Both return alot of info on the InnoDB storage engine. Not
sure how to relate this to each SQL query though.
may be of interest too:
13.5.4.16. SHOW PROCESSLIST Syntax
SHOW [FULL] PROCESSLIST
SHOW PROCESSLIST shows you which threads are running. You
can also get this information using the mysqladmin
processlist statement. If you have the SUPER privilege, you
can see all threads. Otherwise, you can see only your own
threads (that is, threads associated with the MySQL account
that you are using). See Section 13.5.5.3, KILL Syntax. If
you do not use the FULL keyword, only the first 100
characters of each statement are shown in the Info field.
This statement is very useful if you get the too many
connections error message and want to find out what is going
on. MySQL reserves one extra connection to be used by
accounts that have the SUPER privilege, to ensure that
administrators should always be able to connect and check
the system (assuming that you are not giving this privilege
to all your users).
Regards
Keith
In theory, theory and practice are the same;
in practice they are not.
On Wed, 19 Apr 2006, Samuel Ziegler wrote:
To: mysql@lists.mysql.com
From: Samuel Ziegler [EMAIL PROTECTED]
Subject: Per query DB stats... ideally for InnoDB
Is there any way to retrieve per SQL query stats from MySQL?
Specifically for my need, the ability to determine the amount of system
resources required to perform the query, ie CPU, disk usage, etc...
I poked through the docs did some net searching, but couldn't find
anything that I could use.
I chatted with someone who thought that InnoDB had had some code added
to it to start down this path, but that it wasn't exposed to the user
level at all.
An alternative would be a good method of determining the resource cost
of a query though an examination of the explain data.
Thanks!
- Sam
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Compiling 5.0.20 on Tru64
Hi, I'm trying to compile on Tru64 using the configure from the reference guide for using the HP/Compaq compiler (corrected for 'inline') CC=cc -pthread CFLAGS=-O4 -ansi_alias -ansi_args -fast -inline all -arch host CXX=cxx -pthread CXXFLAGS=-O4 -ansi_alias -ansi_args -fast -inline all \ -arch host -noexceptions -nortti export CC CFLAGS CXX CXXFLAGS ./configure \ --prefix=/usr/local/mysql \ --with-low-memory \ --enable-large-files \ --enable-shared=yes \ --with-named-thread-libs=-lpthread -lmach -lexc -lc However, when it comes time to link, 'ld' throws the error: /usr/bin/ld: -pthread: Unknown flag /usr/bin/ld: Usage: /usr/bin/ld [options] file [...] gmake[2]: *** [libz.la] Error 1 gmake[2]: Leaving directory `/data/src/mysql/mysql-5.0.20/zlib' gmake[1]: *** [all-recursive] Error 1 gmake[1]: Leaving directory `/data/src/mysql/mysql-5.0.20' gmake: *** [all] Error 2 I've tried moving the -pthread to CFLAGS with no effect. Has anybody gotten around this problem? Thanks in advance Quentin The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to Verify Replication Status?
I have master-slave replication working fine. However, I worry about the possibility of the master and slave accidentally getting out of synchronization. Are there circumstances (other than a direct INSERT to the slave) that could cause the master and slave to be out of sync? Is there a way to periodically do some kind of full check to verify that the slave is an exact duplicate of the master? I thought of just counting the rows in all the tables on both servers, but that only tells part of the story. Is the a more elegant and complete way? Also, the servers are separated by a slow WAN link, so transferring the whole database across the network is not an option. -- Eric Robinson Director of Information Technology Physician Select Management, LLC 775.720.2082
Starting mysql through sudo
Hi, What binaries directory premissions are required to enable in sudo to start mysql Instance through sudo. Thanks, - How low will we go? Check out Yahoo! Messengers low PC-to-Phone call rates.
Re: How to Verify Replication Status?
Never used it, but this might help: http://dev.mysql.com/doc/refman/5.0/en/checksum-table.html James Harvard At 5:00 pm -0700 19/4/06, Robinson, Eric wrote: I have master-slave replication working fine. However, I worry about the possibility of the master and slave accidentally getting out of synchronization. Are there circumstances (other than a direct INSERT to the slave) that could cause the master and slave to be out of sync? Is there a way to periodically do some kind of full check to verify that the slave is an exact duplicate of the master? I thought of just counting the rows in all the tables on both servers, but that only tells part of the story. Is the a more elegant and complete way? Also, the servers are separated by a slow WAN link, so transferring the whole database across the network is not an option. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
newbie optimization question
Hi. I'm new to database optimization and I have a couple of questions. I have a table like this: +++-+-+ | id | fullname | email | user_id | +++-+-+ Where fullname and email are varchar(100) and user_id is a non nullable foreign key. I have indices on every column. InnoDB engine. * Question 1: How can I optimize the case where I filter on one key but sort on another? This is fast: SELECT * FROM contacts WHERE fullname LIKE j% ORDER BY fullname LIMIT 10; But this is slow: SELECT * FROM contacts WHERE fullname LIKE j% ORDER BY email LIMIT 10; EXPLAIN tells me that the optimizer is using filesort for the second but not the first (which makes sense.) * Question 2: Why does introducing an extra WHERE clause make things slower? If I do this: SELECT * FROM contacts WHERE fullname LIKE j% AND user_id=1 ORDER BY fullname LIMIT 10; The results come back several orders of magnitude slower. This is despite the facts that: A) The results are the same for this query as the one without the test for user_id and B) About 95% of the records of in the table have user_id=1 Any insight will be greatly appreciated. Thanks, Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
where group and inner join
Best groupmember, I am doing this query that works fine. SELECT tps.tour_player_id, sum(if(tsh.hole_number=9, tps.strokes, '0')) AS 'front_9', sum(if(tsh.hole_number=10, tps.strokes, '0')) AS 'back_9', sum(tps.strokes) AS 'score' FROM tour_player_score tps INNER JOIN tour_scorecard_hole tsh ON tps.scorecard_hole_id=tsh.id GROUP BY tps.tour_player_id ORDER BY score, back_9; However, I would like to just get the result where sum(tps.strokes)90, so I added WHERE sum(tps.strokes)90 after the inner join like this SELECT tps.tour_player_id, sum(if(tsh.hole_number=9, tps.strokes, '0')) AS 'front_9', sum(if(tsh.hole_number=10, tps.strokes, '0')) AS 'back_9', sum(tps.strokes) AS 'score' FROM tour_player_score tps INNER JOIN tour_scorecard_hole tsh ON tps.scorecard_hole_id=tsh.id WHERE sum(tps.strokes) 90 GROUP BY tps.tour_player_id ORDER BY score, back_9; It gives me error : Invalid use of group function Where does the error come from? And how would I solve this? Best regards, Peter Lauri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: where group and inner join
In the last episode (Apr 20), Peter Lauri said: Best groupmember, I am doing this query that works fine. SELECT tps.tour_player_id, sum(if(tsh.hole_number=9, tps.strokes, '0')) AS 'front_9', sum(if(tsh.hole_number=10, tps.strokes, '0')) AS 'back_9', sum(tps.strokes) AS 'score' FROM tour_player_score tps INNER JOIN tour_scorecard_hole tsh ON tps.scorecard_hole_id=tsh.id GROUP BY tps.tour_player_id ORDER BY score, back_9; However, I would like to just get the result where sum(tps.strokes)90, so I added WHERE sum(tps.strokes)90 after the inner join like this SELECT tps.tour_player_id, sum(if(tsh.hole_number=9, tps.strokes, '0')) AS 'front_9', sum(if(tsh.hole_number=10, tps.strokes, '0')) AS 'back_9', sum(tps.strokes) AS 'score' FROM tour_player_score tps INNER JOIN tour_scorecard_hole tsh ON tps.scorecard_hole_id=tsh.id WHERE sum(tps.strokes) 90 GROUP BY tps.tour_player_id ORDER BY score, back_9; It's useful to note that SELECT statements generally work in the order they are written. The WHERE clause applies to the records as they are read from the source tables, and at that point, no grouping has been done, so there's no sum. Try moving your filter to a HAVING clause, which comes between GROUP BY and HAVING, and applies to the final resultset of the table (and at that point, you do have a sum(tps.strokes) column). -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: where group and inner join
In the last episode (Apr 19), Dan Nelson said: It's useful to note that SELECT statements generally work in the order they are written. The WHERE clause applies to the records as they are read from the source tables, and at that point, no grouping has been done, so there's no sum. Try moving your filter to a HAVING clause, which comes between GROUP BY and HAVING, and applies to the which comes between GROUP BY and ORDER BY, of course :) -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: where group and inner join
Thanks. That worked smooth as silk! -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Thursday, April 20, 2006 11:42 AM To: Peter Lauri Cc: mysql@lists.mysql.com Subject: Re: where group and inner join In the last episode (Apr 19), Dan Nelson said: It's useful to note that SELECT statements generally work in the order they are written. The WHERE clause applies to the records as they are read from the source tables, and at that point, no grouping has been done, so there's no sum. Try moving your filter to a HAVING clause, which comes between GROUP BY and HAVING, and applies to the which comes between GROUP BY and ORDER BY, of course :) -- Dan Nelson [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: mysqldumps from java program
Thank you guys, let me try in this way, i will come back if i get any problems. On 4/19/06, balaraju mandala [EMAIL PROTECTED] wrote: Hi Everybody, I need a suggestion regarding mysqldump. My problem is my application is creatiing around 500Mb of data per day. As i want my application run 24*7*365. I need a mechanisem where i can move, previous day's data to another location( i.e) at any given time i just want to store one or two days data only in my current DB. So i planned to make this by using mysqldump, as u know it will create files which we can upload where ever we need. Is this is a good idea, or we have another better mechanisem? I am planning to automate this using Java. Is we have any prebiuild tools for this?
