Re: Checking for good update
On Sun, 12 Mar 2006, Michael Stassen wrote:
To: [EMAIL PROTECTED]
From: Michael Stassen [EMAIL PROTECTED]
Subject: Re: Checking for good update
[EMAIL PROTECTED] wrote:
looks a bit strange to me.
$result = mysql_query($query) or die('Query couldn\'t
executed:'.mysql_error());
please try something like this:
Why? There's nothing wrong with the above statement.
I've never seen logic like that before. It looks to me like
fbsd_user is trying to use the OR operator outside an if
statement.
Is the mentioned in the php manual somewhere Michael?
I've not tested this - but it looks like you are mixing sending the
mysql query and testing for the result of the query at the same time,
which AFAIK is not possible.
You should try it. It works just fine, and isn't the problem. The
problem is that you cannot treat the result of an UPDATE as if it were a
SELECT.
Regards
Keith Roberts
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to Log Warnings and Errors from queries
Hi Rithish, Thank you all for your suggestion, I would definitely give it a shot. Regards, Ryan. On 3/13/06, Rithish Saralaya [EMAIL PROTECTED] wrote: Hello Ryan. I am more of a developer than a MySQL administrator. Hence, I would always favour applications logging query errors rather than being dependent on MySQL to generate a log for me. Of course, I may be wrong. You could write a query execution function, say exec_mysql_query(...) in one of you files, say 'Db.inc' and have it included in all your files. exec_mysql_query(...) will log all mysql errors into a file. and you may provide an web-interface (assuming this is a web application) to view/download the log files. Regards, Rithish. -Original Message- From: ryan lwf [mailto:[EMAIL PROTECTED] Sent: Friday, March 10, 2006 8:14 PM To: Dan Nelson Cc: mysql@lists.mysql.com Subject: Re: How to Log Warnings and Errors from queries Hi Dan, Noted with thanks. As such, is there a workaround to log problematic sql queries ran against the mysqld server ? Do I need to write separate script to do this ? Regards, Ryan. On 3/10/06, Dan Nelson [EMAIL PROTECTED] wrote: In the last episode (Mar 08), ryan lwf said: I understand that the option log-errors and log-warnings only logs server related internal errors. How do I enable logging errors from queries executed, so that I can fix the problematic query statement accordingly? The statement SHOW WARNINGS and SHOW ERRORS does not work on my server with mysqld-4.0.25 binary version. Those commands appeared in MySQL 4.1. Before then, warnings were simply counted. -- Dan Nelson [EMAIL PROTECTED]
Re: Checking for good update
[EMAIL PROTECTED] wrote:
On Sun, 12 Mar 2006, Michael Stassen wrote:
[EMAIL PROTECTED] wrote:
looks a bit strange to me.
$result = mysql_query($query) or die('Query couldn\'t
executed:'.mysql_error());
please try something like this:
Why? There's nothing wrong with the above statement.
I've never seen logic like that before. It looks to me like
fbsd_user is trying to use the OR operator outside an if
statement.
Is the mentioned in the php manual somewhere Michael?
I've not tested this - but it looks like you are mixing sending the
mysql query and testing for the result of the query at the same time,
which AFAIK is not possible.
You should try it. It works just fine, and isn't the problem. The
problem is that you cannot treat the result of an UPDATE as if it were a
SELECT.
Regards
Keith Roberts
Yes, this is documented. It's also standard practice (in perl and C as well).
OR is not part of an if statement, it is a logical operator.
http://www.php.net/manual/en/language.operators.logical.php A or B has a
value, true or false, depending on the values of A and of B. In fact, if A is
true, then A or B is certainly true, so there's no need to look at B at all.
This short-circuit evaluation, combined with the fact that every assignment
returns the assigned value
http://www.php.net/manual/en/language.expressions.php, makes a statement like
this possible.
$result = mysql_query($query) or die('Query error:'.mysql_error());
First, the function mysql_query() is called. Its return value is assigned to
$result, *and* returned as the return value of the assignment operator (=). Now
we know A. If mysql_query succeeded, its return value (A) evaluates as true, so
the or operation must be true, so no need to look at B. If, on the other hand,
A is false (mysql_query failed), we must evaluate B to determine the value of
the or expression. Of course, to determine the value of B, we have to call
the referenced function, die().
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback is not take effect on MySQL 5.0.18
2006/3/11, Truong Tan Son [EMAIL PROTECTED]: Dear Sir, On RedHat Enterprise 4, and MySQL 5.0.18, I did : mysql set autocommit=0; mysql savepoint abc; mysql insert something mysql rollback to save point abc; Query OK, 0 rows affected, 1 warning (0.00 sec) ^^ RollBack is NOT take effect. But on WindowsXP, it is GOOD. What is wrong ? Did you check if the table are innoDB ? -- Pooly Webzine Rock : 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]
Re: InnoDB Commit question
Rob, - Original Message - From: Rob Brooks [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, March 12, 2006 6:02 PM Subject: InnoDB Commit question --=_NextPart_000_0033_01C645BC.03223720 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi, we have a db with myisam and a single innodb table. 2 separate processes are inserting data into the myisam tables and the innodb table at the same time. We have noticed an issue where commits to the innodb table appear to be delayed until the process inserting into the myisam tables is finished. Has anyone else noticed this behavior? What could be causing this? which MySQL version you are using? Please describe in detail what MySQL statement(s) you use to insert into the MyISAM tables. Please post SHOW PROCESSLIST and SHOW INNODB STATUS\G readings when the commit is seemingly hung. I am not aware of any mechanism that should delay the processing of a commit in this case. Any help appreciated Rob Brooks The Brian Group LLC Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys 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]
Re: Checking for good update
Thankyou for that explanation Michael.
I shall look into using that construct in my own code now!
Apologies to fbsd_user for my previous comments on his
coding style.
Regards
Keith Roberts
In theory, theory and practice are the same;
In practice they are not.
On Mon, 13 Mar 2006, Michael Stassen wrote:
To: [EMAIL PROTECTED]
From: Michael Stassen [EMAIL PROTECTED]
Subject: Re: Checking for good update
[EMAIL PROTECTED] wrote:
On Sun, 12 Mar 2006, Michael Stassen wrote:
[EMAIL PROTECTED] wrote:
looks a bit strange to me.
$result = mysql_query($query) or die('Query couldn\'t
executed:'.mysql_error());
please try something like this:
Why? There's nothing wrong with the above statement.
I've never seen logic like that before. It looks to me like fbsd_user
is trying to use the OR operator outside an if statement.
Is the mentioned in the php manual somewhere Michael?
I've not tested this - but it looks like you are mixing
sending the
mysql query and testing for the result of the query at the
same time,
which AFAIK is not possible.
You should try it. It works just fine, and isn't the problem.
The
problem is that you cannot treat the result of an UPDATE as if it
were a
SELECT.
Regards
Keith Roberts
Yes, this is documented. It's also standard practice (in perl and C as
well).
OR is not part of an if statement, it is a logical operator.
http://www.php.net/manual/en/language.operators.logical.php A or B
has a value, true or false, depending on the values of A and of B. In
fact, if A is true, then A or B is certainly true, so there's no need to
look at B at all. This short-circuit evaluation, combined with the fact
that every assignment returns the assigned value
http://www.php.net/manual/en/language.expressions.php, makes a statement
like this possible.
$result = mysql_query($query) or die('Query error:'.mysql_error());
First, the function mysql_query() is called. Its return value is assigned
to $result, *and* returned as the return value of the assignment operator
(=). Now we know A. If mysql_query succeeded, its return value (A)
evaluates as true, so the or operation must be true, so no need to look at
B. If, on the other hand, A is false (mysql_query failed), we must
evaluate B to determine the value of the or expression. Of course, to
determine the value of B, we have to call the referenced function, die().
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQLMigrationsToolkit ...
Hallo, I'm using MySQL MigrationsToolkit 1.0.23rc and while transfering big Table (MySQL 4.0.x to MySQL 5.x, 4.000.000 entries) I get this message: The SQL create statements could not be created (error: 0). MigrationMysql.dataBulkTransfer :Java heap space Details: What can I do? Best Regards, Rafal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQLMigrationsToolkit ...
At 12:31 13.03.2006, Rafal Kedziorski wrote: Hallo, I'm using MySQL MigrationsToolkit 1.0.23rc and while transfering big Table (MySQL 4.0.x to MySQL 5.x, 4.000.000 entries) I get this message: The SQL create statements could not be created (error: 0). MigrationMysql.dataBulkTransfer :Java heap space Details: I choosed: Execute Bulk Transfer Best Regards, Rafal What can I do? Best Regards, Rafal -- 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: MySQLMigrationsToolkit ...
Rafal Kedziorski wrote: The SQL create statements could not be created (error: 0). MigrationMysql.dataBulkTransfer :Java heap space What can I do? That is Java memory related problem. Check out where it invoke the Java VM if possible and add memory parametars like -Xms256m -Xmx900m (it assume that you have 1GB memory, if you have 512MB put -XMx420m). Hope it helps. -- Mladen Adamovic http://home.blic.net/adamm http://www.shortopedia.com http://www.froola.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
updating federated table affects only one row per request, should update more rows ..
I try to update multiple rows in a federated table with one query, where field a=x and field b=x.. There are 4 rows that match these criteria (selecting returns 4 rows) but trying to update using the following query updates only one record. calling the same query 4 times, updates only one record per request. whats going wrong here, it's a simple basic query.. updates only 1 record (should update 4 records) --- update tbl_lager set fld_shopid=0 ,fld_orderid=0 where fld_orderid=10 and fld_shopid=3 repeating this query 4 times updates 1 record/update the 5th update updates no record (correct) a similar query (select instead of update) returns 4 records, as it should .. - select * from tbl_lager where fld_orderid=10 and fld_shopid=3 returns 4 records, correct e.g. this query runs correct ans updates 4 records: --- update tbl_lager set fld_shopid=0 ,fld_orderid=0 where fld_pid=1911 does anybody have a similar problem/ any tips? The queries are executed on mySql 5.0.18-max, the server containing the physical data is running 4.1.18-nt. (Both servers are running on the same machine (my notebook, running xp home, sp2, mysql 5 on port 3306 and mysql 4.1 on port 3307) heres the structure of the federated table: CREATE TABLE `tbl_lager` ( `fld_autoid` int(11) NOT NULL auto_increment, `fld_pid` int(11) NOT NULL default '0', `fld_hid` int(11) NOT NULL default '0', `fld_ekp` decimal(14,2) NOT NULL default '0.00', `fld_mwst` int(11) NOT NULL default '0', `fld_indatum` datetime NOT NULL default '-00-00 00:00:00', `fld_status` int(2) NOT NULL default '0', `fld_orderid` int(11) NOT NULL default '0', `fld_shopid` int(3) NOT NULL default '0', PRIMARY KEY (`fld_autoid`), UNIQUE KEY `fld_autoid` (`fld_autoid`), KEY `fld_pid` (`fld_pid`), KEY `fld_hid` (`fld_hid`), KEY `fld_mwst` (`fld_mwst`), KEY `fld_orderid` (`fld_orderid`), KEY `fld_status` (`fld_status`), KEY `fld_shopid` (`fld_shopid`) ) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://user:[EMAIL PROTECTED]:3307/dbname/tbl_lager'; thx -- Sebastian Mork [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
simple query
Hi, all I have a table which has a column with time. It's format is like -00-00 00:00:00 (default value). I want to get the minimum and maximum values for this cloumn. Obviously, -00-00 00:00:00 isn't the minimum value I want. What I did for the minimum time, suppose the column name is ctime, the table name is tbl_a: select ctime form tbl_a where ctime-00-00 00:00:00 order by ctime limit 1; Using this query, I am able to get the minimum time value. But what is the query for max time value? I guess it's something about reverseing the order by, but I couldn't find it. Could anyone please give me a hand? Thanks a lot. Xiaobo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple query
Hope this helps: select min(ctime), max(ctime) from tbl_a where ctime != -00-00 00:00:00 Best regards, Diego - Original Message - From: Xiaobo Chen [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, March 13, 2006 11:17 AM Subject: simple query Hi, all I have a table which has a column with time. It's format is like -00-00 00:00:00 (default value). I want to get the minimum and maximum values for this cloumn. Obviously, -00-00 00:00:00 isn't the minimum value I want. What I did for the minimum time, suppose the column name is ctime, the table name is tbl_a: select ctime form tbl_a where ctime-00-00 00:00:00 order by ctime limit 1; Using this query, I am able to get the minimum time value. But what is the query for max time value? I guess it's something about reverseing the order by, but I couldn't find it. Could anyone please give me a hand? Thanks a lot. Xiaobo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Información de NOD32 1.1440 (20060312) __ Este mensaje ha sido analizado con NOD32 antivirus system http://www.nod32.com ___ 1GB gratis, Antivirus y Antispam Correo Yahoo!, el mejor correo web del mundo http://correo.yahoo.com.ar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Compare lists Query?
Dear All, I had some issues in past with timestamp fields as a result I am unable to upgrade to mysql 4.1 version. I am sure below the query recommended by Josh works with 4.1 or above. Would it be possible to rewrite this query for 3.23 version? Again, thank you Josh and all others for your kind help and comments. Josh [EMAIL PROTECTED] wrote: Here's one method: SELECT cl1.list_name, count(*) as count FROM customerList cl1 WHERE cl1.id IN (SELECT cl2.id FROM customerList cl2 WHERE cl2.list_name='CA10') and cl1.list_name != 'CA10' GROUP BY cl1.list_name --- Yesmin Patwary wrote: Good morning all, We have 12 customer lists: CA01, CA02, .,CA12. Table: customerList +---+--+ | list_name | id | +---+--+ | CA10 | 20BE | | CA07 | 20BE | | CA11 | 20BE | | CA03 | 20BE | | CA10 | NQCR | | CA04 | NQCR | | CA02 | MVYK | | CA10 | 0BEC | | AND SO ON. | +---+--+ Each list has 25 to 350 customers. Same customer_id may exist in multiple lists. We need to compare CA10 list customer_ids with other 11 lists to find matching id count by list_name. The query output should be something similar below: +--+---+ | list_name |count | +--+---+ | CA05 | 60 | | CA07 | 42 | | CA01 | 35 | | CA03 | 28 | | CA09 | 15 | | AND SO ON | +---+--+ Can this be done with a SELECT statement without using perl or php? Thanks in advance for any help. - Yahoo! Mail Bring photos to life! New PhotoMail makes sharing a breeze.
mysql5 options file location
I am building mysql5 latest from source on Solaris. The location of the options file is very confusing and does not work according to the online documentation. For example, I have --basedir of /opt/csw/mysql5 and --datadir of /opt/csw/mysql5/var. If I put my.cnf in datadir or in basedir, the file is not used. I am using mysqld_safe to start mysqld. my.cnf options will only work if I put the file in /etc/ './libexec/mysqld --verbose --help' returns: Default options are read from the following files in the given order: /etc/my.cnf ~/.my.cnf This is very different from my mysql4 builds from source, which include the documented server-specific file listed after /etc/my.cnf How can I get mysql5 to use a server-specfic options file? Am I missing a configure option or defines for mysql5? Thanks, Alex -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Optimization Question
Robert DiFalco [EMAIL PROTECTED] wrote on 03/11/2006 12:43:43 PM: In a previous database engine I was using an IN was more optimal than a . So, for example: SELECT * FROM table WHERE table.type IN (1,2,3); Where the possible values of type are 0-3, was appreciably faster than: SELECT * FROM table WHERE table.type 0; I've been playing with the Query Browser and checking out the optimization documents and haven't been able to make a clear call on whether or not this is also the case with MySQL/InnoDB. TIA, R. YES, YES, YES! This is definitely an optimization. When you say IN or =, you are asking for matching values. Matches can come from indexes. When you say or NOT IN, you are asking for everything BUT matches. In order to evaluate a negative, the database engine (and this is usually true regardless of database server) almost always performs a full table scan to test every row to make sure it is either or NOT IN. At the very best, they have to perform a full index scan which is still less efficient than ranged or values-based lookups. It's when you get into the situation where you are matching against dozens of IN-clause items that you may run into slowdowns again. Until you reach 2 or 3 dozen terms (depending on your hardware) you should be faster with an IN comparison than a or a NOT IN comparison. An optimization to search for BUT a term or two is to create a temporary table of all of your terms and delete the exact ones you want to exclude. Put an index on yoru temp table then JOIN that back into your query again (replacing the huge IN clause). The database will match index to index and things will get fast again. This technique can scale up to some really big queries. Always try to code for the affirmative tests. Your users will thank you. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Compare lists Query?
Yesmin Patwary wrote: Dear All, I had some issues in past with timestamp fields as a result I am unable to upgrade to mysql 4.1 version. I am sure below the query recommended by Josh works with 4.1 or above. Would it be possible to rewrite this query for 3.23 version? Again, thank you Josh and all others for your kind help and comments. Josh [EMAIL PROTECTED] wrote: Here's one method: SELECT cl1.list_name, count(*) as count FROM customerList cl1 WHERE cl1.id IN (SELECT cl2.id FROM customerList cl2 WHERE cl2.list_name='CA10') and cl1.list_name != 'CA10' GROUP BY cl1.list_name SELECT cl1.list_name, COUNT(*) AS count FROM customerList cl1 INNER JOIN customerList cl2 USING (id) WHERE cl1.name = 'CA10' AND cl2.name != 'CA10' GROUP BY cl1.list_name; PB - --- Yesmin Patwary wrote: Good morning all, We have 12 customer lists: CA01, CA02, .,CA12. Table: customerList +---+--+ | list_name | id | +---+--+ | CA10 | 20BE | | CA07 | 20BE | | CA11 | 20BE | | CA03 | 20BE | | CA10 | NQCR | | CA04 | NQCR | | CA02 | MVYK | | CA10 | 0BEC | | AND SO ON. | +---+--+ Each list has 25 to 350 customers. Same customer_id may exist in multiple lists. We need to compare CA10 list customer_ids with other 11 lists to find matching id count by list_name. The query output should be something similar below: +--+---+ | list_name |count | +--+---+ | CA05 | 60 | | CA07 | 42 | | CA01 | 35 | | CA03 | 28 | | CA09 | 15 | | AND SO ON | +---+--+ Can this be done with a SELECT statement without using perl or php? Thanks in advance for any help. - Yahoo! Mail Bring photos to life! New PhotoMail makes sharing a breeze. No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.2.1/279 - Release Date: 3/10/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.2.1/279 - Release Date: 3/10/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Checking for good update
Thank you Michael. I learned a lot from your detailed explanation
of how the update and select functions work in relation to
checking for good execution.
I used
if (mysql_affected_rows() == 1)
and got the results I was after.
I am stilling having problem with users browser
caching the screen, but will post that as separate question.
Again thanks for your expert help.
-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED]
Sent: Monday, March 13, 2006 4:18 AM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: Checking for good update
[EMAIL PROTECTED] wrote:
On Sun, 12 Mar 2006, Michael Stassen wrote:
[EMAIL PROTECTED] wrote:
looks a bit strange to me.
$result = mysql_query($query) or die('Query couldn\'t
executed:'.mysql_error());
please try something like this:
Why? There's nothing wrong with the above statement.
I've never seen logic like that before. It looks to me like
fbsd_user is trying to use the OR operator outside an if
statement.
Is the mentioned in the php manual somewhere Michael?
I've not tested this - but it looks like you are mixing sending
the
mysql query and testing for the result of the query at the same
time,
which AFAIK is not possible.
You should try it. It works just fine, and isn't the problem.
The
problem is that you cannot treat the result of an UPDATE as if it
were a
SELECT.
Regards
Keith Roberts
Yes, this is documented. It's also standard practice (in perl and C
as well).
OR is not part of an if statement, it is a logical operator.
http://www.php.net/manual/en/language.operators.logical.php A or
B has a
value, true or false, depending on the values of A and of B. In
fact, if A is
true, then A or B is certainly true, so there's no need to look at
B at all.
This short-circuit evaluation, combined with the fact that every
assignment
returns the assigned value
http://www.php.net/manual/en/language.expressions.php, makes a
statement like
this possible.
$result = mysql_query($query) or die('Query
error:'.mysql_error());
First, the function mysql_query() is called. Its return value is
assigned to
$result, *and* returned as the return value of the assignment
operator (=). Now
we know A. If mysql_query succeeded, its return value (A) evaluates
as true, so
the or operation must be true, so no need to look at B. If, on the
other hand,
A is false (mysql_query failed), we must evaluate B to determine the
value of
the or expression. Of course, to determine the value of B, we
have to call
the referenced function, die().
Michael
--
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: Compare lists Query?
You could rotate the output... basically get 1 row with 11 columns (CA01_count,CA02_count,...,CA12_count) (leaving out CA10) joining all 12 tables together... Or... perhaps we can help with the timestamp issues you are having and get you upgraded to later version of mysql that supports nested SELECT statements. --- Yesmin Patwary [EMAIL PROTECTED] wrote: Dear All, I had some issues in past with timestamp fields as a result I am unable to upgrade to mysql 4.1 version. I am sure below the query recommended by Josh works with 4.1 or above. Would it be possible to rewrite this query for 3.23 version? Again, thank you Josh and all others for your kind help and comments. Josh [EMAIL PROTECTED] wrote: Here's one method: SELECT cl1.list_name, count(*) as count FROM customerList cl1 WHERE cl1.id IN (SELECT cl2.id FROM customerList cl2 WHERE cl2.list_name='CA10') and cl1.list_name != 'CA10' GROUP BY cl1.list_name --- Yesmin Patwary wrote: Good morning all, We have 12 customer lists: CA01, CA02, .,CA12. Table: customerList +---+--+ | list_name | id | +---+--+ | CA10 | 20BE | | CA07 | 20BE | | CA11 | 20BE | | CA03 | 20BE | | CA10 | NQCR | | CA04 | NQCR | | CA02 | MVYK | | CA10 | 0BEC | | AND SO ON. | +---+--+ Each list has 25 to 350 customers. Same customer_id may exist in multiple lists. We need to compare CA10 list customer_ids with other 11 lists to find matching id count by list_name. The query output should be something similar below: +--+---+ | list_name |count | +--+---+ | CA05 | 60 | | CA07 | 42 | | CA01 | 35 | | CA03 | 28 | | CA09 | 15 | | AND SO ON | +---+--+ Can this be done with a SELECT statement without using perl or php? Thanks in advance for any help. - Yahoo! Mail Bring photos to life! New PhotoMail makes sharing a breeze. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Auto increment Primary Index fields in replicated db
All, We are using circular replication now on db's that were originally stand alone. One problem we have is that all the primary index fields for most of the tables are auto increment fields. This prevents us from writing to both db servers because of confilicting INDEX entries. Is there some way to have this work? Someone on another msg board told me to look at Auto_increment_increment and Auto_increment_offset to accompish this but I don't see anything like that in the MySQL docs. Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help regarding a simple query
Hi, I am searching for a query where i can have pattern matching without considering the cases.You can consider the following example for detailed description of what i want exactly. Let my table X consists of following data Name --- venu venup venugopla VenugOpal VENU papasani papasni pvenu Now i need to get all the records which consists of the string venu(case should not be considered either case should be).i.e i should get 1,2,3,4,5,8 records I will be very much thankful if any of you give me the query for this. Thanks in Advance, Regards, venu. (sorry for my poor English)
MySQLMigrationsToolkit 1.0.23rc
hi, after migration from MySQL 4.0.26 to MySQL 5.0.18 I'm missing auto_increment on MySQL 5 side in appropriates tables. Best Regards, Rafal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help regarding a simple query
-Original Message- From: VenuGopal Papasani [mailto:[EMAIL PROTECTED] Sent: Monday, March 13, 2006 10:33 To: mysql@lists.mysql.com Subject: Help regarding a simple query Hi, I am searching for a query where i can have pattern matching without considering the cases.You can consider the following example for detailed description of what i want exactly. Let my table X consists of following data Name --- venu venup venugopla VenugOpal VENU papasani papasni pvenu Now i need to get all the records which consists of the string venu(case should not be considered either case should be).i.e i should get 1,2,3,4,5,8 records I will be very much thankful if any of you give me the query for this. Thanks in Advance, Regards, venu. (sorry for my poor English) Won't this work? Select * from X where name like '%venu%' jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Optimization Question
Shawn, Any performance gains for specifying type 0 than type 0 ? R. From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, March 13, 2006 6:37 AM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Query Optimization Question Robert DiFalco [EMAIL PROTECTED] wrote on 03/11/2006 12:43:43 PM: In a previous database engine I was using an IN was more optimal than a . So, for example: SELECT * FROM table WHERE table.type IN (1,2,3); Where the possible values of type are 0-3, was appreciably faster than: SELECT * FROM table WHERE table.type 0; I've been playing with the Query Browser and checking out the optimization documents and haven't been able to make a clear call on whether or not this is also the case with MySQL/InnoDB. TIA, R. YES, YES, YES! This is definitely an optimization. When you say IN or =, you are asking for matching values. Matches can come from indexes. When you say or NOT IN, you are asking for everything BUT matches. In order to evaluate a negative, the database engine (and this is usually true regardless of database server) almost always performs a full table scan to test every row to make sure it is either or NOT IN. At the very best, they have to perform a full index scan which is still less efficient than ranged or values-based lookups. It's when you get into the situation where you are matching against dozens of IN-clause items that you may run into slowdowns again. Until you reach 2 or 3 dozen terms (depending on your hardware) you should be faster with an IN comparison than a or a NOT IN comparison. An optimization to search for BUT a term or two is to create a temporary table of all of your terms and delete the exact ones you want to exclude. Put an index on yoru temp table then JOIN that back into your query again (replacing the huge IN clause). The database will match index to index and things will get fast again. This technique can scale up to some really big queries. Always try to code for the affirmative tests. Your users will thank you. 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: Query Optimization Question
Yes, a ranged query should respond faster than a negation. In some cases you can seriously improve query performance for a negation query if you split it into two range queries unioned together. Here is a pseudo example: This query should be slow due to the table scan it takes to test the condition: SELECT ... FROM ... WHERE indexfield 16 This query should be noticeably faster due to using ranged queries (partial index ranged matches): ( SELECT ... FROM ... WHERE indexfield 16 ) UNION ( SELECT ... FROM ... WHERE indexfield 16 ) Of course, if you have to do a table scan ANYWAY (because a value you have in a constraint is not in an index) then this won't help. This optimization is all about getting the engine to use an index whenever it can instead of performing a table scan. Of course, that is not always possible. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Robert DiFalco [EMAIL PROTECTED] wrote on 03/13/2006 10:48:29 AM: Shawn, Any performance gains for specifying type 0 than type 0 ? R. From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, March 13, 2006 6:37 AM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Query Optimization Question Robert DiFalco [EMAIL PROTECTED] wrote on 03/11/2006 12:43:43 PM: In a previous database engine I was using an IN was more optimal than a . So, for example: SELECT * FROM table WHERE table.type IN (1,2,3); Where the possible values of type are 0-3, was appreciably faster than: SELECT * FROM table WHERE table.type 0; I've been playing with the Query Browser and checking out the optimization documents and haven't been able to make a clear call on whether or not this is also the case with MySQL/InnoDB. TIA, R. YES, YES, YES! This is definitely an optimization. When you say IN or =, you are asking for matching values. Matches can come from indexes. When you say or NOT IN, you are asking for everything BUT matches. In order to evaluate a negative, the database engine (and this is usually true regardless of database server) almost always performs a full table scan to test every row to make sure it is either or NOT IN. At the very best, they have to perform a full index scan which is still less efficient than ranged or values-based lookups. It's when you get into the situation where you are matching against dozens of IN-clause items that you may run into slowdowns again. Until you reach 2 or 3 dozen terms (depending on your hardware) you should be faster with an IN comparison than a or a NOT IN comparison. An optimization to search for BUT a term or two is to create a temporary table of all of your terms and delete the exact ones you want to exclude. Put an index on yoru temp table then JOIN that back into your query again (replacing the huge IN clause). The database will match index to index and things will get fast again. This technique can scale up to some really big queries. Always try to code for the affirmative tests. Your users will thank you. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Query Optimization Question
Interesting, that seems like an optimization the query optimizer could do itself when it sees a operator on a indexed numeric. From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, March 13, 2006 8:01 AM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: RE: Query Optimization Question Yes, a ranged query should respond faster than a negation. In some cases you can seriously improve query performance for a negation query if you split it into two range queries unioned together. Here is a pseudo example: This query should be slow due to the table scan it takes to test the condition: SELECT ... FROM ... WHERE indexfield 16 This query should be noticeably faster due to using ranged queries (partial index ranged matches): (SELECT ... FROM ... WHERE indexfield 16 ) UNION ( SELECT ... FROM ... WHERE indexfield 16 ) Of course, if you have to do a table scan ANYWAY (because a value you have in a constraint is not in an index) then this won't help. This optimization is all about getting the engine to use an index whenever it can instead of performing a table scan. Of course, that is not always possible. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Robert DiFalco [EMAIL PROTECTED] wrote on 03/13/2006 10:48:29 AM: Shawn, Any performance gains for specifying type 0 than type 0 ? R. From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, March 13, 2006 6:37 AM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Query Optimization Question Robert DiFalco [EMAIL PROTECTED] wrote on 03/11/2006 12:43:43 PM: In a previous database engine I was using an IN was more optimal than a . So, for example: SELECT * FROM table WHERE table.type IN (1,2,3); Where the possible values of type are 0-3, was appreciably faster than: SELECT * FROM table WHERE table.type 0; I've been playing with the Query Browser and checking out the optimization documents and haven't been able to make a clear call on whether or not this is also the case with MySQL/InnoDB. TIA, R. YES, YES, YES! This is definitely an optimization. When you say IN or =, you are asking for matching values. Matches can come from indexes. When you say or NOT IN, you are asking for everything BUT matches. In order to evaluate a negative, the database engine (and this is usually true regardless of database server) almost always performs a full table scan to test every row to make sure it is either or NOT IN. At the very best, they have to perform a full index scan which is still less efficient than ranged or values-based lookups. It's when you get into the situation where you are matching against dozens of IN-clause items that you may run into slowdowns again. Until you reach 2 or 3 dozen terms (depending on your hardware) you should be faster with an IN comparison than a or a NOT IN comparison. An optimization to search for BUT a term or two is to create a temporary table of all of your terms and delete the exact ones you want to exclude. Put an index on yoru temp table then JOIN that back into your query again (replacing the huge IN clause). The database will match index to index and things will get fast again. This technique can scale up to some really big queries. Always try to code for the affirmative tests. Your users will thank you. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Help regarding a simple query
Now i need to get all the records which consists of the string
venu(case should not be considered either case should be).i.e i should get
1,2,3,4,5,8 records
A simple way is ...
...
WHERE LOCATE('venu', col_name ) 0
...
or if the column is [VAR]BINARY, LOCATE('venu',CAST(col_name AS CHAR))
for case insensitivity.
PB
-
VenuGopal Papasani wrote:
Hi,
I am searching for a query where i can have pattern matching without
considering the cases.You can consider the following example for detailed
description of what i want exactly.
Let my table X consists of following data
Name
---
venu
venup
venugopla
VenugOpal
VENU
papasani
papasni
pvenu
Now i need to get all the records which consists of the string
venu(case should not be considered either case should be).i.e i should get
1,2,3,4,5,8 records
I will be very much thankful if any of you give me the query for this.
Thanks in Advance,
Regards,
venu.
(sorry for my poor English)
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.2.1/279 - Release Date: 3/10/2006
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.2.1/279 - Release Date: 3/10/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Auto increment Primary Index fields in replicated db
Please refer to these two links, they would give you a clear explaination. there is also an example in the second link which you can easily test . http://dev.mysql.com/doc/refman/5.0/en/replication-auto-increment.html http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html ( scroll down for variables auto_increment_*) Kishore Jalleda On 3/13/06, Jeff [EMAIL PROTECTED] wrote: All, We are using circular replication now on db's that were originally stand alone. One problem we have is that all the primary index fields for most of the tables are auto increment fields. This prevents us from writing to both db servers because of confilicting INDEX entries. Is there some way to have this work? Someone on another msg board told me to look at Auto_increment_increment and Auto_increment_offset to accompish this but I don't see anything like that in the MySQL docs. Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
users browser caching the screen
Now I know what I am going to talk about is not directly related to this mysql list, but I am in need of some concept ideas. To set the background. Its a very common practice in the registration process of a new user to verify the users email address is valid by sending a email to the entered email address with a link in it to a screen that updates the users emailed verified flag in his table record. I have such a process. Once a week I review my apache activity log and I noticed a lot of log records for the file that process the link to update the users email verified flag, (over 1500 from same ip address). To me this looked like an attack to break into my web application. Research and testing indicates that the screen is Cached by the users browser and he is changing the passed link info repeatedly in effort to break in. This screen is the only one that does not have session security control because its launched from the verify email I sent him. Now my registration sign up screen has a Captcha Security Code Random-Noisy-Image and part of that is a string of headers to the browser to stop caching. They look like this. // send several headers to make sure the image is not cached // taken directly from the PHP Manual // Date in the past header(Expires: Mon, 26 Jul 1997 05:00:00 GMT); // always modified header(Last-Modified: . gmdate(D, d M Y H:i:s) . GMT); // HTTP/1.1 header(Cache-Control: no-store, no-cache, must-revalidate); header(Cache-Control: post-check=0, pre-check=0, false); // HTTP/1.0 header(Pragma: no-cache); Now my desire is to somehow have the screen that is the target of the email link to issue these headers before displaying its html code so the user can not any longer run a script against this screen trying to break in. Is this possible and if so how. Is this kind of attack happening to anyone else? 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]
Can i have query for this
Hi, I am searching for a query where i can have pattern matching without considering the cases.You can consider the following example for detailed description of what i want exactly. Let my table X consists of following data Name --- venu venup venugopla VenugOpal VENU papasani papasni pvenu venu--k venu-t Now i need to get all the records which consists of the string venu(case should not be considered either case should be).i.e i should get 1,2,3,4,5,8 records but i should not get 9 and 10th records which is also consists of venu. I will be very much thankful if any of you give me the query for this. Thanks in Advance, Regards, venu. (sorry for my poor English)
E/R Tool
Hello , Is there a mysql or any other tool that generates a E/R diagram using an existing mysql database. Thank You Vinay
Problems with timestamp field after upgrading MySQL Server.
Dear All, First of all, I would like to thank to Josh and Peter Brawley for their kind help on previous issue. Here is another Issue: There are many tables that have timestamp field with 8 char (mmdd). I use this format all over our website and to create various reports. Below is static php code that I used numerous places: ? $year=substr($date, 0, 4); $month=substr($date, 4, 2); $day=substr($date, 6, 2); ? All timestamp dependent reports and web pages had problems, once I upgrade to MySQL 4.1.12. At the end I had to downgrade to 3.23 to bring web site operation to normal. Is there anyway to preserve old timestamp format after upgrading? MySQL - 3.23 +++-- | Field | Type | Default (mmdd) +++-- |log_date|timestamp(8)| MySQL - 4.1.12 ++---+ | Field | Type| Default ++---+ |log_date| timestamp |-00-00 00:00:00 Josh [EMAIL PROTECTED] wrote: You could rotate the output... basically get 1 row with 11 columns (CA01_count,CA02_count,...,CA12_count) (leaving out CA10) joining all 12 tables together... Or... perhaps we can help with the timestamp issues you are having and get you upgraded to later version of mysql that supports nested SELECT statements. --- Yesmin Patwary wrote: Dear All, I had some issues in past with timestamp fields as a result I am unable to upgrade to mysql 4.1 version. I am sure below the query recommended by Josh works with 4.1 or above. Would it be possible to rewrite this query for 3.23 version? Again, thank you Josh and all others for your kind help and comments. Josh wrote: Here's one method: SELECT cl1.list_name, count(*) as count FROM customerList cl1 WHERE cl1.id IN (SELECT cl2.id FROM customerList cl2 WHERE cl2.list_name='CA10') and cl1.list_name != 'CA10' GROUP BY cl1.list_name --- Yesmin Patwary wrote: Good morning all, We have 12 customer lists: CA01, CA02, .,CA12. Table: customerList +---+--+ | list_name | id | +---+--+ | CA10 | 20BE | | CA07 | 20BE | | CA11 | 20BE | | CA03 | 20BE | | CA10 | NQCR | | CA04 | NQCR | | CA02 | MVYK | | CA10 | 0BEC | | AND SO ON. | +---+--+ Each list has 25 to 350 customers. Same customer_id may exist in multiple lists. We need to compare CA10 list customer_ids with other 11 lists to find matching id count by list_name. The query output should be something similar below: +--+---+ | list_name |count | +--+---+ | CA05 | 60 | | CA07 | 42 | | CA01 | 35 | | CA03 | 28 | | CA09 | 15 | | AND SO ON | +---+--+ Can this be done with a SELECT statement without using perl or php? Thanks in advance for any help. - Yahoo! Mail Bring photos to life! New PhotoMail makes sharing a breeze. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Yahoo! Mail Use Photomail to share photos without annoying attachments.
Problems with timestamp field after upgrading MySQL Server.
Dear All, First of all, I would like to thank to Josh and Peter Brawley for their kind help on previous issue. Here is another Issue: There are many tables that have timestamp field with 8 char (mmdd). I use this format all over our website and to create various reports. Below is static php code that I used numerous places: ? $year=substr($date, 0, 4); $month=substr($date, 4, 2); $day=substr($date, 6, 2); ? All timestamp dependent reports and web pages had problems, once I upgrade to MySQL 4.1.12. At the end I had to downgrade to 3.23 to bring web site operation to normal. Is there anyway to preserve old timestamp format after upgrading? MySQL - 3.23 +++-- | Field | Type | Default (mmdd) +++-- |log_date|timestamp(8)| MySQL - 4.1.12 ++---+ | Field | Type| Default ++---+ |log_date| timestamp |-00-00 00:00:00 Josh [EMAIL PROTECTED] wrote: You could rotate the output... basically get 1 row with 11 columns (CA01_count,CA02_count,...,CA12_count) (leaving out CA10) joining all 12 tables together... Or... perhaps we can help with the timestamp issues you are having and get you upgraded to later version of mysql that supports nested SELECT statements. --- Yesmin Patwary wrote: Dear All, I had some issues in past with timestamp fields as a result I am unable to upgrade to mysql 4.1 version. I am sure below the query recommended by Josh works with 4.1 or above. Would it be possible to rewrite this query for 3.23 version? Again, thank you Josh and all others for your kind help and comments. Josh wrote: Here's one method: SELECT cl1.list_name, count(*) as count FROM customerList cl1 WHERE cl1.id IN (SELECT cl2.id FROM customerList cl2 WHERE cl2.list_name='CA10') and cl1.list_name != 'CA10' GROUP BY cl1.list_name --- Yesmin Patwary wrote: Good morning all, We have 12 customer lists: CA01, CA02, .,CA12. Table: customerList +---+--+ | list_name | id | +---+--+ | CA10 | 20BE | | CA07 | 20BE | | CA11 | 20BE | | CA03 | 20BE | | CA10 | NQCR | | CA04 | NQCR | | CA02 | MVYK | | CA10 | 0BEC | | AND SO ON. | +---+--+ Each list has 25 to 350 customers. Same customer_id may exist in multiple lists. We need to compare CA10 list customer_ids with other 11 lists to find matching id count by list_name. The query output should be something similar below: +--+---+ | list_name |count | +--+---+ | CA05 | 60 | | CA07 | 42 | | CA01 | 35 | | CA03 | 28 | | CA09 | 15 | | AND SO ON | +---+--+ Can this be done with a SELECT statement without using perl or php? Thanks in advance for any help. - Yahoo! Mail Bring photos to life! New PhotoMail makes sharing a breeze. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Yahoo! Mail Use Photomail to share photos without annoying attachments.
E/R Tool
Hello , Is there a mysql or any other tool that generates a E/R diagram using an existing mysql database. Thank You Vinay
Re: mysql5 options file location
Hi Alex. It seems that mysqld and all the client programs insist on reading /etc/my.cnf first. To overide this behaviour for a particular instance of mysqld you need to pass the --defaults-file option as the FIRST parameter to mysqld_safe or mysqld if you are not using mysqld_safe. --defaults-extra-file=path The name of an option file to be read in addition to the usual option files. If given, this option must be first. --defaults-file=path The name of an option file to be read instead of the usual option files. If given, this option must be first. --no-defaults Do not read any option files. If given, this option must be first. More options are found in /usr/local/mysql-5.0.18/man/man1/mysqld_safe.1 I found this behaviour very annoying at first. But I now use this to my advantage. I have split my my.cnf file like this: /etc/my.cnf only has parameters used by mysql client programs. the my.cnf that lives in the mysqld installation directory only contains directives pertinent to that particular version of mysqld, and nothing there for the client programs. That way you have total control over all the parameters passed to all client programs in one central place, /etc/my.cnf. You do not have to worry about mysqld reading the wrong parameters for its invocation, because you have a seperate my.cnf just for that version of mysqld. I have written the following script to start a particular version of mysqld. This lives in /usr/local/mysql-version/bin/ #! /bin/sh # # start the MySQL database server /usr/local/mysql-5.0.18/bin/mysqld \ --defaults-file=/usr/local/mysql-5.0.18/my.cnf \ --port= \ --socket=/var/lib/mysql/mysql.sock \ --pid=/var/lib/mysql/laptop.pid \ --user=mysql \ --datadir=/var/lib/mysql And this script gets called from /etc/init.d/boot.local when the machine boots up. /etc/init.d/halt.local calls the following script to shutdown the mysqld server gracefully. #! /bin/sh # # stop the MySQL database server /usr/local/mysql-5.0.18/bin/mysqladmin shutdown \ -usqlsuperuser -p \ --socket=/var/lib/mysql/mysql.sock The other advantage of passing parameters on the command-line to mysqld is that you can actually see, using a visual process manager like http://www.student.nada.kth.se/~f91-men/qps/ * if mysqld is running OK * what parameters you passed to mysqld, eg the port, socket, datadir etc, each mysqld is using * how many different versions of mysqld you have running I find this very helpfull when running two versions of mysqld at a time, eg testing a newer version against an already installed version, before removing the older version. HTH Keith In theory, theory and practice are the same; In practice they are not. On Mon, 13 Mar 2006, Alex Moore wrote: To: mysql@lists.mysql.com From: Alex Moore [EMAIL PROTECTED] Subject: mysql5 options file location I am building mysql5 latest from source on Solaris. The location of the options file is very confusing and does not work according to the online documentation. For example, I have --basedir of /opt/csw/mysql5 and --datadir of /opt/csw/mysql5/var. If I put my.cnf in datadir or in basedir, the file is not used. I am using mysqld_safe to start mysqld. my.cnf options will only work if I put the file in /etc/ './libexec/mysqld --verbose --help' returns: Default options are read from the following files in the given order: /etc/my.cnf ~/.my.cnf This is very different from my mysql4 builds from source, which include the documented server-specific file listed after /etc/my.cnf How can I get mysql5 to use a server-specfic options file? Am I missing a configure option or defines for mysql5? Thanks, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help regarding a simple query
-Original Message- From: VenuGopal Papasani [mailto:[EMAIL PROTECTED] Sent: Monday, March 13, 2006 11:48 To: Jeff Subject: Re: Help regarding a simple query Hi Jeff, This is venu again.Last mail i did not include a constraint that is what irritating me most.Actually if i got venu-kkk I should not get that venu-kkk. This was the query actually i want. Can you please give me teh query for that Regards, venu. Please post all responses to the mailing list, not directly to another person. the % is a wild card character Name -- Venu VENU XVENU yVeNu Venuzztest select * from X where Name like '%venu%' returns: Venu VENU XVENU yVeNu Venuzztest select * from X where Name like 'venu%' returns: Venu VENU Venuzztest select * from X where Name like '%venu' returns: Venu VENU XVENU yVeNu Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: users browser caching the screen
fbsd_user wrote: Now I know what I am going to talk about is not directly related to this mysql list, but I am in need of some concept ideas. To set the background. It’s a very common practice in the registration process of a new user to verify the users email address is valid by sending a email to the entered email address with a link in it to a screen that updates the users emailed verified flag in his table record. I have such a process. Once a week I review my apache activity log and I noticed a lot of log records for the file that process the link to update the users email verified flag, (over 1500 from same ip address). To me this looked like an attack to break into my web application. Research and testing indicates that the screen is Cached by the users browser and he is changing the passed link info repeatedly in effort to break in. This screen is the only one that does not have session security control because it’s launched from the verify email I sent him. Now my registration sign up screen has a Captcha Security Code Random-Noisy-Image and part of that is a string of headers to the browser to stop caching. They look like this. // send several headers to make sure the image is not cached // taken directly from the PHP Manual // Date in the past header(Expires: Mon, 26 Jul 1997 05:00:00 GMT); // always modified header(Last-Modified: . gmdate(D, d M Y H:i:s) . GMT); // HTTP/1.1 header(Cache-Control: no-store, no-cache, must-revalidate); header(Cache-Control: post-check=0, pre-check=0, false); // HTTP/1.0 header(Pragma: no-cache); Now my desire is to somehow have the screen that is the target of the email link to issue these headers before displaying its html code so the user can not any longer run a script against this screen trying to break in. Is this possible and if so how. Is this kind of attack happening to anyone else? Thanks for any help you can offer. You have no way of knowing it the user is running a browser at all. He could have grabbed the page once, and run a script that pretends to be a browser. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
socket error
I can connect to mysql with mysql -u dbname -p When I start MySQL Administrator v 1.1.6 it says Could not connect to host 'localhost'. MySQL Error Nr. 2002 Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111) ...then I Click on Details and enter, '/var/lib/mysql/mysql.sock' And can connect, no problem. Doesn't the Administrator look to /etc/my.conf for the socket location info? This is my my.conf file [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/lib/mysql/mysqlerrorlog old_passwords=1 [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Thanks Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: E/R Tool
http://www.thekompany.com/products/dataarchitect/ There are free evaluation copies to download, and it's not that dear to buy a copy, very good value actually. I'm not sure if there is a free Linux version that theKompany have released as well. Keith In theory, theory and practice are the same; In practice they are not. On Mon, 13 Mar 2006, Vinay wrote: To: mysql@lists.mysql.com From: Vinay [EMAIL PROTECTED] Subject: E/R Tool Hello , Is there a mysql or any other tool that generates a E/R diagram using an existing mysql database. Thank You Vinay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: socket error
Jim Douglas [EMAIL PROTECTED] wrote on 03/13/2006 12:24:56 PM: I can connect to mysql with mysql -u dbname -p When I start MySQL Administrator v 1.1.6 it says Could not connect to host 'localhost'. MySQL Error Nr. 2002 Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111) ...then I Click on Details and enter, '/var/lib/mysql/mysql.sock' And can connect, no problem. Doesn't the Administrator look to /etc/my.conf for the socket location info? This is my my.conf file [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/lib/mysql/mysqlerrorlog old_passwords=1 [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Thanks Jim It may look there but I don't see where you are declaring any settings it wants to pay attention to. You need a new [mysql] section with a socket= setting in it in order for your clients (like MySQL Administrator) to know where your socket is hiding. [mysql] socket=/var/lib/mysql/mysql.sock [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/lib/mysql/mysqlerrorlog old_passwords=1 [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid More details are here: http://dev.mysql.com/doc/refman/4.1/en/option-files.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
DROP DATABASE doesn't actually drop the database?
I am scripting out the creation of a database so I can make changes and then run the script to generate a clean copy (by running it in MySQL Query Browser). The script DROPs all the tables, then CREATEs them again along with all the indices and whatnot. However, if I run the script after having run it once (if I close the Query Browser and then open it again and reload the script later), I get error 1061 Duplicate key name on all of the indices. So I figured I missed something, and I'll just DROP the whole database and then run the script. I add a DROP DATABASE databasename; and a CREATE DATABASE databasename; at the start of my script and try to run it again. Now I get errors 1008 Can't drop database 'databasename'; database doesn't exist and 1007 Can't create database 'databasename'; database exists, followed by the index errors above. If I drop the database from MySQL Administrator (or the command-line client), and then run the script again I get the same errors. The database and all of the scripted objects are created (properly as far as I can tell), but why would I get these errors? I am using the latest version of MySQL (5.0.19) with InnoDB as the storage engine on Windows 2000 Professional SP4, but I have the same problem with 5.0.17. If there is any other information you need I'd be happy to supply it.
Re: Problems with timestamp field after upgrading MySQL Server.
Sure is... SELECT DATE_FORMAT(dateField,'%Y%m%d') as dateField Take a look at: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html -Josh --- Yesmin Patwary [EMAIL PROTECTED] wrote: Dear All, First of all, I would like to thank to Josh and Peter Brawley for their kind help on previous issue. Here is another Issue: There are many tables that have timestamp field with 8 char (mmdd). I use this format all over our website and to create various reports. Below is static php code that I used numerous places: ? $year=substr($date, 0, 4); $month=substr($date, 4, 2); $day=substr($date, 6, 2); ? All timestamp dependent reports and web pages had problems, once I upgrade to MySQL 4.1.12. At the end I had to downgrade to 3.23 to bring web site operation to normal. Is there anyway to preserve old timestamp format after upgrading? MySQL - 3.23 +++-- | Field | Type | Default (mmdd) +++-- |log_date|timestamp(8)| MySQL - 4.1.12 ++---+ | Field | Type| Default ++---+ |log_date| timestamp |-00-00 00:00:00 Josh [EMAIL PROTECTED] wrote: You could rotate the output... basically get 1 row with 11 columns (CA01_count,CA02_count,...,CA12_count) (leaving out CA10) joining all 12 tables together... Or... perhaps we can help with the timestamp issues you are having and get you upgraded to later version of mysql that supports nested SELECT statements. --- Yesmin Patwary wrote: Dear All, I had some issues in past with timestamp fields as a result I am unable to upgrade to mysql 4.1 version. I am sure below the query recommended by Josh works with 4.1 or above. Would it be possible to rewrite this query for 3.23 version? Again, thank you Josh and all others for your kind help and comments. Josh wrote: Here's one method: SELECT cl1.list_name, count(*) as count FROM customerList cl1 WHERE cl1.id IN (SELECT cl2.id FROM customerList cl2 WHERE cl2.list_name='CA10') and cl1.list_name != 'CA10' GROUP BY cl1.list_name --- Yesmin Patwary wrote: Good morning all, We have 12 customer lists: CA01, CA02, .,CA12. Table: customerList +---+--+ | list_name | id | +---+--+ | CA10 | 20BE | | CA07 | 20BE | | CA11 | 20BE | | CA03 | 20BE | | CA10 | NQCR | | CA04 | NQCR | | CA02 | MVYK | | CA10 | 0BEC | | AND SO ON. | +---+--+ Each list has 25 to 350 customers. Same customer_id may exist in multiple lists. We need to compare CA10 list customer_ids with other 11 lists to find matching id count by list_name. The query output should be something similar below: +--+---+ | list_name |count | +--+---+ | CA05 | 60 | | CA07 | 42 | | CA01 | 35 | | CA03 | 28 | | CA09 | 15 | | AND SO ON | +---+--+ Can this be done with a SELECT statement without using perl or php? Thanks in advance for any help. - Yahoo! Mail Bring photos to life! New PhotoMail makes sharing a breeze. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Yahoo! Mail Use Photomail to share photos without annoying attachments. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with timestamp field after upgrading MySQL Server.
Alternatively, you might be able to re-render times and dates in their condensed format by auto-converting them to a numeric value. Try adding zero to your date columns in your select clauses. Once condensed, your substring code should begin working as before. SELECT datecol +0 as datecol FROM ... Shawn Green Database Administrator Unimin Corporation - Spruce Pine Josh [EMAIL PROTECTED] wrote on 03/13/2006 12:42:32 PM: Sure is... SELECT DATE_FORMAT(dateField,'%Y%m%d') as dateField Take a look at: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html -Josh --- Yesmin Patwary [EMAIL PROTECTED] wrote: Dear All, First of all, I would like to thank to Josh and Peter Brawley for their kind help on previous issue. Here is another Issue: There are many tables that have timestamp field with 8 char (mmdd). I use this format all over our website and to create various reports. Below is static php code that I used numerous places: ? $year=substr($date, 0, 4); $month=substr($date, 4, 2); $day=substr($date, 6, 2); ? All timestamp dependent reports and web pages had problems, once I upgrade to MySQL – 4.1.12. At the end I had to downgrade to 3.23 to bring web site operation to normal. Is there anyway to preserve old timestamp format after upgrading? MySQL - 3.23 +++-- | Field | Type | Default (mmdd) +++-- |log_date|timestamp(8)| MySQL - 4.1.12 ++---+ | Field | Type| Default ++---+ |log_date| timestamp |-00-00 00:00:00 Josh [EMAIL PROTECTED] wrote: You could rotate the output... basically get 1 row with 11 columns (CA01_count,CA02_count,...,CA12_count) (leaving out CA10) joining all 12 tables together... Or... perhaps we can help with the timestamp issues you are having and get you upgraded to later version of mysql that supports nested SELECT statements. --- Yesmin Patwary wrote: Dear All, I had some issues in past with timestamp fields as a result I am unable to upgrade to mysql 4.1 version. I am sure below the query recommended by Josh works with 4.1 or above. Would it be possible to rewrite this query for 3.23 version? Again, thank you Josh and all others for your kind help and comments. Josh wrote: Here's one method: SELECT cl1.list_name, count(*) as count FROM customerList cl1 WHERE cl1.id IN (SELECT cl2.id FROM customerList cl2 WHERE cl2.list_name='CA10') and cl1.list_name != 'CA10' GROUP BY cl1.list_name --- Yesmin Patwary wrote: Good morning all, We have 12 customer lists: CA01, CA02, ….,CA12. Table: customerList +---+--+ | list_name | id | +---+--+ | CA10 | 20BE | | CA07 | 20BE | | CA11 | 20BE | | CA03 | 20BE | | CA10 | NQCR | | CA04 | NQCR | | CA02 | MVYK | | CA10 | 0BEC | | …AND SO ON. | +---+--+ Each list has 25 to 350 customers. Same customer_id may exist in multiple lists. We need to compare CA10 list customer_id’s with other 11 lists to find matching id count by list_name. The query output should be something similar below: +--+---+ | list_name |count | +--+---+ | CA05 | 60 | | CA07 | 42 | | CA01 | 35 | | CA03 | 28 | | CA09 | 15 | | …AND SO ON… | +---+--+ Can this be done with a SELECT statement without using perl or php? Thanks in advance for any help. - Yahoo! Mail Bring photos to life! New PhotoMail makes sharing a breeze. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Yahoo! Mail Use Photomail to share photos without annoying attachments. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
timestamp
In my mysql 4.4 table definition the default attributes are (ON UPDATE CURRENT_TIMESTAMP). Reading the manual my understanding is this is saying that the auto timestamp update feature is active. The manual does not say what the trigger is to make the timestamp in the row to be bumped to the current timestamp. I have noticed that the timestamp field is only bumped when I update a field in the row. I would like it to be auto bumped every time the row is selected/read. Is there a way to do this without creating a timestamp from date in my php code and then updating the row instead of reading the row? The final goal is to auto bump the timestamp every time the user logs on. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: timestamp
fbsd_user [EMAIL PROTECTED] wrote on 03/13/2006 01:10:17 PM: In my mysql 4.4 table definition the default attributes are (ON UPDATE CURRENT_TIMESTAMP). Reading the manual my understanding is this is saying that the auto timestamp update feature is active. The manual does not say what the trigger is to make the timestamp in the row to be bumped to the current timestamp. I have noticed that the timestamp field is only bumped when I update a field in the row. I would like it to be auto bumped every time the row is selected/read. Is there a way to do this without creating a timestamp from date in my php code and then updating the row instead of reading the row? The final goal is to auto bump the timestamp every time the user logs on. It sounds like there is a logon process that the user must perform. What's the issue you have about updating your timestamp from within that process? That's where it sounds most logical to me to add the code to bump your timestamp value. From a design point of view, it would seriously slow down the entire server if it had to check for something to do on EVERY read from ANY table. In order to do what you want the database to do, that facility would need to exist so that the engine could bump the timestamp automatically. It is generally much better to NOT write SELECT statements to a log or to do anything else that would slow them down. Checking every row that ever got selected to see if any column in it is an auto-update-on-select-timestamp column would do that in a major way. It also breaks all kinds of SQL rules to make a SELECT clause into something that modifies data. That would lead to all sorts of data chaos and I want no part of it. I am sorry, but if you want the timestamp changed you are going to have to initiate that change by an UPDATE statement. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: E/R Tool
Excellent DB design tool Peter. Thanks for posting the information. http://www.fabforce.net/dbdesigner4/ I have bookmarked that, and will be checking that out soon! Regards Keith In theory, theory and practice are the same; In practice they are not. On Mon, 13 Mar 2006, Peter M. Groen wrote: To: mysql@lists.mysql.com From: Peter M. Groen [EMAIL PROTECTED] Subject: Re: E/R Tool On Monday 13 March 2006 17:37, Vinay wrote: Hello , Is there a mysql or any other tool that generates a E/R diagram using an existing mysql database. Thank You Vinay Try fabforce for DbDesigner4MySQL. Very good tool. -- Peter M. Groen Open Systems Development Klipperwerf 12 2317 DZ Leiden T : +31-(0)71-5216317 M : +31-(0)6-29563390 E : [EMAIL PROTECTED] Skype : peter_m_groen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: E/R Tool
On Monday 13 March 2006 17:37, Vinay wrote: Hello , Is there a mysql or any other tool that generates a E/R diagram using an existing mysql database. Thank You Vinay Try fabforce for DbDesigner4MySQL. Very good tool. -- Peter M. Groen Open Systems Development Klipperwerf 12 2317 DZ Leiden T : +31-(0)71-5216317 M : +31-(0)6-29563390 E : [EMAIL PROTECTED] Skype : peter_m_groen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql++1.7.1 vc++6 compile errors
Hi I am using visual studio C++ 6, with SDK, my mysql database is 4.1.18, i downloaded mysql++ 1.7.1 as this is the compatiable version for vc++6. the example program compiles and runs. when i go to make my own project i use the wizard and create a basic dialog MFC app. at the top of the main cpp file i add #include mysql++ and then in the program call Connection con(login,localhost,root,abc); in the mysql++ download there is many lib and include folders, i have copyied the contents of these in the the vc++6 include and lib folders. when come to compile i get many errors as seen below. please help c:\program files\microsoft visual studio\vc98\include\type_info1.hh(39) : warning C4800: 'int' : forcing value to bool 'true' or 'false' (performance warning) c:\program files\microsoft visual studio\vc98\include\type_info1.hh(159) : warning C4800: 'unsigned int' : forcing value to bool 'true' or 'false' (performance warning) c:\program files\microsoft visual studio\vc98\include\type_info1.hh(172) : warning C4800: 'int' : forcing value to bool 'true' or 'false' (performance warning) c:\program files\microsoft visual studio\vc98\include\type_info1.hh(176) : warning C4800: 'int' : forcing value to bool 'true' or 'false' (performance warning) c:\program files\microsoft visual studio\vc98\include\type_info1.hh(180) : warning C4800: 'int' : forcing value to bool 'true' or 'false' (performance warning) c:\program files\microsoft visual studio\vc98\include\type_info1.hh(184) : warning C4800: 'int' : forcing value to bool 'true' or 'false' (performance warning) c:\program files\microsoft visual studio\vc98\include\convert1.hh(40) : warning C4273: 'strtol' : inconsistent dll linkage. dllexport assumed. c:\program files\microsoft visual studio\vc98\include\convert1.hh(41) : warning C4273: 'strtoul' : inconsistent dll linkage. dllexport assumed. c:\program files\microsoft visual studio\vc98\include\sql_query1.hh(37) : warning C4800: 'class SQLQuery *' : forcing value to bool 'true' or 'false' (performance warning) c:\program files\microsoft visual studio\vc98\include\sql_query1.hh(135) : warning C4355: 'this' : used in base member initializer list c:\program files\microsoft visual studio\vc98\include\compare1.hh(48) : warning C4800: 'int' : forcing value to bool 'true' or 'false' (performance warning) c:\program files\microsoft visual studio\vc98\include\result1.hh(33) : warning C4355: 'this' : used in base member initializer list c:\program files\microsoft visual studio\vc98\include\result1.hh(42) : warning C4800: 'char' : forcing value to bool 'true' or 'false' (performance warning) c:\program files\microsoft visual studio\vc98\include\utility(25) : warning C4786: 'std::_Treestd::basic_stringchar,std::char_traitschar,std::allocatorchar ,std::pairstd::basic_stringchar,std::char_traitschar,std::allocatorchar const ,int,std::mapstd::basic_stringchar,std::char_traitschar,std::allocatorchar ,int,std::lessstd::basic_stringchar,std::char_traitschar,std::allocatorchar ,std::allocatorint ::_Kfn,std::lessstd::basic_stringchar,std::char_traitsch ar,std::allocatorchar ,std::allocatorint ::iterator' : identifier was truncated to '255' characters in the debug information c:\program files\microsoft visual studio\vc98\include\map(93) : see reference to class template instantiation 'std::pairstd::_Treestd::basic_stringchar,std::char_traitschar,std::allocatorchar ,std::pairstd::basic_stringchar,std::ch ar_traitschar,std::allocatorchar const ,int,std::mapstd::basic_stringchar,std::char_traitschar,std::allocatorchar ,int,std::lessstd::basic_stringchar,std::char_traitschar,std::allocatorchar ,std::allocatorint ::_Kfn,std::le ssstd::basic_stringchar,std::char_traitschar,std::allocatorchar ,std::allocatorint ::iterator,bool' being compiled c:\program files\microsoft visual studio\vc98\include\map(93) : while compiling class-template member function 'int __thiscall std::mapstd::basic_stringchar,std::char_traitschar,std::allocatorchar ,int,std::lessstd::basic_stringcha r,std::char_traitschar,std::allocatorchar ,std::allocatorint ::operator [](const std::basic_stringchar,std::char_traitschar,std::allocatorchar )' Generating Code... Linking... mysqlDlg.obj : error LNK2001: unresolved external symbol public: __thiscall MysqlConnection::~MysqlConnection(void) (??1MysqlConnection@@[EMAIL PROTECTED]) mysqlDlg.obj : error LNK2001: unresolved external symbol public: __thiscall MysqlConnection::MysqlConnection(char const *,char const *,char const *,char const *,bool) (??0MysqlConnection@@[EMAIL PROTECTED]@Z) Debug/mysql.exe : fatal error LNK1120: 2 unresolved externals Error executing link.exe. mysql.exe - 3 error(s), 14 warning(s) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql++1.7.1 vc++6 compile errors
the example program compiles and runs. when i go to make my own project i use the wizard and create a basic dialog MFC app. at the top of the main cpp file i add #include mysql++ and then in the program call Connection con(login,localhost,root,abc); in the mysql++ download there is many lib and include folders, i have copyied the contents of these in the the vc++6 include and lib folders. when come to compile i get many errors as seen below. please help ...snip... Generating Code... Linking... mysqlDlg.obj : error LNK2001: unresolved external symbol public: __thiscall MysqlConnection::~MysqlConnection(void) (??1MysqlConnection@@[EMAIL PROTECTED]) mysqlDlg.obj : error LNK2001: unresolved external symbol public: __thiscall MysqlConnection::MysqlConnection(char const *,char const *,char const *,char const *,bool) (??0MysqlConnection@@[EMAIL PROTECTED]@Z) Debug/mysql.exe : fatal error LNK1120: 2 unresolved externals Error executing link.exe. mysql.exe - 3 error(s), 14 warning(s) Have you included mysql++.lib in your list of libraries to link to within your project? (Project - Settings - 'All Configurations' from 'Settings for:' combo, Link tab, 'Input' from 'Category' combo) -- Jason Teagle [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: E/R Tool
[EMAIL PROTECTED] wrote: Excellent DB design tool Peter. Thanks for posting the information. http://www.fabforce.net/dbdesigner4/ It has become MySQL Workbench (http://forums.mysql.com/read.php?10,73820,73820#msg-73820), but it isn't production-ready yet. PB -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.2.1/279 - Release Date: 3/10/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql++1.7.1 vc++6 compile errors
thanks for the reply Jason, i have now added mysql++.lib to the linker and also copyied the libmysql.dll to the system folder. the program complies now with a few performance warnings but no errors. there is one link error LINK : warning LNK4098: defaultlib LIBC conflicts with use of other libs; use /NODEFAULTLIB:library In the settings if i do as it says above, i get over 200 errors!! so i revert back. i have the simple code below but when the program is ran, the first message box comes up and then the program crashes MessageBox(test1); Connection con(login, localhost, root, abc); MessageBox(test2); the crash error is abnormal program termination if i use the example console code that comes with mysql++ 1.7.1 i get the following error at run time. Error: Client does not support authentication protocol requested by server; consider upgrading MySQL client as i already said, i am running mysql++1.7.1 and mysql 4.1.18 and vc++6 please help -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql++1.7.1 vc++6 compile errors
Keith Lee wrote: there is one link error LINK : warning LNK4098: defaultlib LIBC conflicts with use of other libs; use /NODEFAULTLIB:library In the settings if i do as it says above, i get over 200 errors!! so i revert back. Visual C++ is very picky about the way programs are built when linked against third-party libraries. Those libraries have to be built using the exact same build settings: whether to use multithreading or not, whether to use the dynamic C runtime library or the static one, whether to use Unicode or not, etc... If you get one of these wrong, you can get all kinds of havoc. Study the project settings for MySQL++ and either change them to match your program's build settings, or vice versa. the crash error is abnormal program termination Wrap the Connection object creation in a try block. MySQL++ may be throwing an exception. An uncaught exception kills the program. P.S. You do realize that you can get the entry level version of Visual Studio 2005 for free right now, don't you? Then you will be able to use the current version of MySQL++, so we can offer you better support. We're not picking on Visual C++ here. We don't support eight year old versions of GCC, either. -- Warren Young Maintainer of MySQL++ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DROP DATABASE doesn't actually drop the database?
Thanks for your comments! I ran my entire script (DROP DATABASE and all) with the command-line client, and got no errors. Perhaps there is something with the Query Browser that is causing this problem. I added the IF EXISTS and IF NOT EXISTS in appropriate places (although I can see the database there beforehand, and see it disappear in MySQL Administrator, it shouldn't be necessary in this case because I know what is there). Of course, I still get the duplicate index errors in Query Browser, but I suspect it is that program and not the database server that is causing this. The reason I am scripting this manually is because I am trying to learn and practice MySQL (I come from a MS SQL Server background), and this database is very small (3 or 4 tables, a view and 2 stored procedures so far). It is not difficult to make a change and re-run the script. When I get anything good (and of decent size) going I'll start using the other options; right now I'm just playing with a throwaway database to get a feel for the syntax. On 3/13/06, Andreas Krüger [EMAIL PROTECTED] wrote: Stephen, the behavior of MySQL server sounds bizarre, as to what information you give us. For the DROP DATABASE and DROP TABLE statements, there is an option that prevents an error, if database or table don' t exist: DROP DATABASE* IF EXISTS *db_name DROP TABLE* IF EXISTS *tbl_name You might also want to have a look at the 5.0 manual: http://dev.mysql.com/doc/refman/5.0/en/drop-database.html http://dev.mysql.com/doc/refman/5.0/en/drop-table.html I am further astonished that you do script files manually for re-creating databases and tables.* Why don' t you use mysqldump for dumping a database and its tables mysql for loading the dumped information?* You can dump a database by mysqldump db_name db_name.sql There are many handy options to mysqldump as --add-drop-table and -all that you should consider to use You then can manually update the dump files in a text editor. mysql db_name db_name.sql recreates the dumped data with all tables. Andy Stephen Cook wrote: I am scripting out the creation of a database so I can make changes and then run the script to generate a clean copy (by running it in MySQL Query Browser). The script DROPs all the tables, then CREATEs them again along with all the indices and whatnot. However, if I run the script after having run it once (if I close the Query Browser and then open it again and reload the script later), I get error 1061 Duplicate key name on all of the indices. So I figured I missed something, and I'll just DROP the whole database and then run the script. I add a DROP DATABASE databasename; and a CREATE DATABASE databasename; at the start of my script and try to run it again. Now I get errors 1008 Can't drop database 'databasename'; database doesn't exist and 1007 Can't create database 'databasename'; database exists, followed by the index errors above. If I drop the database from MySQL Administrator (or the command-line client), and then run the script again I get the same errors. The database and all of the scripted objects are created (properly as far as I can tell), but why would I get these errors? I am using the latest version of MySQL (5.0.19) with InnoDB as the storage engine on Windows 2000 Professional SP4, but I have the same problem with 5.0.17. If there is any other information you need I'd be happy to supply it.
Re: mysql5 options file location
On Mon, 13 Mar 2006 16:52:03 + (GMT) [EMAIL PROTECTED] wrote: Hi Alex. It seems that mysqld and all the client programs insist on reading /etc/my.cnf first. To overide this behaviour for a particular instance of mysqld you need to pass the --defaults-file option as the FIRST parameter to mysqld_safe or mysqld if you are not using mysqld_safe. Yes, I understand everything that you said Keith and have used the options file logic to my advantage as well. Thanks for the full description. What is not working for me is that the server-specific file does not appear to be built into all of the objects like it was in 4.1. For example, my.cnf is in basedir. my.cnf has a [mysqld] group that defines many options like various logging selections and tuning. I do not want those options in a global file since I am not supposed to write to /etc/. I am using a directory structure similar to /usr/local/mysql5, but it is actually /opt/csw/mysql5. The server-specific options are read on mysqld_safe at startup, as evidenced by `mysqladmin variables`. Without a /etc/my.cnf file, 'my_print_defaults mysqld' prints nothing. With a /etc/my.cnf file, 'my_print_defaults mysqld' prints only the options in /etc/my.cnf. This is very different from 4.1 and causing me a lot of headaches and is probably my biggest issue with 5.0. I could go on about the utilities that do not work without a global options file. Another clue about this change from 4.1 is the `mysqld --verbose --help` output not listing a server-specific options file on 5.0 I just wondered if I did something wrong or if I can just not use some utilities in 5.0 The situation with 5.0 and the options file is really much bigger than what I have described. I have big problems with the 5.0 location of the server-specific options file moving from datadir to basedir. But that is another discussion. Thanks, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
update using 'set' keyword
Trying to bump the count_of_logons by 1 using this update. Phpmyadmin shows the count staying at zero. I think that this SET count_of_logons = 'count_of_logons + 1' is not coded correctly, but I get no errors so can not tell. Anybody have any ideas? The table def has count_of_logons INT, $sql = UPDATE members SET count_of_logons = 'count_of_logons + 1' WHERE logon_id = '$logonid' AND logon_pw= '$logonpw' AND email_verified = 'Y'; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Importing CSV file into MySQL DB - Newbie Question
Hi Derek, You never gave us a SHOW CREATE TABLE simple1, which would have helped. To replicate your problem, I did the following, on a linux box (it looks like you're using Windows), using mysql 5.0.18-standard-log: CREATE DATABASE cars; use cars; CREATE TABLE `simple1` ( `one` char(10) default NULL, `two` char(10) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; and then made a text file like your simple1.csv and ran the following: mysqlimport --lines-terminated-by=\n --fields-terminated-by=, --local --user=root --password=rootpass cars simple1.csv and got: cars.simple1: Records: 1 Deleted: 0 Skipped: 0 Warnings: 1 I'd guess I got a warning and you didn't because of the version of MySQL. when I checked out the table, indeed I found that I had only one row. So I tried again, figuring that the binary knew what the end of the line was: mysqlimport --fields-terminated-by=, --local --user=root --password=rootpass cars simple1.csv cars.simple1: Records: 3 Deleted: 0 Skipped: 0 Warnings: 1 aha! 3 records this time! select * from simple1; +--+-+ | one | two | +--+-+ | test1 | test2 | | test11 | test3 | | | NULL| +--+-+ 3 rows in set (0.00 sec) I can guess that I got a warning because there was no comma-separated list on the 3rd line, so it put the first value (blank) into the first field of the 3rd record, but had nothing to put in the 2nd value, so it put NULL. You don't need double quotes in the file. However, if you are importing someone else's file or a previous export, you can put a --fields-enclosed-by='' (that is, single-quote double-quote single-quote) tag to tell mysqlimport that it shouldn't look at the double quotes. hope this helps! Sheeri On 3/5/06, Derek Doerr [EMAIL PROTECTED] wrote: I have a CSV file that I want to import into a MySQL DB table. The file contains 15 fields. The able to import into will contain those same 15 fields, plus an auto-generated Primary Key fields. Since this is the first time I'm working with mysqlimport, I created a small test table to start with - simple1, containing two varchar fields - field1 and field2. I'm trying to import a small test file into simple1, to get the hang of using mysqlimport. The test file contains 2 records and 3 lines - the 3rd line is blank: test1,test2 test11,test3 I run the import as follows: C:\Program Files\xampp\mysql\binmysqlimport.exe --lines-terminated-by=\r --fields-terminated-by=, --local --user=root cars c:\dev\test\simple1.csv The import report shows: Records: 1 Deleted: 0 Skipped: 0 Warnings: 0 The data that ends up in the DB, however, only contains 1 record: \test1\,\test2\ \test11\ (1) how do I get mysqlimport to import both records, properly parsing the fields - two fields per record? (2) do I need to wrap the imported records in double-quotes? Why do the double-quotes show up in the MySQL DB table? -- 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: E/R Tool
OK TY Peter. I have downloaded both DBDesigner and MySQL Workbench. Looking forward to workbench reaching GA status. Regards Keith In theory, theory and practice are the same; In practice they are not. On Mon, 13 Mar 2006, Peter Brawley wrote: To: [EMAIL PROTECTED] From: Peter Brawley [EMAIL PROTECTED] Subject: Re: E/R Tool [EMAIL PROTECTED] wrote: Excellent DB design tool Peter. Thanks for posting the information. http://www.fabforce.net/dbdesigner4/ It has become MySQL Workbench (http://forums.mysql.com/read.php?10,73820,73820#msg-73820), but it isn't production-ready yet. PB -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.2.1/279 - Release Date: 3/10/2006 -- 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: Problem mysql 4.1 to mysql5 -
Which binary did you use, and where did you download it from? what is the result of uname -a ? are they both 64 bit machines? 32-bit machines? where is libstdc++ on both machines? It's possible one machine has a library linked to another location, or they have them in different locations. Specifically check /lib and /usr/lib vs. /lib64 and /usr/lib64. Hope that helps -Sheeri On 3/5/06, bobgoodwin [EMAIL PROTECTED] wrote: I have two FC4 computers I believe to be set up identically, I installed mysql5 in both, the first one made the change without a hitch but the second refuses to install with the error message: ./bin/mysqld: error while loading shared libraries: libstdc++.so.5: cannot open shared object file: No such file or directory Installation of system tables failed! Libstdc++ appears to be present? Can anyone offer a helpful suggestion? BobG ./configure NOTE: This is a MySQL binary distribution. It's ready to run, you don't need to configure it! To help you a bit, I am now going to create the needed MySQL databases and start the MySQL server for you. If you run into any trouble, please consult the MySQL manual, that you can find in the Docs directory. Installing all prepared tables ./bin/mysqld: error while loading shared libraries: libstdc++.so.5: cannot open shared object file: No such file or directory Installation of system tables failed! Examine the logs in ./data for more information. You can also try to start the mysqld daemon with: ./bin/mysqld --skip-grant You can use the command line tool ./bin/mysql to connect to the mysql database and look at the grant tables: shell ./bin/mysql -u root mysql -- 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: update using 'set' keyword
fbsd_user wrote: Trying to bump the count_of_logons by 1 using this update. Phpmyadmin shows the count staying at zero. I think that this SET count_of_logons = 'count_of_logons + 1' is not coded correctly, but I get no errors so can not tell. Anybody have any ideas? The table def has count_of_logons INT, $sql = UPDATE members SET count_of_logons = 'count_of_logons + 1' WHERE logon_id = '$logonid' AND logon_pw= '$logonpw' AND email_verified = 'Y'; Why are you quoting 'count_of_logons + 1'? In any case, that's the problem. 'count_of_logons + 1' is a string. You are assigning it to an INT, so it must be converted to a number. Strings which do not start with anything numeric convert to 0. For example: mysql SELECT 'count_of_logons + 1' + 0; +---+ | 'count_of_logons + 1' + 0 | +---+ | 0 | +---+ 1 row in set (0.00 sec) Leave out the quotes to get the expected result: $sql = UPDATE members SET count_of_logons = count_of_logons + 1 WHERE logon_id = '$logonid' AND logon_pw= '$logonpw' AND email_verified = 'Y'; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange problem: Increasing Memory / HEAP Table
What does SHOW CREATE TABLE give you? -Sheeri On 3/11/06, Holger Sunke [EMAIL PROTECTED] wrote: Hallo, is there a way of maintaining Memory tables or rebuilding indexes? I have a big memory table with about 300k rows, 12 Attributes, 7 BTREE-Indexes and a PRIMARY KEY (Hash index). Now i'm doing many many Updates (and a little number of inserts) on this table that also do effect indexed attributes. (With many updates I mean about 2000 per second...thats why I have chosen Memory storage engine) The strange thing is that my table grows above linear to the happening inserts, so average row size (=total memory used by table divided by number of rows) increases too. When I now copy this table to another database or another table name by CREATE TABLE new ... and then INSERT INTO new SELECT * FROM old memory used by new table can be about 50% (depends on number of actions done on the old table before) of memory used by old table although Structure and Data are equal. Could that be a bug? Table size especially of Memory tables should not increase by updates i think... greets! -- 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: mysql5 options file location
In your global /etc/my.cnf fle, you should be able to split that into sections for each client program that you want to set specific options for. Eg. # /etc/my.cnf # MySQL client program configuration file # last updated 2006-03-05 # mysqld server configuration file lives in # /usr/local/mysql-version/my.cnf #--- # mysql client program configuration options #--- [mysql] no-auto-rehash # needed for security - to stops multiple deletes/updates # without a where clause safe-updates # # The following options will be passed to all MySQL clients [client] socket = /var/lib/mysql/mysql.sock port = 7000 # [myisamchk] set-variable = key_buffer=20M set-variable = sort_buffer=20M set-variable = read_buffer=2M set-variable = write_buffer=2M # [mysqldump] quick set-variable = max_allowed_packet=16M # # available programs/scripts are: (from 4.0.21 version) #my_print_defaults mysqladmin #myisamchk mysqlbinlog #myisamlog mysqlbug - n/a #myisampack mysqlcheck #mysql mysqld #mysql_convert_table_format - .plmysqld_multi #mysql_find_rows mysqldump #mysql_fix_privilege_tables n/a mysqlhotcopy - .pl #mysql_install_dbmysqlimport #mysql_setpermission - .pl mysqlshow #mysql_zap mysqltest #mysqlaccess - .pl safe_mysqld # # end of mysql client program configurations # /etc/my.cnf Take a look at the man pages for the client programs, as well as mysqld_safe and mysqld, in /opt/csw/mysql5/man/man1/ Most of the options for client programs are listed there. Exactly what are the problems you are having with the server specific my.cnf file? AFAIK it should be in basedir, and not normally in datadir. The log files are OK to have in datadir - that's the default place SuSE Linux puts them. HTH Keith In theory, theory and practice are the same; In practice they are not. On Mon, 13 Mar 2006, Alex Moore wrote: To: mysql@lists.mysql.com From: Alex Moore [EMAIL PROTECTED] Subject: Re: mysql5 options file location On Mon, 13 Mar 2006 16:52:03 + (GMT) [EMAIL PROTECTED] wrote: Hi Alex. It seems that mysqld and all the client programs insist on reading /etc/my.cnf first. To overide this behaviour for a particular instance of mysqld you need to pass the --defaults-file option as the FIRST parameter to mysqld_safe or mysqld if you are not using mysqld_safe. Yes, I understand everything that you said Keith and have used the options file logic to my advantage as well. Thanks for the full description. What is not working for me is that the server-specific file does not appear to be built into all of the objects like it was in 4.1. For example, my.cnf is in basedir. my.cnf has a [mysqld] group that defines many options like various logging selections and tuning. I do not want those options in a global file since I am not supposed to write to /etc/. I am using a directory structure similar to /usr/local/mysql5, but it is actually /opt/csw/mysql5. The server-specific options are read on mysqld_safe at startup, as evidenced by `mysqladmin variables`. Without a /etc/my.cnf file, 'my_print_defaults mysqld' prints nothing. With a /etc/my.cnf file, 'my_print_defaults mysqld' prints only the options in /etc/my.cnf. This is very different from 4.1 and causing me a lot of headaches and is probably my biggest issue with 5.0. I could go on about the utilities that do not work without a global options file. Another clue about this change from 4.1 is the `mysqld --verbose --help` output not listing a server-specific options file on 5.0 I just wondered if I did something wrong or if I can just not use some utilities in 5.0 The situation with 5.0 and the options file is really much bigger than what I have described. I have big problems with the 5.0 location of the server-specific options file moving from datadir to basedir. But that is another discussion. Thanks, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump backup on filters
Replicate your databases to another server, and take your dumps from that, so you can lock your replication slave and take your dumps while your site is still running. As an added bonus, you get a server suitable for manual failover should anything happen to your master database. -Sheeri On 3/10/06, Rithish Saralaya [EMAIL PROTECTED] wrote: Your table is missing. Try this: mysqldump --single-transaction -u root clientdb table --where=FLD_CLIENT_ID=1 client1_dbbackup.sql Yes. The tables are missing. That's because I want the backup of all the tables in the db, and those tables that have the column FLD_CLIENT_ID, they should be filtered by the where clause. This is because, if we are to reproduce the system for a client, then all we need to do is to run this backup on a new db. So, it looks like I will have to backup the db in phases. 1. Backup client-specific tables mysqldump --single-transaction -u root clientdb table_M1 table_M table_MX --where=FLD_CLIENT_ID=1 client1_dbbackup.sql 2. Then append the other tables to the backup. mysqldump --single-transaction -u root clientdb table_N1 table_N2 table_NX client1_dbbackup.sql However, this means that I cannot take a backup when the system is up and running. Any other ideas, folks? Regards, Rithish. -- 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: mysql5 options file location
On Mon, 13 Mar 2006 23:05:30 + (GMT) [EMAIL PROTECTED] wrote: Exactly what are the problems you are having with the server specific my.cnf file? Sorry, I thought that I had described the problem. A quick example was 'my_print_defaults mysqld' returning only the options defined in the global file. None of the options from the server-specific my.cnf are returned. This is not the way 4.1, and probably earlier, works. Thanks, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback is not take effect on MySQL 5.0.18
Dear Sir, I could not find table of innoDB in mysql. mysql show tables; +--+ | Tables_in_mysql | +---+ | columns_priv | | db | | func | | host | | tables_priv | | user | +--+ I set innodb_table_locks=0 in my.cnf , but ROLLBACK is still not effect. Could you teach me more ? Thanks and best regards, - Original Message - From: Pooly [EMAIL PROTECTED] To: MySQL General mysql@lists.mysql.com Sent: Monday, March 13, 2006 5:13 PM Subject: Re: Rollback is not take effect on MySQL 5.0.18 2006/3/11, Truong Tan Son [EMAIL PROTECTED]: Dear Sir, On RedHat Enterprise 4, and MySQL 5.0.18, I did : mysql set autocommit=0; mysql savepoint abc; mysql insert something mysql rollback to save point abc; Query OK, 0 rows affected, 1 warning (0.00 sec) ^^ RollBack is NOT take effect. But on WindowsXP, it is GOOD. What is wrong ? Did you check if the table are innoDB ? -- Pooly Webzine Rock : 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: users browser caching the screen
I guess you are saying that trying to have my php script send the users browser html headers to stop caching is not really going to solve my problem. I did put a block rule in my firewall for the attackers ip address and that stopped the attach from recurring. But to make sure it don't happen again I am adding a Captcha Security Code Random-Noisy-Images to the screen. This uses a randomly generated graphic image to stymie auto-submission scripts. -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: Monday, March 13, 2006 12:13 PM To: [EMAIL PROTECTED] Cc: Mysql Subject: Re: users browser caching the screen fbsd_user wrote: Now I know what I am going to talk about is not directly related to this mysql list, but I am in need of some concept ideas. To set the background. It’s a very common practice in the registration process of a new user to verify the users email address is valid by sending a email to the entered email address with a link in it to a screen that updates the users emailed verified flag in his table record. I have such a process. Once a week I review my apache activity log and I noticed a lot of log records for the file that process the link to update the users email verified flag, (over 1500 from same ip address). To me this looked like an attack to break into my web application. Research and testing indicates that the screen is Cached by the users browser and he is changing the passed link info repeatedly in effort to break in. This screen is the only one that does not have session security control because it’s launched from the verify email I sent him. Now my registration sign up screen has a Captcha Security Code Random-Noisy-Image and part of that is a string of headers to the browser to stop caching. They look like this. // send several headers to make sure the image is not cached // taken directly from the PHP Manual // Date in the past header(Expires: Mon, 26 Jul 1997 05:00:00 GMT); // always modified header(Last-Modified: . gmdate(D, d M Y H:i:s) . GMT); // HTTP/1.1 header(Cache-Control: no-store, no-cache, must-revalidate); header(Cache-Control: post-check=0, pre-check=0, false); // HTTP/1.0 header(Pragma: no-cache); Now my desire is to somehow have the screen that is the target of the email link to issue these headers before displaying its html code so the user can not any longer run a script against this screen trying to break in. Is this possible and if so how. Is this kind of attack happening to anyone else? Thanks for any help you can offer. You have no way of knowing it the user is running a browser at all. He could have grabbed the page once, and run a script that pretends to be a browser. -- 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: Query Optimization Question
Robert DiFalco wrote: In a previous database engine I was using an IN was more optimal than a . So, for example: SELECT * FROM table WHERE table.type IN (1,2,3); Where the possible values of type are 0-3, was appreciably faster than: SELECT * FROM table WHERE table.type 0; I've been playing with the Query Browser and checking out the optimization documents and haven't been able to make a clear call on whether or not this is also the case with MySQL/InnoDB. Mladen Adamovic wrote: IN should be faster implemented with both hash tables and BTREE's so nowadays it should also be faster than as all MySQL implementation AFAIK use those well known data structures for indexes. [EMAIL PROTECTED] wrote: YES, YES, YES! This is definitely an optimization. When you say IN or =, you are asking for matching values. Matches can come from indexes. When you say or NOT IN, you are asking for everything BUT matches. In order to evaluate a negative, the database engine (and this is usually true regardless of database server) almost always performs a full table scan to test every row to make sure it is either or NOT IN. At the very best, they have to perform a full index scan which is still less efficient than ranged or values-based lookups. It's when you get into the situation where you are matching against dozens of IN-clause items that you may run into slowdowns again. Until you reach 2 or 3 dozen terms (depending on your hardware) you should be faster with an IN comparison than a or a NOT IN comparison. An optimization to search for BUT a term or two is to create a temporary table of all of your terms and delete the exact ones you want to exclude. Put an index on your temp table then JOIN that back into your query again (replacing the huge IN clause). The database will match index to index and things will get fast again. This technique can scale up to some really big queries. Always try to code for the affirmative tests. Your users will thank you. Implicit in Mladen and Shawn's answers, but never actually mentioned in the original post, is the presence of an index on the type column. This is probably obvious to all concerned, but I mention it for completeness: without an index on type, there is no difference between type IN (1,2,3) and type != 0. That is, the question is not whether IN is better than !=, but rather which will allow the optimizer to make good use of the index on type. I find mysql's optimizer is pretty good with well-written queries, as long as subqueries aren't involved, so my initial reaction was to expect no difference. After all, as the optimizer considers the WHERE conditions and the available indexes, it is certainly possible, at least theoretically, for it to notice that type IN (1,2,3) and type != 0 are identical conditions. That is, a clever optimizer could treat them identically. Shawn's and Mladen's answers gave me pause, however, and aroused my curiosity, so I decided to test: SELECT VERSION(); +---+ | VERSION() | +---+ | 4.1.15| +---+ SELECT cat, COUNT(*) FROM inits GROUP BY cat; +--+--+ | type | COUNT(*) | +--+--+ |0 |44224 | |1 | 1919 | |2 | 1931 | |3 | 1926 | +--+--+ mysql EXPLAIN SELECT * FROM inits WHERE cat IN (1,2,3) \G *** 1. row *** id: 1 select_type: SIMPLE table: inits type: range possible_keys: cat_idx key: cat_idx key_len: 5 ref: NULL rows: 8117 Extra: Using where mysql EXPLAIN SELECT * FROM inits WHERE cat != 0 \G *** 1. row *** id: 1 select_type: SIMPLE table: inits type: range possible_keys: cat_idx key: cat_idx key_len: 5 ref: NULL rows: 8120 Extra: Using where As you can see, the optimizer plans to use the index in both cases, examining 8117 rows in the IN case and 8120 rows in the != case, to get 5776 matching rows out of 50,000 (about 11.55%). On the other hand, it makes a difference how many rows will match. What is the distribution of values of type? If the number of matching rows is more than about 30% of the table, the optimizer won't use an available index in any case. For example, mysql EXPLAIN SELECT * FROM inits WHERE cat IN (0,1,2) \G *** 1. row *** id: 1 select_type: SIMPLE table: inits type: ALL possible_keys: cat_idx key: NULL key_len: NULL ref: NULL rows: 5 Extra: Using where mysql EXPLAIN SELECT * FROM inits WHERE cat !=3 \G *** 1. row *** id: 1 select_type: SIMPLE table:
Re: mysql5 options file location
I get the same results as you do using this: karsites:/usr/local/mysql-5.0.18/bin # ./my_print_defaults mysqld karsites:/usr/local/mysql-5.0.18/bin # ./my_print_defaults client --socket=/var/lib/mysql/mysql.sock --port= karsites:/usr/local/mysql-5.0.18/bin # It seems like the last parameter passed to my_print_defaults tells my_print_defaults to get that particular section from the/etc/my.cnf file and print it out. However, you can pass a parameter to my_print_defaults, to tell it which my.cnf file to check. So doing this: karsites:/usr/local/mysql-5.0.18/bin # ./my_print_defaults --defaults-file=/usr/local/mysql-5.0.18/my.cnf mysqld --basedir=/usr/local/mysql-5.0.18 --server-id=1 --skip-name-resolve --skip-locking --set-variable=key_buffer=16M --set-variable=max_allowed_packet=1M --set-variable=table_cache=64 --set-variable=sort_buffer=512K --set-variable=net_buffer_length=8K --set-variable=myisam_sort_buffer_size=8M --log=5-0-18.log --log-bin=laptop-bin --log-error=5-0-18.error-log --log-queries-not-using-indexes --log-slow-admin-statements --log-slow-queries=5-0-18.slow-log --log-warnings karsites:/usr/local/mysql-5.0.18/bin # Does actually return the correct my.cnf file details. You can also use the -c /usr/local... shorthand option to tell my_print_defaults which my.cnf to look at. 'my_print_defaults --help' will return all the available options you can use. This is actually a Linux ELF file, and not a shell script Alex. That's something I have also just learned, so I'm pleased you mentioned it, because I was having the same problem, until I checked the options available, using --help. Are there any other problems you have encountered using this type of my.cnf setup? Keith In theory, theory and practice are the same; In practice they are not. On Mon, 13 Mar 2006, Alex Moore wrote: To: mysql@lists.mysql.com From: Alex Moore [EMAIL PROTECTED] Subject: Re: mysql5 options file location On Mon, 13 Mar 2006 23:05:30 + (GMT) [EMAIL PROTECTED] wrote: Exactly what are the problems you are having with the server specific my.cnf file? Sorry, I thought that I had described the problem. A quick example was 'my_print_defaults mysqld' returning only the options defined in the global file. None of the options from the server-specific my.cnf are returned. This is not the way 4.1, and probably earlier, works. Thanks, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback is not take effect on MySQL 5.0.18
2006/3/14, Truong Tan Son [EMAIL PROTECTED]: Dear Sir, I could not find table of innoDB in mysql. Tables in the mysql are MyISAM and should stay that way. Odds are that there is a skip-innodb in your my.cnf on your RHE, and not you XP. what produces a show status ? mysql show tables; +--+ | Tables_in_mysql | +---+ | columns_priv | | db | | func | | host | | tables_priv | | user | +--+ I set innodb_table_locks=0 in my.cnf , but ROLLBACK is still not effect. Could you teach me more ? Thanks and best regards, - Original Message - From: Pooly [EMAIL PROTECTED] To: MySQL General mysql@lists.mysql.com Sent: Monday, March 13, 2006 5:13 PM Subject: Re: Rollback is not take effect on MySQL 5.0.18 2006/3/11, Truong Tan Son [EMAIL PROTECTED]: Dear Sir, On RedHat Enterprise 4, and MySQL 5.0.18, I did : mysql set autocommit=0; mysql savepoint abc; mysql insert something mysql rollback to save point abc; Query OK, 0 rows affected, 1 warning (0.00 sec) ^^ RollBack is NOT take effect. But on WindowsXP, it is GOOD. What is wrong ? Did you check if the table are innoDB ? -- Pooly Webzine Rock : 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] -- Pooly Webzine Rock : 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]
