RE: Get Affected Rows after Stored Procedure COMMIT
Fetch rows_affected after each INSERT/UPDATE. Tally them in @variables, if you like. The information is not (I think) available after COMMIT. -Original Message- From: Neil Tompkins [mailto:neil.tompk...@googlemail.com] Sent: Tuesday, July 02, 2013 4:30 AM To: [MySQL] Subject: Get Affected Rows after Stored Procedure COMMIT Hi, I have a number of INSERT and UPDATE statements in a MySQL Stored Procedure, that works in the form of START TRANSACTION followed by COMMIT. Also I am handling any EXCEPTION. However, after calling COMMIT, how can I get the number of Rows that were affected either INSERTED or UPDATTED ? Thanks, Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Full text search and sign as a part of the keyword
ft_min_word_len=3
stopwords! IDIOT! I was testing using stop words! :)
let me change my test org name.
thanks
FULLTEXT (at least the MyISAM version) has 3 gotchas:
ft_min_word_len=4, stopwords, and the 50% rule
-Original Message-
From: shawn green [mailto:shawn.l.gr...@oracle.com]
Sent: Tuesday, July 02, 2013 10:21 AM
To: mysql@lists.mysql.com
Subject: Re: Full text search and sign as a part of the keyword
Hello,
(my response is not top-posted)
On 7/2/2013 12:50 PM, l...@afan.net wrote:
Another correction: Searching for Com, the test org is NOT gonna be
listed but all others will.
Searching for Com no results at all.
Actually, looks like I'm wrong.
For testing purpose I made an org
CompMe
When search for Comp it's gonna
be shown on the list.
When search for Comp it's also gonna be shown.
But
Construction Company as well.
Then I changed the name of
the test org to ComMe.
Searching for Com, the test org is gonna be listed.
Though, Com no results at
all.
?!?
Hi to all,
I have this full text search query
SELECT name, org_id,
address_id
FROM organization
WHERE org_active='Y' AND MATCH(name) AGAINST('ABC*' IN
BOOLEAN
MODE)
and I'm not getting any results. And there IS a org ABC, Inc.
My assumption is the ampersand sign as a part of the keyword.
Any idea?
Read this:
http://dev.mysql.com/doc/refman/5.5/en/server-system-
variables.html#sysvar_ft_boolean_syntax
Then search on Comp Me.
Let us know your results.
--
Shawn Green
MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware
and
Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
database perfomance worries
We are on a quest to improve the overall performance of our database. It's
generally
working pretty well, but we periodically get big slowdowns for no apparent
reason. A
prime example today - in the command line interface to the DB, I tried to
update one
record, and got:
mysql update agent set number_of_emails = 5 where acnt = 'AR287416';
Query OK, 1 row affected (36.35 sec)
Rows matched: 1 Changed: 1 Warnings: 0
36 seconds to update one table? The primary key is `acnt`. If I run the same
(basic)
command again a few seconds later, I get:
mysql update agent set number_of_emails = 15 where acnt = 'AR287416';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Why would we be getting such huge variations? We're running Solaris 10 on i386,
with
4 processors and 16GB of memory, MySQL 5.1.46-log. We are working out a plan to
upgrade
to MySQL 5.6, but I certainly don't want to depend on that upgrade to solve all
performance
problems.
CREATE TABLE `agent`
(
`acnt` varchar(20) NOT NULL,
`passwd` varchar(20) NOT NULL,
`package` char(2) DEFAULT NULL,
`data_template` varchar(20) DEFAULT 'NULL',
`default_search_type` enum('1','2','3') NOT NULL DEFAULT '1',
`status` enum('A','T','P','C','D','X','S') NOT NULL
`flags` varchar(10) DEFAULT NULL COMMENT 'pipe-separated flags',
`aliases` varchar(4000) NOT NULL DEFAULT '',
`offices` varchar(4000) NOT NULL DEFAULT '',
`license_no` varchar(40) NOT NULL DEFAULT '',
`agent_code` varchar(20) DEFAULT NULL,
`office_code` varchar(20) DEFAULT NULL,
`parent_acnt` varchar(20) DEFAULT NULL,
`number_of_agentlinks` int(11) DEFAULT NULL,
`number_of_emails` int(11) DEFAULT NULL,
`fname` varchar(30) DEFAULT NULL,
`lname` varchar(30) DEFAULT NULL,
whole bunch of other fields
PRIMARY KEY (`acnt`),
KEY `parent_acnt` (`parent_acnt`),
KEY `status` (`status`),
KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
--
Andy Wallace
iHOUSEweb, Inc.
awall...@ihouseweb.com
(866) 645-7700 ext 219
--
Sometimes it pays to stay in bed on Monday, rather than spending the rest of the
week debugging Monday's code.
- Christopher Thompson
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: 1 file
On Wed, Jul 3, 2013 at 12:36 PM, Rick James rja...@yahoo-inc.com wrote: I once found a slowlog called simply 1. But I did not track down the cause. Possibly it was a not-so-correct configuration script. SHOW VARIABLES LIKE '%dir%'; Nothing was set to 1 ibdata1 grows (never shrinks) when data is added, ALTER is done, etc. It will reuse free space within itself. innodb_file_per_table=1 is recommended Having an explicit PRIMARY KEY on InnoDB tables is recommended. (MEMORY did not care much.) -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Wednesday, July 03, 2013 9:29 AM To: shawn green Cc: mysql@lists.mysql.com Subject: Re: 1 file On Wed, Jul 3, 2013 at 9:51 AM, shawn green shawn.l.gr...@oracle.com wrote: Hello Larry, On 7/3/2013 11:27 AM, Larry Martell wrote: We recently changed from in memory files to InnoDB files. Today we noticed that in every server's data dir there is file called '1' that seems to get updated every time the iddata1 file gets updated. On some servers it's comparable in size to the iddata1 file, on other servers it's 10-15x larger, and on others it's 1/2 the size. What is this file. Googling revealed nothing about this. That is not something an official MySQL build would do. Consult with the person (or group) that compiled your binaries. Now, if you have enabled --innodb-file-per-table and if you have named your table '1' then that file is probably '1.ibd'. That would be expected. But that seems unlikely based on your other details. Did you also enable a separate undo log, perhaps? Although if you had, it should be 'undo1' not just '1' http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_i nnodb_undo_tablespaces So, that simple '1' file also seems unusual to me. Thanks for the reply. I asked our DBA group and here's the answer I got: The file is currently accessed by mysqld, please don’t delete it. Looking at the file header, it appeared to be an innodb datafile. But no idea how it was created. Sigh. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: 1 file
I once found a slowlog called simply 1. But I did not track down the cause. Possibly it was a not-so-correct configuration script. SHOW VARIABLES LIKE '%dir%'; ibdata1 grows (never shrinks) when data is added, ALTER is done, etc. It will reuse free space within itself. innodb_file_per_table=1 is recommended Having an explicit PRIMARY KEY on InnoDB tables is recommended. (MEMORY did not care much.) -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Wednesday, July 03, 2013 9:29 AM To: shawn green Cc: mysql@lists.mysql.com Subject: Re: 1 file On Wed, Jul 3, 2013 at 9:51 AM, shawn green shawn.l.gr...@oracle.com wrote: Hello Larry, On 7/3/2013 11:27 AM, Larry Martell wrote: We recently changed from in memory files to InnoDB files. Today we noticed that in every server's data dir there is file called '1' that seems to get updated every time the iddata1 file gets updated. On some servers it's comparable in size to the iddata1 file, on other servers it's 10-15x larger, and on others it's 1/2 the size. What is this file. Googling revealed nothing about this. That is not something an official MySQL build would do. Consult with the person (or group) that compiled your binaries. Now, if you have enabled --innodb-file-per-table and if you have named your table '1' then that file is probably '1.ibd'. That would be expected. But that seems unlikely based on your other details. Did you also enable a separate undo log, perhaps? Although if you had, it should be 'undo1' not just '1' http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_i nnodb_undo_tablespaces So, that simple '1' file also seems unusual to me. Thanks for the reply. I asked our DBA group and here's the answer I got: The file is currently accessed by mysqld, please don’t delete it. Looking at the file header, it appeared to be an innodb datafile. But no idea how it was created. Sigh. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Master not creating new binary log.
Hi all I hope all are well. I would like some assistance with something really strange. We currently have a problem with a master slave setup running mysql 5.0. This is one of our legacy servers which are in the planning to be upgraded, however in order for this to be done the replication needs to be up and running. The problem we have currently however is that the binary logs on the master was moved to a seperate partition due to disc space restrictions. A new binlog file called mysql-bin.1 was created and everything seemed to work fine. However, the moment the file reached the file size of 100Mb, it does not go on to create a new binlog file called mysql-bin.2 and the replication fails stating that it is unable to read the binary log file. Thus far we have done a flush logs and reset master , but the same problem occurs, where it creates mysql-bin.1 and the moment it reaches it's max size and suppose to create a new file, it stops and does not create the new one. I really hope this makes sense, and that someone can perhaps point us in the correct direction. Any help would be appreciated. Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: 1 file
-Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Wednesday, July 03, 2013 11:29 AM To: shawn green Cc: mysql@lists.mysql.com Subject: Re: 1 file On Wed, Jul 3, 2013 at 9:51 AM, shawn green shawn.l.gr...@oracle.com wrote: Hello Larry, On 7/3/2013 11:27 AM, Larry Martell wrote: We recently changed from in memory files to InnoDB files. Today we noticed that in every server's data dir there is file called '1' that seems to get updated every time the iddata1 file gets updated. On some servers it's comparable in size to the iddata1 file, on other servers it's 10-15x larger, and on others it's 1/2 the size. What is this file. Googling revealed nothing about this. That is not something an official MySQL build would do. Consult with the person (or group) that compiled your binaries. Now, if you have enabled --innodb-file-per-table and if you have named your table '1' then that file is probably '1.ibd'. That would be expected. But that seems unlikely based on your other details. Did you also enable a separate undo log, perhaps? Although if you had, it should be 'undo1' not just '1' http://dev.mysql.com/doc/refman/5.6/en/innodb- parameters.html#sysvar_innodb_undo_tablespaces So, that simple '1' file also seems unusual to me. Thanks for the reply. I asked our DBA group and here's the answer I got: The file is currently accessed by mysqld, please don’t delete it. Looking at the file header, it appeared to be an innodb datafile. But no idea how it was created. Sigh. -- I believe this is due to one of the variables in your my.cnf file. You probably set it to 1, thinking this enabled it, but in actuality, the variable can be set by just putting it the my.cnf file If it has an assignment, then the assignment is the file name to use. I think there are several variables that this is the case for For example, log and log_error If you have log_error=1 or log=1 this would be the file There may be others variables, but one of those is where I have seen the 1 file being created before If it isn't one of those two (which it may not, since you said the header looks to be innodb), I would review all the variables that are set to =1, and see if that is the proper assignment MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately.
Re: 1 file
On Wed, Jul 3, 2013 at 11:34 AM, Peterson, Timothy R timothy_r_peter...@uhc.com wrote: -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Wednesday, July 03, 2013 11:29 AM To: shawn green Cc: mysql@lists.mysql.com Subject: Re: 1 file On Wed, Jul 3, 2013 at 9:51 AM, shawn green shawn.l.gr...@oracle.com wrote: Hello Larry, On 7/3/2013 11:27 AM, Larry Martell wrote: We recently changed from in memory files to InnoDB files. Today we noticed that in every server's data dir there is file called '1' that seems to get updated every time the iddata1 file gets updated. On some servers it's comparable in size to the iddata1 file, on other servers it's 10-15x larger, and on others it's 1/2 the size. What is this file. Googling revealed nothing about this. That is not something an official MySQL build would do. Consult with the person (or group) that compiled your binaries. Now, if you have enabled --innodb-file-per-table and if you have named your table '1' then that file is probably '1.ibd'. That would be expected. But that seems unlikely based on your other details. Did you also enable a separate undo log, perhaps? Although if you had, it should be 'undo1' not just '1' http://dev.mysql.com/doc/refman/5.6/en/innodb- parameters.html#sysvar_innodb_undo_tablespaces So, that simple '1' file also seems unusual to me. Thanks for the reply. I asked our DBA group and here's the answer I got: The file is currently accessed by mysqld, please don’t delete it. Looking at the file header, it appeared to be an innodb datafile. But no idea how it was created. Sigh. -- I believe this is due to one of the variables in your my.cnf file. You probably set it to 1, thinking this enabled it, but in actuality, the variable can be set by just putting it the my.cnf file If it has an assignment, then the assignment is the file name to use. I think there are several variables that this is the case for For example, log and log_error If you have log_error=1 or log=1 this would be the file There may be others variables, but one of those is where I have seen the 1 file being created before If it isn't one of those two (which it may not, since you said the header looks to be innodb), I didn't say that - one of my DBA's said that. I didn't have permission to look at the file. But one of my sysadmins did and she found that it was ascii text. I would review all the variables that are set to =1, and see if that is the proper assignment I did that and I found it: log-slow-queries = 1 mysql show variables like 'slow_query_log_file'; +-+---+ | Variable_name | Value | +-+---+ | slow_query_log_file | 1 | +-+---+ 1 row in set (0.00 sec) Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
1 file
We recently changed from in memory files to InnoDB files. Today we noticed that in every server's data dir there is file called '1' that seems to get updated every time the iddata1 file gets updated. On some servers it's comparable in size to the iddata1 file, on other servers it's 10-15x larger, and on others it's 1/2 the size. What is this file. Googling revealed nothing about this. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
