mySQL authetication after upgrade

Hi,

It appears that the authentication behaviour when connecting to a mySQL-based database from a Windows client has changed after upgrading the databases to IJC 5.4. When trying to create a New Schema in the first step I get the following error message:

'...: host is not allowed to connect to this MySQL server'

Before the upgrade this was not a problem. Interestingly things work as they should when connecting from a Linux client. Some googling tought me that I can work around this by adding 'root@%' to the allowed hosts on the mySQL server, but then all connections from Windows clients get root access, and the IJC user login step when creating a new schema is skipped.

Please advice.

Hi,

the only thing that I can think of that has caused this is an update to the MySQL JDBC driver that was done for 5.4. No change in IJC itself would have casued this. As such, I don't think we can do anything in IJC about it, as its just a matter of configuring the security within MySQL.

As you are connecting from multiple clients then you will definitely need a setting like 'user@%' as an allowed host, where 'user' is the DB user that you are using. However it is definitely not advised to use the root account for this. Much better to create a special 'ijc' user in MySQL for this purpose, specify 'ijc@%' as an allowed host and grant the ijc user access to only the particular MySQL schema that you are using. That way you can connect from any client, but not access anything in the db other than what you need.

Tim

Hi Tim,

I followed your advice and created a common user with global access. However, when reconnecting to the databases I need to promote all tables in order to be able to see/use them. Do I need to do this separately for all clients and users?

Thanks,

Evert

Hi Homer,

no you should not need to do this. All entities an data tree should be visible to all user of a an IJC schema. In contract views (e..g form or grid view) belong to the individual IJC user so will not be seen unless they are shared.

I think there are two things that might explain this:

1. you have connected to a diferent MySQL schema within the database (e.g the last part of the connection url). I assume this is not the case as you would see different tables form beefore, but it probably worth mentioning.

2. You used the 'New schema' wizard instead of the 'connect to schema' wizard. New schema create a completely new IJC schmea instance in the DB and will need tales promoting. Connect to schema will 'pick up' an exisiting IJC schema and will have all the entities and data trees that were already there present.

So if you use 'Connect to schema' then I suspect you will see multiple schemas, the old one and the new (empty) one.

Tim

 

Hi,

Looks like scenario 2 applies. How can I safely remove redundant (old) schemes?

Evert

the easiest apporach if probably to do this directly in the database.

First identify the IJC schemas that you want to delete (of the one you want to keep). You can find the ID of the schema in IJC in the schema settings (r-click menu for the schema in the projects window). The ID is the 32 character hexadecimal string.

Then in the database look at the IJC_SCHEMA table and identify the rows for the unwanted schema. To find all the schemas use this:

select ITEM_ID from IJC_SCHEMA where GENERIC_TYPE = 'SCHEMA'

Then delete the schemas you don't want.

delete from IJC_SCHEMA where ITEM_ID = '<your_schema_id>'

 

Tim

Many thanks, this worked nicely!

Cheers,

Evert