How do i manage tags in mysql?

I am building a web application where users will upload a file (XML format) with some data and some keywords/tags describing the data. This information will then be parsed and stored in MySQL database. Now i am not sure how to store these keywords/tags in the database and how to use them to retrieve appropriate data when the user enters keywords from the application frontend. I know i will have to build some kind of ranking algorithm to rank the keywords but my application is still in its initial stages, so right now I am more concerned about how to retrieve data than the efficiency/relevance of retrieved data.

Please help me or guide me to a resource which can help me.

Thanks!

amit

173k26 gold badges227 silver badges326 bronze badges

asked Jun 29, 2011 at 17:03

you can store JSON array in mysql>=5.7 for tags like below :

["tag1","tag2",...]

if you use an extra table for store tags , you need to join and search for adding a new tag , its bad way when we have too many tags in database !

answered Oct 11, 2016 at 9:35

Not the answer you're looking for? Browse other questions tagged mysql tags keyword or ask your own question.

From version 8.0.21, a configurable tag framework is available to allow the metadata of InnoDB Cluster, InnoDB ClusterSet, or InnoDB ReplicaSet to be marked with additional information. Tags make it possible to associate custom key-value pairs to a Cluster, ReplicaSet, or instance. Tags have been reserved for use by MySQL Router that enables a compatible MySQL Router to support hiding instances from applications. The following tags are reserved for this purpose:

• _hidden instructs MySQL Router: Excludes the instance from the list of possible destinations for client applications.

• _disconnect_existing_sessions_when_hidden: Instructs the router to disconnect existing connections from instances that are marked to be hidden.

For more information, see Removing Instances from Routing.

In addition, the tags framework is user-configurable. Custom tags can consist of any ASCII character and provide a namespace, which serves as a dictionary key-value pairs that can be associated with Clusters, ReplicaSets, or their specific instances. Tag values can be any JSON value. This configuration enables you to add your own attributes on top of the metadata.

Showing Tags

The Cluster.options() operation shows information about the tags assigned to individual cluster instances as well as to the cluster itself. For example, the InnoDB Cluster assigned to myCluster could show:

mysql-js> myCluster.options()
{
    "cluster": {
        "name": "test1",
        "tags": {
            "ic-1:3306": [
                {
                    "option": "_disconnect_existing_sessions_when_hidden",
                    "value": true
                },
                {
                    "option": "_hidden",
                    "value": false
                }
            ],
            "ic-2:3306": [],
            "ic-3:3306": [],
            "global": [
                {
                    "option": "location:",
                    "value": "US East"
                }
            ]
        }
    }
}

This cluster has a global tag named location which has the value US East, and instance ic-1 has been tagged.

Setting Tags on a Cluster Instance

You can set tags at the instance level, which enables you for example to mark an instance as not available, so that applications and router treat it as offline. Use the Cluster.setInstanceOption(instance, option, value) operation to set the value of a tag for the instance.

The instance argument is a connection string to the target instance. The option argument must be a string with the format namespace:option. The value parameter is the value that should be assigned to option in the specified namespace. If the value is null, the option is removed from the specified namespace. For instances which belong to a cluster, the setInstanceOption() operation only accepts the tag namespace. Any other namespace results in an ArgumentError.

For example, to use JavaScript to set the tag test to true on the myCluster instance ic-1, issue:

mysql-js> myCluster.setInstanceOption("icadmin@ic-1:3306", "tag:test", true);

Or using Python to set the tag test to true on the myCluster instance ic-1, issue:

mysql-py> myCluster.set_instance_option("icadmin@ic-1:3306", "tag:test", True);

Removing Instances from Routing

When AdminAPI and MySQL Router are working together, they support specific tags that enable you to mark instances as hidden and remove them from routing. MySQL Router then excludes such tagged instances from the routing destination candidates list. This functionality enables you to safely take a server instance offline so that applications and MySQL Router ignore it. For example, while you perform maintenance tasks, such as a server upgrade or configuration changes.

When the _hidden tag is set to true, this instructs MySQL Router to exclude the instance from the list of possible destinations for client applications. The instance remains online, but is not routed to for new incoming connections. The _disconnect_existing_sessions_when_hidden tag controls how existing connections to the instance are closed. This tag is assumed to be true, and it instructs any MySQL Router instances bootstrapped against the InnoDB Cluster, InnoDB ClusterSet, or InnoDB ReplicaSet to disconnect any existing connections from the instance when the _hidden tag is true.

When _disconnect_existing_sessions_when_hidden is false, any existing client connections to the instance are not closed if _hidden is true. The reserved _hidden and _disconnect_existing_sessions_when_hidden tags are specific to instances and cannot be used at the cluster level.

Warning

When the use_gr_notifications MySQL Router option is enabled, it defaults to 60 seconds. This means that when you set tags, it takes up to 60 seconds for MySQL Router to detect the change. To reduce the waiting time, change use_gr_notifications to a lower value.

For example, suppose you want to remove the ic-1 instance which is part of an InnoDB Cluster assigned to myCluster from the routing destinations. Use the setInstanceOption() JavaScript operation to enable the _hidden and _disconnect_existing_sessions_when_hidden tags:

mysql-js> myCluster.setInstanceOption("icadmin@ic-1:3306", "tag:_hidden", true);

Or use the set_instance_option() Python operation to enable the _hidden and _disconnect_existing_sessions_when_hidden tags:

			mysql-js> myCluster.set_instance_option("icadmin@ic-1:3306", "tag:_hidden", true);

You can verify the change in the metadata by checking the options. For example the change made to ic-1 would show in the options as:

mysql-js> myCluster.options()
{
    "cluster": {
        "name": "test1",
        "tags": {
            "ic-1:3306": [
                {
                    "option": "_disconnect_existing_sessions_when_hidden",
                    "value": true
                },
                {
                    "option": "_hidden",
                    "value": true
                }
            ],
            "ic-2:3306": [],
            "ic-3:3306": [],
            "global": []
        }
    }
}

You can verify that MySQL Router has detected the change in the metadata by viewing the log file. A MySQL Router that has detected the change made to ic-1 would show a change such as:

2020-07-03 16:32:16 metadata_cache INFO [7fa9d164c700] Potential changes detected in cluster 'testCluster' after metadata refresh
2020-07-03 16:32:16 metadata_cache INFO [7fa9d164c700] view_id = 4, (3 members)
2020-07-03 16:32:16 metadata_cache INFO [7fa9d164c700]     ic-1:3306 / 33060 - mode=RW
2020-07-03 16:32:16 metadata_cache INFO [7fa9d164c700]     ic-1:3306 / 33060 - mode=RO
2020-07-03 16:32:16 metadata_cache INFO [7fa9d164c700]     ic-1:3306 / 33060 - mode=RO hidden=yes disconnect_when_hidden=yes
2020-07-03 16:32:16 routing INFO [7fa9d164c700] Routing routing:testCluster_x_ro listening on 64470 got request to disconnect invalid connections: metadata change
2020-07-03 16:32:16 routing INFO [7fa9d164c700] Routing routing:testCluster_x_rw listening on 64460 got request to disconnect invalid connections: metadata change
2020-07-03 16:32:16 routing INFO [7fa9d164c700] Routing routing:testCluster_rw listening on 6446 got request to disconnect invalid connections: metadata change
2020-07-03 16:32:16 routing INFO [7fa9d164c700] Routing routing:testCluster_ro listening on 6447 got request to disconnect invalid connections: metadata change

To bring the instance back online, use the setInstanceOption() operation to remove the tags, and MySQL Router automatically adds the instance back to the routing destinations, and it becomes online for applications. For example:

mysql-js> myCluster.setInstanceOption(icadmin@ic-1:3306, "tag:_hidden", false);

Verify the change in the metadata by checking the options again:

mysql-js> myCluster.options()
{
    "cluster": {
        "name": "test1",
        "tags": {
            "ic-1:3306": [
                {
                    "option": "_disconnect_existing_sessions_when_hidden",
                    "value": true
                },
                {
                    "option": "_hidden",
                    "value": false
                }
            ],
            "ic-2:3306": [],
            "ic-3:3306": [],
            "global": []
        }
    }
}

Setting Tags on a Cluster

The Cluster.setOption(option, value) operation enables you to change the value of a namespace option for the whole cluster. The option argument must be a string with the format namespace:option.

The value parameter is the value to be assigned to option in the specified namespace. If the value is null, the option is removed from the specified namespace. For Clusters, the setOption() operation accepts the tag namespace. Any other namespace results in an ArgumentError.

Tip

Tags set at the cluster level do not override tags set at the instance level. You cannot use Cluster.setOption() to remove all tags set at the instance level.

There is no requirement for all the instances to be online, only that the cluster has quorum. To tag the InnoDB Cluster assigned to myCluster with the location tag set to US East, issue the following in JavaScript:

mysql-js> myCluster.setOption("tag:location", "US East")
mysql-js> myCluster.options()
{
    "cluster": {
        "name": "test1",
        "tags": {
            "ic-1:3306": [],
            "ic-2:3306": [],
            "ic-3:3306": [],
            "global": [
                {
                    "option": "location:",
                    "value": "US East"
                }
            ]
        }
    }
}

Or issue the following in Python:

mysql-py> myCluster.set_option("tag:location", "US East")
mysql-pys> myCluster.options()
{
    "cluster": {
        "name": "test1",
        "tags": {
            "ic-1:3306": [],
            "ic-2:3306": [],
            "ic-3:3306": [],
            "global": [
                {
                    "option": "location:",
                    "value": "US East"
                }
            ]
        }
    }
}

Removing Tags from a Cluster

To remove a tag from a Cluster, use the Cluster.setOption(option, value) operation with a null value in JavaScript, and a None value in Python.

To remove the tag from InnoDB Cluster assigned to myCluster with the location tag, issue the following in JavaScript:

mysql-js> myCluster.setOption("tag:location", null)

Or using Python:

mysql-js> myCluster.set_option("tag:location", None)

User Defined Tagging

AdminAPI supports the tag namespace, where you can store information in the key-value pairs associated with a given Cluster, ReplicaSet or instance. The options under the tag namespace are not constrained, meaning you can tag with whatever information you choose, as long as it is a valid MySQL ASCII identifier.

You can use any name and value for a tag, as long as the name follows the following syntax: _ or letters followed by alphanumeric and _ characters.

The namespace option is a colon separated string with the format namespace:option, where namespace is the name of the namespace and option is the actual option name. You can set and remove tags at the instance level, or at the Cluster or ReplicaSet level.

Tag names can have any value as long as it starts with a letter or underscore, optionally followed by alphanumeric and _ characters, for example, ^[a-zA-Z_][0-9a-zA-Z_]*. Only built-in tags are allowed to start with the underscore _ character.

How you use custom tags is up to you. You could set a custom tag on a Cluster to mark the location of the Cluster. For example, set a custom tag named location with a value of EMEA on the Cluster.

How to use tag in MySQL?

How do you store tags in SQL?

How do I store multiple tags in a database?

Can I store object in MySQL?