Newbie-question: Error when compiling MySQL 4.1.9 with BerkeleyDB 4.3.27
Hi,
greetings to the list.
I'm in process of compiling MySQL 4.1.9 using BerkeleyDB 4.3.27.
When i start make, after configuring i get the following
error-message :
--snipp--
ha_berkeley.cc:103: error: type specifier omitted for parameter `db_notices'
ha_berkeley.cc:103: error: syntax error before `)' token
--snipp--
As db_notices is nowhere declared i
look around in the sources and find the following in
/bdb/build_win32/db.h :
--snipp--
typedef enum
{
DB_NOTICE_LOGFILE_CHANGED
} db_notices;
--snipp--
So i put it into ha_berkeley.h
and it seems to work , but then the next errormessages
come up , which haven't such a simple solution :
--snipp--
ha_berkeley.cc:139: error: invalid conversion from `void (*)(const char*,
char*)' to `void (*)(const DB_ENV*, const char*, const char*)'
ha_berkeley.cc:141: error: `set_noticecall' undeclared (first use this
function)
ha_berkeley.cc:141: error: (Each undeclared identifier is reported only once
for each function it appears in.)
ha_berkeley.cc:150: error: `DB_VERB_CHKPOINT' undeclared (first use this
function)
ha_berkeley.cc: In function `int berkeley_commit(THD*, void*)':
ha_berkeley.cc:215: error: `txn_commit' undeclared (first use this function)
ha_berkeley.cc: In function `int berkeley_rollback(THD*, void*)':
ha_berkeley.cc:228: error: `txn_abort' undeclared (first use this function)
ha_berkeley.cc: At global scope:
ha_berkeley.cc:291: error: type specifier omitted for parameter `db_notices'
ha_berkeley.cc:291: error: syntax error before `)' token
ha_berkeley.cc: In function `void berkeley_noticecall(...)':
ha_berkeley.cc:293: error: `notice' undeclared (first use this function)
ha_berkeley.cc:295: error: `DB_NOTICE_LOGFILE_CHANGED' undeclared (first use
this function)
ha_berkeley.cc: In function `int berkeley_cmp_packed_key(DB*, const
DBT*, const
DBT*)':
ha_berkeley.cc:387: error: 'const struct __db_dbt' has no member named '
app_private'
ha_berkeley.cc:387: error: 'const struct __db_dbt' has no member named '
app_private'
ha_berkeley.cc: In member function `virtual int ha_berkeley::open(const
char*,
int, unsigned int)':
ha_berkeley.cc:547: error: `txn_begin' undeclared (first use this
function ..
--snipp (and so on) --
So, does anybody know a solution for this ?
My setup :
Suse Linux 9.1
BDB 4.3.27(installed in /usr/local/bdb-4.3.9, symlinked to
/usr/local/bdb-4.3)
MySQL 4.1.9
Here's how i configure (i always create a little script):
--snipp--
#!/bin/sh
#
# configure make template-script
#
# generated 2005.01.19 19:24,06 by make-mk
#
# (c) H. Rueter 01/2005
#
CPPFLAGS=-I/usr/local/bdb-4.3/include
LDFLAGS=-L/usr/local/bdb-4.3/lib
export CPPFLAGS LDFLAGS
make clean
rm config.cache
./configure \
--prefix=/usr/local/mysql-4.1.9 \
--mandir=/usr/man \
--enable-shared \
--enable-static \
--enable-thread-safe-client \
--enable-assembler \
--enable-local-infile \
--with-isam \
--with-pthread \
--with-unix-socket-path=/var/run/mysql.sock \
--with-mysqld-user=mysql \
--with-openssl=/usr \
--with-extra-charsets=all \
--with-berkeley-db=/usr/local/bdb-4.3 \
--with-berkeley-db-includes=/usr/local/bdb-4.3/include \
--with-berkeley-db-libs=/usr/local/bdb-4.3/lib \
make checkinstall
--snipp--
Thanks in advance
Harry
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Licencing
Hello, we're about to start using MySQL as our main database server for our web-based applications, so I started looking at the commercial licencing. My question is this: Are we allowed to upgrade from 4.0 to 4.1 or 5.0 free of charge? Or do we have to purchase another licence when moving between versions? Also, i'm unsure whether to purchase the MySQL Pro licence, as we've not used InnoDB yet, but I see that MySQL 4.1 uses it by default. I've noticed that it has cascading delete and updates... But don't know much else about it. Thanks for any help you can offer. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
optimize mysql 5.01
HI, Currently 16 records in a table slow down the search alot. Most of the queries are based on the date field. so I m adding index on it at the moment. I m wondering how much speed I can gain after finshed the index. Apart from indexing, what other alternativies I can use to speed up the search? Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bad query, locking the server
Hi, sometimes I run a query with LEFT JOIN on a big table and that causes 100% CPU usage on our windows 2003 server. during that nobody has access to the server (very very very slow), even when I wanted to kill MySQL process, it took about 5 min.s just to show the task manager window. after 20 minutes I had to reset the server. How can I set MySQL not to lock the server? where the server doesn't response then I can't use KILL process command or like that. so as a administrator, when a user run a bad query and locks the server, what you do? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Undo function?
Hi, I get very nervous when I log onto my database via SSH and type in queries manually. Take the following query for example: Delete FROM Users WHERE User_ID = 5; If I hit return before I start typing WHERE then things would be disastorous! Is there any type of undo function with MySQL? Thanks for your help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Undo function?
Hello, I get very nervous when I log onto my database via SSH and type in queries manually. Take the following query for example: Delete FROM Users WHERE User_ID = 5; If I hit return before I start typing WHERE then things would be disastorous! Is there any type of undo function with MySQL? Use innodb tables and set autocommit to OFF. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Error: 1146 in Egroupware
Hi, I am using egroupware and in the calendar on a specific day and event I click on the link but MySQL gives me the following error Database error: Invalid SQL: SELECT * FROM phpgw_links WHERE (link_app1 = 'calendar' AND link_id1 = '5') OR (link_app2 = 'calendar' AND link_id2 = '5') ORDER BY link_lastmod DESC mysql Error: 1146 (Table 'egroupware.phpgw_links' doesn't exist) File: /var/www/html/egroupware/infolog/inc/class.solink.inc.php Line: 132 Function: calendar.uicalendar.view 3.23.58 I am using php 4.2.2 and MySQL, also have no background of php and Mysql, Please help!! Thank you, SShahzad
Re: MySQL Error: 1146 in Egroupware
On Thursday 20 January 2005 13:47, Shahzad, Saleem might have typed: Hi, I am using egroupware and in the calendar on a specific day and event I click on the link but MySQL gives me the following error Database error: Invalid SQL: SELECT * FROM phpgw_links WHERE (link_app1 = 'calendar' AND link_id1 = '5') OR (link_app2 = 'calendar' AND link_id2 = '5') ORDER BY link_lastmod DESC mysql Error: 1146 (Table 'egroupware.phpgw_links' doesn't exist) This is nothing to do with MySQL - you installed eGroupware incorrectly by the looks. Suggest RTM of the eGroupware install docs again. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SubQueries
Hi, Thank you. Could you please help me in writing an equvivalent query in mysql for the following oracle subquery?. update macvm set embedded='Y' where vm_server in (select a.vm_server from macvm a, component b, element c where a.vm_server = b.name (+) and b.id = c.id (+) and c.sxvariant = 'I3'); Thanks, Narasimha -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 08, 2004 9:28 PM To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS) Cc: mysql@lists.mysql.com Subject: Re: SubQueries Since 4.0.22 does NOT have subqueries, you will have to use a JOIN http://dev.mysql.com/doc/mysql/en/JOIN.html http://dev.mysql.com/doc/mysql/en/Rewriting_subqueries.html ... as in this example SELECT PAGE_SERVICE.TIMEOUT , PAGE_SERVICE.PAGE_SERVICE_COMMENT , PAGE_SERVICE.NUMERICMSGMAXSIZE , PAGE_SERVICE.ALPHAMSGMAXSIZE , PAGE_SERVICE.PASSWORD , PAGE_SERVICE.PHONE_NO , PAGE_SERVICE.NAME , PAGE_SERVICE.PAGE_SERVICE_ID FROM PAGE_SERVICE LEFT JOIN PAGER ON PAGE_SERVICE.PAGE_SERVICE_ID = PAGER.PAGE_SERVICE_ID WHERE PAGER.PAGE_SERVICE_ID IS NULL; ... best wishes! Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 12/08/2004 09:36:09 AM: Hi, I have a sub query in oracle, I want to convert it into Mysql 4.0.21 compatible SELECT PAGE_SERVICE.TIMEOUT, PAGE_SERVICE.PAGE_SERVICE_COMMENT, PAGE_SERVICE.NUMERICMSGMAXSIZE, PAGE_SERVICE.ALPHAMSGMAXSIZE, PAGE_SERVICE.PASSWORD, PAGE_SERVICE.PHONE_NO, PAGE_SERVICE.NAME, PAGE_SERVICE.PAGE_SERVICE_ID FROM PAGE_SERVICE WHERE NOT EXISTS (SELECT 1 FROM PAGER WHERE PAGE_SERVICE.PAGE_SERVICE_ID = PAGER.PAGE_SERVICE_ID) WITH CHECK OPTION; The above query is not displaying all the records whose PAGER.PAGE_SERVICE_ID is equal to PAGE_SERVICE.PAGE_SERVICE_ID. As subqueries are not supporting in mysql, could any one of you please give a solution . Waiting for the reply. Thanks in Advance Thanks, 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. 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: MySQL Error: 1146 in Egroupware
Database error: Invalid SQL: SELECT * FROM phpgw_links WHERE (link_app1 = 'calendar' AND link_id1 = '5') OR (link_app2 = 'calendar' AND link_id2 = '5') ORDER BY link_lastmod DESC mysql Error: 1146 (Table 'egroupware.phpgw_links' doesn't exist) That would indicate to me 2 possibilities: 1) Database 'egroupware' has a different name or does not exist, or 2) Table phpgw_links has a different name or does not exist As Duncan said, maybe the EGW install was incorrect... cheers Chris Ramsay - Web Developer - The Danwood Group Ltd. T: +44 (0) 1522 834482 F: +44 (0) 1522 884488 e: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] w: http://www.danwood.co.uk - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: getting mysql5.0.2 for solaris 8
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I am trying to compile this version for myself, but am having problems, as there is a complaint that sql_functions, get_hash_symbol and symbols are undeclared. Is there any plan to make a binary for solaris 8 for mysql 5.0.2? Thank you. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFB78CMikQgpVn8xrARAkFeAJ4vwqNqnzH1gQd2Iz/1bLnUUJ/1LACgiSkn Ajymc+etpCmgNkVrO2s0GoQ= =zDIc -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: can a subquery be used in an insert
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I would like to set one column to point to the auto-incrementing index of the last comment in the database for my application, but I am getting an error when I run my test, and so I wonder if what I am trying to do is possible. I am using mysql 5.0.1, but I may have to switch to 4.1.9 if I can't get ~ mysql 5.0.2 for solaris 8, to do my tests with subqueries. My SQL command is: INSERT INTo comments VALUES(0, 1, NULL, 'testuser', 'this is a test comment', (SELECT max(idx) FROM comments WHERE sourceid=1)) ~ I get an error: You can't specify target table 'comments' for update in FROM clause. But, if I don't have 'From comments', then I get an error also. Thank you for any help. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFB78G0ikQgpVn8xrARAhy9AJ9Bl+Yy+RMRtHrbzd0Iv4OONZXt8wCfWTJ2 ishDXVCmM1/+pBZsusy1Kvk= =tdXW -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
privileges
According to phpMyAdmin I have: user 'root' on 'localhost' with no password with Global privileges set to 'All privileges'. If I do a SELECT user(), I get '[EMAIL PROTECTED]'. Yet there is a database that doesn't now allow me to Browse. How can that be? Don't I have sufficient rights? Thanks! __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Undo function?
[snip] I get very nervous when I log onto my database via SSH and type in queries manually. Take the following query for example: Delete FROM Users WHERE User_ID = 5; If I hit return before I start typing WHERE then things would be disastorous! Is there any type of undo function with MySQL? [/snip] You'll want to read this http://dev.mysql.com/doc/mysql/en/COMMIT.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can a subquery be used in an insert
James Black wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I would like to set one column to point to the auto-incrementing index of the last comment in the database for my application, but I am getting an error when I run my test, and so I wonder if what I am trying to do is possible. My SQL command is: INSERT INTo comments VALUES(0, 1, NULL, 'testuser', 'this is a test comment', (SELECT max(idx) FROM comments WHERE sourceid=1)) Try : Insert into comments(list of you fields here ) select 0,1,null,'testuser','testcomment',max(idx) from comments where sourceid=1; but querying max(idx) is a bad idea. you could get the last inserted id with the following command : SELECT last_insert_id() from comments LIMIT 1; the last_insert_id is available on a per connection basis. but why are you inserting back in comments a field from comments ??? -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Undo function?
On Thu, Jan 20, 2005 at 08:58:18AM -0600, Jay Blanchard wrote: [snip] I get very nervous when I log onto my database via SSH and type in queries manually. Take the following query for example: Delete FROM Users WHERE User_ID = 5; If I hit return before I start typing WHERE then things would be disastorous! Is there any type of undo function with MySQL? [/snip] You'll want to read this http://dev.mysql.com/doc/mysql/en/COMMIT.html And try: $ mysql --i-am-a-dummy that makes it refuse deletes without 'where' Good luck, Harmen -- The Moon is Waxing Gibbous (81% of Full) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MYSQL + acquiring table relationships
Paul Wallace [EMAIL PROTECTED] wrote on 01/19/2005 06:50:48 PM: Was there a reply to this post? If so, I could have (did) missed it. Can you please resend? Rgds Paul. Sorry, but I never said (woops!) Which language I use depends on where I want to run the script and how often, and the fact that I am working in an M$ shop. I don't have any code I could share right now, maybe later (employer restrictions). What it boils down to is that you execute a SHOW CREATE TABLE query then scan the results looking for the lines that start with FOREIGN KEY. From that point it is simple to break the line down by syntax (ON, REFERENCES, a set of parentheses) to find the child field, the parent table, and the parent field. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Shawn, what language(s) are you using to parse this? Could you share the code? Thanks, Dave Merrill I prefer to parse the results of a SHOW CREATE TABLE... query. It's rather trivial to detect which rows in the result of that statement are your FOREIGN KEYS. They not only indicate which table(s) is/are this table's parent(s) but also which columns participate in each relationship. This technique does not rely on any particular connection library as the functionality is provided by MySQL itself. Once the INFORMATION_SCHEMA views are made available as of v5.x+ we will have an alternative to using the SHOW family of statements to extract schema metadata. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Undo function?
Hello, If you define table type as InnoDB, you can use transactions (see the link below). You will need set AUTOCOMMIT=0, and after you can issue COMMIT or ROLLBACK at the end of query or session to submit or cancel a transaction. I don't think you can use transactions for mysql system tables because they have to be MyISAM type, so the best solution for them will be backups. http://dev.mysql.com/doc/mysql/en/InnoDB_transaction_model.html -Original Message- From: shaun thornburgh [mailto:[EMAIL PROTECTED] Sent: Thursday, January 20, 2005 8:17 AM To: mysql@lists.mysql.com Subject: Undo function? Hi, I get very nervous when I log onto my database via SSH and type in queries manually. Take the following query for example: Delete FROM Users WHERE User_ID = 5; If I hit return before I start typing WHERE then things would be disastorous! Is there any type of undo function with MySQL? Thanks for your help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
indexing operation running for few hours
Hi, I don't know what is wrong with running indexing on this table. It have been last for nearly 3 hours. here is the command I run at the prompt of mysql : alter table inventory add index(basename); Because it is running indexing, all operation seems hang up. As far as I can remember I only injected 2300+ records in this table, compared to the transaction table in the same database which has 16 records ,this table is a tiny one in size. I run index on the trasactoin table, and it only took 43 min finishsed indexting on the date field. Can anybody tell me what is going on? Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can a subquery be used in an insert
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Philippe Poelvoorde wrote: | James Black wrote: | | -BEGIN PGP SIGNED MESSAGE- | Hash: SHA1 | | I would like to set one column to point to the auto-incrementing index | of the last comment in the database for my application, but I am getting | an error when I run my test, and so I wonder if what I am trying to do | is possible. | | | My SQL command is: | INSERT INTo comments VALUES(0, 1, NULL, 'testuser', 'this is a test | comment', (SELECT max(idx) FROM comments WHERE sourceid=1)) | | | Try : | | Insert into comments(list of you fields here ) | select 0,1,null,'testuser','testcomment',max(idx) from comments | where sourceid=1; | | but querying max(idx) is a bad idea. you could get the last inserted id | with the following command : | SELECT last_insert_id() from comments LIMIT 1; | | the last_insert_id is available on a per connection basis. | | but why are you inserting back in comments a field from comments ??? | ~ I want to store the parent comment to the comment being added, so I can ensure the comments are printed out in the correct order. ~ I will try putting in my list of fields, and see if that works any better. ~ I am writing a wiki, and I am trying to store the comments for each wiki into my database now. :) ~ Thank you for your response, btw. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFB78nPikQgpVn8xrARAsiPAJ4kowIdeqUUgTyzn2oelHhBh/4khACeOaeQ 9IhVMUjrn38PPAnTAzLblKU= =A2Mi -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bad query, locking the server
Hi, sometimes I run a query with LEFT JOIN on a big table and that causes 100% CPU usage on our windows 2003 server. during that nobody has access to the server (very very very slow), even when I wanted to kill MySQL process, it took about 5 min.s just to show the task manager window. after 20 minutes I had to reset the server. How can I set MySQL not to lock the server? where the server doesn't response then I can't use KILL process command or like that. so as an administrator, when a user run a bad query and locks the server, what you do? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bad query, locking the server
Hi, sometimes I run a query with LEFT JOIN on a big table and that causes 100% CPU usage on our windows 2003 server. during that nobody has access to the server (very very very slow), even when I wanted to kill MySQL process, it took about 5 min.s just to show the task manager window. after 20 minutes I had to reset the server. How can I set MySQL not to lock the server? where the server doesn't response then I can't use KILL process command or like that. so as an administrator, when a user run a bad query and locks the server, what you do? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SubQueries
Hi, Could you please help me in writing an equvivalent query in mysql 4.0.21 for the following oracle subquery?. update macvm set embedded='Y' where vm_server in (select a.vm_server from macvm a, component b, element c where a.vm_server = b.name (+) and b.id = c.id (+) and c.sxvariant = 'I3'); Thanks, 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]
RE: Undo function?
Backups are good too :-) Andy -Original Message- From: Artem Koltsov [mailto:[EMAIL PROTECTED] Sent: 20 January 2005 15:06 To: mysql@lists.mysql.com Subject: RE: Undo function? Hello, If you define table type as InnoDB, you can use transactions (see the link below). You will need set AUTOCOMMIT=0, and after you can issue COMMIT or ROLLBACK at the end of query or session to submit or cancel a transaction. I don't think you can use transactions for mysql system tables because they have to be MyISAM type, so the best solution for them will be backups. http://dev.mysql.com/doc/mysql/en/InnoDB_transaction_model.html -Original Message- From: shaun thornburgh [mailto:[EMAIL PROTECTED] Sent: Thursday, January 20, 2005 8:17 AM To: mysql@lists.mysql.com Subject: Undo function? Hi, I get very nervous when I log onto my database via SSH and type in queries manually. Take the following query for example: Delete FROM Users WHERE User_ID = 5; If I hit return before I start typing WHERE then things would be disastorous! Is there any type of undo function with MySQL? Thanks for your help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. -- 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]
4.0.20 - 4.1.9 Can't open file: 'Autor.ibd'
Hello, some weeks ago we switched from mysql 4.0.20 to 4.18 and later to 4.1.9. A customer now told us, that he can't access his old InnoDB-tables. He gets #1016 - Can't open file: 'Autor.ibd' (errno: 1) if he tries to access the table Autor. This happens to other InnoDB-tables created with mysql 4.0.20, too. The only file left is the Autor.frm. Have innodb-tables been stored in a central file at 4.0.20 maybe and now individual table-files with 4.1.x? How can this happen? Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SubQueries
[EMAIL PROTECTED] wrote on 01/20/2005 08:59:15 AM: Hi, Thank you. Could you please help me in writing an equvivalent query in mysql for the following oracle subquery?. update macvm set embedded='Y' where vm_server in (select a.vm_server from macvm a, component b, element c where a.vm_server = b.name (+) and b.id = c.id (+) and c.sxvariant = 'I3'); Thanks, Narasimha -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 08, 2004 9:28 PM To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS) Cc: mysql@lists.mysql.com Subject: Re: SubQueries Since 4.0.22 does NOT have subqueries, you will have to use a JOIN http://dev.mysql.com/doc/mysql/en/JOIN.html http://dev.mysql.com/doc/mysql/en/Rewriting_subqueries.html ... as in this example SELECT PAGE_SERVICE.TIMEOUT , PAGE_SERVICE.PAGE_SERVICE_COMMENT , PAGE_SERVICE.NUMERICMSGMAXSIZE , PAGE_SERVICE.ALPHAMSGMAXSIZE , PAGE_SERVICE.PASSWORD , PAGE_SERVICE.PHONE_NO , PAGE_SERVICE.NAME , PAGE_SERVICE.PAGE_SERVICE_ID FROM PAGE_SERVICE LEFT JOIN PAGER ON PAGE_SERVICE.PAGE_SERVICE_ID = PAGER.PAGE_SERVICE_ID WHERE PAGER.PAGE_SERVICE_ID IS NULL; ... best wishes! Shawn Green Database Administrator Unimin Corporation - Spruce Pine Please Google for tutorials on how to JOIN tables (using the explicit JOIN clauses, not the implicit inner joins created by comma-separated lists of tables) and RTM (see links above). Most subqueries will easily translate to explicit JOINs. Learning this form, now, will save you lots of heartache later. Original ORACLE query (re-formatted): update macvm set embedded='Y' where vm_server in( select a.vm_server from macvm a, component b, element c where a.vm_server = b.name (+) and b.id = c.id (+) and c.sxvariant = 'I3' ); To be perfectly HONEST, I have had limited exposure to the ORACLE join syntax. If I remember correctly, the (+) is on the side of the equation with the optional results (but I could be wrong). So I think that we translate this clause from the subquery: from macvm a, component b, element c where a.vm_server = b.name (+) and b.id = c.id (+) and c.sxvariant = 'I3' to read: FROM macvm a LEFT JOIN component b ON a.vm_server = b.name LEFT JOIN element c ON b.id = c.id AND c.sxvariant = 'I3' HOWEVER!! Because we _need_ a field from the table element to have a particular value. We should (for performance reasons) use INNER JOINs to ensure that the column sxvariant always contains the value 'I3'. That would change that portion of the subquery to read: FROM macvm a INNER JOIN component b ON a.vm_server = b.name INNER JOIN element c ON b.id = c.id and c.sxvariant = 'I3' Since macvm IS THE TABLE THAT CONTAINS THE FIELD YOU WANT TO UPDATE, and the JOINs accurately define the set of records you want to change (it usually takes a combination of JOINs and WHERE conditions to define the set of records to update but this time it didn't) we can use this clause as is as the target of the update statement. PROPOSED TRANSLATION: UPDATE macvm a INNER JOIN component b ON a.vm_server = b.name INNER JOIN element c ON b.id = c.id AND c.sxvariant = 'I3' SET embedded='Y'; ALTERNATIVE TRANSLATION: UPDATE macvm a INNER JOIN component b ON a.vm_server = b.name INNER JOIN element c ON b.id = c.id SET embedded='Y'; WHERE c.sxvariant = 'I3'; ALTERNATIVE TRANSLATION 2: UPDATE macvm a LEFT JOIN component b ON a.vm_server = b.name LEFT JOIN element c ON b.id = c.id SET embedded='Y'; WHERE c.sxvariant = 'I3'; If you ever want to double-check that you are going to update the correct set of rows, check your update target by rearranging your UPDATE statement into a SELECT statement. I usually list the target columns (the columns that get SET to some value) first, then I list the columns that participate in the JOINS and maybe even those that participate in the WHERE clause, too. If everything seems correct, then you know you have a good UPDATE target. Here is how I would manually verify the first translation: SELECT a.embedded , a.vm_server , b.id , c.sxvariant FROM macvm a INNER JOIN component b ON a.vm_server = b.name INNER JOIN element c ON b.id = c.id AND c.sxvariant = 'I3'; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
sqlyog
hai, am using sqlyog for mysql i have taken back up in batch ie .sql i want to restore automatically can it be possible to restore by writing a code in vb using sqlyog i have written a programme in vb for taking back up automatically need help to restore the same automatically regards prathioma rao
Re: can a subquery be used in an insert
~ I want to store the parent comment to the comment being added, so I can ensure the comments are printed out in the correct order. Then, see the post earlier called SQL schema, where there is child-parent relationship with comments. But you now when a user post a comment which is the parent comment(0 if there is no parent, or the value you give thanks to a hidden input field in your wiki), so you normally don't need that extra query. -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help forming SQL query
I have the following query which will get me all of the emails for my current membership: SELECT email.email_address FROM member, email WHERE member.member_primary_email_id = email.email_id AND member.member_standing != Dropped ORDER BY email.email_address I also have a query which will get me the email addresses of anyone who has updated their profile (and thus has a row in updatehistory) SELECT DISTINCT email.email_address FROM member, email, updatehistory WHERE member.member_primary_email_id = email.email_id AND member.member_standing != Dropped AND member.member_id = updatehistory.member_id_editor ORDER BY email.email_address; How would I get the emails for every member who does NOT have a row in updatehistory? ~ Mike - Mike Zornek Web Designer, Media Developer, Programmer and Geek Personal site: http://MikeZornek.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help forming SQL query
You should *ALWAYS* indicate which version of MySQL you are using when you ask this sort of question; the answers is almost always it depends: if you are using 3.x, do X, if you are using 4.0.x do Y, if you are using 4.1.x do Z. It's a lot of work to list all of those options and most people won't do it. It would also help if you provided information about what columns occurred in each table so that potential responders are clear on what information is in each of your tables. Including a few typical sample rows is also very helpful for some of us, although some people can envision the data clearly with only the column names. Rhino - Original Message - From: Mike Zornek [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, January 20, 2005 11:01 AM Subject: Need help forming SQL query I have the following query which will get me all of the emails for my current membership: SELECT email.email_address FROM member, email WHERE member.member_primary_email_id = email.email_id AND member.member_standing != Dropped ORDER BY email.email_address I also have a query which will get me the email addresses of anyone who has updated their profile (and thus has a row in updatehistory) SELECT DISTINCT email.email_address FROM member, email, updatehistory WHERE member.member_primary_email_id = email.email_id AND member.member_standing != Dropped AND member.member_id = updatehistory.member_id_editor ORDER BY email.email_address; How would I get the emails for every member who does NOT have a row in updatehistory? ~ Mike - Mike Zornek Web Designer, Media Developer, Programmer and Geek Personal site: http://MikeZornek.com -- 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.300 / Virus Database: 265.7.0 - Release Date: 17/01/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.7.0 - Release Date: 17/01/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: symlink to /var/lib/mysql
Hello. What is in error log? Usually it is located in the data directory or in the place which was specified in config file. More information you may find at: http://dev.mysql.com/doc/mysql/en/Symbolic_links.html kaustubh shinde [EMAIL PROTECTED] wrote: Hi all I recently installed SUSE linux and I am running MySQL 4.0.21 The way system is configured, /var ran out of disk space. I have RAID installed and /data is on raid. So I created a new folder mysql in /data and copied all the files from /var/lib/mysql to this folder. I renamed /var/lib/mysql to /var/lib/mysql1 and created a symbolic link mysql from /var/lib to /data/mysql. The link works just fine. Previously it would give me no space on device error on start up. Now it doesnt give me any error. I get following msg: linux:/home/kausti # mysqld_safe ]Starting mysqld-max daemon with databases from /var/lib/mysql STOPPING server from pid file /var/lib/mysql/linux.pid 050119 17:31:57 mysqld ended If I try to start MySQL from YaST, it fails and gives 'unspecified error' messege. I would really appreciate any ideas. I am kinda new to SUSE. So if I am asking a dumb question please excuse me. Thanks Kaustubh _ The MS Office product suite. Make efficiency a habit. http://www.microsoft.com/india/office/experience/ Simplify your life. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to set character set UTF-8 for mysqldump
Hello. See: http://dev.mysql.com/doc/mysql/en/Problems_with_character_sets.html Manish [EMAIL PROTECTED] wrote: How can I set character set as UTF-8 for mysqldump command? When I try this, I get an error - mysqldump --default-character-set=UTF-8 -uNAME -pXXX mydb mydb.sql mysqldump: Character set 'UTF-8' is not a compiled character set and is not specified in the 'C:\mys ql\\share\charsets\Index' file How can I do this? TIA, - Manish -- 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 Licencing
Hello. The complete answer on your question you can receive at [EMAIL PROTECTED] Mat [EMAIL PROTECTED] wrote: Hello, we're about to start using MySQL as our main database server for our web-based applications, so I started looking at the commercial licencing. My question is this: Are we allowed to upgrade from 4.0 to 4.1 or 5.0 free of charge? Or do we have to purchase another licence when moving between versions? Also, i'm unsure whether to purchase the MySQL Pro licence, as we've not used InnoDB yet, but I see that MySQL 4.1 uses it by default. I've noticed that it has cascading delete and updates... But don't know much else about it. Thanks for any help you can offer. -- 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: sqlyog
You're not serious are you? You don't really mean that you want a restore to be automatic do you? I don't think I've ever met a DBA who would trust a fully-automatic restore: most DBAs want to have some control over the restore process so that they can at least *initiate* it when they want it. Otherwise, there is the risk that a restore will take place when they DON'T want a restore. Many DBAs would also want the option of choosing the restore point too. For instance, should the data be restored to the latest backup or possibly to an earlier backup if there is a chance that data corruption took place before the last backup. Restoring the wrong backup wouldn't be a good thing in that case. If you really do mean that you want to completely automate the restore, what event is going to trigger the restore? Rhino - Original Message - From: PRATHIMA RAO [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, January 15, 2005 10:53 AM Subject: sqlyog hai, am using sqlyog for mysql i have taken back up in batch ie .sql i want to restore automatically can it be possible to restore by writing a code in vb using sqlyog i have written a programme in vb for taking back up automatically need help to restore the same automatically regards prathioma rao No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.7.0 - Release Date: 17/01/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.7.0 - Release Date: 17/01/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help forming SQL query
On 1/20/05 11:16 AM, Rhino [EMAIL PROTECTED] wrote:
You should *ALWAYS* indicate which version of MySQL you are using when you
ask this sort of question; the answers is almost always it depends: if you
are using 3.x, do X, if you are using 4.0.x do Y, if you are using 4.1.x do
Z. It's a lot of work to list all of those options and most people won't do
it.
It would also help if you provided information about what columns occurred
in each table so that potential responders are clear on what information is
in each of your tables. Including a few typical sample rows is also very
helpful for some of us, although some people can envision the data clearly
with only the column names.
Rhino
Ok, noted. I'll include this info with any future questions.
i'm using MySQL 4.0.17-log
Email:
+---+--+--+-+-+-
---+
| Field | Type | Null | Key | Default | Extra
|
+---+--+--+-+-+-
---+
| email_id | smallint(5) unsigned | | PRI | NULL|
auto_increment |
| email_type| enum('Home','Work') | | | Work|
|
| email_address | varchar(50) | | UNI | |
|
| member_id | smallint(5) unsigned | | MUL | 0 |
|
| email_private | tinyint(1) | | | 0 |
|
+---+--+--+-+-+-
Update History:
++--+--+-+-+
+
| Field | Type | Null | Key | Default |
Extra |
++--+--+-+-+
+
| updatehistroy_id | smallint(5) unsigned | | PRI | NULL|
auto_increment |
| updatehistroy_datetime | datetime | YES | | NULL|
|
| member_id_edited | smallint(5) unsigned | | | 0 |
|
| member_id_editor | smallint(5) unsigned | | | 0 |
|
| updatehistory_details | text | | | |
|
++--+--+-+-+
- Original Message -
From: Mike Zornek [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, January 20, 2005 11:01 AM
Subject: Need help forming SQL query
I have the following query which will get me all of the emails for my
current membership:
SELECT email.email_address
FROM member, email
WHERE
member.member_primary_email_id = email.email_id
AND member.member_standing != Dropped
ORDER BY email.email_address
I also have a query which will get me the email addresses of anyone who
has
updated their profile (and thus has a row in updatehistory)
SELECT DISTINCT email.email_address
FROM member, email, updatehistory
WHERE
member.member_primary_email_id = email.email_id
AND member.member_standing != Dropped
AND member.member_id = updatehistory.member_id_editor
ORDER BY email.email_address;
How would I get the emails for every member who does NOT have a row in
updatehistory?
~ Mike
-
Mike Zornek
Web Designer, Media Developer, Programmer and Geek
Personal site: http://MikeZornek.com
--
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.300 / Virus Database: 265.7.0 - Release Date: 17/01/2005
~ Mike
-
Mike Zornek
Web Designer, Media Developer, Programmer and Geek
Personal site: http://MikeZornek.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL quota problem
Hello, I have trouble with setting mysql database quota per user at my server. I`m using MySQL 4.0.22 on Linux with kernel 2.4.29. I`m using group quota on my server and all user`s files, mails and databases are with group $user (user`s mysql database directory is with setgid bit enabled to apply the group id to the new crated tables also). The problem is that when some user exceeds his quota the MySQL server dies for all users with: Disk is full writing. Waiting for someone to free space... Retry in 60 secs I suppose that this behaivior is caused, because the thread is waiting for free space, and when someone tries to write again to the same database, a very common example is some buletin board; a new thread is started that waits again, etc until the max threads are full and the MySQL server does nothing until it is restarted or the quota for the problem database is increased. Can anyone tell me is there a working solution for MySQL quota per database or a patch to return disk exceed error and not wait ? Because in my case the MySQL can`t write only to one database, all other hundreds of databases are operational, but server dies for all ... Thank You Iavor Stoev
Re: Need help forming SQL query
Mike Zornek [EMAIL PROTECTED] wrote on 01/20/2005 11:01:38 AM: I have the following query which will get me all of the emails for my current membership: SELECT email.email_address FROM member, email WHERE member.member_primary_email_id = email.email_id AND member.member_standing != Dropped ORDER BY email.email_address I also have a query which will get me the email addresses of anyone who has updated their profile (and thus has a row in updatehistory) SELECT DISTINCT email.email_address FROM member, email, updatehistory WHERE member.member_primary_email_id = email.email_id AND member.member_standing != Dropped AND member.member_id = updatehistory.member_id_editor ORDER BY email.email_address; How would I get the emails for every member who does NOT have a row in updatehistory? ~ Mike - Mike Zornek Web Designer, Media Developer, Programmer and Geek Personal site: http://MikeZornek.com First, I need to you recognize that listing tables with commas in the FROM clause creates an implicit INNER JOIN between the tables. That means that your query SELECT DISTINCT email.email_address FROM member, email, updatehistory WHERE member.member_primary_email_id = email.email_id AND member.member_standing != Dropped AND member.member_id = updatehistory.member_id_editor ORDER BY email.email_address; IS EQUIVALENT to this more explicitly defined query: SELECT DISTINCT email.email_address FROM member INNER JOIN email ON member.member_primary_email_id = email.email_id INNER JOIN updatehistory ON member.member_id = updatehistory.member_id_editor WHERE member.member_standing != Dropped ORDER BY email.email_address; In order to detect non-matches between two tables, you perform an outer join between them and look for the records that don't match. They will be easy to spot because the engine will put NULLs into every column of the optional table for those rows that don't match up with the required table. In a LEFT JOIN, the table on the left of the clause is the required table and the one on the right is the optional table. Reverse that for RIGHT JOINs. With that knowledge in hand, we will now change the query to detect which rows of member (required) do not match any rows in updatehistory (optional). We change the JOIN on that table from INNER JOIN to LEFT JOIN and look for NULL values where there shouldn't be any by adding another condition to the WHERE clause (they should only exist if rows between the two tables didn't meet your ON conditions(s), correct?). We don't need to change anything else. SELECT DISTINCT email.email_address FROM member INNER JOIN email ON member.member_primary_email_id = email.email_id LEFT JOIN updatehistory ON member.member_id = updatehistory.member_id_editor WHERE member.member_standing != Dropped AND updatehistory.member_id_editor IS NULL ORDER BY email.email_address; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: sub query is extermely slow
[EMAIL PROTECTED] wrote: SELECT DISTINCT i.basename FROM inventory i INNER JOIN transaction t ON i.prodcode = t.prodcode AND t.date = '2004-01-01' AND t.date = '2004-01-31' INNER JOIN transaction tt ON i.prodcode = tt.prodcode AND tt.date = '2005-01-01' AND tt.date = '2005-01-31' INNER JOIN customer c ON c.custcode = t.custcode AND c.custcode = tt.custcode WHERE i.basename is not NULL and i.prodname is not NULL order by i.basename This should give you a list of inventory basenames for all current customers (their names are still in the customer table) that had transactions (ordered products?) during both JAN 2004 and JAN 2005. This list will show only the products that were ordered during BOTH time periods BY THE SAME CUSTOMER at least once (at least one repeat sale, year to year, in JAN). Is this what you were after or was there a different question you were trying to answer? Hi, while this is much faster than subquery, I found there is cumsy way faster than this series inner joins. I dicovered that if I create two different temporary tables with create view as select ... for 2 differnet period, the join between this temp tables is also much faster than this series inner joins approach. With 16 records in the transaction table, the series inner joins takes very long time (more than 15 minutes) give out a result. Any comment? Thanks Sam Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help forming SQL query
That's a great start but you haven't shown the Member table. It would also
be very useful if you included a few sample rows from each table. For
example, I'm looking at the member_id_edited and member_id_editor columns so
that I can create tiny sample tables like yours and I have no idea what
values will typically go in those columns.
By the way, did you know that there are typos in the first two column names
of the Update_History table? updatehistroy should be spelled
updatehistory
Rhino
- Original Message -
From: Mike Zornek [EMAIL PROTECTED]
To: MySQL mysql@lists.mysql.com
Cc: Rhino [EMAIL PROTECTED]
Sent: Thursday, January 20, 2005 11:26 AM
Subject: Re: Need help forming SQL query
On 1/20/05 11:16 AM, Rhino [EMAIL PROTECTED] wrote:
You should *ALWAYS* indicate which version of MySQL you are using when
you
ask this sort of question; the answers is almost always it depends: if
you
are using 3.x, do X, if you are using 4.0.x do Y, if you are using 4.1.x
do
Z. It's a lot of work to list all of those options and most people
won't do
it.
It would also help if you provided information about what columns
occurred
in each table so that potential responders are clear on what information
is
in each of your tables. Including a few typical sample rows is also very
helpful for some of us, although some people can envision the data
clearly
with only the column names.
Rhino
Ok, noted. I'll include this info with any future questions.
i'm using MySQL 4.0.17-log
Email:
+---+--+--+-+-+-
---+
| Field | Type | Null | Key | Default | Extra
|
+---+--+--+-+-+-
---+
| email_id | smallint(5) unsigned | | PRI | NULL|
auto_increment |
| email_type| enum('Home','Work') | | | Work|
|
| email_address | varchar(50) | | UNI | |
|
| member_id | smallint(5) unsigned | | MUL | 0 |
|
| email_private | tinyint(1) | | | 0 |
|
+---+--+--+-+-+-
Update History:
++--+--+-+-+
+
| Field | Type | Null | Key | Default |
Extra |
++--+--+-+-+
+
| updatehistroy_id | smallint(5) unsigned | | PRI | NULL|
auto_increment |
| updatehistroy_datetime | datetime | YES | | NULL|
|
| member_id_edited | smallint(5) unsigned | | | 0 |
|
| member_id_editor | smallint(5) unsigned | | | 0 |
|
| updatehistory_details | text | | | |
|
++--+--+-+-+
- Original Message -
From: Mike Zornek [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, January 20, 2005 11:01 AM
Subject: Need help forming SQL query
I have the following query which will get me all of the emails for my
current membership:
SELECT email.email_address
FROM member, email
WHERE
member.member_primary_email_id = email.email_id
AND member.member_standing != Dropped
ORDER BY email.email_address
I also have a query which will get me the email addresses of anyone who
has
updated their profile (and thus has a row in updatehistory)
SELECT DISTINCT email.email_address
FROM member, email, updatehistory
WHERE
member.member_primary_email_id = email.email_id
AND member.member_standing != Dropped
AND member.member_id = updatehistory.member_id_editor
ORDER BY email.email_address;
How would I get the emails for every member who does NOT have a row in
updatehistory?
~ Mike
-
Mike Zornek
Web Designer, Media Developer, Programmer and Geek
Personal site: http://MikeZornek.com
--
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.300 / Virus Database: 265.7.0 - Release Date: 17/01/2005
~ Mike
-
Mike Zornek
Web Designer, Media Developer, Programmer and Geek
Personal site: http://MikeZornek.com
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.7.0 - Release Date: 17/01/2005
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.7.0 - Release Date: 17/01/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Undo function?
Also a good practice for shakey hands is to start mysql with the --safe-updates option. This will not allow a delete or update to be executed without a where clause. On Thu, 20 Jan 2005 10:55 am, Andy Eastham wrote: Backups are good too :-) Andy -Original Message- From: Artem Koltsov [mailto:[EMAIL PROTECTED] Sent: 20 January 2005 15:06 To: mysql@lists.mysql.com Subject: RE: Undo function? Hello, If you define table type as InnoDB, you can use transactions (see the link below). You will need set AUTOCOMMIT=0, and after you can issue COMMIT or ROLLBACK at the end of query or session to submit or cancel a transaction. I don't think you can use transactions for mysql system tables because they have to be MyISAM type, so the best solution for them will be backups. http://dev.mysql.com/doc/mysql/en/InnoDB_transaction_model.html -Original Message- From: shaun thornburgh [mailto:[EMAIL PROTECTED] Sent: Thursday, January 20, 2005 8:17 AM To: mysql@lists.mysql.com Subject: Undo function? Hi, I get very nervous when I log onto my database via SSH and type in queries manually. Take the following query for example: Delete FROM Users WHERE User_ID = 5; If I hit return before I start typing WHERE then things would be disastorous! Is there any type of undo function with MySQL? Thanks for your help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. -- 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] John Pignata [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Workaround for Windows bug in 4.1.9 in innodb_file_per_table: operating system error number 87
Hi! If you have put: innodb_file_per_table in your my.cnf or my.ini in Windows, and try to run MySQL-4.1.9, the mysqld server exits and prints: 050117 3:07:59 InnoDB: Operating system error number 87 in a file operation. to the .err log. You can work around the bug in 4.1.9 by putting: innodb_flush_method=unbuffered to your my.cnf or my.ini. This is the bug http://bugs.mysql.com/bug.php?id=8021. A full fix will be in 4.1.10. When I fixed the critical bug #7496, I introduced this bug in the Windows version. Regards, Heikki -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sub query is extermely slow
sam wun [EMAIL PROTECTED] wrote on 01/20/2005 11:45:40 AM: [EMAIL PROTECTED] wrote: SELECT DISTINCT i.basename FROM inventory i INNER JOIN transaction t ON i.prodcode = t.prodcode AND t.date = '2004-01-01' AND t.date = '2004-01-31' INNER JOIN transaction tt ON i.prodcode = tt.prodcode AND tt.date = '2005-01-01' AND tt.date = '2005-01-31' INNER JOIN customer c ON c.custcode = t.custcode AND c.custcode = tt.custcode WHERE i.basename is not NULL and i.prodname is not NULL order by i.basename This should give you a list of inventory basenames for all current customers (their names are still in the customer table) that had transactions (ordered products?) during both JAN 2004 and JAN 2005. This list will show only the products that were ordered during BOTH time periods BY THE SAME CUSTOMER at least once (at least one repeat sale, year to year, in JAN). Is this what you were after or was there a different question you were trying to answer? Hi, while this is much faster than subquery, I found there is cumsy way faster than this series inner joins. I dicovered that if I create two different temporary tables with create view as select ... for 2 differnet period, the join between this temp tables is also much faster than this series inner joins approach. With 16 records in the transaction table, the series inner joins takes very long time (more than 15 minutes) give out a result. Any comment? Thanks Sam Shawn Green Database Administrator Unimin Corporation - Spruce Pine Once you start getting into larger sets of data (millions of rows), Taking snapshots (as you did with your CREATE TABLE ... SELECT statements) can provide some significant performance boosts. Here is a numerical analysis of the two situations: Assume we have a table with 100 (1Millon = 1e6) rows in it that needs to be joined to itself for a query (much as in the query above). Computing the JOIN of two tables that each contain 1e6 rows could result in a virtual table containing up to 1e12 rows. The actual number of rows in a JOIN will be smaller than the pure Cartesian product because it will be limited to only those rows match your ON conditions. However the number 1e12 represents how many permutations of data the engine must decide between in order to calculate the results of the JOIN. Let's imagine that instead of needing to join the entire table to itself, that we only need to match 1 (1e4) rows of the table's data against another set of 1 rows (two nice round figures that could stand in for the actual number of transaction records during January of each year in our original query data). A JOIN between those two sets of rows would result in a maximum Cartesian product of only 1e8 rows. If it takes a fixed length of time (not a variable length of time) for the query engine to decide if any one row of a JOIN's Cartesian product belongs to the final JOIN results (based on evaluating the ON conditions against each row combination) then the subset JOIN will reduce the time it takes to compute the virtual table by a factor of 1 (1e12/1e8 = 1e4). That means that the query engine made 999,999,990,000 fewer comparisons to build the virtual table based on the JOINed sub-tables than it would need to build the same table based on the whole table joined to itself. Your results seem to support this analysis. The time you spent creating the subsets of data (time to select rows + time to write them into a table, twice) was much, MUCH less than the time it would take to sort through all of the other possible combinations of data in your original table that didn't need to participate in your analysis. As I said at the beginning, this is a query optimization technique for certain queries against larger datasets. For queries that deal with tables on the order of a few hundred thousand rows or less, it may not apply. Your mileage will vary. Only actual testing will reveal the actual performance of one technique over the other. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: privileges
Hello. What is the output of the following statement: show grants for 'root'@'localhost'; J C [EMAIL PROTECTED] wrote: According to phpMyAdmin I have: user 'root' on 'localhost' with no password with Global privileges set to 'All privileges'. If I do a SELECT user(), I get '[EMAIL PROTECTED]'. Yet there is a database that doesn't now allow me to Browse. How can that be? Don't I have sufficient rights? Thanks! __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com -- 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.sock file exists, but still cannot connect to server
Okay, with the good help I was getting, and looking at the all clues MySQL was giving me, like Table 'mysql.host' doesn't exist and Can't find file: './mysql/host.frm', I concluded that the 4.1.9 installation conflicts with the default 4.0.18 installation. For instance, 4.0.18 expects files in /var/mysql and /usr/libexec, but 4.1.9 wants them in /usr/local/mysql, etc. There isn't a mysql/data directory anywhere at all for 4.0.18. The two seem incompatible (at least, it's beyond me...). So, I backed out my 4.1.9 installation, and was able to get things going again under 4.0.18. I guess I'll have to wait until Apple upgrades MySQL with OS X 10.3.8. Thanks to all! Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Password Security Suggestions?
Howdy, I'm looking for ways to better enforce user password security through MySQL. Currently, it does not appear that there are any restrictions on minimum length, password expiration times or strong password checking. Other than educating the users directly, is there a way to enforce any or all of these? Backend LDAP? PAM? Thanks! -Jeremy
Setting up a Disk Quota for a database
I am using mysql 3.23. I want to know if it is possible to setup different size quotas for different databases? Emil Diego Website Administrator University of Miami School of Business 305.284.5449 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.830 / Virus Database: 565 - Release Date: 1/6/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL design
Hello, I don't have any chance with this topic :-) I usually follow that list with my professional email, and there are often good advices on normalization or sql design. Do you need any more details ? Thanks for your help [EMAIL PROTECTED] écrit: Hello, I've got a DB design issue here, and I would like to hear your views on this problem. I've got a website where people can post comments on various ressources : news, articles, polls, and so on. So every comments are in a table with a field for the ressource type (news=1,polls=2, and so on) and a field for the ressource ID of this particular ressource. Comments are also threaded so : Create table comments ( id integer not null auto_increment, comment_parent integer not null, comment_thread not null, --used in solution 1 for efficiency ressource_type integer not null, ressource_id integer not null, body blob ); + index and key :) The problem is the forum, where usually the traffic is higher. I've got two solutions : 1. a new topic on one forum goes in the same table 'comments'. Since the forum is displayed by thread (topic + replies), managing which comments has been seen by someone is done on a thread basis, whereas on all the website it's done on the couple (ressource type, ressource id) basis [the same for displaying comments]. So there is two logics for keeping track of comments viewed by people, for displaying comments and for moderation (removal comments, or topics). _but_ topics and replies are in the same table, which makes sense to me because they are similar. (same fields). 2. solution two : separate each topic from replies. a new topic is sent in a new table create table forum_post ( id integer not null auto_increment, body blob ) and then all the logic for comments seen, moderation, displaying is absolutely the same than the rest of the website... but I don't see why similar things would be in different tables. Also, searching the forum would be a little more work to merge comments and posts. It ay seems stupid, but having different way of displaying comments is beginning to be a hard work, and I believe with solution 2, queries will be faster (index would be more balanced : at the moment the forum number one has 2 messages, but no other couples (ressource type, ressource id) has more than 100 comments. So what would you think about those solutions ? Has anyone other solutions for forum ? Thanks for your help. Pooly :-) ps: if you want to have a look : http://www.w-fenec.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: sqlyog
snip You're not serious are you? You don't really mean that you want a restore to be automatic do you? snip If you really do mean that you want to completely automate the restore, what event is going to trigger the restore? Rhino I believe what the OP is after is restoring a database dump that has been sent from elsewhere (via email). His previous post (restoring database) stated that was his intention. In a reply to that post I did say that he could use: mysql [-p] [-u] [-h] db_name dumpfile.sql Graham - Original Message - From: PRATHIMA RAO [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, January 15, 2005 10:53 AM Subject: sqlyog hai, am using sqlyog for mysql i have taken back up in batch ie .sql i want to restore automatically can it be possible to restore by writing a code in vb using sqlyog i have written a programme in vb for taking back up automatically need help to restore the same automatically regards prathioma rao -- -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.7.0 - Release Date: 17/01/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.7.0 - Release Date: 17/01/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1.9 with Intel C++ Compiler
How long until we'll see an Intel icc compile of 4.1.9 on the downloads site? Thanks. Jeremiah Gowdy Senior Software Engineer FreedomVOICE Systems
Re: SubQueries
You must be having a problem with your email client as this is about the ninth time I have seen this same request today. Please check your client for problems. I know because I have responded once already. Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 01/20/2005 10:20:12 AM: Hi, Could you please help me in writing an equvivalent query in mysql 4.0.21 for the following oracle subquery?. update macvm set embedded='Y' where vm_server in (select a.vm_server from macvm a, component b, element c where a.vm_server = b.name (+) and b.id = c.id (+) and c.sxvariant = 'I3'); Thanks, 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]
Re: can a subquery be used in an insert
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Philippe Poelvoorde wrote: | | Insert into comments(list of you fields here ) | select 0,1,null,'testuser','testcomment',max(idx) from comments | where sourceid=1; | | but querying max(idx) is a bad idea. you could get the last inserted id | with the following command : | SELECT last_insert_id() from comments LIMIT 1; | | the last_insert_id is available on a per connection basis. | | but why are you inserting back in comments a field from comments ??? | ~ I looked in the archive for anything about SQL schema, but there were just two messages, and the original message had no thread with it. ~ I took your suggestion, and tried the following, but got the same error message: INSERT INTO comments (idx,sourceid,created,userid,comment,parentid) VALUES(0,1,NULL,'testuser','this is a test',(SELECT max(idx) FROM comments WHERE sourceid=1)) ~ The error is still: You can't specify target table 'comments' for update in FROM clause - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFB8AopikQgpVn8xrARAjI2AJ4se/25M8AcUvtO9Q8GAFe62/aIUQCgjVOJ CPYRiuFlXtm+MXhytxo5YKc= =mWUq -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
privileges
What is the output of the following statement: show grants for 'root'@'localhost'; Grants for '[EMAIL PROTECTED]' GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION It turns out that the database that I was trying to decipher, appears to have nothing but empty tables. I inserted a row into one of them, and the browse started working. Thanks! __ Do you Yahoo!? All your favorites on one personal page Try My Yahoo! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can a subquery be used in an insert
James Black [EMAIL PROTECTED] wrote on 01/20/2005 02:44:41 PM: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Philippe Poelvoorde wrote: | | Insert into comments(list of you fields here ) | select 0,1,null,'testuser','testcomment',max(idx) from comments | where sourceid=1; | | but querying max(idx) is a bad idea. you could get the last inserted id | with the following command : | SELECT last_insert_id() from comments LIMIT 1; | | the last_insert_id is available on a per connection basis. | | but why are you inserting back in comments a field from comments ??? | ~ I looked in the archive for anything about SQL schema, but there were just two messages, and the original message had no thread with it. ~ I took your suggestion, and tried the following, but got the same error message: INSERT INTO comments (idx,sourceid,created,userid,comment,parentid) VALUES(0,1,NULL,'testuser','this is a test',(SELECT max(idx) FROM comments WHERE sourceid=1)) ~ The error is still: You can't specify target table 'comments' for update in FROM clause - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFB8AopikQgpVn8xrARAjI2AJ4se/25M8AcUvtO9Q8GAFe62/aIUQCgjVOJ CPYRiuFlXtm+MXhytxo5YKc= =mWUq -END PGP SIGNATURE- James, You should already KNOW the parent comment of a comment when you create it. Don't you? Imagine the following thread: TOPIC (1) Comment on Topic (2) Comment on Topic (3) Someone comes along and wants to add a new comment to the main TOPIC message. The value you need for your parent field is 1 not MAX(ID) which would be 3. Someone else wants to add a comment to the first comment (#2). It's parent value needs to be 2 NOT the MAX(ID) which would now be 4 (thanks to the addition that just happened). Do you now understand why we are all questioning WHY you are trying to use the MAX(ID) value? That's not something you look up but supply as part of your data. When someone clicks on a topic/comment to generate a new comment, you should already have the value of the parent topic's ID. No look-ups required. Maybe I am just missing the purpose of the field you are trying to populate. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
upgrading from 3.23 to 4.0.x
We are in the process of upgrading from 3.23.x to 4.0.x via compiling (can't use rpms). I've read through old forum posts and the documentation and I see there is this requirement for libmysqlclient.so.10 while the new 4.0.x comes with libmysqlclient.so.12. I also see that many people are using MySQL-shared-compat.rpm which includes this: /usr/lib/libmysqlclient.so /usr/lib/libmysqlclient.so.10 /usr/lib/libmysqlclient.so.10.0.0 /usr/lib/libmysqlclient.so.12 /usr/lib/libmysqlclient.so.12.0.0 /usr/lib/libmysqlclient_r.so /usr/lib/libmysqlclient_r.so.10 /usr/lib/libmysqlclient_r.so.10.0.0 /usr/lib/libmysqlclient_r.so.12 /usr/lib/libmysqlclient_r.so.12.0.0 Anyway, What I am wondering is should I just copy over the old libmysqlclient.so.10 files to the new install or should I just recompile whatever is using libmysqlclient.so.10? Also, what is using libmysqlclient.so.10 besides php and dbd? Thanks, Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can a subquery be used in an insert
James Black [EMAIL PROTECTED] wrote on 01/20/2005 03:08:33 PM: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] wrote: | You should already KNOW the parent comment of a comment when you create | it. Don't you? Imagine the following thread: ~ I am trying to not have to pass in the parent id, but I may have to do that, if need be. The problem I run into is that there may be more than one comment to the last comment seen, and I want each comment to only have one child, so by having it do a subquery I can ensure this to be more likely, as it will be more atomic of an operation. ~ I don't know if we are going to have threaded comments yet. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFB8A/BikQgpVn8xrARAumUAJ9Q0mOjzHk0tM+Gk0tmRqlJyQ09bgCeM3dK t6DnKYczJQkLcBHHlOA7u0c= =F0Dm -END PGP SIGNATURE- With the each child holding a parent id, you leave yourself the opportunity to store more than one child per parent. If you invert your definition of what goes into that field and made it a next comment id or child comment id, you will enforce by database design only one child per comment. Either way you go I don't think you can do what you are trying to do in a single statement. The error you are getting is telling you that you are attempting to access a table that is currently locked (it's locked due to the other predicate in your statement). Either your INSERT is blocking your SELECT or your SELECT is preventing the INSERT. This wouldn't happen if you were selecting and inserting to different tables but you aren't. I would suggest you rewrite your INSERT process as two or more statements (get max(), do insert) wrapped either in a LOCK/UNLOCK block or BEGIN/COMMIT block to enforce the atomicity you desire. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Why is this happeneing?
I am not sure what I am noticing is the intended behavior of MySql. I created my table with CHAR(15) data types. When I describe the table, it appears as VARCHAR(15) .. Here are the statements - alter table mytable add (loc char(15)); Query OK, 1 row affected, 1 warning (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql describe mytable; +-+-+--+-+---+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+---+---+ | name| varchar(12) | YES | | NULL | | | is_emp | tinyint(4) | YES | | NULL | | | dept | smallint(6) | YES | | NULL | | | salary | float | YES | | NULL | | | company | varchar(12) | YES | | NULL | | | etime | timestamp | YES | | CURRENT_TIMESTAMP | | | loc | varchar(15) | YES | | NULL | | +-+-+--+-+---+---+ 7 rows in set (0.00 sec) Only when I add a field with as CHAR , it shows up as CHAR(1). Any explanations??? I have the solaris 32 bit MySQL-MAX package installed. Version is 4.1.9. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why is this happeneing?
This is documented in the manual http://dev.mysql.com/doc/mysql/en/Silent_column_changes.html. Michael Nupur Jain wrote: I am not sure what I am noticing is the intended behavior of MySql. I created my table with CHAR(15) data types. When I describe the table, it appears as VARCHAR(15) .. Here are the statements - alter table mytable add (loc char(15)); Query OK, 1 row affected, 1 warning (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql describe mytable; +-+-+--+-+---+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+---+---+ | name| varchar(12) | YES | | NULL | | | is_emp | tinyint(4) | YES | | NULL | | | dept | smallint(6) | YES | | NULL | | | salary | float | YES | | NULL | | | company | varchar(12) | YES | | NULL | | | etime | timestamp | YES | | CURRENT_TIMESTAMP | | | loc | varchar(15) | YES | | NULL | | +-+-+--+-+---+---+ 7 rows in set (0.00 sec) Only when I add a field with as CHAR , it shows up as CHAR(1). Any explanations??? I have the solaris 32 bit MySQL-MAX package installed. Version is 4.1.9. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Embedding MySQL in application, probably developed in C#
We are building an application for myths of the world. It started out in Access, but when we decided to distribute it via CD to attendees at an art retrospective (about myths of the world), we began looking for an alternative db, since not everyone has Access on their computer. There will be no charge for the retrospective or the application CD. The application will eventually be web-based and available on the internet. MySQL was recommended to us, as was developing in C#. i have read that MySQL can be embedded in applications. Is this an advisable route for us? to develop and C# and embed MySQL in the application? i'm somewhat at a loss as how to proceed, and time is running out. Any help/advice is greatly appreciated. --elizabeth -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query cache confused when using different client protocols
Try this: Setup a database server, with MySQL 4.1, with query cache turned on Setup a client machine with MySQL 4.0 Setup a client machine with MySQL 4.1 (libmysqlclient14) Create very simple table : CREATE TABLE woepwoep (CNT int NOT NULL); Insert single row : INSERT INTO woepwoep SET CNT=10; Now, do select on client machine with MySQL 4.0 : SELECT CNT FROM woepwoep; Query gets cached in format suitable for old protocol Now, do select on client machine with MySQL 4.1 : SELECT CNT FROM woepwoep; This returns undesirable results. The other way around (initial query done on MySQL 4.1 client, subsequent query done on MySQL 4.0 client) fails too, but then it at least say 'Malformed packet'. Is this a bug? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indizes fr groe Datenbank
Hi Again, You have to optimize a large database (2 Million records). since I have a huge amount of 'static' records (2 million and more) which will not change any more, and on the other hand at about 2.ooo (growing) records which are updated regulary, I wonder if its useful having those static records in a seperate table... The SELECT-queries go into a MERGE table which unites the 'dynamic' and 'static' record-table. Will this speed up my Update-Queries, because the index in the dynamic-table will be built up much faster? thanks for your help. Regards, Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0.20 - 4.1.9 Can't open file: 'Autor.ibd'
Marten, - Original Message - From: Marten Lehmann [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, January 20, 2005 5:47 PM Subject: 4.0.20 - 4.1.9 Can't open file: 'Autor.ibd' Hello, some weeks ago we switched from mysql 4.0.20 to 4.18 and later to 4.1.9. A customer now told us, that he can't access his old InnoDB-tables. He gets #1016 - Can't open file: 'Autor.ibd' (errno: 1) if he tries to access the table Autor. This happens to other InnoDB-tables created with mysql 4.0.20, too. The only file left is the Autor.frm. Have innodb-tables been stored in a central file at 4.0.20 maybe and now individual table-files with 4.1.x? How can this happen? I hope that you have not destroyed the ibdata file. The tables are there. Did you edit the my.cnf in the upgrade? What does mysqld print to the .err log? Regards Marten 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Installationa of MySQL 4.1.9 problem
I was uninstall server version 3.23 (or something like this), then install 4.1.7 (with some troubles), and than uninstall 4.1.7 and try to install 4.1.9. version. Everything looks fine, except installation procedure can't install MySQL like Windows service (Win XP pro). I was trying to install manualy (folowing instructions in my.ini file), and in command prompt type: mysqld --install MySQL41 --defaults-file=C:\Program Files\MySQL\MySQL Server 4.1\my.ini and then net start MySQL41 I have next message in command prompt window: The MySQL41 service is starting The MySQL41 service could not be started A system error has occured. System error 1067 has occured. The process terminated unexpectedly. What is wrong??? -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.7.0 - Release Date: 1/17/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
show table types
SHOW TABLE TYPES became available in MYSQL 4.1. What is the equivalent for MYSQL 3.23.49? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Embedding MySQL in application, probably developed in C#
On Wed, 19 Jan 2005 21:28:05 -0800, elizabeth baker [EMAIL PROTECTED] said: We are building an application for myths of the world. It started out in Access, but when we decided to distribute it via CD to attendees at an art retrospective (about myths of the world), we began looking for an alternative db, since not everyone has Access on their computer. There will be no charge for the retrospective or the application CD. The application will eventually be web-based and available on the internet. MySQL was recommended to us, as was developing in C#. I like MYSQL and PHP esp. for the web. http://www.onlamp.com/pub/a/php/2004/02/19/php_foundations.html i have read that MySQL can be embedded in applications. Is this an advisable route for us? to develop and C# and embed MySQL in the application? i'm somewhat at a loss as how to proceed, and time is running out. Any help/advice is greatly appreciated. --elizabeth -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Embedding MySQL in application, probably developed in C#
elizabeth baker wrote: We are building an application for myths of the world. It started out in Access, but when we decided to distribute it via CD to attendees at an art retrospective (about myths of the world), we began looking for an alternative db, since not everyone has Access on their computer. There will be no charge for the retrospective or the application CD. The application will eventually be web-based and available on the internet. MySQL was recommended to us, as was developing in C#. i have read that MySQL can be embedded in applications. Is this an advisable route for us? to develop and C# and embed MySQL in the application? i'm somewhat at a loss as how to proceed, and time is running out. Any help/advice is greatly appreciated. --elizabeth If you want a quick_and_dodgy way of doing it, you can do the front-end in MS Access developer, and have the data in MySQL. I can't tell you about licensing issues with MySQL - I suppose it depends on whether your app is commercial or not, but you should track down the answer to this before going any further. The developer version of Access lets you create installation packages that will put a 'runtime' version of MS Access on Windows computers - without the need for an Access or Office license. In reality, it's a pretty tricky thing to get working right. We had a bad enough time on our network, and we're all using Windows 2000. We had even more issues getting the thing to run on Windows NT4, and I hate to think what you'd have to go through to get it to install on other versions of Windows. But if you're familiar with Access already and you're pushed for time ( but you can handle testing your installation package on all different types of Windows installs ), then this may be the way to go. REMEMBER: this is the quick and dodgy solution, and I suppose you have to actually have the developer version of Access to start with. If you don't like this option, I would go with either Perl or PHP. You can make an installer to get a LAMP system up and runnning fairly easily. I haven't done it, but I've read about it, and it doesn't look too hard. If you don't like a web-based interface, you could look at http://gtk2-perl.sourceforge.net/ - I've been using this setup for Rapid Application Development under Linux for quite some time now. It's a very nice system. Just grab Gtk2 for Windows, ActiveState Perl, and the Gtk2-Perl binaries and you're off. As for C#, I've never used it, but I don't think you'll find too many MySQL users recommending it somehow - it's better to stick with open source solutions. MySQL gets far more testing with open source languages than with C#, and your tech support for open source systems is going to be far better as well. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
isam to myisam
Hi there, I'm running Freebsd 5.3 and just installed mysql-standard-4.1.9-unknown-freebsd4.7-i386. I'm able to run mysqld and mysql, etc. I used to run Freebsd 2.2 and I think it was mysql 3.21.33b... I'm not sure. My old system faded away rather abruptly and I didn't get a chance to do database backups or anything. I have one database that I'd like to recover. The database had 5 tables in it. Each one had three files associated with it, ie editors.ISD, editors.ISM and editors.frm. I have access to the tables but can't load them into my new mysql. I copied them into /usr/local/mysql/data/BagusDatabase and made sure the permissions were the same as my test directory. I have been reading around and thought I came across the answer with the mysql_convert_table_format script. I tried changing into my new BagusDatabase directory and running: mysql_convert_table_format --user='root' --password='mypassword' --type='ISA M' BagusDatabase The script then reports: Converting tables: converting editors Can't convert editors: Error Can't find file: 'editors.MYI' (errno: 2) I bet this is old hat for some of you out there. Can someone help me figure out how to recreate my database? Thanks, Bagus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: isam to myisam
At 22:46 -0600 1/20/05, Bagus wrote: Hi there, I'm running Freebsd 5.3 and just installed mysql-standard-4.1.9-unknown-freebsd4.7-i386. I'm able to run mysqld and mysql, etc. I used to run Freebsd 2.2 and I think it was mysql 3.21.33b... I'm not sure. My old system faded away rather abruptly and I didn't get a chance to do database backups or anything. I have one database that I'd like to recover. The database had 5 tables in it. Each one had three files associated with it, ie editors.ISD, editors.ISM and editors.frm. I have access to the tables but can't load them into my new mysql. I copied them into /usr/local/mysql/data/BagusDatabase and made sure the permissions were the same as my test directory. I have been reading around and thought I came across the answer with the mysql_convert_table_format script. I tried changing into my new BagusDatabase directory and running: mysql_convert_table_format --user='root' --password='mypassword' --type='ISA M' BagusDatabase According to the help message, the --type option is for specifying the table type that you want to convert the tables *to*. So it should be --type=MyISAM, I think. You could also execute this statement for each table that you want to convert: ALTER TABLE tbl_name ENGINE=MyISAM; The script then reports: Converting tables: converting editors Can't convert editors: Error Can't find file: 'editors.MYI' (errno: 2) I bet this is old hat for some of you out there. Can someone help me figure out how to recreate my database? Thanks, Bagus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- 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: isam to myisam
I did try --type='myisam' --type='isam' --type=myisam --type=isam --type='MYISAM' --type=MYISAM --type='ISAM' --type=ISAM All produced identical errors. Specifying the individual tables didn't change the error note. Any other ideas? Is this the right way to go about changing these ISD, ISM, and frm files to MYD, MYI and frm? Do I need to do this? Is there another way to get mysql to read the old ISD, ISM and frm files? Thanks, Bagus -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Thursday, January 20, 2005 10:58 PM To: Bagus; mysql@lists.mysql.com Subject: Re: isam to myisam At 22:46 -0600 1/20/05, Bagus wrote: Hi there, I'm running Freebsd 5.3 and just installed mysql-standard-4.1.9-unknown-freebsd4.7-i386. I'm able to run mysqld and mysql, etc. I used to run Freebsd 2.2 and I think it was mysql 3.21.33b... I'm not sure. My old system faded away rather abruptly and I didn't get a chance to do database backups or anything. I have one database that I'd like to recover. The database had 5 tables in it. Each one had three files associated with it, ie editors.ISD, editors.ISM and editors.frm. I have access to the tables but can't load them into my new mysql. I copied them into /usr/local/mysql/data/BagusDatabase and made sure the permissions were the same as my test directory. I have been reading around and thought I came across the answer with the mysql_convert_table_format script. I tried changing into my new BagusDatabase directory and running: mysql_convert_table_format --user='root' --password='mypassword' --type='ISA M' BagusDatabase According to the help message, the --type option is for specifying the table type that you want to convert the tables *to*. So it should be --type=MyISAM, I think. You could also execute this statement for each table that you want to convert: ALTER TABLE tbl_name ENGINE=MyISAM; The script then reports: Converting tables: converting editors Can't convert editors: Error Can't find file: 'editors.MYI' (errno: 2) I bet this is old hat for some of you out there. Can someone help me figure out how to recreate my database? Thanks, Bagus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL quota problem
Hi Iavor, On Fri, 2005-01-21 at 02:33, Iavor Stoev wrote: I have trouble with setting mysql database quota per user at my server. I`m using MySQL 4.0.22 on Linux with kernel 2.4.29. I`m using group quota on my server and all user`s files, mails and databases are with group $user (user`s mysql database directory is with setgid bit enabled to apply the group id to the new crated tables also). The problem is that when some user exceeds his quota the MySQL server dies for all users with: Disk is full writing. Waiting for someone to free space... Retry in 60 secs I suppose that this behaivior is caused, because the thread is waiting for free space, and when someone tries to write again to the same database, a very common example is some buletin board; a new thread is started that waits again, etc until the max threads are full and the MySQL server does nothing until it is restarted or the quota for the problem database is increased. Can anyone tell me is there a working solution for MySQL quota per database or a patch to return disk exceed error and not wait ? Because in my case the MySQL can`t write only to one database, all other hundreds of databases are operational, but server dies for all ... Coding this into the RDBMS is theoretically possible, and at least for InnoDB and other transactional engines (cluster, etc) you could trigger a rollback on quote full. However, such luxiory does not exist with MyISAM. While your setup is pretty nifty, I would suggest a different approach. Simply run a cron job that checks whatever you want to check, and adjusts user privileges accoringly (i.e. grant/revoke INSERT privs and any other privileges you want controlled). Depending on the cron job frequency that's slightly rougher in terms of disk space use, but the handling could be much cleaner. Regards, Arjen. -- Arjen Lentz, Community Relations Manager MySQL AB, www.mysql.com MySQL User Conference (Santa Clara CA, 18-21 April 2005) Early registration now open: www.mysqluc.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
