Assuming my dimDate has a surrogate key. Should all the date columns in the *dimensional tables* (not the fact tables) store the surrogate key of date dimension? Or just plain date? For example, in dimensional table dimCustomer, there may be birthday, join date, graduation date, .... Etc.

I'm tuning the server in order to speed up several SQL queries generated by a product and that can't be modified or tuned themselves. I'm stuck on a query that creates a CTO an then an INSERT / SELECT statement w/ a ON CONFLICT clause, this is the execution plan : Insert on data_location.md_associe_interlo_coord_red t (cost=0.57..592368.31 rows=0 width=0) (actual rows=0 loops=1) Conflict Resolution: UPDATE Conflict Arbiter Indexes: pkmd_associe_interlo_coord_red Conflict Filter: (t.b_batchid Index Scan using i2mi_associe_interlo_coord_red on data_location.mi_associe_interlo_coord_red s (cost=0.57..592368.31 rows=1343311 width=1187) (actual rows=1341390 loops=1) Output: s.b_pubid, s.b_sourceid, s.b_classname, s.b_batchid, (COALESCE(s.b_credate, '2025-08-01 08:43:28.853'::timestamp without time zone))::timestamp(6) without time zone, (COALESCE(s.b_upddate, '2025-08-01 08:43:28.853'::timestamp without time zone))::timestamp(6) without time zone, COALESCE(s.b_creator, 'semadmin'::character varying), COALESCE(s.b_updator, 'semadmin'::character varying), s.b_matchgrp, s.b_xgrp, s.b_confirmationstatus, NULL::numeric, NULL::character varying, s.b_hassuggmerge, s.b_suggmergeid, s.b_suggmergeconfscore, NULL::character varying, s.b_suggmergemasterscount, s.associe_interlo_coord_red, NULL::numeric, s.b_confirmedsdpk, s.num_compte_externe, s.siret, s.siren, s.nir, NULL::date, s.type_interlocuteur, NULL::integer, s.f_redevabilite, s.fp_redevabilite, s.fs_redevabilite, s.f_interlocuteur, s.fp_interlocuteur, s.fs_interlocuteur, s.f_coordonnee, s.fp_coordonnee, s.fs_coordonnee, s.id_golden_redevabilite, NULL::integer, s.dt_maj, s.code_organisme_source Index Cond: (s.b_batchid = '52615'::numeric) Filter: ((s.b_classname)::text = 'AssocieInterloCoordRede'::text) Buffers: shared hit=9 read=239313 written=57 Settings: effective_cache_size = '43GB', effective_io_concurrency = '50', max_parallel_workers = '4', random_page_cost = '2', search_path = '"$user", public, extensions', work_mem = '1GB' Table mi_associe_interlo_coord_red has +131M records (+140GB on disk) and md_associe_interlo_coord_red +129M records has (+81GB on disk). Colonne | Type | Collationnement | NULL-able | Par défaut | Stockage | Compression | Cible de statistiques | Description ---------------------------+--------------------------------+-----------------+-----------+------------+----------+-------------+-----------------------+------------- b_pubid | character varying(30) | | not null | | extended | | | b_sourceid | character varying(128) | | not null | | extended | | | b_classname | character varying(128) | | not null | | extended | | | b_batchid | numeric(38,0) | | not null | | main | | | b_credate | timestamp(6) without time zone | | | | plain | | | b_upddate | timestamp(6) without time zone | | | | plain | | | b_creator | character varying(128) | | | | extended | | | b_updator | character varying(128) | | | | extended | | | b_pendingactions | character varying(30) | | | | extended | | | b_matchgrp | numeric(38,0) | | | | main | | | b_oldmatchgrp | numeric(38,0) | | | | main | | | b_xgrp | uuid | | | | plain | | | b_confirmationstatus | character varying(30) | | | | extended | | | b_confscore | numeric(38,0) | | | | main | | | b_confscoretype | character varying(30) | | | | extended | | | b_hassuggmerge | boolean | | | | plain | | | b_suggmergeid | numeric(38,0) | | | | main | | | b_suggmergeconfscore | numeric(38,0) | | | | main | | | b_suggmergeconfscoretype | character varying(30) | | | | extended | | | b_suggmergemasterscount | numeric(38,0) | | | | main | | | associe_interlo_coord_red | numeric(38,0) | | | | main | | | b_oldsdpk | numeric(38,0) | | | | main | | | b_confirmedsdpk | numeric(38,0) | | | | main | | | num_compte_externe | character varying(18) | | | | extended | | | siret | character varying(14) | | | | extended | | | siren | character varying(9) | | | | extended | | | nir | character varying(13) | | | | extended | | | dtmaj | date | | | | plain | | | type_interlocuteur | character varying(128) | | | | extended | | | f_redevabilite | numeric(38,0) | | | | main | | | fp_redevabilite | character varying(30) | | | | extended | | | fs_redevabilite | character varying(128) | | | | extended | | | f_interlocuteur | numeric(38,0) | | | | main | | | fp_interlocuteur | character varying(30) | | | | extended | | | fs_interlocuteur | character varying(128) | | | | extended | | | f_coordonnee | numeric(38,0) | | | | main | | | fp_coordonnee | character varying(30) | | | | extended | | | fs_coordonnee | character varying(128) | | | | extended | | | id_golden_redevabilite | character varying(128) | | | | extended | | | dt_maj | date | | | | plain | | | code_organisme_source | character varying(3) | | | | extended | | | Index : "pkmi_associe_interlo_coord_red" PRIMARY KEY, btree (b_pubid, b_sourceid) "fkmi_associe_interlocuteur_re2" btree (fp_interlocuteur, fs_interlocuteur) "fkmi_associe_interlocuteur_re3" btree (fp_coordonnee, fs_coordonnee) "fkmi_associe_interlocuteur_red" btree (fp_redevabilite, fs_redevabilite) "i1mi_associe_interlo_coord_red" btree (associe_interlo_coord_red) "i2mi_associe_interlo_coord_red" btree (b_batchid) "i3mi_associe_interlo_coord_red" btree (b_matchgrp) "i4mi_associe_interlo_coord_red" btree (b_suggmergeid) "i5mi_associe_interlo_coord_red" btree (b_xgrp) "usr_idx_mi_associe_interlocuteur_red_b_pendingactions" btree (b_pendingactions) "usr_mi_associe_interlo_coord_red_b_batchid_idx" btree (b_batchid, b_classname, b_pubid, b_sourceid, fp_coordonnee, fs_coordonnee, fp_interlocuteur, fs_interlocuteur, fp_redevabilite, fs_redevabilite) "usr_mi_associe_interlo_coord_red_fp_fs_type" btree (fp_interlocuteur, fs_interlocuteur, type_interlocuteur) "usr_mi_associe_interlo_coord_red_id_golden_redevabilite_idx" btree (id_golden_redevabilite, type_interlocuteur) Méthode d'accès : heap Options: autovacuum_enabled=true, fillfactor=70 Colonne | Type | Collationnement | NULL-able | Par défaut | Stockage | Compression | Cible de statistiques | Description ---------------------------+--------------------------------+-----------------+-----------+------------+----------+-------------+-----------------------+------------- b_pubid | character varying(30) | | not null | | extended | | | b_sourceid | character varying(128) | | not null | | extended | | | b_classname | character varying(128) | | not null | | extended | | | b_batchid | numeric(38,0) | | not null | | main | | | b_credate | timestamp(6) without time zone | | | | plain | | | b_upddate | timestamp(6) without time zone | | | | plain | | | b_creator | character varying(128) | | | | extended | | | b_updator | character varying(128) | | | | extended | | | b_matchgrp | numeric(38,0) | | | | main | | | b_xgrp | uuid | | | | plain | | | b_confirmationstatus | character varying(30) | | | | extended | | | b_confscore | numeric(38,0) | | | | main | | | b_confscoretype | character varying(30) | | | | extended | | | b_hassuggmerge | boolean | | | | plain | | | b_suggmergeid | numeric(38,0) | | | | main | | | b_suggmergeconfscore | numeric(38,0) | | | | main | | | b_suggmergeconfscoretype | character varying(30) | | | | extended | | | b_suggmergemasterscount | numeric(38,0) | | | | main | | | associe_interlo_coord_red | numeric(38,0) | | not null | | main | | | b_oldsdpk | numeric(38,0) | | | | main | | | b_confirmedsdpk | numeric(38,0) | | | | main | | | num_compte_externe | character varying(18) | | | | extended | | | siret | character varying(14) | | | | extended | | | siren | character varying(9) | | | | extended | | | nir | character varying(13) | | | | extended | | | dtmaj | date | | | | plain | | | type_interlocuteur | character varying(128) | | | | extended | | | f_redevabilite | numeric(38,0) | | | | main | | | fp_redevabilite | character varying(30) | | | | extended | | | fs_redevabilite | character varying(128) | | | | extended | | | f_interlocuteur | numeric(38,0) | | | | main | | | fp_interlocuteur | character varying(30) | | | | extended | | | fs_interlocuteur | character varying(128) | | | | extended | | | f_coordonnee | numeric(38,0) | | | | main | | | fp_coordonnee | character varying(30) | | | | extended | | | fs_coordonnee | character varying(128) | | | | extended | | | id_golden_redevabilite | character varying(128) | | | | extended | | | dt_maj | date | | | | plain | | | code_organisme_source | character varying(3) | | | | extended | | | Index : "pkmd_associe_interlo_coord_red" PRIMARY KEY, btree (b_pubid, b_sourceid) "fkmd_associe_interlocuteur_re2" btree (fp_interlocuteur, fs_interlocuteur) "fkmd_associe_interlocuteur_re3" btree (fp_coordonnee, fs_coordonnee) "fkmd_associe_interlocuteur_red" btree (fp_redevabilite, fs_redevabilite) "i1md_associe_interlo_coord_red" btree (associe_interlo_coord_red) "i2md_associe_interlo_coord_red" btree (b_suggmergeid) "usr_idx_md_associe_interlocuteur_red_b_pendingactions" btree (b_batchid) Méthode d'accès : heap Server has 64GB RAM and 16 vCores, concurrency is low, typcally 3/4 postgres process running. Workload is mostly huge batches, and very little OLTP. Adding memory is not an option because of the company policies Create a covering index is not an option because of the limit of 27 for columns in INCLUDE clause. I tried to recreate the PK by including the b_batchid column (to avoid heap read while handling the conflict I thought) but it didn't help How does it handle the conflict ? why the cost of the upsert is 0?

I set up a new standalone mongo2.6 and copy one folder(database) from another machine to its data path (because I enabled directoryperdb). Then I set up another machine to make them as a replica set. After rs.initiate and data sync finished, I found the local file size on primary and secondary are: primary: 232G secondary: 316G I found similar question here https://stackoverflow.com/questions/14843229/mongodb-replica-set-disk-size-difference-in-primary-and-secondary-nodes But as the above answer, the primary should use more disk space than the secondary. I need to migrate about 25T data from the old mongo 2.6 cluster. If every database's local file size takes so much more disk space in the secondary, then I need to take this as a spec consideration in the newly bought machine. :(

We want to upgrade to MySQL 8.0 from MySQL 5.6 but upgrade manual seems to cumbersome and risky to follow doing an in-place upgrade so we have decided to do it via MySQL Workbench export (mysqldump) and import our exported database tables into a fresh new MySQL 8.0 installation Should it be successful or should we do something else in the middle for it to work?

I have two clustered Microsoft SQL Servers (SQLA & SQLB) installed, and confirmed that both of the servers have an ODBC connector for a local PostgreSQL server. From that ODBC connection, I have a linked server created for use in some stored procedures that fails at least once a fortnight with this error message: > Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "POSTGRESP23" When troubleshooting the issues, the ODBC connector on both SQLA and SQLB tests successfully from the System DSN menu on the server; the error originates from the linked server. Currently, to fix this for convenience and lower downtime I am just deleting the linked server and remaking it, pointing it to the same ODBC object. However, this is not a sustainable process. Can anyone suggest where to look when troubleshooting? As I'm at a loss. **Additional Information** psqlODBC_X64 is installed on both machines already from https://odbc.postgresql.org/ System DSN settings: Linked Server settings: EXEC master.dbo.sp_addlinkedserver @server = N'POSTGRESP23', @srvproduct=N'', @provider=N'MSDASQL', @datasrc=N'PSQLPROD' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'POSTGRESP23',@useself=N'False',@locallogin=NULL,@rmtuser=N'postgres',@rmtpassword='########' EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'collation compatible', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'data access', @optvalue=N'true' EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'dist', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'pub', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'rpc', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'rpc out', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'sub', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'connect timeout', @optvalue=N'0' EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'collation name', @optvalue=null EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'lazy schema validation', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'query timeout', @optvalue=N'0' EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'use remote collation', @optvalue=N'true' EXEC master.dbo.sp_serveroption @server=N'POSTGRESP23', @optname=N'remote proc transaction promotion', @optvalue=N'true'

Table_A is an existing table which has 3 columns Id (PK), Name, Stock. I would like to create a trigger to create a table. For example, Table_A has an item, Id (PK) = 3, Name = Apple, Stock = 10. By using triggers I would like to create a new table called Table_B and when I write the following code "INSERT INTO Table_B VALUES(3,5)" a new table will be created and it will have 2 columns ProductId and Quanity. At the same time the value for Stock for Table_A will be decreased by 5. Every time I insert something to Table_B I would like them to get added to Table_B and the stock in Table_A to be decreased by the quantity of that item inserted with the related ProductId. Thanks!

we want to have a an entz in the postgres logs upon execution of the archive_command. Someone came up with this archive_command which should achieve this test ! -f /opt/db/backup/postgres/archives/%f && echo $(date "+%F %T.%3N %Z") [$$] ARCH: archiving WAL: %f && cp %p /opt/db/backup/postgres/archives/%f The goal is to have a line telling us which wal file was archived at what time. it throws an error though

2025-08-07 18:06:44.184 CEST    FATAL:  invalid value for parameter "archive_command": "test ! -f /opt/db/backup/postgres/archives/%f && echo $(date "+%F %T.%3N %Z")
[$$] ARCH: archiving WAL: %f && cp %p /opt/db/backup/postgres/archives/%f"
2025-08-07 18:06:44.184 CEST    DETAIL:  String contains unexpected placeholder "%F".

Any idea how to adjust the archive command in order to get this going correctly?

I have a table of users with ids in the following form user123@domain.com. When searching and sorting the users, I need user1@domian.com to be before user14@domain.com but since 4 is “smaller” than @ it sorts the other way around. After looking around I came across the following collation: CREATE COLLATION numeric (provider = icu, locale = 'en-u-kn-true'); Which fixed the problem, but caused another one, users with numbers that started with 2 appeared after users that started with 1 For example, user19@domain.com came before user2@domain.com From the docs, it says that kn-true and looks at the number’s value but it looks like it only compares the digits. I'm using Postgres 11.6. I have two servers: one on Windows (local) and a remote one on Linux. Is anyone familiar with this issue?

There is DB with unknown amount of corrupted unique indexes. Currently I discover them one by one when I try REINDEX DATABASE CONCURRENTLY , handle the specific duplication violation (manipulating data, mainly delete the duplications using scripts), reindex the specific table or index, and continue to the next index (again only right after I discover it using REINDEX DATABASE CONCURRENTLY). Not mentioning that each time I get indexes with the '_ccnew' suffix, that AFAIK are indexes that were tried to be created by a previous reindex concurrently but couldn’t be done, usually because they are violating a uniqueness check. The failed attempts to reindex concurrently will sit in there and should be dropped manually. **Concurrent** reindex is used in order to prevent a shutdown. I want to reduce those "roundtrips" of searching the next violation and wonder if there is a more efficient way to get a general data about the status of all the index corruptions or unique violations that a Postgres DB has.

I’ve got one server with two SQL instances. The server has 128 GB RAM. - Instance 1 has peak load from 08:00–10:00 - Instance 2 has peak load from 14:00–17:00 Currently: - Instance 1 has max server memory = 70 GB - Instance 2 has max server memory = 50 GB - The rest of the day, both are nearly idle. Would it make sense to dynamically shift memory? give more to one instance during its peak while reducing it on the other? Any downsides to doing that with a scheduled script?

As far as I understand, SPI_getbinval returns a pointer into the passed row. That is, the following would be unsafe:

dat = SPI_getbinval(SPI_tuptable->vals, SPI_tuptable->tupdesc, 1, &isnull);
SPI_finished();
if (isnull)
    PG_RETURN_NULL();
// Unsafe since SPI_finished() deallocates SPI_tuptable
return dat;

The SPI interface provides the utility SPI_copytuple. Is the following safe? Is there any problem with using SPI_tuptable->tupdesc? Moreover, is there a more efficient way to do this directly with the returned Datum instead of copying the entire HeapTuple?

// We copy the whole tuple into the outer context.  Better way to copy an individual Datum?
dat = SPI_getbinval(SPI_copytuple(SPI_tuptable->vals), SPI_tuptable->tupdesc, 1, &isnull);
SPI_finished();
if (isnull)
  PG_RETURN_NULL();
return dat;

We have a Firewalled storage account which has a few files that we need to access via Synapse notebook. We do that with below code : abfss_path = f'abfss://container@storageaccount.dfs.core.windows.net/data.csv' df = spark.read.load(abfss_path, format='csv',header=True) display(df.limit(10)) The managed identity and my user (objectid) for the Synapse workspace has ' Storage Blob Data Contributor' role on the storage account. The below have been enabled: - Allow Azure services on the trusted services list to access this storage account. - Specify resource instances that will have access to your storage account based on their system-assigned managed identity. Running the above code works from the pipeline with a synapse notebook task but **fails when run from the studio through the notebook cell**. Error : ***Operation failed: "This request is not authorized to perform this operation.", 403*** I have tried everything but can't get it to work. Via Pipeline Via Studio Notebook Cell I can see AAD (pass through) takes precedence when running from studio, so I tried running the session as 'managed Identity'. This doesn't change anything and I keep getting same error. How to get this working? Note : I am not using the managed vnet in my synapse workspace.

I am establishing Multi Master Replication [All mater can write to their database], Initially I started with two server. After establishing connection and sync data on both servers. There are some questions which are bothering me like 1. If there is connection lost between two server and at that time both updates the same row or create a row with same primary. There will be collision between them and sync will break. 2. Can we implement same things for Multiple Master configuration? 3. Is there any monitoring and conflict resolution tools which can I use?

I have the following trigger that outputs a number in the format such as 170200005 where 17 is the year and 02 is the month (February) and changes as months changes e.g March 03 then the last digits are incremental. BEGIN SET @var1 = LPAD((SELECT MONTH(CURRENT_DATE())), 2, '0'); SET @var2 = (SELECT YEAR(CURRENT_DATE)); SET @var2 = (SELECT MID(@var2, 3, 2)); SET @var1 = CONCAT(@var2, @var1); INSERT INTO table1_seq VALUES (NULL); SET NEW.id = CONCAT(@var1, LPAD(LAST_INSERT_ID(), 5, '0')); END I would like to have a code that will output in the same format as highlighted before but then starts from 00001 whenever the month changes.

looks like i do it wrong. I turned auto_explain on with adding next valued to postgresql.conf: shared_preload_libraries = 'pg_stat_statements,pg_wait_sampling,pg_stat_kcache,auto_explain' auto_explain.log_min_duration = '3s' in my monitoring I can see long running queries that exceeded limit of 3s and successfully finished. 'values' here says how long query lasted: But when I look into my log 'postgresql-Mon.log' (and next days) there is nothing about those queries. Only a few strings about modules during database startup process. Am I missing something?

Is there a way to query a DB2 Linked Server without using Openquery? IE, is it possible to query DB2 through a linked server by doing something like the following? select * from [db2_linked_server].[database].[tablename]

I am getting pooler error: query_wait_timeout for different databases in the pgbouncer log 6 to 7 times a day . When the error occurs for masterdata database , it gives me a timeout in the application. The configuration is below :

: "md5"
  default_pool_size: 10
  max_client_conn: 5000
  max_db_connections: 400
  min_pool_size: 0
  pool_mode: "transaction"
  reserve_pool_size: 20
  reserve_pool_timeout: 1
  query_timeout: 0
  ignore_startup_parameters: "search_path, extra_float_digits"
  server_reset_query: "DISCARD ALL"
  server_reset_query_always: 1

As the query timeout is disabled (query_timeout:0) and the pool_mode is set to transaction, why am I still having this error? Do I need to set some min_pool_size for my masterdata database to eliminate the above error?

I'm getting the following error: > postgres@:~/>cat loadable_libraries.txt could not load library >"$libdir/plpython2": ERROR: could not access file >"$libdir/plpython2": No such file or directory In database: db_name When trying to run pg_upgrade from PostgreSQL-11 to PostgreSQL-13. /usr/pgsql-13/bin/pg_upgrade -b /usr/pgsql-11/bin -B /usr/pgsql-13/bin -c -d /localpart0/db/postgres/11/db_name/data -D/localpart0/db/postgres/13/db_name/data -j 20 -r -v --clone I don't have any plpython* extensions and already tried changing the pg_pltemplate entries for plpythonu and plpython2u to plpython3 and run the pg_upgrade but it fails either way. Installed Packages: > postgresql11-plpython.x86_64 11.7-1PGDG.rhel7 @postgres11.prd > postgresql13-plpython3.x86_64 13.1-1PGDG.rhel7 @postgres13.prd Any guidance would be greatly appreciated.

According to [the release note of MySQL 9.4](https://dev.mysql.com/doc/relnotes/mysql/9.4/en/news-9-4-0.html) , *JSON duality views* are now supported. [*27.7.1 JSON Duality View Syntax*](https://dev.mysql.com/doc/refman/9.4/en/json-duality-views-syntax.html) shows this example:

CREATE TABLE customers (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    street VARCHAR(50),
    city VARCHAR(25),
    state CHAR(2),
    postcode CHAR(5),
    phone CHAR(10),
    email VARCHAR(50)
);

CREATE JSON DUALITY VIEW jdv_customers
  AS
    SELECT JSON_DUALITY_OBJECT( {
                '_id'   : c.id,
                'name'  : { 'fname' : c.first_name,
                            'lname' : c.last_name },
                'email' : c.email,
                'area'  : { 'city'  : c.city,
                            'state' : c.state }
                }
            )
FROM customers c;

Let me test that:

> SELECT VERSION();

+-----------+
| VERSION() |
+-----------+
| 9.4.0     |
+-----------+

> CREATE TABLE customers (
->     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
->     first_name VARCHAR(25),
->     last_name VARCHAR(25),
->     street VARCHAR(50),
->     city VARCHAR(25),
->     state CHAR(2),
->     postcode CHAR(5),
->     phone CHAR(10),
->     email VARCHAR(50)
-> );

Query OK, 0 rows affected

> CREATE JSON DUALITY VIEW jdv_customers
->   AS
->     SELECT JSON_DUALITY_OBJECT( {
->                 '_id'   : c.id,
->                 'name'  : { 'fname' : c.first_name,
->                             'lname' : c.last_name },
->                 'email' : c.email,
->                 'area'  : { 'city'  : c.city,
->                             'state' : c.state }
->                 }
->             )
-> FROM customers c;

(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{\n                '_id'   : c.id,\n                'name'  : { 'fname' : c.first_' at line 3")

Why? Is this just a documentation bug or am I missing something? --- Edit: Reading [the documentation of JSON_DUALITY_OBJECT()](https://dev.mysql.com/doc/refman/9.4/en/json-creation-functions.html#function_json-duality-object) , it seems - {} of JSON_DUALITY_OBJECT( { ... } ) found in the example is incorrect. We must omit that. - Nested object { ... } found in the example is incorrect. Instead we have to specify a subquery like this:

'name' : (
      SELECT JSON_DUALITY_OBJECT(
        /* ... */
      )
      FROM customers AS c2
      WHERE c2.id = c.id
    ),

- And the documentation also says > If the table is projected multiple times, the set of columns projected must be consistent across all instances of the table projection. , meaning that top-level query and all of the subqueries must repeat the same set of columns. Following these rules gives:

CREATE JSON DUALITY VIEW jdv_customers AS
SELECT JSON_DUALITY_OBJECT(
  '_id'        : c.id,
  'first_name' : c.first_name,
  'last_name'  : c.last_name,
  'email'      : c.email,
  'city'       : c.city,
  'state'      : c.state,

  'name' : (
    SELECT JSON_DUALITY_OBJECT(
      'id'         : c2.id,         
      'first_name' : c2.first_name,
      'last_name'  : c2.last_name,
      'email'      : c2.email,
      'city'       : c2.city,
      'state'      : c2.state
    )
    FROM customers AS c2
    WHERE c2.id = c.id
  ),

  'area' : (
    SELECT JSON_DUALITY_OBJECT(
      'id'         : c3.id,
      'first_name' : c3.first_name,
      'last_name'  : c3.last_name,
      'email'      : c3.email,
      'city'       : c3.city,
      'state'      : c3.state
    )
    FROM customers AS c3
    WHERE c3.id = c.id
  )
)
FROM customers AS c;

This actually works (no syntax error) but is far more verbose then the official example. I even doubt if this is really an intended use-case of JSON duality view. Probably the intended use-case is not to project a single flat row to a nested JSON object but to directly map an inherently nested structure (e.g. that given by JOIN) to a JSON object without re-structuring.

I've been trying to add a new node to a replica set without success due to these errors: > 2019-08-12T06:48:15.031+0200 I ASIO [NetworkInterfaceASIO-RS-0] > Ending connection to host mongo03:27017 due to bad connection status; > 1 connections to that host remain open 2019-08-12T06:48:15.031+0200 I > REPL [replication-224] Restarting oplog query due to error: > NetworkInterfaceExceededTimeLimit: error in fetcher batch callback: > Operation timed out. Last fetched optime (with hash): { ts: > Timestamp(1565585283, 1317), t: 9 }. Restarts > remaining: 10 2019-08-12T06:48:15.032+0200 I REPL > [replication-224] Scheduled new oplog query Fetcher source: > mongo03:27017 database: local query: { find: "oplog.rs", filter: { ts: > { $gte: Timestamp(1565585283, 1317) } }, tailable: true, oplogReplay: > true, awaitData: true, maxTimeMS: 2000, batchSize: 13981010, term: 9, > readConcern: { afterOpTime: { ts: Timestamp(1565585283, 1317), t: 9 } > } } query metadata: { $replData: 1, $oplogQueryData: 1, > $readPreference: { mode: "secondaryPreferred" } } active: 1 > findNetworkTimeout: 7000ms getMoreNetworkTimeout: 10000ms shutting > down?: 0 first: 1 firstCommandScheduler: RemoteCommandRetryScheduler > request: RemoteCommand 2262327 -- target:mongo03:27017 db:local cmd:{ > find: "oplog.rs", filter: { ts: { $gte: Timestamp(1565585283, 1317) } > }, tailable: true, oplogReplay: true, awaitData: true, maxTimeMS: > 2000, batchSize: 13981010, term: 9, readConcern: { afterOpTime: { ts: > Timestamp(1565585283, 1317), t: 9 } } } active: 1 > callbackHandle.valid: 1 callbackHandle.cancelled: 0 attempt: 1 > retryPolicy: RetryPolicyImpl maxAttempts: 1 maxTimeMillis: -1ms > 2019-08-12T06:48:17.637+0200 I ASIO [NetworkInterfaceASIO-RS-0] > Connecting to mongo03:27017 2019-08-12T06:48:17.645+0200 I ASIO > [NetworkInterfaceASIO-RS-0] Successfully connected to mongo03:27017, > took 8ms (2 connections now open to mongo03:27017) > 2019-08-12T06:49:36.961+0200 I - [repl writer worker 13] > DB.CollectionName collection clone progress: 221390504/643329790 > 34% (documents copied) 2019-08-12T06:51:52.859+0200 I ASIO > [NetworkInterfaceASIO-RS-0] Ending connection to host mongo03:27017 > due to bad connection status; 1 connections to that host remain open > 2019-08-12T06:51:52.859+0200 I REPL [replication-229] Restarting > oplog query due to error: NetworkInterfaceExceededTimeLimit: error in > fetcher batch callback: Operation timed out. Last fetched optime (with > hash): { ts: Timestamp(1565585502, 204), t: 9 }. > Restarts remaining: 10 2019-08-12T06:51:52.859+0200 I ASIO > [NetworkInterfaceASIO-RS-0] Connecting to mongo03:27017 > 2019-08-12T06:51:52.859+0200 I REPL [replication-229] Scheduled > new oplog query Fetcher source: mongo03:27017 database: local query: { > find: "oplog.rs", filter: { ts: { $gte: Timestamp(1565585502, 204) } > }, tailable: true, oplogReplay: true, awaitData: true, maxTimeMS: > 2000, batchSize: 13981010, term: 9, readConcern: { afterOpTime: { ts: > Timestamp(1565585502, 204), t: 9 } } } query metadata: { $replData: 1, > $oplogQueryData: 1, $readPreference: { mode: "secondaryPreferred" } } > active: 1 findNetworkTimeout: 7000ms getMoreNetworkTimeout: 10000ms > shutting down?: 0 first: 1 firstCommandScheduler: > RemoteCommandRetryScheduler request: RemoteCommand 2272962 -- > target:mongo03:27017 db:local cmd:{ find: "oplog.rs", filter: { ts: { > $gte: Timestamp(1565585502, 204) } }, tailable: true, oplogReplay: > true, awaitData: true, maxTimeMS: 2000, batchSize: 13981010, term: 9, > readConcern: { afterOpTime: { ts: Timestamp(1565585502, 204), t: 9 } } > } active: 1 callbackHandle.valid: 1 callbackHandle.cancelled: 0 > attempt: 1 retryPolicy: RetryPolicyImpl maxAttempts: 1 maxTimeMillis: > -1ms I'm a little bit lost regarding how to debug it. It's a 250GB database, the synchronization is being done through a private network 100Mb. There is an iptables in all servers but the traffic from the private network is allowed: -A INPUT -i eth1 -p tcp -m tcp --dport 27017 -m state --state NEW,ESTABLISHED -j ACCEPT At some point the replication process fails: > 2019-08-12T02:22:37.582+0200 I REPL [replication-143] Restarting > oplog query due to error: ExceededTimeLimit: error in fetcher batch > callback: Executor error during find command :: caused by :: errmsg: > "oper ation exceeded time limit". Last fetched optime (with hash): { > ts: Timestamp(1565569328, 710), t: 9 }. Restarts > remaining: 1 2019-08-12T02:22:37.582+0200 I REPL [replication-143] > Scheduled new oplog query Fetcher source: mongo03:27017 database: > local query: { find: "oplog.rs", filter: { ts: { $gte: > Timestamp(1565569328, 710) } }, tailable: true, oplogReplay: true, > awaitData: true, maxTimeMS: 2000, batchSize: 13981010, term: 9, > readConcern: { afterOpTime: { ts: Timestamp(1565569328, 710), t: 9 } } > } query metadata: { $replData: 1, $oplogQ ueryData: 1, > $readPreference: { mode: "secondaryPreferred" } } active: 1 > findNetworkTimeout: 7000ms getMoreNetworkTimeout: 10000ms shutting > down?: 0 first: 1 firstCommandScheduler: RemoteCommandRetryScheduler > re quest: RemoteCommand 1381614 -- target:mongo03:27017 db:local cmd:{ > find: "oplog.rs", filter: { ts: { $gte: Timestamp(1565569328, 710) } > }, tailable: true, oplogReplay: true, awaitData: true, maxTimeMS: > 2000, ba tchSize: 13981010, term: 9, readConcern: { afterOpTime: { ts: > Timestamp(1565569328, 710), t: 9 } } } active: 1 callbackHandle.valid: > 1 callbackHandle.cancelled: 0 attempt: 1 retryPolicy: RetryPolicyImpl > maxAttem pts: 1 maxTimeMillis: -1ms 2019-08-12T02:22:39.612+0200 I > REPL [replication-142] Error returned from oplog query (no more > query restarts left): ExceededTimeLimit: error in fetcher batch > callback: Executor error during find command :: caused by :: errmsg: > "operation exceeded time limit" 2019-08-12T02:22:39.615+0200 I REPL > [replication-142] Finished fetching oplog during initial sync: > ExceededTimeLimit: error in fetcher batch callback: Executor error > during find command :: caused by :: errms g: "operation exceeded time > limit". Last fetched optime and hash: { ts: Timestamp(1565569328, > 710), t: 9 } 2019-08-12T02:22:43.034+0200 I REPL > [replication-142] CollectionCloner ns:DB.CollectionName finished > cloning with status: IllegalOperation: AsyncResultsMerger killed > 2019-08-12T02:22:44.551+0200 W REPL [replication-142] collection > clone for 'DB.CollectionName' failed due to IllegalOperation: While > cloning collection 'DB.CollectionName' there was an error > 'AsyncResultsMerger killed' 2019-08-12T02:22:44.551+0200 I REPL > [replication-142] CollectionCloner::start called, on ns:DB.CollectionName2 > 2019-08-12T02:22:44.551+0200 W REPL [replication-142] database > 'dbName' (9 of 9) clone failed due to ShutdownInProgress: collection > cloner completed 2019-08-12T02:22:44.551+0200 I REPL > [replication-142] Finished cloning data: ShutdownInProgress: > collection cloner completed. Beginning oplog replay. > 2019-08-12T02:22:44.551+0200 I REPL [replication-142] Initial sync > attempt finishing up. And it has to start over again. Don't know if I could configure a bigger time out to avoid this disconnections. Thanks in advance.