Re: why can not pass constant to stored procedure?
On 10 Mar, 2012, at 7:06 pm, Cifer Lee wrote: when we call procedure normally we declare the parameter out of the procedure and pass the variable to procedure like this set @x=1; call *a_procedure*(@x); why can not directly pass the digit 1 to the* a_procedure* ? You can pass the argument directly as long as it is not an INOUT parameter. Antony T Curtis atcur...@gmail.com 0523 C487 9187 6972 6894 AEC7 3087 F819 B477 B687
Re: ::1 root entry in mysql.user
::1 is the IPv6 address for localhost. On 18 Dec 2011, at 09:17, lourenstcc wrote: Hi, I installed mysql for mac os x. Now I am inspecting mysql.user and I see entries for root which I am not to confident with. On a debian installation there is no host=::1 entry Can you explain this entry? (Could I have inadvertantly made it myself?) Here: mysql select user, host, password from mysql.user; +---+ +---+ | user | host | password | +---+ +---+ | root | localhost | | | root | mbprebel.local | | | root | 127.0.0.1 || | root | ::1 | | Thanks, Lourens -- 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: large temp files created by mysql
Something you're doing is creating a very large temporary table as part of handling it's query. Usual culprit would be something doing a full table join combined with an order by or group by which would typically cause MySQL to need to create a temp table. You should do EXPLAINs on your slow-running queries to find out which ones are likely... If it is generating a 30gig file, I'd expect it must be a very slow query. Regards Antony, On 24 Oct 2011, at 08:03, Joey L wrote: On Mon, Oct 24, 2011 at 10:52 AM, Joey L mjh2...@gmail.com wrote: I have a very large table - approx 3 or 4 gig in size. When i initiate a process on my webpage - mysql starts to create a temporary table in /tmp directory. Is there a way i can create this file/table ahead of time so mysql does not have to create it ? thanks mjh Sorry - it is 30 gig file . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=antony.cur...@ieee.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Slower performance with LOCK TABLES
Even for MyISAM tables, LOCK TABLES is not usually the best solution for increasing performance. When there is little to no contention, LOCK TABLES doesn't offer much value. MyISAM works best when you can get more work done in a statement: Instead of executing a bunch of insert statements, combine them into a single multi-row insert statement, as an example. On 22 Sep 2011, at 06:13, Hank wrote: Thanks for your reply. I failed to mention that these are MYISAM tables, so no transactions. And like I said, this is not a production box nor is there any application running, so there's no contention for the tables being locked. I'm trying to update a database design on two tables with 200 million records each, so anything I can do to increase the performance of these long running queries will shorten the migration running time. What I was referring to was that in the documentation, that when using LOCK TABLES, mysql does not update the key cache until the lock is released, versus when not using LOCK TABLES it does update the key cache on each insert/update/delete. see: http://tuxradar.com/practicalphp/18/2/22 In my testing, I'm seeing a slow down when I use LOCK TABLES versus running the same queries without it. I'm just trying to find a reason why that might be the case. -Hank On Thu, Sep 22, 2011 at 12:42 AM, Antony T Curtis antonycur...@verizon.net wrote: LOCK TABLES...WRITE is very likely to reduce performance if you are using a transactional storage engine, such as InnoDB/XtraDB or PBXT. The reason is that only one connection is holding the write lock and no other concurrent operation may occur on the table. LOCK TABLES is only really useful for non-transactional tables and maybe a few specialized operations where it has its advantages but for 99.9% of cases, it should not be used. What does increase performance is the proper use of transactions with appropriate use of SELECT...FOR UPDATE and SELECT...LOCK IN SHARE MODE. Regards, Antony. On 21 Sep 2011, at 20:34, Hank wrote: According to everything I've read, using LOCK TABLES...WRITE for updates, inserts and deletes should improve performance of mysql server, but I think I've been seeing the opposite effect. I've been doing quite a bit of testing on a 64bit install of CentOS 5.5 installed as a guest OS on a VMWare ESXi 4.0 hypervisor on a Dell R610. There are no other VMs on this box, and there are no other users or threads running on the OS. Just me. I'm using this box strictly for testing of large database migration scripts. It seems like when I execute some of these long running statements without locking the tables, the code runs quite a bit faster than when I do lock the tables. And before testing each run, I do restart the server so there is no query caching and I also use FLUSH TABLES between each test run. All I'm asking is this: Can anything think of a scenario on a single user-box and mysql instance, that locking tables would cause these DML statements to slow down compared to not locking the tables? Thanks, -Hank
Re: Slower performance with LOCK TABLES
LOCK TABLES...WRITE is very likely to reduce performance if you are using a transactional storage engine, such as InnoDB/XtraDB or PBXT. The reason is that only one connection is holding the write lock and no other concurrent operation may occur on the table. LOCK TABLES is only really useful for non-transactional tables and maybe a few specialized operations where it has its advantages but for 99.9% of cases, it should not be used. What does increase performance is the proper use of transactions with appropriate use of SELECT...FOR UPDATE and SELECT...LOCK IN SHARE MODE. Regards, Antony. On 21 Sep 2011, at 20:34, Hank wrote: According to everything I've read, using LOCK TABLES...WRITE for updates, inserts and deletes should improve performance of mysql server, but I think I've been seeing the opposite effect. I've been doing quite a bit of testing on a 64bit install of CentOS 5.5 installed as a guest OS on a VMWare ESXi 4.0 hypervisor on a Dell R610. There are no other VMs on this box, and there are no other users or threads running on the OS. Just me. I'm using this box strictly for testing of large database migration scripts. It seems like when I execute some of these long running statements without locking the tables, the code runs quite a bit faster than when I do lock the tables. And before testing each run, I do restart the server so there is no query caching and I also use FLUSH TABLES between each test run. All I'm asking is this: Can anything think of a scenario on a single user-box and mysql instance, that locking tables would cause these DML statements to slow down compared to not locking the tables? Thanks, -Hank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Binary builds for AIX
Alas, I mothballed my old RS/6000 AIX machine a few years ago. It was getting quite old and only ran AIX 4.3 I had toyed with the idea of getting a more modern machine from ebay but to be honest, I haven't had much time recently. On 9 Sep 2011, at 08:22, Peter Gershkovich wrote: I noticed that there is no binary builds for AIX any more. What would be the best way to install a current version of MySQL (5.5) on AIX? Specifically I am looking for instructions for AIX 6.1 and/or 7.1 Thanks, Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=antony.cur...@ieee.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: non-relational engine for mySQL?
I believe such things already exist, for example the Nitro storage engine. There is a presentation about it at the coming MySQL conference... http://en.oreilly.com/mysql2009/public/schedule/detail/6984 Regards, Antony On 6 Feb 2009, at 14:50, Daevid Vincent wrote: When our database reaches the 10-100TB range, we may need to consider non-relational databases. Relational databases like MySQL tend to heavily rely on random access, which is governed by the slow disk seek rates. On the other hand, non-relational database pioneered by Google's Map/Reduce framework operate at much faster disk transfer rates. Open source solutions incorporating these ideas exist, such as Yahoo's Hadoop and CouchDB. Are there currently (or plans for) any mySQL engines with this type of database? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: trigger that calls a webservice??
On 20 Jun 2008, at 06:43, James wrote: On Fri, June 20, 2008 9:12 am, robert rottermann wrote: Hi there, is it possible to define an update trigger that calls a webservice (or just some external method that would do it). we have a web frontent, that does the indexing of data in its own catalog (zope/plone). so I would like to be able to push an update to the frontend. thanks robert I think the answer is no (at least it was last year) but I found work arounds. Google for mysql external command trigger. You can declare a stored proc as an XMLRPC request and use that as a trigger. Such functionality already works in our experimental tree. http://forge.mysql.com/wiki/ProjectPage_External_Language_Stored_Procedures Regards Antony. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JAVA UDF HOW
Hi! MySQL is an open-source product. That allows anyone to contribute patches and new features from the community. In time, when the feature is mature, it may be included in some future version. Documentation on this feature is very limited because the (2) people behind the project have only pursued it during their personal free time. The code is of alpha quality and the target audience is mostly aimed at people of expert knowledge who can largely figure out how to use it without the aid of verbose documentation. Of course, if anyone is willing to contribute documentation, it would be greatly appreciated. The forge site is a wiki: Anyone may add stuff to it. Regards, Antony On 5 Jun 2008, at 05:05, Abhayjeet Singh Grewal wrote: Thanks Everybody, I went through the project and was a little dissapointed with MySQL not supporting Java natively as yet. Let's hope we include this support in coming future. Also, there is not enough documentation for the project mentioned: http://forge.mysql.com/wiki/ProjectPage_External_Language_Stored_Procedures Thanks again !! Abhay Grewal On Thu, Jun 5, 2008 at 11:04 AM, Antony T Curtis [EMAIL PROTECTED] wrote: Hi, Check out this link for Java stored procedures with MySQL http://forge.mysql.com/wiki/ProjectPage_External_Language_Stored_Procedures It is probably what you are looking for. I have some more info on my blog at http://antbits.blogspot.com/ For more information, you can email Eric Herman [EMAIL PROTECTED] or you can email me. Currently, you will not see any Java Stored Procedure functionality in any official MySQL release but I plan to maintain a fork with this feature. Soon, I plan to be in a position to build 'preview' binaries for Linux, FreeBSD, Windows and MacOS. Regards, Antony Curtis, (Not speaking on behalf of my current or any prior employer) On 4 Jun 2008, at 01:39, Abhayjeet Singh Grewal wrote: Thanks Martin, I looked at the link, but I guess I was not able to put my question in the right way. Basically I have a Java Package and I want to call that package from MYSQL function or procedure. Any help would be much appreciated. Thanks, Abhay On Tue, Jun 3, 2008 at 9:32 PM, Martin [EMAIL PROTECTED] wrote: Abhay- It seems you are referring to the UDF Oracle specific User-defined forms Have you looked at http://download.oracle.com/docs/cd/E10391_01/doc.910/e10363/oimadm.htm#CIAEFFAD ? Martin - Original Message - From: Abhayjeet Singh Grewal [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, June 03, 2008 9:50 AM Subject: JAVA UDF HOW Hi, I am working on oracle to mysql migration and really stuck bad with Java UDF. I did not find any documantation and the ones which I did do not work. Please help me at the earliest. I am using MySQL 5.0.51B comunity server with Java JDK1.6 on windows 2003. Regards, Abhay Grewal
Re: JAVA UDF HOW
Hi, Check out this link for Java stored procedures with MySQL http://forge.mysql.com/wiki/ProjectPage_External_Language_Stored_Procedures It is probably what you are looking for. I have some more info on my blog at http://antbits.blogspot.com/ For more information, you can email Eric Herman [EMAIL PROTECTED] or you can email me. Currently, you will not see any Java Stored Procedure functionality in any official MySQL release but I plan to maintain a fork with this feature. Soon, I plan to be in a position to build 'preview' binaries for Linux, FreeBSD, Windows and MacOS. Regards, Antony Curtis, (Not speaking on behalf of my current or any prior employer) On 4 Jun 2008, at 01:39, Abhayjeet Singh Grewal wrote: Thanks Martin, I looked at the link, but I guess I was not able to put my question in the right way. Basically I have a Java Package and I want to call that package from MYSQL function or procedure. Any help would be much appreciated. Thanks, Abhay On Tue, Jun 3, 2008 at 9:32 PM, Martin [EMAIL PROTECTED] wrote: Abhay- It seems you are referring to the UDF Oracle specific User-defined forms Have you looked at http://download.oracle.com/docs/cd/E10391_01/doc.910/e10363/oimadm.htm#CIAEFFAD ? Martin - Original Message - From: Abhayjeet Singh Grewal [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, June 03, 2008 9:50 AM Subject: JAVA UDF HOW Hi, I am working on oracle to mysql migration and really stuck bad with Java UDF. I did not find any documantation and the ones which I did do not work. Please help me at the earliest. I am using MySQL 5.0.51B comunity server with Java JDK1.6 on windows 2003. Regards, Abhay Grewal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FreeBSD MySQL Performance Tunning suggestions???
Hi,
FreeBSD 7 should offer much better performance for MySQL. The FreeBSD
kernel developers have found ways to relieve some of the kernel
bottlenecks which permit multithreaded applications to operate much
better.
Regards,
Antony.
On 3 Jun 2008, at 03:43, VeeJay wrote:
Hi Guys
I need some performance tuning suggestions/help from you.
At my job, I am going to build a Web Server with
1. FreeBSD 7.0-RELEASE amd64
2. Apache 2.2.8
3. PHP 4.4.8 (or may be PHP5, what do you suggest?)
Server's hardware configuration is as follow:
2 x Quad Core Xeon E5450 3.0GHz,2x6MB,1333FSB
16GB (8x2GB Dual Rank DIMMs) 667MHz FBD
6 x 450GB SAS 15k 3.5 HD Hot Plug
PERC 6/i, Integrated Controller Card x6 backplane
PE2950 III C5 MSS R10 Add-in PERC 5/i / 6/i 1 S
TCP/IP Offload Engine 2P
Broadcom TCP/IP Offload Engine functionality (TOE) Not Enabled
For FreeBSD 7.0-RELEASE amd64 Which MySQL 5.0 would be used ?
1. FreeBSD 7.x (x86_64)
or
2. FreeBSD 6.x (x86)
I have done some googling and made these configuration files for
Apache and
MySQL?
Apache:
httpd.conf-
start
# =
# Basic settings
# =
ServerType standalone
ServerRoot /usr/local/apache
PidFile /usr/local/apache/logs/httpd.pid
ScoreBoardFile /usr/local/apache/logs/httpd.scoreboard
ResourceConfig /dev/null
AccessConfig /dev/null
# =
# Performance settings
# =
Timeout 300
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 10
StartServers 5
MaxClients 256
MaxRequestsPerChild 0
# =
# Apache modules
# =
ClearModuleList
AddModule mod_log_config.c
AddModule mod_mime.c
AddModule mod_dir.c
AddModule mod_access.c
AddModule mod_auth.c
AddModule mod_php4.c
AddModule mod_rewrite.c
AddModule mod_security.c
AddModule mod_setenvif.c
# =
# General settings
# =
Port 80
User apache
Group apache
ServerAdmin [EMAIL PROTECTED]
UseCanonicalName Off
ServerSignature Off
HostnameLookups Off
ServerTokens Prod
IfModule mod_dir.c
DirectoryIndex index.html
/IfModule
DocumentRoot /home/apache/www
# =
# Access control
# =
Directory /
Options None
AllowOverride None
Order deny,allow
Deny from all
/Directory
Directory /home/apache/www
Order allow,deny
Allow from all
/Directory
Directory /home/apache/www/vhosts/mydomain.com/public_html
Order allow,deny
Allow from all
/Directory
# =
# MIME encoding
# =
IfModule mod_mime.c
TypesConfig /usr/local/apache/conf/mime.types
/IfModule
DefaultType text/plain
IfModule mod_mime.c
AddEncoding x-compress Z
AddEncoding x-gzip gz tgz
AddType application/x-tar .tgz
AddType application/x-httpd-php .html
/IfModule
# =
# Logs
# =
LogLevel warn
LogFormat %h %l %u %t \%r\ %s %b \%{Referer}i\ \%{User-Agent}i
\
combined
LogFormat %h %l %u %t \%r\ %s %b common
LogFormat %{Referer}i - %U referer
LogFormat %{User-agent}i agent
ErrorLog /var/apache/logs/error_log
CustomLog /var/apache/logs/access_log combined
# =
# Virtual hosts
# =
NameVirtualHost *
VirtualHost *
DocumentRoot /home/apache/www/vhosts/mydomain.com/public_html
ServerName www.mydomain.com
ServerAlias mydomain.com
ErrorLog /var/apache/logs/vhosts/mydomain.com/error_log
CustomLog /var/apache/logs/vhosts/mydomain.com/access_log
combined
IfModule mod_rewrite.c
RewriteEngine on
RewriteRule ^/([a-z]{2})/index.html$ /index.html?topicid=$1
/IfModule
ErrorDocument 400 /page_error.html
ErrorDocument 401 /page_error.html
ErrorDocument 403 /page_error.html
ErrorDocument 404 /page_error.html
ErrorDocument 500 /page_error.html
/VirtualHost
#
# Logging GET/POST requests, defending against
# Cross-Site-Scripting (XSS) and SQL Injection attacks
#
IfModule mod_security.c
AddHandler application/x-httpd-php .html
#Turn the filtering engine On or Off
SecAuditEngine On
# Only log suspicious requests
SecAuditEngine RelevantOnly
SecAuditLog /var/apache/logs/audit_log
SecFilterScanPOST On
SecFilterEngine On
SecFilterDefaultAction deny,log,status:500
Re: Debug Stored Proc
Hi, Currently there is no way but there is a WorkLog for implementing such a feature, It may be possible to encourage someone to implement such a feature request. Regards, Antony. On 6 May 2008, at 14:58, Bryan Cantwell wrote: Is there no way to step thru a stored proc in order to debug it and see what it is doing? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 6.0.4 Alpha has been released ! (part 1 of 2)
Missing feature not mentioned... Falcon works on PowerPC and UltraSparc. Regards, Antony On 10 Mar, 2008, at 11:53, Joerg Bruehe wrote: Dear MySQL users, MySQL 6.0.4-alpha, a new version of the MySQL database system including the Falcon transactional storage engine (now at beta stage), has been released. The main page for MySQL 6.0 is at: http://www.mysql.com/mysql60/ If you are new to the Falcon storage engine and need more information, please read the Falcon Evaluation Guide at: http://www.mysql.com/why-mysql/white-papers/falcon-getting-started.php and the Falcon White Paper at: http://www.mysql.com/why-mysql/white-papers/storage-engines-falcon.php MySQL 6.0.4-alpha is available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/mysql/6.0.html and mirror sites. Note that not all mirror sites may be up to date at this point in time, so if you can't find this version on some mirror, please try again later or choose another download site. We welcome and appreciate your feedback, bug reports, bug fixes, patches etc.: http://forge.mysql.com/wiki/Contributing Despite all trimming, describing all changes since the last released version of MySQL 6.0 exceeds the mailing list configuration. We had to split this message into two parts, this one (part 1) lists all changes which are labeled functionality, security, incompatible, or important. You can view the full list online at http://dev.mysql.com/doc/refman/6.0/en/news-6-0-4.html Functionality, security, incompatible, or important changes since the last release: Functionality added or changed: * Important Change: Partitioning: Security Fix: It was possible, by creating a partitioned table using the DATA DIRECTORY and INDEX DIRECTORY options to gain privileges on other tables having the same name as the partitioned table. As a result of this fix, any table-level DATA DIRECTORY or INDEX DIRECTORY options are now ignored for partitioned tables. (Bug#32091: http://bugs.mysql.com/32091, CVE-2007-5970 (http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2007-5970)) See also Bug#29325: http://bugs.mysql.com/29325, Bug#32111: http://bugs.mysql.com/32111 * Incompatible Change: The Unicode implementation has been extended to provide support for supplementary characters that lie outside the Basic Multilingual Plane (BMP). Noteworthy features: + utf16 and utf32 character sets have been added. These correspond to the UTF-16 and UTF-32 encodings of the Unicode character set, and they both support supplementary characters. + The utf8 character set from previous versions of MySQL has been renamed to utf8mb3, to reflect that its encoding uses a maximum of three bytes for multi-byte characters. (Old tables that previously used utf8 will be reported as using utf8mb3 after an in-place upgrade to MySQL 6.0, but otherwise work as before.) + The new utf8 character set in MySQL 6.0 is similar to utf8mb3, but its encoding allows up to four bytes per character to enable support for supplementary characters. + The ucs2 character set is essentially unchanged except for the inclusion of some newer BMP characters. In most respects, upgrading from MySQL 5.1 to 6.0 should present few problems with regard to Unicode usage, although there are some potential areas of incompatibility. Some examples: + For the variable-length character data types (VARCHAR and the TEXT types), the maximum length in characters for utf8 columns is less in MySQL 6.0 than previously. + For all character data types (CHAR, VARCHAR, and the TEXT types), the maximum number of characters for utf8 columns that can be indexed is less in MySQL 6.0 than previously. Consequently, if you want to upgrade tables from the old utf8 (now utf8mb3) to the current utf8, it may be necessary to change some column or index definitions. For additional details about the new Unicode character sets and potential incompatibilities, see Section 9.1.8, Unicode Support, and Section 9.1.9, Upgrading from Previous to Current Unicode Support. If you use events, a known issue is that if you upgrade from MySQL 5.1 to 6.0.4, the event scheduler will not work, even after you run mysql_upgrade. (This is an issue only for an upgrade, not for a new installation of MySQL 6.0.4.) To work around this upgrading problem, use these instructions: 1. In MySQL 5.1, before upgrading, create a dump file containing your mysql.event table: shell mysqldump -uroot -p mysql event event.sql 2. Stop the server, upgrade to MySQL 6.0, and start the server. 3. Recreate the mysql.event table using the dump file: shell mysql -uroot -p mysql event.sql 4. Run mysql_upgrade to upgrade the other system tables in the mysql database: shell mysql_upgrade -uroot -p 5. Restart the server. The event scheduler should run normally. * Incompatible Change: Because of a change in the format of the Falcon pages stored within Falcon database files, Falcon
