|
Did you check the error log? Are there any warnings or errors in there?
Could you have a disk space problem on the machine?
Is it a newly created table, or are you using an old one (converting it to different engines as you said)?
If it's an old table, does it work for other tables?
Please paste the output of SHOW CREATE TABLE for the table and question, an example of the INSERT statement which fails, and attach your cnf file(s) or the output of SHOW VARIABLES.
Thanks.
|
|
Hi
1. no the own message i get is the one i coppied above
2. disk an memory on this vm is huge bigger than the sizes needed by this write access.
This was what i thougt as well and therefore i already testet serveral memory an storage settings up an down, and also the storage engines memory, innodb and myisam. but that does not play any role at all.
3. this table is a memory table, and newly created, The import job does first a truncate on this table, empty up all( and this, i can always see, wen write something in this table, after job start it is empty again )
4. table schema
MariaDB [cacti]> show create table poller_command;
|
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
Table Create Table
|
|
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
poller_command CREATE TABLE `poller_command` (
|
`poller_id` smallint(5) unsigned NOT NULL DEFAULT '0',
|
`time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
|
`action` tinyint(3) unsigned NOT NULL DEFAULT '0',
|
`command` varchar(200) NOT NULL DEFAULT '',
|
PRIMARY KEY (`poller_id`,`action`,`command`)
|
) ENGINE=MyISAM DEFAULT CHARSET=latin1
|
|
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
======= original dump of this table =========================
-- Table structure for table `poller_output`
|
–
|
|
DROP TABLE IF EXISTS `poller_output`;
|
SET @saved_cs_client = @@character_set_client;
|
SET character_set_client = utf8;
|
CREATE TABLE `poller_output` (
|
`local_data_id` mediumint(8) unsigned NOT NULL default '0',
|
`rrd_name` varchar(19) NOT NULL default '',
|
`time` datetime NOT NULL default '0000-00-00 00:00:00',
|
`output` varchar(1000) NOT NULL,
|
PRIMARY KEY (`local_data_id`,`rrd_name`,`time`)
|
) ENGINE=MEMORY DEFAULT CHARSET=latin1;
|
SET character_set_client = @saved_cs_client;
|
=================================================
but as i mentioned, i changed the storage engine serval times for tests, and exact that works with the mysql 5.0.67-log SUSE MySQL RPM without any error. The php scripts confused me a bit, but what i cut see in the logoutput i could do always manual without any problem on mariadb, even when keys are double tha works.
vars.selma.20170823.log vars.leap42.2-test.20170823.log
selma is the production system and leapxxx ist a testvm
5. i still try to get a somehow more usefull var log out of the php script ( cacti boost ) which contains the full sql commands.
6. The fact that the old mysql work, without any problem and the newer mariadb not at all, whatever i tried, sql mode, mem, .. is confusing me.
Thanks
|
|
sorry the right table output from selma is
MariaDB [cacti]> show create table poller_output;
|
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| poller_output | CREATE TABLE `poller_output` (
|
`local_data_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
|
`rrd_name` varchar(19) NOT NULL DEFAULT '',
|
`time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
|
`output` varchar(1000) NOT NULL,
|
PRIMARY KEY (`local_data_id`,`rrd_name`,`time`)
|
) ENGINE=MEMORY DEFAULT CHARSET=latin1 |
|
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [cacti]>
|
the errors belongs till now only about the table poller_output in the cacti db
Thenaks
|
|
Thanks not Thenaks sorry
|
|
Hi
Oh and the four tables
plugin_flowview_dnscache
poller_output
poller_output_boost
poller_output_boost_processes
i not even copied the data from the old system, i omly cretated these tables new on maridb
because they just contains temporary data for the boostplugin to work data in to the other tables. the boost plugin shall speedup the data import job
Tanks
|
|
and the settings of the former working system
tanks vars.patty.20170823.log
Server version: 5.0.67-log SUSE MySQL RPM
thanks
|
|
How much data are you trying to load into the table?
Based on your server settings and table definition, and assuming that you re-created the table with these settings, not just truncated it, you should be able to load approx. 1M of rows, after which it will rightfully report being full.
Please execute on your 10.0 server (via the command-line client) and paste the unabridged output of the following:
DROP TABLE IF EXISTS `poller_output`;
|
SET @saved_cs_client = @@character_set_client;
|
SET character_set_client = utf8;
|
CREATE TABLE `poller_output` (
|
`local_data_id` mediumint(8) unsigned NOT NULL default '0',
|
`rrd_name` varchar(19) NOT NULL default '',
|
`time` datetime NOT NULL default '0000-00-00 00:00:00',
|
`output` varchar(1000) NOT NULL,
|
PRIMARY KEY (`local_data_id`,`rrd_name`,`time`)
|
) ENGINE=MEMORY DEFAULT CHARSET=latin1;
|
SET character_set_client = @saved_cs_client;
|
|
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'poller_output' \G
|
|
INSERT INTO `poller_output` VALUES (1,'foo',NOW(),'bar');
|
Regarding your 5.0 instance, please also run there
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'poller_output' \G
|
SHOW CREATE TABLE poller_output \G
|
SELECT COUNT(*) FROM poller_output;
|
|
|
Hi
i did these steps an here are the outputs
test02.log test03.log
and i started again a data input with the same result error 1114 table poller_output full.
For info i tock sevaral times bevore some data, what these job wants to import and that also worked without problem, but the php job could never import any date at all only on the old mysql and i dont know why.
thanks
|
|
Hi and can write in not my job
test04.log
|
|
So, the table is functional, rows can get inserted there; but MEMORY tables have a limitation on the amount of data you can insert into them.
Your local settings seem to be different on selma vs the other two servers, when said about 1M rows earlier, I referred to selma settings. On the machine from which you collected the last output, settings are different, there you can insert something about 15,800 rows. So, I have to ask again – how much data are you trying to insert into the table?
|
|
Hi
i dit a select count from the poller_output table each 2 sec and the max rows which a could see was 5847 rows in the runnig old mysql system.
thanks
|
|
Hi again
the biggest number a could see till now was 7356 rows.
where can i read out this information from a table and whre can i encrease this setting.
thanks
|
|
Try to set a bigger value for @@max_heap_table_size. On 10.0, you can run this:
TRUNCATE TABLE poller_output;
|
SET @@max_heap_table_size = 1073741824, @@global.max_heap_table_size = 1073741824;
|
ALTER TABLE poller_output FORCE;
|
And then try to load your data again.
|
|
Hi tried that but still
ERROR: SQL Failed! Error:'1114', Message:'The table 'poller_output' is full',
thanks
test08.log
|
|
Please right after you got the error (without doing anything else), run again SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'poller_output'\G and paste the output.
|
|
Hi i did that
and see result, first the old mysql patty and then the testsystem called zeus
test09.log
thanks
|
|
Hi
what means DATA_FREE: 0
thanks
|
|
Hi have you got an idea what data free 0 in a table means?
thanks
|
|
You are running a number of selects from I_S at seemingly random times, it is impossible to say what means when and where, because we don't know what you are doing with the table between those runs. Which result was right after you got the error? Why are there so many different results?
Could you please do exactly as asked, run consequently
- DROP TABLE
- CREATE TABLE
- SELECT from I_S
- LOAD DATA (get the error)
- SELECT from I_S
from the same connection or at least from the same terminal, one by one, without anything else in between, and paste the whole output?
And yet another time, could you please answer – how much data are you trying to insert into the table and how are you doing it?
|
|
Hi what do you mean with ist I_S ?
and the client ist a php modul from cacti. I tried to get a usefull log from these scripts, exacly a dump of certain vers, but that ist still somhow a problem.
thanks
|
|
> Hi what do you mean with ist I_S ?
INFORMATION_SCHEMA
|
|
Hi ja im thinking how can i get that done, because thats a job, and i dont have an exact log of all sql commands, produced from this job. This job runs one time per minute.After import to poller_output next steps to work in the data and than produce data for rrd database.
A couple days before i did an full strace from this php job, but the own what i could see was a resource conflict on a unix socket, but thats not unusual. Probably another strace with the setting of a very long line and that produce gigabytes of trace data.
But what makes me still confused is, why does that run on with an 5 years old mysql without any problem?
thanks
|
|
Hi is there a posibility in mariadb to log all commands ( sql ) which mariadb got from a client, specially the insert commands. in the query log i can see only queries, and oly the successful ones.
thanks
|
|
I don't know what you mean by query log.
If you run
SET GLOBAL general_log_file=<path and name>;
|
SET GLOBAL general_log=1;
|
It will log all queries received from the clients in the given file. Make sure that the user under which mysqld is running has writing permissions for the given location.
|
test02.log