RE: Question!
Read the online Manual. -Original Message- From: Jarikre Efemena [mailto:jefem...@yahoo.com] Sent: Monday, March 30, 2009 11:30 PM To: mysql@lists.mysql.com Subject: Question! Dear sir, I am young web developer using PHP Script in designing interactive website. I desire to include Mysql database on my websites. Please, how do I import, upload/export Mysql database to a website server after creating a Mysql user account and a particular database on my local machine? I will be very grateful if comprehensive response is granted to my question. Thank You. Efe Jarikre Nigeria -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SSL Connections
Hi Andy, I used the same user. I list the steps that I made: on the server MySQL (10.43.249.17) I created the user: GRANT ALL PRIVILEGES ON *.* TO 'pippo'@'%' IDENTIFIED BY '*'; then, on the same server: mysql --ssl-ca=/root/openssl/cacert.pem -u pippo -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1872 Server version: 5.0.45 Source distribution on the another server : mysql --ssl-ca=/root/openssl/cacert.pem -u pippo -h 10.43.249.17 -p Enter password: ERROR 2026 (HY000): SSL connection error mysql --ssl-ca=/root/openssl/cacert.pem --ssl-cert=/root/openssl/ client-cert.pem --ssl-key=/root/openssl/client-key.pem -u pippo -h 10.43.249.17 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1884 Server version: 5.0.45 Source distribution I believe that to be asked the certification to client but I wish it was not the case. Thanks. Ing. Stefano Elmopi Gruppo Darco - Area ICT Sistemi Via Ostiense 131/L Corpo B, 00154 Roma cell. 3466147165 tel. 0657060500 email:stefano.elm...@sociale.it Il giorno 30/mar/09, alle ore 22:39, Andy Shellam ha scritto: Hi Stefano, I'm guessing your remote (non-local) server is using a different user account than your local server. r...@localhost r...@% r...@somehost.com are all different users. If your remote host, or wildhost user account has the REQUIRE X509 flag (user must be certified) but your local one doesn't, that would explain it. What are your MySQL versions on both your server and remote client? What errors do you get on your client? Regards, Andy Stefano Elmopi wrote: Hi, I'm trying to make connections ssl. I followed the MySQL guide and I've configured the server then I created a normal user, without the REQUIRE SSL. On the same server can connect with the only option: mysql --ssl-ca=cacert.pem -u Pluto -p but if I try to connect from another host, I must, forcibly, also specify the options: --ssl-cert=client-cert.pem --ssl-key=client-key.pem Why ?? The guide says that even from another host, on a normal user, you can use the only option: --ssl-ca but it is not, I am forced to certify the client. Something wrong? Thanks Ing. Stefano Elmopi Gruppo Darco - Area ICT Sistemi Via Ostiense 131/L Corpo B, 00154 Roma cell. 3466147165 tel. 0657060500 email:stefano.elm...@sociale.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Setting auto increment value in an update statement
Hi, I have a table 'test' +-+--+--+-+--- ++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+--- ++ | id | int(11) | NO | PRI | NULL | auto_increment | | foo | varchar(255) | NO | | NULL || | bar | varchar(255) | NO | | NULL || | payload | longblob | YES | | NULL || +-+--+--+-+--- ++ where 'payload' size usually is quite big! Now I want to manipulate a number of rows in a non-trivial way and at the same time set 'id' for those rows to a new number in the sequence. I.e. UPDATE test SET id = NEXT_INSERT_ID(), foo = some_function(foo) WHERE bar ... My motivation is that this table has several consumers in other applications that keep track of the biggest 'id' they have seen between polls and thus can keep track of new and changed rows. Right now, I solve this problem by copying the rows to a temporary table, then delete them and insert them from the temporary table but this moves my huge payload around which I really want to avoid. How can I solve this in a better way? /Andreas Pardeike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
ANN: Database Workbench 3.3 Pro released!
Ladies, gentlemen, Upscene Productions is proud to announce the next version of the popular database development tool: Database Workbench Pro 3.3 Changes Highlights in 3.3 - - Better Stored Routine Debugger - Ability to cancel import/export processes - Monitoring GUI for Firebird 2.1 - Ability to cancel running queries in NexusDB, SQL Anywhere and Firebird 2.1 - Stored Routine debugger for SQL Anywhere - Many new features, enhancements and bug fixes... New Feature Highlights in 3.2, 3.1 and 3.0 - - new database engine support - InterBase and Firebird Trigger Debugger - Conceptual and Physical Data Model Diagramming - ADO/ODBC Explorer (run queries against any ADO/ODBC datasource) - Workspace Feature for increased usability - Increased Oracle support (object types, collection types, object tables and more) - Increased MySQL support (events, spatial extensions and more) - Oracle Stored Code Debugger - Code Editor enhancements (parameter insight, parenthesis highlighting) - File Compare Tool ... and much more ... Download a trial at: http://www.upscene.com/downloads.htm What's new?: http://www.upscene.com/products/dbw/whatsnew30.htm Full list of features and fixes: http://customer.upscene.com/script/mantisgateway.exe/fixed?fixedin=3.3.0projectid=1 Database Workbench supports: - Borland InterBase ( 4.x - 8.x ) - Firebird ( 1.x, 2.x ) - MS SQL Server/MSDE ( v6.5, 7, 2000, 2005, 2008, MSDE 1 2, SQL Express ) - MySQL 4.x, 5.x - Oracle Database ( 8i, 9i, 10g, 11g ) - Sybase SQL Anywhere ( 9, 10 and 11 ) - NexusDB ( 2.07 and up ) If you experience any problems with this new version, don't hestitate and either go to the website and send a support email or email directly to supp...@upscene.com Thank you for your support, Martijn Tonies Database Workbench - the database developer tool for professionals Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Annoying .mysql_history problem
Using MySQL 5.0.67 on OpenSolaris 2008.11, whenever I hit Ctrl-C to terminate a long-running test query, it hangs while it kills the thread. No problem here. If I accidentally (or impatiently) hit Ctrl-C again, it terminates the MySQL CLI. Again, no problem here. What's getting me is that on Ubuntu, everything up to that point was still written to .mysql_history, while on OSol it only goes back as far as my last clean exit. Is there any way to get the Ubuntu behavior? I know the easy answer is Don't use Ctrl-C, you hack but I'd kind of like the .mysql_history to be written as I go. Thanks for any pointers, kabel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
getGeneratedKeys
I have a stored procedure that inserts a record that I call from a PreparedStatement. When I call #getGeneratedKeys it always returns a null result set. Is that expected behavior? Robert DiFalco | Chief Technology Officer (Products) Direct: 503.276.7564 Mobile: 503.890.4994 Charlotte Caswell | Executive Assistant Direct: 503.276.7665 TRIPWIRE | The Leader in Configuration Audit Control Check out the latest Tripwire news! http://www.tripwire.com/press/index.cfm
binary handling - any difference between V4 and V5
Hi, First off, you have to forgive my ignorance, I'm not very savvy with DBs so I hope that I explain myself correctly. I have an anomaly on a development system. There are several fields with the type set as bigint(20) that are meant to store a bit pattern. The pattern is used to determine country restrictions on where items may be purchased. On the live, V4.1.10, system the restrictions work as they should. If update the fields with a string such as: ctrymsk0=ffdf when view on the CLI if shows as -9007199254740993 and when a query such as SELECT COUNT(*) FROM products WHERE productid=808800034 AND (ctrymsk0 0x0020); I get the expected results. I don't fully understand all that is going on with the above bit pattern. What I do know is that the same operations on a V5 system always result in a value of 9223372036854775807 regardless of what pattern is supplied and the returns incorrect results when queried with the bitwise AND. I thought that perhaps there is either a difference in the way bit pattern like this are stored between version 4 and version 5. Or Perhaps there is a mechanism/configuration that allows v4 to stores bits in this way which is I need to enable on V5. Does anyone have a ideas that might either explain the difference or perhaps know what data type these fields should be stored in under V5? TIA, Dp. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Joining memory tables is very very slow!
I'm using MySQL 5.1.30 and have several memory tables with indexes on the appropriate columns. When I try and join 2 particular memory tables together to get 5k rows, it takes 90 seconds. This is incredibly slow considering table1 has 11k rows and table2 has 5k rows. A table join like this should take 10 ms. An explain shows it is not using and index for the table but Extra has Using where;Using join buffer. I can try and use Force Index(..) and it still will not use the index. If I use the MyISAM table instead of that one particular memory table the query takes 800ms which is reasonably fast. Is there a way to force it not to use the join buffer? Has anyone else noticed the slow memory table joins in 5.1? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Data structure for matching for company data
You can use this structure with MyISAM tables. It will work fine except you won't have the advantage of database-level enforcement of foreign key constraints--do it with code. Or use InnoDB tables (enable/load the innobase plugin.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Mysql High load CPU
Helo there guys today ive got a brig problem my server that runs only Mysql is undegoind a very load, the server is ok but memory and cpu usage are very high mys server configuration is a 2x Quad Core Intel® Xeon® E5450, 2x6MB Cache, 3.0GHz, 1333MHz FSB 8GB 800MHz Memory 2x SAS 73GB 15000RPM in RAID 1 and i'm running Myslq Server version: 5.1.32-0.dotdeb.1 (Debian) The server runs mostly InnoD files it has a little Mysam tables Oh yeah I use moodle 1.7.2+ if anyone knows this :D bellow is my.cnf file my server is a Ubuntu 8.04 Tls Version (with all updates and upgrades) Really need help cause server may crash and this cannot heapen. # ## my.cnf ### # # # The MySQL database server configuration file. # # You can copy this to one of: # - /etc/mysql/my.cnf to set global options, # - ~/.my.cnf to set user-specific options. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html # This will be passed to all mysql clients # It has been reported that passwords should be enclosed with ticks/quotes # escpecially if they contain # chars... # Remember to edit /etc/mysql/debian.cnf when changing the socket location. [client] port = 3306 socket = /var/run/mysqld/mysqld.sock # Here is entries for some specific programs # The following values assume you have at least 32M ram # This was formally known as [safe_mysqld]. Both versions are currently parsed. [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = -18 [mysqld] # # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/english skip-external-locking # # For compatibility to other Debian packages that still use # libmysqlclient10 and libmysqlclient12. #old_passwords = 1 # # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. #bind-address = 127.0.0.1 # # * Fine Tuning do MY.CNF # #The size of the buffer used for index blocks. Increase this to get better index handling (for all reads and multiple writes) to as much as you can afford; 64M on a 256M machine that mainly runs MySQL is quite common. #key_buffer_size=1500M alterado no dia da divisao dos servidores key_buffer_size=2000M #Each thread that needs to do a sort allocates a buffer of this size. #sort_buffer_size=700M alterado no dia da divisao dos servidores sort_buffer_size=1000M #If no specific storage engine/table type is defined in an SQL-Create statement the default type will be used. default-storage-engine=innodb #Used to help MySQL to decide when to use the slow but safe key cache index create method. myisam_max_extra_sort_file_size=300k #Don't use the fast sort index method to created index if the temporary file would get bigger than this. myisam_max_sort_file_size=2M #The buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE. myisam_sort_buffer_size=100M #The bigger you set this the less disk I/O is needed to access data in tables. On a dedicated database server you may set this parameter up to 80% of the machine physical memory size. Do not set it too large, though, because competition of the physical memory may cause paging in the operating system. innodb_buffer_pool_size=6000M #Size of a memory pool InnoDB uses to store data dictionary information and other internal data structures. A sensible value for this might be 2M, but the more tables you have in your application the more you will need to allocate here. If InnoDB runs out of memory in this pool, it will start to allocate memory from the operating system, and write warning messages to the MySQL error log. innodb_additional_mem_pool_size=400M #Size of each log file in a log group in megabytes. Sensible values range from 1M to 1/n-th of the size of the buffer pool specified below, where n is the number of log files in the group. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. But larger log files also mean that recovery will be slower in case of a crash. The combined size of log files must be less than 4 GB on 32-bit computers. The default is 5M. innodb_log_file_size=214M #The size of the buffer which InnoDB uses to write log to the log files on disk. Sensible values range from 1M to 8M. A big log buffer allows large transactions to run without a need to write the log to disk until the transaction commit. Thus, if you have big transactions, making the log buffer big will save disk I/O. innodb_log_buffer_size=300M #Specifies when log files are flushed to disk. innodb_flush_log_at_trx_commit=1 #Number of file I/O
A mytop automation shell script (someone may find useful)
http://www.daevid.com/content/examples/snippets.php scroll down to Automatic Monitoring of remote servers You'll need Gnome, ssh keys (for remote execution), .mytop file, wmctrl and xtrlock.
Re: Joining memory tables is very very slow!
Memory tables use hash indexes by default instead of b-tree. Try changing the index, that should help significantly. regards, Walter On Tue, Mar 31, 2009 at 6:47 PM, mos mo...@fastmail.fm wrote: I'm using MySQL 5.1.30 and have several memory tables with indexes on the appropriate columns. When I try and join 2 particular memory tables together to get 5k rows, it takes 90 seconds. This is incredibly slow considering table1 has 11k rows and table2 has 5k rows. A table join like this should take 10 ms. An explain shows it is not using and index for the table but Extra has Using where;Using join buffer. I can try and use Force Index(..) and it still will not use the index. If I use the MyISAM table instead of that one particular memory table the query takes 800ms which is reasonably fast. Is there a way to force it not to use the join buffer? Has anyone else noticed the slow memory table joins in 5.1? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=li...@olindata.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysql High load CPU
Hi, probably your system is swapping on disk, immediately reduce the sort_buffer_size, it is a per connection buffer, and your setting is way too high: sort_buffer_size=1000M (with 8 client threads you finish your ram) set it to something between 256K and 8 M sort_buffer_size=1M also read_rnd_buffer_size=270M again is too high, try with same principle (256K - 8M) read_rnd_buffer_size=1M these are the first quick fixes I would do. Cheers Claudio 2009/4/1 Tadeu Alves tadeu...@gmail.com Helo there guys today ive got a brig problem my server that runs only Mysql is undegoind a very load, the server is ok but memory and cpu usage are very high mys server configuration is a 2x Quad Core Intel® Xeon® E5450, 2x6MB Cache, 3.0GHz, 1333MHz FSB 8GB 800MHz Memory 2x SAS 73GB 15000RPM in RAID 1 and i'm running Myslq Server version: 5.1.32-0.dotdeb.1 (Debian) The server runs mostly InnoD files it has a little Mysam tables Oh yeah I use moodle 1.7.2+ if anyone knows this :D bellow is my.cnf file my server is a Ubuntu 8.04 Tls Version (with all updates and upgrades) Really need help cause server may crash and this cannot heapen. # ## my.cnf ### # # # The MySQL database server configuration file. # # You can copy this to one of: # - /etc/mysql/my.cnf to set global options, # - ~/.my.cnf to set user-specific options. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html # This will be passed to all mysql clients # It has been reported that passwords should be enclosed with ticks/quotes # escpecially if they contain # chars... # Remember to edit /etc/mysql/debian.cnf when changing the socket location. [client] port = 3306 socket = /var/run/mysqld/mysqld.sock # Here is entries for some specific programs # The following values assume you have at least 32M ram # This was formally known as [safe_mysqld]. Both versions are currently parsed. [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = -18 [mysqld] # # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/english skip-external-locking # # For compatibility to other Debian packages that still use # libmysqlclient10 and libmysqlclient12. #old_passwords = 1 # # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. #bind-address = 127.0.0.1 # # * Fine Tuning do MY.CNF # #The size of the buffer used for index blocks. Increase this to get better index handling (for all reads and multiple writes) to as much as you can afford; 64M on a 256M machine that mainly runs MySQL is quite common. #key_buffer_size=1500M alterado no dia da divisao dos servidores key_buffer_size=2000M #Each thread that needs to do a sort allocates a buffer of this size. #sort_buffer_size=700M alterado no dia da divisao dos servidores sort_buffer_size=1000M #If no specific storage engine/table type is defined in an SQL-Create statement the default type will be used. default-storage-engine=innodb #Used to help MySQL to decide when to use the slow but safe key cache index create method. myisam_max_extra_sort_file_size=300k #Don't use the fast sort index method to created index if the temporary file would get bigger than this. myisam_max_sort_file_size=2M #The buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE. myisam_sort_buffer_size=100M #The bigger you set this the less disk I/O is needed to access data in tables. On a dedicated database server you may set this parameter up to 80% of the machine physical memory size. Do not set it too large, though, because competition of the physical memory may cause paging in the operating system. innodb_buffer_pool_size=6000M #Size of a memory pool InnoDB uses to store data dictionary information and other internal data structures. A sensible value for this might be 2M, but the more tables you have in your application the more you will need to allocate here. If InnoDB runs out of memory in this pool, it will start to allocate memory from the operating system, and write warning messages to the MySQL error log. innodb_additional_mem_pool_size=400M #Size of each log file in a log group in megabytes. Sensible values range from 1M to 1/n-th of the size of the buffer pool specified below, where n is the number of log files in the group. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. But larger log files also mean that
MySQL - facts
Need some facts for a Report ... For a hypothetical Kitchen Company's shopping Catalogue. a.. What advantages does a database have over flat-file systems ? b.. Are there any disadvantages in switching to a database solution ? c.. What is MySQL, and how does it relate to ASP, Javascript/VBscript and flat-files ? d.. Are there any alternatives to MySQL ? -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 12840 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message
Re: MySQL - facts
Bob, Your questions are really far too broad for this list. I will give them a shot but you wont find them very helpful, I'm afraid. And the Kitchen company won't come into it. a.. What advantages does a database have over flat-file systems ? This could be the subject of an essay. In fact it is the subject of tens of thousands of essays since the mid-70's, starting with Codd and Date. You might want to google for an introduction to Relational Database Management Systems (RDBMS). Any cursory response on a mailing list could not begin to do that question justice. b.. Are there any disadvantages in switching to a database solution ? See answer to question a. c.. What is MySQL, and how does it relate to ASP, Javascript/VBscript and flat-files ? MySQL is a Relational Database Management System. ASP is a web development language (one of many) which often uses RDBMSs to store and retrieve data. Javascript/VBscript are client-side scripting languages and, generally speaking, have nothing to do with the question at hand. flat-files are a alternative means of storing and retrieving data. They are still in use for certain limited applications. They generally are much faster but are *far* less powerful and leave a whole mes of low-level details in the programmers hands. d.. Are there any alternatives to MySQL ? Dozens: Oracle, postgresql, Informix, DB2, SQL Server, Ingres, Sybase... Best of luck researching your report. -- - michael dykman - mdyk...@gmail.com - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
