Friday, February 14, 2020

MySQL Support Engineers Sought For Positions in Australia, Russia, Mexico and Philippines

Oracle, the world's premier database company, is hiring expert technical support staff for MySQL - the world's most popular open source database! By joining this proven team of MySQL professionals, you will assist customers in the resolution of their issues with your wide-ranging skill set. From explaining database internals to reviewing schema design, from application architecture review to client code analysis, from best practices definition to defect analysis - customers will look to you for assistance in addressing their needs in a timely, professional manner. You will continually exercise and grow your diverse skills as you deliver the highest quality support possible to customers. Work location is flexible.



Job duties are varied and complex utilizing independent judgment. May have project lead role. 4 years experience with Core products or five years experience with Applications products and have a technical degree i.e., BS Computer Science/Management Information Systems/Science/ Engineering/Math/Physics/Chemistry with a 3.0 GPA OR (for Applications) proven professional/ technical experience, i.e., demonstrating an understanding of Applications at a functional and technical level (preferably Oracle)

Additional Details: Successful candidates will possess the following characteristics and qualifications:

·       Excellent knowledge of the MySQL server (preferably MySQL Cluster) and utilities

·       Experience managing MySQL in production settings

·       3+ years C/C++ or Java software development experience

·       Practical experience with distributed computing

·       Understanding of high availability, load balancing, and performance

·       Understanding of database engine design and implementation

·       Strong troubleshooting skills

·       Strong experience with Linux development environments

·       Proven skills in solving complex issues

·       Ability to pass all levels of MySQL Certification

·       Excellent spoken and written communications skills in English

·       A desire to serve customers with a passion

·       A desire to be a team player within a small virtual community

·       Ability to manage multiple priorities with limited supervision


Detailed Description and Job Requirements

As a member of the Support organization, your focus is to deliver post-sales support and solutions to the Oracle customer base while serving as an advocate for customer needs. This involves resolving post-sales non-technical customer inquiries via phone and electronic means, as well as, technical questions regarding the use of and troubleshooting for our Electronic Support Services. A primary point of contact for customers, you are responsible for facilitating customer relationships with Support and providing advice and assistance to internal Oracle employees on diverse customer situations and escalated issues.

As a Sr. Support Engineer, you will be the technical interface to customers, Original Equipment Manufacturers (OEMs) and Value-Added Resellers (VARs) for resolution of problems related to the installation, recommended maintenance and use of Oracle products. Have an understanding of all Oracle products in their competencies and in-depth knowledge of several products and/or platforms. Also, you should be highly experienced in multiple platforms and be able to complete assigned duties with minimal direction from management. In this position, you will routinely act independently while researching and developing solutions to customer issues.

Job duties are varied and complex utilizing independent judgment. May have project lead role. 4 years experience with Core products or five years experience with Applications products and have a technical degree i.e., BS Computer Science/Management Information Systems/Science/ Engineering/Math/Physics/Chemistry with a 3.0 GPA OR (for Applications) proven professional/ technical experience, i.e., demonstrating an understanding of Applications at a functional and technical level (preferably Oracle)

As part of Oracle's employment process candidates will be required to successfully complete a pre-employment screening process. This will involve identity and employment verification, professional references, education verification and professional qualifications and memberships (if applicable).

Job: Support
Travel: No
Job Type: Regular Employee Hire
Organization: Oracle
Locations; Australia, Russia, Mexico and Philippines

For more information see Russia Mexico Australia Philippines  

Russia: https://oracle.taleo.net/careersection/jobdetail.ftl?job=20000018&lang=en
Mexico: https://oracle.taleo.net/careersection/jobdetail.ftl?job=200000ZL&lang=en
Australia: https://oracle.taleo.net/careersection/jobdetail.ftl?job=19001JEU&lang=en
Philippines: https://oracle.taleo.net/careersection/jobdetail.ftl?job=190019PY&lang=en



Thursday, February 13, 2020

The MySQL Track at the Southern California Linux Expo 2020

  Friday March 6th you need to be in Pasadena, California for the MySQL Track at the Southern California Linux Expo. SCaLE is the biggest open source show in North America and the only one in the great Los Angeles area. 

  We have been lucky to have a MySQL track for the last several years and this year I think we have the best lineup ever!  We start at 9:30 in room 101 with Michael Marx's presentation on InnoDB Cluster, then switch to Building a Database as a Service on Kubernetes by Lucy Burns and Abhi Vaidyanatha.  Next Alexander Rubin speaks on Protecting Personal and Health Data in MySQL before lunch.

After lunch comes Implementing MySQL Database-as-a-Service using Open Source tools by Matthias Crauwels. That session is followed by sessions on isolation levels by Pep Pla, new MySQL feratures by me, and PMM from Peter Zaitsev.

And we finish with an ProxySQL introduction from the always entertaining Solomon Chang.

So please attend the MySQL track and drop by the MySQL booth in the expo hall.

Thursday, February 6, 2020

MySQL 8.0 DBA Certification Exam Now Available

You can now earn your MySQL 8.0 DBA Certification Exam is now available. It is Oracle Exam 1Z0-908 and I was one of the many question writers.  Some of you may remember when I was the head of MySQL Certification and I can assure you this is a very complete test of all your MySQL DBA skills. And it asks very relevant questions and not trivial facts. So anyone taking this exam and earning this certification really knows their MySQL!

So what do you need to know:

Architecture

  • Configure client connections to the server
  • Understand how MySQL stores data
  • Understand how InnoDB stores data and logs
  • Configure buffers and caches
  • Understand and use the Data Dictionary

Security

  • Create user accounts and roles
  • Use authentication plug-ins
  • Control user and role permissions
  • Recognize common security risks
  • Secure MySQL server connections
  • Provide password and login security
  • Secure the MySQL host environment
  • Prevent SQL injection attacks
  • Encrypt MySQL data
  • Configure MySQL Enterprise Firewall

Query Optimization

  • Examine how MySQL optimizes queries
  • Analyze queries with MySQL Enterprise Monitor
  • Create indexes to improve server performance
  • Monitor and understand index statistics

High Availability Techniques

  • Explain how replication provides high availability and scalability
  • Configure replication
  • Explain the role of the binary log in replication
  • Configure multisource replication
  • Explain the role of replication threads
  • Monitor and troubleshoot replication
  • Describe MySQL InnoDB cluster and Group Replication
  • Configure a MySQL InnoDB cluster
  • Perform an InnoDB cluster recovery

Server Installation and Configuration

  • Install and use the MySQL server and client programs
  • Identify the files and folders created during installation
  • Start and stop MySQL
  • Upgrade MySQL
  • Configure MySQL by using options and option files
  • Configure MySQL variables
  • Launch multiple MySQL servers on the same host

Monitoring and Maintenance

  • Configure and view MySQL log files
  • Monitor MySQL processes and status
  • Configure MySQL Enterprise Audit
  • Use MySQL Enterprise Monitor to view activity in MySQL
  • Monitor database growth and explain capacity planning
  • Troubleshoot problems with locked resources

Backups and Recovery

  • Distinguish between the different types of backup
  • Implement a backup strategy
  • Backup and restore data with MySQL Enterprise Backup
  • Use mysqldump and mysqlpump to perform logical backups
  • Explain when and how to use raw file backups
  • Back up the binary log

You can register for your exam here.

Tuesday, February 4, 2020

DBA Gym -- Free Workouts to Shape Up Your Database Strengths

Need to pump up your form or at least your third normalized form?  If you want to be a better developer, database administrator, or data expert then you need to know about normalizing your data. What is data normalization? Well Wikipedia says it is is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency.

Did you know there is a free GYM that lets you work on forms.  There are even quizzes  to help you test your knowledge.  This is part of the Oracle Gym and I started the Normally Normalized: Third Normal Form workout

The course states Normalizing your data structures is a key step towards high quality database design. This workout introduces you to Third Normal Form, from Wikipedia and the Database Programmer. And I can assure you that the workouts are brief, fun, and will really pack on mental muscle.

You will need an Oracle login which is free and each workout is short. Actually the two supporting documents and the three quizzes are each 5-6 minutes so in a half an hour you can complete the entire workout, or save each part by itself over a longer time period.

Other Courses are available such as deductive logic, PL/SQL, and knowing the difference between RANGE and ROWS in a windowing function.  Now most of these workout are designed for the Oracle database but the concepts usually fir very well with MySQL too. While me MySQL-ers may not need to use PL/SQL the materials on the higher level concepts are invaluable and the quizzes are going to make you think.

So if you are looking for a challenge or need a quick workout, please check out the Oracle Gym.

Friday, January 31, 2020

MySQL Pre-FOSDEM Days - A Quick Review

A sold out, two-day event with over 30 sessions on various aspects of MySQL is a tough thing to organize and accomplish. But the MySQL pre-FOSDEM Days was an amazing success. If you missed this event then you really missed two full days of presentations by MySQL engineers and subject matter experts from the MySQL Community. 


It all started on Thursday January 30th with Geir Hoydalsvik talking about Simplifying MySQL which not a simple talk.  Big efforts are being made to clean up and modernize the MySQL Server Core code.  Work is being undertaken to change to a Volcano Model for extended flexibility. 

This was followed by Kenny Gryp showing how the new InnoDB Replica Set. Previously it was easier to setup InnoDB Cluster Replication than simple primary-secondary replication. 

From that point on there were two different tracks with many talks on indexing JSON Arrays, detailed information on date-time types, the new redo log, hash joins, the big changes in NDB Cluster, and more. 

The audience members I talked to where impressed with the new features, their access to MySQL engineers, and the quality of the talks.  And on Friday it was topped off by the fantastic community dinner. 

Next year you will hear about the 2021 event and I highly encourage you to participate.  This even has become one of the better 'raw' technical content events on the calendar and anyone running MySQL would greatly benefit from attending.

And a big round of applause for my colleagues from the MySQL Community Team - Lenka Kasparova and LeFred -- for all their hard work and on their accomplishments. Lenka routinely makes miracles happen with the various tasks related to funding while Fred brought the various other components together for a stupendous event. 

Check Constraints and Duplicate Names


i was working on some example code for using check constraints and was fairly happy with my first bit of of code.

CREATE TABLE c1 (x INT 
                   CONSTRAINT x_gt_0 CHECK (x > 0)
);

it worked well and did what I wanted which was to reject data where the value for x was not one or greater. MySQL has allowed you to have constraint checks for many years but they only came to life in 8.0.16.  Before that version the checks were simply ignored.

So I was writing some more demo code and smacked into what I thought was an odd error.

CREATE TABLE c2 (x INT 
                   CONSTRAINT x_gt_0 CHECK (x > 0), 
                   CONSTRAINT x_lt_10 CHECK ( x < 10)
);
ERROR: 3822: Duplicate check constraint name 'x_gt_0'.

I did have a constraint in another table named x_gt_0.  It seemed odd to me and it was odd enough that it sent me to the manual.  If you refer to 13.1.20.7 CHECK Constraints of the MySQL 8.0 server manual, there in black and white is

The SQL standard specifies that all types of constraints (primary key, unique index, foreign key, check) belong to the same namespace. In MySQL, each constraint type has its own namespace per schema (database). Consequently, CHECK constraint names must be unique per schema; no two tables in the same schema can share a CHECK constraint name. (Exception: A TEMPORARY table hides a non-TEMPORARY table of the same name, so it can have the same CHECK constraint names as well.)
Ah, it is because of the danged named spaces.  And if the SQL standard specifies then I have to live with it.

Work around? Yes, I quickly renamed the constraint in question getting what I wanted.  In this case it was not a big thing as the name of the constraint, while descriptive, was not named by some imperative.

>CREATE TABLE c2 (x INT 
                  CONSTRAINT x_gt_1 CHECK (x > 1), 
                  CONSTRAINT x_lt_10 CHECK ( x < 10)
);
Query OK, 0 rows affected (0.0801 sec)
 >insert into c2 (x) values (100);
ERROR: 3819: Check constraint 'x_lt_10' is violated.
>insert into c2 (x) values (0);
ERROR: 3819: Check constraint 'x_gt_1' is violated.
>insert into c2 (x) values (4);
Query OK, 1 row affected (0.0035 sec)

So there is always another way to skin a cat but how many skinless cats do you need hanging around?         

If you want to be proactive you can refer to the INFORMATION_SCHEMA.check_constraints table to see if you have already used that name.

>select * from INFORMATION_SCHEMA.check_constraints 
        where CONSTRAINT_NAME='x_gt_0'\G

*************************** 1. row ***************************
CONSTRAINT_CATALOG: def
 CONSTRAINT_SCHEMA: demo
   CONSTRAINT_NAME: x_gt_0
      CHECK_CLAUSE: (`x` > 0)
*************************** 2. row ***************************
CONSTRAINT_CATALOG: def
 CONSTRAINT_SCHEMA: xtest
   CONSTRAINT_NAME: x_gt_0
      CHECK_CLAUSE: (`x` > 0)
2 rows in set (0.0017 sec)

Remember you can reuse the name but it has to be in a different schema. That is why I can have x_gt_0 used twice but once in the demo schema and once in the xtest schema.







create table porridge (id int unsigned auto_increment,
temp integer
       constraint too_hot check (temp > 100),
       constraint too_cold check ( temp < 70),
primary key(id)
);

Friday, January 24, 2020

MySQL Document Store Tutorial

When I tell people that they can use MySQL without SQL they tend to be skeptical.  But with the MySQL Document Store you can do just that with a new NoSQL API and in this case there is no structured query language.pre-FOSDEM MySQL Days (which is sold out and there is a waiting list) is my tutorial on using the Document Store.  Those in my session will be able to see how to use the MySQL Shell (mysqlsh) to connect to a MySQL server and save data without have to do the many things a DBA used to have to do in the past such as normalize data, setup relations, and several other tasks.  Plus the schema-less Document Store means you can alter your data needs without having to do an endless series of ALTER TABLES.
MySQL without SQL
MySQL Document Store let you save and retrieve data without needed the use of structured query language (SQL)
Part of the tutorial is a workbook and slides that I should be able to publish if they are well received.  And maybe a video for those who will not be able to make it to Brussels.

Wednesday, January 15, 2020

Neat Stuff in MySQL 8.0.19

MySQL 8.0.19 came out this week and can he downloaded here.   One of the first things I do when a new release happens is look at the release notes.  The release note cover the changes from the previous versions and the latest edition edition details some interesting new stuff.

Password Locking


MySQL now enables administrators to configure user accounts such that too many consecutive login failures due to incorrect passwords cause temporary account locking. The required number of failures and the lock time are configurable per account, using the FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME options of the CREATE USER and ALTER USER statements.

YEAR(4) and Year


Support for the YEAR(2) data type was removed in MySQL 5.7.5, leaving only YEAR and YEAR(4) as valid specifications for year-valued data. Because YEAR and YEAR(4) are semantically identical, specifying a display width is unnecessary, so YEAR(4) is now deprecated and support for it will be removed in a future MySQL version. Statements that include data type definitions in their output no longer show the display width for YEAR. This change applies to tables, views, and stored routines, and affects the output from SHOW CREATE and DESCRIBE statements, and from INFORMATION_SCHEMA tables.

Syntax Change 


MySQL now supports explicit table clauses and table value constructors according to the SQL standard. These have now been implemented, respectively, as the TABLE statement and the VALUES statement. What does that mean? 

You can use TABLE like you use SELECT.  The two following statements are equivalent;

TABLE t ORDER BY c LIMIT 10 OFFSET 3; SELECT * FROM t ORDER BY c LIMIT 10 OFFSET 3;

 And ROW will help ease confusion for those adding multiple records in a statement.  Again the following two statements are equivalent.

INSERT INTO t1 VALUES ROW(1,2,3), ROW(4,5,6), ROW(7,8,9); INSERT INTO t1 VALUES (1,2,3), (4,5,6), (7,8,9);

 You can also select from a VALUES table value constructor just as you would a table, bearing in mind that you must supply a table alias when doing so. Using column aliases, you can also select individual columns

mysql> SELECT a,c FROM (VALUES ROW(1,2,3), ROW(4,5,6)) AS t(a,b,c); +---+---+ | a | c | +---+---+ | 1 | 3 | | 4 | 6 | +---+---+



Recursion Limits on CTEs


Before 8.0.19 it was not possible to use LIMIT in the recursive SELECT part of a recursive common table expression (CTE). LIMIT is now supported in such cases, along with an optional OFFSET clause

WITH RECURSIVE cte AS ( SELECT CAST("x" AS CHAR(100)) AS a FROM DUAL UNION ALL SELECT CONCAT("x",cte.a) FROM qn WHERE LENGTH(cte.a) < 10 LIMIT 3 OFFSET 2; ) SELECT * FROM cte;


Alias on DUPLICATE KEY Updates


MySQL now supports aliases in the VALUES and SET clauses of INSERT INTO ... ON DUPLICATE KEY UPDATE statement for the row to be inserted and its columns. Note the use of 'new' as an alias.

INSERT INTO t VALUES (9,5), (7,7), (11,-1) AS new ON DUPLICATE KEY UPDATE a = a + new.a - new.b;

Microsoft Windows System Command

 Previously, the system (\!) command for the mysql command-line client worked only for Unix systems. It now works on Windows as well. For example, system cls or \! cls may be used to clear the screen


X Protocol Compression


Compression is supported for messages sent over X Protocol connections. Connections can be compressed if the server and the client agree on a compression algorithm to use. By default, X Protocol announces support for the deflate, lz4, and zstd compression algorithms.

Row Based Replication for Security


A new setting REQUIRE_ROW_FORMAT is added for replication channels, which makes the channel accept only row-based replication events. You can specify REQUIRE_ROW_FORMAT using a CHANGE MASTER TO statement to enforce row-based binary logging for a replication channel that is secured with privilege checks, or to increase the security of a channel that is not secured in this way. By allowing only row-based replication events, REQUIRE_ROW_FORMAT prevents the replication applier from taking actions such as creating temporary tables and executing LOAD DATA INFILE requests, which protects the replication channel against some known attack vectors. Row-based binary logging (binlog_format=ROW) must be used on the replication master when REQUIRE_ROW_FORMAT is set.

But Wait -- There's More


These were only the big items that caught my eye and a recommend reading the release notes to see if there is something that did not catch my eye that may be of interest for you.