Recovering table structures.
Had to rebuild my laptop this weekend. Made sure to make a backup copy of /var/lib/mysql/*. Loaded on the mysql binary tar (as gentoo doesn't have 4.1.3 in portage yet), and copied my data back across. However, even with the innodb files in place, the mysql engine is saying things like: Didn't find any fields in table 'company_addresses' Didn't find any fields in table 'company_contacts' I realise that this is probably because you're not meant to copy innodb files around : Is there any way I can recover the structures (ie, create table statements) - the data is irrelevant and can be recreated easily. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database export
I have a 4GB database which i need to move to another hosting company. The source and destination databases are mysql so there is no conversion issue. My question is has anyone any recomendations on which is the best method for doing this. I am aware of doing a database dump and then importing into the new server. But i want to know if there are any issues with the file size. i.e. will carrying out an import time out with the file size being so large. I have also come across mysqlhotcopy, will this do? Any thoughts on this will be much appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database export
I used to check, optimize and repair tables, tar the database directory and copy it simply. :) For me this is the simpliest way. Best Regards, Cemal Dalar a.k.a Jimmy System Administrator Web Developer http://www.dalar.net - Original Message - From: Edd Dawson [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, August 16, 2004 1:39 PM Subject: Database export I have a 4GB database which i need to move to another hosting company. The source and destination databases are mysql so there is no conversion issue. My question is has anyone any recomendations on which is the best method for doing this. I am aware of doing a database dump and then importing into the new server. But i want to know if there are any issues with the file size. i.e. will carrying out an import time out with the file size being so large. I have also come across mysqlhotcopy, will this do? Any thoughts on this will be much appreciated. -- 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]
SQL_SLAVE_SKIP_COUNTER
Hi, If I run these commands on slave: SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 (3 times and then...) START SLAVE How many counter will be skipped? Is it 3 or just 1? Thank you. Regards, Batara -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication blocked
A few brief comments on this one... (1) Multi-threaded would probably cause thousands of problems Well, not necessarily. Currently, the slave reads transaction A from the relay log, executes its actions, commits it, then reads transaction B, executes and commits. It wouldn't be unreasonable to allow the two transactions to execute on different threads, provided that (a) B can't commit until A does and (b) in case of deadlock, B is rolled back and re-executed, rather than A. Small matter of programming, but not necessarily intractable. (2) If replication is being used for backup, a transaction on the master is backed up as soon as it has been copied to the relay log. A delay in executing the relay log isn't a problem unless either (a) you are doing queries against the slave and need timely data or (b) such delays are so frequent that the slave can never catch up. (3) In the original post, the problem was that during 12 minutes to create an index on the slave, other transactions can be locked out. Presumably, on the master, shared locks are set on every row of the table as the ALTER TABLE is run, so it is certainly possible that updates to the table can be delayed or that a deadlock can happen on the master, as well. = Original message follows = Date: Fri, 13 Aug 2004 14:42:28 -0700 From: Jeremy Zawodny [EMAIL PROTECTED] To: Donny Simonton [EMAIL PROTECTED] Cc: 'Batara Kesuma' [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: Replication blocked On Fri, Aug 13, 2004 at 01:19:14AM -0500, Donny Simonton wrote: There is only one thread for replication on the slave. It does one step a time. If you use mysqlbinlog on one of your binary files on your master, you will see exactly how it all works. No, there are 2 threads: the IO (or relay) thread, and the SQL thread. Multi-threaded would probably cause thousands of problems. Unless it was threaded per table, but that would still cause problems because of multi-table deletes and updates. Agreed. Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best way to construct query for 1M records DB?
Hello all, I am stuck on the best way to proceed in order to make a SQL query efficient and quick searching through a very large (near million and growing) database. I am working with MySQL 3.23.58 and using PHP to construct a dynamic query, based on HTML form input. Please let me know if you need more information, or would like to see the code which constructs the SQL statement. My form fields: Skills Last Name First Name City State Tables: candidate resume Candidate fields: Candidate_ID Last_Name First_Name Location ..snip.. Resume fields: Candidate_ID Section_ID Section_Value --- I did not create this database, and changing it is really not an option. --- Within PHP, I construct a dynamic query, using only those fields that the user has submitted in the form. Example, where SKILLS and STATE have been filled in: SELECT resume.Section_Value, candidate.Location FROM candidates, resumes WHERE resume.Section_Value CONTAINS 'html' OR 'unix' AND resume.Section_ID = '1' AND candidate.Location LIKE '%CA%' OR '%California%' (note: this is necessary due to the way the original table was created. Location information is in one text field, with some entries entered as 'CA' and some as 'California') Or where Location only is selected: SELECT candidate.Location FROM candidate WHERE camdodate.Location LIKE '%CA%' OR '%California%' I have thought of approaching this 2 ways. 1. Search first for candidates meeting every other criteria, ie. State, or Last Name. Then drill down on any results for matching skills. 2. Construct the full query based on all matches based on fields entered, and then matching results between tables with Candidate_ID for display. Of course, what if there IS no other criteria to drill down upon? Right now, the form requires that a state always be present. However, I will need to build in the ability to search for anyone with HTML and Unix skills in any state. So if 'skills' are the only fields submitted, the dynamic query would look like: SELECT resume.Section_Value FROM resume WHERE resume.Section_Value CONTAINS 'html' OR 'unix' AND resume.Section_ID = '1' (resume.Section_ID is the section for skills) It would entail searching through millions of records. Is there any way to make this faster and/or more efficient, based on the way I'm handling it? How long would this search take for so many records? Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Script to fill a Date base....
Hello
How I can fill a Database in MySQL with PHP whit the method POST???
I have a DB in MySQL and I have to fill with PHP, I don't know how I can, I
need a Script like this
?
$servidor = localhost ;
$usuario = pepe ;
$contrasena = * ;
$basededatos = pepe_bd ;
$conectar = mysql_connect($servidor,$usuario,$contrasena) ;
mysql_select_db($basededatos,$conectar) ;
if($enviar) {
mysql_query(insert into usuarios (usuario,contrasena,email) values
('$usuario','$contrasena','$email')) ;
echo El usuario ha sido registrado. ;
}
?
form method=post action=registrar.php
input type=text name=usuariobr
input type=password name=contrasenabr
input type=text name=emaibrbr
input type=submit name=enviar value=Registrar
/form
but this is not working I don't why?
Please if anyone have a Script that Do the same thing please send to me ok,
I only looking it and take the part of them that I need. ok.
Bye and thanks.
Yusdaniel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql-Prozesse lassen sich nicht mehr killen :-(
Hallo miteinander dies ist mein erster Thread. Weil ich nimmer weiter weiß wende ich mich an Euch. Hab nen Traffich collector der in ne Datenbank auf nem anderen Server schreibt. Nachdem der collector gerade am laden in die Datebank war wurde per cronjob das rotieren des in-use tables angestoßen. Natürlich hat sich das laden aufgehängt. Mein problem ist nun das sich nun über die Nacht hinweg so 15 Prozesse angehäuft haben. 1.Problem:Hab nun : Mysql kill z.B 23465; Gemacht nun steht da killed in der prozesslist aber die Verbindungen gehen nicht weg. Gibt's da ne möglichkeit die ganz verschwinden zu lassen um wieder was in die Datenbaqnk zu laden? 2.Problem: Wenn ich killall -9 mysqld mach und mysqld restart dann stoppt der server aber die Prozesse sind noch da in ps -edf Und ich kann die nicht mit kill -9 2344 usw. abschießen Nur wenn die Prozesse noch da sind is es nicht möglich den mysqld neu zu starten Was kann ich tun um die Zombies ohne reboot zu beenden. Benutze fedora core1 und mysql Ver 12.22 Distrib 4.0.20, for pc-linux (i686) mit freundlichem Gruß / kind regards Daniel Beuter SIEMENS IS IT PS 221 OP4 Network Solutions Werner von Siemens Str. 60 91052 Erlangen Fon: +49 (9131) -6303-211 Fax: +49 (9131) 7-42234 mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
SV: ASP Connection to Mysql fails
Hi
Try doing a response.write conn after you have made a connection with the
DSN
that should print out the connection string the DSN is using
i hope this help debug the problem
Nickolai
Paul Stearns wrote:
Actually I can connect from the web server to the DB server using DSN (the
odbc tool under windows). What I cannot do is connect from ASP in the same
way.
I have a work around, but I still would like to determine what the problem
is with a DSNless connection.
The work around is to use a DSN entry and call it, for example;
conn.open dsn=myodbcconn; user=myuser; password=mypassword
where myodbcconn is a System DSN
Why doesn't;
conn.open DRIVER={MySQL ODBC 3.51 Driver}; SERVER=myserver.com;
USER=myuser; PASSWORD=mypassword; DATABASE=myDB; PORT:3307; OPTION=35;
work?
Yes the port number really is 3307.
Paul
-Original Message-
From: Michael Stassen [EMAIL PROTECTED];]
Sent: 8/15/2004 8:05:23 PM
To: [EMAIL PROTECTED]
Subject: Re: ASP Connection to Mysql fails
If I understand you correctly, you can connect to both mysql servers from
localhost, but not to one from the other. To mysql, a user is a
combination
of user and connecting host, so I think your problem translates as you can
connect as [EMAIL PROTECTED], but you cannot connect as
[EMAIL PROTECTED]
You probably need to add a mysql user to allow this. See the manual
for
the details: http://dev.mysql.com/doc/mysql/en/Privilege_system.html and
http://dev.mysql.com/doc/mysql/en/GRANT.html.
Michael
Paul Stearns wrote:
Ignore the sentence that starts Remember...
When I telnet to port 3307 on the DB server from the web server I get the
version, a few characters of garbage and it disconnects.
Paul
-Original Message-
From: Paul Stearns [EMAIL PROTECTED];]
Sent: 8/15/2004 6:01:59 PM
To: [EMAIL PROTECTED]
Subject: ASP Connection to Mysql fails
I have an ASP application running on a server with a mysql DB, everything
works great. I connect using localhost, no problems.
I have a second mysql DB on another server. I connect to it via the odbc
Data Source Administrator from the web server with no problems.
When I try to connect from an ASP script from the web server using the
following;
=
Set connMailServer = Server.CreateObject(ADODB.Connection)
ConnectString = DRIVER={MySQL ODBC 3.51 Driver}; SERVER=myserver.com;
USER=root; PASSWORD=rootPW; DATABASE=myDB; PORT:3307; OPTION=35;
IntDebug=connMailServer.Open(ConnectString)
=
The server, user, password, database and port are what I used in the odbc
Data Source Administrator. Remember I can connect to my local DB from ASP.
I receive the following error;
==
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[MySQL][ODBC 3.51 Driver]Can't connect to MySQL server on 'myserver.com'
(10061)
=
The environment is w2k server on both computers. The MYSql version on
remote server is 4.0.17.
I'm so confused...
Paul
--
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 eats 100% CPU on a quad CPU system...
Hello people, I'm currently configuring a quad CPU system as a standalone MySQL server. The machine runs nothing except for MySQL and some trivial things like SSH and iptables. A second server acts as a webserver and is connected to this one though an internal LAN connection. Using top or ps, it shows that MySQL eats over 99% of all the available CPU. Using slmon, it shows that indeed all 4 CPUs are fully in use. Still, it appears to be terribly slow and I don't think MySQL should eat over 99% CPU on a quad CPU system... The machine runs mysqld 4.0.18 on Debian Sarge. A stripped down my.cnf is included below (I merely removed the comments). Could anyone tell me what can cause this problem? Thanks, - Fahr ===my.cnf=== [client] port= 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] err-log = /var/log/mysql/mysql.err socket = /var/run/mysqld/mysqld.sock open_files_limit = 8192 [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language= /usr/share/mysql/english skip-locking thread_stack= 128K skip-innodb max_connections = 500 key_buffer = 150M myisam_sort_buffer_size = 64M join_buffer_size = 1M read_buffer_size = 1M sort_buffer_size = 1M table_cache = 1500 thread_cache_size = 128 wait_timeout = 14400 connect_timeout = 10 max_allowed_packet = 16M max_connect_errors = 10 query_cache_limit = 1M query_cache_size = 32M query_cache_type = 1 [mysqldump] quick max_allowed_packet = 16M [mysql] [isamchk] key_buffer = 64M sort_buffer = 64M read_buffer = 16M write_buffer = 16M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication, Windows, Modems
Hi, hoping someone's come across this before: I need to replicate data between two copies of MySQL, both running on Windows XP. The amount of data is tiny - maybe 35k a day as an upper bound. It's basically monitoring data from a remote device. The problem is that neither machine is internet-enabled - they both have modems in them. So, what's the best way to run this sequence several times a day? a) connect to the remote machine via modem b) kick off the MySQL replication process I'm thinking a small VB app, but any pointers gratefully received. Google hasn't thrown up much of use, and Windows administration isn't really my thing. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GROUP BY optimization headscratcher
Reaching on this one, but how about reversing your join: FROM T2,T1 Based on your query, it appears you are summarizing all the data in T2 and pulling some extra data from T1. Since you are doing a full table scan (using all the records) on T2, you probably want to center your query around that table. You could even try a left join (T2 left join T1 on T2.qid=T1.qid). Also, create another index on the guid column in T2. That should allow MySQL to use the index to sort the records by guid for grouping. Finally, make sure your sort_buffer setting is high enough to accommodate what you are trying to do. On Aug 14, 2004, at 5:17 PM, Matt Eaton wrote: Michael, and . However, it didn't work. Whole thing still takes about 1 second. On the other hand, I realized I'm an idiot and that the reason it was running so fast when I handled temporary tables myself is that I was using mysqlcc, which truncated the first table to 1000 rows rather than 475,000, which--as one would imagine--sped things up considerably. However, I'm still looking for a way to make this fast. This is an integral part of my application, it'd be a big load off my mind ( my processesor) if I could get it under half a second on my box. I've made the changes Michael suggested, so I was wondering if anyone had suggestions on how to optimize this further. Below please find the query in question, a little background, the create statements and the output of explain: SELECT T2.guid, sum(T2.d+T1.d) AS theSum FROM T1, T2 WHERE T1.qid=T2.qid GROUP BY T2.guid; (I grouped by the wrong T last time, sorry). T1 contains one user, and their answers to various questions, so guid actually has only 1 value in this table, and qid has about 65, for a total of 65 rows. T2 contains about 15,000 users, so guid has 15,000 different values and qid has 34 possible values, and the total cardinality comes out to around 475,000. The Create Table statements look like: CREATE TABLE `T1` ( `guid` smallint(5) unsigned NOT NULL default '0', `qid` smallint(5) unsigned NOT NULL default '0', `a` tinyint(2) NOT NULL default '-2', `d` tinyint(2) NOT NULL default '-2', UNIQUE KEY `IX_T1_qid_guid` (`qid`,`guid`) ) TYPE=MyISAM CREATE TABLE `T2` ( `guid` mediumint(8) unsigned NOT NULL default '0', `qid` tinyint(3) unsigned NOT NULL default '0', `a` tinyint(4) NOT NULL default '0', `d` decimal(1,0) unsigned NOT NULL default '0', UNIQUE KEY `IX_T2_qid_guid` (`qid`,`guid`) ) TYPE=MyISAM And the explain is: +---+--+++-+ +--+-+ | table | type | possible_keys | key| key_len | ref| rows | Extra | +---+--+++-+ +--+-+ | T1| ALL | IX_T1_qid_guid | NULL |NULL | NULL | 65 | Using temporary; Using filesort | | T2| ref | IX_T2_qid_guid | IX_T2_qid_guid | 1 | T1.qid | 4979 | Using where | +---+--+++-+ +--+-+ Thanks so much! -Matt -Original Message- From: [mailto:[EMAIL PROTECTED] Sent: Saturday, August 14, 2004 3:46 AM To: Matt Eaton Subject: Re: GROUP BY optimization headscratcher Matt ME CREATE TABLE `T1` ( ME `guid` smallint(5) unsigned NOT NULL default '0', ME `qid` smallint(5) unsigned NOT NULL default '0', ME `a` tinyint(2) NOT NULL default '-2', ME `d` tinyint(2) NOT NULL default '-2', ME KEY `IX_FW_qid` (`qid`), ME KEY `IX_FW_d` (`d`) ME ) TYPE=HEAP ME CREATE TABLE `T2` ( ME `guid` mediumint(8) unsigned NOT NULL default '0', ME `qid` tinyint(3) unsigned NOT NULL default '0', ME `a` tinyint(4) NOT NULL default '0', ME `d` decimal(1,0) unsigned NOT NULL default '0', ME PRIMARY KEY (`guid`,`qid`), ME KEY `IX_s23aw_d` (`d`), ME KEY `IX_s23aw_qid` (`qid`) ME ) TYPE=HEAP ME SELECT T1.guid, sum(T1.d + T2.d) as theSum ME FROM T1, T2 ME WHERE T1.qid=T2.qid ME GROUP BY T1.guid make key in T1: KEY `` (qid,guid ) and change table type to MyIsam for both table. Michael Monashev http://softsearch.ru/ -- 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] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL eats 100% CPU on a quad CPU system...
It depends on lots of things. But as an estimate.. Probably you need to add some indexes.. Which type of queries you are using? which database type? Other things.. what about database size? maybe you need more RAM etc.. Best Regards, Cemal Dalar a.k.a Jimmy System Administrator Web Developer http://www.dalar.net - Original Message - From: Fahr o Mavastradioz [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, August 16, 2004 3:48 PM Subject: MySQL eats 100% CPU on a quad CPU system... Hello people, I'm currently configuring a quad CPU system as a standalone MySQL server. The machine runs nothing except for MySQL and some trivial things like SSH and iptables. A second server acts as a webserver and is connected to this one though an internal LAN connection. Using top or ps, it shows that MySQL eats over 99% of all the available CPU. Using slmon, it shows that indeed all 4 CPUs are fully in use. Still, it appears to be terribly slow and I don't think MySQL should eat over 99% CPU on a quad CPU system... The machine runs mysqld 4.0.18 on Debian Sarge. A stripped down my.cnf is included below (I merely removed the comments). Could anyone tell me what can cause this problem? Thanks, - Fahr ===my.cnf=== [client] port= 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] err-log = /var/log/mysql/mysql.err socket = /var/run/mysqld/mysqld.sock open_files_limit = 8192 [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language= /usr/share/mysql/english skip-locking thread_stack= 128K skip-innodb max_connections = 500 key_buffer = 150M myisam_sort_buffer_size = 64M join_buffer_size = 1M read_buffer_size = 1M sort_buffer_size = 1M table_cache = 1500 thread_cache_size = 128 wait_timeout = 14400 connect_timeout = 10 max_allowed_packet = 16M max_connect_errors = 10 query_cache_limit = 1M query_cache_size = 32M query_cache_type = 1 [mysqldump] quick max_allowed_packet = 16M [mysql] [isamchk] key_buffer = 64M sort_buffer = 64M read_buffer = 16M write_buffer = 16M -- 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]
AW: Mysql-Prozesse lassen sich nicht mehr killen :-(
OK Sorry for the german, will do it now in this way ;-) An script like this you said is running already,but the strange thing is that (for explanation--I have two datacollectors and one mysqlserver with 2 databases 1 and 2), If collector 1 is writing in db1 and collector 2 is starting loading in db2 , the second one who startes hang up und gets this zombielike status. My connections variable is on 100 ??? P.S: These processes are in Top shown with a S (sleep?) I found out only when I kill the right process which is in servername.pid then killall -9 mysqld And /mysqld stop, /mysqld start should let me kill all these processes. (but not always) -Ursprüngliche Nachricht- Von: KSTrainee [mailto:[EMAIL PROTECTED] Gesendet: Montag, 16. August 2004 14:56 An: Beuter Daniel Betreff: AW: Mysql-Prozesse lassen sich nicht mehr killen :-( Hallo, ein Hinweis vorweg: Bitte nur in Englisch auf die Liste posten, da es sich um eine internationale Liste handelt. Ich bekomm jedes mal nen Fön, wenn jemand in Spanisch oder Koreanisch posted - also sollten wir das auch nicht tun :-) Zu deinem Problem: Du kannst die Prozesse wahrscheinlich deshalb nicht killen, weil der aktuell ausgeführte Thread z.Z. in einer I/O Funktion auf ring0 im kernel läuft. Würde der Prozess jetzt abgebrochen, hätte dies eine kernel panic zur Folge - Freeze. Unter top sollte in der Prozess-Spalte ein einzelnes 'D' auf diesen Umstand hinweisen. Die Prozesse sind ohne Reboot meines Wissens nach nicht zu killen - das macht in sofern aber nichts, als dass sie keine CPU-Zeit verbrauchen und der verbrauchte Speicher irgendwann auf Nimmerwiedersehen in der swap verschwindet. Lösung des Problems ist, das Laden in die DB und die Rotation zu synchronisieren. Eine Möglichkeit ist z.B., dass du ein temp file mit der PID des Lade-Prozesses irgendwo hinschreibst. Der cronjob prüft, ob hinter der PID noch ein Prozess steht und rotiert nicht, falls das zutrifft. -Ursprüngliche Nachricht- Von: Beuter Daniel [mailto:[EMAIL PROTECTED] Gesendet: Montag, 16. August 2004 14:44 An: [EMAIL PROTECTED] Betreff: Mysql-Prozesse lassen sich nicht mehr killen :-( Hallo miteinander dies ist mein erster Thread. Weil ich nimmer weiter weiß wende ich mich an Euch. Hab nen Traffich collector der in ne Datenbank auf nem anderen Server schreibt. Nachdem der collector gerade am laden in die Datebank war wurde per cronjob das rotieren des in-use tables angestoßen. Natürlich hat sich das laden aufgehängt. Mein problem ist nun das sich nun über die Nacht hinweg so 15 Prozesse angehäuft haben. 1.Problem:Hab nun : Mysql kill z.B 23465; Gemacht nun steht da killed in der prozesslist aber die Verbindungen gehen nicht weg. Gibt's da ne möglichkeit die ganz verschwinden zu lassen um wieder was in die Datenbaqnk zu laden? 2.Problem: Wenn ich killall -9 mysqld mach und mysqld restart dann stoppt der server aber die Prozesse sind noch da in ps -edf Und ich kann die nicht mit kill -9 2344 usw. abschießen Nur wenn die Prozesse noch da sind is es nicht möglich den mysqld neu zu starten Was kann ich tun um die Zombies ohne reboot zu beenden. Benutze fedora core1 und mysql Ver 12.22 Distrib 4.0.20, for pc-linux (i686) mit freundlichem Gruß / kind regards Daniel Beuter SIEMENS IS IT PS 221 OP4 Network Solutions Werner von Siemens Str. 60 91052 Erlangen Fon: +49 (9131) -6303-211 Fax: +49 (9131) 7-42234 mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
connections are keeping inceresing
Hi, I found my MySQL's connections are keeping increasing recently. I setted the timeout as below: | interactive_timeout | 14400 | | wait_timeout | 14400 | When I run processlist, I found there are a lot of connections over 14400 but the connections are still sleeping. That's why my connections are keep inceasing. 61534 admin192.168.1.10abc 40099 Sleep 60027admin 192.168.1.10abc 40090 Sleep 61554admin 192.168.1.10abc 40041 Sleep 61732 admin 192.168.1.10abc 39825 Sleep Could anyone tell how to fix it? I am using MySQL-4.0.20 with Max on RedHat 9. Thanks Linda
Re: Mysql-Prozesse lassen sich nicht mehr killen :-(
Hi Daniel, try this: prompt mysqladmin kill [id],[id] (for usage see mysqladmin --help | less) Cheers, Thomas Spahni On Mon, 16 Aug 2004, Beuter Daniel wrote: Hallo miteinander dies ist mein erster Thread. Weil ich nimmer weiter weiß wende ich mich an Euch. Hab nen Traffich collector der in ne Datenbank auf nem anderen Server schreibt. Nachdem der collector gerade am laden in die Datebank war wurde per cronjob das rotieren des in-use tables angestoßen. Natürlich hat sich das laden aufgehängt. Mein problem ist nun das sich nun über die Nacht hinweg so 15 Prozesse angehäuft haben. 1.Problem:Hab nun : Mysql kill z.B 23465; Gemacht nun steht da killed in der prozesslist aber die Verbindungen gehen nicht weg. Gibt's da ne möglichkeit die ganz verschwinden zu lassen um wieder was in die Datenbaqnk zu laden? 2.Problem: Wenn ich killall -9 mysqld mach und mysqld restart dann stoppt der server aber die Prozesse sind noch da in ps -edf Und ich kann die nicht mit kill -9 2344 usw. abschießen Nur wenn die Prozesse noch da sind is es nicht möglich den mysqld neu zu starten Was kann ich tun um die Zombies ohne reboot zu beenden. Benutze fedora core1 und mysql Ver 12.22 Distrib 4.0.20, for pc-linux (i686) mit freundlichem Gruß / kind regards Daniel Beuter SIEMENS IS IT PS 221 OP4 Network Solutions Werner von Siemens Str. 60 91052 Erlangen Fon: +49 (9131) -6303-211 Fax: +49 (9131) 7-42234 mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems with DBI
Hi
Not sure if I should be posting to the DBI list really, but I will try
this one too.
I'm running suse linux enterprise server 8.2, MySQL 4.0.15-standard-log,
DBI 1.32
I get the following error message:
DBD::mysql::db rollback failed: ROLLBACK failed at dbirollbacktest.cgi
line 49
DBD::mysql::db rollback failed: ROLLBACK failed at dbirollbacktest.cgi
line 49
My script looks like this:
my $dbh = DBI-connect(DBI:mysql:test:localhost, ... );
$dbh-{AutoCommit} = 0;
$dbh-{RaiseError} = 1;
eval {
# the first SQL statement should be OK
my $sql = insert into testac(object_id, object_title) values
(22,'michael');
$dbh-do($sql);
# the second SQL statement should fail
my $sql = insert into testac(object_id, object_title) values
('\'string',22);
$dbh-do($sql);
# if get to here, commit
$dbh-commit();
};
if ($@) {
print One of the SQL statements failed\n;
$dbh-rollback();
}
$dbh-disconnect;
Does anyone know why my rollback won't work?
Thanks
Mick
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Recovering table structures. [recovered]
On Monday 16 August 2004 11:05, Duncan Hill might have typed: Had to rebuild my laptop this weekend. Made sure to make a backup copy of /var/lib/mysql/*. Loaded on the mysql binary tar (as gentoo doesn't have 4.1.3 in portage yet), and copied my data back across. However, even with the innodb files in place, the mysql engine is saying things like: Didn't find any fields in table 'company_addresses' Didn't find any fields in table 'company_contacts' I realise that this is probably because you're not meant to copy innodb files around : Is there any way I can recover the structures (ie, create table statements) - the data is irrelevant and can be recreated easily. Google eventually turned up http://archives.neohapsis.com/archives/mysql/2004-q1/1056.html which worked like a charm. I've lost all of the foreign key restraints, but I can recreate those easily. bash + hexedit = recovered structures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: connections are keeping inceresing
I would guess that your scripting library is not closing your connections for you as your variables go out of scope. Each database access library (PHP, DBI, ADO, etc) has its own command to manually close a connection. I suggest you review your code and make sure that you manually close each connection as soon as you no longer need it. This is a cross-platform issue and not related to any language in particular. Eventually, the garbage collection routine in your language will find your abandoned connections and reclaim them but that could take quite a while depending on how much heap space you have and the aggressiveness of your language's collector. It's always best to manually close your connection and dereference it before allowing the variables to go out of scope. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Linda [EMAIL PROTECTED] wrote on 08/16/2004 09:38:43 AM: Hi, I found my MySQL's connections are keeping increasing recently. I setted the timeout as below: | interactive_timeout | 14400 | | wait_timeout | 14400 | When I run processlist, I found there are a lot of connections over 14400 but the connections are still sleeping. That's why my connections are keep inceasing. 61534 admin192.168.1.10abc 40099 Sleep 60027admin 192.168.1.10abc 40090 Sleep 61554admin 192.168.1.10abc 40041 Sleep 61732 admin 192.168.1.10abc 39825 Sleep Could anyone tell how to fix it? I am using MySQL-4.0.20 with Max onRedHat 9. Thanks Linda
Re: Problems with DBI
michael watson (IAH-C) wrote:
Hi
Not sure if I should be posting to the DBI list really, but I will try
this one too.
I'm running suse linux enterprise server 8.2, MySQL 4.0.15-standard-log,
DBI 1.32
I get the following error message:
DBD::mysql::db rollback failed: ROLLBACK failed at dbirollbacktest.cgi
line 49
DBD::mysql::db rollback failed: ROLLBACK failed at dbirollbacktest.cgi
line 49
My script looks like this:
my $dbh = DBI-connect(DBI:mysql:test:localhost, ... );
$dbh-{AutoCommit} = 0;
$dbh-{RaiseError} = 1;
eval {
# the first SQL statement should be OK
my $sql = insert into testac(object_id, object_title) values
(22,'michael');
$dbh-do($sql);
# the second SQL statement should fail
my $sql = insert into testac(object_id, object_title) values
('\'string',22);
$dbh-do($sql);
# if get to here, commit
$dbh-commit();
};
if ($@) {
print One of the SQL statements failed\n;
$dbh-rollback();
}
$dbh-disconnect;
Does anyone know why my rollback won't work?
Thanks
Mick
No idea, but you could try doing explicit commit/rollback and begin
transaction (I used it with Sybase without any problems). Maybe the
DBI::MySQL doesn't support transaction.
--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL question, SELECT DISTINCT
say I'm selecting distinct (non-duplicate) rows for insertion, insert into original_table select distinct * from new_table these tables have 3 fields/row. Per the above code all 3 fields are evaluated by distict * . But my question is: I want to ignore field1, therefore I only want to test if any rows have field2 *and* field3 as dups - that would be my distinct test and then do the insert based on that. Test f2 and f3, not f1. What the sql? Help is greatly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question, SELECT DISTINCT
It all depends on which values of f1 you want to ignore. f1 f2 f3 - - -- val1-1 val2 val3 val1-2 val2 val3 val1-3 val2 val3 Which value of f1 would you want in your new table? Which ones to ignore? Are there other columns (beyond these 3) to move as well? Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine leegold [EMAIL PROTECTED] wrote on 08/16/2004 11:29:33 AM: say I'm selecting distinct (non-duplicate) rows for insertion, insert into original_table select distinct * from new_table these tables have 3 fields/row. Per the above code all 3 fields are evaluated by distict * . But my question is: I want to ignore field1, therefore I only want to test if any rows have field2 *and* field3 as dups - that would be my distinct test and then do the insert based on that. Test f2 and f3, not f1. What the sql? Help is greatly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question, SELECT DISTINCT
On Mon, 16 Aug 2004 11:36:32 -0400, [EMAIL PROTECTED] said: It all depends on which values of f1 you want to ignore. f1 f2 f3 - - -- val1-1 val2 val3 val1-2 val2 val3 val1-3 val2 val3 Which value of f1 would you want in your new table? Which ones to ignore? I want the DISTINCT to ignore the f1 column completely. But I want all 3 cols. ie. the entire row selected though. Pretend that f1 is a unique PK it'll always be different, I want to DISTINCT to ignore it, so if 2 or mores row have dup f2 *and* f3 Distinct will let only one through. Are there other columns (beyond these 3) to move as well? Nope. Just 3 cols. Thanks Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine leegold [EMAIL PROTECTED] wrote on 08/16/2004 11:29:33 AM: say I'm selecting distinct (non-duplicate) rows for insertion, insert into original_table select distinct * from new_table these tables have 3 fields/row. Per the above code all 3 fields are evaluated by distict * . But my question is: I want to ignore field1, therefore I only want to test if any rows have field2 *and* field3 as dups - that would be my distinct test and then do the insert based on that. Test f2 and f3, not f1. What the sql? Help is greatly appreciated. -- 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: Problems with DBI
I have a test.testac table created by a test script from a previous install.
It is of type MyISAM, which does not support transactions. Did you alter
or create your testac table to be of type InnoDB, which does support
transactions? You can check with
SHOW CREATE TABLE testac;
or
SHOW TABLE STATUS LIKE 'testac';
I tried your code on my machine (as dbhtest.pl). First, I ran it with
testac still of type MyISAM, with the following results:
DBD::mysql::db do failed: 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
'string',22)' at line 2 at /Users/stassenm/bin/dbhtest.pl line 18.
One of the SQL statements failed
DBD::mysql::db rollback failed: Warning: Some non-transactional changed tables
couldn't be rolled back at /Users/stassenm/bin/dbhtest.pl line 26.
DBD::mysql::db rollback failed: Warning: Some non-transactional changed tables
couldn't be rolled back at /Users/stassenm/bin/dbhtest.pl line 26.
Next, I converted testac to InnoDB, then ran it again, with these results:
DBD::mysql::db do failed: 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
'string',22)' at line 2 at /Users/stassenm/bin/dbhtest.pl line 18.
One of the SQL statements failed
In the latter case, the rollback was successful. In neither case did my
output match yours.
For reference, I'm running Mac OS X (10.3.5), MySQL 4.0.20, DBI 1.42
(current is 1.43), and DBD-mysql 2.9003 (current is 2.9004). I note that
your DBI version, 1.32, is significantly older than mine/current. You
didn't say what version of DBD-mysql you have. So, I would guess that:
* testac is type MyISAM, so transactions aren't supported. Possibly your
DBI-DBD-mysql is fine, other than that the error message wasn't very helpful.
or
* testac is type InnoDB, but there is a problem with rollback in your
DBI-DBD-mysql which has since been fixed.
Michael
michael watson (IAH-C) wrote:
Hi
Not sure if I should be posting to the DBI list really, but I will try
this one too.
I'm running suse linux enterprise server 8.2, MySQL 4.0.15-standard-log,
DBI 1.32
I get the following error message:
DBD::mysql::db rollback failed: ROLLBACK failed at dbirollbacktest.cgi
line 49
DBD::mysql::db rollback failed: ROLLBACK failed at dbirollbacktest.cgi
line 49
My script looks like this:
my $dbh = DBI-connect(DBI:mysql:test:localhost, ... );
$dbh-{AutoCommit} = 0;
$dbh-{RaiseError} = 1;
eval {
# the first SQL statement should be OK
my $sql = insert into testac(object_id, object_title) values
(22,'michael');
$dbh-do($sql);
# the second SQL statement should fail
my $sql = insert into testac(object_id, object_title) values
('\'string',22);
$dbh-do($sql);
# if get to here, commit
$dbh-commit();
};
if ($@) {
print One of the SQL statements failed\n;
$dbh-rollback();
}
$dbh-disconnect;
Does anyone know why my rollback won't work?
Thanks
Mick
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
T-SQL SUM() Overflow?
Hello, I'm trying to run a T-SQL query that will SUM a column of type INT. I'm, however, receiving the following error: Arithmetic overflow error converting expression to data type int. The four rows that its trying to sum have values of 3098082, 2488114, 316804, and 10657. What do I do? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication, Windows, Modems
David Otton [EMAIL PROTECTED] wrote on 16/08/2004 13:55:59: Hi, hoping someone's come across this before: I need to replicate data between two copies of MySQL, both running on Windows XP. The amount of data is tiny - maybe 35k a day as an upper bound. It's basically monitoring data from a remote device. The problem is that neither machine is internet-enabled - they both have modems in them. So, what's the best way to run this sequence several times a day? a) connect to the remote machine via modem b) kick off the MySQL replication process I'm thinking a small VB app, but any pointers gratefully received. Google hasn't thrown up much of use, and Windows administration isn't really my thing. I don't think you need to stop/start the replication process. If you set up the slave to replicate, it will keep trying to reconnect to the master at intervals - my memory says one minute, but the on-line manual is too soggy to check at the moment. You can therefore set up replication and leave it active while the modem connection is down. All you need to do is start up the modem connection at regular intervals and ensure it stays connected for at least two minutes. Linux users do this with a cron job; windows has some sort of scheduled task system. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question, SELECT DISTINCT
Let me see if I can explain it a little betterIf you need to move all 3 columns to the new table but you only want *1* row where f2 and f3 have a unique combination of values, how do you want to choose *which* value of f1 to move over with that combination? Do you want the minimum value, the maximum value, or no value at all? Or, is there some other criteria you need to consider in order to populate the f1 column of the new table with a value from your old table? Or, will the new table provide its own value for the f1 column? If I look at the sample data I set up, I see 3 rows with a unique f2/f3 combination but you only want to move 1 of them to a new table... Which value from f1 do you want to keep and which 2 do you want to throw away during the move? You are eliminating f1 values by reducing how many times the f2/f3 combination appears in the new table. All I need from you is a method to decide which f1 to keep and the SQL writes itself (almost) ;-D Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine leegold [EMAIL PROTECTED] wrote on 08/16/2004 12:13:06 PM: On Mon, 16 Aug 2004 11:36:32 -0400, [EMAIL PROTECTED] said: It all depends on which values of f1 you want to ignore. f1 f2 f3 - - -- val1-1 val2 val3 val1-2 val2 val3 val1-3 val2 val3 Which value of f1 would you want in your new table? Which ones to ignore? I want the DISTINCT to ignore the f1 column completely. But I want all 3 cols. ie. the entire row selected though. Pretend that f1 is a unique PK it'll always be different, I want to DISTINCT to ignore it, so if 2 or mores row have dup f2 *and* f3 Distinct will let only one through. Are there other columns (beyond these 3) to move as well? Nope. Just 3 cols. Thanks Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine leegold [EMAIL PROTECTED] wrote on 08/16/2004 11:29:33 AM: say I'm selecting distinct (non-duplicate) rows for insertion, insert into original_table select distinct * from new_table these tables have 3 fields/row. Per the above code all 3 fields are evaluated by distict * . But my question is: I want to ignore field1, therefore I only want to test if any rows have field2 *and* field3 as dups - that would be my distinct test and then do the insert based on that. Test f2 and f3, not f1. What the sql? Help is greatly appreciated. -- 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]
Update MySQL row using URL link?
I currently update MySQL rows using phpMyAdmin. For example, dropping the following into the phpMyAdmin GUI: UPDATE mysql_db SET publish = 1 WHERE Date = 'Sunday, August 15, 2004 21:04:32' Since I get the update info in an e-mail send whenever the form is submitted, I'd like to turn querys like the above encoded into a URL - bypassing phpMyAdmin - which when clicked, will update the row. I found this article last night: Make SQL Queries over HTTP with XML with VS.NET (http://www.aspfree.com/c/a/ASP/Make-SQL-Queries-over-HTTP-with-XML-with-VSNET/ ) SELECT CustomerId, CompanyName FROM Customer http://localhost/sql?sql=select%20CustomerId,%20CompanyName%20from%20Customers%20FOR%20XML%20AUTO = The above looks very much like what I have in mind, but for MySQL. Anybody know how to do this? Many thanks in advance, -Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database Stats
I have about 25 databases that I need to run the same query against that returns about 5 columns of information. I need this information then published within an Excel spreedsheet. What is the best way do handle this? Currently, I'm running each query manually, copying/pasting the result to Excel, and repeating... I need to run this procedure every week, and it takes a considerable amount of time. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Which ports
Greetings, I have Mysql installed on my freebsd box. I want to be able to access the database from the internet, but I need to allow the ports used in my firewall. What ports does mysql use ? thanks, Darryl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database Stats
I have about 25 databases that I need to run the same query against that returns about 5 columns of information. I need this information then published within an Excel spreedsheet. What is the best way do handle this? Currently, I'm running each query manually, copying/pasting the result to Excel, and repeating... I need to run this procedure every week, and it takes a considerable amount of time. In that case, I suggest automating it with a small program. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which ports
Greetings, Howdie, I have Mysql installed on my freebsd box. I want to be able to access the database from the internet, but I need to allow the ports used in my firewall. What ports does mysql use ? http://dev.mysql.com/doc/mysql/search.php?q=portfrom=%2Fdoc%2Fmysql%2Fen%2Flang=encharset=iso-8859-1 According to the documentation 3306 by default With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: T-SQL SUM() Overflow?
David- try redeclaring your INTEGER columns as BIGINT instead of INTEGER http://dev.mysql.com/doc/mysql/en/Numeric_types.html HTH, Martin Gainty From: David Mohorn [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: T-SQL SUM() Overflow? Date: Mon, 16 Aug 2004 11:37:12 -0500 MIME-Version: 1.0 Received: from lists.mysql.com ([213.136.52.31]) by mc1-f9.hotmail.com with Microsoft SMTPSVC(5.0.2195.6824); Mon, 16 Aug 2004 09:39:52 -0700 Received: (qmail 9896 invoked by uid 109); 16 Aug 2004 16:37:22 - Received: (qmail 9877 invoked from network); 16 Aug 2004 16:37:22 - Received: neutral (lists.mysql.com: local policy) X-Message-Info: 6sSXyD95QpX8hhZnhYoqefW/otoY+LQE Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm List-ID: mysql.mysql.com Precedence: bulk List-Help: mailto:[EMAIL PROTECTED] List-Unsubscribe: mailto:[EMAIL PROTECTED] List-Post: mailto:[EMAIL PROTECTED] List-Archive: http://lists.mysql.com/mysql/171030 Delivered-To: mailing list [EMAIL PROTECTED] Message-ID: [EMAIL PROTECTED] X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2627 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 In-Reply-To: [EMAIL PROTECTED] X-Virus-Checked: Checked Return-Path: [EMAIL PROTECTED] X-OriginalArrivalTime: 16 Aug 2004 16:39:52.0808 (UTC) FILETIME=[A7A10680:01C483AF] Hello, I'm trying to run a T-SQL query that will SUM a column of type INT. I'm, however, receiving the following error: Arithmetic overflow error converting expression to data type int. The four rows that its trying to sum have values of 3098082, 2488114, 316804, and 10657. What do I do? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Dont just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database Stats
prototype of PHP script
$db_stack=array(ip,user,pass,dbname,table,ip2,user2,pass2,dbname2,table2
,...);
$resultset=array();
foreach($db_stack as $k=$v){
/// query database and get results
$resultset[].=$portion_of_information;
}
foreach($resultset as $k=$v){
//append row and save as csv file
}
regards,
Khazret Sapenov
- Original Message -
From: David Mohorn [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, August 16, 2004 12:58 PM
Subject: Database Stats
I have about 25 databases that I need to run the same query against that
returns about 5 columns of information.
I need this information then published within an Excel spreedsheet.
What is the best way do handle this?
Currently, I'm running each query manually, copying/pasting the result
to Excel, and repeating...
I need to run this procedure every week, and it takes a considerable
amount of time.
--
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]
release all mysql locks
Is there any command I can issue to release ALL locks held by any/all transactions ? I know that restarting the server does this, but is there a way to do this without restarting ? thanks, Mayuran -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question, SELECT DISTINCT
On Mon, 16 Aug 2004 12:39:32 -0400, [EMAIL PROTECTED] said: Let me see if I can explain it a little betterIf you need to move all 3 columns to the new table but you only want *1* row where f2 and f3 have a unique combination of values, how do you want to choose *which* value of f1 to move over with that combination? Do you want the minimum value, the maximum value, or no value at all? Whoa, it's not that complicatedI want to text only f2 f3 for uniqueness, not f1 f2 f3. That's all. If I'm not making it clear - don't worry...it's not life or death. Thanks. ...snip... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question, SELECT DISTINCT
You were perfectly clear. We understand that you only want to test f2 and f3 for uniqueness. The question is, which of the possible values of f1 do you want to get. Do you see? For a particular unique f2, f3 combination, there may be multiple f1 values. How should we choose which one to put in the new table? That is what Shawn has asked twice, and you have not answered. Until you answer that, no one can provide a correct solution. Michael leegold wrote: On Mon, 16 Aug 2004 12:39:32 -0400, [EMAIL PROTECTED] said: Let me see if I can explain it a little betterIf you need to move all 3 columns to the new table but you only want *1* row where f2 and f3 have a unique combination of values, how do you want to choose *which* value of f1 to move over with that combination? Do you want the minimum value, the maximum value, or no value at all? Whoa, it's not that complicatedI want to test only f2 f3 for uniqueness, not f1 f2 f3. That's all. If I'm not making it clear - don't worry...it's not life or death. Thanks. ...snip... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OS X Embedded Server
I have written an application for MAC OS X.3 that interfaces with MySQL through the Client/Server approach. I would like to simplify the installation process of the application by using the embedded MySQL server instead. However, I am having trouble figuring out how to: 1. Include the MySQL embedded server in my application bundle 2. Initiate the embedded server from within my application's code 3. Change my existing queries to work with the embedded server Anyone have experience with this? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrading Mysql and Perl DBD
I now have MySQL 4.0.2 and MySQL 4.1.3 beta both installed and running on one machine. I want to be able to use the Perl DBI / DBD to connect to both servers. I understand that there have been some changes between the MySQL 4.0.X and 4.1.X series that may require differently compiled DBD's. So my question is, do i need to have 2 separate DBD versions, or will the newer one work for both MySQL servers. The old DBD does not work for the new server. This is demonstrated by the error message: DBI-connect(...) failed: Client does not support authentication protocol requested by server; consider upgrading MySQL client at connect_test.pl line 19 All the connect_test.pl tries to do is create a db connection to each server separately. Any insight would be greatly appreciated thanks much sean peters [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question, SELECT DISTINCT
On Mon, 16 Aug 2004 13:57:13 -0400, Michael Stassen [EMAIL PROTECTED] said: You were perfectly clear. We understand that you only want to test f2 and f3 for uniqueness. The question is, which of the possible values of f1 do you want to get. Do you see? For a particular unique f2, f3 combination, there may be multiple f1 values. How should we choose which one to put in the new table? Oh, I understand now, sorry. If I said it makes no difference then you'd ask what the heck I have f1 for in the first place...It actually doesn't make a difference. Maybe I should drop f1. f1 is an auto-increment int. so I imagine I'd want f1 re-incremented in numerical order to take the gaps out. Not exactly normalized (or normal:^), thanks. That is what Shawn has asked twice, and you have not answered. Until you answer that, no one can provide a correct solution. Michael leegold wrote: On Mon, 16 Aug 2004 12:39:32 -0400, [EMAIL PROTECTED] said: Let me see if I can explain it a little betterIf you need to move all 3 columns to the new table but you only want *1* row where f2 and f3 have a unique combination of values, how do you want to choose *which* value of f1 to move over with that combination? Do you want the minimum value, the maximum value, or no value at all? Whoa, it's not that complicatedI want to test only f2 f3 for uniqueness, not f1 f2 f3. That's all. If I'm not making it clear - don't worry...it's not life or death. Thanks. ...snip... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading Mysql and Perl DBD
This is covered in the manual http://dev.mysql.com/doc/mysql/en/Password_hashing.html: A 4.1 client can connect to a pre-4.1 server, because the client understands both the old and new password hashing mechanisms. So, if you build the Perl modules against the 4.1.x library, you should be able to connect to either server. Michael sean c peters wrote: I now have MySQL 4.0.2 and MySQL 4.1.3 beta both installed and running on one machine. I want to be able to use the Perl DBI / DBD to connect to both servers. I understand that there have been some changes between the MySQL 4.0.X and 4.1.X series that may require differently compiled DBD's. So my question is, do i need to have 2 separate DBD versions, or will the newer one work for both MySQL servers. The old DBD does not work for the new server. This is demonstrated by the error message: DBI-connect(...) failed: Client does not support authentication protocol requested by server; consider upgrading MySQL client at connect_test.pl line 19 All the connect_test.pl tries to do is create a db connection to each server separately. Any insight would be greatly appreciated thanks much sean peters [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question, SELECT DISTINCT
Then I'd suggest you declare f1 as an AUTO_INCREMENT column in the target table, leave it out of the SELECT, and let it auto-generate IDs. Something like this: INSERT INTO original_table (f2, f3) SELECT DISTINCT f2, f3 FROM new_table; I did that in the same order as your original message, though I'd have expected original_table and new_table to be swapped, based on their names. See the manual http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html for the details on INSERT...SELECT. Michael leegold wrote: On Mon, 16 Aug 2004 13:57:13 -0400, Michael Stassen [EMAIL PROTECTED] said: You were perfectly clear. We understand that you only want to test f2 and f3 for uniqueness. The question is, which of the possible values of f1 do you want to get. Do you see? For a particular unique f2, f3 combination, there may be multiple f1 values. How should we choose which one to put in the new table? Oh, I understand now, sorry. If I said it makes no difference then you'd ask what the heck I have f1 for in the first place...It actually doesn't make a difference. Maybe I should drop f1. f1 is an auto-increment int. so I imagine I'd want f1 re-incremented in numerical order to take the gaps out. Not exactly normalized (or normal:^), thanks. That is what Shawn has asked twice, and you have not answered. Until you answer that, no one can provide a correct solution. Michael leegold wrote: On Mon, 16 Aug 2004 12:39:32 -0400, [EMAIL PROTECTED] said: Let me see if I can explain it a little betterIf you need to move all 3 columns to the new table but you only want *1* row where f2 and f3 have a unique combination of values, how do you want to choose *which* value of f1 to move over with that combination? Do you want the minimum value, the maximum value, or no value at all? Whoa, it's not that complicatedI want to test only f2 f3 for uniqueness, not f1 f2 f3. That's all. If I'm not making it clear - don't worry...it's not life or death. Thanks. ...snip... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question, SELECT DISTINCT
On Mon, 16 Aug 2004 13:57:13 -0400, Michael Stassen [EMAIL PROTECTED] said: You were perfectly clear. We understand that you only want to test f2 and f3 for uniqueness. The question is, which of the possible values of f1 do you want to get. Do you see? For a particular unique f2, f3 combination, there may be multiple f1 values. How should we choose which one to put in the new table? Oh, I understand now, sorry. If I said it makes no difference then you'd ask what the heck I have f1 for in the first place...It actually doesn't make a difference. Maybe I should drop f1. f1 is an auto-increment int. so I imagine I'd want f1 re-incremented in numerical order to take the gaps out. Not exactly normalized (or normal:^), thanks. That is what Shawn has asked twice, and you have not answered. Until you answer that, no one can provide a correct solution. Michael leegold wrote: On Mon, 16 Aug 2004 12:39:32 -0400, [EMAIL PROTECTED] said: Let me see if I can explain it a little betterIf you need to move all 3 columns to the new table but you only want *1* row where f2 and f3 have a unique combination of values, how do you want to choose *which* value of f1 to move over with that combination? Do you want the minimum value, the maximum value, or no value at all? Whoa, it's not that complicatedI want to test only f2 f3 for uniqueness, not f1 f2 f3. That's all. If I'm not making it clear - don't worry...it's not life or death. Thanks. ...snip... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question, SELECT DISTINCT
Disregard by last message it's a repeat. THANKS for the help! On Mon, 16 Aug 2004 14:32:27 -0400, Michael Stassen [EMAIL PROTECTED] said: Then I'd suggest you declare f1 as an AUTO_INCREMENT column in the target table, leave it out of the SELECT, and let it auto-generate IDs. Something like this: INSERT INTO original_table (f2, f3) SELECT DISTINCT f2, f3 FROM new_table; I did that in the same order as your original message, though I'd have expected original_table and new_table to be swapped, based on their names. See the manual http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html for the details on INSERT...SELECT. Michael leegold wrote: On Mon, 16 Aug 2004 13:57:13 -0400, Michael Stassen [EMAIL PROTECTED] said: You were perfectly clear. We understand that you only want to test f2 and f3 for uniqueness. The question is, which of the possible values of f1 do you want to get. Do you see? For a particular unique f2, f3 combination, there may be multiple f1 values. How should we choose which one to put in the new table? Oh, I understand now, sorry. If I said it makes no difference then you'd ask what the heck I have f1 for in the first place...It actually doesn't make a difference. Maybe I should drop f1. f1 is an auto-increment int. so I imagine I'd want f1 re-incremented in numerical order to take the gaps out. Not exactly normalized (or normal:^), thanks. That is what Shawn has asked twice, and you have not answered. Until you answer that, no one can provide a correct solution. Michael leegold wrote: On Mon, 16 Aug 2004 12:39:32 -0400, [EMAIL PROTECTED] said: Let me see if I can explain it a little betterIf you need to move all 3 columns to the new table but you only want *1* row where f2 and f3 have a unique combination of values, how do you want to choose *which* value of f1 to move over with that combination? Do you want the minimum value, the maximum value, or no value at all? Whoa, it's not that complicatedI want to test only f2 f3 for uniqueness, not f1 f2 f3. That's all. If I'm not making it clear - don't worry...it's not life or death. Thanks. ...snip... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database Stats
25 databases with the SAME query? Sounds like the database structure are pretty similar. Perhaps you want to look into merge tables or unions. On Aug 16, 2004, at 12:58 PM, David Mohorn wrote: I have about 25 databases that I need to run the same query against that returns about 5 columns of information. I need this information then published within an Excel spreedsheet. What is the best way do handle this? Currently, I'm running each query manually, copying/pasting the result to Excel, and repeating... I need to run this procedure every week, and it takes a considerable amount of time. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb_log_archive : should I activate it?
Hello I have looked in the archive of the mailing list, to see if there was any discussion on that, but I have not seen any message. I just install mysql 4.3.1 and I am wondering if I should archive the logfiles. In the documentation it is said about innodb_log_archive : This value should currently be set to 0. As recovery from a backup is done by MySQL using its own log files, there is currently no need to archive InnoDB log files. The default value of this parameter is 0 . I have 2 log files. Usually I prefer to use archiving (at least I do it in Oracle) to be sure that records in the logfiles are not overwriten. I know that the size of the logfile should prevent this but still I would prefer. I am wondering why it is said that there is not need. Also I have looked for information about the checkpoint. Is it a time dependant checkpoint or a size dependant. Can we modify the time or the size? Thank you in advance for your help Johanne Duhaime courrier: [EMAIL PROTECTED]
Why VARCHAR TO CHAR automatically when the length less than 4.
Hello all, I have a questions about varchar columns change to CHAR columns automatically. When I designed a table t1 create table t1(id varchar(3) not null) in MySQL. After that, we use desc t1 to see the description of table t1. The result is the following: === +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | id| char(3) | YES | | NULL| | +---+-+--+-+-+---+ I did not understand why the varchar(3) is changed to char(3) automatically. While if we define a varchar(4) it will not change to char(4) automatically. Could somebody help and explain that please? Thanks a lot! Emi Lu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql 4.0.20(binary) Relay Log Purging not working
We just upgraded to MySQL 4.0.20 (binary), and we use replication to a
slave running the same. We're having some difficulty deleting the relay
log files the slave creates. I'm fairly confused, as the manual clearly
says we shouldn't be having this problem:
According to the MySQL manual:
Relay logs have the same format as binary logs, so you can use
mysqlbinlog to read them. A relay log is automatically deleted by the
SQL thread as soon as it has executed all its events and no longer needs
it). There is no explicit mechanism for deleting relay logs, because the
SQL thread takes care of doing so. However, from MySQL 4.0.14, FLUSH
LOGS rotates relay logs, which will influence when the SQL thread
deletes them.
So, I've run Flush Logs on the slave, and it's created the next log file
in the sequence. But the old ones still stay around indefinitely. Even
after issuing stop slave/start slave. I cannot find a command similar
to PURGE MASTER LOGS for the slave to purge logs on the slave. Show
slave status shows the slave Relay_log_space increasing steadily even
after rotation. The Exec_master_log_pos is in sync with the master,
meaning the old relay files are nolonger needed.
There is a feature in 4.1.1:
--relay-log-purge={0|1}
Disables or enables automatic purging of relay logs as soon as they are
not needed any more. The default value is 1 (enabled). This is a global
variable that can be changed dynamically with SET GLOBAL
relay_log_purge. This option is available as of MySQL 4.1.1.
Does that mean that log purging doesn't exist until 4.1.1, or that the
'option to turn it off' isn't available until 4.1.1? If log purging
isn't available until the next major mysql release, how can I delete
these log files now? Even if I delete them off disk, they are still
referenced in the 'relay-bin.index' file. Presumably I'm not allowed to
update that file on a live slave...
Ideas?
-Joe
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Which ports
This has to be the top lazy question of the year. Do you know google.com or mysql.com ? Like google.com: Mysql port _ Steve Poirier -Original Message- From: Darryl Hoar [mailto:[EMAIL PROTECTED] Sent: August 16, 2004 1:02 PM To: [EMAIL PROTECTED] Subject: Which ports Greetings, I have Mysql installed on my freebsd box. I want to be able to access the database from the internet, but I need to allow the ports used in my firewall. What ports does mysql use ? thanks, Darryl -- 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]
GROUP BY
My original concept to use group by was
GROUP BY(MajorColumnGroupIng...MinorColumnGrouping)
I am trying to be convinced that
GROUP BY(Minor..Major) will work just as well
Is there a difference???
thanks,
Martin Gainty
From: Joe Kislo [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Mysql 4.0.20(binary) Relay Log Purging not working
Date: 16 Aug 2004 16:27:31 -0400
MIME-Version: 1.0
Received: from lists.mysql.com ([213.136.52.31]) by mc2-f12.hotmail.com
with Microsoft SMTPSVC(5.0.2195.6824); Mon, 16 Aug 2004 13:30:32 -0700
Received: (qmail 774 invoked by uid 109); 16 Aug 2004 20:27:44 -
Received: (qmail 751 invoked from network); 16 Aug 2004 20:27:38 -
Received: pass (lists.mysql.com: local policy)
X-Message-Info: JGTYoYF78jHFrEJMsdUGZJhqJ7wpDudt
Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm
List-ID: mysql.mysql.com
Precedence: bulk
List-Help: mailto:[EMAIL PROTECTED]
List-Unsubscribe:
mailto:[EMAIL PROTECTED]
List-Post: mailto:[EMAIL PROTECTED]
List-Archive: http://lists.mysql.com/mysql/171053
Delivered-To: mailing list [EMAIL PROTECTED]
X-Mailer: Ximian Evolution 1.0.5 Message-Id:
[EMAIL PROTECTED]
X-Virus-Checked: Checked
Return-Path: [EMAIL PROTECTED]
X-OriginalArrivalTime: 16 Aug 2004 20:30:32.0285 (UTC)
FILETIME=[E099A0D0:01C483CF]
We just upgraded to MySQL 4.0.20 (binary), and we use replication to a
slave running the same. We're having some difficulty deleting the relay
log files the slave creates. I'm fairly confused, as the manual clearly
says we shouldn't be having this problem:
According to the MySQL manual:
Relay logs have the same format as binary logs, so you can use
mysqlbinlog to read them. A relay log is automatically deleted by the
SQL thread as soon as it has executed all its events and no longer needs
it). There is no explicit mechanism for deleting relay logs, because the
SQL thread takes care of doing so. However, from MySQL 4.0.14, FLUSH
LOGS rotates relay logs, which will influence when the SQL thread
deletes them.
So, I've run Flush Logs on the slave, and it's created the next log file
in the sequence. But the old ones still stay around indefinitely. Even
after issuing stop slave/start slave. I cannot find a command similar
to PURGE MASTER LOGS for the slave to purge logs on the slave. Show
slave status shows the slave Relay_log_space increasing steadily even
after rotation. The Exec_master_log_pos is in sync with the master,
meaning the old relay files are nolonger needed.
There is a feature in 4.1.1:
--relay-log-purge={0|1}
Disables or enables automatic purging of relay logs as soon as they are
not needed any more. The default value is 1 (enabled). This is a global
variable that can be changed dynamically with SET GLOBAL
relay_log_purge. This option is available as of MySQL 4.1.1.
Does that mean that log purging doesn't exist until 4.1.1, or that the
'option to turn it off' isn't available until 4.1.1? If log purging
isn't available until the next major mysql release, how can I delete
these log files now? Even if I delete them off disk, they are still
referenced in the 'relay-bin.index' file. Presumably I'm not allowed to
update that file on a live slave...
Ideas?
-Joe
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
_
Is your PC infected? Get a FREE online computer virus scan from McAfee®
Security. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Which ports
To all of those that provided the information, thank you. Next time I have a question, I will do the research before bothering the list. -Darryl -Original Message- From: Steve Poirier [mailto:[EMAIL PROTECTED] Sent: Monday, August 16, 2004 3:36 PM To: [EMAIL PROTECTED] Subject: RE: Which ports This has to be the top lazy question of the year. Do you know google.com or mysql.com ? Like google.com: Mysql port _ Steve Poirier -Original Message- From: Darryl Hoar [mailto:[EMAIL PROTECTED] Sent: August 16, 2004 1:02 PM To: [EMAIL PROTECTED] Subject: Which ports Greetings, I have Mysql installed on my freebsd box. I want to be able to access the database from the internet, but I need to allow the ports used in my firewall. What ports does mysql use ? thanks, Darryl -- 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]
Query formation / index optimization?
Hello, I am trying to create a query for the following scenario, that doesn't take ages and ages to return. Let's assume the following columns in an events table: user_name, event_type, event_time. I want to select a count of all users which have more than 1 (2 or more) entries in the event table that match event_type 'abc' or 'def' (but not anything else). I also need to say before an arbitrary time. I have used PostgreSQL quite a lot, but I'm not sure how to most efficiently form the query on MySQL. The other question is about index usage. There is currently an index (which I see via show indexes from events) on the user_name column, but it is not being used when I type in a query using that column (i.e. select count(*) from events where event = 'abc' group by user_name). How do I describe just one index in MySQL to see the full details? What is the best way to form this query (I am used to using PostgreSQL and subselects)? What indexes would be the most beneficial to the query suggestion? Thank you for your time, any response appreciated. -- Casey Allen Shobe | http://casey.allen.shobe.info [EMAIL PROTECTED] | ICQ: 1494523 | AIM: SomeLinuxGuy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with xalan and mysql in same application (long list of er rors)
Hi everyone,
I am working on an API wrapper that will use both MySQL and the Xalan xml
api.
My versions are:
Xalan-C_1_8_0-windows_2000-msvc_60.zip (available at xml.apache.org)
xerces-c_2_5_0-windows_nt-msvc_60.zip (available at xml.apache.org)
mysql 4.0.20
windows 2000 professional service pack 4
microsoft visual c++ 6.0
The problem occurs when I include the mysql headers before the xalan
headers. It is easy enough to reverse them, but since I am working on an API
I cannot guarantee that my user will not include the mysql api before they
include my wrapper api. Can anyone help? Below I am listing a code snippet
that will compile, one that will not compile, and the errors that are
produced from the bad code snippet.
# The following code compiles with no problems #
#include xalanc/XMLSupport/XMLSupportDefinitions.hpp
#include xalanc/XMLSupport/FormatterToXML.hpp
#include my_global.h
#include mysql.h
int main(int argc, char *argv[])
{
return 0;
}
###
# The following code will not compile #
###
#include my_global.h
#include mysql.h
#include xalanc/XMLSupport/XMLSupportDefinitions.hpp
#include xalanc/XMLSupport/FormatterToXML.hpp
int main(int argc, char *argv[])
{
return 0;
}
##
# The following errors are produced by the 2nd set of code above #
##
Configuration: TestXML - Win32 Debug
Compiling... TestXML.cpp c:\program files\microsoft visual
studio\vc98\include\xutility(99) : error C2059: syntax error : 'end Parse'
c:\program files\microsoft visual studio\vc98\include\xutility(100) : error
C2143: syntax error : missing ';' before '{' c:\program files\microsoft
visual studio\vc98\include\xutility(100) : error C2447: missing function
header (old-style formal list?) c:\program files\microsoft visual
studio\vc98\include\xstring(19) : error C2954: template definitions cannot
nest c:\program files\microsoft visual studio\vc98\include\xstring(41) :
error C2065: '_Al' : undeclared identifier
c:\program files\microsoft visual studio\vc98\include\xstring(597) : see
reference to class template instantiation 'std::basic_string_E,_Tr,_A'
being compiled c:\program files\microsoft visual
studio\vc98\include\xstring(41) : error C2440: 'type cast' : cannot convert
from 'int' to 'class std::allocator'
The target type has no constructors
c:\program files\microsoft visual studio\vc98\include\xstring(597) : see
reference to class template instantiation 'std::basic_string_E,_Tr,_A'
being compiled c:\program files\microsoft visual
studio\vc98\include\xstring(41) : error C2056: illegal expression
c:\program files\microsoft visual studio\vc98\include\xstring(597) : see
reference to class template instantiation 'std::basic_string_E,_Tr,_A'
being compiled c:\program files\microsoft visual
studio\vc98\include\xstring(41) : error C2071:
'basic_string_E,_Tr,_A::basic_string_E,_Tr,_A' : illegal storage class
c:\program files\microsoft visual studio\vc98\include\xstring(597) : see
reference to class template instantiation 'std::basic_string_E,_Tr,_A'
being compiled c:\program files\microsoft visual
studio\vc98\include\xstring(41) : error C2461: 'basic_string_E,_Tr,_A' :
constructor syntax missing formal parameters
c:\program files\microsoft visual studio\vc98\include\xstring(597) : see
reference to class template instantiation 'std::basic_string_E,_Tr,_A'
being compiled c:\program files\microsoft visual
studio\vc98\include\xstring(42) : error C2146: syntax error : missing ';'
before identifier 'basic_string'
c:\program files\microsoft visual studio\vc98\include\xstring(597) : see
reference to class template instantiation 'std::basic_string_E,_Tr,_A'
being compiled c:\program files\microsoft visual
studio\vc98\include\xstring(600) : error C2039: 'npos' : is not a member of
'basic_string_E,_Tr,_A' c:\program files\microsoft visual
studio\vc98\include\xstring(600) : error C2734: 'npos' : const object must
be initialized if not extern c:\program files\microsoft visual
studio\vc98\include\xstring(600) : error C2734: 'npos' : const object must
be initialized if not extern c:\program files\microsoft visual
studio\vc98\include\xstring(371) : error C2059: syntax error : '{'
c:\program files\microsoft visual studio\vc98\include\stdexcept(29) : see
reference to class template instantiation 'std::basic_stringchar,struct
std::char_traitschar,class std::allocatorchar ' being compiled
c:\program files\microsoft visual studio\vc98\include\xstring(371) : error
C2334: unexpected token(s) preceding '{'; skipping apparent function body
c:\program files\microsoft visual studio\vc98\include\stdexcept(29) : see
reference to class template instantiation 'std::basic_stringchar,struct
std::char_traitschar,class
Re: MySQL eats 100% CPU on a quad CPU system...
Are you swapping? Check vmstat and iostat Are your queries optimized, are they doing full table scans? Enable the slow query log. On Mon, 16 Aug 2004 14:48:35 +0200, Fahr o Mavastradioz [EMAIL PROTECTED] wrote: Hello people, I'm currently configuring a quad CPU system as a standalone MySQL server. The machine runs nothing except for MySQL and some trivial things like SSH and iptables. A second server acts as a webserver and is connected to this one though an internal LAN connection. Using top or ps, it shows that MySQL eats over 99% of all the available CPU. Using slmon, it shows that indeed all 4 CPUs are fully in use. Still, it appears to be terribly slow and I don't think MySQL should eat over 99% CPU on a quad CPU system... The machine runs mysqld 4.0.18 on Debian Sarge. A stripped down my.cnf is included below (I merely removed the comments). Could anyone tell me what can cause this problem? Thanks, - Fahr ===my.cnf=== [client] port= 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] err-log = /var/log/mysql/mysql.err socket = /var/run/mysqld/mysqld.sock open_files_limit = 8192 [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language= /usr/share/mysql/english skip-locking thread_stack= 128K skip-innodb max_connections = 500 key_buffer = 150M myisam_sort_buffer_size = 64M join_buffer_size = 1M read_buffer_size = 1M sort_buffer_size = 1M table_cache = 1500 thread_cache_size = 128 wait_timeout = 14400 connect_timeout = 10 max_allowed_packet = 16M max_connect_errors = 10 query_cache_limit = 1M query_cache_size = 32M query_cache_type = 1 [mysqldump] quick max_allowed_packet = 16M [mysql] [isamchk] key_buffer = 64M sort_buffer = 64M read_buffer = 16M write_buffer = 16M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL eats 100% CPU on a quad CPU system...
I use memlock to ensure it stays in memory. More than likely it's table optimization. EXPLAIN and SHOW INDEXES will likely help you once you turn slow query logging on. -J -Original Message- From: Justin Swanhart [mailto:[EMAIL PROTECTED] Sent: Monday, August 16, 2004 3:24 PM To: [EMAIL PROTECTED] Subject: Re: MySQL eats 100% CPU on a quad CPU system... Are you swapping? Check vmstat and iostat Are your queries optimized, are they doing full table scans? Enable the slow query log. On Mon, 16 Aug 2004 14:48:35 +0200, Fahr o Mavastradioz [EMAIL PROTECTED] wrote: Hello people, I'm currently configuring a quad CPU system as a standalone MySQL server. The machine runs nothing except for MySQL and some trivial things like SSH and iptables. A second server acts as a webserver and is connected to this one though an internal LAN connection. Using top or ps, it shows that MySQL eats over 99% of all the available CPU. Using slmon, it shows that indeed all 4 CPUs are fully in use. Still, it appears to be terribly slow and I don't think MySQL should eat over 99% CPU on a quad CPU system... The machine runs mysqld 4.0.18 on Debian Sarge. A stripped down my.cnf is included below (I merely removed the comments). Could anyone tell me what can cause this problem? Thanks, - Fahr ===my.cnf=== [client] port= 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] err-log = /var/log/mysql/mysql.err socket = /var/run/mysqld/mysqld.sock open_files_limit = 8192 [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language= /usr/share/mysql/english skip-locking thread_stack= 128K skip-innodb max_connections = 500 key_buffer = 150M myisam_sort_buffer_size = 64M join_buffer_size = 1M read_buffer_size = 1M sort_buffer_size = 1M table_cache = 1500 thread_cache_size = 128 wait_timeout = 14400 connect_timeout = 10 max_allowed_packet = 16M max_connect_errors = 10 query_cache_limit = 1M query_cache_size = 32M query_cache_type = 1 [mysqldump] quick max_allowed_packet = 16M [mysql] [isamchk] key_buffer = 64M sort_buffer = 64M read_buffer = 16M write_buffer = 16M -- 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: Why VARCHAR TO CHAR automatically when the length less th an 4.
This is described here: http://dev.mysql.com/doc/mysql/en/Silent_column_changes.html. The table is created as a fixed table format. -Original Message- From: Emi Lu To: [EMAIL PROTECTED] Sent: 8/16/04 3:20 PM Subject: Why VARCHAR TO CHAR automatically when the length less than 4. Hello all, I have a questions about varchar columns change to CHAR columns automatically. When I designed a table t1 create table t1(id varchar(3) not null) in MySQL. After that, we use desc t1 to see the description of table t1. The result is the following: === +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | id| char(3) | YES | | NULL| | +---+-+--+-+-+---+ I did not understand why the varchar(3) is changed to char(3) automatically. While if we define a varchar(4) it will not change to char(4) automatically. Could somebody help and explain that please? Thanks a lot! Emi Lu -- 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]
Full text for keyword(s) search?
If I want to search for a word (or multiple words) in a field on one table, searching through at least a thousand records (and growing daily), should I be using full text or something else to make this search faster? I'm using MySQL 3.23.58. Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Kernel panic when mysql stop command issued
I know that 2.6.7 works just fine with MySQL 4.0.20. I don't use Max though. Donny -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Monday, August 16, 2004 12:11 AM To: Demetrios Stavrinos Cc: [EMAIL PROTECTED] Subject: Re: Kernel panic when mysql stop command issued On Sat, Aug 14, 2004 at 03:01:06PM -0700, Demetrios Stavrinos wrote: Kernel panic: Fatal exception in interrupt...In Interrupt handler - not syncing message appears when the mysql-max stop is issued. Other than that everything works. I changed hardware (everything new) and re-installed Linux and MySQL and upgraded to latest 2.6.3 from mdk (It was happening with the previous 2.6.3 also). Problem is repeatable 4 out of 5 tries. Linux 2.6.3-15mdkenterprise #1 SMP Fri Jul 2 20:07:05 mysql MySQL-Max-4.0.20-3mdk. Has any one heard or seen anything like it? Try a different kernel. If MySQL is able to screw with the kernel, it's a kernel bug--or a weird hardware problem manifesting itself as one. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.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]
Weird behavior on varchar fields
Hi, I have one table that is presenting strange behaviour, we set the varchar field for not allow nulls so, on 5% of the records the application is inserting blanks but when we select the value and we set the condition = it returns the same asand. Even, we tried to set the field into the primary key to try to correct this, but is the same. We double check on the application and we have a lot of validation code to avoid this problem. Does anyone have any clue about it? Best Regards Alejandro
Migration tools/plan from oracle 7.3 to Mysql 4.0
Hi, I am new to MySql. Could you please let me know the different migration tools avialable for the migration of oracle 7.3 database to mysql 4.0 classic version. Out of those which one is the best in performance and cost wise. As per my knowledge views, stored procedures and triggers are not supported in MySql 4.0. Is it correct?. If so, what are all the alternatives for converting the views, stored procedures and triggers in oracle 7.3 to MySql 4.0. I will be very happy for giving me a very good explanation on this. Thanks, Narasimha -Original Message- From: Bob Afifi [mailto:[EMAIL PROTECTED] Sent: Mon 8/16/2004 10:13 PM To: [EMAIL PROTECTED] Cc: Subject: Update MySQL row using URL link? I currently update MySQL rows using phpMyAdmin. For example, dropping the following into the phpMyAdmin GUI: UPDATE mysql_db SET publish = 1 WHERE Date = 'Sunday, August 15, 2004 21:04:32' Since I get the update info in an e-mail send whenever the form is submitted, I'd like to turn querys like the above encoded into a URL - bypassing phpMyAdmin - which when clicked, will update the row. I found this article last night: Make SQL Queries over HTTP with XML with VS.NET (http://www.aspfree.com/c/a/ASP/Make-SQL-Queries-over-HTTP-with-XML-with-VSNET/ ) SELECT CustomerId, CompanyName FROM Customer http://localhost/sql?sql=select%20CustomerId,%20CompanyName%20from%20Customers%20FOR%20XML%20AUTO = The above looks very much like what I have in mind, but for MySQL. Anybody know how to do this? Many thanks in advance, -Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments.
