Banner
Home News Feeds Planet MySQL
Newsfeeds
Planet MySQL
Planet MySQL - http://www.planetmysql.org/

  • Shinguz: Get rid of wrongly deleted InnoDB tables
    Taxonomy upgrade extras: BackupRestoreRecoveryinnodbtablePrecaution: Before you try this out on your production system do a BACKUP first! FromDual Backup Manager can help you with this. Situation A MySQL user has delete its InnoDB table files for example like this: shell> rm -f $datadir/test/test.* Analysis We do some analysis first: mysql> DROP TABLE test; ERROR 1051 (42S02): Unknown table 'test' mysql> CREATE TABLE test (id INT) ENGINE = InnoDB; ERROR 1050 (42S01): Table '`test`.`test`' already existsThe MySQL error log shows us the following information: 141022 17:09:04 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. InnoDB: If you are installing InnoDB, remember that you must create InnoDB: directories yourself, InnoDB does not create them. 141022 17:09:04 InnoDB: Error: trying to open a table, but could not InnoDB: open the tablespace file './test/test.ibd'! InnoDB: Have you moved InnoDB .ibd files around without using the InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE? InnoDB: It is also possible that this is a temporary table #sql..., InnoDB: and MySQL removed the .ibd file for this. InnoDB: Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html InnoDB: for how to resolve the issue.Fix User claims that he does NOT need the table and/or the data any more but wants to get rid of the error messages and/or create a new table with the same name. mysql> CREATE SCHEMA recovery; mysql> use recovery mysql> CREATE TABLE test (id INT) ENGINE = InnoDB; mysql> \! cp $datadir/recovery/test.frm $datadir/test/ mysql> DROP SCHEMA recovery; mysql> use test mysql> DROP TABLE test; Prove To prove it works we create a new table and fill in some records: mysql> CREATE TABLE test (id int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, data VARCHAR(64), ts TIMESTAMP) ENGINE = InnoDB; mysql> INSERT INTO test VALUES (NULL, 'Test data', NULL); Literature Troubleshooting InnoDB Data Dictionary Operations

  • MySQL sys version 1.2.0 released
    MySQL sys version 1.2.0 has just been released. All views now work with MySQL 5.7.5, and the ONLY_FULL_GROUP_BY changes. There is also a new script available (generate_sql_file.sh) that will allow RDS users to easily generate a single SQL file, using a specified user, that can be loaded in to an RDS instance. See the README for details on how to use that. Here’s a full summary of the other changes: Backwards Incompatible Changes The host_summary_by_stages and user_summary_by_stages wait_sum and wait_avg columns were renamed to total_latency and avg_latency respectively, for consistency. The host_summary_by_file_io_type and user_summary_by_file_io_type latency column was renamed to total_latency, for consistency. Improvements Made the truncation length for the format_statement view configurable. This includes adding a new persistent sys_config table to store the new variable – statement_truncate_len – see the README for usage Added total_latency to the schema_tables_with_full_table_scans view, and added an x$ counterpart Added innodb_buffer_free to the schema_table_statistics_with_buffer view, to summarize how much free space is allocated per table in the buffer pool The schema_unused_indexes view now ignores indexes named PRIMARY (primary keys) Added rows_affected and rows_affected_avg stats to the statement_analysis views The statements_with_full_table_scans view now ignores any SQL that starts with SHOW Added a script, generate_sql_file.sh, that can be used to generate a single SQL file, also allowing substitution of the MySQL user to use, and/or whether the SET sql_log_bin … statements should be omitted. This is useful for those using RDS, where the root@localhost user is not accessible, and sql_log_bin is disabled (Issue #5) Added a set of memory_by_thread_by_current_bytes views, that summarize memory usage per thread with MySQL 5.7′s memory instrumentation Improved each of the host specific views to return aggregate values for background threads, instead of ignoring them, in the same way as the user summary views Bug Fixes Added the missing memory_by_host view for MySQL 5.7 Added missing space for hour notation within the format_time function Fixed views affected by MySQL 5.7 ONLY_FULL_GROUP_BY and functional dependency changes

  • MySQL Crash-safe replication, Binlog Servers and Percona Live London
    I just publish a post on the Booking.com blog: http://blog.booking.com/better_crash_safe_replication_for_mysql.html  Spoiler: it uses Binlog Servers.This is also the opportunity to tell you that I will be at Percona Live London at the beginning of November, and that I will give a talk about Binlog Servers: High Availability, Disaster Recovery and Extreme Read Scaling using Binlog Servers.  I will not talk too much about Binlog Server for crash-safe replication, but I will present a new use-case for Binlog Servers that I did not blog about yet.  I am looking forward to meet you there.

  • libAttchSQL Hits First GA!
    We have come a long way since the first code was put down for libAttachSQL on the 4th July. It has been a fantastic project to work on so I am very pleased to announce our first GA release. For those who haven't seen it so far libAttachSQL is a non-blocking, lightweight C API for MySQL servers. It is Apache 2.0 licensed so is compatible with most Open Source and commercial licensing. HP's Advanced Technology Group saw a need in this field not just for HP itself but for other companies and projects too. As for the GA release itself, there are not many changes over the RC release beyond stability fixes. A full list can be seen in the version history documentation. In addition to the GA release we have recently had a driver for Sysbench merged into their trunk so libAttachSQL can be used for benchmarking MySQL servers. We have also started work on a tool called AttachBench which when complete will run similar MySQL tests as Sysbench but will allow for multiple connections per thread (something libAttachSQL excels at). At the moment AttachBench requires the tables from Sysbench's "Select" test already setup and I don't recommend tinkering with it yet unless you don't mind getting a bit dirty. With the release of libAttachSQL 1.0.0 we have also launched a new website on libattachsql.org. It is a basic Pelican based site (very much like this blog) but will make it much easier for anyone to add content, just like this blog all the source is in RST files on GitHub. Download links for libAttachSQL 1.0.0 can be found on the News section of the project website. There is a source package as well as packages for RHEL/CentOS 6.x and 7.x. Packages for Ubuntu 12.04 and 14.04 are waiting to be built in the PPA at time of posting. We hope to have releases for more operating systems in the near future. Rest assured we are not stopping here. I already have ideas of what I want to see in 1.1 and we have some spin-off projects planned. If you would like to learn more please come along to my talk on libAttachSQL at Percona Live London. I'm also talking to several people outside of HP to see what they would like in libAttachSQL and am happy to talk to anyone else who wants to know more and has feedback. Many thanks to everyone who has helped us get this far.

  • Why I moved my Joomla website to MariaDB
    Wed, 2014-10-22 09:02svetoslavsavovNext time you want to build a fast, well-optimized website, don't forget the database system. Many content management systems (CMS) let you choose a relational database management system (RDBMS) to use on the back end. MySQL, famous for its stability and security, is a popular choice; in addition to its numerous features, it has a large community, many contributors, and good documentation. However, MySQL is now owned by Oracle, and its future is not clear, which makes MariaDB, an application compatible database system, an excellent alternative. Joomla, a popular CMS, is written in PHP and by default uses MySQL as its database system. Since MariaDB can provide improved functionality, performance, and stability, you might want to use MariaDB instead of MySQL with Joomla. Although MariaDB is not listed in Joomla's technical requirements, it is safe to migrate your Joomla site's database to it. I'll walk through the process on a CentOS server, since it is a popular distribution for hosting web servers. While the syntax might differ slightly for other Linux distributions, the algorithm is the same. Before you migrate, you might want to do some benchmarks, so you can see whether your efforts have led to improvements. You can turn on the Joomla debugging option through the admin's back end or by executing the command # sed -i "s@\$debug = '0';@\$debug = '1';@" configuration.php. After that, you can load the front page of your website and see the full list of the queries that are run. Navigate to the bottom of the page, click on the Database Queries link and the queries will be listed. Copy them and turn off the debugging if you are benchmarking your live website; if you don't, your visitors will see its output after each page's footer. Then use a text editor like vim or nano to paste them in your test .sql file, put each query on a single line, removing unnecessary blank spaces in them – for example, the format should be as follows: SELECT folder AS type, element AS name, params FROM gpa_extensions WHERE enabled >= 1 AND type ='plugin' AND state >= 0 AND access IN (1,1) ORDER BY ordering – and save the SQL file. To run a benchmark, use a database server load emulator called mysqlslap, like this: # mysqlslap --concurrency=100 --iterations=10 --query=several_joomla_queries.sql --create-schema=myuser_joomla -umyuser_joom -p7654e684c4d14ab544261568101cc9c4 Benchmark Average number of seconds to run all queries: 0.275 seconds Minimum number of seconds to run all queries: 0.233 seconds Maximum number of seconds to run all queries: 0.351 seconds Number of clients running queries: 100 Average number of queries per client: 3 That command simulates 100 MySQL clients that concurrently run several sample SELECT queries grabbed from the debugging database queries output and saved in the several_joomla_queries.sql file on your Joomla database. The connection to the database is established with the database name, the associated database username and the password specified in the Joomla’s configuration.php file. The queries in my test are a small excerpt from the Joomla debugging tool database queries list, which I specified should iterate 10 times. Once the migration to MariaDB is completed, you can perform the same test and compare the results, since the load emulator is replicated in MariaDB. Now you can proceed with the migration. Create a full backup of the database files and your MySQL configuration file, just in case something goes wrong, then stop the MySQL server: # cp /var/lib/mysql/ mysql_backup -r # cp /etc/my.cnf /etc/my.cnf_backup # service mysql stop Shutting down MySQL (Percona Server)... SUCCESS! Next, remove the existing MySQL 5.5 installation. First, list the packages that you plan to remove (rpm -qa | grep -i mysql-). Then complete the actual process; use the following command with caution: # for i in `rpm -qa | grep -i mysql-`; do rpm -e --nodeps $i; done. Next, generate a MariaDB repository file for your architecture and place it under the /etc/yum.repos.d/ folder: # cat /etc/yum.repos.d/MariaDB.repo # MariaDB 5.5 CentOS repository list - created 2014-09-28 19:49 UTC # http://mariadb.org/mariadb/repositories/ [mariadb] name = MariaDB baseurl = http://yum.mariadb.org/5.5/centos6-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1 Install the MariaDB 5.5 server and client with the command # yum install MariaDB-server MariaDB-client. . Since MariaDB is a drop-in replacement for MySQL, the Joomla website should now work without further modifications. Clear your web browser's cache and test it. If Joomla works, you can run the benchmark again. When I did that, I saw better results with my benchmark test queries: # mysqlslap --concurrency=100 --iterations=10 --query=several_joomla_queries.sql --create-schema=myuser_joomla -umyuser_joom -p7654e684c4d14ab544261568101cc9c4 Benchmark Average number of seconds to run all queries: 0.185 seconds Minimum number of seconds to run all queries: 0.122 seconds Maximum number of seconds to run all queries: 0.237 seconds Number of clients running queries: 100 Average number of queries per client: 3 As you can see, in a very short time, replacing MySQL with a fast and reliable database system like MariaDB can improve a database application's user experience. Tags: CMSHowto About the Author Svetoslav Savov Svetoslav Savov an a Linux enthusiast with more than 8 years of experience on a Senior position in one of the leading web hosting companies. He has a master degree in the Computer networks and enjoys working with open-source software solutions. In his spare time he practices extreme sports and travels around the world. e-mail address: svetlio_81@yahoo.com

Banner

 

Copyright © 2001 - 2013 K-Factor Technologies, Inc.



Site Meter

a href=