HonoredMule's questions

I have a working Percona database server running multiple databases with mostly InnoDB tables (maybe a few hundred tables total). Any table that exists is working fine, yet I cannot convert any existing MyISAM tables or even create new InnoDB tables in any schema (even new fresh ones).

In fact I can trigger the crash (Error Code: 2013. Lost conenction to MySQL server during query) as minimally as this:

create schema testschema;
use testschema;
create table testtable (
    testfield int(10)
);

(InnoDB is the default engine, and I get the same result making the engine explicit, while specifying MyISAM creates the table successfully.

What gives?

Some diagnostics:

cat /proc/$(pgrep mysqld$)/limits
...
Max open files            8192                 8192                 files
...

Starting from the crash, (large) error log has these possibly notable lines

2016-12-19 19:06:43 7f3880047700  InnoDB: Assertion failure in thread 139880642672384 in file fsp0fsp.cc line 1899
...
23:06:43 UTC - mysqld got signal 6 ;
...
stack_bottom = 7f3880046ea0 thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x3b)[0x8f4b9b]
/usr/sbin/mysqld(handle_fatal_signal+0x378)[0x647468]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x11390)[0x7f388cf34390]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x38)[0x7f388c2d8428]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x16a)[0x7f388c2da02a]
/usr/sbin/mysqld[0x55f90b]
...
/usr/sbin/mysqld[0x9bfcd4]
/usr/sbin/mysqld(_Z15ha_create_tableP3THDPKcS2_S2_P24st_ha_create_informationbb+0x1fe)[0x58b9be]
/usr/sbin/mysqld(_Z16rea_create_tableP3THDPKcS2_S2_P24st_ha_create_informationR4ListI12Create_fieldEjP6st_keyP7handlerb+0x1d3)[0x771033]
/usr/sbin/mysqld[0x72bb5e]
/usr/sbin/mysqld(_Z26mysql_create_table_no_lockP3THDPKcS2_P24st_ha_create_informationP10Alter_infojPb+0x114)[0x72c734]
/usr/sbin/mysqld(_Z18mysql_create_tableP3THDP10TABLE_LISTP24st_ha_create_informationP10Alter_info+0xdf)[0x72c98f]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x5696)[0x6d9596]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x604)[0x6daa64]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x19c2)[0x6dc542]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x245)[0x6a0bf5]
/usr/sbin/mysqld(handle_one_connection+0x39)[0x6a0c49]
/usr/sbin/mysqld(pfs_spawn_thread+0x140)[0x98da00]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7f388cf2a6ba]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f388c3a982d]
...
Query (7f3804004c10): is an invalid pointer
Connection ID (thread ID): 76
Status: NOT_KILLED
...
161219 19:06:43 mysqld_safe Number of processes running now: 0
161219 19:06:43 mysqld_safe mysqld restarted

A restart occurs which performs rollback and ens successfully, though the log does mention this InnoDB: Last MySQL binlog file position 0 18814, file name /storage/logs/mysql/mysql-bin.000005 and there is no binlog set up (this path no longer exists either).

ignore_builtin_innodb......................... OFF
innodb_adaptive_flushing...................... ON
innodb_adaptive_flushing_lwm.................. 10
innodb_adaptive_hash_index.................... ON
innodb_adaptive_hash_index_partitions......... 1
innodb_adaptive_max_sleep_delay............... 150000
innodb_additional_mem_pool_size............... 8388608
innodb_api_bk_commit_interval................. 5
innodb_api_disable_rowlock.................... OFF
innodb_api_enable_binlog...................... OFF
innodb_api_enable_mdl......................... OFF
innodb_api_trx_level.......................... 0
innodb_autoextend_increment................... 64
innodb_autoinc_lock_mode...................... 1
innodb_buffer_pool_dump_at_shutdown........... OFF
innodb_buffer_pool_dump_now................... OFF
innodb_buffer_pool_filename................... ib_buffer_pool
innodb_buffer_pool_instances.................. 8
innodb_buffer_pool_load_abort................. OFF
innodb_buffer_pool_load_at_startup............ OFF
innodb_buffer_pool_load_now................... OFF
innodb_buffer_pool_populate................... OFF
innodb_buffer_pool_size....................... 805306368
innodb_change_buffer_max_size................. 25
innodb_change_buffering....................... all
innodb_checksum_algorithm..................... innodb
innodb_checksums.............................. ON
innodb_cleaner_lsn_age_factor................. high_checkpoint
innodb_cmp_per_index_enabled.................. OFF
innodb_commit_concurrency..................... 0
innodb_compression_failure_threshold_pct...... 5
innodb_compression_level...................... 6
innodb_compression_pad_pct_max................ 50
innodb_concurrency_tickets.................... 5000
innodb_corrupt_table_action................... assert
innodb_data_file_path......................... ibdata1:10M:autoextend
innodb_data_home_dir.......................... 
innodb_disable_sort_file_cache................ OFF
innodb_doublewrite............................ ON
innodb_empty_free_list_algorithm.............. backoff
innodb_fake_changes........................... OFF
innodb_fast_shutdown.......................... 1
innodb_file_format............................ Antelope
innodb_file_format_check...................... ON
innodb_file_format_max........................ Antelope
innodb_file_per_table......................... ON
innodb_flush_log_at_timeout................... 1
innodb_flush_log_at_trx_commit................ 1
innodb_flush_method........................... 
innodb_flush_neighbors........................ 1
innodb_flushing_avg_loops..................... 30
innodb_force_load_corrupted................... OFF
innodb_force_recovery......................... 0
innodb_foreground_preflush.................... exponential_backoff
innodb_ft_aux_table........................... 
innodb_ft_cache_size.......................... 8000000
innodb_ft_enable_diag_print................... OFF
innodb_ft_enable_stopword..................... ON
innodb_ft_max_token_size...................... 84
innodb_ft_min_token_size...................... 3
innodb_ft_num_word_optimize................... 2000
innodb_ft_result_cache_limit.................. 2000000000
innodb_ft_server_stopword_table............... 
innodb_ft_sort_pll_degree..................... 2
innodb_ft_total_cache_size.................... 640000000
innodb_ft_user_stopword_table................. 
innodb_io_capacity............................ 200
innodb_io_capacity_max........................ 2000
innodb_kill_idle_transaction.................. 0
innodb_large_prefix........................... OFF
innodb_lock_wait_timeout...................... 50
innodb_locking_fake_changes................... ON
innodb_locks_unsafe_for_binlog................ OFF
innodb_log_arch_dir........................... ./
innodb_log_arch_expire_sec.................... 0
innodb_log_archive............................ OFF
innodb_log_block_size......................... 512
innodb_log_buffer_size........................ 8388608
innodb_log_checksum_algorithm................. innodb
innodb_log_compressed_pages................... ON
innodb_log_file_size.......................... 50331648
innodb_log_files_in_group..................... 2
innodb_log_group_home_dir..................... ./
innodb_lru_scan_depth......................... 1024
innodb_max_bitmap_file_size................... 104857600
innodb_max_changed_pages...................... 1000000
innodb_max_dirty_pages_pct.................... 75
innodb_max_dirty_pages_pct_lwm................ 0
innodb_max_purge_lag.......................... 0
innodb_max_purge_lag_delay.................... 0
innodb_mirrored_log_groups.................... 1
innodb_monitor_disable........................ 
innodb_monitor_enable......................... 
innodb_monitor_reset.......................... 
innodb_monitor_reset_all...................... 
innodb_old_blocks_pct......................... 37
innodb_old_blocks_time........................ 1000
innodb_online_alter_log_max_size.............. 134217728
innodb_open_files............................. 512
innodb_optimize_fulltext_only................. OFF
innodb_page_size.............................. 16384
innodb_print_all_deadlocks.................... OFF
innodb_purge_batch_size....................... 300
innodb_purge_threads.......................... 1
innodb_random_read_ahead...................... OFF
innodb_read_ahead_threshold................... 56
innodb_read_io_threads........................ 4
innodb_read_only.............................. OFF
innodb_replication_delay...................... 0
innodb_rollback_on_timeout.................... OFF
innodb_rollback_segments...................... 128
innodb_sched_priority_cleaner................. 19
innodb_show_locks_held........................ 10
innodb_show_verbose_locks..................... 0
innodb_sort_buffer_size....................... 1048576
innodb_spin_wait_delay........................ 6
innodb_stats_auto_recalc...................... ON
innodb_stats_method........................... nulls_equal
innodb_stats_on_metadata...................... OFF
innodb_stats_persistent....................... ON
innodb_stats_persistent_sample_pages.......... 20
innodb_stats_sample_pages..................... 8
innodb_stats_transient_sample_pages........... 8
innodb_status_output.......................... OFF
innodb_status_output_locks.................... OFF
innodb_strict_mode............................ OFF
innodb_support_xa............................. ON
innodb_sync_array_size........................ 1
innodb_sync_spin_loops........................ 30
innodb_table_locks............................ ON
innodb_thread_concurrency..................... 0
innodb_thread_sleep_delay..................... 10000
innodb_track_changed_pages.................... OFF
innodb_undo_directory......................... .
innodb_undo_logs.............................. 128
innodb_undo_tablespaces....................... 0
innodb_use_atomic_writes...................... OFF
innodb_use_global_flush_log_at_trx_commit..... ON
innodb_use_native_aio......................... ON
innodb_use_sys_malloc......................... ON
innodb_version................................ 5.6.22-71.0
innodb_write_io_threads....................... 4

Basically what I'm looking for is a way to tell mysql (percona-flavored) to take the following steps:

• Flush all committed transactions to the filesystem
• Start buffering all transactions (keep the filesystem in a coherent state)
• Wait for me to do something (take a zfs snapshot)
• Unbuffer writes and resume normal operations

A solution which allows DDL statements to break the backup are acceptable (because this backup solution will be applied frequently and DDL statements will be very infrequent - worst-case scenario would be having to roll back a db one hour more than expected).

Backup operations should be minimally disruptive to performance, but this is not a system under heavy load. Solutions which perform this procedure per-database or system-wide are both fine, though the latter is somewhat preferred.

I'm trying to configure Percona with LDAP support via PAM including group mappings and restriction. My Percona authentication setup is as follows:

INSTALL PLUGIN pam SONAME 'auth_pam.so';

create user 'dba'@'%' IDENTIFIED WITH auth_pam AS 'mysql';
grant all privileges on *.* to 'dba'@'%' with grant option;

create user 'dbr'@'%' IDENTIFIED WITH auth_pam AS 'mysql';
grant select on *.* to 'dbr'@'%';

CREATE USER ''@'' IDENTIFIED WITH auth_pam AS 'mysql, sudo=dba, mysql=dbr';
GRANT PROXY ON 'dba'@'%' TO ''@'';
GRANT PROXY ON 'dbr'@'%' TO ''@'';

And in /etc/pam.d/mysql I have:

auth required pam_warn.so
auth required pam_ldap.so
account required pam_ldap.so
account required pam_succeed_if.so user ingroup mysql

With these in place, the following works:

• Log in as an LDAP user in sudo (and mysql), gain privileges of dba.
• Log in as an LDAP user in mysql, gain privileges of dbr.
• Attempt login with invalid username and/or password, get no access.

And the following behaviors are incorrect (should be refused login entirely):

• Log in as an LDAP user in sudo (but not mysql), still gain privileges of dba.
• Log in as an LDAP user in neither group, gain access with no privileges.

It took quite a while to eventually figure out that the configuration in /etc/pam.d/mysql is having no effect. The behavior is the same even when that file is not present.

My understanding is that the first value in the string mysql, sudo=dba, mysql=dbr tells Percona to use the PAM configuration in a file named mysql, and all tutorials show the filename resolving to /etc/pam.d/mysql.

What am I missing?

I have a storage server running Nexenta (OpenSolaris kernel, Ubuntu userspace) with MySQL on top of a ZFS storage array, using innodb_file_per_table and ulimit -n set to 8K. mysqltuner.pl confirms the file limit and claims there are 169 files.

The following command:

pfiles `fuser -c / 2>/dev/null

indicates one mysqld process having 485 file/device descriptors (and they're almost all for files) so I don't know how reliable the tuning script is, but it is still way less than 8K and this list also finds no other process which is close to it's limit. The global total number of descriptors in use is around 1K.

So what can cause mysqld to be constantly streaming the following errors?

[date] [host] mysqld[pid]: warning: cannot open /etc/hosts.allow: Too many open files
[date] [host] mysqld[pid]: warning: cannot open /etc/hosts.deny: Too many open files

Everything appears to actually be operating fine, but the issue is constantly flooding the admin console and starts right away on a fresh boot (not only reproducible, but always from mysqld and always the hosts files, whose permissions are the default -rw-r--r-- 1 root root). I could, of course, suppress it from the admin console but I'd rather get to the bottom of it and still allow mysqld warnings/errors to reach the admin console.

EDIT: not only is the actual file descriptor well within sane limits, the issue also persists (with immediate appearance) even with the file limit raised to 65535 and always only on hosts.allow/deny.

I'm interested in running a reverse proxy with 2-3 virtual machines behind it. Each internal server will run multiple virtual hosts, and rather than manually configuring each individual vhost on the proxy (a variety of vhosts come and go too often for this to be practical), I would like to use something which can employ pattern matching in a sequential order to find the appropriate back-end server. For example:

Server 1: *.dev.mysite.com
Server 2: *.stage.mysite.com
Server 3: *.mysite.com, dev.mysite.com, stage.mysite.com, mysite.com
Server 4: *

In the above configuration, task.dev.mysite.com would go to Server 1, dev.mysite.com would go to Server 3, yoursite.stage.mysite.com to Server 2, www.mysite.com to Server 3, and yoursite.com to Server 4.

I've looked into using Squid, Varnish, and nginx so far. I have my opinions regarding their respective desirability and general suitability, but it's not readily apparent if any of them can handle dynamic server selection in this manner and not require per-vhost configuration. Apache on the other hand can do this handily and simply, but otherwise (aside from being well-known and familiar) seems very poorly suited to the partly-performance-serving task.

Performance isn't actually a major concern yet, but it seems foolish to use Apache if another system will perform far better and can also handle the desired 'hands-free' configuration. But so is frequently having to adjust the gateway for all production services and risk network-wide outage...and so also is setting oneself up for longer downtime later if Apache becomes a too-small bottleneck.

Which of these (or other) reverse proxies can do it/would do it best?

And maybe I should post this as a separate question, but if Apache is the only practical option, how safe/reliable/predictable is apache-mpm-event in apache2.2 (Ubuntu 12.04.1) particularly for a dedicated reverse proxy? As I understand it the Event MPM was declared "safe" as of 2.4 but it's unclear whether reaching stability in 2.4 has any implications for the older (2.2) versions available in official/stable package channels of various distros.

Is it possible to enable a Linux or OpenSolaris based server to process standard unix login requests without spinning up the hard drive holding the root partition?

I have a Nexenta-based (1) server that uses a very old 40GB PATA drive for the root partition which could not reside in the main storage array (nor would I want it to, though it does back up to it). It performs well because everything that actively accesses storage uses the SATA raid2z array. This means that the root partition itself is actually completely inactive and the 40GB disk is usually left asleep.

Or rather, almost completely inactive. Login requests require the drive to spin up, causing both annoying login delays and unnecessary strain on this old drive which could otherwise remain completely dormant.

As far as I can tell, the drive is only accessed to read the passwd and shadow files, and I'm surprised that these do not remain cached in memory after first being read and thereafter unmodified. Is it possible to explicitly keep them cached, relocate them elsewhere, or otherwise prevent the root partition from having to spin up? (2)

1. For those unfamiliar, Nexenta is a variant of Ubuntu that uses the OpenSolaris kernel and tool chain while supporting the Debian tool chain as much as possible. For the most part, I have access to the capabilities of both.
2. Please note that I do not consider a suitable solution anything that breaks logins or bootup when the storage array is not present.