building mysql 5.5.8 with default charset

[Varshavchick Alexander]

Hello,

is it a bug or a feature that if building mysql 5.5.8 from source with the 
options 'cmake . -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci'

the charset in include/config.h remains latin1:

#define MYSQL_DEFAULT_CHARSET_NAME "latin1"
#define MYSQL_DEFAULT_COLLATION_NAME "latin1_swedish_ci"

Probably there are some other places as well. As a result, if php is built 
against this mysql installation, it assumes the default charset to be 
latin1, so that many old php applications require adding the explicit 
connection settings (set names utf8) to work correctly.


The amazing thing is that in the 5.1 branch it works the other way - 
replaces everywhere to utf8, so 'set names utf8' is not needed.


Is there any way to make it so in 5.5.8?


Alexander Varshavchick, Metrocom Joint Stock Company
Phone: (812)718-3322, 718-3115(fax)

--
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 restore failing

[杨涛涛]

Hi, I think you didn't run mysql_upgrade script.
David Yeung, In China, Beijing.
My First Blog:http://yueliangdao0608.cublog.cn
My Second Blog:http://yueliangdao0608.blog.51cto.com
My Msn: yueliangdao0...@gmail.com



2010/12/3 Shawn Green (MySQL) 

> On 12/3/2010 03:40, Machiel Richards wrote:
>
>> I checked now and saw that they have already attempted a restore
>> previously and hence the original table was dropped and recreated.
>>
>> I found some links on the internet stating that after restoring the
>> dump file to the new version, the proc table should be dumped using the
>> new version and reloaded.
>> ... snip ...
>> Any ideas?
>>
>
>
> The easiest way to migrate between major versions is to dump logical
> contents of the system data tables (the entire MySQL database) separately
> from the rest of the data. It changes very slowly so there is no risk of
> being out of sync with the rest of the data.
>
>
> For example, instead of dumping the user tables (user, db, privs-table,
> etc...) and restoring them as raw data on the new system, you should get the
> SHOW GRANTS reports for each of your users.
>
> http://dev.mysql.com/doc/refman/5.1/en/show-grants.html
>
> Instead of dumping the raw data in the `proc` table, use the --routines
> option of mysqldump instead to write out the stored procedures as SQL
> statements.
>
>
> http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_routines
>
> Did you remember to dump your triggers (which are database-specific) using
> the --events option so that they were recreated with your production data
> tables?
>
> The tables in the `mysql` database can and usually do change sizes and
> definitions between major versions. The utility mysql_upgrade will modify
> the table definitions to match the current version after you restore your
> old-version tables but if you want to try to avoid that step, you can use my
> techniques.
>
> --
> Shawn Green
> MySQL Principal Technical Support Engineer
> Oracle USA, Inc.
> Office: Blountville, TN
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com
>
>

Re: MySql - crashes daily

[杨涛涛]

Hi.
   Can you show me some of your critical system logs?

David Yeung, In China, Beijing.
My First Blog:http://yueliangdao0608.cublog.cn
My Second Blog:http://yueliangdao0608.blog.51cto.com
My Msn: yueliangdao0...@gmail.com



2010/12/7 Raj Shekhar 

> In infinite wisdom Basil Daoust  wrote:
>
> > Any help greatly appreciated.
> >
> > Our mysql is restarting a LOT!
> > Here is what is in the log, it doesn't seem to give much help.
> >
>
> Do you have the coredump from this crash?  If yes, can you load the core
> dump into gdb and run "bt" (backtrace) and see what it produces?
>
> Check this link to see what steps are required
> <
> http://ronaldbradford.com/blog/how-to-crash-mysqld-intentionally-2010-03-05/
> >
>
>
>
> --
> Raj Shekhar
> -
> If there's anything more important than my ego around, I want it
> caught and shot now.
> -
> Read the latest at my blog: "Humor in the bookstore" <
> http://rajshekhar.net/blog/archives/391-Humor-in-the-bookstore.html>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com
>
>

Re: Negative connection/thread IDs in mysqld.log?

[杨涛涛]

Hi,
  The negative IDs is fine.  You should read the document for the data type
section, especially INT.


David Yeung, In China, Beijing.
My First Blog:http://yueliangdao0608.cublog.cn
My Second Blog:http://yueliangdao0608.blog.51cto.com
My Msn: yueliangdao0...@gmail.com



2010/12/15 Atle Veka 

> Hi, I tried searching for an answer to this to no avail. I noticed my
> mysqld.log contains negative connection/thread IDs. mysqld itself shows
> positive IDs as expected. Some sort of wrap?
>
> /usr/sbin/mysqld, Version: 5.0.32. started with:
> Tcp port: 3306  Unix socket: /tmp/mysql.sock
> Time Id CommandArgument
> 101212  8:00:01 -2049127301 Connect ...
>
>
> Regards,
> Atle
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com
>
>

Re: Discontinued AUTO_INCREMENT problem....

[杨涛涛]

Hi.
   This is a good point for this issue.
David Yeung, In China, Beijing.
My First Blog:http://yueliangdao0608.cublog.cn
My Second Blog:http://yueliangdao0608.blog.51cto.com
My Msn: yueliangdao0...@gmail.com



2010/12/21 partha sarathy 

> Hi,
>
> There is one variable called innodb_autoinc_lock_mode. If the value is 0,
> this
> issue wont come. You might set it to 1 or 2.
>
> -Partha
> www.mafiree.com
>
>
>
> - Original Message 
> From: Wagner Bianchi 
> To: 杨涛涛 
> Cc: Xavier Correyeur ; mysql@lists.mysql.com
> Sent: Tue, 21 December, 2010 3:28:00 PM
> Subject: Re: Discontinued AUTO_INCREMENT problem
>
> Too curious...could you share a SHOW CREATE TABLE from this table as
> requested before?
>
> Best regards.
> --
> Wagner Bianchi
>
>
> 2010/12/21 杨涛涛 
>
> > Hi.
> >   You can show us your show create table statement as well.
> >
> >
> > 杨涛
> > 我博客1:http://yueliangdao0608.cublog.cn
> > My 我博客2:http://yueliangdao0608.blog.51cto.com
> >
> >
> > 2010/12/20 Xavier Correyeur 
> >
> > > Hi everybody !
> > >
> > > A have a discontinued AUTO_INCREMENT sequence when i insert data in a
> > table
> > > with a 100 (or more) items SELECT request.
> > > The problem (or situation) is reproductible, you can see an example
> > below.
> > >
> > > Anybody could explain this to me ?
> > >
> > > Cheers
> > > XC
> > >
> > > My MySQL version : Ver 14.14 Distrib 5.1.41, for debian-linux-gnu
> (i486)
> > > using readline 6.1
> > >
> > > == Example =
> > >
> > > -- CREATE test table
> > >
> > > mysql> create table test(`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
> > > `name` VARCHAR(255), `test` int(10), KEY `keyid`(`id`)) ENGINE=InnoDB
> > > DEFAULT CHARSET=latin1;
> > > Query OK, 0 rows affected (0.00 sec)
> > >
> > > -- INSERT DATA FROM ANOTHER TABLE
> > >
> > > mysql> insert into test(name) select `name`from user limit 100;
> > > Query OK, 100 rows affected (0.01 sec)
> > > Records: 100  Duplicates: 0  Warnings: 0
> > >
> > > -- AUTO_INCREMENT ID CHECK => OK
> > >
> > > mysql> select max(`id`) from test;
> > > +---+
> > > | max(`id`) |
> > > +---+
> > > |  100 |
> > > +---+
> > > 1 row in set (0.00 sec)
> > >
> > > --INSERT DATA WITH CHECKED SELECTREQUEST 2 => DATA INSERT OK
> > >
> > > mysql> insert into test(name) select `name` from userlimit 100;
> > > Query OK, 100 rows affected (0.01 sec)
> > > Records: 100  Duplicates: 0  Warnings: 0
> > >
> > > -- AUTO_INCREMENT ID CHECK => should be 100 + 100 = 200
> > > -- => 27 IDs are unset, first ID of 2nd insert is 128 instead of 101
> > > -- No field between 100 and 128
> > >
> > > mysql> select max(`id`) from test;
> > > +---+
> > > | max(`id`) |
> > > +---+
> > > |  227 |
> > > +---+
> > > 1 row in set (0.00 sec)
> > >
> > > == End Example =
> > >
> > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> > > http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com
> > >
> > >
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com
>
>

Re: Trigger?

[Wagner Bianchi]

I think if you built a trigger to update value of foo's column after, this
trigger will not be compiled cause it will execute two transactions on the
same one. Try it...

Best regards.
--
Wagner Bianchi


2010/12/21 Jerry Schwartz 

> Aha! That was the clue I needed. Thank you so much.
>
>
>
> So, to make sure I understand:
>
>
>
> A “BEFORE” trigger is executed **between** the time that the record is
> assembled and the time that the action occurs. That’s why the constraints on
> the field value were being applied before my trigger was triggered.
>
>
>
> Contrariwise, I assume that an “AFTER” trigger would be executed last,
> after everything has been done.
>
>
>
> Am I correct?
>
>
>
> By the way,
>
>
>
> SET NEW.foo = IFNULL(NEW.foo, 'ok')
>
>
>
> works just fine.
>
>
>
> Regards,
>
>
>
> Jerry Schwartz
>
> Global Information Incorporated
>
> 195 Farmington Ave.
>
> Farmington, CT 06032
>
>
>
> 860.674.8796 / FAX: 860.674.8341
>
> E-mail: je...@gii.co.jp
>
> Web site: www.the-infoshop.com
>
>
>
> *From:* Wagner Bianchi [mailto:wagnerbianch...@gmail.com]
> *Sent:* Monday, December 20, 2010 6:44 PM
>
> *To:* Jerry Schwartz
> *Cc:* mysql@lists.mysql.com
> *Subject:* Re: Trigger?
>
>
>
> Well, to produce this result, the first thing that we have to do is to *get
> rid of* the NOT NULL constraint of the column `foo`. After it, the 'null'
> can be sent within a INSERT statement, as below:
>
>
>   mysql> show create table testtrigger\G
> *** 1. row ***
>Table: testtrigger
> Create Table: CREATE TABLE `testtrigger` (
>   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
>   `foo` char(10) DEFAULT NULL,
>   PRIMARY KEY (`id`)
> ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
> 1 row in set (0.05 sec)
>
> so, after to create table, we create the trigger:
>
> mysql> create trigger trg_test
> -> before insert on testtrigger
> -> for each row
> -> begin
> ->   if(NEW.foo IS NULL || NEW.foo = '') then
> -> set NEW.foo = 'Ok';
> ->   end if;
> -> end;
> -> //
> Query OK, 0 rows affected (0.04 sec)
>
> mysql> insert into testtrigger set id =100, foo =null;
> Query OK, 1 row affected (0.03 sec)
>
> mysql> select * from testtrigger;
> +-+--+
> | id  | foo  |
> +-+--+
> | 100 | Ok   |
> +-+--+
> 1 row in set (0.00 sec)
>
> The way that your table is now, with foo NOT NULL, you can't send foo =null
> with a query cause column don't accept null values. The column was defined
> as a not null.
>
> Look this:
>
> mysql> alter table testtrigger modify foo char(10) not null;
> Query OK, 1 row affected (0.10 sec)
> Records: 1  Duplicates: 0  Warnings: 0
>
> mysql> insert into testtrigger set id =100, foo =null;
> ERROR 1048 (23000): Column 'foo' cannot be null
>
> Did you get?
>
> Best regards.
>
> --
>
> Wagner Bianchi
>
>
>
> 2010/12/20 Jerry Schwartz 
>
> I've never used a trigger before, and I want to make one that sounds like
> it
> should be simple.
>
> Create Table: CREATE TABLE `testtrigger` (
>  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
>  `foo` char(10) NOT NULL,
>  PRIMARY KEY (`id`)
> ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
>
> Here's what I want to do: if no value is supplied for `foo`, or if a NULL
> value is supplied for `foo`, I want to set it to a particular value.
>
> I tried things like this:
>
> SET NEW.foo = IFNULL(NEW.foo,'ok')
>
> But that didn't work.
>
> If you point me in the right direction, I'll be okay from there (I hope).
>
> Thanks.
>
> Regards,
>
> Jerry Schwartz
> Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
> E-mail: je...@gii.co.jp
> Web site: www.the-infoshop.com
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com
>
>
>

Corrupted TRG file?

[Jerry Schwartz]

Version 5.1.31 Community (Win32)

 

I created a trigger named TC_Products_Date_Trigger on the table TC_Products.

 

I typed in 

 

SHOW CREATE TRIGGER tc_products_date_trigger;

 

and this came back at me:

 

ERROR 1602 (HY000): Corrupted TRG file for table `subtable_usa`.`tc_products`

 

I almost jumped out of my skin. Then I typed in

 

SHOW CREATE TRIGGER TC_Products_Date_Trigger;

 

and that worked just fine. I didn’t find any bug reports that seemed relevant, 
but I can’t believe I’m first one to stumble on this.

 

Regards,

 

Jerry Schwartz

Global Information Incorporated

195 Farmington Ave.

Farmington, CT 06032

 

860.674.8796 / FAX: 860.674.8341

E-mail:   je...@gii.co.jp 

Web site:   www.the-infoshop.com

 

Download, configure, compile, and install MySQL 5.5.8 from source code on Linux

[shahryar ghazi]

Download, configure, compile, and install MySQL 5.5.8 from source code on
Linux

thanks.

RE: Trigger?

[Jerry Schwartz]

Aha! That was the clue I needed. Thank you so much.

 

So, to make sure I understand:

 

A “BEFORE” trigger is executed **between** the time that the record is 
assembled and the time that the action occurs. That’s why the constraints on 
the field value were being applied before my trigger was triggered.

 

Contrariwise, I assume that an “AFTER” trigger would be executed last, after 
everything has been done.

 

Am I correct?

 

By the way, 

 

SET NEW.foo = IFNULL(NEW.foo, 'ok')

 

works just fine.

 

Regards,

 

Jerry Schwartz

Global Information Incorporated

195 Farmington Ave.

Farmington, CT 06032

 

860.674.8796 / FAX: 860.674.8341

E-mail: je...@gii.co.jp 

Web site: www.the-infoshop.com  

 

From: Wagner Bianchi [mailto:wagnerbianch...@gmail.com] 
Sent: Monday, December 20, 2010 6:44 PM
To: Jerry Schwartz
Cc: mysql@lists.mysql.com
Subject: Re: Trigger?

 

Well, to produce this result, the first thing that we have to do is to get rid 
of the NOT NULL constraint of the column `foo`. After it, the 'null' can be 
sent within a INSERT statement, as below:

  mysql> show create table testtrigger\G
*** 1. row ***
   Table: testtrigger
Create Table: CREATE TABLE `testtrigger` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `foo` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.05 sec)

so, after to create table, we create the trigger:

mysql> create trigger trg_test
-> before insert on testtrigger
-> for each row
-> begin
->   if(NEW.foo IS NULL || NEW.foo = '') then
-> set NEW.foo = 'Ok';
->   end if;
-> end;
-> //
Query OK, 0 rows affected (0.04 sec)

mysql> insert into testtrigger set id =100, foo =null;
Query OK, 1 row affected (0.03 sec)

mysql> select * from testtrigger;
+-+--+
| id  | foo  |
+-+--+
| 100 | Ok   |
+-+--+
1 row in set (0.00 sec)

The way that your table is now, with foo NOT NULL, you can't send foo =null 
with a query cause column don't accept null values. The column was defined as a 
not null.

Look this:

mysql> alter table testtrigger modify foo char(10) not null;
Query OK, 1 row affected (0.10 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into testtrigger set id =100, foo =null;
ERROR 1048 (23000): Column 'foo' cannot be null

Did you get?



Best regards.

--

Wagner Bianchi

 

2010/12/20 Jerry Schwartz 

I've never used a trigger before, and I want to make one that sounds like it
should be simple.

Create Table: CREATE TABLE `testtrigger` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `foo` char(10) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

Here's what I want to do: if no value is supplied for `foo`, or if a NULL
value is supplied for `foo`, I want to set it to a particular value.

I tried things like this:

SET NEW.foo = IFNULL(NEW.foo,'ok')

But that didn't work.

If you point me in the right direction, I'll be okay from there (I hope).

Thanks.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com

 

RE: Trigger?

[Jerry Schwartz]

Here's my latest attempt:

localhost >CREATE TRIGGER makefoo BEFORE INSERT ON testtrigger
-> FOR EACH ROW
-> SET NEW.foo = IFNULL(NEW.foo, 'ok')
-> |
Query OK, 0 rows affected (0.00 sec)

As you can see, the trigger syntax is correct; but it doesn't do what I want.

localhost >INSERT INTO testtrigger (id, foo) VALUES (NULL, 'xxx');
Query OK, 1 row affected (0.00 sec)

localhost >INSERT INTO testtrigger (id, foo) VALUES (NULL, NULL);
ERROR 1048 (23000): Column 'foo' cannot be null

localhost >INSERT INTO testtrigger (id) VALUES (NULL);
ERROR 1364 (HY000): Field 'foo' doesn't have a default value

So I'm missing something important.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com


>-Original Message-
>From: Michael Dykman [mailto:mdyk...@gmail.com]
>Sent: Monday, December 20, 2010 6:25 PM
>To: Jerry Schwartz
>Cc: mysql@lists.mysql.com
>Subject: Re: Trigger?
>
>The expression you supplied looks right enough..  how was it declared?
> as an on UPDATE/on INSERT trigger or just a single case?
>
>
> - michael dykman
>
>On Mon, Dec 20, 2010 at 5:21 PM, Jerry Schwartz  wrote:
>> I've never used a trigger before, and I want to make one that sounds like 
>> it
>> should be simple.
>>
>> Create Table: CREATE TABLE `testtrigger` (
>>  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
>>  `foo` char(10) NOT NULL,
>>  PRIMARY KEY (`id`)
>> ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
>>
>> Here's what I want to do: if no value is supplied for `foo`, or if a NULL
>> value is supplied for `foo`, I want to set it to a particular value.
>>
>> I tried things like this:
>>
>> SET NEW.foo = IFNULL(NEW.foo,'ok')
>>
>> But that didn't work.
>>
>> If you point me in the right direction, I'll be okay from there (I hope).
>>
>> Thanks.
>>
>> Regards,
>>
>> Jerry Schwartz
>> Global Information Incorporated
>> 195 Farmington Ave.
>> Farmington, CT 06032
>>
>> 860.674.8796 / FAX: 860.674.8341
>> E-mail: je...@gii.co.jp
>> Web site: www.the-infoshop.com
>>
>>
>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com
>>
>>
>
>
>
>--
> - michael dykman
> - mdyk...@gmail.com
>
> May the Source be with you.




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Discontinued AUTO_INCREMENT problem....

[partha sarathy]

Hi,

There is one variable called innodb_autoinc_lock_mode. If the value is 0, this 
issue wont come. You might set it to 1 or 2.

-Partha
www.mafiree.com



- Original Message 
From: Wagner Bianchi 
To: 杨涛涛 
Cc: Xavier Correyeur ; mysql@lists.mysql.com
Sent: Tue, 21 December, 2010 3:28:00 PM
Subject: Re: Discontinued AUTO_INCREMENT problem

Too curious...could you share a SHOW CREATE TABLE from this table as
requested before?

Best regards.
--
Wagner Bianchi


2010/12/21 杨涛涛 

> Hi.
>   You can show us your show create table statement as well.
>
>
> 杨涛
> 我博客1:http://yueliangdao0608.cublog.cn
> My 我博客2:http://yueliangdao0608.blog.51cto.com
>
>
> 2010/12/20 Xavier Correyeur 
>
> > Hi everybody !
> >
> > A have a discontinued AUTO_INCREMENT sequence when i insert data in a
> table
> > with a 100 (or more) items SELECT request.
> > The problem (or situation) is reproductible, you can see an example
> below.
> >
> > Anybody could explain this to me ?
> >
> > Cheers
> > XC
> >
> > My MySQL version : Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486)
> > using readline 6.1
> >
> > == Example =
> >
> > -- CREATE test table
> >
> > mysql> create table test(`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
> > `name` VARCHAR(255), `test` int(10), KEY `keyid`(`id`)) ENGINE=InnoDB
> > DEFAULT CHARSET=latin1;
> > Query OK, 0 rows affected (0.00 sec)
> >
> > -- INSERT DATA FROM ANOTHER TABLE
> >
> > mysql> insert into test(name) select `name`from user limit 100;
> > Query OK, 100 rows affected (0.01 sec)
> > Records: 100  Duplicates: 0  Warnings: 0
> >
> > -- AUTO_INCREMENT ID CHECK => OK
> >
> > mysql> select max(`id`) from test;
> > +---+
> > | max(`id`) |
> > +---+
> > |  100 |
> > +---+
> > 1 row in set (0.00 sec)
> >
> > --INSERT DATA WITH CHECKED SELECTREQUEST 2 => DATA INSERT OK
> >
> > mysql> insert into test(name) select `name` from userlimit 100;
> > Query OK, 100 rows affected (0.01 sec)
> > Records: 100  Duplicates: 0  Warnings: 0
> >
> > -- AUTO_INCREMENT ID CHECK => should be 100 + 100 = 200
> > -- => 27 IDs are unset, first ID of 2nd insert is 128 instead of 101
> > -- No field between 100 and 128
> >
> > mysql> select max(`id`) from test;
> > +---+
> > | max(`id`) |
> > +---+
> > |  227 |
> > +---+
> > 1 row in set (0.00 sec)
> >
> > == End Example =
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.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: max allowed packets on Mac

[Sharl]

你出现的太频繁了，兄弟

You post too frequently in the list. 

杨涛涛 编写：

>I agree with michael dykman.
>杨涛
>我博客1:http://yueliangdao0608.cublog.cn
>My 我博客2:http://yueliangdao0608.blog.51cto.com
>
>
>2010/12/18 Michael Dykman 
>
>> I'm not nuts about deploying MySQL on macs, but I think you need to
>> assume root.  As your mac admin user, try
>> $ sudo su -
>> and give your admin password when asked.  You should now be root and
>> can expect permissions to behave as you would expect.
>>
>>  - michael dykman
>>
>> On Fri, Dec 17, 2010 at 12:16 PM, g...@noiseunit.com 
>> wrote:
>> > Hi,
>> >
>> > I found this page on the wiki regarding max allowed packets.
>> > http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html
>> >
>> >
>> > However, on my Mac OSX when I try to run the following from terminal
>> window
>> > I get "Permission Denied"
>> >
>> > shell> mysqld --max_allowed_packet=16M
>> >
>> > And if I try to run as sudo but I again get "Permission Denied" even
>> though
>> > I am logged in as Admin on my machine.
>> >
>> > So I tried to chmod 777 on mysqld but again received an error:
>> >
>> > "chmod: Unable to change file mode on mysqld: Operation not permitted"
>> >
>> > Any help on how I can rectify this would be appreciated.
>> >
>> > Thanks,
>> > Greg
>> >
>> >
>> > --
>> > MySQL General Mailing List
>> > For list archives: http://lists.mysql.com/mysql
>> > To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com
>> >
>> >
>>
>>
>>
>> --
>>  - michael dykman
>>  - mdyk...@gmail.com
>>
>>  May the Source be with you.
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com
>>
>>

Re: PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

[杨涛涛]

Maybe you should give a password to MySQL's root. And I think this note will
disappear.
David Yeung, In China, Beijing.
My First Blog:http://yueliangdao0608.cublog.cn
My Second Blog:http://yueliangdao0608.blog.51cto.com
My Msn: yueliangdao0...@gmail.com



2010/12/17 Yves Goergen 

> On 14.12.2010 20:36 CE(S)T, Alejandro Bednarik wrote:
> > Are you using ubuntu deb's or mysql bin? Do you get that message when
> > the service start?  Look init script to see what it does.
>
> I get the message when MySQL is started. I've installed the Ubuntu
> standard package mysql-server-5.1. It has already asked for a root
> password during package configuration and I did enter some password there.
>
> I just tried to grep my whole filesystem for parts of this message but
> either grep failed "allocating memory" (there's plenty left!) or it
> didn't find the text in a file I could start something with. So I still
> don't know where the message comes from!
>
> --
> Yves Goergen "LonelyPixel" 
> Visit my web laboratory at http://beta.unclassified.de
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com
>
>

Re: Discontinued AUTO_INCREMENT problem....

[Wagner Bianchi]

Too curious...could you share a SHOW CREATE TABLE from this table as
requested before?

Best regards.
--
Wagner Bianchi


2010/12/21 杨涛涛 

> Hi.
>   You can show us your show create table statement as well.
>
>
> 杨涛
> 我博客1:http://yueliangdao0608.cublog.cn
> My 我博客2:http://yueliangdao0608.blog.51cto.com
>
>
> 2010/12/20 Xavier Correyeur 
>
> > Hi everybody !
> >
> > A have a discontinued AUTO_INCREMENT sequence when i insert data in a
> table
> > with a 100 (or more) items SELECT request.
> > The problem (or situation) is reproductible, you can see an example
> below.
> >
> > Anybody could explain this to me ?
> >
> > Cheers
> > XC
> >
> > My MySQL version : Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486)
> > using readline 6.1
> >
> > == Example =
> >
> > -- CREATE test table
> >
> > mysql> create table test(`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
> > `name` VARCHAR(255), `test` int(10), KEY `keyid`(`id`)) ENGINE=InnoDB
> > DEFAULT CHARSET=latin1;
> > Query OK, 0 rows affected (0.00 sec)
> >
> > -- INSERT DATA FROM ANOTHER TABLE
> >
> > mysql> insert into test(name) select `name`from user limit 100;
> > Query OK, 100 rows affected (0.01 sec)
> > Records: 100  Duplicates: 0  Warnings: 0
> >
> > -- AUTO_INCREMENT ID CHECK => OK
> >
> > mysql> select max(`id`) from test;
> > +---+
> > | max(`id`) |
> > +---+
> > |  100 |
> > +---+
> > 1 row in set (0.00 sec)
> >
> > --INSERT DATA WITH CHECKED SELECTREQUEST 2 => DATA INSERT OK
> >
> > mysql> insert into test(name) select `name` from userlimit 100;
> > Query OK, 100 rows affected (0.01 sec)
> > Records: 100  Duplicates: 0  Warnings: 0
> >
> > -- AUTO_INCREMENT ID CHECK => should be 100 + 100 = 200
> > -- => 27 IDs are unset, first ID of 2nd insert is 128 instead of 101
> > -- No field between 100 and 128
> >
> > mysql> select max(`id`) from test;
> > +---+
> > | max(`id`) |
> > +---+
> > |  227 |
> > +---+
> > 1 row in set (0.00 sec)
> >
> > == End Example =
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com
> >
> >
>

Re: MySQL Parallel Inserts

[杨涛涛]

Hi.
   MySQL do the sql parse in the single way, just one thread. So if there
are parallel insert statement, it will not faster than before.
David Yeung, In China, Beijing.
My First Blog:http://yueliangdao0608.cublog.cn
My Second Blog:http://yueliangdao0608.blog.51cto.com


2010/12/17 Andy 

> Shawn/Krishna,
>
> Thank you. I will try this.
>
> -Andy
>
>
> On Tue, Dec 14, 2010 at 8:13 AM, Krishna Chandra Prajapati <
> prajapat...@gmail.com> wrote:
>
> > Hi Andy,
> >
> > I agree, prefer LOAD DATA INFILE... command for bulk loading. It is 30%
> > faster than normal inserts.
> >
> > Krishna
> >
> >
> > On Tue, Dec 14, 2010 at 8:02 AM, Andy  wrote:
> >
> >> Greetings everyone.
> >>
> >> I am in a situation where I need to do parallel inserts into MySQL
> >> database
> >> from inside my Perl program. Basically, I have several million records
> to
> >> insert into the database, and hence I would rather do them in parallel
> >> than
> >> doing them one at a time. I looked around but did not find any
> information
> >> on doing this. Does MySQL not support parallel reads/writes?
> >>
> >> Thanks in advance.
> >>
> >> Andy
> >>
> >
> >
>

Re: Kill DELETE Query

[杨涛涛]

Yeah. The "Delete from tablename " will remove the record one by one.  So
you can cancel it whenever you don't need to continue.

David Yeung, In China, Beijing.
My First Blog:http://yueliangdao0608.cublog.cn
My Second Blog:http://yueliangdao0608.blog.51cto.com


2010/12/17 Willy Mularto 

> Thanks for the confirmation.
>
>
>
> sangprabv
> sangpr...@gmail.com
> http://www.petitiononline.com/froyo/
>
>
> On Dec 17, 2010, at 1:31 PM, Ananda Kumar wrote:
>
> > No...it will not.
> >
> > On Fri, Dec 17, 2010 at 11:26 AM, Willy Mularto 
> wrote:
> > Thanks for the reply. I used non stored procedure approach. Another
> question is if I kill the process will it crash the table? Thanks.
> >
> >
> >
> > sangprabv
> > sangpr...@gmail.com
> > http://www.petitiononline.com/froyo/
> >
> >
> > On Dec 17, 2010, at 12:06 PM, Ananda Kumar wrote:
> >
> >> If u have used a stored proc to delete the rows, and commting freqently,
> then the kill will happen faster.
> >> If you have just used "delete from table_name where , then it
> would take toot much time to rollback all the deleted but not commited rows.
> >>
> >> Regards
> >> anandkl
> >>
> >> On Fri, Dec 17, 2010 at 8:37 AM, Willy Mularto 
> wrote:
> >> Hi List,
> >> I run a delete query to delete around 1 million rows in innodb table,
> It's been hours and still unfinish. Is it safe to kill that delete query
> process while the table is also inserting and updating other rows? Thanks.
> >>
> >>
> >>
> >>
> >> sangprabv
> >> sangpr...@gmail.com
> >> http://www.petitiononline.com/froyo/
> >>
> >>
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
> >>
> >>
> >
> >
>
>

Re: max allowed packets on Mac

[杨涛涛]

I agree with michael dykman.
杨涛
我博客1:http://yueliangdao0608.cublog.cn
My 我博客2:http://yueliangdao0608.blog.51cto.com


2010/12/18 Michael Dykman 

> I'm not nuts about deploying MySQL on macs, but I think you need to
> assume root.  As your mac admin user, try
> $ sudo su -
> and give your admin password when asked.  You should now be root and
> can expect permissions to behave as you would expect.
>
>  - michael dykman
>
> On Fri, Dec 17, 2010 at 12:16 PM, g...@noiseunit.com 
> wrote:
> > Hi,
> >
> > I found this page on the wiki regarding max allowed packets.
> > http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html
> >
> >
> > However, on my Mac OSX when I try to run the following from terminal
> window
> > I get "Permission Denied"
> >
> > shell> mysqld --max_allowed_packet=16M
> >
> > And if I try to run as sudo but I again get "Permission Denied" even
> though
> > I am logged in as Admin on my machine.
> >
> > So I tried to chmod 777 on mysqld but again received an error:
> >
> > "chmod: Unable to change file mode on mysqld: Operation not permitted"
> >
> > Any help on how I can rectify this would be appreciated.
> >
> > Thanks,
> > Greg
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com
> >
> >
>
>
>
> --
>  - michael dykman
>  - mdyk...@gmail.com
>
>  May the Source be with you.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com
>
>

Re: mysqldumpslow

[杨涛涛]

Yeah,  It can only parse the local file. If you expect to parse the remote
one, just download it,haha.

杨涛
我博客1:http://yueliangdao0608.cublog.cn
My 我博客2:http://yueliangdao0608.blog.51cto.com


2010/12/19 Eric Bergen 

> I don't think so. mysqldumpslow parses the log file on the local server.
>
> On Fri, Dec 17, 2010 at 3:01 AM, partha sarathy 
> wrote:
> > Is it possible to run mysqldumpslow on a remote host?
> >
> > -Partha
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=eric.ber...@gmail.com
> >
> >
>
>
>
> --
> Eric Bergen
> eric.ber...@gmail.com
> http://www.ebergen.net
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com
>
>

Re: Discontinued AUTO_INCREMENT problem....

[杨涛涛]

Hi.
   You can show us your show create table statement as well.


杨涛
我博客1:http://yueliangdao0608.cublog.cn
My 我博客2:http://yueliangdao0608.blog.51cto.com


2010/12/20 Xavier Correyeur 

> Hi everybody !
>
> A have a discontinued AUTO_INCREMENT sequence when i insert data in a table
> with a 100 (or more) items SELECT request.
> The problem (or situation) is reproductible, you can see an example below.
>
> Anybody could explain this to me ?
>
> Cheers
> XC
>
> My MySQL version : Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486)
> using readline 6.1
>
> == Example =
>
> -- CREATE test table
>
> mysql> create table test(`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
> `name` VARCHAR(255), `test` int(10), KEY `keyid`(`id`)) ENGINE=InnoDB
> DEFAULT CHARSET=latin1;
> Query OK, 0 rows affected (0.00 sec)
>
> -- INSERT DATA FROM ANOTHER TABLE
>
> mysql> insert into test(name) select `name`from user limit 100;
> Query OK, 100 rows affected (0.01 sec)
> Records: 100  Duplicates: 0  Warnings: 0
>
> -- AUTO_INCREMENT ID CHECK => OK
>
> mysql> select max(`id`) from test;
> +---+
> | max(`id`) |
> +---+
> |  100 |
> +---+
> 1 row in set (0.00 sec)
>
> --INSERT DATA WITH CHECKED SELECTREQUEST 2 => DATA INSERT OK
>
> mysql> insert into test(name) select `name` from userlimit 100;
> Query OK, 100 rows affected (0.01 sec)
> Records: 100  Duplicates: 0  Warnings: 0
>
> -- AUTO_INCREMENT ID CHECK => should be 100 + 100 = 200
> -- => 27 IDs are unset, first ID of 2nd insert is 128 instead of 101
> -- No field between 100 and 128
>
> mysql> select max(`id`) from test;
> +---+
> | max(`id`) |
> +---+
> |  227 |
> +---+
> 1 row in set (0.00 sec)
>
> == End Example =
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com
>
>