Manejando datos

Common_schema: repetitive tasks with foreach

Posted by in MariaDB, MySQL

Although we have spoken several times of this framework MariaDB MySQL-today a new feature, I bring very useful when you need to perform repetitive tasks based on consultations. It is used foreach call. Here I present a practical example of how you’ve used to perform a specific task: to replace all null to zero in a table with a lot of fields values. Locating the table fields The best way of locating the fields that will be used in our task is by using the information_schema, an internal database needed…read more

Learning #MySQL at SoyData.Net

Posted by in Databases, MariaDB, MySQL

This entrance is dedicated to a new website for learning #BigData in spanish: What is It is a collaborative online training platform on all matters relating to data in spanish. It now has 15 fully uploaded and available courses to which they have registered more than five hundred people in just two weeks. These courses address specific high demand technologies like Hadoop, MongoDB, R and cover key areas such as visualization, cloud computing or Structured Query Language (SQL). It also provides an introduction to #BigData concept for those…read more

Solution to Error 1067 Can’t start the server on MySQL

Posted by in MySQL

As I already wrote here, a week later after reporting this bug (because I change the datadir property on the config file my.ini) I didn’t have a solution for error 1067 Can’t Start service on MySQL 5.6.10. Now, you can read how I solve it!! How the error 1067 araise If you would like to raise the error, jsut install a MySQL 5.6.10 server, and modify the path where data is saved on the config file. Easy!! Trying in a new machine A pesar del error, uno siempre piensa que…read more

Common-schema: redundant keys on MySQL-MariaDB

Posted by in Databases

Let’s keep on learning more and more about common-schema, and I execute a query about redundant keys: SELECT * FROM common_schema.redundant_keys; The result to this query informs me that these keys are, literally, redundant, and the optimal solution si to remove them. Also, the query has more interesting information! After this, you think: “Well, if they are redundant, let’s remove it“. But, included in the previous query, there is a filed called “sql_drop_index” with the SQL sentence to remove the index. Even more, because common_schema includes a function EVAL very…read more

Listing primary keys of a database in MySQL-MariaDB

Posted by in MariaDB, MySQL

A few days ago, I needed to ask to all tables of a database about what is the name of the primary key of each table. The solution is easy, just using the data from the database information_schema, and by retrieving the data for the selected schema, this way: SELECT TABLE_NAME as table, COLUMN_NAME FROM `information_schema`.`COLUMNS` WHERE (`TABLE_SCHEMA` = ‘database’) AND (`COLUMN_KEY` = ‘PRI’); And …  for what do I need this query? Well, the origen of the problem was this: I created a view with the previuos SQL sentence…read more