← Back to team overview

maria-developers team mailing list archive

Re: Implementing implicit primary key in mysql server

 

Hi Jeremy,

Thank you for your suggestion.
I also want to just add the PK field for custom automatically, but some of
our customs can't accept it.
Because they are using "SELECT * FROM table .... " or " INSERT INTO table
VALUES(...) ", if I add a visible PK for them, "SELECT *" will show this
value, then their applications will report errors.
So I have to set this field as an implicit filed.

Thanks,
Lixun



On Sat, Jun 22, 2013 at 4:36 AM, Jeremy Cole <jeremycole@xxxxxxxxxx> wrote:

> Lixun,
>
> I've thought about this a bit and I'm not sure this will be very simple to
> do (or rather it's more complicated than it might seem). While I think it
> is not that hard to expose the __id field to replication internals, I think
> in order for this to really work it would need to be exposed to other
> tools, such as mysqldump. It is also unclear how to decide when it is safe
> to use this __id field (how to determine if it is in sync between master
> and slave).
>
> As an alternate suggestion, what about ignoring the current implicit PK
> behavior, and instead automatically adding a field using auto_increment
> when the user doesn't provide a PK:
>
> __id BIGINT UNSIGNED NOT NULL auto_increment,
> PRIMARY KEY(__id)
>
> Regards,
>
> Jeremy
>
>
> On Wed, Jun 19, 2013 at 2:40 AM, Lixun Peng <penglixun@xxxxxxxxx> wrote:
>
>> Hi Sergei,
>>
>> You are right, let users add primary key is best.
>> But I can't let users who don't want to create primary key can't use our
>> MySQL service.
>> Amazon RDS also allow users to create the tables without primary key,
>> just change binlog_format to MIXED to solve replication problem.
>>
>> I think this feature is very useful for any MySQL cloud service
>> providers, all of them will face this problem in one day.
>> I will try to do some analysis/research in this feature implement first,
>> I will need your help :-)
>>
>> I will notice any new updates in this email.
>>
>>
>> Thanks,
>> Lixun
>>
>>
>>
>>
>> On Wed, Jun 19, 2013 at 5:14 PM, Sergei Golubchik <serg@xxxxxxxxxxx>wrote:
>>
>>> Hi, Lixun!
>>>
>>>
>>> On Jun 18, Lixun Peng wrote:
>>> > Hi,
>>> >
>>> > As we know, InnoDB has implicit primary key if a table hasn't defined
>>> > a primary key.  However MySQL server doesn't know this primary key, so
>>> > this primary key will not apear in binlog.
>>> >
>>> > When we are using ROW format binlog for replication, if a table has no
>>> any
>>> > indexes, that's a disaster. If a table without indexes do a DML
>>> > (UPDATE/DELETE), of course it will run a long time in master, but in
>>> slave,
>>> > it still need a long time. It will cause serious slave replication
>>> delay.
>>> ...
>>>
>>> > I think mysql server can do the same thing as InnoDB do, if user
>>> doesn't
>>> > define the primary key, mysql can add the primary key automatically.
>>> >
>>> > How do you think?
>>>
>>> Well, that's doable. A much easier solution would be to require a user
>>> to create a primary key. It's a one-line change:
>>>
>>> -  Table_flags ha_table_flags() const { return cached_table_flags; }
>>> +  Table_flags ha_table_flags() const { return cached_table_flags |
>>> HA_REQUIRE_PRIMARY_KEY; }
>>>
>>> But what you suggest is possible too, I believe.
>>>
>>> Regards,
>>> Sergei
>>>
>>
>>
>>
>> --
>> Senior Database Engineer @ Taobao.com
>>
>> Mobile Phone: +86 18658156856 (Hangzhou)
>> Gtalk: penglixun(at)gmail.com
>> Twitter: http://www.twitter.com/plinux
>> Blog: http://www.penglixun.com
>>
>> _______________________________________________
>> Mailing list: https://launchpad.net/~maria-developers
>> Post to     : maria-developers@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~maria-developers
>> More help   : https://help.launchpad.net/ListHelp
>>
>>
>


-- 
Senior Database Engineer @ Taobao.com

Mobile Phone: +86 18658156856 (Hangzhou)
Gtalk: penglixun(at)gmail.com
Twitter: http://www.twitter.com/plinux
Blog: http://www.penglixun.com

Follow ups

References