X Protocol, MySQL Shell, Document Store Concepts, and CRUD APIs

Learning outcomes

ServiceHub stores relational work orders, but equipment metadata can vary by device family: a compressor has pressure stages, a battery has chemistry and cycle count, and a robot has controller firmware and joint count. One option is a JSON column in a relational table. Another is MySQL Document Store, which exposes JSON document collections through the X Protocol and X DevAPI. The important design question is not whether CRUD feels “NoSQL-like”; it is how that interface coexists with the same MySQL server, security model, storage, transactions, and indexing responsibilities.

X Protocol is a second client interface, not a second database

The classic MySQL protocol normally serves SQL clients on port 3306. X Plugin exposes X Protocol, whose default port is normally 33060 when the plugin is enabled. MySQL Shell can speak either protocol. Document Store operations require an X Protocol session; connecting MySQL Shell through classic protocol does not create the same X DevAPI document context.

SELECT @@version AS server_version;SHOW VARIABLES LIKE 'mysqlx_port';SHOW VARIABLES LIKE 'mysqlx_bind_address';SELECT PLUGIN_NAME,PLUGIN_STATUS,PLUGIN_TYPE,PLUGIN_LIBRARYFROM information_schema.PLUGINSWHERE PLUGIN_NAME='mysqlx';SELECT USER(),CURRENT_USER(),CONNECTION_ID();SHOW SESSION STATUS LIKE 'Ssl_cipher';

Current MySQL 8.4 installs X Plugin by default, but configuration, packaging, startup options, or security policy can still make X Protocol unavailable. The evidence above tells you what this server is doing. If mysqlx_port is not 33060, use the observed value.

Create a narrow document-lab account

CREATE USER IF NOT EXISTS 'special_doc'@'127.0.0.1'  IDENTIFIED BY 'Disposable-Document-Lab-Only!';GRANT SELECT, INSERT, UPDATE, DELETE,      CREATE, DROP, ALTER, INDEXON servicehub_special_lab.*TO 'special_doc'@'127.0.0.1';SHOW GRANTS FOR 'special_doc'@'127.0.0.1';

Document Store does not bypass MySQL authentication or authorization. The account still has a MySQL user/host identity and privileges. A collection lives inside a schema (database), so schema-level privilege design remains relevant.

Create and query a collection with MySQL Shell

# Use the mysqlx_port value verified above (normally 33060).mysqlsh --mysqlx special_doc@127.0.0.1:33060/servicehub_special_lab# In MySQL Shell:\jsprint(session.uri)print(session.getConnectionId())

var db = session.getSchema('servicehub_special_lab');try { db.dropCollection('asset_docs'); } catch (e) {}var assets = db.createCollection('asset_docs');assets.add([  {_id:'asset-1001', type:'compressor', site:'BAKU-NORTH',   telemetry:{pressureBar:7.4, temperatureC:66.1}, tags:['critical','rotating']},  {_id:'asset-1002', type:'battery', site:'BAKU-HARBOR',   telemetry:{socPct:82, cycles:412}, tags:['mobile']},  {_id:'asset-1003', type:'robot', site:'GANJA-SVC',   controller:{firmware:'4.2.1', joints:6}, tags:['robotics','inspection']}]).execute();var r = assets.find('site = :site')              .bind('site','BAKU-NORTH')              .execute();print(r.fetchAll());assets.modify('_id = :id')      .bind('id','asset-1001')      .set('telemetry.pressureBar', 7.6)      .execute();print(assets.find('_id = :id').bind('id','asset-1001').execute().fetchOne());

Each document requires a unique _id. If the application omits it, the server can generate one. In this lab we provide stable IDs so later verification is deterministic. The filter uses a bound value rather than string concatenation, preserving the same structure-versus-data discipline used for SQL prepared statements.

Keep the relational representation visible

A collection stores JSON documents and can also be treated as a table through X DevAPI. This matters because “Document Store” does not mean the optimizer, indexes, durability, or operational lifecycle disappear. The schema can expose a collection as a table, and document fields can be addressed through JSON paths.

var assetsTable = db.getCollectionAsTable('asset_docs');var rows = assetsTable  .select(["doc->>'$.type' AS asset_type",           "doc->>'$.site' AS site_code"])  .where("doc->>'$.site' = :site")  .bind('site','BAKU-NORTH')  .execute();print(rows.fetchAll());print(db.getCollections());print(db.getTables());

For frequent predicates on a JSON path, design an indexable representation rather than expecting arbitrary document traversal to scale for free. X DevAPI supports collection indexes, and ordinary relational JSON patterns such as generated/functional indexes remain relevant. A document interface changes the programming model; it does not cancel physical design.

Document convenience still has relational physical-design consequences

A collection API can make document CRUD feel schema-flexible, but query cost still lands on MySQL storage structures. X DevAPI collection indexes are ordinary MySQL indexes built on generated/virtual columns that extract typed values from the JSON document. This matters operationally: the indexed document path must have a compatible, consistent type; text indexes require an explicit prefix length; index creation consumes storage and write work; and the optimizer still has to decide whether using the index is cheaper than scanning.

// Continue in MySQL Shell JavaScript mode over X Protocol.assets.createIndex('idx_site', {  fields: [{field: '$.site', type: 'TEXT(32)', required: true}]});session.runSql('SHOW INDEX FROM servicehub_special_lab.asset_docs');// Compare the application-facing query with the stored index contract.assets.find('site = :site')      .bind('site', 'BAKU-NORTH')      .fields('_id','asset_code','site','status')      .execute();

The important mental model is that Document Store does not bypass indexing theory from Chapters 8 and 9. It changes the API and data representation while retaining MySQL’s optimizer, InnoDB storage, locking, redo/undo, backup, privileges, and capacity costs underneath. If the document shape is highly variable, an index definition can also become a data-quality contract because values at that JSON path must map to the declared type.

Failure case: use classic protocol and expect Document Store globals

mysqlsh --mysql special_doc@127.0.0.1:3306/servicehub_special_lab\jsprint(db)# Expected lesson outcome:# classic protocol connects to MySQL, but the X DevAPI document workflow# used above is not established the same way. Reconnect with --mysqlx# and the verified mysqlx_port instead of randomly changing the server.

This is a useful failure because the server itself may be healthy. The defect is a client/protocol mismatch. Diagnose URI/protocol/port/plugin state and TLS/authentication separately; do not disable secure transport or grant broad privileges to make a protocol error disappear.

When Document Store is a good boundary

The right conclusion is often hybrid: relational columns for stable invariants, JSON or collections for flexible attributes, and explicit indexes for observed query paths.

Verification and cleanup checkpoint

var assets = db.getCollection('asset_docs');print('count =', assets.count());print(assets.find('_id = "asset-1001"').execute().fetchOne());// Leave the collection for Lessons 3–5 decision comparisons.// Final cleanup happens in Lesson 5.

Authoritative references

• MySQL Community Server 8.4 Downloads
• MySQL NDB Cluster 8.4 Downloads
• MySQL 8.4 — NDB Cluster
• MySQL 8.4 — InnoDB Compared with NDB Cluster
• MySQL 8.4 — Using MySQL as a Document Store
• MySQL Shell 8.4
• MySQL X DevAPI User Guide
• MySQL 8.4 — Spatial Analysis Functions
• MySQL 8.4 — Full-Text Search