Re: [Maria-developers] How to change max key length for MyISAM?

[Reindl Harald]

Am 15.01.21 um 09:01 schrieb Luuk:

On 14-1-2021 21:45, Reindl Harald wrote:

I'm trying to install prestashop-1.7.7.1 on MyISAM


and why does that piece of software don't work with sensible defaults 
and force you too pacth source code and recompile your database server 
in a way nobody but you is running?


this is asking for troubles and until someone can prove a *real* 
benefit i call it bullshit and won't install "prestashop-1.7.7.1 on 
MyISAM"


the other possibilty is that it's designed to work with innoDB and 
*not* MyISAM which means: just don't enforce MyISAM, key sizes are 
your smallest problems in reality



A: It is a MySQL issue. 
http://stackoverflow.com/questions/8746207/1071-specified-key-was-too-long-max-key-length-is-1000-bytes 


as expected DO NOT INSTALL IT ON MYISAM jesus christ
ENGINE=InnoDB

and no it's not a mysql issue when a software is written in a way than 
even the installer dies


CREATE TABLE IF NOT EXISTS `pds_core_menu_items` (
  `menu_id` varchar(32) NOT NULL,
  `parent_menu_id` int(32) unsigned DEFAULT NULL,
  `menu_name` varchar(255) DEFAULT NULL,
  `menu_link` varchar(255) DEFAULT NULL,
  `plugin` varchar(255) DEFAULT NULL,
  `menu_type` int(1) DEFAULT NULL,
  `extend` varchar(255) DEFAULT NULL,
  `new_window` int(1) DEFAULT NULL,
  `rank` int(100) DEFAULT NULL,
  `hide` int(1) DEFAULT NULL,
  `template_id` int(32) unsigned DEFAULT NULL,
  `alias` varchar(255) DEFAULT NULL,
  `layout` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`menu_id`),
  KEY `index` (`parent_menu_id`,`menu_link`,`plugin`,`alias`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] How to change max key length for MyISAM?

[Reindl Harald]

Am 14.01.21 um 21:39 schrieb Witold Filipczyk:

Dnia Thu, Jan 14, 2021 at 08:52:14PM +0100, Sergei Golubchik napisał(a):

Hi, Witold!

On Jan 14, Witold Filipczyk wrote:


Why do you want to increase the max key length?


I like mysqlhotcopy as backup tool, but new soft requires big key.


Yes, that's what I was asking. What does it require, can you show an
example of a table definition?

I mean, a non-unique key doesn't have to be that long. If it's smaller,
it'll still help to narrow the search to all rows with the same
1000-byte prefix, and then just compare them all with the exact 3K
value. So 1K-key will still help even if values are 3K (except when all
your values have exactly the same 1K prefix, but it's not a very
realistic scenario). But with 1K keys you'll fit 3 times more keys per
index page, you'll have less disk IO, and less tree levels to search.

You would really need 3K, if you have 3K UNIQUE key, for UNIQUE 3k
contraints. Using 1K keys would change the semantics, values won't
necessarily be unique anymore. But since 10.4 MariaDB supports unique
constraints of arbitrary length. So, again, you should not need to
increase max key length for that (except that you're on 10.3 now).


I'm trying to install prestashop-1.7.7.1 on MyISAM


and why does that piece of software don't work with sensible defaults 
and force you too pacth source code and recompile your database server 
in a way nobody but you is running?


this is asking for troubles and until someone can prove a *real* benefit 
i call it bullshit and won#t install "prestashop-1.7.7.1 on MyISAM"


the other possibilty is that it's designed to work with innoDB and *not* 
MyISAM which means: just don't enforce MyISAM, key sizes are your 
smallest problems in reality


___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] [External] Binlog handling of faulty DDL query

[Reindl Harald]

Am 02.09.18 um 23:04 schrieb Pavel Ivanov:
> Note that to make CREATE USER atomic mysql.user table will need to be
> made to use InnoDB storage engine rather than MyISAM. And that would
> involve pretty significant amount of changes throughout the code base.
> 
> And yes, MySQL switched mysql.user table (as well as other mysql.*
> tables) to use InnoDB storage engine only in 8.0. In 5.7 and earlier
> it was still MyISAM.

accept or refuse the whole query and so log it complete or not at all is
a different beast then make it atomic

please don't consider make InnoDB mandatory, here are small
mariadb-servers where only loading innodb would multiply the whole footprint

> On Sun, Sep 2, 2018 at 5:59 AM Simon Mudd  wrote:
>>
>> Hi,
>>
>> On 2 Sep 2018, at 11:27, Sachin Setiya  wrote:
>>
>> Hi Everyone!
>>
>> Suppose this case
>>
>> CREATE USER
>>  user1@localhost IDENTIFIED BY 'BsG9#9.cem#!85',
>>  user2@localhost IDENTIFIED BY 'x';
>>
>> user2 has too short passowrd which will give error (if we use security 
>> plugin)
>>
>> IN the case of *mariadb* we will create user1 and wont create user2
>> In the case of *mysql (8.0)* we will return error and we will not
>> create any user
>>
>> Our approach create a lots of problem for binary log and
>> replication(MDEV-14716, MDEV-16728)
>> Because in Query_log_event::write() we write the whole query not the
>> parts of it.
>> So we have 2 options to solve this either write modified query into
>> binlog or do what mysql does.
>>
>> So what you think , which approach we should use ? Or there is another
>> better way ?
>>
>>
>> I’m a user of MySQL / MariaDB and I hadn’t been aware you can use CREATE 
>> USER on more than one user. I guess I need to study the manual more …
>>
>> Having said that you mention MySQL 8.0 and say the behaviour there is 
>> atomic. Is it also atomic in 5.7 and earlier? It would be good to know.
>> [ I then go to check …]
>>
>> Also do we want the statement to be atomic? I don’t know if this comes under 
>> SQL specifications or not. If it does follow that.
>>
>> If not as a user that administers mysql account information I’d prefer the 
>> statement to be atomic. As you say it causes less confusion
>> and it either works or does not.  That’s easier to handle when you automatic 
>> account creation and maintenance which some of us do.
>>
>> So looking at https://dev.mysql.com/doc/refman/8.0/en/create-user.html it 
>> seems the atomic part is explicitly mentioned.
>> https://dev.mysql.com/doc/refman/5.7/en/create-user.html does not give this 
>> guarantee so behaviour more likely roughly matches current MariaDB behaviour.
>>
>> In any account management I’ve used I’ve always handled users one by one and 
>> thus atomic behaviour is implicitly guaranteed.
>> However, given it is possible to create more than one user at once I’d 
>> certainly prefer to see the behaviour being atomic as otherwise you’re going 
>> to be
>> forced to check each of the accounts on the server after attempting to 
>> create them to see if they are there and configured as you expect. That
>> task is more complex than handing a more simple error.
>>
>> While this seems to be a dirty word now I would like to see as much 
>> compatibility in MariaDB vs [Oracle] MySQL as possible and where this is 
>> reasonable.
>> Behaviour in MySQL has changed and while it doesn’t have to change in 
>> MariaDB the change in MySQL seems “reasonable”. I think it would be good
>> for MariaDB to follow that for the reasons stated above, if possible.
>>
>> Based on expected behaviour what gets written to the binlog I think becomes 
>> easier. You send the statement as given as you only send it if it has 
>> succeeded.

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Deprecate INT(M) in SHOW?

[Reindl Harald]

Am 23.04.2018 um 16:17 schrieb Peter Laursen:
> https://dev.mysql.com/doc/refman/8.0/en/numeric-type-overview.html "
> /|M|/ indicates the maximum display width for integer types. The maximum
> display width is 255. Display width is unrelated to the range of values
> a type can contain"

i know that - these days - but it's not logical and expected when you
start to work with this stuff and one of many gotchas

> I just inserted "11" into an INT(5) column and was able to
> create a INT(200) column as well.
> 
> On Mon, Apr 23, 2018 at 3:59 PM, Reindl Harald <h.rei...@thelounge.net
> <mailto:h.rei...@thelounge.net>> wrote:
> 
> 
> 
> Am 23.04.2018 um 15:51 schrieb Alexander Barkov:
> > Does anybody see any problems if in 10.4 we fix all metadata statements,
> > such as:
> > 
> > - SHOW CREATE TABLE t1
> > - DESCRIBE t1
> > - SELECT * FROM INFORMATION_SCHEMA.COLUMNS
> > not to print the default length for non-ZEROFILL columns?
> yes - consumer code may rely on parse outputs
> 
> and frankly i assumed that when i define a unsigned tinyit(2) in can
> hold 0-99 and would reject -1 oder 100 in strict-mode with an error

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Deprecate INT(M) in SHOW?

[Reindl Harald]

Am 23.04.2018 um 15:51 schrieb Alexander Barkov:
> Does anybody see any problems if in 10.4 we fix all metadata statements,
> such as:
> 
> - SHOW CREATE TABLE t1
> - DESCRIBE t1
> - SELECT * FROM INFORMATION_SCHEMA.COLUMNS
> not to print the default length for non-ZEROFILL columns?
yes - consumer code may rely on parse outputs

and frankly i assumed that when i define a unsigned tinyit(2) in can
hold 0-99 and would reject -1 oder 100 in strict-mode with an error

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

[Maria-developers] 10.2.10 compiler warnings Fedora 26 / GCC7

[Reindl Harald]

maybe worth to take a look

[  8%] Building C object 
libmariadb/mariadb_config/CMakeFiles/mariadb_config.dir/mariadb_config.c.o
/home/builduser/rpmbuild/BUILD/mariadb-10.2.10/libmariadb/mariadb_config/mariadb_config.c:9:49: 
warning: backslash and newline separated by space

 #define LIBS"-L/usr/lib64/mysql/ -lmariadb "\

[ 20%] Building C object 
libmariadb/libmariadb/CMakeFiles/mariadb_obj.dir/ma_dtoa.c.o
/home/builduser/rpmbuild/BUILD/mariadb-10.2.10/libmariadb/libmariadb/ma_dtoa.c: 
In function 'dtoa':
/home/builduser/rpmbuild/BUILD/mariadb-10.2.10/libmariadb/libmariadb/ma_dtoa.c:1465:14: 
warning: this statement may fall through [-Wimplicit-fallthrough=]

 leftright= 0;
 ~^~~
/home/builduser/rpmbuild/BUILD/mariadb-10.2.10/libmariadb/libmariadb/ma_dtoa.c:1467:3: 
note: here

   case 4:
   ^~~~
/home/builduser/rpmbuild/BUILD/mariadb-10.2.10/libmariadb/libmariadb/ma_dtoa.c:1473:14: 
warning: this statement may fall through [-Wimplicit-fallthrough=]

 leftright= 0;
 ~^~~
/home/builduser/rpmbuild/BUILD/mariadb-10.2.10/libmariadb/libmariadb/ma_dtoa.c:1475:3: 
note: here

   case 5:
   ^~~~
At top level:
/home/builduser/rpmbuild/BUILD/mariadb-10.2.10/libmariadb/libmariadb/ma_dtoa.c:1161:21: 
warning: 'tinytens' defined but not used [-Wunused-const-variable=]

 static const double tinytens[]=
 ^~~~

[ 24%] Building C object strings/CMakeFiles/strings.dir/dtoa.c.o
/home/builduser/rpmbuild/BUILD/mariadb-10.2.10/libmariadb/libmariadb/mariadb_dyncol.c: 
In function 'mariadb_dyncol_val_double':
/home/builduser/rpmbuild/BUILD/mariadb-10.2.10/libmariadb/libmariadb/mariadb_dyncol.c:4049:15: 
warning: argument 1 null where non-null expected [-Wnonnull]

 *dbl= strtod(str, );
   ^
In file included from 
/home/builduser/rpmbuild/BUILD/mariadb-10.2.10/libmariadb/include/ma_global.h:168:0,
 from 
/home/builduser/rpmbuild/BUILD/mariadb-10.2.10/libmariadb/libmariadb/mariadb_dyncol.c:64:
/usr/include/stdlib.h:126:15: note: in a call to function 'strtod' 
declared here

 extern double strtod (const char *__restrict __nptr,

[ 33%] Building C object mysys/CMakeFiles/mysys.dir/my_malloc.c.o
/home/builduser/rpmbuild/BUILD/mariadb-10.2.10/mysys/my_lib.c: In 
function 'my_dir':
/home/builduser/rpmbuild/BUILD/mariadb-10.2.10/mysys/my_lib.c:144:3: 
warning: 'readdir_r' is deprecated [-Wdeprecated-declarations]

   while (!(READDIR(dirp,(struct dirent*) dirent_tmp,dp)))
   ^
In file included from /usr/include/features.h:410:0,
 from /usr/include/pthread.h:21,
 from 
/home/builduser/rpmbuild/BUILD/mariadb-10.2.10/include/my_global.h:278,
 from 
/home/builduser/rpmbuild/BUILD/mariadb-10.2.10/mysys/mysys_priv.h:19,
 from 
/home/builduser/rpmbuild/BUILD/mariadb-10.2.10/mysys/my_lib.c:19:

/usr/include/dirent.h:189:12: note: declared here
 extern int __REDIRECT (readdir_r,

[ 68%] Building C object storage/maria/CMakeFiles/aria.dir/ma_check.c.o
/home/builduser/rpmbuild/BUILD/mariadb-10.2.10/storage/myisam/myisamchk.c: 
In function 'myisamchk':
/home/builduser/rpmbuild/BUILD/mariadb-10.2.10/storage/myisam/myisamchk.c:1411:22: 
warning: '%ld' directive writing between 1 and 10 bytes into a region of 
size 8 [-Wformat-overflow=]

sprintf(null_pos,"%ld",(long) keyseg->null_pos+1);
  ^~~
/home/builduser/rpmbuild/BUILD/mariadb-10.2.10/storage/myisam/myisamchk.c:1411:21: 
note: directive argument in the range [1, 4294967296]

sprintf(null_pos,"%ld",(long) keyseg->null_pos+1);
 ^
In file included from /usr/include/stdio.h:939:0,
 from 
/home/builduser/rpmbuild/BUILD/mariadb-10.2.10/include/my_global.h:322,
 from 
/home/builduser/rpmbuild/BUILD/mariadb-10.2.10/include/my_base.h:23,
 from 
/home/builduser/rpmbuild/BUILD/mariadb-10.2.10/include/myisam.h:26,
 from 
/home/builduser/rpmbuild/BUILD/mariadb-10.2.10/storage/myisam/myisamdef.h:20,
 from 
/home/builduser/rpmbuild/BUILD/mariadb-10.2.10/storage/myisam/fulltext.h:21,
 from 
/home/builduser/rpmbuild/BUILD/mariadb-10.2.10/storage/myisam/myisamchk.c:18:
/usr/include/bits/stdio2.h:33:10: note: '__builtin___sprintf_chk' output 
between 2 and 11 bytes into a destination of size 8

   return __builtin___sprintf_chk (__s, __USE_FORTIFY_LEVEL - 1,
  ^~
   __bos (__s), __fmt, __va_arg_pack ());
   ~

[ 68%] Building C object storage/maria/CMakeFiles/aria.dir/ma_preload.c.o
/home/builduser/rpmbuild/BUILD/mariadb-10.2.10/storage/myisam/mi_test3.c: 
In function 'test_write':
/home/builduser/rpmbuild/BUILD/mariadb-10.2.10/storage/myisam/mi_test3.c:367:30: 
warning: '%7ld' directive writing between 7 and 11 bytes into a region 
of size 8 [-Wformat-overflow=]

   sprintf((char*) 

[Maria-developers] GCC7 warnings

[Reindl Harald]

hard to say how serious they are but maybe a good idea to take a look

/home/builduser/rpmbuild/BUILD/mariadb-10.2.8/libmariadb/mariadb_config/mariadb_config.c:9:49: 
warning: backslash and newline separated by space

 #define LIBS"-L/usr/lib64/mysql/ -lmariadb "\

/home/builduser/rpmbuild/BUILD/mariadb-10.2.8/libmariadb/libmariadb/mariadb_lib.c: 
In function 'mariadb_get_infov':
/home/builduser/rpmbuild/BUILD/mariadb-10.2.8/libmariadb/libmariadb/mariadb_lib.c:3872:30: 
warning: this statement may fall through [-Wimplicit-fallthrough=]

   *((unsigned long *)arg)= mysql->client_flag;
   ~~~^~~~
/home/builduser/rpmbuild/BUILD/mariadb-10.2.8/libmariadb/libmariadb/mariadb_lib.c:3875:3: 
note: here

   default:
   ^~~

In file included from 
/home/builduser/rpmbuild/BUILD/mariadb-10.2.8/plugin/handler_socket/libhsclient/hstcpcli.cpp:13:0:
/home/builduser/rpmbuild/BUILD/mariadb-10.2.8/plugin/handler_socket/libhsclient/hstcpcli.hpp:33:14: 
warning: 'template class std::auto_ptr' is deprecated 
[-Wdeprecated-declarations]

 typedef std::auto_ptr hstcpcli_ptr;
  ^~~~
In file included from /usr/include/c++/7/memory:80:0,
 from 
/home/builduser/rpmbuild/BUILD/mariadb-10.2.8/plugin/handler_socket/libhsclient/hstcpcli.hpp:15,
 from 
/home/builduser/rpmbuild/BUILD/mariadb-10.2.8/plugin/handler_socket/libhsclient/hstcpcli.cpp:13:

/usr/include/c++/7/bits/unique_ptr.h:51:28: note: declared here
   template class auto_ptr;

/home/builduser/rpmbuild/BUILD/mariadb-10.2.8/libmariadb/libmariadb/ma_dtoa.c: 
In function 'dtoa':
/home/builduser/rpmbuild/BUILD/mariadb-10.2.8/libmariadb/libmariadb/ma_dtoa.c:1465:14: 
warning: this statement may fall through [-Wimplicit-fallthrough=]

 leftright= 0;
 ~^~~
/home/builduser/rpmbuild/BUILD/mariadb-10.2.8/libmariadb/libmariadb/ma_dtoa.c:1467:3: 
note: here

   case 4:
   ^~~~
/home/builduser/rpmbuild/BUILD/mariadb-10.2.8/libmariadb/libmariadb/ma_dtoa.c:1473:14: 
warning: this statement may fall through [-Wimplicit-fallthrough=]

 leftright= 0;
 ~^~~
/home/builduser/rpmbuild/BUILD/mariadb-10.2.8/libmariadb/libmariadb/ma_dtoa.c:1475:3: 
note: here

   case 5:
   ^~~~
At top level:
/home/builduser/rpmbuild/BUILD/mariadb-10.2.8/libmariadb/libmariadb/ma_dtoa.c:1161:21: 
warning: 'tinytens' defined but not used [-Wunused-const-variable=]

 static const double tinytens[]=
 ^~~~

/home/builduser/rpmbuild/BUILD/mariadb-10.2.8/libmariadb/libmariadb/mariadb_dyncol.c: 
In function 'mariadb_dyncol_val_double':
/home/builduser/rpmbuild/BUILD/mariadb-10.2.8/libmariadb/libmariadb/mariadb_dyncol.c:4044:7: 
warning: this statement may fall through [-Wimplicit-fallthrough=]

   {
   ^
/home/builduser/rpmbuild/BUILD/mariadb-10.2.8/libmariadb/libmariadb/mariadb_dyncol.c:4060:5: 
note: here

 case DYN_COL_DATETIME:
 ^~~~

/home/builduser/rpmbuild/BUILD/mariadb-10.2.8/libmariadb/libmariadb/mariadb_dyncol.c:4050:15: 
warning: argument 1 null where non-null expected [-Wnonnull]

 *dbl= strtod(str, );
   ^
In file included from 
/home/builduser/rpmbuild/BUILD/mariadb-10.2.8/libmariadb/include/ma_global.h:168:0,
 from 
/home/builduser/rpmbuild/BUILD/mariadb-10.2.8/libmariadb/libmariadb/mariadb_dyncol.c:64:
/usr/include/stdlib.h:126:15: note: in a call to function 'strtod' 
declared here

 extern double strtod (const char *__restrict __nptr,
   ^~

/home/builduser/rpmbuild/BUILD/mariadb-10.2.8/libmariadb/unittest/libmariadb/t_aurora.c: 
In function 'test_reconnect':
/home/builduser/rpmbuild/BUILD/mariadb-10.2.8/libmariadb/unittest/libmariadb/t_aurora.c:100:3: 
warning: ignoring return value of 'system', declared with attribute 
warn_unused_result [-Wunused-result]
   system("/usr/local/aws/bin/aws rds failover-db-cluster 
--db-cluster-identifier instance-1-cluster");


^~~

/home/builduser/rpmbuild/BUILD/mariadb-10.2.8/mysys/my_lib.c: In 
function 'my_dir':
/home/builduser/rpmbuild/BUILD/mariadb-10.2.8/mysys/my_lib.c:144:3: 
warning: 'readdir_r' is deprecated [-Wdeprecated-declarations]

   while (!(READDIR(dirp,(struct dirent*) dirent_tmp,dp)))
   ^
In file included from /usr/include/features.h:410:0,
 from /usr/include/pthread.h:21,
 from 
/home/builduser/rpmbuild/BUILD/mariadb-10.2.8/include/my_global.h:278,
 from 
/home/builduser/rpmbuild/BUILD/mariadb-10.2.8/mysys/mysys_priv.h:19,
 from 
/home/builduser/rpmbuild/BUILD/mariadb-10.2.8/mysys/my_lib.c:19:

/usr/include/dirent.h:189:12: note: declared here
 extern int __REDIRECT (readdir_r,

/home/builduser/rpmbuild/BUILD/mariadb-10.2.8/storage/myisam/myisamchk.c: 
In function 'myisamchk':

Re: [Maria-developers] MariaDB 10.1.15

[Reindl Harald]

Am 04.07.2016 um 14:01 schrieb Antonio Fernandes:

What happened to MariaDB 10.1.15?

I've upgraded my slave server, waited a few days (like I always do before
master) and now, no MariaDB 10.1.15 :)

Ohh, btw, 10.1.14 changelog
(https://mariadb.com/kb/en/mariadb/mariadb-10114-changelog/) still displays
that 10.1.15 is the latest


when you follow devel/users list it has a crash-bug regression, see archives



signature.asc
Description: OpenPGP digital signature
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] nariadb 10.1.13 fails with openssl on gentoo

[Reindl Harald]

Am 17.04.2016 um 13:19 schrieb Benny Pedersen:

On 2016-04-17 10:33, Reindl Harald wrote:

dunno why you send that to the *developers* list?
"maria-disc...@lists.launchpad.net" is for pure user questions
and we both are just users in most cases



[root@buildserver:~]$ openssl s_client -showcerts -connect 127.0.0.1:3306
CONNECTED(0003)
139800112973696:error:140770FC:SSL
routines:SSL23_GET_SERVER_HELLO:unknown protocol:s23_clnt.c:794:


hope developpers will check this


it don't matter - SSL2/SSL3 is gone - complain at openssl why it is 
tried at all




signature.asc
Description: OpenPGP digital signature
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] nariadb 10.1.13 fails with openssl on gentoo

[Reindl Harald]

dunno why you send that to the *developers* list?
"maria-disc...@lists.launchpad.net" is for pure user questions
and we both are just users in most cases

Am 17.04.2016 um 01:39 schrieb Benny Pedersen:

if openssl works for othres i like to know a working my.cnf to make it
work, i have added my ssql same way as used in dovecot / postfix, no ssl
error in mysql, but openssl s_client -showcerts -connect 127.0.0.1:3306
says ssl23 fails, at best i see ssl3 tlsv1 fails, output is
CONNECTED(0003)


nonsense - it works likely independent what opeensl says here - why 
don't you just try first a mysql client and look at the connection vars?


[root@buildserver:~]$ autotest.php mysql_ssl
/Volumes/dune/buildserver/autotest/parts/mysql_ssl.php
OK: mysql-over-ssl - DHE-RSA-AES128-SHA / TLSv1

[root@buildserver:~]$ openssl s_client -showcerts -connect 127.0.0.1:3306
CONNECTED(0003)
139800112973696:error:140770FC:SSL 
routines:SSL23_GET_SERVER_HELLO:unknown protocol:s23_clnt.c:794:

---
no peer certificate available
---
No client certificate CA names sent
---
SSL handshake has read 7 bytes and written 201 bytes
---
New, (NONE), Cipher is (NONE)
Secure Renegotiation IS NOT supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
SSL-Session:
Protocol  : TLSv1.2
Cipher: 
Session-ID:
Session-ID-ctx:
Master-Key:
Key-Arg   : None
Krb5 Principal: None
PSK identity: None
PSK identity hint: None
Start Time: 1460881738
Timeout   : 300 (sec)
Verify return code: 0 (ok)


no peer certificate available
---
No client certificate CA names sent
---
SSL handshake has read 7 bytes and written 308 bytes
---
New, (NONE), Cipher is (NONE)
Secure Renegotiation IS NOT supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
SSL-Session:
 Protocol  : TLSv1.2
 Cipher: 
 Session-ID:
 Session-ID-ctx:
 Master-Key:
 Key-Arg   : None
 PSK identity: None
 PSK identity hint: None
 SRP username: None
 Start Time: 1460845475
 Timeout   : 300 (sec)
 Verify return code: 0 (ok)
---

how to debug it more from here ?


there is nothing to debug




signature.asc
Description: OpenPGP digital signature
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Log warnings when upgrading from mysql 5.6 -> mariadb 10.0

[Reindl Harald]

Am 11.01.2016 um 09:39 schrieb Otto Kekäläinen:

2016-01-11 9:25 GMT+02:00 Jan Lindström :

These warnings are printed because some of the system table definitions have
been changed, you should run mysql_upgrade and restart should be again more
silent.


mysql_upgrade is always run automatically and the next restart was silent

I just wanted to check to be sure that these indeed can be ignored.
 From user point of view it would be nicer if the upgrade ran
automatically before any of the mysqld starts so that there would not
be any "alarming" errors in the log, but that is very minor


chicken/egg problem

mysql_upgrade can't run before the daemon is started



signature.asc
Description: OpenPGP digital signature
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] unsubscribe

[Reindl Harald]

so what - read the list footer - no user can unsubscribe for you

Am 14.06.2015 um 21:48 schrieb Abdul Hakeem:

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp




signature.asc
Description: OpenPGP digital signature
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] [Commits] 97037da: Replace static usage of AES_CTR with current encryption algorithm.

[Reindl Harald]

Hi

Am 17.03.2015 um 19:18 schrieb Jeremy Cole:

But the whole encryption_algorithm stuff seems not well thought out in
any case


there was a recent thread on the orcle list today
it's sad that ECHDE / AES-GCM / SHA256 are not working at all

currently only DHE-RSA-AES128-SHA / DHE-RSA-AES256-SHA are working with 
forward secrecy at all while CBC instead GCM should be avoided beause 
security as well as performance on modern CPU's


ssl-cipher = 
ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-SHA256:ECDHE-RSA-AES128-SHA:DHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES128-SHA256:DHE-RSA-AES128-SHA:DHE-RSA-AES256-SHA:RSA-AES256-SHA




signature.asc
Description: OpenPGP digital signature
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Any plans about starting to use /var/lib/mariadb for data instead of /var/lib/mysql?

[Reindl Harald]

way easier - don't purge it at all - there is a reason why
the package manager on Redhat based systems *never* removes
subfolders below /var/lib

versioned datadirs are a hell

* it's the distributions responsiblity not allow *install*
  different mysql favours at the same time (conflicts in RPM)

* it's the admins responsibility not down- and side-grade
  without thinking and making backups

breaking upgrades because boder cases is a bad idea

we are working here with MariaDB 5.5.38 on datadirs originally
created with MySQL 3.x around 13 years ago on MacOSX and they
are just upgraded over years with mysql_upgrade and moved
between arcihtectures and operating systems

*only* if that would not be possible in a future version it
would need action and fixes - down and side-grades are always
critical and should always be only done once and in one
direction

Am 24.07.2014 14:23, schrieb Jean Weisbuch:
 I dont think that the option to have one datadir per major version (eg: 
 /var/lib/mysql-5.6) is a good idea, it will
 add confusion and might break scripts or symlinks on major version upgrade 
 for example while only one major version
 should exist at once on the system (at least if the current way of handling 
 mysql versions is done on Debian).
 
 As for the switching to a different datadir, it would also imply to use a 
 different config directory as well such
 as /etc/mariadb as the datadir is actually set on /etc/mysql/my.cnf.
 
 The whole debate depends on the possibility to install mariadb and mysql on 
 the same system, if its not the path
 chosen, i would prefer to keep the /var/lib/mysql datadir as the default 
 datadir and add checks on
 mysql/mariadb-server packages purge not to remove the datadir if any of the 
 fork packages is still installed and
 throw a warning message about the possible incompatibility and upgrade 
 methods on migration.
 
 Creating a script to simplify the migration for non-drop-in forks (eg: 
 maria10 and mysql5.6) would also be a
 possibility, it would check if any of the tables on the server has a table 
 engine that is not present on the other
 (eg: aria on mysql) or specific row/columns formats and would dump all tables 
 and grants and import it on the new
 installation.
 
 On 07/24/2014 02:04 PM, Otto Kekäläinen wrote:
 Hello!

 Does the MariaDB project have any plans about switching the data dir
 to /var/lib/mariadb in some release?

 In Debian there has been discussion about separating the
 binary-incompatible MySQL forks into separate /var/lib/fork to ease
 maintenance and secure data so that purging some packages will not
 accidentally delete data that another package uses.

 The thread is named Discussion on handling 5.6 related matters and
 available at 
 http://lists.alioth.debian.org/pipermail/pkg-mysql-maint/2014-July/thread.html

 I'd appreciate any guidelines here on what the upstream plans are.

 The Debian MySQL/MariaDB packaging team willl have an IRC meeting
 tomorrow at 16:00 UTC and I need to express my opinion on the mattes
 and what kind of changes I'm willing to do to the MariaDB package.
 Obviously I'd like to follow upstream as much as possible to keep the
 delta between Debian and upstream small and thus the maintenance
 burden reasonable



signature.asc
Description: OpenPGP digital signature
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Review of MDEV-5730: enhance security using special compilation options

[Reindl Harald]

please make sure that explicit set options are not overriden

in case of GCC the last option wins

so if you set -fstack-protector by add it to the flags you disable
-fstack-protector-strong from below which depends on the GCC version
and is now default in Fedora as example

export CFLAGS=%{optflags} -O3 -fstack-protector-strong 
--param=ssp-buffer-size=4 -fPIC -fomit-frame-pointer
-fno-exceptions -ffixed-ebp -fwrapv -fno-strict-aliasing -D_GNU_SOURCE 
-D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE
export CXXFLAGS=$CFLAGS
export FFLAGS=$CFLAGS
export FCFLAGS=$CFLAGS
export LDFLAGS=-Wl,-z,now -Wl,-z,relro,-z,noexecstack -pie
export SH_LDFLAGS=$LDFLAGS

Am 24.06.2014 10:02, schrieb Kristian Nielsen:
 commit feab48657528b9bb40fb035d51bee28d93710c1e
 Author: Sergei Golubchik s...@mariadb.org
 Date:   Mon Jun 16 21:39:09 2014 +0200

 MDEV-5730 enhance security using special compilation options
 
 -Wl,-z,relro,-z,now
 -pie
 -fstack-protector --param=ssp-buffer-size=4
 -D_FORTIFY_SOURCE=2
 
 The patch is ok to push with a clarified comment as requested below.
 
 I have checked the compiler/linker options added and commented some on them
 below.
 
 diff --git a/CMakeLists.txt b/CMakeLists.txt
 index a24f000..5b0d348 100644
 --- a/CMakeLists.txt
 +++ b/CMakeLists.txt
 @@ -201,6 +201,20 @@ IF (WITH_ASAN)
ENDIF()
  ENDIF()
  
 +OPTION(SECURITY_HARDENED Use security-enhancing compiler features (stack 
 protector, relro, etc) ON)
 
 So these are on by default.
 This means that this patch will cause some performance impact.
 
 So having them on by default is a compromise between performance and security.
 You should probably include in the commit comment a discussion of why the
 security viewpoint was prefered over the performance viewpoint in this case
 (or a comment in the cmake file if you prefer).
 
 The proper way to handle a patch like this would be to measure the performance
 impact, eg. for single-threaded performance with some various simple sysbench
 loads. However, it is quite hard to do such measurements, as you need a setup
 that has low enough noise in results to detect sub-percentage differences of
 performance. I have had some success by locking processes to a single core and
 using `perf` measurements of cycles spent (rather than wall-clock time), but
 even so the results can be tricky to interpret. So it may be too much work for
 a small patch like this.
 
 (And even if the performance improvement can be measured, how can we measure
 the security impact to weight them against one another?)
 
 My personal opinion is that I do not buy into this security mindset much. I
 do not think these options improve security significantly in practise for most
 users. So while the performance impact is probably also not very significant,
 why accept it at all (by default)? But the opposite viewpoint exists and is
 valid as well. So I think this patch is ok if you add a discussion of why it
 was chosen to do it this way.
 
 (Even if the reason is Most distros do it - at least then it is documented
 what the reason is).
 
 Also, the option should mention the possible performance impact. Suggestion:
 
 Use compiler options (stack protector, relro, etc) that may slightly enhance
 security of the resulting binary, at the possible cost of some small
 performance impact.
 
 +IF(SECURITY_HARDENED)
 +  # security-enhancing flags
 +  MY_CHECK_AND_SET_COMPILER_FLAG(-pie -fPIC)
 
 -fPIC can have a significant impact on performance on some platforms. For
 example on 32-bit x86, it requires extra instructions at the head of every
 function, and it makes one less register available for code generation on a
 CPU already starved for registers. On 64-bit x86, the impact is much smaller,
 as the architecture was enhanced to better support -fPIC.
 
 However, I seem to recall that the server is anyway built with -fPIC, for
 libmysqld or something like that? In which case this has no extra impact.
 
 +  MY_CHECK_AND_SET_COMPILER_FLAG(-Wl,-z,relro,-z,now)
 
 This option seems to cause all relocations to happen at load time, rather than
 lazily on-demand. The impact should be higher memory usage and some extra CPU
 time for these relocations, many of which may never be needed.
 
 This option will have the biggest effect on the client programs probably, as
 server startup is already fairly heavy. Probably even for client programs the
 added cost will be not very significant, as even the client programs are
 usually called to do significant processing (eg. connect to a database and so
 on).
 
 I guess these options would mostly hurt small utility programs for shell
 scripts, like `mv` or so.
 
 +  MY_CHECK_AND_SET_COMPILER_FLAG(-fstack-protector 
 --param=ssp-buffer-size=4)
 
 This also adds extra code and memory loads to each function, so it will have
 some impact to performance. It is probably the most significant impact of all
 the added options, though without detailed analysis this is only guesswork, of
 course.
 

Re: [Maria-developers] PIC build options and MariaDB

[Reindl Harald]

Am 02.04.2014 17:10, schrieb Otto Kekäläinen:
 I noticed that the MySQL packages in Debian have PIC stuff in their
 alternative build arguments, see
 http://anonscm.debian.org/gitweb/?p=pkg-mysql/mysql-5.6.git;a=blob;f=debian/rules
 http://anonscm.debian.org/gitweb/?p=pkg-mysql/mysql-5.5.git;a=blob;f=debian/rules
 
 Anybody here who understands deeply the build arguments who could say
 if having these PIC additions is desirable?

surely because you want the result below these days
http://tk-blog.blogspot.co.at/2009/02/relro-not-so-well-known-memory.html

* PIC
* Full RELRO

[root@rh:~]$ /usr/bin/hardening-check /usr/libexec/mysqld
/usr/libexec/mysqld:
 Position Independent Executable: yes
 Stack protected: yes
 Fortify Source functions: yes (some protected functions found)
 Read-only relocations: yes
 Immediate binding: yes



signature.asc
Description: OpenPGP digital signature
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Problems installing mariadb published debian packages when using tmpfs for /tmp

[Reindl Harald]

O_DIRECT can't and never ever will work with tmpfs
there is no direct IO on tmpfs

that's why the default /tmp on tmpfs of more and more
linux distributions is dumb, dangerous and wrong in
more then one context



signature.asc
Description: OpenPGP digital signature
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] MariaDB 5.5.35

[Reindl Harald]

Am 04.02.2014 10:01, schrieb Jean Weisbuch:
 Using a tmpfs directory as the tmpdir has its interest

especially if it leads to swap or is not large enough for
a temp-table for a random operation leading to errors

keep in mind that innodb_buffer_pool and other processes
are fighting for the same RAM

 changing it only because of warnings issued for temporaries
 InnoDB tables doesn't seems to be a solution

it's the solution for exactly the mentioned problem
the other one is not using O_DIRECT
O_DIRECT don't work on tmpfs by definition

 On 03/02/2014 19:09, Reindl Harald wrote:


 Am 03.02.2014 18:08, schrieb ImperialNetwork:
 I started to notice this on my error log since i upgraded from 5.5.34 to 
 5.5.35:

 140203  5:26:25  InnoDB: Failed to set O_DIRECT on file 
 /dev/shm/#sql7e8f6_1a94bc_10.ibd: CREATE: Invalid argument,
 continuing anyway
 140203  5:26:25  InnoDB: O_DIRECT is known to result in 'Invalid argument' 
 on Linux on tmpfs, see MySQL Bug#26662

 I never seen this error/warning until 5.5.35. Should this be normal?

 output of /usr/bin/df -hT would be a good idea

 if you heave /tmp as tmpfs like more and more linux distributions
 set tmpdir in my.cnf somewhere else, O_DIRECT can not work on
 tmpfs by definition and that there was before no warning don't
 mean that it ever worked before

 there where many warnings in the past 6 months about config options
 which are deprecated or not work as someone may think

 the difference is works not silent or not silent in most cases



signature.asc
Description: OpenPGP digital signature
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] MariaDB 5.5.35

[Reindl Harald]

Am 03.02.2014 18:08, schrieb ImperialNetwork:
 I started to notice this on my error log since i upgraded from 5.5.34 to 
 5.5.35:
 
 140203  5:26:25  InnoDB: Failed to set O_DIRECT on file 
 /dev/shm/#sql7e8f6_1a94bc_10.ibd: CREATE: Invalid argument,
 continuing anyway
 140203  5:26:25  InnoDB: O_DIRECT is known to result in 'Invalid argument' on 
 Linux on tmpfs, see MySQL Bug#26662
 
 I never seen this error/warning until 5.5.35. Should this be normal?

output of /usr/bin/df -hT would be a good idea

if you heave /tmp as tmpfs like more and more linux distributions
set tmpdir in my.cnf somewhere else, O_DIRECT can not work on
tmpfs by definition and that there was before no warning don't
mean that it ever worked before

there where many warnings in the past 6 months about config options
which are deprecated or not work as someone may think

the difference is works not silent or not silent in most cases



signature.asc
Description: OpenPGP digital signature
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Why local modifications to PCRE library?

[Reindl Harald]

Am 16.11.2013 19:58, schrieb Alexander Barkov:
 Reindl,
 
 On 11/16/2013 10:46 PM, Reindl Harald wrote:

 Am 16.11.2013 19:39, schrieb Alexander Barkov:
 I'm not sure why bundling PCRE with some our fixes should
 be confusing. We bundle many libraries with our own fixes.
 Before the 10.0.5 release we've bundled the Henry Spenser's regex library 
 with our own modifications for many many
 years.
 No one was ever confused about that :)
 So why do you think it's confusing?

 it violates the concept of shared libraries and is *highly* disapproved by 
 most
 Linux distributions like Fedora which are doing a hard work downstream to
 unbundle all this stuff which wastes a lot of energy all over the world

 https://fedoraproject.org/wiki/Packaging:No_Bundled_Libraries
 
 Thanks for the link. I have added it into the task description:
 https://mariadb.atlassian.net/browse/MDEV-5304
 
 For now, we have to wait for PCRE-8.34 release anyway.
 Adding an option to compile against the external PCRE-8.33
 is not a good idea: any MariaDB user that have a valid
 user and password would be able to crash the server
 by sending a dangerous pattern to RLIKE. We can't do that

no proble, i have my own build-environments with no rules except
it must work relieable for servers i am responsible

I thought it would be good to point out the side effects and the Fedora
page is a great ressource in that context besides it is our favourite
distribution for several reasons

there are always pros and cons

* have all bundled - you *may* be sure somehow all is fine
* at the end of the day the all is relative

there may also be removed some hacks and workarounds in
other packages which are not bundled removed because the
distribution updated the upstream library and a package
behaves not as expected because a older version

the opposite may also happen, so you not really know all
possible combinations of libraries and side effects

i have a strong feeling that use as less as possible downstream
patches in whatever project and try to fix issues in involved
upstream packages is doing a favor for all involved people and
may save a lot fo time and pain over the years and if the goal
having no downstream patches what Fedora has more or less is
reached the result means like very high quality over the stack

that said from a only user in context of MariaDB but with
some years developer experience in other areas



signature.asc
Description: OpenPGP digital signature
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Prep for MariaDB 5.5.33a

[Reindl Harald]

Am 20.09.2013 03:32, schrieb Daniel Bartholomew:
 I've begun the prep for the special 5.5.33a release.
 
 Draft changelog and release notes are here:
 
 https://mariadb.com/kb/en/mariadb-5533a-changelog/
 https://mariadb.com/kb/en/mariadb-5533a-release-notes/
 
 As always, fixes, additions, and such are welcome.
 
 I'm currently just waiting for buildbot to finish building and then
 I'll package up the release so that it can get mirrored over the next
 few hours and we can release tomorrow after some final testing of the
 finished repos. Assuming things go well, my plan is to release around
 noon US Eastern time.

many thanks for https://mariadb.atlassian.net/browse/MDEV-5026
linux-distributions all over the world will appreciate it

https://bugzilla.redhat.com/show_bug.cgi?id=1003410#c6



signature.asc
Description: OpenPGP digital signature
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] MDEV 5007 - help me! it's a old mdev closed as not a bug but don't solve the problem

[Reindl Harald]

Am 10.09.2013 16:55, schrieb Roberto Spadim:
 Hi guys!
 I posted the MDEV-5007 and it's a old problem from MDEV-3795 (the same 
 problem)
 It (MDEV-3795) was closed as a not a bug, but i think it's a bug, but i 
 didn't explained well at the first time
 
 I changed from a mysql server to a mariadb server and the result order by 
 isn't executed, i tried
 myisam,aria,innodb,memory engines and all of them return the same problem 
 (order by not executed)
 
 On the wrong query i don't see the using filesort at explain, and at the 
 right query (with right order by) it
 show using filesort
 
 Could anyone help me? or at least EXPLAIN why the order by shouldn't be 
 executed on the first query, and maybe a
 workaround using sql_optimizer variable or something like it?
 
 it's a closed software and i need to contact developer to change the query, 
 that's why i'm talking about the
 sql_optimizer variable instead of rewrite the query

this is pretty clear the following and will be fixed in 5.5.33
https://mariadb.atlassian.net/browse/MDEV-4978



signature.asc
Description: OpenPGP digital signature
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Implementing implicit primary key in mysql server

[Reindl Harald]

it makes hardly sense to add a primary key not used
in select statements this way and leads only in a
lot of problems and wasted space / performance

it is common practice to remove keys before large
bulk inserts and add the key *after* inserting the
data which would not work with the expected benefit
with your patch

Am 05.07.2013 06:08, schrieb Lixun Peng:
 Hi all,
 
 I implement a demo patch, based on 5.5.18.
 
 
 *1. CREATE TABLE*
 root@localhost : plx 11:54:46 create table test_no_pk (col1 varchar(32));
 Query OK, 0 rows affected (0.01 sec)
 
 root@localhost : plx 11:55:05 desc test_no_pk;
 +--+-+--+-+-++
 | Field| Type| Null | Key | Default | Extra  |
 +--+-+--+-+-++
 | col1 | varchar(32) | YES  | | NULL||
 | __row_id | bigint(20)  | NO   | PRI | NULL| auto_increment |
 +--+-+--+-+-++
 2 rows in set (0.01 sec)
 
 if users has not defined a PK, I will add it automatically.
 
 *2. ALTER TABLE*
 root@localhost : plx 11:55:10 alter table test_no_pk add id int, add primary 
 key(id);
 Query OK, 0 rows affected (0.00 sec)
 Records: 0  Duplicates: 0  Warnings: 0
 
 root@localhost : plx 11:57:02 desc test_no_pk;
 +---+-+--+-+-+---+
 | Field | Type| Null | Key | Default | Extra |
 +---+-+--+-+-+---+
 | col1  | varchar(32) | YES  | | NULL|   |
 | id| int(11) | NO   | PRI | 0   |   |
 +---+-+--+-+-+---+
 2 rows in set (0.01 sec)
 
 When users add a PK, I will remove implicit PK automatically.
 
 root@localhost : plx 11:57:07 alter table test_no_pk drop primary key;
 Query OK, 0 rows affected (0.00 sec)
 Records: 0  Duplicates: 0  Warnings: 0
 
 root@localhost : plx 11:57:42 desc test_no_pk;
 +--+-+--+-+-++
 | Field| Type| Null | Key | Default | Extra  |
 +--+-+--+-+-++
 | col1 | varchar(32) | YES  | | NULL||
 | id   | int(11) | NO   | | 0   ||
 | __row_id | bigint(20)  | NO   | PRI | NULL| auto_increment |
 +--+-+--+-+-++
 3 rows in set (0.00 sec)
 
 When users dropped PK, I will add it automatically.
 
 *3. INSERT VALUES*
 root@localhost : plx 11:59:22 insert into test_no_pk values('abc',2);
 ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'
 root@localhost : plx 11:59:23 insert into test_no_pk values('abc',4);
 ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'
 
 it will report duplicate, *Sergei, can you help me to find why?*
 
 *4. SELECT **
 root@localhost : plx 12:07:23 select * from test_no_pk;
 +--++
 | col1 | id |
 +--++
 | abc  |  6 |
 +--++
 1 row in set (0.00 sec)
 
 root@localhost : plx 12:07:30 select __row_id from test_no_pk;
 +--+
 | __row_id |
 +--+
 |1 |
 +--+
 1 row in set (0.00 sec)
 
 When users run SELECT *, row_id will be filter.
 
 *5. SHOW CREATE*
 
 root@localhost : plx 12:07:35 show create table test_no_pk\G
 *** 1. row ***
Table: test_no_pk
 Create Table: CREATE TABLE `test_no_pk` (
   `col1` varchar(32) DEFAULT NULL,
   `id` int(11) NOT NULL DEFAULT '0'
 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
 1 row in set (0.00 sec)
 
 row_id will be hidden.
 
 
 Thanks,
 Lixun
 
 
 
 
 On Wed, Jun 26, 2013 at 10:59 AM, Lixun Peng pengli...@gmail.com 
 mailto:pengli...@gmail.com wrote:
 
 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 jeremyc...@google.com 
 mailto:jeremyc...@google.com 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 

Re: [Maria-developers] Implementing implicit primary key in mysql server

[Reindl Harald]

Am 05.07.2013 06:32, schrieb Lixun Peng:
 Yes, for normal user is un-meaningful

it is not only un-meaningful it is harmful for people knwoing what they
are doing by massive overhead with no benefit - hence after a bulk insert
your implicit key has to be removed while as example the intented unique
key on a varchar added

 The case is our MySQL Cloud Service, so many users are using our MySQL db for 
 CMS or other programs.
 And it usually has no primary key or any unique keys, so it makes me headache.

i doubt you can solve social problems with tech

 I think you know, if binlog_format=ROW, and tables have no any unique keys, 
 what will happen.
 Now we just change binlog_forma=MIXED to avoid this problem. But our 
 middleware need ROW format, so it still makes
 me headache.
 Because our users don't want to add PK by themselves, they don't know how to 
 modify their application, they just
 download it and install in their web server.
 
 So I want to add a implicit Primary Key for each tables that have no unique 
 keys. Then we can use ROW binlog
 format, so many problems will be solved.

corner cases - this at least needs to be enabled via my.cnf and must not
affect users with well database designs and shoot them in the leg due
bulk inserts

 On Fri, Jul 5, 2013 at 12:15 PM, Reindl Harald h.rei...@thelounge.net 
 mailto:h.rei...@thelounge.net wrote:
 
 it makes hardly sense to add a primary key not used
 in select statements this way and leads only in a
 lot of problems and wasted space / performance
 
 it is common practice to remove keys before large
 bulk inserts and add the key *after* inserting the
 data which would not work with the expected benefit
 with your patch
 
 Am 05.07.2013 06:08, schrieb Lixun Peng:
  Hi all,
 
  I implement a demo patch, based on 5.5.18.
 
 
  *1. CREATE TABLE*
  root@localhost : plx 11:54:46 create table test_no_pk (col1 
 varchar(32));
  Query OK, 0 rows affected (0.01 sec)
 
  root@localhost : plx 11:55:05 desc test_no_pk;
  +--+-+--+-+-++
  | Field| Type| Null | Key | Default | Extra  |
  +--+-+--+-+-++
  | col1 | varchar(32) | YES  | | NULL||
  | __row_id | bigint(20)  | NO   | PRI | NULL| auto_increment |
  +--+-+--+-+-++
  2 rows in set (0.01 sec)
 
  if users has not defined a PK, I will add it automatically.
 
  *2. ALTER TABLE*
  root@localhost : plx 11:55:10 alter table test_no_pk add id int, add 
 primary key(id);
  Query OK, 0 rows affected (0.00 sec)
  Records: 0  Duplicates: 0  Warnings: 0
 
  root@localhost : plx 11:57:02 desc test_no_pk;
  +---+-+--+-+-+---+
  | Field | Type| Null | Key | Default | Extra |
  +---+-+--+-+-+---+
  | col1  | varchar(32) | YES  | | NULL|   |
  | id| int(11) | NO   | PRI | 0   |   |
  +---+-+--+-+-+---+
  2 rows in set (0.01 sec)
 
  When users add a PK, I will remove implicit PK automatically.
 
  root@localhost : plx 11:57:07 alter table test_no_pk drop primary key;
  Query OK, 0 rows affected (0.00 sec)
  Records: 0  Duplicates: 0  Warnings: 0
 
  root@localhost : plx 11:57:42 desc test_no_pk;
  +--+-+--+-+-++
  | Field| Type| Null | Key | Default | Extra  |
  +--+-+--+-+-++
  | col1 | varchar(32) | YES  | | NULL||
  | id   | int(11) | NO   | | 0   ||
  | __row_id | bigint(20)  | NO   | PRI | NULL| auto_increment |
  +--+-+--+-+-++
  3 rows in set (0.00 sec)
 
  When users dropped PK, I will add it automatically.
 
  *3. INSERT VALUES*
  root@localhost : plx 11:59:22 insert into test_no_pk values('abc',2);
  ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'
  root@localhost : plx 11:59:23 insert into test_no_pk values('abc',4);
  ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'
 
  it will report duplicate, *Sergei, can you help me to find why?*
 
  *4. SELECT **
  root@localhost : plx 12:07:23 select * from test_no_pk;
  +--++
  | col1 | id |
  +--++
  | abc  |  6 |
  +--++
  1 row in set (0.00 sec)
 
  root@localhost : plx 12:07:30 select __row_id from test_no_pk;
  +--+
  | __row_id |
  +--+
  |1 |
  +--+
  1 row in set (0.00 sec

Re: [Maria-developers] users table

[Reindl Harald]

Am 19.06.2013 00:03, schrieb Roberto Spadim:
 hi guys, what's the better way to have a shared mysql schema?
 i'm talking about creating a user in one database and this user be created in 
 many databases (in the same server)
 i'm trying with symbolic links in data directory, but sure i have some 
 problems that i need same server version (ok
 no problem) and flush tables in all servers after add an user

this is russian roulette

if you need the same users use a dedicated dtabase with
it's own connection from all applications or implement
logic in the software to connect to all databases for
the insert

this is nothing which should be done with hacks



signature.asc
Description: OpenPGP digital signature
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Aria engine integrity

[Reindl Harald]

Am 20.04.2013 07:27, schrieb Roberto Spadim:.
 i'm using aria since myisam is a bit unsecure and have a slow recovery/repair 
 process, aria storage is more secure
 my doubt is... could i use aria engine over a ext2 file system and have a 
 'security' near a jornaled file system

these are completly different things
how should a application replace the integrity of the underlying filesystem?



signature.asc
Description: OpenPGP digital signature
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] mysql test question

[Reindl Harald]

Am 05.04.2013 22:24, schrieb Rich Prohaska:
 I was wondering if there was a way to run a mysql-test on with a prebuilt 
 data directory.  If so, can you point me
 to some example?

i can not imageine how this could work

a automated test is based on a specific state of whatever
followed by a action and defined state after the action

if you have a unknown state of the data directory you
would have predictable results



signature.asc
Description: OpenPGP digital signature
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp