timezones in 4.1/5.0
Hi, I read Trudy Pelzer's article on Temporal Functionality in MySQL 4.1. http://dev.mysql.com/tech-resources/articles/4.1/time.html A few open questions to time zone internals remain: (1) How are DATETIME and TIMESTAMP columns saved internally? I would guess, UTC. One could also interpret the 4.1.3 changelog this way. http://dev.mysql.com/doc/mysql/en/News-4.1.3.html That is, values stored in such a column are normalized towards UTC and converted back to the current connection time zone when they are retrieved from such a column. But I found no way to make this really sure. (2) If DATETIME/TIMESTAMP values are internally saved UTC, what does mysqldump do with these values? Convert it to the timezone mysqldump is executed? To allow database migration between different timezones, it might be safer if mysqldump would deliver UTC times (or if there were at least another option for this). (3) With system, server and client timezones: How does the client know in which timezone it is running? How does it report this information to the MySQL Server? Has the C API a function to set the timezone? (I found none.) I made a small experiment: - My MySQL server (5.0.2) runs on Linux, system_time_zone=CET, global.time_zone=SYSTEM. - As a client, I used Windows 2000 set to time zone GMT-5 (New York) - Now I used mysql.exe (from MySQL 5.0.2, time zone set to New York) to connect to the MySQL Server (Linux, time zone CET). The time zone information form the client was not reported to the server. @@session.time_zone is SYSTEM (obviously wrong). This means: The time zone is not reported automatically, at least not in all cases. Thanks to all who can provide more insight into these topics! Michael Kofler, author of The Definitive Guide to MySQL (apress) http://kofler.cc/mysql/mysqlbook.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB status: why do I see MyISAM requests?
Frank, - Original Message - From: Frank Denis (Jedi/Sector One) [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, December 13, 2004 12:25 AM Subject: InnoDB status: why do I see MyISAM requests? Just curious... While running SHOW INNODB STATUS, the list of transactions for each sessions part shows queries that are only related to MyISAM tables. Is it the expected behavior? yes, it prints thd-query_str which is the latest query on the connection. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Create Trigger...
Hi, I have problem in using create trigger in MySQL 4.1. I had read through the MySQL manual and found that create trigger may not be supported by MySQL 4.1. Here's the question, how should I do to make MySQL 4.1 to do the same thing as create trigger? Thank you! Regards, Alexander Chai - Win a castle for NYE with your mates and Yahoo! Messenger
Building C string for Insert!!
Hi, I am building the insert string for inserting into particular table t1 using C program. The below string is stored in a variable. say sqlstmt = 'INSERT INTo T1 values(:id,:ename);' The values for the field's id, name will come from the front end. The sql string will be executed using Pro *C in Oracle. Can we get the MySQL Equivalent so that we can use the same in our C program? Do we have bind variable or host variable concepts in MySQL? In MySQL tried with @, i.e set the values of id, ename as @id = 10, @ename = 'Sample' after that used Insert into t1 values(@id,@ename). This worked fine. Can we use the same @ for the binding values in C' Program for mysql?. Thanks for help in advance. Regards, Narasimha 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.
Re: More Illegal mix of collations trouble.
Hello. Put in you [client] section of my.cnf character_set=latin1 [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Last week we upgraded from mysql 4.0 to 4.1.7. Since then some queries on newly created tables (ie created since the upgrade) are failing with the error: #1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' I have been doing a lot of reading. (Until this problem I had never heard of a character set collation.) It looks like some database connections are specifying UTF-8 and over-ridding the default global setting of latin1. See the mysql command line tool log below to see that the variables don't match the global variables. Where do I look to see what is causing this? The /etc/my.cnf already contains this line in the [mysqld] section: default-character-set=latin1 The error message occurs both when I use the mysql command line tool from the local host (mysql Ver 14.7 Distrib 4.1.7, for pc-linux (i686)) and also from PHP on a different server (phpinfo() reports Client API version 3.23.56). When I add the following line to /etc/my.cnf on the same machine as the server runs on and connect with the command line tool I don't have the same problem but this does not fix the connections from the PHP client. (Added to the [mysql] section.) default-character-set=latin1 I don't understand how or why the default connection data encoding would be different from the default table encoding. Maybe someone can explain this to me as well. By using SET CHARACTER SET latin1; in the command line tool the problem goes away but it's silly to have to tell all my clients they have to explicitly set the character set every time they connect. What settings should be adjusted to prevent the errors from occuring? Thanks, /Chad mysql SELECT SUM( Credits ) - FROM Acct_Payments - WHERE Void = 'No'; ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' mysql show variables like 'char%'; + -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Truncating trailing blanks of a constant
Hello. Yes, may be LIKE operator should check the type of column and remove trailing spaces from the comparsion varchar-string, but in TEXT columns trailing spaces are allowed. Thomas Spahni [EMAIL PROTECTED] wrote: Hi everyone, recently I encountered the following problem: SELECT COUNT(id) FROM sometable WHERE somevarchar LIKE 'thistext '; returned 0 (of course!) because trailing blanks can't exist in a column of type VARCHAR. But: Shouldn't the constant be truncated automatically in this context before the comparison is made? I can certainly do it in my application but I think that it would be a consistent behaviour if MySQL would do it. Any opinions from the list? Thomas Spahni -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Repeated repair table ... quick takes same amount of time (long) under 4.1.7
Hello REPAIR QUICK is a recommended method to rebuild FULLTEXT indexes. See: http://dev.mysql.com/doc/mysql/en/Fulltext_Fine-tuning.html Haitao Jiang [EMAIL PROTECTED] wrote: Hi, If I remember correctly, if table is ok, then if one does a table repair, the command should return immediately. I have a table with 14 million rows, and I repeated table repair (quick) 3 times, it took the same amount of time (55 min). It is almost the same time as if I built the index from scratch. Any idea? The table seems ok for query as I checked. This is under MySQL 4.1.7, MyISAM table with full text indexes. Thanks Haitao -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem using debug switch with mysqlimport
Hello. I've submitted a bug: http://bugs.mysql.com/7137 Sure enough... I just installed the latest 4.1 linux binaries, I didn't realize that the server itself had to be compiled with the debug enabled (although now that I realize that it makes complete sense). It would be nice if the documentation for mysqlimport would at least make note of this. Thanks for the response.Settles, Aaron [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL technical issue
Hello. May be you should think about commercial support: https://order.mysql.com/?ref=ensita Dear Sirs, Our company is developing software for SetTopBox STI 5528. We are interested in using your DBMS. Our target platform is STI5528 (SH4 (Hitachi) compliant) platform. OS Linux: kernel 2.4.24 Compiler: gcc 3.03 Our attempts to install 4th or 5th version of MySQL on our platform end in a failure. Whatever table we try to create we have an error. Please find the detailed log attached. With a standard server running on Linux RedHat 9 X86 these operations are completed successfully. We kindly ask you to give us your recommendations regarding this problem and, if possible, advise possible solutions to the problem to our team of programmers. We are looking forward to your reply. Kindest regards, Artem Dikov, Programming Department Chief Ricor Holding 105120 Russia, Moscow Kostomarovsky pereulok, 3 Tel: 007 095 363 45 50 Fax: 007 095 363 45 60 Email:[EMAIL PROTECTED] Site: www.ricor.ru Attachment: [text/html] --=_NextPart_000_0021_01C4DE0C.BD623400 Content-Type: text/plain; name=mysql_bug_report.txt Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename=mysql_bug_report.txt =0A= Marika Matveeva [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: copy2diffdatabase
Hello. See: http://dev.mysql.com/doc/mysql/en/Backup.html To backup one database to another on the same MySQL instance you may FLUSH TABLES WITH READ LOCK and use CREATE TABLE ... SELECT ... to create tables in the second database from tables in the first one. Don't forget to UNLOCK TABLES! N. Kavithashree [EMAIL PROTECTED] wrote: hello, i hv a database ONE with some 10 tables; i want to have a backup so how can i copy these tales to another database in the same server and also to any other server? N. Kavithashree === -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Porting Tables Between Windows and Linux Version of MySQL
Hello. Before doing anything you should read: http://dev.mysql.com/doc/mysql/en/Windows_vs_Unix.html http://dev.mysql.com/doc/mysql/en/Name_case_sensitivity.html lan Scott - Yahoo Acct wrote: We have 2 distinct groups of developers - Windows vs. Linux. Our production server is going to run on Solaris. The issue is that all developers run there own instance of MySQL server Linux or Windows based and the same version 4.1.7. Also, each developer has the database for their particular project so everything from their MySQL server instance would be ported. As we move the systems to the Solaris production server is there any simple way to port all the work onto this platform (backup/restore, etc.)? Any help or weblink, reference material recommendations are appreciated. Alan Scott - Yahoo Acct [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help Required!
Hi, I am building the insert string for inserting into particular table t1 using C program. The below string is stored in a variable. say sqlstmt = 'INSERT INTo T1 values(:id,:ename);' The values for the field's id, name will come from the front end. The sql string will be executed using Pro *C in Oracle. Pro *C can get the values of Id and ename fields which are initialized in the C program and can execute the sql statement in Oracle database. The record got inserted successfully. Can we get the MySQL Equivalent so that we can use the same in our C program? Do we have bind variable or host variable concepts in MySQL? In MySQL prompt we tried with @, i.e. set the values of id, ename as set @id = 10, @ename = 'Sample'; Next we used the below command and it worked fine. Insert into t1 values(@id,@ename). Can we use the same @ for the binding values in C' Program for mysql so that it substitutes the value of fields? Thanks for help in advance. Please help. Regards, Narasimha 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
logs: is there a possibility to stop/start them without stopping server
Hi! I wonder if there is any possibility to switch log off on running server? Usually I have here general logs disabled due to performance and space saving reasons - but of course sometimes it is needed to find out full info about queries executed. It'd be fine to have possibility to run server with log and stopping/starting logging on demand. TIA Remigiusz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: where is my my.cnf files??
Hello. You should use my-xxx files as examples for creating your own. --defaults-file=path_to_file points mysqld to location of config file. Also see: http://dev.mysql.com/doc/mysql/en/Option_files.html Hiu Yen Onn [EMAIL PROTECTED] wrote: hi, i compiled mysql-4.1.7 from source. actually, i want to configure a mysql cluster. from the documentation, i need to add some flag into a file called my.cnf. i searched through the files. it consisted of my-small.cnf, my-medium.cnf, my-huge.cnf. but, i cant see the file my.cnf file. where does it located pls enlighten me..thanks -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Building C string for Insert!!
Hello. I don't know if it is exactly what you want, but look at this: http://dev.mysql.com/doc/mysql/en/SQLPS.html http://dev.mysql.com/doc/mysql/en/C_API_Prepared_statements.html I am building the insert string for inserting into particular table t1 using C program. The below string is stored in a variable. say sqlstmt = 'INSERT INTo T1 values(:id,:ename);' The values for the field's id, name will come from the front end. The sql string will be executed using Pro *C in Oracle. Can we get the MySQL Equivalent so that we can use the same in our C program? Do we have bind variable or host variable concepts in MySQL? Thanks for help in advance. Regards, Narasimha [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY sorting
Mike, Try select * from foo order by x+0, x; x+0 converts x to an integer by taking the digits from the beginning of the string. == original message follows == Date: Sat, 11 Dec 2004 15:36:34 -0600 From: Mike Blezien [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Subject: ORDER BY sorting Hello, I'm trying to figure out how sort a column alphabetically, but some of the values start with a numerical value. So when I do the ORDER BY column_name all the values starting alphabeticlly are listed first, then the numerical values after that.. can this be done in a single query.. MySQL ver. 4.0.20 TIA, -- Mike(mickalo)Blezien -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how escape special in a field
Hello. See: http://dev.mysql.com/doc/mysql/en/mysql_real_escape_string.html Can you send complete test for your problem? YW CHAN (Cai Lun e-Business) [EMAIL PROTECTED] wrote: Hi, I find there seems problem with this select statement when there is a special character inside the table. i.e. select concat(field_1, ',', field_2) as name where ... field_2 actually is something like 'George, Banson' ( with a comma in between ) i guess this , destroy the SQL syntax. Is there any function for protecting this situation? Thanks for your help. Regards, CHAN -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create Trigger...
The statement Create Trigger was added in MySQL 5.0.2. Hi, I have problem in using create trigger in MySQL 4.1. I had read through the MySQL manual and found that create trigger may not be supported by MySQL 4.1. Here's the question, how should I do to make MySQL 4.1 to do the same thing as create trigger? Thank you! It depends which your trigger does. If your trigger statements, you can add the necessary sql statements to simulate this. Regards, Alexander Chai Ivan Cachicatari http://www.latindevelopers.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Restore help! been going 2 weeks
Need help with mysql restore speed Table crashed, had to restore from backup, I started the restore 2 weeks ago, the last change date on the files is the 8th, so mysql has not wrote data into the files sense then, but it's still running! (or is it?) the data restore is pretty quick, the index restore is VERY slow, if it even functions how does everyone deal with this issue, or is this not an issue for anyone else? Here's what I use to backup mysqldump --opt finlog 321st_stat /intranet/backup/321st_stat.sql using this to restore mysql -f 321st_stat.sql Server is a dual 2.8 xeon with 1gig of ram, the only job running is the restore windows 2000, mysql 4.1.7, no options set in the ini file table has aproximatly 80 million records CREATE TABLE `321st_stat` ( `dic` char(3) NOT NULL default '', `fr_ric` char(3) NOT NULL default '', `niin` char(11) NOT NULL default '', `ui` char(2) NOT NULL default '', `qty` char(5) NOT NULL default '', `don` char(14) NOT NULL default '', `suf` char(1) NOT NULL default '', `dte_txn` char(5) NOT NULL default '', `ship_to` char(3) NOT NULL default '', `sta` char(2) NOT NULL default '', `lst_sos` char(3) NOT NULL default '', `esd` char(4) NOT NULL default '', `stor` char(3) NOT NULL default '', `d_t` char(4) NOT NULL default '', `ctasc` char(10) NOT NULL default '', PRIMARY KEY (`dic`,`niin`,`fr_ric`,`don`,`suf`,`dte_txn`,`sta`), KEY `don` (`don`), KEY `niin` (`niin`), KEY `stor` (`stor`), KEY `dic` (`dic`), KEY `ctasc` (`ctasc`) ) ENGINE=MyISAM PACK_KEYS=1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: User variables
[snip] This does not work. It appears that the user variable is not picked up in the WHERE clause - the query works fine if I have: [/snip] You have a fundamental lack of understanding of user variables. A quick read of http://dev.mysql.com/doc/mysql/en/Variables.html will reveal User variables may be used where expressions are allowed. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DateTime ISO format not accepted in Myql 4.1.7
hi all, I've just upgraded one of the replicate servers from 4.0.22 to 4.1.7, just as a test before upgrading all the others. Apparently I had just this problem: datetime data inserted in ISO format is not accepted -- strings like '20041210T104201' when inserted get silently translated to -00-00 00:00. One of my tables uses this as a primary key, so I only got the error when it complained about duplicate keys. For instance: mysql INSERT INTO core (timestamp, version, value) VALUES (20041210T104201, 10, 6.542966 ); ERROR 1062 (23000): Duplicate entry '10--00-00 00:00:00' for key 1 where timestamp is of DATETIME type. This only happens in the 4.1.7 mysql, in 4.0.22 it is correctly interpreted. I find this strange, I thought these iso date/time strings where pretty standard. I generated them with the c++/posix_time to_iso_string() function. Any ideas how to overcome this problem ? Is this a bug ? (I'd be happy to report if that's the case). Many thanks, jan ps.: sorry if this topic have already been discussed under a different subject -- I couldn't find anything about this. -- Jan Pfeifer [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Building C string for Insert!!
To be perfectly honest I believe you have completely confused the programming language you are using with the SQL statements you need to use to interact with the server. These are two separate languages and have their own separate syntaxes. You can use almost any programming language and the programming-language-appropriate MySQL library to make a connection to a MySQL server and interact with it through SQL statements. The SQL you send to the server can be constructed using your programming language or can be partially compiled for you by using the library's prepared statement feature, if it has one. The exact syntax of the commands and data structures that you will use to send those SQL statements and receive the results of those statements will depend *ABSOLUTELY* on the library you are using to connect your programming language to the MySQL server. I think that if you take a step back and discover both what statements the MySQL SQL language supports AND what the library you are using to connect your language to the MySQL server can do, things will begin to make better sense. Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 12/13/2004 04:36:07 AM: Hi, I am building the insert string for inserting into particular table t1 using C program. The below string is stored in a variable. say sqlstmt = 'INSERT INTo T1 values(:id,:ename);' The values for the field's id, name will come from the front end. The sql string will be executed using Pro *C in Oracle. Can we get the MySQL Equivalent so that we can use the same in our C program? Do we have bind variable or host variable concepts in MySQL? In MySQL tried with @, i.e set the values of id, ename as @id = 10, @ename = 'Sample' after that used Insert into t1 values(@id,@ename). This worked fine. Can we use the same @ for the binding values in C' Program for mysql?. Thanks for help in advance. Regards, Narasimha 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.
Strange results - Part 2
This is a follow up message to a earlier threat this week (which is included in the message below) Ok, here's the model table: Table,Create Table model,CREATE TABLE `model` ( `PID` tinyint(3) NOT NULL auto_increment, `VendorID` tinyint(4) NOT NULL default '0', `Model` varchar(15) NOT NULL default '', PRIMARY KEY (`PID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 The vendor table: Table,Create Table vendor,CREATE TABLE `vendor` ( `PID` int(11) NOT NULL auto_increment, `Vendor` varchar(25) NOT NULL default '', PRIMARY KEY (`PID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 And the specs table: Table,Create Table specs,CREATE TABLE `specs` ( `SpecID` int(11) NOT NULL auto_increment, `ProdModel` varchar(15) NOT NULL default '', `ProdImage` text NOT NULL, `ProdPrice` varchar(15) NOT NULL default '', `ProdStroke` varchar(8) NOT NULL default '', `ProdCC` varchar(7) NOT NULL default '', `ProdFuel` varchar(15) NOT NULL default '', `ProdCooling` varchar(40) NOT NULL default '', `ProdStarter` varchar(20) NOT NULL default '', `ProdIgnition` char(3) NOT NULL default '', `ProdTrans` varchar(35) NOT NULL default '', `ProdFSusp` varchar(45) NOT NULL default '', `ProdRSusp` varchar(35) NOT NULL default '', `ProdFBrake` varchar(45) NOT NULL default '', `ProdRBrake` varchar(25) NOT NULL default '', `ProdTireSize` varchar(25) NOT NULL default '', `ProdSpeed` varchar(15) NOT NULL default '', `ProdDimension` varchar(30) NOT NULL default '', `ProdWheelBase` char(3) NOT NULL default '', `ProdSeatHeight` char(3) NOT NULL default '', `ProdGauges` mediumtext NOT NULL, `ProdSafety` mediumtext NOT NULL, `ProdAntiTheft` mediumtext NOT NULL, `ProdWeight` varchar(10) NOT NULL default '', `ProdOption` mediumtext NOT NULL, `ProdPower` varchar(30) NOT NULL default '', `ProdColor` mediumtext NOT NULL, `ProdConsumption` varchar(11) NOT NULL default '', `ProdOilInject` char(3) NOT NULL default '', `ProdLoad` varchar(7) NOT NULL default '', `ProdCargo` varchar(8) NOT NULL default '', `ProdWarranty` varchar(35) NOT NULL default '', `ProdCarb` varchar(35) NOT NULL default '', `ProdCarbManu` varchar(6) NOT NULL default '', `ProdStorage` varchar(30) NOT NULL default '', PRIMARY KEY (`SpecID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 Kevin A. Burton [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Steve Grosz wrote: If you can tell me the command to dump the table format, I'm more than happy to list it here. SHOW CREATE TABLE FOO; -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 I am still looking for assistance with the table problem. Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DBI-connect does not work in crontab but works interactively
Hello
I have a script that check the status of all innodb tables. It works
fine interactively.
But not from the crontab tab.
Here is the error message and also a list of the environment variables
from the crontab
The message is about libz.so that does not exit: but:
l /usr/local/lib/libz.so
lrwxrwxrwx 1 root other 13 Nov 23 16:26
/usr/local/lib/libz.so - libz.so.1.2.1*
Please if you have any idea of what is the problem, could you share it
with me?
Solaris 6, mysql-4.1.7
Johanne Duhaime
---
Your cron job on mercure
perl /seqweb/mysql/myscripts/check_innodb.pl
produced the following output:
install_driver(mysql) failed: Can't load
'/usr/local/lib/perl5/site_perl/5.6.1/sun4-solaris/auto/DBD/mysql/mysql.
so' for module DBD::mysql: ld.so.1: perl: fatal: libz.so: open failed:
No such file or directory at
/usr/local/lib/perl5/5.6.1/sun4-solaris/DynaLoader.pm line 206, DBLIST
line 1. at (eval 4) line 3 Compilation failed in require at (eval 4)
line 3, DBLIST line 1. Perhaps a required shared library or dll isn't
installed where expected at /seqweb/mysql/myscripts/check_innodb.pl line
53
HOME = /seqweb/mysql
LD_LIBRARY_PATH =
/usr/openwin/lib:/usr/lib:/opt/lib:/usr/local/lib/perl5/site_perl/5.6.1/
sun4-solaris/auto/DBD/mysql
LOGNAME = mysql
PATH =
/seqweb/mysql:/usr/openwin/bin/xview:/usr/openwin/bin:/opt/IXImotif/bin:
/opt/bin:/usr/bin:/etc:/usr/ucb:/usr/local/lib/perl5/site_perl/5.6.1/sun
4-solaris/auto/DBD/mysql
SHELL = /usr/bin/sh
TZ = Canada/Eastern
script part
use Env;
use Env qw(PATH HOME TERM);
use Env qw($SHELL @LD_LIBRARY_PATH);
$ENV{'LD_LIBRARY_PATH'}
='/usr/openwin/lib:/usr/lib:/opt/lib:/usr/local/lib/perl
5/site_perl/5.6.1/sun4-solaris/auto/DBD/mysql';
$ENV{'PATH'}='/seqweb/mysql:/usr/openwin/bin/xview:/usr/openwin/bin:/opt
/IXImoti
f/bin:/opt/bin:/usr/bin:/etc:/usr/ucb:/usr/local/lib/perl5/site_perl/5.6
.1/sun4-
solaris/auto/DBD/mysql';
# added just to check environment
foreach (sort keys %ENV) {
print $_ = $ENV{$_}\n;
}
# in bold the instruction that cause the message (line 53)
open (DBLIST, $DB_LIST); #list of all db we have
while (DBLIST) {
my $db_name=$_;
chomp($db_name);
my $dsn = DBI:mysql:$db_name:localhost; # data source name
my $dbh = DBI-connect ($dsn,$DBUSER, $DBPASS,
{ RaiseError = 1, PrintError = 0 })
or die Could not connect to server: $DBI::err ($DBI::errstr)\n;
Query with Average on COUNT(*) values?
For me, a Newbie, this is a tricky one. I've been through the manual and this archive, but haven't found a solution. Could someone of you experienced please help? Is it possible to make this query in one single SQL-statement? (Using MySQL 4.0.15 - If this can be done smarter in 4.1.x, please shed some light on that too as we might upgrade one day). A timestamp for each incoming call is stored in 'incall' of type Datetime in table 'incoming'. With number of calls grouped on the hour I need to follow up: Average of (No. of incoming calls)/hour/weekday. The following command does half ;-) of the job: SELECT DATE_FORMAT(incall, '%a %H') AS DayHour, COUNT(*) AS Calls FROM incoming WHERE DATE_FORMAT(incall, '%Y %m %d') = '2004 12 01' GROUP BY DayHour ORDER BY DATE_FORMAT(incall, '%w %H'); This gives me a result like this: +-+---+ | DayHour | Calls | +-+---+ | Sun 00 | 809 | | Sun 01 | 638 | | Sun 02 | 573 | | Sun 03 | 400 | | Sun 04 | 315 | | Sun 05 | 269 | | Sun 06 | 245 | | Sun 07 | 314 | |... | ... | etc. But these are the totals/hour/weekday, and I need the average/hour/weekday. What I haven't been able to figure out from the manual nor this archive is how to calculate the average, (or how to find out the right divisor for each 'Calls'-value). I tried various ways to use AVG() but none worked. As a test, I also tried 'COUNT(*)/3' and it works, but how can I replace that number 3 with a variable 'n' that has the correct value. Example: On Dec13th at 14:00 hours I want a statistic snapshot from Dec 01 until now. That means that all Calls-values from Wed 00 to Mon 13 should be divided by 2, but the values from Mon 14 to Tue 23 should be divided by 1 to get the correct average value. Thanks in advance Marty
Epoch seconds
Hi I have a table that has start_date and expire_date in the format: 2004-12-31 I also have a start field and expire field I need to convert the start_date to Epoch seconds and place in start and convert the expire_date and place in expire. I'm unsure how to convert and how to do this in one go via a query Help appreciated. Regards John Berman [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DBI-connect does not work in crontab but works interactively
In the last episode (Dec 13), Duhaime Johanne said: I have a script that check the status of all innodb tables. It works fine interactively. But not from the crontab tab. Here is the error message and also a list of the environment variables from the crontab The message is about libz.so that does not exit: but: l /usr/local/lib/libz.so lrwxrwxrwx 1 root other 13 Nov 23 16:26 /usr/local/lib/libz.so - libz.so.1.2.1* Actually libz.so should be in /usr/lib as well on Solaris 9 (it's part of the SUNWzlib package). You can use the 'crle' command as root to add /usr/local/lib to the ld.so searchpath. This will add 3 common library paths: crle -l -u /usr/local/lib:/usr/local/ssl/lib:/usr/sfw/lib -- 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: Strange results - Part 2
Roger Baklund wrote: [...] You are joining the model table on vendor.PID=model.VendorID, and model.VendorID is not a primary or unique key, it could contain duplicates. ... probably the four rows you want. This is ok. It's probably the other join that causes the problem. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleting redundant rows
Hello, You can try delete/filter the redundant rows with this code: (this is an option :D) create table yourtable ( id int not null auto_increment, city varchar(20), cc varchar(2), primary key(id) ); insert into yourtable values (0,'AAA','AA'); ... insert into yourtable values (0,'DDD','CC'); ++--+--+ | id | city | cc | ++--+--+ | 1 | AAA | AA | | 2 | AAA | AA | | 3 | AAA | AA | | 4 | BBB | BB | | 5 | BBB | BB | | 6 | BBB | BB | | 7 | BBB | BB | | 8 | CCC | CC | | 9 | CCC | CC | | 10 | CCC | CC | | 11 | CCC | CC | | 12 | DDD | DD | | 13 | TTT | EE | | 14 | DDD | CC | ++--+--+ -- Creating a secondary table with concatenated columns create table selected as select id,concat(city,cc) tmpfield from yourtable; -- Creating other table from based on latest table. create table selected2 as select id,count(tmpField) Total from selected group by tmpfield; select * from selected2; ++---+ | id | Total | ++---+ | 1 | 3 | | 4 | 4 | | 8 | 4 | | 14 | 1 | | 12 | 1 | | 13 | 1 | ++---+ -- Finally filter the redundant columns select a.id,a.city,a.cc from yourtable a,selected2 b where a.id = b.id; ++--+--+ | id | city | cc | ++--+--+ | 1 | AAA | AA | | 4 | BBB | BB | | 8 | CCC | CC | | 14 | DDD | CC | | 12 | DDD | DD | | 13 | TTT | EE | ++--+--+
Re: Deleting redundant rows
Walter Pucko wrote: Hello there, I do have a table in mysql 4.x with redundant info. Only the autoincrement ID is different. Example: ID citycc 2559756 Witkop SF 2559757 Witkop SF This turns to be a huge problem since I cant find a way to delete the redundant rows with a mysql query. Is there a solution for that? I tried it with distinct, but that does not work, with subselects but this seems not to be supported. ALTER IGNORE TABLE yourtable ADD UNIQUE (city,cc); URL: http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Limit drive usage per thread
From the brief sound of it your using myISAM, and the query taking the most time is not indexed or using an index properly. Alters will lock the table, and once it starts it should finish or your going to have to recover the table. I suggest taking an outage. If you can't Make a replica of the server, put it in a master slave role. Alter the slave (ensure the new column has a default) swap the roles OR copy the table if the application can handle having writes blocked. If not, the slave must take the role of master, and the previous master can go away. -Original Message- From: matt_lists [mailto:[EMAIL PROTECTED] Sent: Monday, December 13, 2004 10:05 AM To: [EMAIL PROTECTED] Subject: Limit drive usage per thread Is there any way to limit drive usage per thread? I have a problem where an update thread will use 100 % of the drive, and simple index searches that should be instant will wait and wait and wait before responding. I dont want one user to kill everybody else I'm adding a column to a large table for a client, but every client is getting hit with a database that seems to be locked up -- 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]
Iteration Possible? 4.0.18
Hi. Is there a way to in the mysql client (4.0.18) to for a list of IDs execute 2 queries with each ID... I.e. : I have a list of IDs 1,2,3,24,19,4,5 ... and would like to loop through them to execute 2 queries for each one so that I can pull the proper records out and print out the results I do not have PHP or such currenlty working on this system and do not wish to have to code two additioanl queries each time I need to add an ID or hunt for the 2 queries when I am deleting them ? Thanks Gary/KC2NPU -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Epoch seconds
John Berman wrote: I have a table that has start_date and expire_date in the format: 2004-12-31 That is an output format... yes? If the column is a string type (char/varchar/text...), you should change it to a DATE type, or maybe TIMESTAMP. The default output format for DATE type columns is -MM-DD. I also have a start field and expire field I need to convert the start_date to Epoch seconds and place in start and convert the expire_date and place in expire. Why? In general it is better to calculate things on the fly when you need it, and only store the information once. You should store either the date or the epoch value, and calculate the other when you need it. I'm unsure how to convert and how to do this in one go via a query A simple UPDATE will do it: UPDATE table SET start = UNIX_TIMESTAMP(start_date), end = UNIX_TIMESTAMP(end_date); # remove redundant columns ALTER TABLE table DROP start_date, DROP end_end; # create columns on the fly SELECT *, FROM_UNIXTIME(start,%Y-%m-%d) start_date, FROM_UNIXTIME(end,%Y-%m-%d) end_date FROM table ... URL: http://dev.mysql.com/doc/mysql/en/Date_and_time_type_overview.html URL: http://dev.mysql.com/doc/mysql/en/Date_and_time_types.html URL: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limit drive usage per thread
Dathan Pattishall wrote: From the brief sound of it your using myISAM, and the query taking the most time is not indexed or using an index properly. Alters will lock the table, and once it starts it should finish or your going to have to recover the table. I suggest taking an outage. If you can't Make a replica of the server, put it in a master slave role. Alter the slave (ensure the new column has a default) swap the roles OR copy the table if the application can handle having writes blocked. If not, the slave must take the role of master, and the previous master can go away. The queries waiting are not locked, and are hitting a different table I'm updating tableA users are doing queries on an indexed field on table B I just stopped my update, and tested the users queries, all responded in ms's, each were hitting the main primary key I am going to take tableA and modify it on an offline server it would be nice if I could limit drive useage so it did not kill the server like that running raid 5 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie question: segmentation fault in mysql_init
At 19:32 +0100 12/13/04, Alina BiŸkowska wrote:
Description:
When I try to call mysql_init() several times in
my program it finishes with segmentation fault.
This happens in different places of my program but always in mysql_init().
This is the gdb output:
#0 0x007ba6ae in malloc_consolidate () from /lib/tls/libc.so.6
#1 0x007b9e6a in _int_malloc () from /lib/tls/libc.so.6
#2 0x007b925d in malloc () from /lib/tls/libc.so.6
#3 0x001716ae in my_malloc () from /usr/local/lib/mysql/libmysqlclient.so.12
#4 0x0016c648 in mysql_init () from /usr/local/lib/mysql/libmysqlclient.so.12
#5 0x0804dee2 in myconnect () at databaseFunctions.c:14
#6 0x0804dfc0 in give_geneValue_with_key
(table=0x8051fe8 \selfRoot_shell\, id=9463)
at databaseFunctions.c:37
#7 0x08049a27 in selfProfiler () at library.c:232
#8 0x0804952c in negativeSelection () at library.c:176
#9 0x0804a92d in negativeSelectionSentinel () at library.c:420
#10 0x0804edad in communicator (arg=0x755d00) at communicator.c:60
#11 0x08048cdf in main () at primary.c:169
Here are the functions I use.
The first function is only to make a connection to database:
MYSQL* myconnect()
{
MYSQL *my_connection=malloc(sizeof(MYSQL *));
my_connection=mysql_init (NULL);
I don't think this solves your problem, but I'm curious:
Why do you allocate memory and assign it to my_connection, and then
immediately throw that memory away by assigning my_connection the value
of mysql_init()? That's a memory leak right there.
if(mysql_real_connect (my_connection,
\130.225.16.5\, \ala\, \alaSdb\, \ala\,
0, NULL, 0))
{
return my_connection;
}
else
{
fprintf (stderr, \Connection failed !!!\\n\);
if (mysql_errno (my_connection))
{
fprintf (stderr,
\Connection error %d: %s\\n\, mysql_errno
(my_connection), mysql_error (my_connection));
return NULL;
}
}
return NULL;
}
This function and a couple of others similar are
to withdraw some data from database. All of them
use myconnection() function;
char* give_geneValue_with_key(char *table,int id)
{
MYSQL* my_connection=myconnect();
MYSQL_RES *result=malloc(sizeof(MYSQL_RES*));
MYSQL_ROW row;
char temp[1];
sprintf(temp,\SELECT geneValue FROM %s where id=\'%d\'\,table,id);
if(mysql_real_query (my_connection,temp,strlen(temp))) return \0\;
if(!(result=mysql_store_result(my_connection))) return \0\;
row=mysql_fetch_row(result);
mysql_free_result(result);
mysql_close(my_connection);
return row[0];
}
If you have any ideas what can be a problem, please let me know.
Alina Binkowska
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie question: segmentation fault in mysql_init
Description:
When I try to call mysql_init() several times in my program it finishes with
segmentation fault.
This happens in different places of my program but always in mysql_init().
This is the gdb output:
#0 0x007ba6ae in malloc_consolidate () from /lib/tls/libc.so.6
#1 0x007b9e6a in _int_malloc () from /lib/tls/libc.so.6
#2 0x007b925d in malloc () from /lib/tls/libc.so.6
#3 0x001716ae in my_malloc () from /usr/local/lib/mysql/libmysqlclient.so.12
#4 0x0016c648 in mysql_init () from /usr/local/lib/mysql/libmysqlclient.so.12
#5 0x0804dee2 in myconnect () at databaseFunctions.c:14
#6 0x0804dfc0 in give_geneValue_with_key (table=0x8051fe8 \selfRoot_shell\,
id=9463)
at databaseFunctions.c:37
#7 0x08049a27 in selfProfiler () at library.c:232
#8 0x0804952c in negativeSelection () at library.c:176
#9 0x0804a92d in negativeSelectionSentinel () at library.c:420
#10 0x0804edad in communicator (arg=0x755d00) at communicator.c:60
#11 0x08048cdf in main () at primary.c:169
Here are the functions I use.
The first function is only to make a connection to database:
MYSQL* myconnect()
{
MYSQL *my_connection=malloc(sizeof(MYSQL *));
my_connection=mysql_init (NULL);
if(mysql_real_connect (my_connection, \130.225.16.5\, \ala\, \alaSdb\,
\ala\, 0, NULL, 0))
{
return my_connection;
}
else
{
fprintf (stderr, \Connection failed !!!\\n\);
if (mysql_errno (my_connection))
{
fprintf (stderr, \Connection error %d: %s\\n\,
mysql_errno (my_connection), mysql_error (my_connection));
return NULL;
}
}
return NULL;
}
This function and a couple of others similar are to withdraw some data from
database. All of them use myconnection() function;
char* give_geneValue_with_key(char *table,int id)
{
MYSQL* my_connection=myconnect();
MYSQL_RES *result=malloc(sizeof(MYSQL_RES*));
MYSQL_ROW row;
char temp[1];
sprintf(temp,\SELECT geneValue FROM %s where id=\'%d\'\,table,id);
if(mysql_real_query (my_connection,temp,strlen(temp))) return \0\;
if(!(result=mysql_store_result(my_connection))) return \0\;
row=mysql_fetch_row(result);
mysql_free_result(result);
mysql_close(my_connection);
return row[0];
}
If you have any ideas what can be a problem, please let me know.
Alina Binkowska
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Getting monthly download report
I have a table like:
CREATE TABLE `user_details` (
`id` bigint(6) unsigned NOT NULL auto_increment,
`user_name` varchar(100) NOT NULL default '',
`user_email` varchar(50) NOT NULL default '',
`user_ref_from` varchar(100) default 'Not Given',
`other_ref` varchar(255) default 'Not Given',
`products` enum('a','b','c') NOT NULL default 'c',
`last_download` mediumint(3) NOT NULL default '40',
`want_notify` enum('Yes','No') default 'Yes',
`dateofdown` date NOT NULL default '-00-00',
`dn_from_email` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `user_email` (`user_email`),
KEY `NotifyIndex` (`want_notify`),
KEY `dateofdown` (`dateofdown`),
KEY `products` (`products` )
) TYPE=MyISAM;
Whenever a user downloads a trial version I keep his
info in the table. I also send a mail to the user with
the download instrcutions. When the user clicks on the
link - i increment dn_from_email by one to know that
he actually downloaded the app by cliciking on the
link.
I get report for my downloads by executing multiple
queries like:
SELECT dayofmonth(dateofdown),
COUNT(IF(products=a,1,NULL)),
COUNT(IF(products=b,1,NULL)),
COUNT(IF(products=c,1,NULL)) from user_details where
month(dateofdown) = month(curdate()) and
year(dateofdown) = year(curdate()) group by 1;
SELECT dayofmonth(dateofdown),
COUNT(IF(products=a,1,NULL)),
COUNT(IF(products=b,1,NULL)),
COUNT(IF(products=c,1,NULL)) from user_details where
dn_from_email 0 and month(dateofdown) =
month(curdate()) and year(dateofdown) =
year(curdate()) group by 1;
Is this method optimized enough?
Also, if for some day dn_from_email = 0 for all the
products then the report goes awry as the number of
rows returned from first query != number of queries
returned from second query.
Am I on the correct path?
Regards,
Karam
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange results - Part 2
Steve Grosz wrote: This is a follow up message to a earlier threat this week (which is included in the message below) model,CREATE TABLE `model` ( PRIMARY KEY (`PID`) vendor,CREATE TABLE `vendor` ( PRIMARY KEY (`PID`) specs,CREATE TABLE `specs` ( PRIMARY KEY (`SpecID`) Like Rhino suggested, you are joining on something other than your primary keys. Your query, slightly re-arranged for readability: select * from vendor left outer join model on vendor.PID=model.VendorID left outer join specs on model.Model=specs.ProdModel where vendor.Vendor='#URL.Vendor#' You are joining the model table on vendor.PID=model.VendorID, and model.VendorID is not a primary or unique key, it could contain duplicates. You are joining the specs table on model.Model=specs.ProdModel, neither is a primary key or unique, both could containt duplicates. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help Required!
Check here. You will find examples here. http://www.geocities.com/jahan.geo [EMAIL PROTECTED] wrote: Hi, I am building the insert string for inserting into particular table t1 using C program. The below string is stored in a variable. say sqlstmt = 'INSERT INTo T1 values(:id,:ename);' The values for the field's id, name will come from the front end. The sql string will be executed using Pro *C in Oracle. Pro *C can get the values of Id and ename fields which are initialized in the C program and can execute the sql statement in Oracle database. The record got inserted successfully. Can we get the MySQL Equivalent so that we can use the same in our C program? Do we have bind variable or host variable concepts in MySQL? In MySQL prompt we tried with @, i.e. set the values of id, ename as set @id = 10, @ename = 'Sample'; Next we used the below command and it worked fine. Insert into t1 values(@id,@ename). Can we use the same @ for the binding values in C' Program for mysql so that it substitutes the value of fields? Thanks for help in advance. Please help. Regards, Narasimha 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Connect issue
Hi,
I have Apache 2, PHP 5 and MySQL 4.1 installed on an XP pro box.
I have created a new database 'ijdb' with a single table 'joke' and have
entered data into two of the three fields in the table.
I can access the database / tables / data from a command prompt, and I can
access and extract the data with an odbc_connect.
However, when I try to connect through a mysql_connect connection type I either
receive a 'Unable to connect to the database server at this time.' error
message - which is my default error message, or, I receive a blank window in IE
/ Mozilla / Opera etc and no error messages (just a blank screen).
I have attached the code I am using below - hoping that someone can see where I
am going wrong and will point me in the right direction.
I have tried removing the @ from the file and this has no effect - interesting?!
The error logs do not reveal anything that indicates a missing table / field.
I wonder if anyone has any ideas ?
Cheers,
Mike
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Strict//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd;
html xmlns=http://www.w3.org/1999/xhtml;
head
titleOur List of Jokes/title
meta http-equiv=content-type
content=text/html; charset=iso-8859-1 /
/head
body
?php
// Connect to the database server
$dbcnx = @mysql_connect('localhost', 'root', 'MyPassword');
if (!$dbcnx) {
echo 'pUnable to connect to the ' .
'database server at this time./p' );
exit();
}
// Select the jokes database
if ([EMAIL PROTECTED]('ijdb')) {
exit('pUnable to locate the joke ' .
'database at this time./p');
}
?
pHere are all the jokes in our database:/p
blockquote
?php
// Request the text of all the jokes
$result = @mysql_query('SELECT joketext FROM joke');
if (!$result) {
exit('pError performing query: ' . mysql_error() . '/p');
}
// Display the text of each joke in a paragraph
while ($row = mysql_fetch_array($result)) {
echo 'p' . $row['joketext'] . '/p';
}
?
/blockquote
/body
/html
Re: Query with Average on COUNT(*) values?
Thanks, Shawn, this helped a lot, although I'm not there yet. (..and you got it absolutely right, I just couldn't explain it correctly). I tried to execute this in OpenOffice, but it seems to use a function called executeQuery(). I don't know if it's part of OpenOffice or a library routine in the JDBC it uses, but I get an error saying that data manipulation statements cannot be issued using executeQuery(), so the query you suggested fails. Working directly with the database (using MySQLcc or the console) works fine. ...but at least I now have a clue of how it works, so I'll try to make a workaround in OOo. Thnx! /Marty On Monday 13 December 2004 22:40, [EMAIL PROTECTED] wrote: It's not a hard as you are making it out to be. You are already able to Group you data on a value that breaks your data into useful chunks. In your case you already figured out one way to differentiate one hour from another and one weekday from another. (The value 'Sun 00' is different than 'Mon 00'). What I don't think you knew how to explain was that you wanted to average each hour of each day across separate weeks. There could be a way to do this in one query but I am not certain how so I will explain a two-step method: First collect the same data you are already generating (totals by hour/weekday) except also collect a week number (so that we collect separate values for each week). Store those values somewhere temporarily (may I suggest a temp table?) CREATE TEMPORARY TABLE tmpStats SELECT DATE_FORMAT(incall, '%V') AS week , DATE_FORMAT(incall, '%a %H') AS DayHour, COUNT(*) AS Calls FROM incoming WHERE DATE_FORMAT(incall, '%Y %m %d') = '2004 12 01' GROUP BY DayHour Now it's simple to average across the weeks: SELECT DayHour, avg(calls) FROM tmpStats GROUP BY DayHour; (Group By has an implicit ORDER BY built into it. You have to specifically ask for that sorting to NOT happen if you don't want it). Now that you no longer need it, you can also drop the temp table: DROP TEMPORARY TABLE tmpStats; HTH, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Onemarty [EMAIL PROTECTED] wrote on 12/13/2004 11:30:36 AM: For me, a Newbie, this is a tricky one. I've been through the manual and this archive, but haven't found a solution. Could someone of you experienced please help? Is it possible to make this query in one single SQL-statement? (Using MySQL 4.0.15 - If this can be done smarter in 4.1.x, please shed some light on that too as we might upgrade one day). A timestamp for each incoming call is stored in 'incall' of type Datetime in table 'incoming'. With number of calls grouped on the hour I need to follow up: Average of (No. of incoming calls)/hour/weekday. The following command does half ;-) of the job: SELECT DATE_FORMAT(incall, '%a %H') AS DayHour, COUNT(*) AS Calls FROM incoming WHERE DATE_FORMAT(incall, '%Y %m %d') = '2004 12 01' GROUP BY DayHour ORDER BY DATE_FORMAT(incall, '%w %H'); This gives me a result like this: +-+---+ | DayHour | Calls | +-+---+ | Sun 00 | 809 | | Sun 01 | 638 | | Sun 02 | 573 | | Sun 03 | 400 | | Sun 04 | 315 | | Sun 05 | 269 | | Sun 06 | 245 | | Sun 07 | 314 | |... | ... | etc. But these are the totals/hour/weekday, and I need the average/hour/weekday. What I haven't been able to figure out from the manual nor this archive is how to calculate the average, (or how to find out the right divisor for each 'Calls'-value). I tried various ways to use AVG() but none worked. As a test, I also tried 'COUNT(*)/3' and it works, but how can I replace that number 3 with a variable 'n' that has the correct value. Example: On Dec13th at 14:00 hours I want a statistic snapshot from Dec 01 until now. That means that all Calls-values from Wed 00 to Mon 13 should be divided by 2, but the values from Mon 14 to Tue 23 should be divided by 1 to get the correct average value. Thanks in advance Marty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Duplicate columns in mysql database
Hi, I used mysqldump to recreate the a DB in another machine: % mysqldump -A -u root --opt -p backup.sql But I guess that duplicates some columns in the mysql database, `cause when want to add a new user to a new database: mysql grant all on some_db.* to [EMAIL PROTECTED] identified by 'pwd'; I get this: ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'some_db' I've tried to fix the privileges table, but this is the output: % /usr/local/bin/mysql_fix_privilege_tables --verbose --password=password ERROR 1060 at line 19: Duplicate column name 'File_priv' ERROR 1060 at line 32: Duplicate column name 'Grant_priv' ERROR 1060 at line 33: Duplicate column name 'Grant_priv' ERROR 1060 at line 34: Duplicate column name 'Grant_priv' ERROR 1060 at line 45: Duplicate column name 'ssl_type' ERROR 1054 at line 84: Unknown column 'Type' in 'columns_priv' ERROR 1060 at line 90: Duplicate column name 'type' ERROR 1060 at line 100: Duplicate column name 'Show_db_priv' ERROR 1060 at line 117: Duplicate column name 'max_questions' ERROR 1060 at line 127: Duplicate column name 'Create_tmp_table_priv' ERROR 1060 at line 130: Duplicate column name 'Create_tmp_table_priv' ERROR 1061 at line 138: Duplicate key name 'Grantor' ERROR 1060 at line 150: Duplicate column name 'Create_view_priv' ERROR 1060 at line 151: Duplicate column name 'Create_view_priv' ERROR 1060 at line 152: Duplicate column name 'Create_view_priv' ERROR 1060 at line 157: Duplicate column name 'Show_view_priv' ERROR 1060 at line 158: Duplicate column name 'Show_view_priv' ERROR 1060 at line 159: Duplicate column name 'Show_view_priv' done What can I do? Is there a way to fix this? Thanks a lot in advance. Regards, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Compacting myisam files.
I'm having to run some purges of data to clean up disk space until the new storage array arrives. I've got the data archived, and the rows deleted from the table. The only method I know to reclaim the space in the table is to use myisamchk -r. Is there any other faster method for reclaiming the deleted row space? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
searching every occurrence of a term in a text field
Hi all having a table with one text field in which I store text (sic!) I need to extract from it every occurence of a term (or a phrase) with its context, litteraly extract 30 char before and 30 char after the term I have searched. thx for any suggestions ciao Mario -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Compacting myisam files.
Hello. You may use OPTIMIZE TABLE. Duncan Hill [EMAIL PROTECTED] wrote: I'm having to run some purges of data to clean up disk space until the new storage array arrives. I've got the data archived, and the rows deleted from the table. The only method I know to reclaim the space in the table is to use myisamchk -r. Is there any other faster method for reclaiming the deleted row space? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connect issue
Hello. Use: $link = mysql_connect(mysql_host, mysql_user, mysql_password) or die(Could not connect : . mysql_error()); to determine what error occurs when you are connecting. Turn on error reporting in php.ini. Hi, I have Apache 2, PHP 5 and MySQL 4.1 installed on an XP pro box. I have created a new database 'ijdb' with a single table 'joke' and have entered data into two of the three fields in the table. I can access the database / tables / data from a command prompt, and I can access and extract the data with an odbc_connect. However, when I try to connect through a mysql_connect connection type I either receive a 'Unable to connect to the database server at this time.' error message - which is my default error message, or, I receive a blank window in IE / Mozilla / Opera etc and no error messages (just a blank screen). I have attached the code I am using below - hoping that someone can see where I am going wrong and will point me in the right direction. I have tried removing the @ from the file and this has no effect - interesting?! The error logs do not reveal anything that indicates a missing table / field. I wonder if anyone has any ideas ? Cheers, Mike Mike Francis [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ANN: MyCon 2005.2.6 Released
SciBit is proud to announce the release of the newest version of MyCon, v2.6 This version includes many new and improved features as well as all reported bugfixes. Amongst others: 1. Built-in support for the new 4.1 authentication, i.e. without need for an external libmysql.dll 2. Improved CopyPaste and DragDrop functionality for copying/backing up and restoring databases, tables, queries, scripts and report MySQL objects. Now includes Outlook-style Move to/Copy to dialogs. 3. Simplied folder view for all the Mascon fans. 4. Full range of data editors for every MySQL column type, from Blob, Memo, Picture editors to date/time, string editors. Now includes a full date AND time editor for datetime/timestamp columns 5. Skin/Style support For more information see: http://forum.scibit.com/viewtopic.php?t=224 http://forum.scibit.com/viewtopic.php?t=215 http://forum.scibit.com/viewtopic.php?t=164 For free downloads and/or free versions, see: http://www.scibit.com/products/mycon Kind Regards SciBit MySQL Team http://www.scibit.com MySQL Products: http://www.scibit.com/products/mycon http://www.scibit.com/products/mysqlcomponents http://www.scibit.com/products/mysqlx http://www.scibit.com/products/mascon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
No connect to mysql
Hi, I have a problem with mysql 4.0.22 on fedora core 2. ..when I try: #/usr/bin/mysqladmin -u root password '' /usr/bin/mysqladmin: connect to server at 'localhost' failed error: Can't connect to local MySQL server through socket '/var/lib/mysql.sock (2)' Check that mysql is running and that the socket: '/var/lib/mysql.sock' exists ! .. the file mysql.sock not exists but I can to resolve ? .. thanks and sorry for my banal question. Salvatore. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General query question
I want to create entries in the child table (table2) for the missing records. In table2 the primary key is of type Integer, for each new entry it should be Max(table2.PrimaryKeyfield) + 1. Why not make the primary key in table2 autoincrement? If you have an autoincrement field as primary key MySQL will do the max(..)+1 automatically for you! A query like Insert into table2 (field1, field2) select a.field1, a.field2 from table1 a left join table2 b on (a.field1 = b.field1) where b.field1 is null could then be used to fill up the missing records in table2... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General query question
Thanks, I did think of it but not having the option as this is linked to executables, which I'm sure have some sorts of calculation for this field to calculate the next value. regards --- Jigal van Hemert [EMAIL PROTECTED] wrote: I want to create entries in the child table (table2) for the missing records. In table2 the primary key is of type Integer, for each new entry it should be Max(table2.PrimaryKeyfield) + 1. Why not make the primary key in table2 autoincrement? If you have an autoincrement field as primary key MySQL will do the max(..)+1 automatically for you! A query like Insert into table2 (field1, field2) select a.field1, a.field2 from table1 a left join table2 b on (a.field1 = b.field1) where b.field1 is null could then be used to fill up the missing records in table2... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ___ ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
searching every occurrence of a term in a text field
Hi all having a table with one text field in which I store text (sic!) I need to extract from it every occurence of a term (or a phrase) with its context, litteraly extract 30 char before and 30 char after the term I have searched. thx for any suggestions ciao Mario -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
One of my tables seems to repeatedly hang for 30-180 seconds
Hi there, I've been having some trouble with an application that I'm running (www.spurl.net). I have a database that consists of about 20 tables. The largest tables are a little more than one million rows and the database in total around 1GB - so nothing serious there. The amount of queries is on average less than 10 per second and it's running on a pretty powerful machine, so all in all there shouldn't really be any performance issues - and normally there aren't. All of the tables are myISAM. Now, from time to time (even several times per hour) one of my tables seems to hang for 30 - 180 seconds, and as this is a key table in the application it basically brings everything to a halt. The interesting thing is that I can not see a pattern in the web server logs of anything special going on at these times. I have run a check table and analyze table and everything seems to be fine there. So my question is pretty general: What can cause a table to hang like that? Obviously write locks could, but as I said it seems that there is not necessarily any writes or updates going on at the time. And normally (when the server is not in this foul mood) I can run dozens or even hundreds of all my write queries per second. - Can VERY heavy SELECT statements have this effect? - Could it have something to do with indexes (there is only one partial text index on this table in addition to the primary index)? - Could my hosting provider be running some scheduled tasks on the server that cause this? It is a dedicated server and they run backup once a day, which might cause something like this - but that does not go hand in hand with the fact that this happens now and then during the entire day. Basically I'm looking for ideas for what to look into as I'm running out of ideas (have been rewriting queries, making caches and all sort of things). As I'm running this in a hosted environment I don't have full access to everything - but at least a place to start would be really heplful. Thanks in advance, -hjalmar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: One of my tables seems to repeatedly hang for 30-180 seconds
What does the show processlist look like? Have you ran top/vmstat/iostat when the `hanging` occurs? Hjalmar Gislason wrote: Hi there, I've been having some trouble with an application that I'm running (www.spurl.net). I have a database that consists of about 20 tables. The largest tables are a little more than one million rows and the database in total around 1GB - so nothing serious there. The amount of queries is on average less than 10 per second and it's running on a pretty powerful machine, so all in all there shouldn't really be any performance issues - and normally there aren't. All of the tables are myISAM. Now, from time to time (even several times per hour) one of my tables seems to hang for 30 - 180 seconds, and as this is a key table in the application it basically brings everything to a halt. The interesting thing is that I can not see a pattern in the web server logs of anything special going on at these times. I have run a check table and analyze table and everything seems to be fine there. So my question is pretty general: What can cause a table to hang like that? Obviously write locks could, but as I said it seems that there is not necessarily any writes or updates going on at the time. And normally (when the server is not in this foul mood) I can run dozens or even hundreds of all my write queries per second. - Can VERY heavy SELECT statements have this effect? - Could it have something to do with indexes (there is only one partial text index on this table in addition to the primary index)? - Could my hosting provider be running some scheduled tasks on the server that cause this? It is a dedicated server and they run backup once a day, which might cause something like this - but that does not go hand in hand with the fact that this happens now and then during the entire day. Basically I'm looking for ideas for what to look into as I'm running out of ideas (have been rewriting queries, making caches and all sort of things). As I'm running this in a hosted environment I don't have full access to everything - but at least a place to start would be really heplful. Thanks in advance, -hjalmar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Epoch seconds
Here's a SQL statement that converts dates to epoch time in Oracle.
I'm too lazy to convert it to MySQL but it should give you a start.
select 86400 * ( to_date('14-feb-2000 10:38:39', 'dd-mon-
hh24:mi:ss')
- to_date('01-jan-1970', 'dd-mon-') )
from dual;
--Walt
-Original Message-
From: John Berman [mailto:[EMAIL PROTECTED]
Sent: Monday, December 13, 2004 9:51 AM
To: [EMAIL PROTECTED]
Subject: Epoch seconds
Importance: High
Hi
I have a table that has start_date and expire_date in the
format: 2004-12-31
I also have a start field and expire field
I need to convert the start_date to Epoch seconds and place
in start and
convert the expire_date and place in expire.
I'm unsure how to convert and how to do this in one go via a query
Help appreciated.
Regards
John Berman
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql? [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restore help! been going 2 weeks
Nobody else has problems with restores on 8+ gig tables? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limit drive usage per thread
matt_lists wrote: Dathan Pattishall wrote: From the brief sound of it your using myISAM, and the query taking the most time is not indexed or using an index properly. Alters will lock the table, and once it starts it should finish or your going to have to recover the table. I suggest taking an outage. If you can't Make a replica of the server, put it in a master slave role. Alter the slave (ensure the new column has a default) swap the roles OR copy the table if the application can handle having writes blocked. If not, the slave must take the role of master, and the previous master can go away. The queries waiting are not locked, and are hitting a different table I'm updating tableA users are doing queries on an indexed field on table B I just stopped my update, and tested the users queries, all responded in ms's, each were hitting the main primary key I am going to take tableA and modify it on an offline server it would be nice if I could limit drive useage so it did not kill the server like that running raid 5 Anybody have any sugestions? It's not a lock wait, like you see when you modify myISAM query it at the same time, it's just hung waiting for the drives -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Restore help! been going 2 weeks
check your .myd file size. if table type is myisam and it it is more than 4 GB then convert it to InnoDB. --Anil -Original Message- From: matt_lists [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 14, 2004 8:36 PM Cc: [EMAIL PROTECTED] Subject: Re: Restore help! been going 2 weeks Nobody else has problems with restores on 8+ gig tables? -- 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]
Building C string for Insert!!
I am building the insert string for inserting into particular table t1 using C program. The below string is stored in a variable. say sqlstmt = 'INSERT INTo T1 values(:id,:ename);' The values for the field's id, name will come from the front end. The sql string will be executed using Pro *C in Oracle. Can we get the MySQL Equivalent so that we can use the same in our C program? Do we have bind variable or host variable concepts in MySQL? Thanks for help in advance. Regards, Narasimha 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.
Re: Newbie question about index (why are they not updating?)
Hello. Cardinality of index is updated by running ANALYZE TABLE or myisamchk -a. See: http://dev.mysql.com/doc/mysql/en/SHOW_INDEX.html I've created a table with several indexes as follows: $query = CREATE TABLE `data_raw` ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, run_id VARCHAR(20) DEFAULT 'error_internal' NOT NULL, time_run DATETIME DEFAULT '-00-00 00:00:00' NOT NULL, ...clip. PRIMARY KEY (id), INDEX x_run_id (run_id), INDEX x_comp_code (comp_code), INDEX x_time_run (time_run), INDEX x_url (url) ) COMMENT = 'Raw data samples' ; $query executed with php mysql_query. The table is created OK. I add many records to the table with $query = INSERT INTO data_raw( id, run_id, time_run, time_sample, comp_code, url, url_index, err_number, err_desc, err_src ) VALUES( 0, '$run_id', '$time_run', '$ts', '$data[1]', '$data[2]', $data[3], $data[4], '$data[5]', '$data[6]' ); Data is added OK. But, the indexes are not updated! Running myPHPAdmin shows: Indexes: Documentation Keyname Type Cardinality Action Field PRIMARY PRIMARY 12932 Edit Drop id x_run_id INDEX None Edit Drop run_id x_comp_code INDEX None Edit Drop comp_code x_time_run INDEX None Edit Drop time_run x_url INDEX None Edit Drop url Access is slow. If I do an ALTER TABLE ... ADD INDEX ... indexes are updated and subsequent INSERTS update the indexes. I'm sure I'm doing something simple wrong but need a clue as to what? Any help much appreciated. Richard Bell [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge-union operation returnes index null
Hello. Please send the complete output of SHOW CREATE TABLE on all tables, which you want to put into merged table. You may check if your new merged table is ok by running queries of your web application in MySQL-Admin. [EMAIL PROTECTED] wrote: Hello! I have been struggling with this, and don=B4t know how to move on. I have several databases they have the same stucture. I want to gather all data fr=E5n one type of table in the databases to another table: merge-union operation. I have done that operation and it looks okay, but aren=B4t. Beacuse th= e index in the new tables are null, in the old tables the index are the s= ame as the number of instances in the table. And it mathers beacuse the webapplikation shows nothing of whats seems to be in the tables. I have an webbapplikation in php to present the data (it=B4s formated i= nto statistics). And the webbapplikation need besides the answer table al= so a person table. I have Mysql-admin to administrate Mysql. I have some knowledge about databases in general, but Mysql are new to me. My queries looks like this: CREATE TABLE answer (id INT, value TINYINT, question INT, person INT, INDEX(id) ) TYPE=3DMERGE UNION=3D( answer1, person2, person3) CREATE TABLE person (id INT(10) unsigned NOT NULL auto_increment, namec= ode VARCHAR(50) NOT NULL default '', password VARCHAR (8) default NULL, isinvited TINYINT(4) NOT NULL default '0', isdone TINYINT (4) NOT NULL default '0', section TINYINT (3) unsigned default NULL, INDEX(id) , INDEX(namecode) ) TYPE=3DMERGE UNION=3D(person1, person2, person3 I am very thankful for help in this matter. regards Rosemarie = -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Query Browser
Hello. I don't think that a lot of people can answer on your question. Windows ME is rarely found to be an OS for MySQL users. You may report a bug at bugs.mysql.com. Hassan Shaikh [EMAIL PROTECTED] wrote: The MySQL Query Browser online documentation clearly mentions that it runs on 32-bit Windows operating systems, including Windows 95, 98 and Me. However, while installing it on WinME, I get the following error: The Operating System is not adequate for running MySQL Query Browser 1.1 Can anyone please explain? Thanks. Hassan -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysqldump and read lock
Hello. Usually clients are blocked with insert or update statements until table became unlocked. Their threads just sleep and then continue execution (but they can got an error if query doesn't return for a long time). INSERT queries are not buffered in that sense you've ment, unlike they are INSERT DELAYED. May be you should think about incremental backup. See: http://dev.mysql.com/doc/mysql/en/Backup.html [EMAIL PROTECTED] wrote: [-- text/plain, encoding 8bit, charset: US-ASCII, 14 lines --] Hi all, Looking for a way to automate backup I was thinking of scheduling mysqldump on a daily base. It want to use the read lock, but I am not really sure about the consequences of this. Does putting a read lock on the files can cause loss of data? It is no problem pausing the flow of incoming data (invoices and other documents that are put in a monitored directory). But what about the data coming from the application that accesses the database? Say a user changes preferences or creates a new query, which are saved in the database. Is that data lost or buffered by MySql? Or does the application have to provide functionality to anticipate his situation? I know, I could bring down the whole system (webserver, application services and MySql), but with the read lock, the system would be 'down' (querying still possible) for only half an hour and it keeps running. The platform is Win2K. Regards, Olivier -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: logs: is there a possibility to stop/start them without stopping server
Hello. I can't find such feature in documentation and archives. The best way I've found is to comment or uncomment line in your my.cnf file and restart server. Remigiusz Soko?owski [EMAIL PROTECTED] wrote: Hi! I wonder if there is any possibility to switch log off on running server? Usually I have here general logs disabled due to performance and space saving reasons - but of course sometimes it is needed to find out full info about queries executed. It'd be fine to have possibility to run server with log and stopping/starting logging on demand. TIA Remigiusz -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User variables
I am having a problem with a query: SET @SoftwareID = 7; SELECT s.softwareID, s.softwareName, s.softwareVersion, s.softwareCreated, s.softwareChanged, b.buildName, s.supportFlag, s.softwareDesc, s.softwareLicense, s.softwareLocations, s.softwareProductKey, p.platformName, v.vendorName, v.vendorURL FROM software s INNER JOIN platform_groups pg on s.softwareID = pg.softwareID INNER JOIN platforms p on pg.platformID = p.platformID INNER JOIN vendors v on s.vendorID = v.vendorID INNER JOIN builds b on s.buildTypeID = b.buildTypeID WHERE s.softwareID = @SoftwareID This does not work. It appears that the user variable is not picked up in the WHERE clause - the query works fine if I have: WHERE s.softwareID = 7 Is what I am trying to do not supported? I am using version 4.1.7 on FreeBSD 5.3. Best regards, Erich Beyrent -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DBI-connect does not work in crontab but works interactively
In the last episode (Dec 13), Dan Nelson said: In the last episode (Dec 13), Duhaime Johanne said: I have a script that check the status of all innodb tables. It works fine interactively. But not from the crontab tab. Here is the error message and also a list of the environment variables from the crontab The message is about libz.so that does not exit: but: l /usr/local/lib/libz.so lrwxrwxrwx 1 root other 13 Nov 23 16:26 /usr/local/lib/libz.so - libz.so.1.2.1* Actually libz.so should be in /usr/lib as well on Solaris 9 (it's part of the SUNWzlib package). You can use the 'crle' command as root to add /usr/local/lib to the ld.so searchpath. This will add 3 common library paths: crle -l -u /usr/local/lib:/usr/local/ssl/lib:/usr/sfw/lib Oops. Swap the -l and -u :) crle -u -l /usr/local/lib:/usr/local/ssl/lib:/usr/sfw/lib -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Limit drive usage per thread
Is there any way to limit drive usage per thread? I have a problem where an update thread will use 100 % of the drive, and simple index searches that should be instant will wait and wait and wait before responding. I dont want one user to kill everybody else I'm adding a column to a large table for a client, but every client is getting hit with a database that seems to be locked up -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Deleting redundant rows
Hello there, I do have a table in mysql 4.x with redundant info. Only the autoincrement ID is different. Example: ID citycc 2559756 Witkop SF 2559757 Witkop SF This turns to be a huge problem since I cant find a way to delete the redundant rows with a mysql query. Is there a solution for that? I tried it with distinct, but that does not work, with subselects but this seems not to be supported. I am lost here, maybe someone with better sql knowledge can help out. Thank you for any help, Merlin -- GMX ProMail mit bestem Virenschutz http://www.gmx.net/de/go/mail +++ Empfehlung der Redaktion +++ Internet Professionell 10/04 +++ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query with Average on COUNT(*) values?
It's not a hard as you are making it out to be. You are already able to Group you data on a value that breaks your data into useful chunks. In your case you already figured out one way to differentiate one hour from another and one weekday from another. (The value 'Sun 00' is different than 'Mon 00'). What I don't think you knew how to explain was that you wanted to average each hour of each day across separate weeks. There could be a way to do this in one query but I am not certain how so I will explain a two-step method: First collect the same data you are already generating (totals by hour/weekday) except also collect a week number (so that we collect separate values for each week). Store those values somewhere temporarily (may I suggest a temp table?) CREATE TEMPORARY TABLE tmpStats SELECT DATE_FORMAT(incall, '%V') AS week , DATE_FORMAT(incall, '%a %H') AS DayHour, COUNT(*) AS Calls FROM incoming WHERE DATE_FORMAT(incall, '%Y %m %d') = '2004 12 01' GROUP BY DayHour Now it's simple to average across the weeks: SELECT DayHour, avg(calls) FROM tmpStats GROUP BY DayHour; (Group By has an implicit ORDER BY built into it. You have to specifically ask for that sorting to NOT happen if you don't want it). Now that you no longer need it, you can also drop the temp table: DROP TEMPORARY TABLE tmpStats; HTH, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Onemarty [EMAIL PROTECTED] wrote on 12/13/2004 11:30:36 AM: For me, a Newbie, this is a tricky one. I've been through the manual and this archive, but haven't found a solution. Could someone of you experienced please help? Is it possible to make this query in one single SQL-statement? (Using MySQL 4.0.15 - If this can be done smarter in 4.1.x, please shed some light on that too as we might upgrade one day). A timestamp for each incoming call is stored in 'incall' of type Datetime in table 'incoming'. With number of calls grouped on the hour I need to follow up: Average of (No. of incoming calls)/hour/weekday. The following command does half ;-) of the job: SELECT DATE_FORMAT(incall, '%a %H') AS DayHour, COUNT(*) AS Calls FROM incoming WHERE DATE_FORMAT(incall, '%Y %m %d') = '2004 12 01' GROUP BY DayHour ORDER BY DATE_FORMAT(incall, '%w %H'); This gives me a result like this: +-+---+ | DayHour | Calls | +-+---+ | Sun 00 | 809 | | Sun 01 | 638 | | Sun 02 | 573 | | Sun 03 | 400 | | Sun 04 | 315 | | Sun 05 | 269 | | Sun 06 | 245 | | Sun 07 | 314 | |... | ... | etc. But these are the totals/hour/weekday, and I need the average/hour/weekday. What I haven't been able to figure out from the manual nor this archive is how to calculate the average, (or how to find out the right divisor for each 'Calls'-value). I tried various ways to use AVG() but none worked. As a test, I also tried 'COUNT(*)/3' and it works, but how can I replace that number 3 with a variable 'n' that has the correct value. Example: On Dec13th at 14:00 hours I want a statistic snapshot from Dec 01 until now. That means that all Calls-values from Wed 00 to Mon 13 should be divided by 2, but the values from Mon 14 to Tue 23 should be divided by 1 to get the correct average value. Thanks in advance Marty
Double conversion error
Title: Double conversion error I have a query where I perform an update where (CALL_ID = 2.37000e+002); This query updates nothing, even though my CALL_ID column has an id of 237. If I change the end of this query to read: where (CALL_ID = 237);, then the row with CALL_ID=237 is updated. Is there a reason why the double value 2.37000e+002 is not evaluating to 237? Regards, -Brett Berry Boeing Software Tools Engineer
AUTO_INCREMENT working
Can anybody tell me how AUTO_INCREMENT works in MySQL especially when we are using DB Replications. At times I get duplicate key error though taken care that the insertions and updations are done in the master db.
RE: User variables
Hi, Set the value as SET @SoftwareID:=7; Now, use WHERE s.softwareID = @SoftwareID at the end of your query. Thanks, Narasimha -Original Message- From: Erich Beyrent [mailto:[EMAIL PROTECTED] Sent: Monday, December 13, 2004 8:30 PM To: [EMAIL PROTECTED] Subject: User variables I am having a problem with a query: SET @SoftwareID = 7; SELECT s.softwareID, s.softwareName, s.softwareVersion, s.softwareCreated, s.softwareChanged, b.buildName, s.supportFlag, s.softwareDesc, s.softwareLicense, s.softwareLocations, s.softwareProductKey, p.platformName, v.vendorName, v.vendorURL FROM software s INNER JOIN platform_groups pg on s.softwareID = pg.softwareID INNER JOIN platforms p on pg.platformID = p.platformID INNER JOIN vendors v on s.vendorID = v.vendorID INNER JOIN builds b on s.buildTypeID = b.buildTypeID WHERE s.softwareID = @SoftwareID This does not work. It appears that the user variable is not picked up in the WHERE clause - the query works fine if I have: WHERE s.softwareID = 7 Is what I am trying to do not supported? I am using version 4.1.7 on FreeBSD 5.3. Best regards, Erich Beyrent -- 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how escape special in a field - fixed ( null plus something = ? )
Gleb, Thanks, eventually I find that the problem is not the special. But the statement concat(field1,',',field2) as something will generate NULL if the field1 is NULL. Not sure if it's documented anywhere, or just a simple programming concept that null plus anything equal null.. -- Original Message --- From: Gleb Paharenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Mon, 13 Dec 2004 13:44:42 +0200 Subject: Re: how escape special in a field Hello. See: http://dev.mysql.com/doc/mysql/en/mysql_real_escape_string.html Can you send complete test for your problem? YW CHAN (Cai Lun e-Business) [EMAIL PROTECTED] wrote: Hi, I find there seems problem with this select statement when there is a special character inside the table. i.e. select concat(field_1, ',', field_2) as name where ... field_2 actually is something like 'George, Banson' ( with a comma in between ) i guess this , destroy the SQL syntax. Is there any function for protecting this situation? Thanks for your help. Regards, CHAN -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] --- End of Original Message --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: Help me optimize this query
Thanks for your inputs Eric. I tried this but it couldn't give me any insight abt how can optimize this for space saving of temp tables. Maybe I am not expert enough to interpret this output, so here it is - - Manish Seeing the explain log, it looks to me like you donot have any index defined on t1 / t2. Having indexes on columns you frequently query on should help reduce space and time. In your case t1.YYY or t2.ZZZ Is there a foreign key relation ship between t1.YYY and t2.ZZZ ? Also, is it not possible to use nested queries in the mysql version you are using. I am not sure from what version nested queries are supported if at all :- select * from t1 where t1.YYY in (select t2.ZZZ from t2 where t2.AAA like '%bla%'); If the nested queries are not supported then you would have to use temporary tables or heap tables in mysql to generate a temporary result by breaking the query. insert into temporary_table select t2.ZZZ from t2 where t2.AAA like '%bla%'; Then use this temporary to join with t1 in an update. Again, before trying this, make sure you have all indexes, foreign keys defined. Defining this itself may solve your problem. Regards, Goutham S Mohan --- Software Engineer, Hewlett Packard [GDIC] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how escape special in a field - fixed ( null plus something = ? )
YW CHAN (Cai Lun e-Business) wrote:
Gleb,
Thanks, eventually I find that the problem is not the special.
But the statement concat(field1,',',field2) as something will
generate NULL if the field1 is NULL.
Not sure if it's documented anywhere, or just a simple programming
concept that null plus anything equal null..
Yes, that is documented. You can use concat_ws(',', field1, field2)
See: http://dev.mysql.com/doc/mysql/en/String_functions.html
HTH,
Wolfram
-- Original Message --- From: Gleb Paharenko
[EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Mon, 13
Dec 2004 13:44:42 +0200 Subject: Re: how escape special in a field
Hello.
See:
http://dev.mysql.com/doc/mysql/en/mysql_real_escape_string.html
Can you send complete test for your problem?
YW CHAN (Cai Lun e-Business) [EMAIL PROTECTED] wrote:
Hi,
I find there seems problem with this select statement when there
is a special character inside the table. i.e.
select concat(field_1, ',', field_2) as name where ...
field_2 actually is something like 'George, Banson' ( with a
comma in
between )
i guess this , destroy the SQL syntax.
Is there any function for protecting this situation?
Thanks for your help.
Regards, CHAN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
