The last blog entry was very popular and there were
lots of requests for some introductory information on the spatial
data types.
Well Known Text Or Binary
I am going to use the GEOMETRY data type over POINT, LINESTRING,
or POLYGON as it can store any of those three while the other
three can only contain data matching their name (so POINT can
holds only point data, etc.). The values are stored in an
internal geometry format but it takes wither WKT or WKB formatted
data.
Those are Well-Known Text (WKT) or Well-Known Binary (WKB)
formats repectively. I am hoping most of your are better with
text than binary so the following examples demonstrate how to
insert geometry values into a table by converting WKT values to
internal geometry format.
So let us start with a …
When you write stored procedures in MySQL, you sometimes need to
generate queries on the fly, for example as you process the
result of another query. This is supported using prepared statements. This blog explores how
you can take advantage of the sys schema to simplify
the use of dynamic queries.
Executing a query using the sys.execute_prepared_stmt() procedure.
The sys schema includes several stored procedures
and functions as well as views to make the database
administrator’s life easier. One of these is the …
MySQL before version 5.7 had less than stellar Geographic
Information Systems (GIS) support. In version 5.7 the
Boost.Geometry two dimensional or 2D libraries were added.
And with 8.0 came the three dimensional or 3D libraries. But how
do you use these features?
I would like to state up front that this is all new to me and
this is an attempt for me to document what it takes to go from
zero knowledge on GIS to something hopefully better. What I
want to do as an exercise is to get the distance between two
places from their longitude and latitude, say two cities near
where I live. So what do we have to do to accomplish
that?
It is actually easy with the functions provided if we have the
longitude and latitude in an SRID 4326 format.
SELECT ST_Distance(
(SELECT loc FROM cities WHERE name =
'Trondheim'),
(SELECT loc FROM cities WHERE …
Why migrating to MySQL 8.0 ?
MySQL 8.0 brings a lot of new features. These features make MySQL database much more secure (like new authentication, secure password policies and management, …) and fault tolerant (new data dictionary), more powerful (new redo log design, less contention, extreme scale out of InnoDB, …), better operation management (SQL Roles, instant add columns), many (but really many!) replication enhancements and native group replication… and finally many cool stuff like the new Document Store, the new MySQL Shell and MySQL InnoDB Cluster that you should already know if you follow this blog (see these TOP 10 for features for developers and this TOP 10 for DBAs & OPS).
Not anymore a drop in replacement !
We …
[Read more]On this article, I will show you how to migrate your wordpress database from the MariaDB on CentOS to the real MySQL.
Why migrating to MySQL 8.0 ?
MySQL 8.0 brings a lot of new features. These features make MySQL database much more secure (like new authentication, secure password policies and management, …) and fault tolerant (new data dictionary), more powerful (new redo log design, less contention, extreme scale out of InnoDB, …), better operation management (SQL Roles, instant add columns), many (but really many!) replication enhancements and native group replication… and finally many cool stuff like the new Document Store, the new MySQL Shell and MySQL InnoDB Cluster that you should already know if you follow this …
[Read more]Please join Percona’s Principal Support Engineer, Sveta Smirnova, as she presents MySQL Performance Schema in 1 hour on Thursday, March 21st, 2019, at 10:00 am PDT (UTC-7) / 1:00 pm EDT (UTC-4).
MySQL 8.0 Performance Schema is a mature tool, used by humans and monitoring products. It was born in 2010 as “a feature for monitoring server execution at a low level.” The tool has grown over the years with performance fixes and DBA-faced features. In this webinar, I will give …
[Read more]Consistency during reads have been a small concern from the adopters of MySQL InnoDB Cluster (see this post and this one).
This is why MySQL supports now (since 8.0.14) a new consistency model to avoid such situation when needed.
Nuno Carvalho and Aníbal Pinto already posted a blog series I highly encourage you to read:
[Read more]MySQL error log contains diagnostics messages such as errors, warnings and notes that occur during MySQL startup, shutdown and while the server is running. For example, a InnoDB table is corrupted and need to repaired, This will be recorded in the error log. MySQL 8.0 Error uses the MySQL component architecture for log event filtering and writing. The MySQL system variable log_error_services controls which log components to enable and the rules for filtering the log events. The component table in the mysql system database contains the information about currently loaded comments and shows which components have been registered with INSTALL COMPONENT. To confirm the components installed, you may use the SQL below:
SELECT * FROM mysql.component;
Currently the available log components are in lib/plugins:
- component_log_filter_dragnet.so
- component_log_sink_json.so …
Join Percona Support Engineer, Vinicius Grippa, as he presents his talk Enhancing MySQL Security on Thursday, March 7th, 2019 at 7:00 AM PST (UTC-8) / 10:00 AM EST (UTC-5).
Security is always a challenge when it comes to data. What’s more, regulations like GDPR add a whole new layer on top of it, with rules more and more restrictive to access and manipulate data. Join us in this presentation to check security best practices, as well as traditional and new features available for MySQL including features coming with the new …
[Read more]
Percona is glad to announce the release of Percona XtraBackup 8.0.5 on March 4, 2019. Downloads are available from our download site and from apt and yum repositories.
Percona XtraBackup enables MySQL backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate long periods of downtime. Offered free as an open source solution, it drives down backup costs while …
[Read more]