Create tables problem...
Jakkie3000
I was unable to create tables using pinba storage engine...
mysql says
ERROR 1005 (HY000): Can't create table 'pinba.request' (errno: 140)
mysql is 5.1.37
show plugins says that engine is active:
| PINBA | ACTIVE | STORAGE ENGINE | libpinba_engine.so | GPL
|
any ideas?
what if I make tables with myisam?
How to turn debuging in pinba so I can see what is the problem.
Hope you can help me
Thanks in advance,
Antony Dovgal
> Dear Developers,
>
> I was unable to create tables using pinba storage engine...
>
> mysql says
> ERROR 1005 (HY000): Can't create table 'pinba.request' (errno: 140)
> mysql is 5.1.37
> show plugins says that engine is active:
> | PINBA | ACTIVE | STORAGE ENGINE | libpinba_engine.so | GPL
> |
>
> any ideas?
How did you build the plugin and how did you build the server?
Please show me the configure options you used in both cases.
> what if I make tables with myisam?
They would be MyISAM, apparently.
> How to turn debuging in pinba so I can see what is the problem.
Any error messages in the log?
See log-error in your my.cnf.
--
Wbr,
Antony Dovgal
---
http://pinba.org - realtime statistics for PHP
Georg Tadeev
OS is fedora and most packages are installed by rpms.
I build mysql from source with following options
./configure --prefix=/usr/local/mysql
--localstatedir=/usr/local/mysql/data
--with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock
--enable-assembler \
--with-big-tables --with-debug --with-zlib-dir=bundled
--with-named-curses-libs=/usr/lib64/libncurses.a --without-docs
--without-geometry --without-man \
--without-ndb-binlog --without-ndb-debug --without-server --without-libedit
then I compiled pinba engine via configure
./configure --with-mysql=/a0/mysql-5.1.37 --with-judy --with-protobuf
--with-event --libdir=/usr/local/mysql/lib/mysql/plugin
After changing pinba engine to mysql, I was able to create tables;
But still no data is populated...
I didn't change any options in my.cnf, because defaults are ok.
Then I used tcpdump to see if UDP packets arrive at port... and they do arrive.
Does plugin need any configuration? Like db user & pass? which
database & table to use?
I think I am missing something...
Thank you in advance
Antony Dovgal
> After changing pinba engine to mysql, I was able to create tables;
> But still no data is populated...
No wonder - they _have_ to be of PINBA type, otherwise they're just usual tables.
>> Any error messages in the log?
So, do you see any error messages?
Georg Tadeev
./mysqld --defaults-file=/usr/local/mysql/etc/my.cnf --user=mysql
--log-warnings --log-error=/usr/local/mysql/mysqld.log --pinba=ON
no error in log file...
netstat -anp | grep 30002
udp 0 0 0.0.0.0:30002 0.0.0.0:*
2288/mysqld
pinba is listening for packets
mysql> show plugins
mysql> CREATE TABLE `request` ( `id` int(11) NOT NULL DEFAULT '0',
`hostname` varchar(16) DEFAULT NULL, `req_count` int(11) DEFAULT
NULL, `server_name` varchar(64) DEFAULT NULL, `script_name`
varchar(128) DEFAULT NULL, `doc_size` float DEFAULT NULL,
`mem_peak_usage` float DEFAULT NULL, `req_time` float DEFAULT NULL,
`ru_utime` float DEFAULT NULL, `ru_stime` float DEFAULT NULL,
`timers_cnt` int(11) DEFAULT NULL, `status` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)) ENGINE=PINBA DEFAULT CHARSET=latin1
COMMENT='request';
noting in log file nor console where mysqld is run... even with
log-warnings enabled
so as far as I cant create table with pinba engine, I'm stalled at this step.
I just wonder why I cant use tables of another type? What is the difference?
After pinba plugin receives and parses data from packet, it must fire
simple insert and why does it care type of the table?
There is no difference between inserts for myisam and innodb tables,
same inserts can go for blackhole engine too...
Can I edit code and change that strict requirement? I'm not worried
about performance issues, have plenty of resources to spend for pinba.
Thanks in advance,
Antony Dovgal
>
> OS is fedora and most packages are installed by rpms.
> I build mysql from source with following options
>
> ./configure --prefix=/usr/local/mysql
> --localstatedir=/usr/local/mysql/data
> --with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock
> --enable-assembler \
> --with-big-tables --with-debug --with-zlib-dir=bundled
> --with-named-curses-libs=/usr/lib64/libncurses.a --without-docs
> --without-geometry --without-man \
> --without-ndb-binlog --without-ndb-debug --without-server --without-libedit
--without-server ??
So how did you manage to build MySQL server without server?
Antony Dovgal
> so as far as I cant create table with pinba engine, I'm stalled at this step.
> I just wonder why I cant use tables of another type? What is the difference?
You miss the point completely.
Pinba tables are not just tables, they are interfaces to the data.
> After pinba plugin receives and parses data from packet, it must fire
> simple insert and why does it care type of the table?
It doesn't insert any data in any tables.
Strictly speaking, there are even no rows in these tables, they are dynamically generated on each request.
Otherwise all this stuff doesn't make any sense at all - I could use a simple script to
insert/delete the data into the tables instead of a custom storage engine.
> Can I edit code and change that strict requirement? I'm not worried
> about performance issues, have plenty of resources to spend for pinba.
No, you can't.
This is how it works.
Antony Dovgal
>
> OS is fedora and most packages are installed by rpms.
> I build mysql from source with following options
>
> ./configure --prefix=/usr/local/mysql
> --localstatedir=/usr/local/mysql/data
> --with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock
> --enable-assembler \
> --with-big-tables --with-debug --with-zlib-dir=bundled
> --with-named-curses-libs=/usr/lib64/libncurses.a --without-docs
> --without-geometry --without-man \
> --without-ndb-binlog --without-ndb-debug --without-server --without-libedit
Just built MySQL 5.1.37 with the same options (except --without-server, of course),
added libpinba_engine.so and created tables successfully.
Georg Tadeev
many options during make stage)
I will try all over again, thank you.
Georg Tadeev
I am failing again...
I will write down all steps, maybe I am doing something wrong...
1) building mysql from freshly extracted source
--localstatedir=/usr/local/mysql/data
--with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock
--enable-assembler \
--with-big-tables --with-debug --with-zlib-dir=bundled
--with-named-curses-libs=/usr/lib64/libncurses.a --without-docs
--without-geometry --without-man \
make install
./mysql_install_db --basedir=/usr/local/mysql
--builddir=/a0/mysql-5.1.37 --datadir=/usr/local/mysql/data
--user=mysql
run mysql ./mysqld --defaults-file=/usr/local/mysql/etc/my.cnf
--user=mysql --log-warnings --log-error=/usr/local/mysql/mysqld.log
2) compile pinba
NOTE: version 0.0.4 wont compile
configure: Regenerating protocol code
/usr/local/mysql/pinba_engine-0.0.4/pinba.proto: No such file or directory
configure: error: Failed to regenerate protocol code
downloaded latest version
./configure --with-mysql=/a0/mysql-5.1.37 --with-judy --with-protobuf
--with-event --libdir=/usr/local/mysql/lib/mysql/plugin && make &&
make install
then doing plugin install INSTALL PLUGIN pinba SONAME 'libpinba_engine.so'
in same mysql session show plugin to see pinba active.
same session creating db, create table fails, error message is same
but I am sure that I cant confuse versions because it's production and
password protected and it cant show pinba in show plugins, it woud
throw unknown engine pinba...
Still no error in file/console...
is there any option for more verbose logging for pinba? or for mysql?
It should have such flag... for debugging purposes...
I would greatly appreciate your help,
Antony Dovgal
> ./configure --prefix=/usr/local/mysql
> --localstatedir=/usr/local/mysql/data
> --with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock
> --enable-assembler \
> --with-big-tables --with-debug --with-zlib-dir=bundled
> --with-named-curses-libs=/usr/lib64/libncurses.a --without-docs
> --without-geometry --without-man \
> --without-ndb-binlog --without-ndb-debug --without-libedit && make &&
First, please try to reduce the number of options to the required minimum.
I'm fairly sure you don't need all of these in order to test it.
> 2) compile pinba
Please make sure you use the same MySQL sources you used for MySQL server, configured with the same options.
> NOTE: version 0.0.4 wont compile
> configure: Regenerating protocol code
> /usr/local/mysql/pinba_engine-0.0.4/pinba.proto: No such file or directory
> configure: error: Failed to regenerate protocol code
Oh, thanks a lot. I fixed that.
> same session creating db, create table fails, error message is same
> ERROR 1005 (HY000): Can't create table 'pinba.request' (errno: 140)
How _exactly_ do you create tables?
Using the mysql console? Are you sure you don't connect to the other MySQL instance by default?
> Still no error in file/console...
Which probably means that you don't use it at all.
> is there any option for more verbose logging for pinba? or for mysql?
See if you can get some info with this: http://dev.mysql.com/doc/refman/5.0/en/query-log.html
> It should have such flag... for debugging purposes...
>
> I would greatly appreciate your help,
You can, of course, use GDB to attach to MySQL process, set a backtrace at "ha_pinba::create"
function and see if it's called and where exactly it fails.
If there is a way for me to login into this machine, I can do it myself.
Georg Tadeev
failed again...
compiled mysql via
./configure --prefix=/usr/local/mysql
--localstatedir=/usr/local/mysql/data
--with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock
--with-big-tables --with-debug
compiled pinba
./configure --with-mysql=/a0/mysql-5.1.37 --with-event
be installed there as show plugins tell.
See screenshot and query log attached.
I was unable to use GDB:
[root@MASTER local]# gdbserver --attach comm 20976
Attached; pid = 20976
Could not open remote device: Invalid argument.
Killing inferior
I will try to get permission for your logon. anyway I must install it
on another production server.
Antony Dovgal
> Dear Antony
>
> failed again...
Too bad.
Then I can't help you without a shell to this server.
> I cant mix mysql instances, existing one is 5.0, and pinba plugin cant
> be installed there as show plugins tell.
> See screenshot and query log attached.
What do you see with `show engines` ?
Georg Tadeev
I have to postpone installation of pinba for couple weeks...
Great respect for creating great tool, I need it anyway, dead or alive :)
show engines:
| PINBA | YES | Pinba engine
| NO | NO | NO |
Antony Dovgal
> Thanks Antony for your time and help,
> I have to postpone installation of pinba for couple weeks...
> Great respect for creating great tool, I need it anyway, dead or alive :)
>
> show engines:
> | PINBA | YES | Pinba engine
> | NO | NO | NO |
>
Ok, I have more ideas then, need to look at it with GDB.
Alex Vorona
> ---
> Pinba Engine development mailing list
> To unsubscribe, send an email to pinba-engine...@googlegroups.com
> -~----------~----~----~----~------~----~------~--~---
>
>
Hello,
I have the same error with pinba_engine-201001301855 and mysql 5.1.42
Here are some gdb output
[root@xa1 ~]# gdb -p 28658
GNU gdb Fedora (6.8-37.el5)
Copyright (C) 2008 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later
<http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law. Type "show copying"
This GDB was configured as "i386-redhat-linux-gnu".
Attaching to process 28658
Reading symbols from /usr/sbin/mysqld...(no debugging symbols found)...done.
warning: .dynamic section for "/usr/lib/libz.so.1" is not at the
expected address
warning: difference appears to be caused by prelink, adjusting expectations
Reading symbols from /lib/libpthread.so.0...(no debugging symbols
found)...done.
[Thread debugging using libthread_db enabled]
[New Thread 0xb7ff06c0 (LWP 28658)]
[New Thread 0xa20fcb90 (LWP 30022)]
[New Thread 0xa212db90 (LWP 28670)]
[New Thread 0xa2b2eb90 (LWP 28669)]
[New Thread 0xa352fb90 (LWP 28668)]
[New Thread 0xb2243b90 (LWP 28667)]
[New Thread 0xb2c44b90 (LWP 28666)]
[New Thread 0xb3645b90 (LWP 28665)]
[New Thread 0xb4ed6b90 (LWP 28663)]
[New Thread 0xb58d7b90 (LWP 28662)]
[New Thread 0xb62d8b90 (LWP 28661)]
[New Thread 0xb7fefb90 (LWP 28660)]
Loaded symbols for /lib/libpthread.so.0
Reading symbols from /lib/libdl.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib/libdl.so.2
Reading symbols from /lib/libcrypt.so.1...(no debugging symbols
found)...done.
Loaded symbols for /lib/libcrypt.so.1
Reading symbols from /lib/libnsl.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib/libnsl.so.1
Reading symbols from /lib/libm.so.6...
(no debugging symbols found)...done.
Loaded symbols for /lib/libm.so.6
Reading symbols from /lib/libc.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib/libc.so.6
Reading symbols from /lib/ld-linux.so.2...(no debugging symbols
found)...done.
Loaded symbols for /lib/ld-linux.so.2
Reading symbols from /lib/libgcc_s.so.1...(no debugging symbols
found)...done.
Loaded symbols for /lib/libgcc_s.so.1
Reading symbols from /lib/libnss_files.so.2...(no debugging symbols
found)...done.
Loaded symbols for /lib/libnss_files.so.2
Reading symbols from /usr/lib/mysql/plugin/libpinba_engine.so...done.
Loaded symbols for /usr/lib/mysql/plugin/libpinba_engine.so
Reading symbols from /usr/lib/libprotobuf.so.6...done.
Loaded symbols for /usr/lib/libprotobuf.so.6
Reading symbols from /usr/lib/libevent-1.4.so.2...done.
Loaded symbols for /usr/lib/libevent-1.4.so.2
Reading symbols from /usr/lib/libJudy.so.1...done.
Loaded symbols for /usr/lib/libJudy.so.1
Reading symbols from /usr/lib/libstdc++.so.6...done.
Loaded symbols for /usr/lib/libstdc++.so.6
Reading symbols from /usr/lib/libz.so.1...done.
Loaded symbols for /usr/lib/libz.so.1
Reading symbols from /lib/librt.so.1...done.
Loaded symbols for /lib/librt.so.1
Reading symbols from /lib/libresolv.so.2...done.
Loaded symbols for /lib/libresolv.so.2
0x0088d402 in __kernel_vsyscall ()
(gdb) break ha_pinba::create
Breakpoint 1 at 0xa30a56: file ha_pinba.cc, line 60.
(gdb) continue
Continuing.
====================================================================
executing in mysql console (tag123 specially to distinguish from table name)
CREATE TABLE `tag` (
`id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=PINBA DEFAULT CHARSET=latin1 COMMENT='tag123';
====================================================================
Breakpoint 1, ha_pinba::create (this=0xa276b18, name=0xa20fb380
"./pinba/tag", table_arg=0xa20fa9e0, create_info=0xa20fb8b0) at
ha_pinba.cc:60
60 in ha_pinba.cc
(gdb) p *table_arg
$5 = {s = 0xa20fa590, file = 0xa276b18, next = 0x0, prev = 0x0, parent =
0x0, child_l = 0x0, child_last_l = 0x0, in_use = 0xa2280d0, field =
0xa252a18,
record = {0xa252910 "\2104[\bd\017\"\n", 0xa252910
"\2104[\bd\017\"\n"}, write_row_record = 0x0, insert_values = 0x0,
covering_keys = {map = 0},
quick_keys = {map = 0}, merge_keys = {map = 0}, keys_in_use_for_query
= {map = 0}, keys_in_use_for_group_by = {map = 0},
keys_in_use_for_order_by = {
map = 0}, key_info = 0xa252ae0, next_number_field = 0x0,
found_next_number_field = 0x0, timestamp_field = 0x0, triggers = 0x0,
pos_in_table_list = 0x0,
group = 0x0, alias = 0xa22b978 "", null_flags = 0xa252910
"\2104[\bd\017\"\n", bitmap_init_value = 0x0, def_read_set = {bitmap =
0xa252b78, n_bits = 2,
last_word_mask = 4294967292, last_word_ptr = 0xa252b78, mutex =
0x0}, def_write_set = {bitmap = 0xa252b7c, n_bits = 2, last_word_mask =
4294967292,
last_word_ptr = 0xa252b7c, mutex = 0x0}, tmp_set = {bitmap =
0xa252b80, n_bits = 2, last_word_mask = 4294967292, last_word_ptr =
0xa252b80,
mutex = 0x0}, read_set = 0xa20faa6c, write_set = 0xa20faa80,
query_id = 0, quick_rows = {0 <repeats 64 times>}, const_key_parts = {
0 <repeats 64 times>}, quick_key_parts = {0 <repeats 64 times>},
quick_n_ranges = {0 <repeats 64 times>}, quick_condition_rows = 0,
timestamp_field_type = TIMESTAMP_NO_AUTO_SET, map = 0, lock_position =
0, lock_data_start = 0, lock_count = 0, tablenr = 0, used_fields = 0,
temp_pool_slot = 0, status = 0, db_stat = 0, derived_select_number =
0, current_lock = 2, copy_blobs = 0 '\0', maybe_null = 0, null_row = 0 '\0',
force_index = 0 '\0', force_index_order = 0 '\0', force_index_group =
0 '\0', distinct = 0 '\0', const_table = 0 '\0', no_rows = 0 '\0',
key_read = 0 '\0', no_keyread = 0 '\0', open_placeholder = 0 '\0',
locked_by_logger = 0 '\0', no_replicate = 0 '\0', locked_by_name = 0 '\0',
fulltext_searched = 0 '\0', no_cache = 0 '\0', open_by_handler = 0
'\0', auto_increment_field_not_null = 0 '\0', insert_or_update = 0 '\0',
alias_name_used = 0 '\0', get_fields_in_item_tree = 0 '\0',
children_attached = 0 '\0', reginfo = {join_tab = 0x0, lock_type =
TL_UNLOCK,
not_exists_optimize = false, impossible_range = false}, mem_root =
{free = 0xa252900, used = 0xa276b08, pre_alloc = 0x0, min_malloc = 32,
block_size = 996, block_num = 6, first_block_usage = 0,
error_handler = 0x819b6a0 <sql_alloc_error_handler>}, grant =
{grant_table = 0x0, version = 0,
privilege = 0, want_privilege = 0, orig_want_privilege = 0}, sort =
{io_cache = 0x0, sort_keys = 0x0, buffpek = 0x0, buffpek_len = 0,
addon_buf = 0x0,
addon_length = 0, addon_field = 0x0, unpack = 0, record_pointers =
0x0, found_records = 0}, part_info = 0x0, no_partitions_used = false}
(gdb) p *create_info
$6 = {table_charset = 0x0, default_table_charset = 0x86c7860,
connect_string = {str = 0x0, length = 0}, password = 0x0, tablespace =
0x0, comment = {
str = 0xa2747a8 "tag123", length = 6}, data_file_name = 0x0,
index_file_name = 0x0, alias = 0xa2743c0 "tag", max_rows = 0, min_rows = 0,
auto_increment_value = 0, table_options = 9, avg_row_length = 0,
used_fields = 70144, key_block_size = 0, merge_list = {elements = 0,
first = 0x0,
next = 0x0}, db_type = 0xa21f810, row_type = ROW_TYPE_DEFAULT,
null_bits = 0, options = 0, merge_insert_method = 0, extra_size = 15,
unused1 = HA_CHOICE_UNDEF, table_existed = false, frm_only = false,
varchar = true, storage_media = HA_SM_DEFAULT, unused2 = HA_CHOICE_UNDEF}
(gdb) p table_arg->s->comment
$7 = {str = 0x0, length = 170004320}
(gdb) p create_info->comment
$8 = {str = 0xa2747a8 "tag123", length = 6}
(gdb)
====================================================================
Strange, but comment.str is empty in table_arg->s, while it's present
and correct in create_info
And of course I cannot create table with correct COMMENT='tag' too :)
Thanks,
Alex
Antony Dovgal
> I have the same error with pinba_engine-201001301855 and mysql 5.1.42
Give me full bt please.
Alex Vorona
> On 01/31/2010 12:11 AM, Alex Vorona wrote:
>
>> I have the same error with pinba_engine-201001301855 and mysql 5.1.42
>>
>
> Give me full bt please.
>
>
were for rhel5 while mysqld was 'generic linux'.
Thanks,
Alex
Antony Dovgal
Ah, you compiled Pinba with MySQL sources that didn't match the MySQL daemon?
That explains it, yes. I would expect weird crashes and other things in such case.