Is there way to have logical connection in MongoDB? For example in oracle we can define tns.ora so client can connect with logical name. Although mongos can run on client servers, but when there are many different partner applications we we will need to worry about running daemon on partner machines. Is there alternative to provide mongo connection in a logical way and not necessarily physical mongo sever and port number? Thanks!

If your client applications are connecting directly to MongoDB, they will typically use a connection string which varies depending on the type of deployment:

Standalone - mongod  hostname (and optionally, port)
Replica set - includes one or more mongod hosts in the replica set as a "seed list" to discover the current configuration
Sharded cluster - includes one or more mongos hosts to connect to the sharded cluster

There isn't a direct equivalent of the "logical connection" abstraction. However, since you are already connecting via mongos, it sounds like you have a sharded cluster which does abstract the connection details for the underlying shards. All mongos connecting to the same cluster have to use the same --configdb list.

If you deploy a mongos per application host, each application can connect to the mongos on localhost. Alternatively you could have a fixed set of shared mongos and expose these via DNS cnames that resolve to "local" mongos for that application or data centre.

If you want to further lockdown per-client access, each client application could authenticate using different credentials which limit their access/permissions per-database. MongoDB 2.6 adds further granularity including collection-level access. For more information see Security Concepts in the MongoDB manual.


Two tables with two different charsets I have one table which stores input from user, so to allow national characters I've set charset to utf8mb4.

Then I have second table, which can grow really large (100+ millions of rows) and I'm 100% sure that this table will store only ASCII characters, so I've set charset for this table to latin1 to save disk space.

Is there any downside of having different charsets among tables in one database? Is it considered bad practice?

Or is there any other way how to resolve this issue? (to keep disk occupation minimal and allow national characters)

EDIT:

output of show create table which takes user input (and has utf8mb4)

 CREATE TABLE `sensor` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `label` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` varchar(3000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created` datetime(6) NOT NULL,
  `state` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `region` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `model_reference` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `label` (`label`),
  KEY `id` (`id`),
  KEY `sensor_ibfk_1` (`model_reference`),
 FULLTEXT KEY `fulltext_index`(`label`,`description`,`state`,`region`),
 CONSTRAINT `sensor_ibfk_1` FOREIGN KEY (`model_reference`) REFERENCES      `model` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4      COLLATE=utf8mb4_unicode_ci

output of show create table which is autogenerated (and has latin1)

CREATE TABLE `sensor_history` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `sensor_reference` int(20) DEFAULT NULL,
 `temperature` varchar(16) DEFAULT NULL,
 `pressure` varchar(16) DEFAULT NULL,
 `at` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
 PRIMARY KEY (`id`),
 KEY `sensor_history_ibfk_1` (`sensor_reference`),
 CONSTRAINT `sensor_history_ibfk_1` FOREIGN KEY (`sensor_reference`)     REFERENCES `sensor` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

Each column of each table of each database can have a different CHARACTER SET and COLLATION.  There is no downside unless you JOIN on a column that has different charset and/or collation.

Ascii (7-bit stuff) is a subset of latin1 and of utf8mb4.  As such, the encoding is identical, so no space difference will be seen for purely ascii text.

However, there are a few hiccups when you declare unnecessarily large strings, especially with wider character sets.  Probably you won't hit any hiccups.  Please provide SHOW CREATE TABLE for a review of likely cases.

The client must declare what charset it is using, so UTF-8 is what you would need there.  In that case, any non-utf8mb4 columns would be automatically converted when going between client and server.

More

When storing into VARCHAR(...) CHARACTER SET utf8mb4, there is a 1-byte or 2-byte length overhead, plus

"abc" (ascii) will take 3 bytes - each ascii character takes 1 byte,
"ÛØÎ" will take 6 bytes - most European chars take 1- or 2-bytes,
"José" will take 5 bytes - the "é" takes 2 bytes,
"" (Emoji) will take 12 bytes; each Emoji is 3 or 4 bytes.  Ditto for Chinese.
"$€" (currency symbols) will take 4 bytes -- 1 for "$" plus 3 for Euro.

With CHARACTER SET latin1:

"abc", "ÛØÎ", "José", "$€" can be correctly stored in latin1, using 3,3,4,2 bytes, respectively.
Emoji, all Asian languages, Greek, Cyrillic, and others cannot be represented in latin1.  Garbage ensues.  The particular garbage depends on the various settings; this Q&A lists common garblings.

For clear sailing:

The column must be declared (explicitly or implicitly) with a CHARACTER SET that can hold the characters.  (English will work with any character set, but Emoji will work only with utf8mb4.)
The connection parameters (or SET NAMES) must correctly say what encoding is used in the client.
There must be a mapping between the two charsets.  (English always maps.  (Western Europe can map among latin1, utf8, utf8mb4, but not much else.)


Is a year-to-date dimension the same as a time dimension? If I have a table such as...:

YYMM | Sales
------------
1201 | 10
1202 | 20
1203 | 30
1204 | 40

... then I might describe this as having a time dimension. If I wanted to calculate a year-to-date value of Sales, would "year to date" be a different dimension to time, or is it actually the same and the additional measure implies the different meaning? Such as:

YYMM | Sales | SalesYTD
-----------------------
1201 | 10    | 10
1202 | 20    | 30
1203 | 30    | 60
1204 | 40    | 100

Year to date sales is a different measure.  Depending on your platform (OLAP systems, for example) it may be purely calculated on the fly.  If you have an appropriate unit of analysis to do so, store it, otherwise calculate it on the fly.


How can I create a test copy of my production database that is updated daily on SQL Server? I have a production database on SQL Server.  I want a copy of that database to use for running tests.  I'd like the test copy to be updated nightly, so that I always have access to fresh data every morning.

The simple solution would be a full backup and restore each night, and then run the latest migration scripts on test.  However, the production database is huge (300Gb+), we have to copy across a not-so-fast link, and the whole operation takes longer than one night.

What I would like to do is perform one full backup-restore and then use differential backups each night.   Would it be possible to rollback all changes made on test during the day, then restore the latest diff from prod, then run the latest migration scripts?  If so, how should I do this?

The answer to your question is NO. Even rolling back all changes to the test database, you are still stuck with the fact that you cannot restore a differential backup to a database that is already recovered. You will always have to start over by restoring the latest FULL backup, then restore the latest DIFF that belongs with the latest FULL. Even if you save a copy of a 'FULL' backup on the test server (as Aaron points out in step1), you will eventually have to copy over a newer FULL backup to allow you to restore DIFFS that belong with that FULL.


Fetch Records alternatively from database I have following table where each record indicates either start time or end time of a shift for staff.

The first record is for start time, and the next immediate record is for end time.

SELECT *
FROM  `recorddata`
ORDER BY  `recorddata`.`Date_Time` ASC
LIMIT 0 , 30

What i want is all start and end time together as result of query.

Without considering performance, it is possible to solve by using one query with 3 subquery inside.

SELECT * FROM
   (SELECT * FROM
       (SELECT id, Date_Time, MOD(@no := @no + 1, 2) AS even
        FROM recorddata  ORDER BY Date_Time ASC) l
    WHERE even = 0) a,
   (SELECT id, Date_Time FROM recorddata ORDER BY Date_Time ASC) b
WHERE a.Date_Time < b.Date_Time
GROUP BY a.id


Can't restore SQL Server db from full backup, log processing fails, database in 'restoring' state I am trying to set up a database for development purposes on my PC's local SQL Server Developer Edition 12.0.2000.8. I've got a full database backup and separate transaction-log-only backup files available which were sent to me over the network.

When trying to restore from full backup, after some time (~1 hour maybe, the database is ~270 GB in size), I'm getting an error :

  System.Data.SqlClient.SqlError: An error occurred while processing the
  log for database 'database name'.  If possible, restore from backup.
  If a backup is not available, it might be necessary to rebuild the
  log. (Microsoft.SqlServer.SmoExtended)

After this, the db is in 'Restoring..' state.

I wanted to run something like (got it from this question)

ALTER DATABASE recovery_test_2 SET EMERGENCY;
ALTER DATABASE recovery_test_2 SET SINGLE_USER;

DBCC CHECKDB (recovery_test_2, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

against it, but naturally I can't as the database is in 'Restoring.." state.
Restarting the restore process on it leads to the same error message, dropping and restoring again didn't help too.

How do I get the db up and working? Transactional consistency doesn't matter to me.

The SSMS auto-generated restore script :

  USE [master]
  RESTORE DATABASE [database_name] FROM  DISK = N'D:\database_name.bak' WITH  FILE = 1,
  MOVE N'database_name' TO N'D:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\DATA\database_name.mdf',
  MOVE N'database_name_index' TO N'D:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\DATA\database_name_index.ndf',
  MOVE N'database_name_log' TO N'D:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\DATA\database_name_log.ldf',
  NOUNLOAD,
  STATS = 5

  GO

The result of query suggested by @Craig Efrein

  The log cannot be rebuilt because there were open transactions/users
  when the database was shutdown, no checkpoint occurred to the
  database,  or the database was read-only. This error could occur if
  the transaction log  file was manually deleted or lost due to a
  hardware or environment failure.

From the comments moved to chat, this note from the OP:

I just tried attaching it via GUI. As I selected the .mdf file, the SSMS identified the database consisted of 3 files (data, index, log), but somehow even though I was restoring WITH MOVE, the database details about file path were saying it wrong! So I just pointed them at the old log/data/index file and... The database is online.


How to copy database schema in the same database for testing purposes? I need to copy... by any means, a schema of my database in that same database, let's say, I have SC1 and I have to duplicate it as SC2 without the data contained in SC1 (in the same database).

How could that process be carried out?

I am using Oracle Database 11gR2.

If using Toad: Database -> Export -> Generate Schema Script

You can choose to copy an entire schema (SC1, in your case) and then ensure that it has

Then log in as the SC2 schema user (or as DBA/SYS) and deploy the SC2 schema objects using the "deploy all" script that comes with the export.  If any of the objects use functions/procedures from other schemas, you'll need to ensure that either the correct permissions are set up, or you run this as DBA/SYS.

If using SQL Developer:  Tools -> Database export

Choose the source Database and Ensure "Export DDL" is checked (if you want to copy the objects as well).

Then, select which objects you want to copy.

In the "Specify objects" area, Click "More" and choose the source schema.  Then select the objects and click the right arrow, or click the double right arrow to select all objects.

Keep moving through the wizard and it should export multiple scripts to run.  Run them all and you've got yourself a copy.

NOTE!  I highly recommend you don't do this in production.  I'm hoping you're not trying to create SC2 in a production Database.


Postgres query not using indexes with conditions from multiple tables I'm having trouble with a query that I expect to use my indexes, but it is doing a sequential scan instead. Here's my query with explain:

explain SELECT invoice_uuid AS uuid FROM invoice_tcgplayer_line_items
JOIN invoice_tcgplayer_details USING(invoice_uuid)
JOIN card_inventories ON card_inventories.uuid = invoice_tcgplayer_line_items.inventory_uuid
JOIN cards ON cards.uuid = card_inventories.card_uuid
WHERE order_number = 'bcfong28' OR cards.name ~* 'bcfong28' ;

QUERY PLAN

------------------------------------------------------------------------------------------------------------------
Hash Join  (cost=2087.79..2383.92 rows=3 width=16)
   Hash Cond: (invoice_tcgplayer_line_items.invoice_uuid = invoice_tcgplayer_details.invoice_uuid)
   Join Filter: ((invoice_tcgplayer_details.order_number = 'bcfong28'::text) OR (cards.name ~* 'bcfong28'::text))
   ->  Nested Loop  (cost=2064.62..2349.22 rows=2549 width=31)
         ->  Hash Join  (cost=2064.57..2103.20 rows=2549 width=32)
               Hash Cond: (invoice_tcgplayer_line_items.inventory_uuid = card_inventories.uuid)
               ->  Seq Scan on invoice_tcgplayer_line_items  (cost=0.00..29.65 rows=2549 width=32)
               ->  Hash  (cost=1734.18..1734.18 rows=94395 width=32)
                     ->  Seq Scan on card_inventories  (cost=0.00..1734.18 rows=94395 width=32)
         ->  Index Scan using cards_pkey on cards  (cost=0.06..0.10 rows=1 width=31)
               Index Cond: (uuid = card_inventories.card_uuid)
   ->  Hash  (cost=19.31..19.31 rows=1103 width=35)
         ->  Seq Scan on invoice_tcgplayer_details  (cost=0.00..19.31 rows=1103 width=35)
(13 rows)

It's doing a sequential scan on card_inventories which has a little under 100k rows in it. If i remove the cards.name ~* part of the WHERE clause (and do a SELECT on cards.name instead), it DOES use the primary key index of card_inventories.uuid. Why is this?

Here's the table description for card_inventories:

\d card_inventories
                       Table "public.card_inventories"
    Column     |            Type             | Collation | Nullable | Default
---------------+-----------------------------+-----------+----------+---------
 uuid          | uuid                        |           | not null |
 card_uuid     | uuid                        |           | not null |
 condition     | text                        |           | not null |
 language      | text                        |           | not null |
 quantity      | integer                     |           | not null |
 created_at    | timestamp without time zone |           | not null |
 updated_at    | timestamp without time zone |           | not null |
 sort_order    | text                        |           |          |
 offline       | boolean                     |           | not null |
 buylist_price | integer                     |           |          |
 overstock     | boolean                     |           | not null | false
 playset_price | integer                     |           | not null | 0
Indexes:
    "card_inventories_pkey" PRIMARY KEY, btree (uuid)
    "buylist_price_condition_idx" btree (buylist_price, condition)
    "buylist_price_idx" btree (buylist_price)
    "card_inventories_card_uuid_idx" btree (card_uuid)
    "condition_idx" btree (condition)
Foreign-key constraints:
    "card_inventories_card_uuid_fkey" FOREIGN KEY (card_uuid) REFERENCES cards(uuid) ON DELETE CASCADE

I do have a trgm fulltext index on cards.name as well so that I can use ~*.

The order_number = 'bcfong28' OR cards.name ~* 'bcfong28' is the cause of the table scan. The planner has no available shortcut that an index would provide, so it has no choice to check all rows to see whether one contraint or the other holds.

Indexing invoice_tcgplayer_details.order_number will cut out out the sequential scan on invoice_tcgplayer_details, and this may be enough for the planner to bring the trgm index to bear on the other and combine the two through a bitmap-or hash join.

That said, the UNION approach will be pretty good, since one of the selects will be nothing more than

SELECT inventory_uuid
FROM invoice_tcgplayer_details
WHERE order_number = 'bcfong28'


Pull entire row per user based on MAX(Column) MySQL I am trying to pull whole record from database for the highest "Score" column per user.

TestTable structure

------------------
ID | Name | Score|
------------------
1  | john | 10
2  | mark | 20
3  | ted  | 15
4  | john | 5
5  | ted  | 50
6  | ted  | 70

So when I run

SELECT ID, Name, MAX(Score)
FROM TestTable
GROUP BY Name

I want the query to return entire row for highest score per user.

This is what I am expecting:

2  | mark | 20
1  | john | 10
6  | ted  | 70

But it does not necessarily return correct rows. It always return MAX(Score) but other values are not necessarily from the same row.

This is what I am getting:

1  | john | 20
2  | mark | 10
3  | ted  | 70

How can I pull this properly?
I am using MySQL.

I need it to perform good as this will be a big query pulling a lot of data from big tables.

Please help :)

You need to group by both ID and Name:

SELECT ID, Name, MAX(Score)
FROM TestTable
GROUP BY ID, Name


What's your data ETL methodology? I think this is more like an open question: How do you do the ETL for your company?

We are using MySQL as our database management system and we have a production schema. Every month, when we received new data files, we processed the data into that.

The problem is, if directly processing data into the production schema, if anything goes wrong, we have to rollback (if we made backup before). So, currently, I replicate that schema and do the ETL there, once everything is fine, I move those tables into the production one.

However, the size of the production schema is about 250GB (some table rows are more than 800mil), it is difficult to replicate that schema and do the ETL.

So, any brilliant idea? Appreciated!

Additional considerations:

We are MySQL 8.0 Enterprise Edition.
Database is hosted on AWS RHEL EC2.
When using the tables, we may need to refer to all historical data so I don't think partition by month will help...

If you are replacing all the data, then build new tables, test them, then swap.
If you have a Master-Master replication setup, then break the connection, do the ETL, test, then failover and reestablish the link.
If something else, please elaborate.

More details from me after more from you.


Is it any good for performance to have master and slave on the same server There are similar questions on this site, but they mostly cover server failure and data backup aspects.
I'm concerned only as far as query performance. If master and slave share the same resources, Master table is used only for writes and Slave only for reads, will this help db to handle growing number of queries more efficiently, given that queries are properly optimized?

It's very unlikely that running master and slave on the same machine would be beneficial, because the two instances would not actually be sharing resources in any positive or helpful way.

Running two instances with the same data means you have to divide up the available resources in some combination of implicit (cpu, disk, memory including OS cache) and explicit (buffer pool/key cache configuration) ways...

Significantly, the fact that one is a replica of the other means that database writes are contending for double resources at almost the same time: for each write done by the master, a comparable write has to be done by the slave, which means -- approximately -- twice the disk utilization, plus the fact that every binlog entry is written at least twice, first to the master binlog, then to the slave's relay log, then to the slave's binlog if enabled.

The fact that each instance now has less memory available means that less data can be cached in memory, meaning that you will further increase the need for disk access, as less cached data means more disk I/O, almost by definition.

There might be specific scenarios where splitting uo the workload like this could serve a useful purpose, but situations where such a setup would degrade performance seem to significantly outnumber any scenarios where you'd see an improvement... and any such cases would likely be very workload-specific.


Set up View Column to be last Value I have a View in my database set up to display data for parts that my company uses for projects.

One of the columns in the View shows what our recorded price for the part is.  However, as time goes on, and prices change, there will be many such entries for part prices.

Is there a way within a View setup, to set it so that the value retrieved for a column is that last value in that table?

Or is this a situation where I'd need to include the date of the price log as part of the view data, and then do a TOP 1 ... ORDER BY DESC type query against my view?

EDIT:

For example right now if I query my View for specifically the part with Part_Number = '0010.05.20.20' I will get the following Table result:

Part_Number    Supplier     Manufacturer  Price  Discount  Currency
0010.05.20.20  A2A Systems  Vario         55.97  1.000     CAD
0010.05.20.20  A2A Systems  Vario         50.25  1.000     USD

I know that the 50.25 price value is the most recent one, because it is the one I just inserted into the database for testing purposes.  However, there is no way to know just from doing a SELECT * FROM PartsView query which price is the most current one.

This is the code for creating my View, to clarify, I am wondering if there is a way to modify this so that the Price column only shows the most recent entry for any criteria it is looking up.  If that is not possible, is my only discourse to add a 'Log Date' Column for when the price was logged?

CREATE VIEW PartsView AS
SELECT
    p.part_number          AS [Part_Number],
    s.supplier_name        AS [Supplier],
    m.manufacturer_name    AS [Manufacturer],
    pl.cost_per_unit       AS [Price],
    pl.discount_percentage AS [Discount],
    cu.currency_type       AS [Currency]
FROM
    dbo.parts      AS p     INNER JOIN
    dbo.suppliers  AS s  ON p.supplier_id = s.supplier_id INNER JOIN
    dbo.price_logs AS pl ON p.part_id = pl.part_id INNER JOIN
    dbo.currencies AS cu ON pl.currency_id = cu.currency_id
GO

Here is an example of how you can pick the latest price for a part.

declare @Parts table (PartID int, PartName varchar(50))
declare @PartsLog table (PartID int, TransactionDate Datetime, Price decimal(11,2))

insert into @Parts(PartID, PartName) values
    (1,'Hammer'),
    (2,'Drill')
insert into @PartsLog(PartID,TransactionDate,Price) values
    (1,'2018-01-01',100.00),
    (1,'2018-07-01',200.00),
    (2,'2018-01-01',50.00),
    (2,'2018-03-01',75.00)

SELECT p.PartID
    ,p.PartName
    ,pl.price
FROM @Parts p
JOIN @PartsLog pl
    ON pl.PartID = p.PartID
        AND pl.TransactionDate = (
            SELECT max(TransactionDate)
            FROM @PartsLog
            WHERE PartID = pl.PartID
            )

| PartID | PartName | price  |
|--------|----------|--------|
| 1      | Hammer   | 200.00 |
| 2      | Drill    | 75.00  |


Why does mySQL automatically add display length to numeric values when adding zerofill property I am trying to understand better how the data types are processed by the MySQL system, so I created a table in the following way:
# Field, Type, Null, Key, Default, Extra
test1, tinyint(3) unsigned zerofill, YES, , ,
test2, smallint(5) unsigned zerofill, YES, , ,
test3, int(10) unsigned zerofill, YES, , ,
test4, bigint(20) unsigned zerofill, YES, , ,

I initially added only the columns without autofill:
create table test (
test1 tinyint,
test2 smallint,
test3 int,
test4 bigint
);

And the table looked like this:
# Field Type    Null    Key Default Extra
test1   tinyint  YES
test2   smallint    YES
test3   int YES
test4   bigint  YES

But then I added the autofill property via alter table and the final result was this:
# Field Type    Null    Key Default Extra
test1   tinyint(3) unsigned zerofill    YES
test2   smallint(5) unsigned zerofill   YES
test3   int(10) unsigned zerofill   YES
test4   bigint(20) unsigned zerofill    YES

My question is why does it define automatically the display length for the columns?

In the past, INT(5) was identical to INT.  The number was useful only when ZEROFILL was also specified.
In the future, ZEROFILL and the number will go away.  This incompatible change is predicted for some time in 8.0.xx.  You are probably seeing some step toward it.
Zerofill is extremely rarely used.  US Zipcodes stored in MEDIUMINT(5) UNSIGNED ZEROFILL is the only case I can think of off-hand.
When Zerofill goes away, there are workarounds for the rare cases where you really need it.
The number confuses a lot of people.  It takes time for newbies to understand that it is normally of no use.
Although DESCRIBE and information_schema.TABLES are tempting for displaying the schema for a table, please get in the habit of using SHOW CREATE TABLE; it rolls the useful information together in a canonical form.


Conditional one to many queries in mysql I have a simple patients table and lots of Lab tests table. Every patient can have many Lab tests. What i want to do is to only  query the tests which are already entered for that specific patient and avoid joining all 100000 tables(which will take lots of resources to execute).

Should i create a table and save which tests are conducted for the patient for later use?

PS: i have to mention that currently im using MYSQL and Doctrine 2(which is an ORM)and I'm out of options for using non-mysql solutions and i know that there are many Electronic medical records softwares using SQL but they are not opensource so we cant study them.

As Nick Chammas pointed out in his comment, your test tables should be combined if they have the same fields and segregated if they have very different fields.

It is difficult to imagine 100,000 different test tables.  If this is not complete hyperbole then you might need to consider other options.

One option would be a document-oriented database.  This would be efficient for storage of loosely structured data and easy retrieval of data for a particular patient.  It would be pretty useless for rapid retrieval of data for similar tests across multiple patients.

Another option would be Entity-Attribute-Value (EAV).  EAV is considered an anti-pattern by most people, but yours may be a situation where it could be useful.  EAV would be less efficient at retrieving the results of a particular test for a particular patient, but it would certainly be better at getting all results for a particular patient than joining thousands of different test tables.  It would also be better than other approaches for getting the same test result values across multiple patients.


A good dataset to experiment NoSQL databases I need to do some experiments in HBase and Cassandra and to do that I need an adequate dataset.

The dataset I'm looking for has to be large enough (i.e. more than 2GB) and the data in it has to be sufficiently unstructured to be representative of the kind of problems that relational technology can't cope. Maybe data derived from social networks, and so on.

Does anyone have that kind of dataset or knows where can I find such a dataset?

[EDIT - added several public dataset sites].

First off, there is no real evidence that NoSQL databases are "better" at handling large datasets than traditional (OldSQL) RDBMSs. Check out Ted Dziuba's article about how he can't wait for NoSQL to die. He makes the point that Walmart continue to use RDBMSs - and they're not a small company!
He says that NoSQL is, and should remain, niche and that in all likelihood, you don't need it. He also makes the reasonable point that Facebook, Google and Twitter are not normal companies with normal data processing needs.

Google Michael Stonebraker's writings on OldSQL, NoSQL and NewSQL (e.g. 1, 2, 3). He makes the point that NoSQL throws the baby out with the bathwater - i.e. NoSQL doesn't enforce ACID transactions and that this is an abomination for a database system. As you'll see from his bio, he's been involved in databases as an academic and in industry for 40 years.

He agrees with  the NoSQL school that OldSQL (think Oracle, MS SQL Server &c.) is "old technology" and needs to be "sent to the home for retired software", and that OldSQL (in this case MySQL) has trapped Facebook in a "fate worse than death".  His point about NewSQL is that for OLTP apps, you need a shared-nothing sharded architecture (check out his VoltDB) and that for OLAP you need dedicated columnar stores, i.e. Vertica (which he sold to HP).

If this doesn't convince you, check out Brian Aker's (former MySQL chief architect) humourous take on NoSQL here.

As for large datasets, I would urge you to Google in the area which particularly interests you. I know that metereological (my dad was one) datasets can be very large and also genomic datasets can also be huge (I studied genetics in uni). This site seems to be right up your alley - with many multi-GB and multi-TB datasets.

[EDIT] Other sites of interest are to be found (1, 2, 3, 4 & 5).

I would strongly urge you to benchmark both RDBMS and NoSQL solutions. As I mentioned, Dziuba says NoSQL is niche - it may suit your particular needs, I don't know. 2GB datasets are now officially small (even tiny - they fit easily on all memory sticks. Nowadays, you've got to be getting into the multiple terabyte region for a database to be getting large. Consider the Apollo moon landing's IT capacity. There was time when 2GB was huge - no so anymore!

Finally, I leave the last word to Ted Dziuba:

  "I'm not just singling out Cassandra - by replacing MySQL or Postgres with a different, new data store, you have traded a well-enumerated list of limitations and warts for a newer, poorly understood list of limitations and warts, and that is a huge business risk".


PostgreSQL ANALYZE execution time over 24h (still running) I upgraded a Postgres DB 9.3.2-->10.5 using pg_upgrade (in place). I did everything according to the documentation and the instructions given by pg_upgrade. Everything went fine but then I realized that the indexes were not being used in one of the tables (maybe others are affected too).

So I started an ANALYZE on that table yesterday which is still running (for over 22h)...!

The question: Is it normal for ANALYZE to have such a long execution time?

The table contains about 30M records. The structure is:

CREATE TABLE public.chs_contact_history_events (
    event_id bigint NOT NULL
           DEFAULT nextval('chs_contact_history_events_event_id_seq'::regclass),
    chs_id integer NOT NULL,
    event_timestamp bigint NOT NULL,
    party_id integer NOT NULL,
    event integer NOT NULL,
    cause integer NOT NULL,
    text text COLLATE pg_catalog."default",
    timestamp_offset integer,
    CONSTRAINT pk_contact_history_events PRIMARY KEY (event_id)
);

ALTER TABLE public.chs_contact_history_events OWNER to c_chs;

CREATE INDEX ix_chs_contact_history_events_chsid
    ON public.chs_contact_history_events USING hash (chs_id)
    TABLESPACE pg_default;

CREATE INDEX ix_chs_contact_history_events_id
    ON public.chs_contact_history_events USING btree (event_id)
    TABLESPACE pg_default;

CREATE INDEX ix_history_events_partyid
    ON public.chs_contact_history_events USING hash (party_id)
    TABLESPACE pg_default;

UPDATE:

I ran the query below in order to get the currently running processes and got a more than interesting results:

SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
AND state = 'active';

It seems that the maintenance tasks and the concurrent recreation of the index table are frozen!

So the next question: is it safe to cancel those processes? And what to do next? IMO stopping them all and restarting index creation will be necessary but I'm unsure.

ANNEX 1

Possibly related errors corrected in v9:

9.3.7 and 9.4.2
Fix possible failure during hash index bucket split, if other processes are modifying the index concurrently

9.3.18 and 9.4.13 and  9.5.8 and 9.6.4
Fix low-probability corruption of shared predicate-lock hash table in Windows builds

9.5.4
Fix building of large (bigger than shared_buffers) hash indexes
The code path used for large indexes contained a bug causing incorrect hash values to be inserted into the index, so that subsequent index searches always failed, except for tuples inserted into the index after the initial build.

Possibly related errors corrected in v10:

10.2
Fix failure to mark a hash index's metapage dirty after adding a new overflow page, potentially leading to index corruption

Prevent out-of-memory failures due to excessive growth of simple hash tables

And last but not least that makes me concerns (since an upgrade seems to be not realistic on the productive environment):

10.6
Avoid overrun of a hash index's metapage when BLCKSZ is smaller than default

Fix missed page checksum updates in hash indexes

ANNEX 2

Upgrade instruction in v10:

Hash indexes must be rebuilt after pg_upgrade-ing from any previous major PostgreSQL version

Major hash index improvements necessitated this requirement. pg_upgrade will create a script to assist with this.

Note that I ran that script of course at the time of upgrade.

After several hours of research and examining the current situation I think I managed to solve the issue. (Many thanks to fellow user ypercube for the inspiration and for Erwin Brandstetter who in parallel came to the same solution.)

So there were several layers of the problem.

1.) UPGRADE

Upgrading with pg_upgrade 9.3.2 --> 10.5 should be made in two steps. First within the same line (9.3.2 --> 9.3.25) and then to 10.x (10.5 in my case)

I made a direct upgrade and it seems that it was the root cause of the problem.

2.) HASH INDEXES

It seems that hash indexes suffered from some strange errors in postgres which have been corrected already but using indexes of the pre-correction versions leads to errors

3.) FROZEN TASKS

It does make sense to look for postgres processes which are running for non-realistic long time. (See query in the question.) In my case it turned out that the recreation of indexes stuck somehow and several other tasks have been blocked as well.

It is safe to cancel most of them with SELECT pg_cancel_backend(__pid__); where pid is the process ID found in the result set of the before-mentioned query. So I did it. I even stopped the autovacuum processes.

4.) MEMORY HANDLING

When after all of this I finally thought I was able to delete and create the new indexes I faced the next problem. After about one minute all maintenance queries exited with an error message:

ERROR: out of memory
DETAIL: Failed on request of size 22355968.
SQL state: 53200

It seems that the memory handling changed between 9.3 and 10. I had to reduce the amount of maintenance_mem in the config:

maintenance_work_mem = 64MB     # min 1MB

It was 512MB before and although the server has 32GB of RAM it was still not working with that.

5.) RECREATING INDEXES

After all it was possible to recreate indexes (drop old ones and create new ones). It would have been easier with a proper script but I had to do it manually. Don't forget that creating and dropping indexes locks the table so in productive environment (like mine) you should do that CONCURRENTLY.

Edit:

I also realized that using hash indexes in my specific case was not really meaningful so I decided to change them to btree at the recreation.

6.) ANALYZE

After recreating the indexes it is necessary to run an analyze on the affected tables (or the whole DB). After all the above actions it will run surprisingly quick even in a huge DB like mine.

The indexes are again being used and the performance is perfect again. So this is a happy end in my first StackExchange post. :-)


Exclude elements are already in file - Foreach loop powershell Please help me to correct the below code . I am trying to exclude the filename which already received email but -and $i.file -ne $SentUsers not excluding the already sent filenames . Please help me .

I am trying below things :

Write filename to a file
Get filename to a file
Exclude filename which already recieved an email by using -and $i.file -ne $SentUsers
Copy new current file name to file by overwriting existing file

Below is my code full :

$Q =

"
*** SQLCode Here ***

"
$ab    = Invoke-Sqlcmd -ServerInstance Serv1 -Database DB1 -Query $Q

## Write filename to a file

$l = $ab | select file | Out-File C:\temp\fname.txt

## Get filename to a file

$SentUsers = Get-Content -Path C:\temp\fname.txt

 foreach($i in $cr  ){
 ## Exclude filename which already recieved an email by using -and $i.file -ne $SentUsers

                    If ( $i.Uid -eq 'No' -and $i.UIntheTeam -eq 'No' -and $i.file -ne $SentUsers ){
                        $user = $i.User

Send-MailMessage -To @("abc") -Cc $user -From 'abc1' -SMTPServer abc.co.jp  -Subject "TestSub " -Body "

** Mail Message Body Here **

" }
                    Else {
                        'Exit'
                    }

                }

# Copy new current file name to file by overwriting existing file

$ab | select file | out-file -FilePath C:\temp\fname.txt

As presented here, your code has many problems.

First off, avoid non-descriptive variable names.  Your code is very difficult to read. Until you read the entire script, you have no idea what any variable is supposed to represent.  Every time you find a new variable, you have to hunt down where it comes from and then keep it all in your head.  It's difficult to follow the logic of your script because there's no contextual information.  If you were trying to remove sensitive information so you could post it on the web, you've removed too much.

Next:

$l = $ab | Select-Object file | Out-File C:\temp\fname.txt

This line makes no sense.  Out-File has no standard output to assign to $l, so $l is always going to be assigned the value of $null.  You also never appear to use $l again.  I don't know what you intend this line to do.

However, if I assume you only want this line to write the file column to a file and that you don't need $l at all, then as written this is going to output a header line.  Your file contents will be:

File
----
MyFile1
MyFile2

You probably don't want that because when you read it back the system will assume File and ---- are files.  The pattern you want is more like this:

$ab | Select-Object -ExpandProperty File | Set-Content C:\temp\fname.txt

$SentUsers = Get-Content -Path C:\temp\fname.txt

I tend to avoid pairing Out-File with Get-Content because they have different encoding defaults, while Get-Content and Set-Content are the same.

Next:

foreach ($i in $cr) {

The variable $cr has no value assigned to it.  If you're not assigning it elsewhere, this entire loop will never run.

Next:

If ( $i.Uid -eq 'No' -and $i.UIntheTeam -eq 'No' -and $i.file -ne $SentUsers ) {

As @ScottHodgin mentions, $SentUsers is likely going to be an array of values.  -ne treats both operands as a scalar.  You probably want one of the containment operators:

$Value -in $Array
$Value -notin $Array
$Array -contains $Value
$Array -notcontains $Value

So, I'd expect this to do what you want:

If ( $i.Uid -eq 'No' -and $i.UIntheTeam -eq 'No' -and $i.file -notin $SentUsers ) {

See Get-Help about_Comparison_Operators for more information on the above operators.

Next:

Else {
    'Exit'
}

Huh?  "Else, output the string 'Exit'."  What are you intending this to do?  Is it supposed to stop the script?

Next:

# Copy new current file name to file by overwriting existing file

$ab | select file | out-file -FilePath C:\temp\fname.txt

Why do you need to do this?  You did the same thing earlier in the script, and $ab hasn't modified since then.  I mean, the file path is the same and everything.  Is that an error?  Did you decide to use the same file path throughout your posted code without leaving in the fact that you're actually changing different files?


How to generates VALUES literal expression using a query? During development and testing VALUES literal expressions are useful because they enable you to store data definition in your SQL query.
WITH foobar(foo, bar) AS (
  VALUES
  (1::integer,'a'::text),
  (2,'b'),
  (3,'c'),
  (4,'d')
)
SELECT * FROM foobar;

However this can become tedious when trying implement really wide or big table.
And it's even more frustrating when this could have been generated from an existing table.

So I there a way to output rows in format easily copy/paste-able as a literal VALUES expression?
The closest I could come by is to output row as record(please note the meta because this SQL actually try to reverse engineer literal VALUES back from a given literal VALUES).
WITH foobar(foo, bar) AS (
  VALUES
  (1::integer,'a'::text),
  (2,'b'),
  (3,'c'),
  (4,'d')
)

SELECT foobar::record FROM foobar;

Here is the psql output:
 foobar
--------
 (1,a)
 (2,b)
 (3,c)
 (4,d)
(4 rows)

However this need extra editing to rightfully quote,type and escape content so should i look for a formatting output trick or an SQL trick?

EDIT: The current top answer is already very good but ideally I would like to know if there is generic way to generate a value expression that can adapt to whatever record type you can throw at (even if it is a composite row with a random numbers of columns).
Let say this could be wrapped in as function like:
row_to_values(IN myrowtype record, OUT myrowtype_as_literal_values text)

At this stage one might wonder "Why not use row_to_json() ?" and yes this could be a valid alternative but we are drifting away from the initial use case I had in mind (quickly generate SQL for testing/occasional purpose) although using json should perform well and is now widely available among PostgreSQL releases.
Even better would be an aggregate function that directly output a clean text definition that specify types on first VALUE and add columns name as alias (but at this stage it's almost a feature proposal and here is not the right place and I'm nobody to ask for such a thing).
Unfortunately I guess this is currently an out of reach goal because as far as my understanding goes it would require a low level access to dynamic metadata about query record type. It's clearly doable for an existing table by querying pg_catalog.pg_attribute but I don't see how to achieve that with dynamic queries.
So if someone come with a nice trick I haven't though off, that would be impressive, otherwise the less generic answer clearly deserve an acceptance.

Are you looking for this:
WITH foobar(foo, bar) AS (
  VALUES
  (1::integer,'a'::text),
  (2,'b'),
  (3,'c'),
  (4,'d')
)
SELECT format('(%s, %L),', foo, bar) FROM foobar;

  format
-----------
 (1, 'a'),
 (2, 'b'),
 (3, 'c'),
 (4, 'd'),
(4 rows)


How to allow NULL in foreign keys of a compound primary key I want to have NULLs in foreign keys of a compound primary key. This is an example of what I would expect to be valid data.

product_id
variant_id

123-123
ABC

123-123
NULL

456-456
ABC

I cannot figure out why the following SQL in postgres gives NOT NULL violation constraint me when inserting NULL as variant_id.
CREATE TABLE IF NOT EXISTS inventory.price (
  product_id             UUID NOT NULL, -- this has to be always to a valid product
  variant_id             UUID,          -- this could be NULL
  amount                 MONEY NOT NULL,
  created_at             TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  -- Constraints
  CONSTRAINT inventory_price_pkey PRIMARY KEY (product_id, variant_id),
  CONSTRAINT inventory_price_inventory_product_fkey FOREIGN KEY (product_id)
    REFERENCES inventory.product (id) MATCH FULL,
  CONSTRAINT inventory_price_inventory_variant_fkey FOREIGN KEY (variant_id)
    REFERENCES inventory.variant (id) MATCH SIMPLE,
  CONSTRAINT inventory_price_amount_gt_0 CHECK (amount > '0'::money)
);

And the inspection to information_schema confirms the non-nullable constraint.

column_name
column_default
is_nullable
data_type

product_id
NULL
NO
uuid

variant_id
NULL
NO
uuid

amount
NULL
NO
money

created_at
now()
NO
timestamp with time zone

I believe that the primary key constraint enforces the not null constraint. You can look at the following Fiddle
I'm not sure what it is that you want to achieve. Should the following be valid:
INSERT INTO price (product_id, variant_id, ...
VALUES ('123-123', null, ...)
     , ('123-123', null, ...)

? If not (i.e. only one allowed null varant per product), you can use a generated column and add the constraint there:
CREATE TABLE IF NOT EXISTS price3 (
  product_id             UUID NOT NULL, -- this has to be always to a valid product
  variant_id             UUID,          -- this could be NULL
  variant_ext_id         UUID NOT NULL GENERATED ALWAYS AS (COALESCE(variant_id, product_id)) STORED,
  amount                 MONEY NOT NULL,
  created_at             TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  -- Constraints
  CONSTRAINT inventory_price_pkey3 PRIMARY KEY (product_id, variant_ext_id),
  CONSTRAINT inventory_price_amount_gt_03 CHECK (amount > '0'::money)
);

You would, of course, need to apply that rule in any dependent tables. From a normalisation point of view, you may want to treat products and variant of products differently.
Other things that come to mind is to use a default value such as:
CREATE TABLE IF NOT EXISTS price (
  product_id             UUID NOT NULL, -- this has to be always to a valid product
  variant_id             UUID DEFAULT '00000000-0000-0000-0000-000000000000' NOT NULL,
  amount                 MONEY NOT NULL,
  created_at             TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  -- Constraints
  CONSTRAINT inventory_price_pkey PRIMARY KEY (product_id, variant_id),
  CONSTRAINT inventory_price_amount_gt_0 CHECK (amount > '0'::money)
);

Edit: Another option is to change the primary key to a unique constraint, see Fiddle:
CREATE TABLE IF NOT EXISTS product (
  product_id UUID NOT NULL PRIMARY KEY
);

insert into product values ('33a9fb48-5c0c-4bec-a1d9-382a73856e53');

CREATE TABLE IF NOT EXISTS price (
  product_id             UUID NOT NULL, -- this has to be always to a valid product
  variant_id             UUID,          -- this could be NULL
  amount                 MONEY NOT NULL,
  created_at             TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  -- Constraints
  CONSTRAINT inventory_price_pkey UNIQUE (product_id, variant_id),
  CONSTRAINT inventory_price_inventory_product_fkey FOREIGN KEY (product_id)
    REFERENCES product (product_id),
  CONSTRAINT inventory_price_amount_gt_0 CHECK (amount > '0'::money)
);

insert into price (product_id, amount)
values ('33a9fb48-5c0c-4bec-a1d9-382a73856e53','12');

insert into price (product_id, variant_id ,amount)
values ('33a9fb48-5c0c-4bec-a1d9-382a73856e53', '33a9fb48-5c0c-4bec-a1d9-382a73856e54', '19');

CREATE TABLE example (
  product_id             UUID NOT NULL,
  variant_id             UUID,
  foreign key (product_id) references product (product_id),
  foreign key (product_id, variant_id) references price (product_id, variant_id)
);

-- valid, f.k. evaluates to Null
insert into example (product_id)
values ('33a9fb48-5c0c-4bec-a1d9-382a73856e53');

-- valid, f.k. evaluates to True
insert into example (product_id, variant_id)
values ('33a9fb48-5c0c-4bec-a1d9-382a73856e53', '33a9fb48-5c0c-4bec-a1d9-382a73856e54');

-- invalid, f.k. evaluates to False
insert into example (product_id, variant_id)
values ('33a9fb48-5c0c-4bec-a1d9-382a73856e53', '33a9fb48-5c0c-4bec-a1d9-382a73856e55');


Different behavior between MySQL 5.5 and 5.6 I have this schema with data:

CREATE TABLE `ticket` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  `type` INT(11) NULL,
  PRIMARY KEY (`id`));

CREATE TABLE `task` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `ticket_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_task_1_idx` (`ticket_id`),
  CONSTRAINT `fk_task_1` FOREIGN KEY (`ticket_id`) REFERENCES `ticket` (`id`)
) ENGINE=InnoDB;

CREATE TABLE `planing` (
  `id` int(11) NOT NULL,
  `date` datetime DEFAULT NULL,
  `task_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_planing_1_idx` (`task_id`),
  CONSTRAINT `fk_planing_1` FOREIGN KEY (`task_id`) REFERENCES `task` (`id`)
) ENGINE=InnoDB ;

INSERT INTO `ticket` (`id`, `name`, `type`) VALUES (1, 'ticket_1', 1);
INSERT INTO `task` (`id`, `name`, `ticket_id`) VALUES (1, 'task_1', 1);
INSERT INTO `task` (`id`, `name`, `ticket_id`) VALUES (2, 'task_2', 1);
INSERT INTO `planing` (`id`, `date`, `task_id`) VALUES (1, '2016-06-01 05:00', 2);

INSERT INTO `ticket` (`id`, `name`, `type`) VALUES (2, 'ticket_2', 1);
INSERT INTO `task` (`id`, `name`, `ticket_id`) VALUES (3, 'task_3', 2);
INSERT INTO `task` (`id`, `name`, `ticket_id`) VALUES (4, 'task_4', 2);
INSERT INTO `planing` (`id`, `date`, `task_id`) VALUES (2, '2016-06-01 05:00', 3);
INSERT INTO `planing` (`id`, `date`, `task_id`) VALUES (3, '2016-07-01 05:00', 4);

INSERT INTO `ticket` (`id`, `name`, `type`) VALUES (3, 'ticket_3', 1);

When I perform a select:

SELECT ticket.id AS ticket_id, task.id AS task_id, planing.date as due_date
FROM ticket
  LEFT JOIN task ON task.ticket_id = ticket.id
  LEFT JOIN planing ON (task.id = planing.task_id AND planing.date IS NOT NULL)
WHERE ticket.type = 1
GROUP BY ticket.id
ORDER BY planing.date ASC
;

on MySQL 5.5 I get

ticket_id | task_id | due_date
1         |1        |null
3         |null     |null
2         |3        |2016-06-01 05:00:00

and on MySQL 5.6 I get

ticket_id | task_id | due_date
3         |null     |null
1         |2        |2016-06-01 05:00:00
2         |3        |2016-06-01 05:00:00

example link

How to get the same behavior on MySQL 5.5 as on MySQL 5.6 (app server uses the MySQL 5.5 version)?

You just need to stop relying on the non-standard behaviour provided by GROUP BY when the only_full_group_by SQL mode is not enabled.

Normally, when grouping, you cannot retrieve a column that is not specified in the GROUP BY clause unless you wrap it in an aggregate function. That is because that column may have many different values (remember, we are talking about grouped rows) and there is no way to specify which value of the many should be used in the output. There is a recent extension to that behaviour according to which columns that are functionally dependent on the GROUP BY columns may be selected without specifying them in the GROUP BY, but MySQL has so far went the simple road of offering you just two modes: either you cannot retrieve non-GROUP BY columns without aggregation at all or you can retrieve any such columns, regardless of functional dependence.

So, in this case, you are retrieving columns task_id and due_date that clearly can have various values per ticket_id. Which value is ultimately selected is out of your control, and that is the problem. The solution is to define for yourself exactly which row from the other tables you want to get when you are grouping by ticket.id and implement the logic the way it can be followed unambiguously.

For instance, if you want to go by the behaviour you are observing in MySQL 5.6, you could define it as

  the earliest non-NULL due_date and its corresponding task_id per ticket_id.

Based on that, you could define the logic as follows:

SELECT
  ticket.id AS ticket_id,
  (
    SELECT
      MIN(p2.task_id)
    FROM
      task AS t2
      INNER JOIN planing AS p2 ON t2.id = p2.task_id
    WHERE
      p2.date = MIN(planing.date)
      AND t2.ticket_id = ticket.id
  ) AS task_id,
  MIN(planing.date) AS due_date
FROM
  ticket
  LEFT JOIN task ON task.ticket_id = ticket.id
  LEFT JOIN planing ON task.id = planing.task_id
WHERE
  ticket.type = 1
GROUP BY
  ticket.id
ORDER BY
  MIN(planing.date) ASC
;

This is certainly much more coding than your original query, but the extra bit of code is not redundant, because this way you are making your results predictable.

The only simpler alternative that might emulate the MySQL 5.6 behaviour could be to rewrite the query like this:

SELECT
  ticket.id AS ticket_id,
  task_id AS task_id,
  planing.date as due_date
FROM
  ticket
  LEFT JOIN
    task
    INNER JOIN planing ON task.id = planing.task_id
  ON task.ticket_id = ticket.id
WHERE
  ticket.type = 1
GROUP BY
  ticket.id
ORDER BY
  MIN(planing.date) ASC
;

In my tests, the above query returned the same results for 5.5 as the original did for 5.6 for your example. Although the two queries should logically give equivalent results, they are not exactly equivalent: the rewritten query explicitly changes the logical order of joins. Perhaps that prompted MySQL to choose a different path through the data.

But whatever the reason for the different output, please keep in mind that there is no guarantee the behaviour will match for every scenario. I will repeat, you need to stop relying on the behaviour that is not documented, or, actually, documented to be undefined:

  The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.


Can SQL Server 2008 / 2012 have execute permission in a function for a user, but not for a table used in the function? I have a function that returns a table. This function SELECTS DB1.dbo.table1

user1 has SELECT permission in this function only, but when selecting from the function error comes up stating that the security context does not allow access to DB1

Can a user have permissions to read only functions but not tables?

In SQL Server you can define the execution context.  This way the user only has rights to execute the function, but when it is executed, it runs in the context specified.

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

Functions (except inline table-valued functions), Stored Procedures, and DML Triggers
{ EXEC | EXECUTE } AS { CALLER | SELF | OWNER | 'user_name' }

The following example would execute the procedure as the owner of the procedure, generally dbo, rather than the user calling it.

CREATE PROCEDURE dbo.usp_Demo
WITH EXECUTE AS OWNER
AS
SELECT user_name();
GO


SQL query with Like operator with Leading Wildcards I have a query as follows:

select top (30000) * from table1 where licenseplate like '%as43bv%'

I have an index on licenseplate but the query will not hit the index due to leading wildcard.

Since the column queries license plate numbers full text search wont come to any benefit we are searching on some letter or number rather than words

The above query fetches millions of rows and brings the CPU usage around 100%.

Is there any efficient way to rewrite the query and make use of the indexes.

What first comes to mind, is that if you are searching for some symbols inside the licenseplate that will always start, for example at 4th symbol (example data 123as43bvxxx ), and it will be always be fixed, you can consider adding computed persisted column to your table

alter table [table1]
add LicenseplateComputed as substring(licenseplate,4,50) persisted

And then create nonclustered index on your newly added computed column "LicenseplateComputed"

After this you will be able to query your table

select top (30000) * from table1 where LicenseplateComputed like 'as43bv%'

and it will perform efficient Index Seek


Impact of changing server machines names with SQL Server 2016 Standard in mirror state It's an enterprise network domain. Two existing Windows server 2016 machines, one each for SQL Server 2016 Standard edition and other is also SQL Server 2016 Standard edition in a passive mirror configuration.

For some reason we need to change both server machine names from say A to B and A1 to B1. Will this have any impact on SQL Server and its passive mirror access? What precautions I need to take? I can understand the connection string with change but I am looking for impact of this change on mirroring configuration particularly

Renaming Windows computer for SQL Server is supported an operation and described in documentation. For mirroring, it needs to be turned off and re-established after renaming.  Remember that renaming Windows requires a reboot, so be prepared for service outage.

  When you rename a computer that is configured to use database
  mirroring, you must turn off database mirroring before the renaming
  operation. Then, re-establish database mirroring with the new computer
  name. Metadata for database mirroring will not be updated
  automatically to reflect the new computer name. Use the following
  steps to update system metadata.

For what it's worth, could you use SQL client aliases instead of renaming the computers?


MySQL ERROR 1137 (HY000) at line 9: Can't reopen table: 'temp_table' I'm receiving an error ERROR 1137 (HY000) at line 9: Can't reopen table: 'temp_table' when executing a query similar to the following:

USE database_name;

CREATE TEMPORARY TABLE temp_table (status varchar(20));
INSERT INTO temp_table (status)
    SELECT status
    FROM client_contractor_status
    WHERE type = 'process';

SELECT table1.col1,
       table1.status,
       (SELECT
            COUNT(*)
        FROM
            table2
        RIGHT OUTER JOIN
            temp_table
            ON table2.status = temp_table.status
        WHERE table2.col1 = table1.col1
        ) AS counter
FROM
    table1

RIGHT OUTER JOIN
    temp_table
    ON table1.status = temp_table.status

I'm (just about) aware of the limitation that says you can't access a temporary table by two different aliases, however I haven't aliased it at all.

Is it being aliased automagically because it's in a sub-query? And if so, how can this be fixed?

Thanks.

This is a known and well documented issue:

  You cannot refer to a TEMPORARY table more than once in the same query. For example, the following does not work:

mysql> SELECT * FROM temp_table, temp_table AS t2;
ERROR 1137: Can't reopen table: 'temp_table'

  This error also occurs if you refer to a temporary table multiple times in a stored function under different aliases, even if the references occur in different statements within the function.

One workaround that comes to mind would be to use a "normal" table as a temporary storage. You would probably need to use a session identifier to make the approach work in a multi-user environment.


Does SQL Server query timeout settings change require a server restart? I'm trying to execute a query that times out after 10 minutes. From experience I know that the query takes about 18 minutes to execute. It's not ideal, and this is being worked on, however for now I just need it to execute.

Having changed the Remote query timeout parameter to 0, however, the query is STILL timing out after 10 minutes. I get the message:

The statement has been terminated.
Msg -2, Level 11, State 0, Line 16
Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

This is using SSMS on a remote computer. Why is the timeout setting not taking effect? Do I need to restart the server? (This isn't possible right now)

According MS-DOCS you don't need to restart SQL Server.

Configure the remote query timeout Server Configuration Option

  Follow Up: After you configure the remote query timeout option

  The setting takes effect immediately without restarting the server.


Last time of trigger execution SQL SERVER 2014 - Is there a way to know last time of trigger execution ?


Why does this query not use the index that exists on SURNAME? There is an index on SURNAME in the PATIENTS table, we run the following query but the index doesn't get used.

DECLARE @SURNAME VARCHAR(30) = 'test'

SELECT SURNAME
FROM PATIENTS AS PAT
WHERE (PAT.SURNAME LIKE @SURNAME + '%' OR @SURNAME IS NULL)

It seems to be because we check if the variable is NULL as if I remove that then I get a constant scan, compute scalar, index seek and nested loop.

We need to check if the parameter is NULL as we then ignore that in the where clause.

Is there a way to do this and get SQL to use the index?

Why does this query not use the index that exists on SURNAME?

Because it has to compile a plan that will work correctly for both the cases where @SURNAME is NULL and when it is not.

In principle it would not be impossible for SQL Server to create such a plan that works in both cases and still uses an index. The dynamic seek mechanism already copes with the case that @SURNAME contains a leading wildcard and so the whole not null part of the index will need to be seeked - but your requirement is not something currently implemented.

The above would only be useful in the event that the index covers the query (as your example does). If the index is non covering then it would not be desirable to do a range seek on the whole index and then require lookups for the whole table.

  Is there a way to get SQL Server to use the index?

If PAT.SURNAME is not nullable you could do

DECLARE @SURNAME VARCHAR(30) = 'test'

SELECT SURNAME
FROM   PATIENTS AS PAT
WHERE  ( PAT.SURNAME LIKE ISNULL(@SURNAME, '') + '%' )

Alternatively you could request a new plan compilation every time, taking into account that execution's parameterised values, with

DECLARE @SURNAME VARCHAR(30) = 'test'

SELECT SURNAME
FROM   PATIENTS AS PAT
WHERE  ( PAT.SURNAME LIKE @SURNAME + '%'
          OR @SURNAME IS NULL )
OPTION (RECOMPILE)

(as long as you are on a reasonably recent version that supports the parameter embedding behaviour.)

Or you could do

DECLARE @SURNAME VARCHAR(30) = 'test'

SELECT SURNAME
FROM   PATIENTS AS PAT
WHERE  ( PAT.SURNAME LIKE @SURNAME + '%'
         AND @SURNAME IS NOT NULL )
UNION ALL
SELECT SURNAME
FROM   PATIENTS AS PAT
WHERE  ( @SURNAME IS NULL )

The plan above will show two table references but hopefully both underneath a filter with a start up predicate so only the relevant branch actually gets executed.

A final option would be to just have two entirely separate queries and choose the correct one with procedural logic.

I suggest reviewing Dynamic Search Conditions in T‑SQL as an informative article on the topic.


MYSQL Server shows it is master server in replication. How to turn off this? I'm using Ubuntu 20.04 version. I have installed MySQL server using apt-get install mysql-server and used mysql_secure_installation for security. Then I have installed phpMyAdmin using apt-get install phpmyadmin.
Opening phpMyAdmin's status tab shows This MySQL server works as master in replication process

Can anyone help me to stop this replication process?
I found nothing about this replication in my.cnf configuration file.
my.cnf file
The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

I added below segement at the end of the my.cnf file.
It solved the problem.
[mysqld]
skip-log-bin

Currently my my.cnf file looks like:
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

[mysqld]
skip-log-bin


Does v$sql_monitor show all queries? I cannot see all queries with v$sql_monitor or gv$sql_monitor.  I can see some queries, none of which have /*+ MONITOR */.  I have tested a query with MONITOR and it still did not show.  I thought it might just be a big explain problem, so I adjusted my session with:

ALTER SESSION SET "_SQLMON_MAX_PLAN"=4020;
ALTER SESSION SET "_SQLMON_MAX_PLANLINES"=4000;

select first_refresh_time, last_refresh_time,
status, sql_text
from v$sql_monitor
order by last_refresh_time desc;

, but it didn't help.  What I am trying to do is see query submitted via an application.  The query works, so I know it is hitting the database.  I simply cannot see the query in Oracle.

What am I doing wrong?  I am using SQL Developer if that matters.

SQL Monitoring will be triggered if one of the following conditions is present:

query is taking more than 5 seconds of CPU time
or
query is taking more than 5 seconds waiting on I/O
or
query is running in parallel
or
/*+ MONITOR */ hint was present in the query

In other words, if your query is a very fast one, doesn't run in parallel and doesn't have MONITOR hint, it won't be monitored.


Use single table with foreign key or two twin tables for text entries? I have a table answers that has several columns including the content iself such as

ID | q_id | list_id | user_module_id | content | updated_timestamp

The content column is a varchar since we want to be able to search through the answer content quickly.

However, we also want to be able to store full text as answers. There are two approaches:

A.  Using two "twin" table (with the same columns names) : a table with the column content of type varchar and a table with the column content of type text.

B. Using a single answer table and storing in the content column a hash key such as q_id7user_module_id362 and use another table that contains only the text entry:

hashkey | content

This approach seems cleaner, however it means doing two UPDATEs AND INSERTs for each entry modification and a JOIN on the two table to pull out the information.

In general is this bad practice to have "twin" tables in a database?

Thanks!

Personally I would avoid the twin table approach most, but not all, the time.  It leads to duplication of information and if you find that a wording of a question is bad in the db, it takes a bit to clean it up.  The cleaner solution would be to have the answer table just join user and question and then you only have one update/insert required.

This being said there can be some valid reasons for the twin table approach.  The major one is that if the questions are relatively free-form, frequently changing, etc. and you do not need to track answers across a given form of the question, then a twin table approach forms essentially an "answer log."  It isn't great for analytics if you are just looking at questions and answers, but it might be in some other fields.  I could imagine linguistic research doing well with a twin table approach.   For example it makes it easier to look into questions relating to effects of wording on response (rather than substantive answers to substantive questions).

So in general, I would recommend against a twin table approach unless you know you need it.  A simple answer table joining both the question table and the user table is sufficient for the most common use cases and it is far more useful in most cases.


How to create an index on an integer json property in postgres I can't figure out for the life of me how to create an index on a property of my json column which is an integer.

I tried it this way (and also dozens of others)

CREATE INDEX user_reputation_idx ON users(("user"->>'reputation')::int)

It works just fine in a query (e.g. ORDER BY ("user"->>'reputation')::int)

What am I missing?

UPDATE

I'm getting a simple syntax error, however, I really have no clue why.

ERROR:  syntax error at or near "::"
LINE 1: ... user_reputation_idx ON users (("user"->>'reputation')::int)

The table definition is quite simple. It's just one column user of typejson.

So, looks like this:

CREATE TABLE users
(
  "user" json
)

Try this instead:

CREATE INDEX user_reputation_idx ON users(cast("user"->>'reputation' AS int));

The Postgres syntax shortcut :: for casts is not allowed without additional parentheses in an index definition (see @bma's comment). It works with the standard SQL function, though: cast(expression AS type) This is not related to the json type per se.

Either way, you can still use the syntax shortcut expression::type in expressions that utilize the index.


Active/current (1-5%) and historical records in same table: index or partitioning? I am trying to discover the correct architecture for the following situation. An application stores 'source data' in multiple tables. Each of these 'source data' tables contains actual, current, active records, as well as old, historical records. There will be a maximum of about a 100 historical 'sets' of data (older data will be cleaned up). Data is loaded in the 'source data tables' once per day or less. The data is otherwise never updated (no OLTP), only read.

95% of the time, queries are executed only on the active records. These queries must be executed almost as fast as if they were the only records in the table. It's ok if queries on the old/historical records are a bit slower (say, a cost increased with a factor 5-10 max).

I currently implemented this as follows:

All 'source data' tables contain a column SRC_CURRENT with value 0 or 1. This column contains a bitmap index (but I've tried with a regular index too).
All 'source data' tables contain a column SRC_DATE with the (truncated) date of the set of data. This column contains a regular index.
All queries on all of these 'source data' tables always include either SRC_CURRENT = 1, or SRC_CURRENT = 0 AND SRC_DATE = <date>
The SRC_CURRENT flag is set correctly each time a new set of data is inserted.

But the performance isn't what I expected. What I'm looking for is a way to let the CBO know that the SRC_CURRENT column only contains a value of 1 in about 1-5% of the rows, and that in that case he can limit the query to only this small subset. If a value of 0 is supplied (95-99% of rows), then he can, of course, just ignore this index and rely on the SRC_DATE index instead to select only the appropriate subset.

Can this be solved using only indexes, do I need partitioning, or is what I'm trying to do simply impossible and should I for instance resort to 2 separate source tables of each type (a table containing the active rows, and a table and a _HIS table) and duplicate all queries, with the application selecting the correct query ?

I'm using Oracle 9i, but if there's a solution to this problem available only in later versions, feel free to mention it too, for future reference.

EDIT

I did a test with partitioning one of the tables on the SRC_CURRENT column. This does seem to fix it, both when targeting SRC_CURRENT = 1 as well as when targeting SRC_CURRENT = 0 AND SRC_DATE = <date>. Is this the proper approach ?

Partitioning should work better than index if the number of rows is large. If the query needs to scan only one partition, it is more efficient than finding current rows through index.

If SRC_CURRENT is updated to 0 when newer rows are loaded, you need to enable row movement for the table. This will allow rows to move from current partition to historical partition.

Alternatively, if current rows are always the most recent rows, you could range-partition the table by date. This would allow you to clear expired rows very fast by running drop partition for the oldest partition. This will also benefit queries against historical data that access a range of dates but not the whole history.


Direct vs Linked Server Connection in Query Execution Time After investigating our ETL Process for months i fell into something strange.
We have one machine (A) which we are using in order to pull data from other machines (B).

When i make a SELECT Statement using Direct Connection from machine (A) to machine (B), the execution time is 09-10 secs
When i make the same SELECT Statement using Linked Server from machine (A) to the same machine (B), the execution time is 25-35 secs.

So why there is this difference?

The same data are transferred in each case.

In the left window, when you connect directly to FORBISPRD08:

SQL Server Management Studio sends a request to
FORNSQPRD02\NAV01, who has the data, and sends it back to
SQL Server Management Studio

In the right window, when you use a linked server

SQL Server Management Studio sends a request to
FORBISPRD08\BIS01, who sends a request to
FORNSQPRD02\NAV01, who has the data, and sends it back to
FORBISPRD08\BIS01, who forwards it on to
SQL Server Management Studio

See the difference? Same data might be sent, but there's more steps involved. When you need data, connect to the server that has the data.

Linked servers are like going to a bar, seeing someone you like, and then starting a chat with their friend instead. You might make progress - but it's not gonna be as fast.


MySQL query to get sum of count distinct column value I have the following mysql query.

SELECT
COUNT(DISTINCT l.userid) AS 'Logins',
DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%M') AS 'Month'
FROM databasename.tablename l
WHERE l.action = 'loggedin' AND YEAR(FROM_UNIXTIME(l.timecreated)) = '2018'
GROUP BY MONTH(FROM_UNIXTIME(l.timecreated))

It will produce the output of:

Logins       Month
---------      -------
33             January
16             February

How does the mysql query looks like to get the sum of column Logins like 49 on this example?

Thanks!

I would start with using the same expression in both select and group by:

SELECT COUNT(DISTINCT l.userid) AS Logins
     , DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%M') AS Month
FROM databasename.tablename l
WHERE l.action = 'loggedin'
  AND YEAR(FROM_UNIXTIME(l.timecreated)) = '2018'
GROUP BY DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%M');

If you want to count the total number of distinct logins you can add ROLLUP as ypercubeᵀᴹ suggests. You can use COALESCE to get a label for the total:

SELECT COUNT(DISTINCT l.userid) AS Logins
     , COALESCE(DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%M'), 'TOTAL') AS Month
FROM databasename.tablename l
WHERE l.action = 'loggedin'
  AND YEAR(FROM_UNIXTIME(l.timecreated)) = '2018'
GROUP BY DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%M') WITH ROLLUP;

In general, the total is less than the sum of the parts. It will be the same only if the logins from Jan is disjoint with the logins from Feb.

If you instead want to sum the number of distinct logins you can do it like:

SELECT SUM(logins), COALESCE(Month, 'Summary') as Month
FROM (
    SELECT COUNT(DISTINCT l.userid) AS Logins
         , DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%M') AS Month
    FROM databasename.tablename l
    WHERE l.action = 'loggedin'
      AND YEAR(FROM_UNIXTIME(l.timecreated)) = '2018'
    GROUP BY DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%M')
) AS T
GROUP BY Month WITH ROLLUP;

This will result in the sum of the number of distinct logins for each month, which will be 49 in your case.


Import Wikipedia .sql dump very slow I'm having troubles to import one of the SQl dumps of Wikipedia into a local database. I've downloaded the categorylinks.sql from https://dumps.wikimedia.org/enwiki/latest/ which is around 17GB when unpacked and I tried to load it into a local MySql database via the command line with mysql -uroot > path/to/file
I monitor the progress my using pipeviewer in front of the command and it begins with ok speed (1-2MB/s) but it gets slower and slower over time, currently sitting at 18KiB/s after a weekend of running and it gives me an ETA of 4 days and that ETA is rather rising then going down.

The first few INSERT statements run at this speed:

Query OK, 7352 rows affected, 64 warnings (0.48 sec)
Records: 7352  Duplicates: 0  Warnings: 64

Query OK, 7145 rows affected, 64 warnings (0.71 sec)
Records: 7145  Duplicates: 0  Warnings: 64

Query OK, 7139 rows affected, 64 warnings (0.66 sec)
Records: 7139  Duplicates: 0  Warnings: 64`

And currently, after 3 days it slows down to:

Query OK, 7166 rows affected, 64 warnings (5 min 25.89 sec)
Records: 7166  Duplicates: 0  Warnings: 64

Query OK, 7013 rows affected, 64 warnings (5 min 8.13 sec)
Records: 7013  Duplicates: 0  Warnings: 64

The warnings are all just "Invalid utf8mb4 character string", so those should not be the problem, right? And the duplicate checks are turned of by setting unique_checks to 0.

I've already tried to tweak many options, most importantly setting autocommit, unique_checks, and foreign_key_checks to 0. Also have set innodb_flush_log_at_trx_commit = 2 as well as playing around with the values of innodb_buffer_pool_size, innodb_log_buffer_size, innodb_log_file_size, innodb_write_io_threads.

Neither RAM (16GB), nor I/O write speed are a problem as the process does not use nearly the available resources according to htop and iotop.
I've read of other people importing >60GB of data in less than 4 hours, so it should be possible to speed it up somehow?

Do you have any recommendations on what other options to change?

Would it be an option to split the SQL file into many smaller files and import them separately? Can you do that parallel?

Thank you very much in advance!

Just in case someone else comes looking for the same type of information. I decided that I'd like to import only a subset of wikipedia pages (the most popular subset) and built a couple of tools that build the list of most popular wikipedia pages (from the monthly logs) and filter the sqldump data files before import.


select max value from 2 tables (mysql) i have two identical tables:

+-------------+------------+--------+--------+
| domain      | date       | score  | source |
+-------------+------------+--------+--------+

The table contains a domain name, (us)date, a score (float) and a source (like source A or source B). I need always the information from yesterday for an report.

But the same domain can be in Table A and Table B with a differed score. If that happens, i need only the higher score. If the score is identical, it doesn't matter which source is used.

A short example:

Table A:

+-------------+------------+--------+----------+
| example.org | 2018-12-12 | 0.98   | source A |
+-------------+------------+--------+----------+
+-------------+------------+--------+----------+
| mydomain.org| 2018-12-12 | 5.09   | source A |
+-------------+------------+--------+----------+

Table B:

+-------------+------------+--------+----------+
| example.org | 2018-12-12 | 2.11   | source B |
+-------------+------------+--------+----------+
+-------------+------------+--------+----------+
| company.com | 2018-12-12 | 9.77   | source B |
+-------------+------------+--------+----------+

In this example, i need the domain example.org from Table B:
example.org, 2.11
mydomain.com, 5.09
company.com, 9,77

I have tried it with union and joins but i have not received a correct result.

Does anyone have an idea? Thank you for your efforts.

You don't need a source or a date in resultset at all. If so, you may use the simplest

SELECT domain, MAX(score) score
FROM (SELECT domain, score FROM tableA /* WHERE date = @date */
      UNION ALL
      SELECT domain, score FROM tableB /* WHERE date = @date */ ) dummy
GROUP BY domain

or (maybe more optimal)

SELECT domain, MAX(score) score
FROM (     SELECT domain, MAX(score) score
           FROM tableA
           /* WHERE date = @date */
           GROUP BY domain
      UNION ALL
           SELECT domain, MAX(score)
           FROM tableB
           /* WHERE date = @date */
           GROUP BY domain
     ) dummy
GROUP BY domain


Why not rebuild indexes with page count <1000? I use Ola Hallengrens script for Index maintenance. Before I did that, I used the following query to see which indexes are fragmented most:

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
ORDER BY indexstats.avg_fragmentation_in_percent desc

In my case, the avg_fragmentation was over 70% for 15 indexes and over 30% for 28 indexes.

So, I rebuild every index using Ola Hallengren's solution. When I ran the query again, this was the result:

Fragmentation over 70% for 12 indexes, over 30% for 15 indexes.

I figured, the reason was because of the page_count, which was lower than 1000 for each of the indexes that were still very fragmented. For example, one of the indexes with a
page_count
 of 967 has a fragmentation percentage of 98,98%! To me, it seems worth rebuilding that index! I did, and afterwards, the fragmentation was 0%. Also, a index with a page_count of 132 went from 95% to 0%

So, my question is, what reasons would there be to NOT rebuild those indexes? One reason might be that rebuilding costs time and resources, but because the indexes are small, doesn't this mean it costs relatively few resources and it would still be benfecial to rebuild it anyway?

There are multiple related question on this site, but all of them answer the question why a index would not defragment, or if indexes are still useful if they are small and you don't defragment them, whereas here the  statement DOES decrease fragmentation, with the question being, why not do it anyway?

The guidance concerning the minimum number of pages is somewhat arbitrary. The biggest benefits of reducing fragmentation are:

It can improve read-ahead performance for large range scans; and
It may improve the page density (number of rows per page)

Both of these factors are less important for small indexes, by definition.

The counter-argument to rebuilding small indexes is essentially:

  "Why bother? Don't you have more important things to worry about?".

That said, rebuilding/reorganizing is not free. It may be worth avoiding the extra effort and log generation in some cases (for example, if the log is shipped/copied across a WAN for any number of possible reasons - mirroring, availability groups, replication...). Also, unless (or even if, in some cases) you are rebuilding online, the rebuild may impact other concurrent processes through locking. Finally, for small indexes, rebuilding may not even reduce the fragmentation anyway, due to allocations from mixed extents (unless you are running with trace flag 1118 enabled).

If you still feel happier rebuilding these small indexes, and don't mind the consequences, then by all means change the value of the @PageCountLevel parameter passed to Ola's procedure.

See the PASS TV recording of Paul Randal's presentation on Index Fragmentation for all the details.

You might also like to watch Brent Ozar talk about Why Index Fragmentation doesn't matter in SQL Server.


How to set the default timezone in PostgreSQL on Amazon RDS? The default timezone is currently set to UTC. How to replace that default value? I am using PostgreSQL running on top of Amazon RDS

ALTER DATABASE <db_name>
  SET TimeZone = 'UTC';

But you shouldn't do that. Using UTC for database is safest thing possible.


How to control reindexing that results in high log send queue During reindexing using Ola's IndexOptimize stored procedure, the log send queues for remote asynchronous replicas can grow quite large if there are a lot of large index defrags, requiring gigantic transaction logs.  There is an @Delay parameter in the script that can be used to insert a delay between each index operation, but it doesn't provide the ability to delay only when the log send queues are high.

A more intelligent throttling mechanism is required to control the log send queue sizes during heavy reindexing.

And yes, I know that reindexing isn't really necessary with our enterprise SSDs, but we do it to appease the third-party support organizations that will blame fragmentation for any performance issue.

Insert the following code into the IndexOptimize procedure to implement a wait that will allow the log send queues to flush before continuing.  As written it will continue once there is less than 1 GB in the log send queues.

/* Pause until the log send queue is less than 1 GB */
WHILE ( (SELECT Sum(log_send_queue_size)
         FROM   sys.dm_hadr_database_replica_states
         WHERE  is_local = 0) > 1000000 )
  BEGIN
      WAITFOR delay '00:01:00'
  END

This code needs to be placed immediately after the IF @Delay > 0 block in the procedure.  With the current version of IndexOptimize, it would look like this:

IF @Delay > 0
BEGIN
    SET @CurrentDelay = DATEADD(ss,@Delay,'1900-01-01')
    WAITFOR DELAY @CurrentDelay
END

/* Pause until the log send queue is less than 1 GB */
WHILE ( (SELECT Sum(log_send_queue_size)
         FROM   sys.dm_hadr_database_replica_states
         WHERE  is_local = 0) > 1000000 )
  BEGIN
      WAITFOR delay '00:01:00'
  END


Calculating duration between two events in a MySQL database (timeseries) I have a (timeseries like) table in MySql which stores some on/off events. These events are reported at random intervals and represent the current state of an IoT switch:

I need help building the SQL query to calculate the duration (in munites) in which the switch was on.

There are a few obstacles:

The on/off events are reported a few times during a period i.e. there are duplicates
MySQL is version 5.5 and cannot be upgraded (i.e. lacking windowing functions). This version does not support nesting of sub queries in views.

UPDATE My goal is to calculate the total time the switch was ON for a given period. For example: between 2018-07-01 00:00:00 and 2018-07-10 23:59:59 the switch was ON for 56 minutes. My idea was to have the "ON" duration intervals in a table like this:

date-time           | duration-in-munites
2018-07-10 13:47:27 | 47
2018-07-11 00:01:13 | 12
...

Where date-time is when the first ON have occurred

From there I could sum up the durations for a given time span.
This may not be the right way to do it so I'm open to suggestions how to do it.

I'm interested only on OFF to ON transition time and duration
The initial values are unknown. The switch may have reported a few "off" events before reporting "on" event
Another issue is that the table contains data from other sources, so the IDs are no sequential.
The MySql server runs on Raspberry PI 3. As far as I know there is no ARM version of MySQL 8.

Here are Create/Insert statements for sample data

Approach 1 (works in any version)

Add a sequence number to the table (or a copy of the columns of interest).
Do a "self join" ON a.seq = b.seq-1 to match up adjacent rows.
Filter down to the status changes: WHERE a.Value != b.Value

The table will contain, for example:

  3  13:44:13  off   4  13:47:27   on
 12  14:27:27   on  13  14:34:48  off

Assuming you want the timespan between pairs of rows in this table, again add a sequence number, and do the self join to extract and subtract the next to last column.

Approach 2 (plug for upgrading)

If you have MariaDB 10.2 or MySQL 8.0, then there are "Windowing functions" that make this task immensely simpler.  See LAG(); it obviates the need for the self joins and the sequencing.

In the first pass, look for LAG(Value) != Value while using ORDER BY DateTime.  In the second pass, diff the LAG of the time with the current time.

Approach 3 (possibly the best for 5.5 without windowing)

Since you are stuck in the nearly-antique 5.5, Consider using a Stored Procedure, not a VIEW.  It will involve two extra tables.  They cannot be CREATE TEMPORARY TABLE since you can't use a temp table twice for the self-join.

So, a better approach (without windowing) would be to use @variables to simulate LAG().

Approach 4 (somewhere between 1 and 3)

This involves a single pass of looking at consecutive rows.  If the first of a consecutive (based on datetime) pair of rows says ON, then calculate the "on time".  Then SUM the "on times".

Details (Maybe I will give you details, but first...)

Provide sample data (not images) ready to run (CREATEs and INSERTs).  Also provide expected output.  (I understand ON to OFF, but what about OFF to ON; and do you want the start time of the range; etc.)

A hiccup

Since you want the "on time" to stop at the top of the hour, extra code is needed to stop the summation, etc.  One artificial way to do this is to add 2 rows, one second apart at the top of each hour.  The first is OFF, the second (1 second later) is ON.  This will create a 1-second error in the results, but has no impact on the Approaches except for simplifying the "top of hour" requirement.


Avoid Sql Conflicts When Inserting data At Same Time Let suppose I have a query that runs multiple times because it is in a while loop which inserts data into a table.

INSERT INTO <table>( psuiKey, group_id) VALUES ('54ds8a784daEd','1');

The problem is when two users from different computers add data at same time. So what is happening is user 1 adds data to the table to looks like this:

psuiKey       group_id
54ds8a784daEd  1
54ds8a785daEd  1

and at the same time user 2 adds data where group_id is 2, but the data looks like this:

psuiKey       group_id
54ds8a784daEd  1
54ds8a785daEd  1
54ds8a786daEd  1
54ds8a787daEd  1

instead of:

psuiKey       group_id
54ds8a784daEd  1
54ds8a785daEd  1
54ds8a786daEd  2
54ds8a787daEd  2

So in short if two users add the same data at same time so group_id is not changing when I find max group_id using:

SELECT MAX(group_id) + 1

So SQL can't able to update it when two users add data at the same time.

Note I cant use group_id as primary key and I also use datetime.

So any help would be helpful!

There are many alternatives you could attempt, such as enforcing an exclusive table lock on the SELECT query (thus delaying one entry until after the other).

My personal favorite would be to enforce staging - production tables just like many of the testing apps I manage.

This way, it doesn’t matter the timing or order. Remember the group_ID column is a database column, so it’s best not to get lost in the details over a value whose only purpose is to enforce differentiating groups


How do I get ER Studio Data Architect to automatically add SCD Fields to my Dimensions? This is my first try using this tool and Google searches have been fruitless.

How can I get ER Studio Data Architect to automatically add the SCD Maintenance Attributes (fields) to the tables I specify as Dimensions? I'm talking about StartDate, EndDate, and RowIsCurrent?

I'd love it if I could specify those fields and their data types once and not have to mess with it again and again.

If you have already created these entities/tables, there is a sample macro that comes with ER/Studio that does just this.  You'll want to run this on a test model first as you may want to customize it to get the exact results you want.

The sample macro is named ADD BASE ATTRIBUTES TO PERSON ENTITY. You, of course, will want it to work on all or selected entities.   You could customize the macro to add these attributes based on the table being selected.

If you want this to happen for new entities, you can create an entity/table that contains these common attributes/columns, then use that as a template.

I have created a macro to create tables with all the properties I want to be common.  The advantage to doing this is that macros can be tied to a hotkeys, making the creation of the new table easy.

If you don't have the resources to mess with a macro, you could do the workaround of creating a new set of Domains (reusable attributes) in their own folder.  Then you can at least quickly grab all 4 or so and drag them on to the tables.  Still manual, but only once per table.

So the answer you are looking for is dependent upon what the status of your model is currently.


MS SQL Server: Do multiple queries in a batch ever execute in parallel and if so what happens when the second query is dependent on the first? If I have a batch of two different SELECT statements, is it possible for them to be executed in parallel (if the SQL Optimizer deems it to be the most efficient execution method)?

If the first SELECT statement is selecting into a temp table and the second SELECT statement is then inserting into that same temp table, does that prevent it from being possible for the two statements to be ran in parallel?

(I'm guessing the answers are yes and yes :).

Batch statements are only ever executed serially in the order they appear in the batch.

Now, if you have two statements sent to the server by two different batches, they will run independently and essentially simultaneously (locking and latching aside).

Take for example the following code:

CREATE TABLE #t
(
    i int
);

INSERT INTO #t (i) VALUES (0);

The CREATE TABLE always runs prior to the INSERT INTO statement.  Consider this:

SELECT 1;

SELECT 2;

The above code will always be ran in order, i.e. SELECT 1 runs first, then after it completes, SELECT 2 runs.

There are multiple ways independent batches can be ran simultaneously, including the use of Multiple Active Result Sets, or MARS, however none of those affect the serial processing of statements within a single batch.


what is MDL? (e.g. 'MDL semantics' or 'MDL locks') Probably Galera related I found a guy talking about MDL semantics in a Galera/XtraDB bug report.

What is MDL? The term MDL also in Galera logs MDL conflict. Please explain in detail.

2018-06-14 17:07:10 140112699321088 [Note] WSREP: cluster conflict due to certification failure for threads:
2018-06-14 17:07:10 140112699321088 [Note] WSREP: Victim thread:
2018-06-14 17:07:10 140112690834176 [Note] WSREP: cluster conflict due to high priority abort for threads:
2018-06-14 17:07:10 140112690834176 [Note] WSREP: Winning thread:
2018-06-14 17:07:10 140112690834176 [Note] WSREP: Victim thread:
2018-06-14 17:07:10 140112690834176 [Note] WSREP: MDL conflict db=APC_MYSQLMON_DB table=TABLE1 ticket=4 solved by abort
2018-06-14 17:07:10 140112695683840 [Note] WSREP: MDL conflict db=APC_MYSQLMON_DB table=TABLE1 ticket=8 solved by abort
2018-06-14 17:07:10 140112695683840 [Note] WSREP: cluster conflict due to certification failure for threads:
2018-06-14 17:07:10 140112695683840 [Note] WSREP: Victim thread:
2018-06-14 17:26:47 140112698108672 [Note] WSREP: cluster conflict due to certification failure for threads:
2018-06-14 17:26:47 140112698108672 [Note] WSREP: Victim thread:

2018-06-14 17:34:48 140087340817152 [Note] WSREP: cluster conflict due to certification failure for threads:
2018-06-14 17:34:48 140087340817152 [Note] WSREP: Victim thread:
2018-06-14 17:36:48 140076554697472 [Note] WSREP: cluster conflict due to high priority abort for threads:
2018-06-14 17:36:48 140076554697472 [Note] WSREP: Winning thread:
2018-06-14 17:36:48 140076554697472 [Note] WSREP: Victim thread:
2018-06-14 17:36:48 140076554697472 [Note] WSREP: MDL conflict db=APC_MYSQLMON_DB table=TABLE1 ticket=4 solved by abort
2018-06-14 17:36:48 140087340817152 [Note] WSREP: MDL conflict db=APC_MYSQLMON_DB table=TABLE1 ticket=8 solved by abort
2018-06-14 17:36:48 140087340817152 [Note] WSREP: cluster conflict due to certification failure for threads:
2018-06-14 17:36:48 140087340817152 [Note] WSREP: Victim thread:

2018-06-14 17:37:50 139917573339904 [Note] WSREP: cluster conflict due to certification failure for threads:
2018-06-14 17:37:50 139917573339904 [Note] WSREP: Victim thread:

2018-06-14 17:05:12 139927950939904 [Warning] WSREP: Failed to report last committed 1941199495, -4 (Interrupted system call)
2018-06-14 17:09:41 139900840323840 [Note] WSREP: cluster conflict due to high priority abort for threads:
2018-06-14 17:09:41 139900840323840 [Note] WSREP: Winning thread:
2018-06-14 17:09:41 139900840323840 [Note] WSREP: Victim thread:

It stands for "MetaData Locking" seeing the sources of MySQL https://dev.mysql.com/doc/dev/mysql-server/8.0.0/mdl_8h.html

It's a way to keep an ACID database by avoiding concurrent access to the same data. The idea is that the metadata of a table are "locked" when a query inside a transaction uses this table. Doing so, another transaction will not be allowed to do DDL queries on these locked tables.

For instance, if you do a START TRANSACTION then a SELECT * FROM t in one client application, then the metadata of the table t are locked. No other client will be able to do a ALTER TABLE t ... because it would mess with the table structure while the 1st client transaction is still opened.

More infos in MySQL doc https://dev.mysql.com/doc/refman/5.5/en/metadata-locking.html