Merge Replication Snapshot Files I am using Merge Replication with SQL 2012. I look in the snapshot directory, but the largest file in there is a prc file which is 646 KB.

I know for sure that the biggest of my replicated tables is 25 MB in the database after replicating, so I am not sure I understand why there aren't larger files in the snapshot directory?

Also is there a place I can look for the snapshot files as they are downloaded to the subscriber?

For instance the merge agent outputs messages such as,

Downloaded 100 change(s) in 'tableName' (100 inserts) : 144083 total

I would like to see the data being downloaded and get a feel for how large it is.

There is a lot of information here,

http://msdn.microsoft.com/en-us/library/ms151769(v=sql.105).aspx

And here,

http://msdn.microsoft.com/en-us/library/ms152502(v=sql.105).aspx

So the thing to be aware of, because I was using parameterised filters the snapshot folder above doesn't contain any data, only schema.

Also I was not filtering correct using SUSER_SNAME() so the filtering was not using the snapshot at all causing me performance issues.

Once that was sorted I could see in the snapshot folder there subfolders with a snapshot for each user of the system. This data is the data filtered for that particular user.


MySQL for Oracle DBA I am a newbie to MySQL, but understand Oracle databases well. I need a quick help to understand the architectural differences. Currently I am working on a Windows platform.

1) In Oracle we have to put the database in archive log mode, do we have to do this with MySQL to perform point in time recovery?

2) In Oracle, an instance starts with memory structure SGA. How does it work in MySQL?

3) RMAN is the online backup tool for oracle. MySQL backup for MySQL, is it capable to do point in time recovery?

4) I don't understand logging and database files layout in MySQL In Oracle if i create tablespace  we see a file on the file system with name. How does this work in MySQL?

Q) RMAN is the online backup tool for oracle. MySQL backup for MySQL, is it capable to do point in time recovery?

A) MySQL use mysqldump as online backup tool.

Q) In Oracle, an instance starts with memory structure SGA. How does it work in MySQL?

A) In MySQL, tuning of the caches similar to Oracle’s buffer cache is done on a per storage engine basis. For the MyISAM engine, the key_buffer can be used to cache index data only, and relies on the underlying operating system to cache data files unlike Oracle’s buffer cache. Because of this difference, while a dedicated Oracle database might allocate 80% of the system’s main memory to the buffer cache, often MySQL’s key buffer does not get set to much over 50% of the database system’s main memory for optimal tuning.

MySQL’s InnoDB storage engine has a more similar cache to Oracle’s buffer cache called innodb_buffer_pool_size and caches both index and data blocks. Like Oracle’s buffer pool, a dedicated MySQL system using only InnoDB tables would do well to set to up 80% of the systems main memory for use in this cache.

Q) I don't understand logging and database files layout in MySQL In Oracle if i create tablespace we see a file on the file system with name. How does this work in MySQL?

A) Each database managed by the MySQL server has its own database directory. This exists as a subdirectory of the data directory, with the same name as the database it represents. For example, a database mydb corresponds to the database directory DATADIR/mydb. This representation allows several database-level statements to be almost trivial in their implementation.

Q) In Oracle we have to put the database in archive log mode, do we have to do this with MySQL to perform point in time recovery?

A)  I found something similar to this question in https://stackoverflow.com/questions/65093/best-way-to-archive-live-mysql-database


Date calculations give different results in different environments This query:
WITH RECURSIVE
cte AS ( SELECT 0 n
         UNION ALL
         SELECT n + 1 FROM cte WHERE n < 20)
SELECT n, ('2020-04-13 00:00:00 Europe/Berlin'::timestamptz - make_interval(days => n)) AT TIME ZONE 'Europe/Berlin'
FROM cte
ORDER BY n

On my machine it gives this result:

Note that the time switches from 00:00 to 23:00 on the day of the DST switch.
On dbfiddle.uk (Link) the query gives this result:

Note that it shows 00:00 for all days, there is no change on the day of the DST switch.
And then on sqlfiddle.com (Link) the result is:

Here we have the switch again, but something else is strange here: The time zone of the result is given as Z (UTC) even though everything before was in time zone Europe/Berlin.

sqlfiddle.com might interpret returned timestamps as having a timezone.
But PostgreSQL does not store a timezone along with a timestamp.
timestamp with timezone fields are internally stored in UTC: https://www.postgresql.org/docs/11/datatype-datetime.html#DATATYPE-TIMEZONES
A timezone is not returned in this query either.
So this behavior of sqlfiddle.com is misleading.


Mysql master master with high latency We have a single master setup with many slaves in germany and some slaves in australia.

To have faster writes in australia we want a master-master setup between germany and australia. Read slaves will remain under the local master in both regions.

We tested Percona XtraDB Cluster, but the commits are synchronous, and writes are way too slow.

Is there an option in XtraDB to speed things up? Maybe tranfer multiple transactions in one block between the two masters? Or allow commits to be accepted before the second nodes applied it?

Is there another master-master solution, that works better (maybe asynchronously) under the given high latency of ~300ms?

PXC (and other Galera solutions) require one network hop at COMMIT time.  If your application can combine writes into a single transaction, that will help at some level.  However, still each COMMIT will take 300ms (or whatever it is).

Regular MySQL can do Master-Master and have both writable.  That replication is asynchronous.  However, there are a number of caveats.  Auto_increments should be configured with auto_increment_offset and _increment.  UNIQUE indexes are problematic; the app must figure out how to avoid 'simultaneous' insertion of the same UNIQUE value on both continents.  Note that replication will simply stop until you manually do something.

Another possibility is NDB Cluster.  However, that has a lot of differences in the syntax and functionality.  The advantage is that it is "eventually consistent".  You set up rules about what to do if, say, duplicate keys are inserted.

There's an old saying:  You can have any of X or Y or Z; pick 2.  So, I ask, what are your 2 reasons for wanting multi-Master?  And what 3rd item are you willing to sacrifice.  (It sounds like latency is not #3.)


Merge one table to another I have a table lets say
Table1 with columns Point_ID, Size, Age, Dist, Treatment
and Table 2 with columns  Point_ID, Size, Age, Dist, Treatment, Path, Location.
Table 2 has similar data for Point_ID, Size, Age, Dist, Treatment as Table1 for some values.

I want to merge extra columns of Table 2 in Table1 such that there is no duplication and the resulted table looks likes  "Point_ID, Size, Age, Dist, Treatment, Path, Location."
There is data in Table1 in columns Point_ID, Size, Age, Dist, Treatment which does not have path and location, so the path and location should be null for them.
What SQL query to be performed?.

Assuming Point_ID is a primary key or unique key shared by the two tables, then you can do this to create a new table Table3 with the data you specified:

CREATE TABLE Table3 AS
  SELECT t1.Point_ID, t1.Size, t1.Age, t1.Dist, t1.Treatment, t2.Path, t2.Location
  FROM Table1 t1
    LEFT OUTER JOIN Table2 t2 ON t1.Point_ID = t2.Point_ID
  UNION
  SELECT Point_ID, Size, Age, Dist, Treatment, Path, Location
  FROM Table2;

For any row with Point_ID in Table1 that doesn't exist in Table2 the Path and Location columns will be NULL. If it does exist, the Path and Location columns will have the values from Table2. For any row with Point_ID in Table2 that doesn't exist in Table1, the whole row will be added to the new table Table3.


MySQL one file per table not working I've followed all of the instructions for enabling the one file per table in MySQL (v8, on Ubuntu 20):

Added innodb_file_per_table=ON
Restarted MySQL
Dumped all databases, one by one.

However, when I import the databases back, everything is written to ibdata1 file.
But, When I create a brand new DB via MySQL Client, and add a random table to it, the data is written to its own folder + table.ibd file.
Any idea what I could be doing wrong?
Few troubleshooting steps I tried:

Reboot
show variables like 'innodb_file_per_table';  (Value = ON)

First of all, innodb_file_per_table is enabled by default for MySQL 8.
If you want to know what tables are sitting in ibdata1, run the following query
select * from information_schema.innodb_tables;

This will inform you about each table and what tablespace it resides in
EXAMPLE
I spun of a vagrant lab that runs MySQL 8.0.17
I loaded the KJV of the Bible and added a fulltext index
Here is the output of the query I mentioned
mysql> select * from information_schema.innodb_tables;
+----------+-----------------------------------------------------+------+--------+------------+------------+---------------+------------+--------------+
| TABLE_ID | NAME                                                | FLAG | N_COLS | SPACE      | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+-----------------------------------------------------+------+--------+------------+------------+---------------+------------+--------------+
|     1025 | mysql/db                                            |  161 |     25 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1026 | mysql/user                                          |  161 |     54 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1027 | mysql/default_roles                                 |  161 |      7 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1028 | mysql/role_edges                                    |  161 |      8 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1029 | mysql/global_grants                                 |  161 |      7 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1030 | mysql/password_history                              |  161 |      7 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1031 | mysql/func                                          |  161 |      7 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1032 | mysql/plugin                                        |  161 |      5 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1054 | mysql/help_topic                                    |  161 |      9 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1055 | mysql/help_category                                 |  161 |      7 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1057 | mysql/help_relation                                 |  161 |      5 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1036 | mysql/servers                                       |  161 |     12 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1037 | mysql/tables_priv                                   |  161 |     11 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1038 | mysql/columns_priv                                  |  161 |     10 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1056 | mysql/help_keyword                                  |  161 |      5 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1040 | mysql/time_zone_name                                |  161 |      5 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1041 | mysql/time_zone                                     |  161 |      5 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1042 | mysql/time_zone_transition                          |  161 |      6 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1043 | mysql/time_zone_transition_type                     |  161 |      8 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1044 | mysql/time_zone_leap_second                         |  161 |      5 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1045 | mysql/procs_priv                                    |  161 |     11 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1046 | mysql/component                                     |  161 |      6 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1047 | mysql/slave_relay_log_info                          |  161 |     12 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1048 | mysql/slave_master_info                             |  161 |     31 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1049 | mysql/slave_worker_info                             |  161 |     16 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1050 | mysql/gtid_executed                                 |  161 |      6 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1051 | mysql/server_cost                                   |  161 |      7 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1052 | mysql/engine_cost                                   |  161 |      9 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1053 | mysql/proxies_priv                                  |  161 |     10 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     1058 | sys/sys_config                                      |   33 |      7 |          1 | Dynamic    |             0 | Single     |            0 |
|     1101 | bible/verses                                        |   33 |      8 |         44 | Dynamic    |             0 | Single     |            0 |
|     1102 | bible/fts_000000000000044d_being_deleted            |   33 |      4 |         45 | Dynamic    |             0 | Single     |            0 |
|     1103 | bible/fts_000000000000044d_being_deleted_cache      |   33 |      4 |         46 | Dynamic    |             0 | Single     |            0 |
|     1104 | bible/fts_000000000000044d_config                   |   33 |      5 |         47 | Dynamic    |             0 | Single     |            0 |
|     1105 | bible/fts_000000000000044d_deleted                  |   33 |      4 |         48 | Dynamic    |             0 | Single     |            0 |
|     1106 | bible/fts_000000000000044d_deleted_cache            |   33 |      4 |         49 | Dynamic    |             0 | Single     |            0 |
|     1107 | bible/fts_000000000000044d_00000000000000cd_index_1 |   33 |      8 |         50 | Dynamic    |             0 | Single     |            0 |
|     1108 | bible/fts_000000000000044d_00000000000000cd_index_2 |   33 |      8 |         51 | Dynamic    |             0 | Single     |            0 |
|     1109 | bible/fts_000000000000044d_00000000000000cd_index_3 |   33 |      8 |         52 | Dynamic    |             0 | Single     |            0 |
|     1110 | bible/fts_000000000000044d_00000000000000cd_index_4 |   33 |      8 |         53 | Dynamic    |             0 | Single     |            0 |
|     1111 | bible/fts_000000000000044d_00000000000000cd_index_5 |   33 |      8 |         54 | Dynamic    |             0 | Single     |            0 |
|     1112 | bible/fts_000000000000044d_00000000000000cd_index_6 |   33 |      8 |         55 | Dynamic    |             0 | Single     |            0 |
|     1113 | bible/books                                         |   33 |      5 |         56 | Dynamic    |             0 | Single     |            0 |
|     1150 | ft_db/ft_test_innodb                                |   33 |      6 |         93 | Dynamic    |             0 | Single     |            0 |
|     1151 | ft_db/fts_000000000000047e_being_deleted            |   33 |      4 |         94 | Dynamic    |             0 | Single     |            0 |
|     1152 | ft_db/fts_000000000000047e_being_deleted_cache      |   33 |      4 |         95 | Dynamic    |             0 | Single     |            0 |
|     1153 | ft_db/fts_000000000000047e_config                   |   33 |      5 |         96 | Dynamic    |             0 | Single     |            0 |
|     1154 | ft_db/fts_000000000000047e_deleted                  |   33 |      4 |         97 | Dynamic    |             0 | Single     |            0 |
|     1155 | ft_db/fts_000000000000047e_deleted_cache            |   33 |      4 |         98 | Dynamic    |             0 | Single     |            0 |
|     1156 | ft_db/fts_000000000000047e_0000000000000107_index_1 |   33 |      8 |         99 | Dynamic    |             0 | Single     |            0 |
|     1157 | ft_db/fts_000000000000047e_0000000000000107_index_2 |   33 |      8 |        100 | Dynamic    |             0 | Single     |            0 |
|     1158 | ft_db/fts_000000000000047e_0000000000000107_index_3 |   33 |      8 |        101 | Dynamic    |             0 | Single     |            0 |
|     1159 | ft_db/fts_000000000000047e_0000000000000107_index_4 |   33 |      8 |        102 | Dynamic    |             0 | Single     |            0 |
|     1160 | ft_db/fts_000000000000047e_0000000000000107_index_5 |   33 |      8 |        103 | Dynamic    |             0 | Single     |            0 |
|     1161 | ft_db/fts_000000000000047e_0000000000000107_index_6 |   33 |      8 |        104 | Dynamic    |             0 | Single     |            0 |
+----------+-----------------------------------------------------+------+--------+------------+------------+---------------+------------+--------------+
55 rows in set (0.00 sec)

mysql> desc information_schema.innodb_tables;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| TABLE_ID      | bigint(21) unsigned | NO   |     |         |       |
| NAME          | varchar(655)        | NO   |     |         |       |
| FLAG          | int(11)             | NO   |     |         |       |
| N_COLS        | int(11)             | NO   |     |         |       |
| SPACE         | bigint(21)          | NO   |     |         |       |
| ROW_FORMAT    | varchar(12)         | YES  |     |         |       |
| ZIP_PAGE_SIZE | int(11) unsigned    | NO   |     |         |       |
| SPACE_TYPE    | varchar(10)         | YES  |     |         |       |
| INSTANT_COLS  | int(11)             | NO   |     |         |       |
+---------------+---------------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

mysql>

Please note that any table whose space value is 0, resides in the ibdata1. It says to in the MySQL 8.0 Docs about INFORMATION_SCHEMA.INNODB_TABLES;
At the moment, there are no tables in ibdata1.
Let's see If I can inject one into ibdata1.
mysql> use bible
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>
mysql>
mysql> set global innodb_file_per_table = 'OFF';
Query OK, 0 rows affected (0.00 sec)

mysql> create table injection (id int not null auto_increment, name varchar(20), primary key (id));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into injection (name) values ('Hello, World !!!');
Query OK, 1 row affected (0.01 sec)

mysql> select * from injection;
+----+------------------+
| id | name             |
+----+------------------+
|  1 | Hello, World !!! |
+----+------------------+
1 row in set (0.00 sec)

mysql>

OK, now let's check information_schema
mysql> select * from information_schema.innodb_tables where space = 0;
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME            | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
|     1162 | bible/injection |   33 |      5 |     0 | Dynamic    |             0 | System     |            0 |
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
1 row in set (0.00 sec)

mysql>

TADA !!!
OK, so now go run
select * from information_schema.innodb_tables where space = 0;

If you don't see any rows returned, then you have nothing in ibdata1.
WHAT IF I STILL HAVE TABLES IN ibdata1?
If you are starting brand new in MySQL. You should not have this problem from the beginning. Notwithstanding, let's say you have this situation in MySQL 8.0. How to you do yank the table out of ibdata1 ???
Let's try ALTER TABLE ... ENGINE=InnoDB
Unfortunately, you cannot perform a NULL ALTER like before:
mysql> select * from information_schema.innodb_tables where space = 0;
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME            | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
|     1162 | bible/injection |   33 |      5 |     0 | Dynamic    |             0 | System     |            0 |
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
1 row in set (0.00 sec)

mysql> set global innodb_file_per_table = 'ON';
Query OK, 0 rows affected (0.00 sec)

mysql> alter table bible.injection engine=InnoDB;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from information_schema.innodb_tables where space = 0;
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME            | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
|     1163 | bible/injection |  161 |      5 |     0 | Dynamic    |             0 | System     |            0 |
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
1 row in set (0.00 sec)

mysql>

You could get away with this in MySQL 5.7 and back. For MySQL 8.0, the temp table used maintains the table's current tablespace metadata.
Let's try CREATE TABLE LIKE
mysql> create table bible.injection2 like bible.injection;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from information_schema.innodb_tables where space = 0;
+----------+------------------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME             | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+------------------+------+--------+-------+------------+---------------+------------+--------------+
|     1163 | bible/injection  |  161 |      5 |     0 | Dynamic    |             0 | System     |            0 |
|     1164 | bible/injection2 |  161 |      5 |     0 | Dynamic    |             0 | System     |            0 |
+----------+------------------+------+--------+-------+------------+---------------+------------+--------------+
2 rows in set (0.00 sec)

mysql> select @@global.innodb_file_per_table;
+--------------------------------+
| @@global.innodb_file_per_table |
+--------------------------------+
|                              1 |
+--------------------------------+
1 row in set (0.00 sec)

mysql>

OK Ouch !!!. That pesky tablespace metadata gets dragged around like luggage.
Let's try ALTER TABLE ... TABLESPACE=innodb_file_per_table
Here we go ...
mysql> create table bible.injection2 like bible.injection;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from information_schema.innodb_tables where space = 0;
+----------+------------------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME             | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+------------------+------+--------+-------+------------+---------------+------------+--------------+
|     1163 | bible/injection  |  161 |      5 |     0 | Dynamic    |             0 | System     |            0 |
|     1164 | bible/injection2 |  161 |      5 |     0 | Dynamic    |             0 | System     |            0 |
+----------+------------------+------+--------+-------+------------+---------------+------------+--------------+
2 rows in set (0.00 sec)

mysql> select @@global.innodb_file_per_table;
+--------------------------------+
| @@global.innodb_file_per_table |
+--------------------------------+
|                              1 |
+--------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE bible.injection2 TABLESPACE=innodb_file_per_table;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from information_schema.innodb_tables where space = 0;
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME            | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
|     1163 | bible/injection |  161 |      5 |     0 | Dynamic    |             0 | System     |            0 |
+----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE bible.injection TABLESPACE=innodb_file_per_table;
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from information_schema.innodb_tables where space = 0;
Empty set (0.00 sec)

mysql>

TADA !!!
CONCLUSION
If you suspect any tables are in ibdata1, find them with
select * from information_schema.innodb_tables where space = 0;

Then just run
ALTER TABLE mydb.mytable TABLESPACE=innodb_file_per_table;

Please remember: The default innodb_file_per_table is ON.
No need to mysqldump and reload.
If you are attempting to shrink ibdata1, you will have load data into a brand new installation. You cannot just delete ibdata1 like you could in MySQL 5.5 and back. MySQL 5.6 + has innodb tables in the mysql schema database. So, you cannot play the same file manipulation games. MySQL 8.0 now demands a lot more respect from a DBA.


Do SQL Server compressed indexes remain compressed on rebuild without specifying data compression? After one rebuilds their SQL Server indexes using page compression (ALTER INDEX IX1 REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)), do subsequent rebuilds (as done by some maintenance scripts past a certain fragmentation threshold) need to specify data compression again? Would the indexes otherwise be effectively decompressed?

Indexes remain compressed when rebuilding / reorganizing them.

Create table and compressed index

 CREATE TABLE DBO.TEST_INDX(id int, bla varchar(255));
 CREATE INDEX IX1 ON dbo.TEST_INDX(id)  WITH (DATA_COMPRESSION = PAGE);

Check Compression

 SELECT i.name, p.data_compression_desc
 FROM sys.partitions P
 INNER JOIN sys.indexes I ON I.object_id = P.object_id AND I.index_id = P.index_id
 WHERE P.data_compression > 0 and I.name = 'IX1';

Result

name    data_compression_desc
IX1     PAGE

Rebuild the index

ALTER INDEX IX1 on  DBO.TEST_INDX rebuild

Check Compression

 SELECT i.name, p.data_compression_desc
 FROM sys.partitions P
 INNER JOIN sys.indexes I ON I.object_id = P.object_id AND I.index_id = P.index_id
 WHERE P.data_compression > 0 and I.name = 'IX1'

Result

name    data_compression_desc
IX1     PAGE

Disabling them and then rebuilding has a different result, since disabling removes the index, while keeping the index definition.

alter index IX1 on  DBO.TEST_INDX DISABLE ;
alter index IX1 on  DBO.TEST_INDX REBUILD ;

Result

name    data_compression_desc

Compression was lost, compression definition would also be lost when dropping and creating the index via SSMS without adapting the Index create script.

Why?

Because the data_compression option is not retained when scripting out the Index create statement.

however, if we disable the index , rebuild with compression and then rebuild again:

alter index IX1 on  DBO.TEST_INDX DISABLE ;
alter index IX1 on  DBO.TEST_INDX REBUILD  WITH (DATA_COMPRESSION = PAGE);
alter index IX1 on  DBO.TEST_INDX REBUILD;

Result

name    data_compression_desc
IX1 PAGE

Testing a rebuild with Ola hallengren's maintenance solution

The parameters are modified for testing purposes.

Add some data to get to one page, as it is needed for the MinNumberOfPages parameter.

INSERT INTO dbo.TEST_INDX(id,bla)
VALUES(5,'test');
go 10

Execute the index optimize proc to print out the statement.

EXECUTE dbo.IndexOptimize
@Databases = 'TestDB',
@FragmentationLow = 'INDEX_REBUILD_ONLINE',
@FragmentationMedium = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@Indexes = 'TestDB.DBO.TEST_INDX',
@Execute = 'N',
@MinNumberOfPages = 1;

Result:

Command: ALTER INDEX [IX1] ON [TestDB].[dbo].[TEST_INDX] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON, RESUMABLE = OFF)

Comment: ObjectType: Table, IndexType: NonClustered, ImageTex
t: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 1, Fragmentation: 0
Outcome: Not Executed
Duration: 00:00:00
Date and time: 2019-01-09 14:48:12

Executing the generated command

ALTER INDEX [IX1] ON [TestDB].[dbo].[TEST_INDX] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON, RESUMABLE = OFF)

Compression is retained

name    data_compression_desc
IX1 PAGE

Testing a rebuild with a maintenance plan (I would strongly argue for ola's solution)

Rebuild indexes

Choose the test table

Add some test fragmentation levels.

Insert some values to get the fragmentation going

INSERT INTO dbo.TEST_INDX(id)
SELECT id from TEST_INDX
go 4

Check the fragmentation percentage

SELECT
I.[name] AS  INDX ,
IPS.avg_fragmentation_in_percent,
IPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), object_id('[dbo].[TEST_INDX]'), NULL, NULL, NULL) AS IPS
INNER JOIN sys.indexes AS I ON I.[object_id] = IPS.[object_id]
AND IPS.index_id = I.index_id
WHERE IPS.database_id = DB_ID()
and I.name = 'IX1'

Result

INDX    avg_fragmentation_in_percent    page_count
IX1 66,6666666666667    3

Run the plan

The interesting part here, when looking at the plan report, is that the DATA_COMPRESSION = PAGE option is added to the generated REBUILD command!

Command:USE [TestDB]
GO
ALTER INDEX [IX1] ON [dbo].[TEST_INDX] REBUILD PARTITION = ALL WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, DATA_COMPRESSION = PAGE)

Fragmentation:

INDX    avg_fragmentation_in_percent    page_count
IX1 0   2

Compression:

name    data_compression_desc
IX1 PAGE


ERROR 1064 (42000): You have an error in your SQL syntax - Cannot understand what the error means I'm trying to import an SQL db, exported from another place. I have the SQL create script. I am not sure what might be going wrong...
My mariadb version is
select version();
+----------------+
| version()      |
+----------------+
| 10.5.8-MariaDB |
+----------------+
1 row in set (0.000 sec)

Table create script:
-- phpMyAdmin SQL Dump
-- version 5.0.4
-- https://www.phpmyadmin.net/
--
-- Host: db
-- Generation Time: Jan 04, 2021 at 05:59 PM
-- Server version: 8.0.22
-- PHP Version: 7.4.11

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `mytestdb`
--
CREATE DATABASE IF NOT EXISTS `mytestdb` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE `mytestdb`;

-- --------------------------------------------------------

--
-- Table structure for table `categories`
--

DROP TABLE IF EXISTS `categories`;
CREATE TABLE `categories` (
  `id` int NOT NULL,
  `category` varchar(100) GENERATED ALWAYS AS (json_unquote(json_extract(`json`,_utf8mb4'$.category'))) VIRTUAL NOT NULL,
  `popular` enum('true','false') GENERATED ALWAYS AS (json_unquote(json_extract(`json`,_utf8mb4'$.popular'))) VIRTUAL NOT NULL,
  `imageUrl` varchar(255) GENERATED ALWAYS AS (json_unquote(json_extract(`json`,_utf8mb4'$.imageUrl'))) VIRTUAL NOT NULL,
  `json` json NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Error:
ERROR 1064 (42000) at line 33: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NOT NULL,
  `popular` enum('true','false') GENERATED ALWAYS AS (json_unquote(...' at line 3

I'm not an expert in Mariadb but I think you cannot do it. From the documentation on generated columns

Generated columns can be referenced in the INSERT, UPDATE, and DELETE statements.
However, VIRTUAL or PERSISTENT generated columns cannot be explicitly set to any other values than NULL or DEFAULT. If a generated column is explicitly set to any other value, then the outcome depends on whether strict mode is enabled in sql_mode. If it is not enabled, then a warning will be raised and the default generated value will be used instead. If it is enabled, then an error will be raised instead.


rsExecutionNotFound when directly accessing an SSRS report I have an SSRS report that can I access from the Web Portal but once I try to access it directly through the URL, I get the following error: Execution 'xxx' cannot be found (rsExecutionNotFound).

I looked at the logs on the Report Server and the error there says  userName: mydomain\myusername not found in the database.

But, of course, it is in the database - I can access the report from the Web Portal.

Any ideas?

UPDATE The same link now works, two days later.

What I have seen in a case like you ask about is you hit a report, and copy the url out and then try to hit it later. Reports have session state, especially if you change paramaters,etc. The url will look like a report url with some funky querystring data added to the end. You can't share that because SSRS can't take your "session state" and then find it later.


Return single row with one to many relationship based on evaluated conditions Database is Postgres.
I have an issue whereby I have a table with the following columns: ID, Product_ID Customer_ID, Price and Item_Type.
This table is joined on Product_ID to another table. For a Given product ID there is the default price (where the Customer_ID is Blank) for a given item type (New, Renewal, Redemption etc.), there may also be a customer specific price list (where Customer_ID is joined to the Customers Table).
The issue is that this Database has been poorly maintained. For Item_Type there are 2 Prices that for this issue we are interested: Renew and Renewal. Some prices have a Renew price, some have a Renewal Price and some have both.
select p.id, pr.customer_id, p.period, pr.price, pr.item_type
from products p
right join prices pr on
pr.product_id = p.id and pr.period = p.period and pr.customer_id is null
where p.status = 'PURCHASED'

As an example this produces the following:
121751  8407508 12 29.95 RENEW
121751  8407508 12 35.95 RENEWAL

The cause is that this product has 2 different prices for Renew and Renewal - looking at the front end application code - if there is an item_type 'RENEWAL' then this price is used, if not then the 'RENEW' price is used.
I've tried using a case statement, both in the select and in a where clause:
case pr.item_type
    when 'RENEWAL' then pr.price
    when 'RENEW' then pr.price
    end

But I still get both rows - I'm not sure how to do a conditional and hierarchical lookup e.g. if Condition A is matched, then return row that matches Condition A, if no match is found, Condition B is matched, then return row that matches Condition B etc.

from products p right join prices pr ... where p.status = 'PURCHASED'

The condition by left table converts your RIGHT JOIN to INNER JOIN implicitly.

if there is an item_type 'RENEWAL' then this price is used, if not then the 'RENEW' price is used

Use, for example, something like (taking into account the former comment):
SELECT ..., COALESCE(t1.price, t2.price) AS price, ...
FROM products
LEFT JOIN prices AS t1 ON t1.item_type = 'RENEWAL' AND {another joining conditions}
LEFT JOIN prices AS t2 ON t2.item_type = 'RENEW' AND {another joining conditions}
WHERE {another conditions}

IF RENEWAL price exists then it will be returned, if not, but RENEW price exists then you'll get it, else you'll get NULL (you may join more price table copies with another price types in needed order).


Is it possible to set stand-by database to active, do changes and then continue restore chain? Basically, I'm using a custom log shipping method on a sql 2012, where the transaction files are manually copied to an offsite location. The files are then restored using something similar:

RESTORE LOG xyz
    FROM MyAdvWorks_log2
    WITH STANDBY = 'c:\undo.bak'

I would really like to be able to set that stand-by database briefly to Active, do various stuff (not just read) and then rollback/discard those changes and allow the restore to continue.

Is that even possible?
If not, can I copy the stand-by server and set it to active (and then when I'm done, delete it)?

(since this is longer than a comment .. so posting this as an answer)

In sql server enterprise edition, you can leverage database snapshots.

If I understand your question, you want to do some changes on a standy (not live) copy of your database and then discard those ?

If that is true, then you can create a database snapshot of your main database using :

CREATE DATABASE snapshotDBName... AS SNAPSHOT OF yourPRODDBNAME

Then do the modifications - Inserts / Updates / Deletes using regular TSQL and once you are done with your modifications, you can drop the snapshot using

DROP DATABASE snapshotDBName

You can read more on SQL Server Database Snapshots

Note: If this is not what you want, then I will delete this answer .. which I wrote assuming stuff.


Why my database mirror break down after changing file group settings from RESTRICTED_USER to MULTI_USER? My environment is the following:
VMWare 5.5 vitalized server MS Windows Server 2008R2 Enterprise domain and SQL Server 2008 R2 Enterprise.
Centralized storage with fibre-channel connection.

I have partitions in my SQL Server DB. I have 2 file groups: one with live data (FG1), second with historical data (HDG).

The second file group is read-only. Each month I make movement in partitions - I add new data (from previous month) to historical data. This process is automatic.

We moved our database to a new server. Initially, I had to do the process manually. During this operation my mirror breaks down (after operation 3 - see process flow bellow) with the following error:

ON PRINCIPAL SERVER:

ROW 0 in LOG:

Date        15.6.2015 20:54:11
Log     SQL Server (Current - 16.6.2015 07:55:00)

Source      spid84

Message
Setting database option MULTI_USER to ON for database MYDB.

ROW 1 in LOG:

Date        15.6.2015 20:54:11
Log     SQL Server (Current - 16.6.2015 07:55:00)

Source      spid18s

Message
Error: 1453, Severity: 16, State: 1.

ROW 2 in LOG:

Date        15.6.2015 20:54:11
Log     SQL Server (Current - 16.6.2015 07:55:00)

Source      spid18s

Message
'TCP://10.201.27.154:5022', the remote mirroring partner for database 'MYDB', encountered error 823, status 3, severity 24. Database mirroring has been suspended.  Resolve the error on the remote server and resume mirroring, or remove mirroring and re-establish the mirror server instance.

REMARK: I executed this operation on the old server many times automatically and I never experience such error.

ON MIRROR SERVER:

ROW 1 in LOG:

Date        15.6.2015 20:54:11
Log     SQL Server (Archive #3 - 15.6.2015 21:33:00)

Source      spid17s

Message
Error: 823, Severity: 24, State: 3.

ROW 2 in LOG:

Date        15.6.2015 20:54:11
Log     SQL Server (Archive #3 - 15.6.2015 21:33:00)

Source      spid17s

Message
The operating system returned error 5(Access is denied.) to SQL Server during a write at offset 0000000000000000 in file 'e:\Databases\MYDB_HISTRICAL.ndf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

MY PROCESS IS FOLLOWING:

1. I make several backups of the Database (Full, File Group and TLog backups).

2. I set DB to RESTRICTED_USER (to allow removing read-only of historical file group flag by script).

2a. I remove READ-ONLY flag of my Historical File Group.

3. I set DB to MULTI_USER to allow normal operation of our software.

4. I update partitions so the data is moved the the historical file group.

5. I repeat steps 2, 2a and 3 in so I can set historical file group READ ONLY again.

6. I make backups again.

Does anyone have idea why I receive that error?

EDIT: We receive the same issue during the different phase of the procedure. This is the only situation in which mirror breaks down so I suppose the problem is inside the procedure, but I can not figure out why!

We found the issue. It is a bug in SQL Server. When we set READ_WRITE the command is not transfeered properly into the mirror DB. When script start change partitions on the mirror server an error occured. After that the syncronization is ruined and the DB on the mirror is locked (in suspended state).

We fix the issue by updating SQL Server to the latest version (our initial version was wiout SP).


JSONB with indexing vs. hstore I am trying to decide on the database design, with as few assumptions (regarding how the web app actually evolves) as possible at this stage.

As a first step, understanding that JOINS are expensive, I am considering a small number of monolithic tables  as opposed to a large number of normalized smaller tables. As a second point, I am confused between using hstore vs. regular tables vs. JSONB (with GiST indexing).

AFAIK (please feel free to correct):

Generally, in Postgres, hstore is known to perform better than other data types. This presentation from FOSDEM PGDAY has some interesting stats (in the second half of the slides).
https://wiki.postgresql.org/images/b/b4/Pg-as-nosql-pgday-fosdem-2013.pdf
An advantage with hstore is the fast indexing (GiN or GiST). However, with JSONB, GiN and GiST indexing can also be applied to JSON data.
This blog from a professional at 2nd Quadrant says "At this point it’s probably worth replacing hstore use with jsonb in all new applications" (scroll to the end):
http://blog.2ndquadrant.com/postgresql-anti-patterns-unnecessary-jsonhstore-dynamic-columns/

So I would like to decide on the following:

For the main (structured) part of the data: should it go in a couple of
relational tables (relatively large with many columns), or should it be a number of key-value stores using hstore?
For the ad hoc (user contributed/unstructured) data, should it be in JSON or ad hoc key value stores in hstore (with the keys stored in one of the main relational tables)?

Relational databases are designed around joins, and optimized to do them well.

Unless you have a good reason not to use a normalized design, use a normalised design.

jsonb and things like hstore are good for when you can't use a normalized data model, such as when the data model changes rapidly and is user defined.

If you can model it relationally, model it relationally. If you can't, consider json etc. If you're choosing between json/jsonb/hstore, generally choose jsonb unless you have a reason not to.

That's what I said in my blog post, which addresses just this topic. Please read the whole post. The paragraph you quoted points out that if you're choosing a dynamic structure you should choose jsonb over hstore, but the rest of the blog post is about why you should usually prefer to model relationally if you can.

So. Model the main structured part relationally. If the tables are really wide with lots of columns, this might be a sign that further normalization is required. Do not be afraid of joins. Learn to love joins. Joining many small tables will often be faster than querying and maintaining big denormalized tables. Denormalize only if you need to for specific cases, and preferably via materialized views ... but don't do it until you know you need to and have an actual concrete problem to solve.

For user-contributed data that's freeform and unstructured, use jsonb. It should perform as well as hstore, but it's more flexible and easier to work with.

One relevant thing to understand: GiST and GIN indexes like those used on jsonb are generally much less efficient than a plain b-tree index.  They're more flexible, but a b-tree index on a normal column will almost always be much, much faster.


Why does my WHERE clause benefit from an "included" column? According to this answer, unless an index is built over the columns which are used to restrict, the query will not benefit from an index.

I have this definition:

CREATE TABLE [dbo].[JobItems] (
    [ItemId]             UNIQUEIDENTIFIER NOT NULL,
    [ItemState]          INT              NOT NULL,
    [ItemPriority]       INT NOT NULL,
    [CreationTime]       DATETIME         NULL DEFAULT GETUTCDATE(),
    [LastAccessTime]     DATETIME         NULL DEFAULT GETUTCDATE(),
     -- other columns
 );

 CREATE UNIQUE CLUSTERED INDEX [JobItemsIndex]
    ON [dbo].[JobItems]([ItemId] ASC);
 GO

CREATE INDEX [GetItemToProcessIndex]
    ON [dbo].[JobItems]([ItemState], [ItemPriority], [CreationTime])
    INCLUDE (LastAccessTime);
GO

and this query:

UPDATE TOP (150) JobItems
SET ItemState = 17
WHERE
    ItemState IN (3, 9, 10)
    AND LastAccessTime < DATEADD (day, -2, GETUTCDATE())
    AND CreationTime < DATEADD (day, -2, GETUTCDATE());

I reviewed the actual plan, and there's just one index seek with the predicate exactly as in the WHERE - no additional "bookmark lookups" to retrieve LastAccessTime even though the latter is only "included" into the index, not part of the index.

It looks to me like this behavior contradicts the rule that the column must be part of the index, and not just "included".

Is the behavior I observe the right one? How can I know in advance if my WHERE benefits from an included column or needs the column to be part of the index?

Your Predicate is different to your Seek Predicate.

A Seek Predicate is used to search the ordered data in the index. In this case, it'll be doing three seeks, one for each ItemState that you're interested in. Beyond that, the data is in ItemPriority order, so no further "Seek" operation can be done.

But before the data is returned, it checks every row using the Predicate, which I refer to as the Residual Predicate. It's done on the results of the Seek Predicate.

Any included column is not part of the ordered data, but can be used to satisfy the Residual Predicate, without having to do the extra Lookup.

You can see material I've written on this around Sargability. Check for a session at SQLBits in particular, at http://bit.ly/Sargability

Edit: To show the impact of Residuals better, run the query using the undocumented OPTION (QUERYTRACEON 9130), which will separate out the Residual into a separate Filter operator (which is actually an earlier version of the plan before the residual gets moved into the Seek operator). It clearly shows the impact of an ineffective Seek, by the number of rows being passed left to the Filter.

It's also worth noting that because of the IN clause on ItemState, the data being passed left is actually in ItemState order, not in ItemPriority order. A composite index on ItemState followed by one of the dates (eg (ItemState, LastAccessTime)) could be used to have three Seeks (notice the Seek Predicate shows three seeks within the one Seek operator), each against two levels, producing data that is still in ItemState order (eg, ItemState=3 and LastAccessTime less than something, then ItemState=9 and LastAccessTime less than something, and then ItemState=10 and LastAccessTime less than something).

An index on (ItemState, LastAccesTime, CreationTime) would be no more useful than one on (ItemState, LastAccessTime) because the CreationTime level is only useful if your Seek is for a particular ItemState and LastAccessTime combination, not a range. Like how the phone book isn't in FirstName order if you are interested in Surnames beginning in F.

If you want a composite index but you are never going to be able to use the later columns in Seek Predicates because of the way you use the earlier columns, then you may as well have them as included columns, where they take less space in the index (because they're only stored at the leaf level of the index, not the higher levels) but can still avoid lookups and get used in Residual predicates.

As per the term Residual Predicate - that's my own term for this property of a Seek. A Merge Join explicitly calls it its equivalent a Residual Predicate, and the Hash Match calls its one a Probe Residual (which you might get from TSA if you match for hash). But in a Seek they just call it Predicate which makes it seem less bad than it is.


Performance after creating new MySQL server When I clone an existing MySQL server, it takes for the same query executed against the same data and producing the same results significantly more time to execute on the new server. It seems the query is running forever (over 5 mins before I kill it as opposed to 15-20 second on the origin server) in the very beginning right after the new server was created then, as some time passes by, the query performance improves and finally it takes several hours before the query in question produces the same execution time as on the origin server.

I experienced this when I clone an AWS EC2 instance running a MySQL server and also when I add a new read replica to an AWS Aurora MySQL cluster.

What is happening in the background that makes the new server perform much worse? Is there a way to monitor / get feedback on these processes?

There are two things you can look into:

Query cache
Buffer pool

Query cache will cache query results (depending on QCache configuration). When the same query is run again, results are returned from the cache instead of MySQL fetching the data directly from the tables. If the Query Cache is full and a new query is executed that needs caching, then the least used Query results are flushed from the Cache to make space for the new query results and so on.

Buffer Pool: InnoDB maintains a storage area called the buffer pool for caching table data and indexes in memory. So if your tables use InnoDB storage engine (or XtraDB for that matter), then the query execution times will improve over a period of time as the Buffer Pool becomes "warm".

Having said that, if your queries are taking too long to run in the first instance, then they may be a candidate for tuning. If you can post results of:

SHOW CREATE table <table name> for the tables involved in the query
the query that is slow

then that will provide a ground for looking into possible improvements to the query and/or indexes that may need to be created/modified.


How to formulate equality predicate on POINT column in PostgreSQL? How to select from a table with an equality predicate on point column?
To be clear: it's a native Postgres point data type, not geometry(point) from PostGIS.

select a.* from indsolv.address As a where a.location = '93.2321, 21.0321';

Getting this error for the above query:

ERROR:  operator does not exist: point = unknown
LINE 1: ...ct a.* from indsolv.address As a where a.location = '93.2321...
                                                         ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
SQL state: 42883

Also tried:

select a.* from indsolv.address As a where a.location = point('93.2321,21.0321');

Still getting an error.

Try as it might, operator type resolution will find no appropriate operator and give up with the error message you reported.

ERROR:  operator does not exist: point = unknown

Adding an explicit type cast (or the point() function achieving the same) to the right argument like you did in your added second attempt will only succeed in making the error message a bit more revealing:

ERROR:  operator does not exist: point = point

The subtle difference may serve as hint: the problem is not a missing cast (though being explicit about data types is never a bad idea). The problem is that there is, in fact, no equality operator = defined for the data type point. The manual:

  Note that the “same as” operator, ~=, represents the usual notion of
  equality for the point, box, polygon, and circle types. Some of these types
  also have an = operator, but = compares for equal areas only. The other scalar
  comparison operators (<= and so on) likewise compare areas for these types.

For the data type point, there is no = at all. See for yourself (tested in Postgres 12):

SELECT *            -- no rows
FROM   pg_operator
WHERE  oprleft = 'point'::regtype
AND    oprname = '=';

The solution is to use the appropriate "same as" operator ~= instead. Works with or without explicit type cast, with or without enclosing parentheses in the string literal, and with the function notation you tested as well - and in this case with either type of string literal as well as with two separate parameters as there are multiple overloaded versions of the point() function:

SELECT a.* FROM indsolv.address a
WHERE a.location ~= point '(93.2321, 21.0321)';

... WHERE a.location ~= point '93.2321, 21.0321';
... WHERE a.location ~= '93.2321, 21.0321'::point;
... WHERE a.location ~= '(93.2321, 21.0321)'::point;
... WHERE a.location ~= cast('93.2321, 21.0321' AS point);
... WHERE a.location ~= cast('(93.2321, 21.0321)' AS point);

... WHERE a.location ~= '(93.2321, 21.0321)';
... WHERE a.location ~= '93.2321, 21.0321';

... WHERE a.location ~= point('93.2321, 21.0321)';
... WHERE a.location ~= point('(93.2321, 21.0321)');
... WHERE a.location ~= point('93.2321', '21.0321');

Related:

ERROR:could not identify an equality operator for type point
Creating custom “equality operator” for PostgreSQL type (point) for DISTINCT calls


Why is this query on JSON values in SQL Server using an index scan instead of an index seek? I have a table with the following schema:
CREATE TABLE [dbo].[Obj1Json](
    [ObjectID] [int] IDENTITY(1,1) NOT NULL,
    [PointerToSource] [nvarchar](255) NULL,
    [CreateDate] [datetime2](7) NOT NULL,
    [ModifiedDate] [datetime2](7) NOT NULL,
    [Indexes] [nvarchar](max) NULL,
    [vAccountID]  AS (json_value([Indexes],'$.AccountID'))

where the Indexes column contains JSON that looks like:
{
  "AccountID": 73786,
  "AccountName": "5869b4e9-f441-463f-8f6d-93b4f4ff8c75",
  "ProcessLocation": "Start",
  "IsPasswordProtected": true,
  "InvoiceDate": "2020-12-30T09:00:32.8473077-05:00"
}

The vAccountID column is used for an index on the JSON in Indexes:
CREATE INDEX IDX_Obj1Json_AccountID
ON Obj1Json(vAccountID)

The table has about 10.5 million rows of randomly generated data (all data in the Indexes column has the same structure, however). The query I'm running is
SELECT JSON_VALUE(Indexes, '$.AccountID')
  FROM [Obj1Json]
  WHERE JSON_VALUE(Indexes, '$.AccountID') = 69725

which returns 110 results. When looking at the execution plan in SSMS, I see that an index scan is being used, whereas I would expect an index seek to be used instead.

The query returns quickly (under a second), but I'm curious why an index seek isn't being used. Is there any obvious reason why SQL Server is using an index scan instead of a seek?

The issue is that JSON_VALUE returns an NVARCHAR and you sent in an INTEGER. This causes a Convert_Implicit.
If you pass in a VARCHAR or NVARCHAR param/value into the WHERE condition, it does a seek and drops the rows read.


Migrate sharepoint database from SQL Server 2012 express to SQL Server 2012 Standard My sharepoint is using SQL Server 2012 R2 Express as a database Server, and I already have reached the limit of space (10GB). In the other hand I have a SQL Server 2012 Standard with proper license.

Would it be possible to migrate my database to SQL Server Std?
Can I install my SQL Server 2012 Std on the same server I have of my SQL Server Express?
Is there any risk doing that in terms of sharepoint compatibility?

  NOTE: I have installed SQL Server 2012 Std on same server but the db size limit still persist, is this normal?

Would it be possible to migrate my database to SQL Server Std?

Yes, a backup and restore approach would work for this situation. For a detailed migration steps (ignoring the ones that you dont need - as it is for migrating from lower to higher version), refer to this answer.

  Can I install my SQL Server 2012 Std on the same server I have of my SQL Server Express?

Yes, Express and standard editions can co-exists together. Make sure that your server have enough memory, CPU and disk space. Also, make sure to cap the Max memory setting on the instances appropriately.

  Is there any risk doing that in terms of sharepoint compatibility?

We have SharePoint running in our environment on SQL server 2012 standard edition, so I dont see any issues. What risks are you referring to ?

  NOTE: I have installed SQL Server 2012 Std on same server but the db size limit still persist, is this normal?

No that is not normal.  See Database size Maximum Capacity Specifications for SQL Server 2012.

Below script will give you more detailed info about the version you are running :

SELECT
   SERVERPROPERTY ('MachineName') AS PhysicalMachineName,
   SERVERPROPERTY ('ServerName') AS SQLServerName,
   SERVERPROPERTY ('Edition') AS ServerEdition,
   CAST(SUBSTRING(@@Version,charindex('SQL',@@version, 1),15) AS VARCHAR(255)) +  ' + '
       + CAST (SERVERPROPERTY ('productlevel')AS VARCHAR (50)) + ' + (Build'
       + CAST (SERVERPROPERTY ('ProductVersion') AS VARCHAR (50)) +')' AS ProductVersion,
    RIGHT(@@version, (Len(@@Version)-charindex('Windows',@@version, 1))+1) AS [O.S.],
 SERVERPROPERTY ('Collation') AS Collation


PostgreSQL storing vectors and computing dot product I am working on a small NLP project in Python that involves performing text similarity on documents(news articles, blog posts etc) and I am thinking of storing each document features vector in Postgres, for instance say I have a document with the content Breakthrough on cancer research..., assuming after all the Python preprocessing it would become something like [(1, 0.225), (2, 0.1), (5, 0.11),...] where the first item in the tuple represents the index of the "feature" (in this case a word), and the second item represents its "score/magnitude".

Now if I have a second document with a similar data structure, say query_vector = [(1, 0.525), (6, 0.9), (7, 0.1221),...] I need to run a query like SELECT * FROM vectors_table WHERE dot_product(vectors_table.features_vector, query_vector) > 0.5. What dot_product will do is multiple the "score/magnitude" of both vectors, which in the example above its gonna be SUM(0.525*0.225, 0.9*0, 0.1221*0, ...) (0 because the other vector don't have any score for the feature with index 6 or 7).

I have a working solution but it is way too slow. My most straightforward solution is to store the vectors as jsonb so it'll become something like {1: 0.225, 2: 0.1, 5:0.11, ...} and I also have just the keys on the jsonb in another column (for faster array intersection computation later on) then I have an UDF as such:

CREATE OR REPLACE FUNCTION dot_product_overlap(query jsonb, query_keys anyarray, target jsonb, target_keys anyarray)
RETURNS float AS $$
DECLARE
    row integer;
    result float := 0;
BEGIN
       FOR row IN SELECT * FROM array_intersect(query_keys, target_keys)
       LOOP
          result:=result+((query->>row)::float*(target->>row)::float);
       END LOOP;
    RETURN result;
END;
$$  LANGUAGE plpgsql;

WHERE array_intersect is another UDF to compute the intersection between 2 array (something like & in intarray). What this UDF did was first compute the intersection between two array of keys and then only compute the multiplication if both vectors has the same key and lastly sum it all up.

This actually works perfectly, just that its extremely slow, running a query against a table with 5000 documents takes ~5s. Now I was wondering is there a more efficient way to store these feature vectors and performing the dot product? I researched a little bit and stumbled upon MADlib cosine_similarity function (which is essentially dot product in this case) but AFAIK it doesn't fit the structure of my feature vectors.

You should write the function in C if you want it to run as fast as possible. Then it might also be a good idea to write your own data type that is stored with a hash table.

If that is too hard, and you don't need that last little bit of performance, you could try to rewrite your function in Perl or Python — both can be run inside the database.


How to recover a lost password in MySQL 5.7.11? (for Windows) I am running MySQL 5.7.11 on Windows 10

I am unable to login to root through the command line client since I lost my password.

How do I change/reset the old password?

Please help, and thanks in advance.

If you installed MySQL as a service, you need the following:
STEP 01
Open Windows Command Line as Administrator and run
C:\> net stop mysql

STEP 02
Add this to C:\Program Files (x86)\MySQL\MySQL 5.7\my.ini under the [mysqld] group header
[mysqld]
skip-grant-tables

STEP 03
C:\> net start mysql
C:\> mysql

You should be logged into mysql
STEP 04
Suppose you want root@localhost's password to be hello, run this
UPDATE mysql.user SET password=password('hello') where user='root' and host='localhost';
exit

STEP 05
C:\> net stop mysql

STEP 06
Remove the skip-grant-tables option from the my.ini
STEP 07
C:\> net start mysql

STEP 08
C:\> mysql -uroot -p

This should prompt you for the password. Enter new password and you should be in.
ALTERNATIVE
Instead of creating my.ini, do the following after you have shutdown MySQL
C:\> cd C:\Program Files\MySQL\MySQL Server 5.7\bin
C:\> start mysqld.exe --skip-grant-tables
C:\> mysql

You should now be inside mysql without a password. Next run this
UPDATE mysql.user SET password=password('hello') where user='root' and host='localhost';
exit

Back at the command line, shutdown mysql like this:
C:\> mysqladmin shutdown

Now, you start MySQL57 Service back up


Ndb cluster change variable for heartbeat and disconnect I search for a variable that I can increase in the server that I don't get any more this error:

Node 2 missed heartbeat 2
Node 3 Disconnected
Node 2 Disconnected

I don't find anything.

Regards,

Configuration variable HeartbeatIntervalDbDb, see in manual:
https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-ndbd-definition.html#ndbparam-ndbd-heartbeatintervaldbdb


MySQL : 30GB of binlogs in 6 Hours I have a mysql master/master setup with tungsten replicator. My server is reaching out of space, occupied around 30G in 6 hours. I'm not sure how to handle this. Would be great if some one can help me out of this.

Thanks,
Swaroop.

If you want to erase all binary logs older than 30 minutes, do this

mysql> PURGE BINARY LOGS BEFORE NOW() - INTERVAL 30 MINUTE;

If you want to erase all binary logs except the last one, do this

mysql> PURGE BINARY LOGS BEFORE NOW();

If you want to erase all binary logs before midnight, do this

mysql> PURGE BINARY LOGS BEFORE DATE(NOW()) + INTERVAL 0 SECOND;

If you want to erase all binary logs before midnight 3 days ago, do this

mysql> PURGE BINARY LOGS BEFORE DATE(NOW()) + INTERVAL 0 SECOND - INTERVAL 3 DAY;

I have discussed deleting binary logs like this before

Apr 26, 2013 ; Is it safe to delete mysql-bin files?
May 28, 2014 : Freeing up disk space in 'mysql\data'

Give it a Try !!!


Cannot Update a System Collection Set I'm trying to tweak the "Server Activity" Data Collection set as outlined in this blog post (I wrote the post, btw. Sorry--it's kinda long). It is working in SQL 2008 R2 and also in SQL 2014.  However, when I run msdb.dbo.sp_syscollector_update_collection_item on SQL 2012, I get this error:

  Msg 14696, Level 16, State 1, Procedure
  sp_syscollector_update_collection_item, Line 70  Cannot update or
  delete a system collection set, or add new collection items to it.

Looking at the code of msdb.dbo.sp_syscollector_update_collection_item, I see differences between the SQL versions:

SQL 2008 R2/SQL 2014

IF (@is_system = 1 AND (@new_name IS NOT NULL))
BEGIN
    -- cannot update, delete, or add new collection items to a system collection set
    RAISERROR(14696, -1, -1);
    RETURN (1)
END

SQL 2012

IF (@is_system = 1 AND (@new_name IS NOT NULL OR @parameters IS NOT NULL))
BEGIN
    -- cannot update, delete, or add new collection items to a system collection set
    RAISERROR(14696, -1, -1);
    RETURN (1)
END

I'm passing in a non-NULL value for @parameters, so I fully understand why I'm getting the error on SQL 2012.

Questions:

Why is the code different for SQL 2012?
Is it safe to manually change the code for msdb.dbo.sp_syscollector_update_collection_item?

Update: (version numbers for further clarification)

Ver     SP  Build Number
--------------------------
2008 R2 SP3 10.50.6000.34
2012    SP2 11.0.5058.0
2014    SP1 12.0.4100.1

Update2:
I've updated SQL 2012 to SP3 (11.0.6020.0)
The issue persists.

I think it's safe to say the or @parameters IS NOT NULL in SQL Server 2012 is a bug.

SQL Server 2012 v11.0.6260.1 also has the erroneous code.
SQL Server 2016 v13.0.4446.0 does not have that piece.
SQL Server 2017 v14.0.3025.34 does not have that piece.

Furthermore, the code prevents sp_syscollector_validate_xml from being called for non-system collection sets.

Since the stored procedure appears to be a system-stored-procedure, you'll likely need to use sp_MS_marksystemobject to mark the stored procedure as a system-stored-procedure after you modify it to remove the or @parameters IS NOT NULL code.

Be aware that changing procedures that have been shipped by Microsoft may make support more difficult.  I would recommend against making this change on production-critical instances.

For reference, the code as seen in my SQL Server 2012 instance is:

CREATE PROCEDURE [dbo].[sp_syscollector_update_collection_item]
    @collection_item_id        int = NULL,
    @name                    sysname = NULL,
    @new_name                sysname = NULL,
    @frequency                int = NULL,
    @parameters                xml = NULL
AS
BEGIN
    -- Security check (role membership)
    IF (NOT (ISNULL(IS_MEMBER(N'dc_operator'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
    BEGIN
        RAISERROR(14677, -1, -1, 'dc_operator')
        RETURN(1) -- Failure
    END

    -- Security checks (restrict functionality for non-dc_admin-s)
    IF ((NOT (ISNULL(IS_MEMBER(N'dc_admin'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
        AND (@new_name IS NOT NULL))
    BEGIN
        RAISERROR(14676, -1, -1, '@new_name', 'dc_admin')
        RETURN (1) -- Failure
    END
    IF ((NOT (ISNULL(IS_MEMBER(N'dc_admin'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
        AND (@parameters IS NOT NULL))
    BEGIN
        RAISERROR(14676, -1, -1, '@parameters', 'dc_admin')
        RETURN (1) -- Failure
    END

    DECLARE @retVal int
    EXEC @retVal = dbo.sp_syscollector_verify_collection_item @collection_item_id OUTPUT, @name OUTPUT
    IF (@retVal <> 0)
        RETURN (@retVal)

    IF (@frequency < 5)
    BEGIN
        DECLARE @frequency_as_char VARCHAR(36)
        SELECT @frequency_as_char = CONVERT(VARCHAR(36), @frequency)
        RAISERROR(21405, 16, -1, @frequency_as_char, '@frequency', 5)
        RETURN (1)
    END

    IF (LEN(@new_name) = 0)  -- can't rename to an empty string
    BEGIN
      RAISERROR(21263, -1, -1, '@new_name')
      RETURN(1) -- Failure
    END

    -- Remove any leading/trailing spaces from parameters
    SET @new_name            = LTRIM(RTRIM(@new_name))

    DECLARE @collection_set_name sysname
    DECLARE @is_system              bit
    DECLARE @is_running             bit
    DECLARE @collector_type_uid     uniqueidentifier
    DECLARE @collection_set_id      int
    SELECT @is_running = s.is_running,
           @is_system = s.is_system,
           @collection_set_name = s.name,
           @collector_type_uid = i.collector_type_uid,
           @collection_set_id = s.collection_set_id
    FROM dbo.syscollector_collection_sets s,
         dbo.syscollector_collection_items i
    WHERE s.collection_set_id = i.collection_set_id
    AND i.collection_item_id = @collection_item_id

    IF (@is_system = 1 AND (@new_name IS NOT NULL OR @parameters IS NOT NULL))
    BEGIN
        -- cannot update, delete, or add new collection items to a system collection set
        RAISERROR(14696, -1, -1);
        RETURN (1)
    END

    IF (@parameters IS NOT NULL)
    BEGIN
        EXEC @retVal = dbo.sp_syscollector_validate_xml @collector_type_uid = @collector_type_uid, @parameters = @parameters
        IF (@retVal <> 0)
            RETURN (@retVal)
    END

    -- if the collection item is running, stop it before update
    IF (@is_running = 1)
    BEGIN
        EXEC @retVal = sp_syscollector_stop_collection_set @collection_set_id = @collection_set_id
        IF (@retVal <> 0)
            RETURN(1)
    END

    -- all conditions go, perform the update
    EXEC @retVal = sp_syscollector_update_collection_item_internal
                            @collection_item_id = @collection_item_id,
                            @name = @name,
                            @new_name = @new_name,
                            @frequency = @frequency,
                            @parameters = @parameters

    -- if you stopped the collection set, restart it
    IF (@is_running = 1)
    BEGIN
        EXEC @retVal = sp_syscollector_start_collection_set @collection_set_id = @collection_set_id
        IF (@retVal <> 0)
            RETURN (1)
    END

    RETURN (0)
END

The code in SQL Server 2016 is:

CREATE PROCEDURE [dbo].[sp_syscollector_update_collection_item]
    @collection_item_id        int = NULL,
    @name                    sysname = NULL,
    @new_name                sysname = NULL,
    @frequency                int = NULL,
    @parameters                xml = NULL
AS
BEGIN
    -- Security check (role membership)
    IF (NOT (ISNULL(IS_MEMBER(N'dc_operator'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
    BEGIN
        RAISERROR(14677, -1, -1, 'dc_operator')
        RETURN(1) -- Failure
    END

    -- Security checks (restrict functionality for non-dc_admin-s)
    IF ((NOT (ISNULL(IS_MEMBER(N'dc_admin'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
        AND (@new_name IS NOT NULL))
    BEGIN
        RAISERROR(14676, -1, -1, '@new_name', 'dc_admin')
        RETURN (1) -- Failure
    END
    IF ((NOT (ISNULL(IS_MEMBER(N'dc_admin'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
        AND (@parameters IS NOT NULL))
    BEGIN
        RAISERROR(14676, -1, -1, '@parameters', 'dc_admin')
        RETURN (1) -- Failure
    END

    DECLARE @retVal int
    EXEC @retVal = dbo.sp_syscollector_verify_collection_item @collection_item_id OUTPUT, @name OUTPUT
    IF (@retVal <> 0)
        RETURN (@retVal)

    IF (@frequency < 5)
    BEGIN
        DECLARE @frequency_as_char VARCHAR(36)
        SELECT @frequency_as_char = CONVERT(VARCHAR(36), @frequency)
        RAISERROR(21405, 16, -1, @frequency_as_char, '@frequency', 5)
        RETURN (1)
    END

    IF (LEN(@new_name) = 0)  -- can't rename to an empty string
    BEGIN
        RAISERROR(21263, -1, -1, '@new_name')
        RETURN(1) -- Failure
    END

    -- Remove any leading/trailing spaces from parameters
    SET @new_name            = LTRIM(RTRIM(@new_name))

    DECLARE @collection_set_name sysname
    DECLARE @is_system              bit
    DECLARE @is_running             bit
    DECLARE @collector_type_uid     uniqueidentifier
    DECLARE @collection_set_id      int
    SELECT @is_running = s.is_running,
            @is_system = s.is_system,
            @collection_set_name = s.name,
            @collector_type_uid = i.collector_type_uid,
            @collection_set_id = s.collection_set_id
    FROM dbo.syscollector_collection_sets s,
            dbo.syscollector_collection_items i
    WHERE s.collection_set_id = i.collection_set_id
    AND i.collection_item_id = @collection_item_id

    IF (@is_system = 1 AND (@new_name IS NOT NULL))
    BEGIN
        -- cannot update, delete, or add new collection items to a system collection set
        RAISERROR(14696, -1, -1);
        RETURN (1)
    END

    IF (@parameters IS NOT NULL)
    BEGIN
        EXEC @retVal = dbo.sp_syscollector_validate_xml @collector_type_uid = @collector_type_uid, @parameters = @parameters
        IF (@retVal <> 0)
            RETURN (@retVal)
    END

    -- if the collection item is running, stop it before update
    IF (@is_running = 1)
    BEGIN
        EXEC @retVal = sp_syscollector_stop_collection_set @collection_set_id = @collection_set_id
        IF (@retVal <> 0)
            RETURN(1)
    END

    -- all conditions go, perform the update
    EXEC @retVal = sp_syscollector_update_collection_item_internal
                            @collection_item_id = @collection_item_id,
                            @name = @name,
                            @new_name = @new_name,
                            @frequency = @frequency,
                            @parameters = @parameters

    -- if you stopped the collection set, restart it
    IF (@is_running = 1)
    BEGIN
        EXEC @retVal = sp_syscollector_start_collection_set @collection_set_id = @collection_set_id
        IF (@retVal <> 0)
            RETURN (1)
    END

    RETURN (0)
END


Dynamically define a RETURN table (column type, name) for subsequent loop I would like to execute ST_Intersection(clipper_geom, clipped_geom) on number of rows coming from a table.

https://postgis.net/docs/ST_Intersection.html

https://postgis.net/docs/ST_Intersects.html

POSTGIS intersection does not support the processing of multiple geometries natively, differently from ST_Intersects() and I had to design a function (returning a table) that selects rows intersecting with my clipper_geom using ST_Intersects(), loop through the result set and compute each intersection with ST_Intersection(). The geom field and clipped_geom_wkt are the ones recording the clipped geometry.

The function works, but I need a different one for every table we want to produce the clipping. I would like to read the input table dynamically (column name and type) and define both in the RETURN statement.

All field names and types are the same, only geom is updated and clipped_geom_wkt is added.

I tried to search Stack Overflow and I found examples on how to create dynamic table structure but none performs a subsequent LOOP over the first results, where the column names have to be matched to insert / update the new data.

This is what I have come up so far, but I am unsure about how to perform the LOOP part, to add the clipped_geom_wkt field and update the geom field. Some responses advise to use RETURNS TABLE (...) if more fields are added to SETOF ...

https://stackoverflow.com/questions/17821482/easy-way-to-have-return-type-be-setof-table-plus-additional-fields

But then it seems that dynamically generated columns are only supported with RETURNS SETOF ...

https://stackoverflow.com/questions/11740256/refactor-a-pl-pgsql-function-to-return-the-output-of-various-select-queries/11751557#11751557

https://stackoverflow.com/questions/8605174/postgresql-error-42601-a-column-definition-list-is-required-for-functions-ret/8611675#8611675

CREATE OR REPLACE FUNCTION clip_palin_polygon_complete(clipped_table text,clipper_geom text, age_sequence VARCHAR)
RETURNS TABLE (rec clipped_table, clipped_geom_wkt text)) AS $$ --not sure if this is the right way to do it...
DECLARE var_r record;
BEGIN
    FOR var_r IN (
        SELECT * FROM clipped_table
        WHERE clipped_table.seq IN (age_sequence)
        AND ST_Intersects(ST_GeomFromText(clipper_geom,4326), clipped_table.geom)
    )
    LOOP
        /*
        these are the original table fields that I would like to keep and match
        dynamically with any table I have as input (clipped_table)
        objectid := var_r.objectid;
        seq := var_r.seq;
        -- etc.
        */

        --below there are the only two fields that need modifying
        geom := (
            SELECT ST_Intersection(ST_GeomFromText(clipper_geom, 4326), var_r.geom) AS geom);
        clipped_geom_wkt := (
            SELECT
            ST_AsText(ST_Intersection(ST_GeomFromText(clipper_geom,4326), var_r.geom)) AS clipped_geom_wkt);
        RETURN NEXT;
    END LOOP;
END; $$
LANGUAGE 'plpgsql'

What you might need

You should be able to avoid all the complication with a simple SELECT instead:

SELECT t.*, ST_Intersection(x.geom, t.geom) AS geom2
FROM   tbl t  -- replace tbl with actual table
JOIN   ST_GeomFromText($clipper_geom, 4326) AS x(geom) ON ST_Intersects(x.geom, t.geom)
WHERE  t.seq = $age_sequence;

Minor difference: you get the original geom and the intersection geom2 in the result. Add the redundant ST_AsText(ST_Intersection(x.geom, t.geom)) AS clipped_geom_wkt to the SELECT list if you really need it.

What you ask

I understand your question like this:

I have various input tables, each has a column geom geometry. I want a function to take a table name and clipper_geom geometry as input and return all rows intersecting with my clipper_geom. One column clipped_geom_wkt text shall be appended and the value of geom changed, each showing the intersection with clipper_geom.

That's not trivial. SQL is a static language, a function demands to know the return type at call time at the latest. We can achieve dynamic results with polymorphic types, but we need a well-known row type to work with. The row type of an existing table is good for it, but you want to append another column. That's not easily possible. The workaround is to return the polymorphic row type, plus an extra column (like you seem to have tried already). Decompose the row type in the function call to arrive at your desired result:

CREATE OR REPLACE FUNCTION my_func(_tbl ANYELEMENT
                                 , clipper_geom text
                                 , age_sequence text)
  RETURNS TABLE (tbl ANYELEMENT, clipped_geom_wkt text) AS
$func$
DECLARE
   _geom geometry := ST_GeomFromText(clipper_geom, 4326);  -- compute once
BEGIN
   FOR tbl IN EXECUTE format(
     'SELECT * FROM %s
      WHERE  seq = $1
      AND    ST_Intersects($2, geom)'
    , pg_typeof(_tbl)
      )
   USING age_sequence  -- data type must match!
       , _geom
   LOOP
      tbl := tbl #= hstore('geom', ST_Intersection(_geom, tbl.geom));
      clipped_geom_wkt := ST_AsText(tbl.geom);
      RETURN NEXT;
   END LOOP;
END
$func$  LANGUAGE plpgsql;  -- don't quote the language name

Call (important!):

SELECT (tbl).*, clipped_geom_wkt
FROM my_func(NULL::public.clipped_table, $my_clipper_geom, $my_age_sequence);

Works for me in Postgres 10.

Notable features:

Polymorphic types.
Note how the row variable tbl is defined implicitly in the RETURNS clause, where it copies the input type dynamically (ANYELEMENT). So you are not bound to use SETOF with polymorphic types like you speculated.
Related (you linked to that one yourself):

Refactor a PL/pgSQL function to return the output of various SELECT queries

FOR-IN-EXECUTE to loop over a dynamic query.
Assignment of a yet-unknown column with the hstore operator #=. See:

How to set value of composite variable field using dynamic SQL

Decomposition of composite type in the function call with (tbl).*

Simpler

I stuck to your design above, but it's unnecessary complication for the given example. The extra column clipped_geom_wkt is completely redundant, just derive it from geom in the function call. Then we can use the input type directly:

CREATE OR REPLACE FUNCTION my_func2(_tbl ANYELEMENT
                                 , clipper_geom text
                                 , age_sequence text)
  RETURNS SETOF ANYELEMENT AS
$func$
DECLARE
   _geom geometry := ST_GeomFromText(clipper_geom, 4326);
BEGIN
   FOR _tbl IN EXECUTE format(
     'SELECT * FROM %s
      WHERE  seq = $1
      AND    ST_Intersects($2, geom)'
    , pg_typeof(_tbl)
      )
   USING age_sequence -- data type must match!
       , _geom
   LOOP
      _tbl := _tbl #= hstore('geom', ST_Intersection(_geom, _tbl.geom));
      RETURN NEXT _tbl;
   END LOOP;
END
$func$  LANGUAGE plpgsql;

Call:

SELECT *, ST_AsText(geom) AS clipped_geom_wkt -- if you need the redundant column
FROM   my_func2(NULL::public.clipped_table, $my_clipper_geom, $my_age_sequence);


SQL Server 2008 database is not coming online after restore.. Pleae need help asap I have restored a 1TB database and now it shows the restore completed 100% but the database is not accessible.

sp_who2 has a SPID 50 performing this restore but current command its showing is 'CREATE INDEX' and this SPID is not completing from quite long time.

When I verified through sp_whoisactive this spid is trying to create index on a table of 12gb size but the % complete shows as 100%...

Now for 2 hours the DB has not been accessible and this spid is not finishing. Main thing to note is this restore happens daily and used to work fine from ages.

Any help ides would be much appreciated. I already tried taking SQL Server services offline and removed the underlying db files and start fresh restore but still it got stuck in same way....

I know this is an old question but will leave this here for anyone else who comes looking.
Ran into this problem today and was able to get it going.

This is difficult to believe but disabling parallelism got it online in my case.

I never would have thought to even try that, only I was watching the sysprocesses for that DB and was seeing the parallelism split / cxpacket wait behavior… the wait types would change like it was working on something.
Thinking maybe maxdop=1 would help whatever this was by not splitting up, changed it and the DB immediately came online.

exec sp_configure 'show advanced options', 1
go
reconfigure
go
exec sp_configure 'max degree of parallelism', 1
go
reconfigure
go

Other info:

Mine was also SQL 2008 database is 176GB
Also saw (at some point) a system spid (under 51) showing "Create Index"
Runs change data capture in production (not sure that had anything to do with it)
Was restoring copy to a dev environment as part of a regular data refresh from production.
Disk space was very tight on the destination server it had just enough room for the data and log files to restore, but was under 10 meg free at that point.
I think the lack of space may have helped cause the problem? I was able to free up some space before the maxdop change, but the database was still not usable until the maxdop setting was changed.
sys.databases showed the state as 'online'
the database looked usable in every way but any attempt to use it would time out


Why can't I log in to SYSTEM without AS SYSDBA When loging in to sqlplus as SYSTEM, why does the login fail with an invalid username/password error if I do not include "AS SYSDBA"

C:\Windows\System32>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 16 01:00:13 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> conn system/password
ERROR: ORA-01017: invalid username/password; logon denied

SQL> conn system/password as sysdba
Connected.
SQL>

I have a script that I need to run locally.  This script runs fine on other machines, but fails with the error above for me.

Because you are not logged in as the user system. When you execute conn system/password as sysdba, Oracle won't even check the user and password (--> from the local systeem where the db is running on). If you enter the system/password as sysdba and then execute 'show user', you'll notice you are logged in as user 'SYS'. So you are actually entering the wrong password when you execute the conn system/password, Oracle doest not lie.
Check http://docs.oracle.com/cd/B28359_01/server.111/b28310/dba006.htm#ADMIN11046 for more information. Because what I told you is not the whole story.


pg_restore does not restore blobs I have a database with many large objects. A table have a column OID that refers to these large objects. I dump the database daily, including all large objects, with command:

pg_dump --format=c --file=/var/backups/pgsql/db-neos.pgdump \
  --compress=6 --blobs neos

and then I restore it, on a different machine, with command:

createdb neos
pg_restore -d neos /mnt/db-neos.pgdump

but, while all tables are correctly created, large objects aren't.
Instead, an error message is displayed by pg_restore:

pg_restore: [compress_io] decompressione dei dati fallita: (null)
(english: could not uncompress data)

I've faced the same issue some months ago, and I have managed to solve it with a bash script. This is the piece of code that take care to backup large objects, and save it to a local folder (/path/to/destination/lo/folder/) with filename = oid (the filename is important in order to restore lo with same oid) :

echo -e "going to export largeobjects belonging to community $i..." && sleep 2
psql -U <username> -X -c "SELECT file_oid FROM <my_table>" \
    --single-transaction \
    --set AUTOCOMMIT=off \
    --set ON_ERROR_STOP=on \
    --no-align \
    -t \
    --field-separator ' ' \
    --quiet \
    -d <my_source_db> \
| while read file_oid ; do
    echo "Exporting largeobject OID: $file_oid"
    ${PSQL} -d <my_source_db> -c "SELECT lo_export($file_oid, '/path/to/destination/lo/folder/$file_oid');"
done

Then to restore them I've written this other piece of code:

echo "going to import largeobjects belonging to community $i..." && sleep 2
LOBJECTS=/path/to/destination/lo/folder/*
for f in $LOBJECTS
do
    echo "Processing $f file..."
    filename=$(basename "$f")
    oid=${filename}

    psql -U <username> -d <my_source_db> -c"
    -- check if largeobject is already present, if it is delete it
    SELECT lo_unlink(${oid});"

    psql -U <username> -d <my_source_db> -c"
    -- import largeobjects
    SELECT lo_import('$f', ${oid});
    "
done

Out there it may exist a simpler solution, but at the time I've not been able to find it, this is why used this approach. I hope you will be more lucky and find a cleaner solution. I'll follow this post in case I can learn something useful too : )


Oracle remote OS authentication, security risks with domain users What security risks can be with domain users?

create user "OPS$KA\USERNAME" identified externally.

grant create session to "OPS$KA\MARIK";

Then MARIK that is in KA domain, can logon to the database without password.

I wander to know if oracle checks that the user is a really domain user?

For example is there a risk that somebody with the computer name LB and username MARIK can connect to the database , even it is not a domain user?

Beware the OS authentication is done by client machine, not by the database server.

I think that within reasonable pessimism to Oracle tools you could expect that it boils down to this:  database server receives a TCP connection from whichever IP address that might pass the network path, and that connection just claims "I promise I've done OS authentication and, trust me, this connection is made on behalf of OPS$JohnSmithCEO."  Neither the database client or database server is given OS password for additional OS authentication! And you can't even trust that this connection came from a valid Oracle software. It can be man-in-the-middle in reality. It's worse than telnet.

Whatever "Windows-specific" checks are done by Oracle, they seem contrived and you can't really trust they are complete and secure.


Oracle - Scan listener and multiple databases I have a two node RAC database server hosting 8 databases. I have 3 scan listeners running. 2 in node 1 and one in the 2nd node. All the 8 databases are running for different applications.

Are these 3 listeners sufficient to manage the work load for different 8 databases? So only the service names will be different for the databases for the respective applications to talk to right?

Are there any best practices to have more scan listeners run on different ports or or is it recommended that way?

Listener is a process running in the Oracle Server machine. The responsibility of the listener is to listen for the incoming database connection request and spawning server process to handle the request(in Dedicated Server mode) or handing over to dispatcher process(in Shared-Server mode). In RAC architecture we have SCAN listener to redirect connection requests to the nodes. So just to redirect the connection three SCAN listeners are enough. However we can have more SCAN listeners by adding more SCAN VIPs.

  No of scan IPs has been determined after careful study by Oracle base development and has been found sufficient as of now. You can still configure more if you want to. But that, I believe, would be the wastage of the IPs. more..

  If the SCAN name resolves to one IP address, root script (root.sh or rootupgrade.sh) will create the number of SCAN VIP resources(ora.scan1.vip) and corresponding SCAN listener resource(ora.LISTENER_SCAN1.lsnr) depend on how many IP address the SCAN name resolves to, i.e.if the SCAN name resolves to two IP addresses, it will create two SCAN VIP resources and two corresponding SCAN listener resource.more...[MOS Account required)

Here is a very good discussion for the same kind of question.

Why 11gR2 scan have limited maximum to 3 ips


Query to collect the recent TimeSampled row using inner joins in MS SQL I have written below inner join query to get the data from my database tables. But  output of this data is coming up with almost 140+ lines. Output is below. I need data in only one row with the most recent value by "TimeSampled"

select DisplayName, ObjectName, CounterName, InstanceName, SampleValue, max(TimeSampled) as TimeSampled
from PerformanceDataAllView pdv with (NOLOCK)
inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid
inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId
where ObjectName = 'Processor'  AND TimeSampled >= DATEADD(MINUTE,-5,GETDATE())
AND DisplayName Like 'BLRW1ETST01.jcp.com'
AND CounterName = '% Processor Time'
GROUP BY DisplayName, ObjectName, CounterName, InstanceName, SampleValue
ORDER BY  TimeSampled desc

How I can retrive results with only last Time Sampled row with respect to DisplayName. I have multiple servers (DisplayName) like this to retrieve the data.

Thanks,
Ramu Chittiprolu

One way is to use the ROW_NUMBER window function to order each row grouped by DisplayName (or better, the underlying primary key, if different and DisplayName isn't indexed), then pick out the latest using the result:

SELECT DisplayName, ObjectName, CounterName, InstanceName, SampleValue, TimeSampled
FROM (
    SELECT DisplayName, ObjectName, CounterName, InstanceName, SampleValue, TimeSampled,
        OrderNum = ROW_NUMBER() OVER (
            PARTITION BY DisplayName
            ORDER BY TimeSampled DESC)
    FROM PerformanceDataAllView pdv
    INNER JOIN PerformanceCounterView pcv
    ON pdv.performancesourceinternalid = pcv.performancesourceinternalid
    INNER JOIN BaseManagedEntity bme
    ON pcv.ManagedEntityId = bme.BaseManagedEntityId
    WHERE ObjectName = 'Processor'
        AND TimeSampled >= DATEADD(MINUTE,-5,GETDATE())
        AND DisplayName LIKE 'BLRW1ETST01.jcp.com'
        AND CounterName = '% Processor Time'
) t
WHERE OrderNum = 1;

That said, there are a few oddities with your query

It's better to qualify column references with the table alias to avoid mishaps if same-named columns get added to different tables
You use LIKE for filtering on DisplayName, yet specify a simple string rather than a pattern
The filter on TimeSampled means, if the most recent entry for a DisplayName is more than five minutes ago, it won't be returned despite being the most recent entry, which goes against how you described the requirements
Be careful on thinking NOLOCK is a magic bullet (https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere)


Select records that do not have associations outside a certain list I have a mySQL table of nodes and each node can be associated with many categories.  I have a list of category ids that are acceptable (498, 499).  I want to select only nodes who do not have categories that are not in the list of acceptable category ids.

In this example, I should get Toby and Joseph back, but not Susan...

I was initially trying this, but the IN clause does not exclude other categories:

SELECT n.nid, n.title, category.dir_phys_category_tid
FROM node AS n
INNER JOIN field_data_dir_phys_category   AS category   ON n.nid = category.entity_id
WHERE n.type = 'dir_physicians'
 AND n.`status` = 1
 AND category.dir_phys_category_tid IN ( 498,499 )

Answer: modified from RolandoMySQLDBA's answer below, with formatting & naming changes

SELECT desired_categories.*
FROM
    ( SELECT nid, title, COUNT(1) AS all_categories
        FROM node
        INNER JOIN field_data_dir_phys_category AS category ON node.nid = category.entity_id
        GROUP BY nid
    ) AS A

INNER JOIN
    ( SELECT nid, COUNT(1) AS good_categories
        FROM node
        INNER JOIN field_data_dir_phys_category AS category ON node.nid = category.entity_id
        WHERE category.dir_phys_category_tid IN (498,499)
        GROUP BY nid
    ) AS B USING (nid)

INNER JOIN
    ( SELECT DISTINCT nid, title
        FROM node
    ) AS desired_categories USING (nid)

WHERE all_categories = good_categories;

Here is the query

SELECT C.* FROM
(SELECT nid,COUNT(1) fullcount
  FROM node
  INNER JOIN field_data_dir_phys_category   AS category   ON node.nid = category.entity_id
  GROUP BY nid) A INNER JOIN
(SELECT nid,COUNT(1) goodcount
  FROM node
  INNER JOIN field_data_dir_phys_category   AS category   ON node.nid = category.entity_id
  WHERE dir_phys_category_tid IN (498,499) GROUP BY nid) B USING (nid)
INNER JOIN (SELECT DISTINCT nid,title FROM node) C USING (nid)
WHERE fullcount=goodcount;

First, let's make some sample data.
NOTE: this uses a single table.  The OP used two tables - mine is intended to demonstrate the method used to count categories, which is the central part of my answer.

DROP DATABASE IF EXISTS doub1ejack;
CREATE DATABASE doub1ejack;
USE doub1ejack
CREATE TABLE node
(
    id INT NOT NULL AUTO_INCREMENT,
    nid INT NOT NULL,
    title VARCHAR(20),
    dir_phys_category_tid INT NOT NULL,
    PRIMARY KEY (id)
);
INSERT INTO node (nid,title,dir_phys_category_tid) VALUES
(1171,'Toby',499),(1172,'Susan',500),(1172,'Susan',499),
(1172,'Susan',498),(1173,'Joseph',499),(1173,'Joseph',498);
SELECT * FROM node;

Let's load it

mysql> DROP DATABASE IF EXISTS doub1ejack;
Query OK, 1 row affected (0.01 sec)

mysql> CREATE DATABASE doub1ejack;
Query OK, 1 row affected (0.00 sec)

mysql> USE doub1ejack
Database changed
mysql> CREATE TABLE node
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     nid INT NOT NULL,
    ->     title VARCHAR(20),
    ->     dir_phys_category_tid INT NOT NULL,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.23 sec)

mysql> INSERT INTO node (nid,title,dir_phys_category_tid) VALUES
    -> (1171,'Toby',499),(1172,'Susan',500),(1172,'Susan',499),
    -> (1172,'Susan',498),(1173,'Joseph',499),(1173,'Joseph',498);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>

Let's look at it

mysql> SELECT * FROM node;
+----+------+--------+-----------------------+
| id | nid  | title  | dir_phys_category_tid |
+----+------+--------+-----------------------+
|  1 | 1171 | Toby   |                   499 |
|  2 | 1172 | Susan  |                   500 |
|  3 | 1172 | Susan  |                   499 |
|  4 | 1172 | Susan  |                   498 |
|  5 | 1173 | Joseph |                   499 |
|  6 | 1173 | Joseph |                   498 |
+----+------+--------+-----------------------+
6 rows in set (0.00 sec)

mysql>

Next, let's run the proposed query

mysql> SELECT C.* FROM
    -> (SELECT nid,COUNT(1) fullcount FROM node GROUP BY nid) A
    -> INNER JOIN
    -> (SELECT nid,COUNT(1) goodcount FROM node
    -> WHERE dir_phys_category_tid IN (498,499)
    -> GROUP BY nid) B USING (nid)
    -> INNER JOIN
    -> (SELECT DISTINCT nid,title FROM node) C USING (nid)
    -> WHERE fullcount=goodcount;
+------+--------+
| nid  | title  |
+------+--------+
| 1171 | Toby   |
| 1173 | Joseph |
+------+--------+
2 rows in set (0.00 sec)

mysql>

OK why does it work ? Let's add the count columns, remove the WHERE and see

mysql> SELECT C.*,fullcount,goodcount FROM
    -> (SELECT nid,COUNT(1) fullcount FROM node GROUP BY nid) A
    -> INNER JOIN
    -> (SELECT nid,COUNT(1) goodcount FROM node
    -> WHERE dir_phys_category_tid IN (498,499)
    -> GROUP BY nid) B USING (nid)
    -> INNER JOIN
    -> (SELECT DISTINCT nid,title FROM node) C USING (nid)
    -> ;
+------+--------+-----------+-----------+
| nid  | title  | fullcount | goodcount |
+------+--------+-----------+-----------+
| 1171 | Toby   |         1 |         1 |
| 1172 | Susan  |         3 |         2 |
| 1173 | Joseph |         2 |         2 |
+------+--------+-----------+-----------+
3 rows in set (0.00 sec)

mysql>

Here is the correlation: When the number of categories (fullcount) is equal to the number of categories which are 498 and/or 499 (goodcount), then that row's nid is acceptable.

Give it a Try !!!