AMBARI : UPGRADE 2.4 to 2.5.0.3 : DB inconsistency

[#1]

hi all,

could you tell me what i need to do with this error, do i need something to ambari database ?

vim /var/log/ambari-server/ambari-server-check-database.log

2017-04-06 14:35:24,471  INFO - Checking to ensure that the MySQL DB engine type is set to InnoDB
2017-04-06 14:35:24,473  INFO - Checking for configs that are not mapped to any service
2017-04-06 14:35:24,478  WARN - You have config(s): jupyter-env-version1481707689621,kerberos-env-version1477636924918,krb5-conf-version1477636924918,jupyter-env-version1481705311090,jupyter-env-version1481644424231,jupyter-env-version1481542656294,jupyter-env-version1481645331185,jupyter-env-version1481704038763,jupyter-env-version1481705933654,jupyter-env-version1481706438726,kerberos-env-version1483975893890,kerberos-env-version1483975894584,jupyter-env-version1481643716711,jupyter-env-version1481707236957 that is(are) not mapped (in serviceconfigmapping table) to any service!
2017-04-06 14:35:24,478  INFO - Checking for configs not mapped to any cluster

[#2]

@mayki wogno

The message that youa re getting is not an "Error" is it a "Warning" instead.

You can avoid it by telling ambari to not to perform the DB consistency check upon the ambari server restart as following:

# ambari-server start --skip-database-check

.

The Warning message indicates that your database has few inconsistency (although it will work with skip-database-check as it is not error) but that needs to be fixed in future. That will require a Ambari DB dump analysis and to manually fix those by running some SQL queries.

[#3]

If startet with that workaround there is warning:

Ambari Server is starting with the database consistency check skipped. Do not make any changes to your cluster topology or perform a cluster upgrade until you correct the database consistency issues. See "/var/log/ambari-server/ambari-server-check-database.log" for more details on the consistency issues.

But that "cluster upgrade" is exactly what i did. (after ignoring the Warning)

Additional after that an error message comes, but the ambari WebGUI is up.

Waiting for server start............................................................ERROR: Exiting with exit code 1.
REASON: Server not yet listening on http port 8080 after 50 seconds. Exiting.

What now?

Any advice on how to repair the db?

[#4]

Consistency checks were added to Ambari because there were few if any referential integrity checks in the ambari DB repository initially and unexpected situations do occur from time-to-time resulting in orphaned records in tables. By adding checks, the aim is to catch issues sooner rather than later. Some DB consistency check violations are not show-stoppers. You've probably had them for a while and you can live with them a while longer. You could try and unpick the orphaned records, backup and delete them. But you are fine continuing with them.

You can find these orphans using the following query in your mysql ambari database:

mysql> select config_id, version_tag, version, type_name from clusterconfig c where not exists (select 1 from serviceconfigmapping s where s.config_id = c.config_id);

You could take a backup of these and delete them.

As mentioned below there is a patch available that will sort his for you in Ambari 2.5.2.

You could wait for that.

[#5]

@mayki wogno

This is a bug and there is patch available. It will be fixed in Ambari 2.5.2.

https://issues.apache.org/jira/browse/AMBARI-20875

[#6]

hi,

I get similar issue. My ambari version is 2.6.2.0

hdp-2.6.4

ambari-server-checkdatabase.log

2018-05-31 20:54:20,699 INFO - Checking config group host mappings 2018-05-31 20:54:20,700 INFO - ******************************* Check database completed *******************************

2018-05-31 21:11:43,978 INFO - Checking DB store version

2018-05-31 21:11:44,829 INFO - DB store version is compatible

2018-05-31 21:11:49,292 INFO - ******************************* Check database started *******************************

2018-05-31 21:11:49,292 INFO - Checking to ensure that the MySQL DB engine type is set to InnoDB

2018-05-31 21:11:49,296 INFO - Checking for configs that are not mapped to any service

2018-05-31 21:11:49,302 WARN - You have config(s): livy-conf-version1,livy-spark-blacklist-version1,livy-log4j-properties-version1,livy-env-version1 that is(are) not mapped (in serviceconfigmapping table) to any service!

2018-05-31 21:11:49,302 INFO - Checking for configs selected more than once

2018-05-31 21:11:49,304 INFO - Checking for hosts without state

2018-05-31 21:11:49,305 INFO - Checking host component states count equals host component desired states count 2018-05-31 21:11:49,306 INFO - Checking services and their configs

2018-05-31 21:11:49,306 INFO - Getting ambari metainfo instance

2018-05-31 21:11:49,306 INFO - Executing query 'GET_SERVICES_WITHOUT_CONFIGS'

2018-05-31 21:11:49,307 INFO - Executing query 'GET_SERVICE_CONFIG_WITHOUT_MAPPING'

2018-05-31 21:11:49,308 INFO - Getting stack info from database

2018-05-31 21:11:49,308 INFO - Executing query 'GET_SERVICES_WITH_CONFIGS'

2018-05-31 21:11:49,310 INFO - Comparing service configs from stack with configs that we got from db

2018-05-31 21:11:49,310 INFO - Getting services from metainfo

2018-05-31 21:11:49,310 INFO - Comparing required service configs from stack with mapped service configs from db

2018-05-31 21:11:49,310 INFO - Getting services which has mapped configs which are not selected in clusterconfig

2018-05-31 21:11:49,312 INFO - Checking Topology tables

2018-05-31 21:11:49,313 INFO - Checking for tables with large physical size

2018-05-31 21:11:49,314 INFO - The database table host_role_command is currently 83.156 MB and is within normal limits (3000.000)

2018-05-31 21:11:49,315 INFO - The database table execution_command is currently 40.547 MB and is within normal limits (3000.000)

2018-05-31 21:11:49,315 INFO - The database table stage is currently 1.594 MB and is within normal limits (3000.000)

2018-05-31 21:11:49,316 INFO - The database table request is currently 1.531 MB and is within normal limits (3000.000)

2018-05-31 21:11:49,316 INFO - The database table alert_history is currently 94.719 MB and is within normal limits (3000.000)

2018-05-31 21:11:49,316 INFO - Checking config group host mappings 2018-05-31 21:11:49,316 INFO - ******************************* Check database completed *******************************

Thanks in advance for helping.....

[#7]

@S Mahapatra

Some of your Mysql database engine are MYISAM, to resolve that do the following

Assumption

• Ambari database=ambari
• MySql root password=test1234;
  
• Log in as root

# mysql -u root -ptest1234 

check the databases, choose your ambari database in my case ambari

mysql> show databases; 
mysql> use ambari; 

Check the tables that are MYISAM

mysql> SELECT TABLE_NAME,ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'ambari'; 

Run the below command for every single table that was marked as MyISAM replace xxx with table name from the above output

ALTER TABLE xxx ENGINE = InnoDB;

All your tables should now be using InnoDB engine

Now your database should be clean you can restart your Ambari server the DB inconsistency should be resolved

Hope that helps please revert

[#8]

Thanks @Geoffrey Shelton Okot for writing.

while migrating the mysql I have already changed it to InnoDB. You can see it here.

still the problem is there.

|serviceconfigmapping | InnoDB |

| servicedesiredstate | InnoDB |

| setting | InnoDB | |

stack | InnoDB |

| stage | InnoDB |

and so........

+-------------------------------+--------+

132 rows in set (0.00 sec)

[#9]

clusterconfigmapping from ambari db is missing which was there before I upgrade hdp2.6

[#10]

@S Mahapatra

Please find below the procedure to clean you orphaned Ambari configs

Create the temporary table to hold the orphaned configs

CREATE TEMPORARY TABLE IF NOT EXISTS orphaned_configs AS
(SELECT
cc.config_id,
cc.type_name,
cc.version_tag
FROM clusterconfig cc, clusterconfigmapping ccm
WHERE cc.config_id NOT IN (SELECT
scm.config_id
FROM serviceconfigmapping scm)
AND cc.type_name != 'cluster-env'
AND cc.type_name = ccm.type_name
AND cc.version_tag = ccm.version_tag); 

Delete the orphaned tables

DELETE FROM clusterconfigmapping
WHERE EXISTS
(SELECT 1 FROM orphaned_configs
WHERE clusterconfigmapping.type_name = orphaned_configs.type_name AND clusterconfigmapping.version_tag = orphaned_configs.version_tag); 

Delete the orphaned configs

DELETE FROM clusterconfig WHERE clusterconfig.config_id IN (SELECT config_id FROM orphaned_configs); 

Validate that the orphaned configs are gone

SELECT * FROM orphaned_configs; 

Drop the temporary table

DROP TABLE orphaned_configs;

Now you Ambari databases should be clean and all your configs properly mapped to the serviceconfigmapping table.

Please let me know if that worked well for you !!

[#11]

@S Mahapatra

Do you still have the backup/dump of your pre-upgrade Ambari MySQL?

Did you encounter any problems during the upgrade and simply ignored it? The desired output should have been like below

INFO: Upgrading database schema
INFO: Return code from schema upgrade command, retcode = 0
INFO: Schema upgrade completed
Adjusting ambari-server permissions and ownership...
Ambari Server 'upgrade' completed successfully.

Please have a look at these links

https://issues.apache.org/jira/browse/AMBARI-21836

You can download the diff https://reviews.apache.org/r/61963/