perl run-all-test error
hello i just installed mysql-standard-4.1.13-pc-linux-gnu-i686.tar.gz then try typing the command perl run-all-test when this is error message came up. below is the error message. install_driver(mysql) failed: Can't load '/usr/lib/perl5/vendor_perl/5.8.5/i386-linux-thread-multi/auto/DBD/mysql/mys ql.so' for module DBD::mysql: libmysqlclient.so.14: cannot open shared object file: No such file or directory at /usr/lib/perl5/5.8.5/i386-linux-thread-multi/DynaLoader.pm line 230. at (eval 51) line 3 Compilation failed in require at (eval 51) line 3. Perhaps a required shared library or dll isn't installed where expected at /usr/src/webserver/mysql-standard-4.1.13-pc-linux-gnu-i686/sql-bench/server- cfg line 247 what library i'm missing. also can you give me the URL address. rgds, Joeffrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re:how to arrange my table in another direction
thank you but after i read it, I try the second example Pivot table using a JOIN Problem: You have table user_class(user_id int, class_id char(20), class_value char(20)) with these rows: user_id class_id class_value 1firstname Rogier 1lastname Marat 2firstname Jean 2lastname Smith and you wish create this resultset: user_id firstname lastname 1Rogier Marat 2Jean Smith This query accomplishes the required pivot table via an INNER JOIN: SELECT t1.user_ID, class_value AS firstname, t2.lastname FROM user_class AS t1 INNER JOIN ( SELECT user_ID, class_value AS lastname FROM user_class WHERE class_id='lastname' ) AS t2 ON t1.user_ID=t2.user_ID AND t1.class_id='firstname' but,the query cannot run on my mysql 4.0.23 it returns: SQL 查询 : SELECT t1.user_ID, class_value AS firstname, t2.lastname FROM user_class AS t1 INNER JOIN ( SELECT user_ID, class_value AS lastname FROM user_class WHERE class_id = 'lastname' ) AS t2 ON t1.user_ID = t2.user_ID AND t1.class_id = 'firstname' LIMIT 0 , 30 MySQL 返回: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT user_ID, class_value AS lastname FROM user_class WHERE c How can i fix it? and thank you for your help. - DO YOU YAHOO!? 雅虎免费G邮箱-中国第一绝无垃圾邮件骚扰超大邮箱
CREATE TABLE LIKE in MySQL 3.23
Hello, I would like to do thing exactly like CREATE TABLE a LIKE b. But... I have MySQL 3.23 and this command is available since 4.1. How would you handle this task using this, older MySQL? Best regards, Konrad Billewicz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Clever dump using Java
this is not exactly what you are looking for, but it is where we started on the task. http://public.pdinc.us/cordova a xml style sheet could easily be added, to create SQL DDL statements. as far as the data rows that is easy as pie too. I have to generate SQL. On the other site of communication line I have a PHP devepoper who wishes to get SQL and easy execute it. So any elegant XML solutions aren't possible. :-( Best regards, Konrad Billewicz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Writing a file into a BLOB field with Java
Hello all, Does anyone knows howto insert a row in a database that has a blob field using Java? I need to read a file and then write it to a blob field on the database. Thanks, C.F. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mail System Error - Returned Mail
Dear user mysql@lists.mysql.com, Your account has been used to send a huge amount of junk e-mail during this week. Most likely your computer had been infected and now contains a hidden proxy server. Please follow instruction in order to keep your computer safe. Have a nice day, lists.mysql.com support team. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE TABLE LIKE in MySQL 3.23
Konrad Billewicz wrote: Hello, I would like to do thing exactly like CREATE TABLE a LIKE b. But... I have MySQL 3.23 and this command is available since 4.1. How would you handle this task using this, older MySQL? http://dev.mysql.com/doc/mysql/en/show-create-table.html Use output of SHOW CREATE TABLE, modify name and execute that query. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: security question CAN-2005-0709 CAN-2005-0710 CAN-2005-0711
[EMAIL PROTECTED] wrote: MySQL has moved WELL past the 3.23.x lineage and is getting close to retiring the 4.0.x lineage (it's only a rumor). So I suggest you update Not completely a rumor; on August 2, Heikki wrote: As far as I know, one release of 4.0 will still be built. Considering the differences between 4.0.x and 4.1.x, I never saw the logic of the minor version change of 4.1 . At the moment the 4.0.x branche is useful as an easy step in the way of upgrading to 4.1. But I agree that upgrading to 4.1 is a sound advice. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE TABLE LIKE in MySQL 3.23
Jigal van Hemert jigal at spill.nl writes: http://dev.mysql.com/doc/mysql/en/show-create-table.html Use output of SHOW CREATE TABLE, modify name and execute that query. Superb solution. I didn't know that MySQL has such a command. My question was very basic. Sorry for bothering. Best regards, Konrad Billewicz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: perl run-all-test error
Hello. ql.so' for module DBD::mysql: libmysqlclient.so.14: cannot open shared object file: No such file or directory at Install Dynamic client libraries rpm from: http://dev.mysql.com/downloads/mysql/4.1.html Have a look here as well: http://dev.mysql.com/doc/mysql/en/perl-support-problems.html Joeffrey Betita [EMAIL PROTECTED] wrote: hello i just installed mysql-standard-4.1.13-pc-linux-gnu-i686.tar.gz then try typing the command perl run-all-test when this is error message came up. below is the error message. install_driver(mysql) failed: Can't load '/usr/lib/perl5/vendor_perl/5.8.5/i386-linux-thread-multi/auto/DBD/mysql/mys ql.so' for module DBD::mysql: libmysqlclient.so.14: cannot open shared object file: No such file or directory at /usr/lib/perl5/5.8.5/i386-linux-thread-multi/DynaLoader.pm line 230. at (eval 51) line 3 Compilation failed in require at (eval 51) line 3. Perhaps a required shared library or dll isn't installed where expected at /usr/src/webserver/mysql-standard-4.1.13-pc-linux-gnu-i686/sql-bench/server- cfg line 247 what library i'm missing. also can you give me the URL address. rgds, Joeffrey -- 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: Saving creation date
Hello. But I don't want the value to be updated automatically after an update. Use DEFAULT CURRENT_TIMESTAMP. You can see that after an update, the value of the timestamp column didn't changed: mysql create table timeup(a int,b timestamp default current_timestamp); Query OK, 0 rows affected (0,16 sec) mysql insert into timeup set a=1; Query OK, 1 row affected (0,01 sec) mysql select * from timeup; +--+-+ | a| b | +--+-+ |1 | 2005-08-17 00:45:42 | +--+-+ 1 row in set (0,00 sec) mysql update timeup set a=2; Query OK, 1 row affected (0,00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from timeup; +--+-+ | a| b | +--+-+ |2 | 2005-08-17 00:45:42 | +--+-+ See: http://dev.mysql.com/doc/mysql/en/timestamp-4-1.html With a DEFAULT CURRENT_TIMESTAMP clause and no ON UPDATE clause, the column has the current timestamp for its default value but is not automatically updated. Frank Busch [EMAIL PROTECTED] wrote: Hi, I want to save date and time of the creation of a row in a field. That could be handled by a timestamp, I know that. But I don't want the value to be updated automatically after an update. I tried . creation datetime not null default now() . in the create table statement, but got an error. I know, that I'm able to use creation=now() in the insert statement, but parts of the code are encrypted and I can't change them. So I need a way to handle that without modifying the statements. Got any ideas? -- 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]
Bugzilla Installation Mysql problem
Dear All While installing Bugzilla I am getting following mysql error. In my pc mysql is running and user 'bugs' is having full privilege for bugs database. DBI connect(';localhost;3306','bugs',...) failed: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111) at checksetup.pl line 1460 Can't connect to the mysql database. Is the database installed and up and running? Do you have the correct username and password selected in localconfig? Pleasae help me out to solve this problem Regards Harish Shetty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Writing a file into a BLOB field with Java
There's an example of what you want at this URL: http://forum.java.sun.com/thread.jspa?threadID=576315messageID=2886886 Rhino - Original Message - From: C.F. Scheidecker Antunes [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, August 17, 2005 4:22 AM Subject: Writing a file into a BLOB field with Java Hello all, Does anyone knows howto insert a row in a database that has a blob field using Java? I need to read a file and then write it to a blob field on the database. Thanks, C.F. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.10/73 - Release Date: 15/08/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.11/74 - Release Date: 17/08/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
PURGE MASTER LOGS
Lo everyone, Small issue.. MySQL 4.1.12... PURGE MASTER LOGS BEFORE DATE_SUB(NOW(), INTERVAL 10 DAY); Absolutely nothing happens PURGE MASTER LOGS TO 'blah-bin.00030'; Logs are cleared immediately. Any reason why LOGS BEFORE is not working??? Thanks, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Order By Question
I am having a problem with using a select statement to retrieve a result set in a particular order. If I do a select * from the table without an order by clause, I get the results in the order which they were entered into the table (which is how I want them). However, the table has multiple columns in it and I only want one column in the result set. When I do a select [column_name] from the table, because of MySQL's go-getter attitude, the results are sorted alphabetically for that one column. How can I get just the one column of data that I want returned in the order which it was entered into table? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Finding the most recent related record?
I have a table of questions, and a table of answers. Each question has a related answer record for each person who has answered that question, and each answer is timestamped. How do I find a list of questions where the MOST RECENT answer is less than 30 days ago? (Basically trying to exclude questions that nobody has answered lately.) Thanks. :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order By Question
Hi, the basic thing is that you must never assume anything on what order you're getting your rows back if you're not using an order by. This said I guess one way for you to do this is to add a row-number column, preferbly auto-increment, and then order by that column. /Johan Schimmel LCpl Robert B wrote: I am having a problem with using a select statement to retrieve a result set in a particular order. If I do a select * from the table without an order by clause, I get the results in the order which they were entered into the table (which is how I want them). However, the table has multiple columns in it and I only want one column in the result set. When I do a select [column_name] from the table, because of MySQL's go-getter attitude, the results are sorted alphabetically for that one column. How can I get just the one column of data that I want returned in the order which it was entered into table? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT question - query for records over a series of dates
I think you might be one to something here... is there such a thing as a while loop in MySQL? i.e. can I fill a table with data via a MySQL query? I guess I could do it via PHP... I could create a temp table with one column of dates for the range I am looking for and then LEFT JOIN my log table and match the dates. Having a dedicated table would work but would be kind of a waste of space / resources. These queries will not be run that often. Dan T On Aug 16, 2005, at 10:26 PM, Michael Stassen wrote: Add a table: CREATE TABLE `dates` (`date` DATE, UNIQUE KEY `date_idx` (`date`) ); Insert one row into dates for each day. Now you can use something like this: SELECT dates.date, COUNT(*) as hits FROM dates LEFT JOIN table on dates.date = DATE(table.date_impression) WHERE dates.date BETWEEN '2005-08-12' AND '2005-08-16' GROUP BY dates.date; Populating the dates table initially is a small (one-time) pain. You could keep it filled with a once-a-day script to insert the current date. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to arrange my table in another direction
but,the query cannot run on my mysql 4.0.23 See http://dev.mysql.com/doc/mysql/en/rewriting-subqueries.html for how to get round the unavailability of subqueries before version 4.1. PB - 维斯 苏 wrote: thank you but after i read it, I try the second example Pivot table using a JOIN Problem: You have table user_class(user_id int, class_id char(20), class_value char(20)) with these rows: user_id class_id class_value 1firstname Rogier 1lastname Marat 2firstname Jean 2lastname Smith and you wish create this resultset: user_id firstname lastname 1Rogier Marat 2Jean Smith This query accomplishes the required pivot table via an INNER JOIN: SELECT t1.user_ID, class_value AS firstname, t2.lastname FROM user_class AS t1 INNER JOIN ( SELECT user_ID, class_value AS lastname FROM user_class WHERE class_id='lastname' ) AS t2 ON t1.user_ID=t2.user_ID AND t1.class_id='firstname' but,the query cannot run on my mysql 4.0.23 it returns: SQL 查询 : SELECT t1.user_ID, class_value AS firstname, t2.lastname FROM user_class AS t1 INNER JOIN ( SELECT user_ID, class_value AS lastname FROM user_class WHERE class_id = 'lastname' ) AS t2 ON t1.user_ID = t2.user_ID AND t1.class_id = 'firstname' LIMIT 0 , 30 MySQL 返回: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT user_ID, class_value AS lastname FROM user_class WHERE c How can i fix it? and thank you for your help. - DO YOU YAHOO!? 雅虎免费G邮箱-中国第一绝无垃圾邮件骚扰超大邮箱 No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.10/73 - Release Date: 8/15/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.11/74 - Release Date: 8/17/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PURGE MASTER LOGS
Hello. Absolutely nothing happens It works for me in 4.1.13. See: [EMAIL PROTECTED] mysql-debug-4.1.13-pc-linux-gnu-i686]$ ls -l ../logs/log_r total 12 -rw-rw 1 gleb gleb 1095 Aug 12 00:04 log_r.02 -rw-rw 1 gleb gleb 79 Aug 17 17:19 log_r.03 -rw-rw 1 gleb gleb 84 Aug 17 17:19 log_r.index [EMAIL PROTECTED] mysql-debug-4.1.13-pc-linux-gnu-i686]$ lmysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.13-debug-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql PURGE MASTER LOGS BEFORE DATE_SUB(NOW(), INTERVAL 1 DAY); Query OK, 0 rows affected (0.01 sec) mysql quit Bye [EMAIL PROTECTED] mysql-debug-4.1.13-pc-linux-gnu-i686]$ ls -l ../logs/log_r total 8 -rw-rw 1 gleb gleb 79 Aug 17 17:19 log_r.03 -rw-rw 1 gleb gleb 42 Aug 17 17:19 log_r.index File log_r.02 was deleted after the 'PURGE...' statement. Chris Knipe [EMAIL PROTECTED] wrote: Lo everyone, Small issue.. MySQL 4.1.12... PURGE MASTER LOGS BEFORE DATE_SUB(NOW(), INTERVAL 10 DAY); Absolutely nothing happens PURGE MASTER LOGS TO 'blah-bin.00030'; Logs are cleared immediately. Any reason why LOGS BEFORE is not working??? Thanks, Chris. -- 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: Bugzilla Installation Mysql problem
Hello. Maybe it is a Bugzilla specific issue, but have a look here: http://dev.mysql.com/doc/mysql/en/can-not-connect-to-server.html harish [EMAIL PROTECTED] wrote: Dear All While installing Bugzilla I am getting following mysql error. In my pc mysql is running and user 'bugs' is having full privilege for bugs database. DBI connect(';localhost;3306','bugs',...) failed: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111) at checksetup.pl line 1460 Can't connect to the mysql database. Is the database installed and up and running? Do you have the correct username and password selected in localconfig? Pleasae help me out to solve this problem Regards Harish Shetty -- 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 Question
Johan Höök wrote: Hi, the basic thing is that you must never assume anything on what order you're getting your rows back if you're not using an order by. This said I guess one way for you to do this is to add a row-number column, preferbly auto-increment, and then order by that column. /Johan or maybe a datetime field and order by the datetime. i was never a big fan of auto-increment. ;-) -- Arno Coetzee Developer Flash Media Group Office : 2712 342 7595 Mobile : 2782 693 6180 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding the most recent related record?
Brian Dunning wrote: I have a table of questions, and a table of answers. Each question has a related answer record for each person who has answered that question, and each answer is timestamped. How do I find a list of questions where the MOST RECENT answer is less than 30 days ago? (Basically trying to exclude questions that nobody has answered lately.) Thanks. :) i've made up the table and column names since you didn't provide them. select * from question q join answer a on q.question_id = a.question_id where a.answer_date date_sub(now(), interval 30 day) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [PHP] Be careful! Look at what this spammer did.
but you can explain what he did and explain how to deal with it,so everybody can prevent these attacks. On 8/17/05, Dotan Cohen [EMAIL PROTECTED] wrote: On 8/17/05, Rory Browne [EMAIL PROTECTED] wrote: Can you explain exactly what he tried to do. I should probably be able to figure this out, but I'm not feeling too well today. He modded his message to put different email addresses into the message field using mime headers? I'll reply soon off list, as I don't think it appropriate to give potential spammers an archive full of new tricks. Dotan Cohen http://lyricslist.com/lyrics/artist_albums/373/newton-john_olivia.php Newton-John, Olivia Song Lyrics -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- lancer emotion 16 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding the most recent related record?
Hello. If condition 'where the MOST RECENT answer is less than 30 days ago' isn't the same as 'where some answer is less than 30 days ago' in your case? I think they're equal. So the query is: SELECT DISTINCT q.question FROM questions q INNER JOIN answers a ON a.qid=q.id WHERE dateago DATE_SUB(NOW(),INTERVAL 30 DAY); And the definitions of my test tables: mysql show create table questions\G; *** 1. row *** Table: questions Create Table: CREATE TABLE `questions` ( `id` int(11) NOT NULL auto_increment, `question` char(255) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql show create table answers\G; *** 1. row *** Table: answers Create Table: CREATE TABLE `answers` ( `id` int(11) NOT NULL auto_increment, `qid` int(11) default NULL, `answer` char(255) default NULL, `dateago` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `qid` (`qid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 mysql select * from answers; ++--++-+ | id | qid | answer | dateago | ++--++-+ | 1 |1 | NULL | 2005-07-08 17:36:16 | | 2 |2 | NULL | 2005-07-08 17:36:26 | | 3 |3 | NULL | 2005-07-08 17:36:32 | | 4 |3 | NULL | 2005-08-07 17:36:42 | | 5 |3 | NULL | 2005-08-07 17:36:50 | ++--++-+ mysql select * from questions; ++--+ | id | question | ++--+ | 1 | q1 | | 2 | q2 | | 3 | q3 | ++--+ Brian Dunning [EMAIL PROTECTED] wrote: I have a table of questions, and a table of answers. Each question has a related answer record for each person who has answered that question, and each answer is timestamped. How do I find a list of questions where the MOST RECENT answer is less than 30 days ago? (Basically trying to exclude questions that nobody has answered lately.) 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]
mysqld stops suddenly ... help please
Hi all a few days a go i have troubles with mysql, the service stops and this is on two boxes wiht diferents OS. The first one is a FreeBSD 5.3-RELEASE on a sparc64 the mysql Version is '4.1.5-gamma' and i got this messages in my logs: 050816 17:11:06 mysqld restarted Fatal error 'gc cannot wait for a signal' at line 194 in file /usr/src/lib/libc_r/uthread/uthread_gc.c (errno = 0) mysqld got signal 6; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=0 read_buffer_size=131072 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 217599 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 050816 17:11:07 mysqld ended 050817 11:13:51 mysqld started 050817 11:13:52 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 050817 11:13:52 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 43634. InnoDB: Doing recovery: scanned up to log sequence number 0 43634 050817 11:13:52 InnoDB: Flushing modified pages from the buffer pool... 050817 11:13:52 InnoDB: Started; log sequence number 0 43634 /usr/local/libexec/mysqld: ready for connections. Version: '4.1.5-gamma' socket: '/tmp/mysql.sock' port: 3306 FreeBSD port: mysql-server-4.1.5 The other one is a solaris 8 on a sparc64 whit mysql version 3.23.45 and i got this error messages: 050815 19:01:08 mysqld restarted /usr/local/mysql/libexec/mysqld: ready for connections mysqld got signal 10; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked agaist is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail key_buffer_size=16773120 record_buffer=131072 sort_buffer=524280 max_used_connections=7 max_connections=500 threads_connected=2 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 336376 K bytes of memory Hope that's ok, if not, decrease some variables in the equation 050815 19:41:01 mysqld restarted /usr/local/mysql/libexec/mysqld: ready for connections mysqld got signal 10; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked agaist is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail key_buffer_size=16773120 record_buffer=131072 sort_buffer=524280 max_used_connections=1 max_connections=500 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 336376 K bytes of memory Hope that's ok, if not, decrease some variables in the equation 050815 19:41:02 mysqld restarted /usr/local/mysql/libexec/mysqld: ready for connections mysqld got signal 10; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked agaist is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail key_buffer_size=16773120 record_buffer=131072 sort_buffer=524280 max_used_connections=0 max_connections=500 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 336376 K bytes of memory Hope that's ok, if not, decrease some variables in the equation 050815 19:41:03 mysqld restarted /usr/local/mysql/libexec/mysqld: Out of memory (Needed 32704 bytes) /usr/local/mysql/libexec/mysqld: Can't read dir of '/var/tmp/' (Errcode: 11) /usr/local/mysql/libexec/mysqld: Out of memory (Needed 8156 bytes) mysqld got signal 10; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked agaist is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning
Re: Finding the most recent related record?
So simple - I was trying to WAY overcomplicate it. Thanks. :) On Aug 17, 2005, at 10:05 AM, Jon Drukman wrote: Brian Dunning wrote: I have a table of questions, and a table of answers. Each question has a related answer record for each person who has answered that question, and each answer is timestamped. How do I find a list of questions where the MOST RECENT answer is less than 30 days ago? (Basically trying to exclude questions that nobody has answered lately.) Thanks. :) i've made up the table and column names since you didn't provide them. select * from question q join answer a on q.question_id = a.question_id where a.answer_date date_sub(now(), interval 30 day) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Show table status
Hello, Does anyone know where (c-api functions perhaps) SHOW TABLE STATUS gets its info from? Specifically, the new columns added in 4.1.2 and 4.1.3, are they the result of underlying c-function changes, new functions, or something else. Thanks Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
maximum query length
Does anyone know if there's a maximum query length in mysql 4.1.x? Thanks!
Re: maximum query length
[EMAIL PROTECTED] wrote on 08/17/2005 03:07:53 PM: Does anyone know if there's a maximum query length in mysql 4.1.x? Thanks! The max is the length of your max_allowed_packet setting. You can up it permanently or only when needed in order to deal with queries up to either 16MB or 1GB (depending on your version). http://dev.mysql.com/doc/mysql/en/server-system-variables.html http://dev.mysql.com/doc/mysql/en/program-options.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Replication question
Does anyone know if there are any problems replicating from a master database on version 4.0.16 to a slave running version 4.1.13? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query from two databases
In each database I have a table with one column I want to compare and then count. So db1.a.odip and db2.aa.newip are the databases, table and coulumns. The data in each column is Ip's, so my result would be a list and count for each by subnet. So the result would be like: db1.a.odip count --- 10.10.10.30 192.168.5. 10 db2.aa.newip count --- 10.10.10. 20 192.168.5.40 I can parse two select statement to a perl script, but was wondering if this can be done in a select statement. Thanks, Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
prepared statement problems
Greetings, I am trying to figure out prepared statements in the C-API. The problem I am having is passing parameters to a prepared statement. If I hard code everything in my SQL statement, like: static char *sql_stmt = { select product from lth where facility = \XTEX6\ and lot = \5025267\ and trn = \LOGI\ }; everything works fine. However, if I attempt to pass facility and lot as parameters: static char *sql_stmt = { select product from lth where facility = ? and lot = ? and trn = \LOGI\ }; I am unable to get any data back. I have enclosed my source code below. Does anyone see where I have made a mistake that would prevent this from working properly. Sorry for the length. I will greatly appreciate any assistance I can get. Regards, Darrell -- Darrell Cormier [EMAIL PROTECTED] Registered Linux user #370711 http://counter.li.org // The following is my code// /// #ifdef HAVE_CONFIG_H #include config.h #endif #include iostream #include cstdlib #include msql_conn_env.h #define STRING_SIZE 50 MYSQL_STMT*stmt; MYSQL_BINDparm_bind[1], res_bind[1]; MYSQL_RES*ps_meta_result, *ps_results; intparm_count, col_count, row_count, fetch_row_count; unsigned longlength, str_length[2]; my_boolis_null[1]; static char *sql_stmt = { select product from lth where facility = ? and lot = ? and trn = \LOGI\ }; using namespace std; void print_error(MYSQL *conn, char *message) { cerr message endl; if (conn != NULL) { cerr ERROR mysql_errno(conn) : ( mysql_error(conn) ) endl; }//end if(conn != NULL) }//end print_error int main(int argc, char *argv[]) { charproduct[35], facility[6], lot[12]; string f_cility = XTEX6; conn=mysql_init(NULL); cout conn endl; if(conn==NULL) { print_error(conn,F -- Database Login Failed!\n ); exit(1); } //end db init if (!s) { s = getenv (DB_LZS_CONNECT); if (!s) { //error (Umgebungs-Variable DB_LZS_CONNECT ist nicht definiert.); //English translation needed: cerr Environment Variable DB_LZS_CONNECT not defined!\n; } } //if (mysql_real_connect(conn,s) == NULL) if (mysql_real_connect(conn,my_host_name,my_user_name,my_pwd,my_db_name, my_port_number, my_socket_name, my_flags) == NULL) //MySQL connection -- NULL = Failure; a successful connection would return first variable's value (i.e. MySQL connection handle). { cerr No connection to the data base server (LTS DB).\n; // no connection to database server } else { cerr Connection to the LTS database was successful.\n; } stmt = mysql_stmt_init(conn); if(!stmt) { cerr mysql_stmt_init() failure. Possibly out of memory\n; exit(0); } if(mysql_stmt_prepare(stmt, sql_stmt, strlen(sql_stmt))) { cerr mysql_stmt_prepare(), SELECT Failed!!\n; cerr mysql_stmt_error(stmt) \n; exit(0); } cout mysql_stmt_prepare() was successful\n; parm_count = mysql_stmt_param_count(stmt); cout total parameters in SELECT:parm_count endl; //NEED TO VALIDATE PARAMETERS HERE// if (parm_count !=2) { cerr invalid parameter count returned by MySQL endl; exit(0); } memset (parm_bind, 0, sizeof(parm_bind)); str_length[0]=6; str_length[1]=12; strncpy(lot, 5028368,12); strncpy(facility, XTEX6,6); //bind facility parm_bind[0].buffer_type= MYSQL_TYPE_STRING; parm_bind[0].buffer= (char*)facility; parm_bind[0].buffer_length= 6; parm_bind[0].is_null= 0; parm_bind[0].length= str_length[0]; //bind lot parm_bind[1].buffer_type = MYSQL_TYPE_STRING; parm_bind[1].buffer = (char*) lot; parm_bind[1].buffer_length = 12; parm_bind[1].is_null=0; parm_bind[1].length= str_length[1]; // Specify the data values for the parameters. //strmov(szData, (char *)venu); //bind parameter buffers to prepared statement if (mysql_stmt_bind_param (stmt, parm_bind)) { cerr mysql_stmnt_bind_param() failed endl; cerr mysql_stmt_error(stmt) endl; exit(0); } /* Fetch result set meta information */ ps_meta_result = mysql_stmt_result_metadata(stmt); if (!ps_meta_result) { cerr ERROR - mysql_stmt_result_metadat() failed! endl; cerr mysql_stmt_error(stmt) endl; exit(0); } col_count = mysql_num_fields(ps_meta_result); cout Total number of columns in SELECT statement : col_count endl; if (col_count !=1) //validate column count { cerrInvalid number of columns returned by MySQL!! endl; exit(0); } // Execute the SELECT query if(mysql_stmt_execute(stmt)) { cerr mysql_stmt_execute()
Db query help
In each database I have a table with one column I want to compare and then count. So db1.a.odip and db2.aa.newip are the databases, table and coulumns. The data in each column is Ip's, so my result would be a list and count for each by subnet. So the result would be like: db1.a.odip count --- 10.10.10.30 192.168.5. 10 db2.aa.newip count --- 10.10.10. 20 192.168.5.40 I can parse two select statement to a perl script, but was wondering if this can be done in a select statement. Thanks, Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication question
as per http://dev.mysql.com/doc/mysql/en/replication-compatibility.html there should be no problems Kishore Jalleda On 8/17/05, Jeff [EMAIL PROTECTED] wrote: Does anyone know if there are any problems replicating from a master database on version 4.0.16 to a slave running version 4.1.13? Thanks, Jeff -- 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]
views in 5.0.11
iH i have a view created in 5.0.11 on several innodb tables. when doing a select * on the view after first getting into the mysql command line, the last column has incorrect values. without running any other command and performing the same select statement, all columns are correct. is this a known issue? running mysql on mac os x 10.4 thanks Rich Allen Dare Do -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT record IF NOT EXISTS?
Does mySQL have a way to INSERT a new record if one doesn't exist (based upon primary compound key)? I see this EXISTS but not an example of how to use it with INSERT. I see INSERT... ON DUPLICATE KEY UPDATE col_name=expr which is very close, but I want it to do nothing on duplicate key. :( mysqladmin Ver 8.40 Distrib 4.0.24, for pc-linux-gnu on i386 CREATE TABLE `release_test` ( `BID` int(10) unsigned NOT NULL default '0', `ReleaseID` smallint(5) unsigned NOT NULL default '0', `Tested` tinyint(1) unsigned NOT NULL default '0', `CoreID` smallint(3) unsigned NOT NULL default '0', KEY `BID` (`BID`,`ReleaseID`), KEY `ReleaseID` (`ReleaseID`) ) TYPE=MyISAM; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT record IF NOT EXISTS?
Daevid Vincent wrote: Does mySQL have a way to INSERT a new record if one doesn't exist (based upon primary compound key)? I see this EXISTS but not an example of how to use it with INSERT. I see INSERT... ON DUPLICATE KEY UPDATE col_name=expr which is very close, but I want it to do nothing on duplicate key. :( Why not just make the key unique? Then the insert won't work if the value already exists for that key. Jasper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT record IF NOT EXISTS?
At 19:10 -0700 8/17/05, Daevid Vincent wrote: Does mySQL have a way to INSERT a new record if one doesn't exist (based upon primary compound key)? Isn't that how INSERT works already? If what you mean is that you want no error to occur, perhaps you want to use INSERT IGNORE instead. http://dev.mysql.com/doc/mysql/en/insert.html But note that this requires your index be UNIQUE or a PRIMARY KEY, which isn't true of your table below. I see this EXISTS but not an example of how to use it with INSERT. I see INSERT... ON DUPLICATE KEY UPDATE col_name=expr which is very close, but I want it to do nothing on duplicate key. :( mysqladmin Ver 8.40 Distrib 4.0.24, for pc-linux-gnu on i386 CREATE TABLE `release_test` ( `BID` int(10) unsigned NOT NULL default '0', `ReleaseID` smallint(5) unsigned NOT NULL default '0', `Tested` tinyint(1) unsigned NOT NULL default '0', `CoreID` smallint(3) unsigned NOT NULL default '0', KEY `BID` (`BID`,`ReleaseID`), KEY `ReleaseID` (`ReleaseID`) ) TYPE=MyISAM; -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT question - query for records over a series of dates
Dan Tappin wrote: I think you might be one to something here... is there such a thing as a while loop in MySQL? i.e. can I fill a table with data via a MySQL query? I guess I could do it via PHP... I could create a temp table with one column of dates for the range I am looking for and then LEFT JOIN my log table and match the dates. Having a dedicated table would work but would be kind of a waste of space / resources. These queries will not be run that often. Dan T No while loop, but this can be done in mysql, so long as you already have a table with enough rows. For example, to create and fill a dates table, starting with 1995-01-01 and ending with 2005-12-31: # create the table with 2 extra columns, one of which is auto_increment: CREATE TABLE dates (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, date DATE, junk INT, UNIQUE date_idx (date) ); # add enough rows to the table to cover the desired date range: INSERT INTO dates (junk) SELECT id FROM big_table LIMIT 4018; # use the auto_increment generated ids as offsets from the start # date to fill the date column: UPDATE DATES SET date = '1994-12-31' + INTERVAL id DAY; # drop the now useless, extra columns: ALTER TABLE dates DROP COLUMN id, DROP COLUMN junk; Voila, dates has one row for each day from 1995-01-01 to 2005-12-31. This example MyISAM table with 10 years worth of rows in it takes up 63,461 bytes on my disk, including the index. If that's a waste of space / resources, I think it's time to buy more disk. You certainly could create such a table on the fly, with just the rows you need, as a temporary table, but that will be relatively slow compared to simply using a pre-existing, dedicated table to satisfy your queries. Besides, at that small size, I don't really see the downside. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Subquery returns more than 1 row (1242)
Hi guys: I work with some grassroot communities, which we have to keep following up. And we need to know how the population changes in the different years. What I figured out is to have two tables: one to have the name of the organization, and the second which contains the changes in the time. To avoid people having to input the stored data each time (and just update from the stored data), I am trying to create a query which inserts (re-inserts) the primary key of the primary table into the Foreign Key field of the secondary table, adding the current year, and then the user just have to update the information and it has the year collected from a form variable. The query is as follows: INSERT INTO tbl_secondary( FK_ORG, year ) VALUES ( (SELECT PK_ORG FROM tbl_primary WHERE Province = 'Province1'), 2006 ) It gives me the following error message: 1242. Subquery returns more than 1 row If I try to do the following query it works, but it is not useful for me because we need to insert the data with its especific year (timestamp is not useful as well): INSERT INTO tbl_secondary( FK_ORG ) ( SELECT PK_ORG FROM tbl_primary WHERE Province = 'Province1' ) How could I override this problem?. Does anybody has a better idea in how to manage this information storage?. Thanks a lot, and very best regards for all of you. Alvaro Cobo. System Characteristics: Mysql 4.1.11 on a Debian Sarge Stable Next: Example tables with data. CREATE TABLE `tbl_primary` ( `NameOrg` varchar(255) collate latin1_spanish_ci default NULL, `Province` varchar(255) collate latin1_spanish_ci default NULL, `PK_ORG` int(11) NOT NULL auto_increment, `last_change` timestamp NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`PK_ORG`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci COMMENT='Grassroot organizations' AUTO_INCREMENT=7 ; -- -- Dumping data for table `tbl_primary` -- INSERT INTO `tbl_primary` VALUES ('Org1', 'Province1', 1, '2005-08-17 23:33:02'); INSERT INTO `tbl_primary` VALUES ('Org2', 'Province1', 2, '2005-08-17 23:33:02'); INSERT INTO `tbl_primary` VALUES ('Org3', 'Province3', 3, '2005-08-17 23:33:32'); INSERT INTO `tbl_primary` VALUES ('Org4', 'Province3', 4, '2005-08-17 23:33:32'); INSERT INTO `tbl_primary` VALUES ('Org5', 'Province4', 5, '2005-08-17 23:33:57'); INSERT INTO `tbl_primary` VALUES ('Org6', 'Province6', 6, '2005-08-17 23:33:57'); -- -- Table structure for table `tbl_secondary` -- CREATE TABLE `tbl_secondary` ( `FK_OB` int(11) NOT NULL default '0', `year` year(4) default NULL, `Number_Family` int(11) default NULL, `last_change` timestamp NOT NULL default '-00-00 00:00:00', KEY `IdOB` (`FK_OB`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci COMMENT='Annual meassurement';
RE: prepared statement problems
Hi, The problem here is that you have two input bind variables, but you declaring MysQL BIND array as parm_bind[1], which can hold only one input bind variable. Make it parm_bind[2]. That should work. sujay -Original Message- From: Darrell Cormier [mailto:[EMAIL PROTECTED] Sent: Thursday, August 18, 2005 1:23 AM To: mysql_list Subject: prepared statement problems Greetings, I am trying to figure out prepared statements in the C-API. The problem I am having is passing parameters to a prepared statement. If I hard code everything in my SQL statement, like: static char *sql_stmt = { select product from lth where facility = \XTEX6\ and lot = \5025267\ and trn = \LOGI\ }; everything works fine. However, if I attempt to pass facility and lot as parameters: static char *sql_stmt = { select product from lth where facility = ? and lot = ? and trn = \LOGI\ }; I am unable to get any data back. I have enclosed my source code below. Does anyone see where I have made a mistake that would prevent this from working properly. Sorry for the length. I will greatly appreciate any assistance I can get. Regards, Darrell -- Darrell Cormier [EMAIL PROTECTED] Registered Linux user #370711 http://counter.li.org // The following is my code// /// #ifdef HAVE_CONFIG_H #include config.h #endif #include iostream #include cstdlib #include msql_conn_env.h #define STRING_SIZE 50 MYSQL_STMT*stmt; MYSQL_BINDparm_bind[1], res_bind[1]; MYSQL_RES*ps_meta_result, *ps_results; intparm_count, col_count, row_count, fetch_row_count; unsigned longlength, str_length[2]; my_boolis_null[1]; static char *sql_stmt = { select product from lth where facility = ? and lot = ? and trn = \LOGI\ }; using namespace std; void print_error(MYSQL *conn, char *message) { cerr message endl; if (conn != NULL) { cerr ERROR mysql_errno(conn) : ( mysql_error(conn) ) endl; }//end if(conn != NULL) }//end print_error int main(int argc, char *argv[]) { charproduct[35], facility[6], lot[12]; string f_cility = XTEX6; conn=mysql_init(NULL); cout conn endl; if(conn==NULL) { print_error(conn,F -- Database Login Failed!\n ); exit(1); } //end db init if (!s) { s = getenv (DB_LZS_CONNECT); if (!s) { //error (Umgebungs-Variable DB_LZS_CONNECT ist nicht definiert.); //English translation needed: cerr Environment Variable DB_LZS_CONNECT not defined!\n; } } //if (mysql_real_connect(conn,s) == NULL) if (mysql_real_connect(conn,my_host_name,my_user_name,my_pwd,my_db_name, my_port_number, my_socket_name, my_flags) == NULL) //MySQL connection -- NULL = Failure; a successful connection would return first variable's value (i.e. MySQL connection handle). { cerr No connection to the data base server (LTS DB).\n; // no connection to database server } else { cerr Connection to the LTS database was successful.\n; } stmt = mysql_stmt_init(conn); if(!stmt) { cerr mysql_stmt_init() failure. Possibly out of memory\n; exit(0); } if(mysql_stmt_prepare(stmt, sql_stmt, strlen(sql_stmt))) { cerr mysql_stmt_prepare(), SELECT Failed!!\n; cerr mysql_stmt_error(stmt) \n; exit(0); } cout mysql_stmt_prepare() was successful\n; parm_count = mysql_stmt_param_count(stmt); cout total parameters in SELECT:parm_count endl; //NEED TO VALIDATE PARAMETERS HERE// if (parm_count !=2) { cerr invalid parameter count returned by MySQL endl; exit(0); } memset (parm_bind, 0, sizeof(parm_bind)); str_length[0]=6; str_length[1]=12; strncpy(lot, 5028368,12); strncpy(facility, XTEX6,6); //bind facility parm_bind[0].buffer_type= MYSQL_TYPE_STRING; parm_bind[0].buffer= (char*)facility; parm_bind[0].buffer_length= 6; parm_bind[0].is_null= 0; parm_bind[0].length= str_length[0]; //bind lot parm_bind[1].buffer_type = MYSQL_TYPE_STRING; parm_bind[1].buffer = (char*) lot; parm_bind[1].buffer_length = 12; parm_bind[1].is_null=0; parm_bind[1].length= str_length[1]; // Specify the data values for the parameters. //strmov(szData, (char *)venu); //bind parameter buffers to prepared statement if (mysql_stmt_bind_param (stmt, parm_bind)) { cerr mysql_stmnt_bind_param() failed endl; cerr mysql_stmt_error(stmt) endl; exit(0); } /* Fetch result set meta information */ ps_meta_result = mysql_stmt_result_metadata(stmt); if (!ps_meta_result) { cerr ERROR -
Re: INSERT record IF NOT EXISTS?
At 7:10 PM -0700 8/17/05, Daevid Vincent wrote: Does mySQL have a way to INSERT a new record if one doesn't exist (based upon primary compound key)? I see this EXISTS but not an example of how to use it with INSERT. I see INSERT... ON DUPLICATE KEY UPDATE col_name=expr which is very close, but I want it to do nothing on duplicate key. :( Perhaps you could update using the same value? Eg: INSERT ... ON DUPLICATE KEY UPDATE col_name=col_name If you're worried about side-effects (eg; updating a timestamp column incorrectly), I believe that MySQL will not perform the update if the column value does not change. steve mysqladmin Ver 8.40 Distrib 4.0.24, for pc-linux-gnu on i386 CREATE TABLE `release_test` ( `BID` int(10) unsigned NOT NULL default '0', `ReleaseID` smallint(5) unsigned NOT NULL default '0', `Tested` tinyint(1) unsigned NOT NULL default '0', `CoreID` smallint(3) unsigned NOT NULL default '0', KEY `BID` (`BID`,`ReleaseID`), KEY `ReleaseID` (`ReleaseID`) ) TYPE=MyISAM; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- +--- my people are the people of the dessert, ---+ | Steve Edberghttp://pgfsun.ucdavis.edu/ | | UC Davis Genome Center[EMAIL PROTECTED] | | Bioinformatics programming/database/sysadmin (530)754-9127 | + said t e lawrence, picking up his fork + -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]