Re: how do I make utf8 the default encoding for my MySQL installation
On Friday, November 18, 2011 at 03:31:44 UTC, martin.muel...@mac.com confabulated: Is it possible to make utf8 the default for all databases and transactions in a MySQL installation? The current default is Latin1. There is a suggestion on the Web to edit the my.cnf file as follows [mysqld] init_connect=’SET collation_connection = utf8_general_ci’ init_connect=’SET NAMES utf8′ default-character-set=utf8 character-set-server=utf8 collation-server=utf8_general_ci skip-character-set-client-handshake On a Mac with MySQL 5.5.17 this produces the error [ERROR] /usr/local/mysql/bin/mysqld: unknown variable 'default-character-set=utf8' The Reference Manual is very tortuous but lacks instructions that are clear to a not very technical user. http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_default-character-set The above link says default-character-set is depreciated and you should be using character-set-server. It also states default-character-set was removed in v5.5.3. You should be able to remove the option. -- There are 10 kinds of people in the world... Those who understand binary, and those who don't. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Help with SELECT and possible JOIN
I have two tables defined: CREATE TABLE `tga_body` ( `body_id` int(10) unsigned NOT NULL auto_increment, `blob_pos` mediumint(8) unsigned NOT NULL, `file_id` int(10) unsigned NOT NULL, `blob_id` int(10) unsigned NOT NULL, PRIMARY KEY USING BTREE (`body_id`), KEY `file_id` (`file_id`), KEY `blob_id` (`blob_id`) ) ENGINE=MyISAM; CREATE TABLE `tga_body_blob` ( `blob_id` int(10) unsigned NOT NULL auto_increment, `blob_data` mediumblob NOT NULL, PRIMARY KEY USING BTREE (`blob_id`), KEY `blob_data` (`blob_data`(64)) ) ENGINE=MyISAM; I need to select all tga_body_blob.blob_data where tga_body.blob_id is equal to tga_body_blob.blob_id and tga_body.file_id is equal to 'some_name' ordered by tga_body.blob_pos. I can type in english what I want. However, I am stumped on the select statement. I'm speculating I will need to use some form of JOIN but am unsure. -d -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help with SELECT and possible JOIN
On Mon, 19 Jan 2009, Duane Hill wrote: I have two tables defined: CREATE TABLE `tga_body` ( `body_id` int(10) unsigned NOT NULL auto_increment, `blob_pos` mediumint(8) unsigned NOT NULL, `file_id` int(10) unsigned NOT NULL, `blob_id` int(10) unsigned NOT NULL, PRIMARY KEY USING BTREE (`body_id`), KEY `file_id` (`file_id`), KEY `blob_id` (`blob_id`) ) ENGINE=MyISAM; CREATE TABLE `tga_body_blob` ( `blob_id` int(10) unsigned NOT NULL auto_increment, `blob_data` mediumblob NOT NULL, PRIMARY KEY USING BTREE (`blob_id`), KEY `blob_data` (`blob_data`(64)) ) ENGINE=MyISAM; I need to select all tga_body_blob.blob_data where tga_body.blob_id is equal to tga_body_blob.blob_id and tga_body.file_id is equal to 'some_name' ordered by tga_body.blob_pos. I can type in english what I want. However, I am stumped on the select statement. I'm speculating I will need to use some form of JOIN but am unsure. I don't know if this is an accurate way of solving or not. I managed to get what I wanted by this select statement: SELECT * FROM tga_body,tga_body_blob WHERE tga_body.file_id = some_id AND tga_body.blob_id = tga_body_blob.blob_id ORDER BY tga_body.blob_pos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Database Creating
On Thu, 7 Aug 2008, [EMAIL PROTECTED] wrote: Hello I would like to create my first mysql database. Is there a gui tool that makes this easy to do? http://dev.mysql.com/downloads/gui-tools/5.0.html -d -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem recovering from mysqldump
On Wed, 28 Nov 2007 12:57:25 +0100 (CET) Alicia Amadoz[EMAIL PROTECTED] wrote: Hi, I want to copy some databases to a new server and I have tried to backup my databases with mysqldump in the old server, then copy those files to the new server and recover them with mysqldump again. The problem is that when doing, mysqldump -u root -p mydb mydb_backup.sql the message that appears is -- MySQL dump 10.11 If mydb_backup.sql is the dump file, try: mysql -u root -p mydb mydb_backup.sql mydb_backup.sql is populated with sql statements that need to be ran using mysql. -- _|_ (_| | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incorrect information in file...
I have pretty much figured out what is going on. I forgot one pretty major item from my initial message. I had started mysql up without any my.cnf configuration file at first and was using it without prior to my using the my-huge.cnf config file. Please correct me if I'm wrong. That ultimately changed the way InnoDB tables were viewed by MySQL because of the new log file and data file path parameters. Which ultimately lead me to this page: http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html On Thursday, August 31, 2006 at 2:25:07 PM, Mikhail confabulated: How much do you care about test table, Duane? In your own words: I do not have anything really set up yet, so drop the table or even the whole testdb database and see if that helps Regards, Mikhail Berman -Original Message- From: Duane Hill [mailto:[EMAIL PROTECTED] Sent: Thursday, August 31, 2006 10:18 AM To: Mikhail Berman Cc: mysql@lists.mysql.com Subject: Re: Incorrect information in file... On Thursday, August 31, 2006 at 2:02:42 PM, Mikhail confabulated: Hi Duane, Have you tried to repair test table using REPAIR to see if that solves your problem? That didn't seem to work. The result returned was: mysql repair table test; +-++--+- + | Table | Op | Msg_type | Msg_text | +-++--+- + | testdb.test | repair | error| Incorrect information in file: './testdb/test.frm' | +-++--+- + I even tried with the USE_FRM option and received the same result. -Original Message- From: Duane Hill [mailto:[EMAIL PROTECTED] Sent: Thursday, August 31, 2006 9:49 AM To: mysql@lists.mysql.com Subject: Incorrect information in file... MySQL v5.0.24 on FreeBSD v6.0-RELEASE I was attempting to do some tweaking in a my.cnf to increase performance on a server here. I had copied the my-huge.cnf config file into /etc and uncommented the section on InnoDB from within. I also changed the thread_concurrency option from 8 to 4. That was the only changes I made in the configuration file. Upon restarting MySQL, I could not select anything from any of the InnoDB tables and received this error: ERROR 1033 (HY000): Incorrect information in file: './testdb/test.frm' I did do some searching in the list archives but couldn't come up with anything concrete. For the convenience, here is the options that were uncommented: innodb_data_home_dir = /var/db/mysql/ innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend innodb_log_group_home_dir = /var/db/mysql/ innodb_log_arch_dir = /var/db/mysql/ innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 -- This message was sent using 100% recycled electrons. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Incorrect information in file...
MySQL v5.0.24 on FreeBSD v6.0-RELEASE I was attempting to do some tweaking in a my.cnf to increase performance on a server here. I had copied the my-huge.cnf config file into /etc and uncommented the section on InnoDB from within. I also changed the thread_concurrency option from 8 to 4. That was the only changes I made in the configuration file. Upon restarting MySQL, I could not select anything from any of the InnoDB tables and received this error: ERROR 1033 (HY000): Incorrect information in file: './testdb/test.frm' I did do some searching in the list archives but couldn't come up with anything concrete. For the convenience, here is the options that were uncommented: innodb_data_home_dir = /var/db/mysql/ innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend innodb_log_group_home_dir = /var/db/mysql/ innodb_log_arch_dir = /var/db/mysql/ innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 -- This message was sent using 100% recycled electrons. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incorrect information in file...
On Thursday, August 31, 2006 at 2:02:42 PM, Mikhail confabulated: Hi Duane, Have you tried to repair test table using REPAIR to see if that solves your problem? No I had not. The problem is easily duplicated and I do not have anything really set up yet. I will try that. Thanks. -Original Message- From: Duane Hill [mailto:[EMAIL PROTECTED] Sent: Thursday, August 31, 2006 9:49 AM To: mysql@lists.mysql.com Subject: Incorrect information in file... MySQL v5.0.24 on FreeBSD v6.0-RELEASE I was attempting to do some tweaking in a my.cnf to increase performance on a server here. I had copied the my-huge.cnf config file into /etc and uncommented the section on InnoDB from within. I also changed the thread_concurrency option from 8 to 4. That was the only changes I made in the configuration file. Upon restarting MySQL, I could not select anything from any of the InnoDB tables and received this error: ERROR 1033 (HY000): Incorrect information in file: './testdb/test.frm' I did do some searching in the list archives but couldn't come up with anything concrete. For the convenience, here is the options that were uncommented: innodb_data_home_dir = /var/db/mysql/ innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend innodb_log_group_home_dir = /var/db/mysql/ innodb_log_arch_dir = /var/db/mysql/ innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 -- This message was sent using 100% recycled electrons. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incorrect information in file...
On Thursday, August 31, 2006 at 2:02:42 PM, Mikhail confabulated: Hi Duane, Have you tried to repair test table using REPAIR to see if that solves your problem? That didn't seem to work. The result returned was: mysql repair table test; +-++--+-+ | Table | Op | Msg_type | Msg_text | +-++--+-+ | testdb.test | repair | error| Incorrect information in file: './testdb/test.frm' | +-++--+-+ I even tried with the USE_FRM option and received the same result. -Original Message- From: Duane Hill [mailto:[EMAIL PROTECTED] Sent: Thursday, August 31, 2006 9:49 AM To: mysql@lists.mysql.com Subject: Incorrect information in file... MySQL v5.0.24 on FreeBSD v6.0-RELEASE I was attempting to do some tweaking in a my.cnf to increase performance on a server here. I had copied the my-huge.cnf config file into /etc and uncommented the section on InnoDB from within. I also changed the thread_concurrency option from 8 to 4. That was the only changes I made in the configuration file. Upon restarting MySQL, I could not select anything from any of the InnoDB tables and received this error: ERROR 1033 (HY000): Incorrect information in file: './testdb/test.frm' I did do some searching in the list archives but couldn't come up with anything concrete. For the convenience, here is the options that were uncommented: innodb_data_home_dir = /var/db/mysql/ innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend innodb_log_group_home_dir = /var/db/mysql/ innodb_log_arch_dir = /var/db/mysql/ innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 -- This message was sent using 100% recycled electrons. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incorrect information in file...
On Thursday, August 31, 2006 at 2:25:07 PM, Mikhail confabulated: How much do you care about test table, Duane? In your own words: I do not have anything really set up yet, so drop the table or even the whole testdb database and see if that helps Sure. That's a quick fix that does happen to correct the problem. However, what if this was happening to a database/table I could not just dump and start over? -Original Message- From: Duane Hill [mailto:[EMAIL PROTECTED] Sent: Thursday, August 31, 2006 10:18 AM To: Mikhail Berman Cc: mysql@lists.mysql.com Subject: Re: Incorrect information in file... On Thursday, August 31, 2006 at 2:02:42 PM, Mikhail confabulated: Hi Duane, Have you tried to repair test table using REPAIR to see if that solves your problem? That didn't seem to work. The result returned was: mysql repair table test; +-++--+- + | Table | Op | Msg_type | Msg_text | +-++--+- + | testdb.test | repair | error| Incorrect information in file: './testdb/test.frm' | +-++--+- + I even tried with the USE_FRM option and received the same result. -Original Message- From: Duane Hill [mailto:[EMAIL PROTECTED] Sent: Thursday, August 31, 2006 9:49 AM To: mysql@lists.mysql.com Subject: Incorrect information in file... MySQL v5.0.24 on FreeBSD v6.0-RELEASE I was attempting to do some tweaking in a my.cnf to increase performance on a server here. I had copied the my-huge.cnf config file into /etc and uncommented the section on InnoDB from within. I also changed the thread_concurrency option from 8 to 4. That was the only changes I made in the configuration file. Upon restarting MySQL, I could not select anything from any of the InnoDB tables and received this error: ERROR 1033 (HY000): Incorrect information in file: './testdb/test.frm' I did do some searching in the list archives but couldn't come up with anything concrete. For the convenience, here is the options that were uncommented: innodb_data_home_dir = /var/db/mysql/ innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend innodb_log_group_home_dir = /var/db/mysql/ innodb_log_arch_dir = /var/db/mysql/ innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 -- This message was sent using 100% recycled electrons. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with INSERT
Can someone either answer or point me somewhere for the answer? I am attempting to store text lines from an e-mail message into a MySQL table through the use of Perl and DBI. Periodically I get the error that states there was an error in the statement and to check the syntax. I know it has something to do with how the statement is being interpreted. Here is the Perl that is in question: $sql = INSERT INTO msg_line_hash ; $sql .= (line_text, line_hash, total_count) ; $sql .= VALUES ; $sql .= ('$msgline', '$digest', 1) ; $sql .= ON DUPLICATE KEY UPDATE total_count = total_count + 1; $sth = $dbh-prepare_cached($sql); $sth-execute(); I'm preparing the statement as cached based on the fact it will be the same statement that will execute several times. Therefore, eliminating having to contact the server on every perpare. I know it's the $msgline that is causing issues in that if I pack $msgline into a readable hex text line, it stores without any errors. That would mean the SQL statement is getting messed up because of some of the characters within $msgline. As to $digest, there isn't any issues with that. It is just an SHA hash of $msgline. -- This message was sent using 100% recycled electrons. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with INSERT
On Mon, 3 Jul 2006, Stefan Hornburg wrote: Duane Hill wrote: Can someone either answer or point me somewhere for the answer? I am attempting to store text lines from an e-mail message into a MySQL table through the use of Perl and DBI. Periodically I get the error that states there was an error in the statement and to check the syntax. I know it has something to do with how the statement is being interpreted. Here is the Perl that is in question: $sql = INSERT INTO msg_line_hash ; $sql .= (line_text, line_hash, total_count) ; $sql .= VALUES ; $sql .= ('$msgline', '$digest', 1) ; $sql .= ON DUPLICATE KEY UPDATE total_count = total_count + 1; $sth = $dbh-prepare_cached($sql); $sth-execute(); I'm preparing the statement as cached based on the fact it will be the same statement that will execute several times. Therefore, eliminating having to contact the server on every perpare. I know it's the $msgline that is causing issues in that if I pack $msgline into a readable hex text line, it stores without any errors. That would mean the SQL statement is getting messed up because of some of the characters within $msgline. Use DBI quote method on $msgline. I'm still a little green with this and had to dig just a wee little bit on the quote method. I've never used the q and qq before. It is working now! Through having to dig just a little, I've also found a more efficient way of doing a loop in the code. Thanks! -- This message was sent using 100% recycled electrons. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How To Pronounce MySQL
On Fri, 9 Jun 2006, Chris White wrote: On Thursday 08 June 2006 05:30 am, Jesse wrote: This may be a really stupid question, but I hate looking stupid if I can avoid it. :-) I have been using Microsoft SQL Server for a while, and I'm now trying to switch all our applications over to use MySQL. Microsoft SQL Server is pronounced Sequel Server. Is MySQL pronounced My Sequel, or is it pronounced My S-Q-L? I mean, generally speaking? Who cares how it's pronounced as long as you spell it right on the command line. I mean, if you can't get it on the command line, you may as well call it a rock ;). Ok. ;-} ln -s /usr/local/mysql/bin/mysql /usr/bin/rock -- This message was sent using 100% recycled electrons. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Condition within a trigger
Hello All, I've been racking my brain trying to figure out something. I'm relatively new to the arena of SQL and have been doing a bunch of reading and experimenting. I have found a need for a trigger that will fire after an insert into a table. The trigger needs to act only if the value of a field contains something. CREATE TRIGGER only_this AFTER INSERT ON table_a FOR EACH ROW INSERT INTO table_b (email_addr,value) values (NEW.email_addr,0); This trigger works fine. However, I only want it to fire when NEW.email_addr contains a specific domain name. This is the part that is causing me confusion. -- This message is made of 100% recycled electrons. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Condition within a trigger
Sorry for responding to my own message. I figured this out. DELIMITER // CREATE TRIGGER only_this AFTER INSERT ON table_a FOR EACH ROW BEGIN IF NEW.email_addr LIKE '[EMAIL PROTECTED]' THEN INSERT INTO table_b (email_addr,value) VALUES (NEW.email_addr,0); END IF; END On Saturday, January 7, 2006 at 2:39:07 AM, [EMAIL PROTECTED] confabulated: Hello All, I've been racking my brain trying to figure out something. I'm relatively new to the arena of SQL and have been doing a bunch of reading and experimenting. I have found a need for a trigger that will fire after an insert into a table. The trigger needs to act only if the value of a field contains something. CREATE TRIGGER only_this AFTER INSERT ON table_a FOR EACH ROW INSERT INTO table_b (email_addr,value) values (NEW.email_addr,0); This trigger works fine. However, I only want it to fire when NEW.email_addr contains a specific domain name. This is the part that is causing me confusion. -- This message is made of 100% recycled electrons. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]