- discuss - lists.mariadb.org

[Maria-discuss] timestamp with time zone support mariaDB 10.1.14
by Karthick Subramanian 23 Feb '17

23 Feb '17

Hi Experts, I got this error, not sure whether maria db supports time zone: MariaDB [devdb]> create table ts (dt datetime, tz timestamp with time zone); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'time zone)' at line 1 My application team is looking for time zone support. Any thought please. reference: https://mariadb.com/kb/en/sql-99/temporal-data-types/#time-with-time-zone

3 3

[Maria-discuss] MariaDB 10.2.4 RC now available
by Daniel Bartholomew 17 Feb '17

17 Feb '17

The MariaDB project is pleased to announce the immediate availability of MariaDB 10.2.4 Release Candidate (RC). See the Release Notes and Changelog for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB 10.2.4 - Release Notes: https://mariadb.com/kb/en/mdb-1024-rn/ - Changelog: https://mariadb.com/kb/en/mdb-1024-cl/ - Downloads: https://downloads.mariadb.org/mariadb/10.2.4 About MariaDB 10.2: - https://mariadb.com/kb/en/what-is-mariadb-102/ APT and YUM Repository Configuration Generator: - https://downloads.mariadb.org/mariadb/repositories/ - - MariaDB Webinars - - - - - - - - - - - - - - - - - - - - - - - - - There are several upcoming MariaDB-focused webinars and many previously held ones available to watch on an on-demand basis at: - https://mariadb.com/news-events/webinars - - MariaDB Books - - - - - - - - - - - - - - - - - - - - - - - - - - There is an ever-growing library of MariaDB books available to help you get the most out of MariaDB. See the MariaDB Books page for details and links: - https://mariadb.com/kb/en/mariadb/books/ - - User Feedback plugin - - - - - - - - - - - - - - - - - - - - - - - MariaDB includes a User Feedback plugin. This plugin is disabled by default. If enabled, it submits basic, completely anonymous MariaDB usage information. This information is used by the developers to track trends in MariaDB usage to better guide development efforts. If you would like to help make MariaDB better, please add "feedback=ON" to your my.cnf or my.ini file! See http://mariadb.com/kb/en/user-feedback-plugin for more information. - - Quality - - - - - - - - - - - - - - - - - - - - - - - - - - - - - The project always strives for quality, but in reality, nothing is perfect. Please take time to report any issues you encounter at: - http://mariadb.org/jira - - Support MariaDB - - - - - - - - - - - - - - - - - - - - - - - - - If you would like to contribute to the MariaDB Foundation, please see the "contributing" and "donations" pages. We also have merchandise available in a cafepress store. All proceeds go to support the MariaDB Foundation. - https://mariadb.com/kb/en/contributing - https://mariadb.org/donate/ We hope you enjoy MariaDB! -- MariaDB Website - http://mariadb.org Twitter - http://twitter.com/mariadbfdn - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledge Base - http://mariadb.com/kb

1 0

Re: [Maria-discuss] 10.1.x: Got packet bigger than 'max_allowed_packet' bytes
by Reindl Harald 17 Feb '17

17 Feb '17

Am 17.02.2017 um 18:26 schrieb Chris Calender: > Hello, > > On 2/17/2017 9:51 AM, Reindl Harald wrote: >> Am 16.02.2017 um 10:04 schrieb Peter Laursen: >>> The particular error message "Got packet bigger than >>> 'max_allowed_packet' bytes" is returned from the client and not the >>> server. >>> >>> Both the server and the client has a "max_allowed_packet" setting. The >>> client-one is listed >>> here https://dev.mysql.com/doc/refman/5.7/en/mysql-command-options.html >> >> well, tell that "mysql_upgrade" (part of mariadb) and "myqltuner" >> (mysqtuner pretends "[!!] Attempted to use login credentials, but they >> were invalid" when this is ste in the [client] section >> >> mysql_upgrade >> /usr/bin/mysql_upgrade: unknown variable 'max_allowed_packet=200M' > > I'm sure you're already aware of this, but for anyone else out there > following along that does not know, cases like this are exactly what the > --loose- prefix is for. That way one should be able to prefix such > variables with --loose-, under say the [client] section, and then client > programs that understand the variable should work, and those that do not > *should* not fail (though I cannot comment specifically on mysqltuner, > as I've not used it before). But that is the general premise of the > --loose-* prefix. no, i am not aware of this and to be honest have zero understanding when client software from the same build don't understand and or at least just ignores options it don't understand we can dicsuss about server better safe than sorry, but frankly i am not a big fan of things not doing anything because they don't understand a future or removed config param > For instance, if you set: > > [client] > loose-max_allowed_packet=200M > > Then mysql_upgrade will only throw a warning and continue on: > > shell> mysql_upgrade -uroot -ppass -P3366 > Warning: mysql_upgrade: unknown variable 'loose-max_allowed_packet=200M' > Phase 1/6: Checking and upgrading mysql database > Processing databases > ... > >> so currently "mysql" is the only client which a) understands it and b) >> proceeeds it correctly - what a mess > > Also, mysqldump does read and process max_allowed_packet correctly. So > for anyone out there reading this strictly in regards to > max_allowed_packet, don't forget mysqldump needs it too (which it would > read it from [client], [mysqldump], or the command line invocation itself)

1 0

[Maria-discuss] 10.1.x: Got packet bigger than 'max_allowed_packet' bytes
by Reindl Harald 17 Feb '17

17 Feb '17

/etc/my.cnf (for many years now) max_allowed_packet = 200M how can it be that when the whole table file is 54MB large with a setting of 200MB that something is larger then 200MB while obviously it was not too large at insert time at 2016-10-06 but with MariaDB 10.0.x instead 10.1.x? PHP says mysqli_fetch_assoc() [<a href='http://at.php.net/manual/de/function.mysqli-fetch-assoc.php'>function.mysqli-fetch-assoc.php</a>]: Malformed server packet. Field length pointing 22 bytes after end of packet MariaDB [afi]> select * from cms1_docmgmt_entrys where doc_id=19; ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes MariaDB [afi]> show variables like 'max_allowed_packet'; +--------------------+-----------+ | Variable_name | Value | +--------------------+-----------+ | max_allowed_packet | 209715200 | +--------------------+-----------+ 1 row in set (0,00 sec) MariaDB [afi]> select doc_file_download_size from cms1_docmgmt_entrys where doc_id=19; +------------------------+ | doc_file_download_size | +------------------------+ | 15838399 | +------------------------+ 1 row in set (0,00 sec) -rw-rw---- 1 mysql mysql 9,3K 2017-02-04 15:39 cms1_docmgmt_entrys.frm -rw-rw---- 1 mysql mysql 54M 2017-02-10 04:05 cms1_docmgmt_entrys.MYD -rw-rw---- 1 mysql mysql 234K 2017-02-10 04:05 cms1_docmgmt_entrys.MYI

3 5

[Maria-discuss] mysqldump 10.1.16
by Karthick Subramanian 16 Feb '17

16 Feb '17

All, I got a replciation problem and planned to take master server dump using below command and tried to restore in slave server: mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=1 -A --routines > ~/dump.sql I got into this error: https://jira.mariadb.org/browse/MDEV-10119 Then I tried this method: http://lists.askmonty.org/pipermail/commits/2016-June/009504.html My question: Is it ok if the GIS procedure currently with definer as definer=`root@localhost` instead of blank as definer=`` for the following two procs in mysql: Slave_MariaDB [(none)]> select db, name, definer from mysql.proc; +---------+------------------------+-----------------+ | db | name | definer | +---------+------------------------+-----------------+ | mysql | AddGeometryColumn | root@localhost | | mysql | DropGeometryColumn | root@localhost | Master_MariaDB [(none)]> select db, name, definer from mysql.proc; +---------+------------------------+-----------------+ | db | name | definer | +---------+------------------------+-----------------+ | mysql | AddGeometryColumn | @ | | mysql | DropGeometryColumn | @ |

2 2

[Maria-discuss] LOAD DATA LOCAL INFILE
by Karthick Subramanian 15 Feb '17

15 Feb '17

HI All, Even after enabling --local-infile=1 at mysql user login, I couldn't load the file locally. mysql -u root --local-infile=1 LOAD DATA LOCAL INFILE ERROR 1148 (42000): The used command is not allowed with this MariaDB version show variables like '%local%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | local_infile | OFF | +---------------+-------+ Even though i enable --local-infile=1, the local-infile still shows OFF. Pleaselet know how can I fix this.

3 4

[Maria-discuss] ROLE and DEFAULT DATABASE
by Karthick Subramanian 09 Feb '17

09 Feb '17

Hi, Can anyone assist me on this below: I have a database called appdb. I need to grant access to this db to different users say: api_user, app_user, portal_user. So I created a ROLE as APP_ROLE and GRANT as below: GRANT SELECT, INSERT, UPDATE, DELETE ON APPDB.* TO APP_ROLE; Later I assign the Role to user: GRANT APP_ROLE TO API_USER@'%' IDENTIFIED BY 'YOU'; SET DEFAULT ROLE APP_ROLE TO API_USER@'%'; But the problem now is: Application team want to choose the database when they establish connection. But they couldn't use APPDB. So they couldn't see any db objects listed in APPDB. Is there a way we can assign a default database also for user.

3 2

[Maria-discuss] default_tmp_storage_engine=NULL
by Ian Gilfillan 09 Feb '17

09 Feb '17

What is the purpose/effect of having MariaDB's default_tmp_storage_engine setting default to NULL instead of the default used in MySQL, Innodb? I can't see any obvious differences in behaviour.

3 3

Re: [Maria-discuss] R: default_tmp_storage_engine=NULL
by Federico Razzoli 09 Feb '17

09 Feb '17

Well, I can't think many cases where InnoDB is not the default engine. But, if it is not, why should you use it for temporary tables? If you mainly use TokuDB, maybe don't want to mix the engines. If you use MyISAM, maybe you don't want transactional temporary tables. I still think that MEMORY makes sense in all cases :) Federico -------------------------------------------- Gio 9/2/17, Ian Gilfillan <ian(a)mariadb.org> ha scritto: Oggetto: Re: [Maria-discuss] R: default_tmp_storage_engine=NULL A: "Maria Discuss" <maria-discuss(a)lists.launchpad.net> Data: Giovedì 9 febbraio 2017, 10:32 > I beleive that there is a difference if default_storage_engine != innodb, because in that case, default_tmp_storage_engine will not be InnoDB. Yes, that's the case. So why is this desirable in MariaDB as opposed to MySQL's implementation? _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss(a)lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

1 0

[Maria-discuss] R: default_tmp_storage_engine=NULL
by Federico Razzoli 09 Feb '17

09 Feb '17

Hi Ian I beleive that there is a difference if default_storage_engine != innodb, because in that case, default_tmp_storage_engine will not be InnoDB. It is much less clear to me why default_tmp_storage_engine doesn't default to MEMORY in both MariaDB and MySQL :) Federico -------------------------------------------- Gio 9/2/17, Ian Gilfillan <ian(a)mariadb.org> ha scritto: Oggetto: [Maria-discuss] default_tmp_storage_engine=NULL A: "Maria Discuss" <maria-discuss(a)lists.launchpad.net> Data: Giovedì 9 febbraio 2017, 09:23 What is the purpose/effect of having MariaDB's default_tmp_storage_engine setting default to NULL instead of the default used in MySQL, Innodb? I can't see any obvious differences in behaviour. _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss(a)lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

2 1