MySql Error #1054 when adding new column to table using prepared statement I'm trying to add a new column named lot to a table, but I'm getting error #1054 - Unknown column 'lot' in 'field list'. What could be the problem? Here's the query:

SET @lot = (SELECT lot_no_start FROM `systemconstants`);
SET @sql = CONCAT('ALTER TABLE ipp_sorted ADD `',lot,'` INT PRIMARY KEY AUTO_INCREMENT=',@lot);
PREPARE st FROM @sql;
EXECUTE st;

Remember that building SQL in this way is just building a String that happens to contain some text that your DBMS understands.

Your new column name, >>lot<<, is just part of that SQL:

SET @sql = CONCAT( 'ALTER TABLE ipp_sorted ADD `lot` INT PRIMARY KEY AUTO_INCREMENT=', @lot );

Leaving >>lot<< outside the string literal, the DBMS is trying to interpret it as a column in something or other (variables being prefixed with '@' signs) but it can't find such a column, so gives up with this error.


SQL Server 2005: Full-text search space requirements Is there a way to calculate the physical drive space required for a full-text search catalog?  Space is cheap, but I'd like to know what I'm getting into.

I'm looking at a table of about 200 articles ranging in length.  I'd like to index the title field, and the body of the article.

UPDATE:  I'm somewhat looking to predict the future.  For example, 200 articles, the title is varchar(500) and the article body is varchar(max).  So, without creating the index, can an estimate be done on how large the full-text search catalog may be?

Here is some old code I wrote for SQL 2000.  It still works on SQL 2005.  You can use this to come up with a rough estimate of how much space you are using per document, then multiply that by your estimated number of documents.

   select 'CatalogName'   = left([name],30),
          'Status'        = case(FULLTEXTCATALOGPROPERTY ([name],'populatestatus'))
                              when 0 then 'Idle'
                              when 1 then 'Full population in progress'
                              when 2 then 'Paused'
                              when 3 then 'Throttled'
                              when 4 then 'Recovering'
                              when 5 then 'Shutdown'
                              when 6 then 'Incremental population in progress'
                              when 7 then 'Building index'
                              when 8 then 'Disk is full.  Paused.'
                              when 9 then 'Change tracking'
                              else 'Unknown'
                            end,
          'ItemCount'     = FULLTEXTCATALOGPROPERTY (name,'itemcount'),
          'IndexSize(MB)' = FULLTEXTCATALOGPROPERTY (name,'indexsize'),
          'UniqueWords'   = FULLTEXTCATALOGPROPERTY (name,'uniquekeycount'),
          'ErrorLogBytes' = FULLTEXTCATALOGPROPERTY (name,'logsize'),
          'Location'      = left(s.path,50)
   from sysfulltextcatalogs s


Why *not* ERROR: index row size xxxx exceeds maximum 2712 for index "foo"? We have repeatedly seen failing attempts to index columns with values exceeding a maximum size. Postgres 10 has this kind of error message for it:

ERROR:  index row size xxxx exceeds maximum 2712 for index "foo_idx"
HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
       Consider a function index of an MD5 hash of the value, or use full text indexing.

Examples:

Character varying index overhead & length limit
Index max row size error

Etc.

Now, a_horse_with_no_name demonstrated a case with much larger text values (10000 characters) that still seems to work with a UNIQUE index in Postgres 9.6. Quoting his test case:

create table tbl (col text);
create unique index on tbl (col);

insert into tbl
values (rpad(md5(random()::text), 10000, md5(random()::text)));

select length(val) from x;  -- 10000

No error, and the column value indeed tested with a length of 10000 characters.

Have there been recent changes or how is this possible?

Short answer: compression.
The data type text allows (lossless!) compression and storage out of line by default:
SELECT typstorage FROM pg_type WHERE typname = 'text';  -- 'x'

The manual about pg_type.typstorage:

p: Value must always be stored plain.
e: Value can be stored in a “secondary” relation (if relation has one, see pg_class.reltoastrelid).
m: Value can be stored compressed inline.
x: Value can be stored compressed inline or stored in “secondary” storage.

Note that m columns can also be moved out to secondary storage, but only as a last resort (e and x columns are moved first).

Test with pg_column_size() instead of length(). Be sure to test actual table columns (with compression applied) not just input values. See:
CREATE TABLE tbl (id int, col text);
INSERT INTO tbl(id, col) VALUES
   (1, rpad(md5('non_random'::text),     100, md5('non_random'::text)))
 , (2, rpad(md5('non_random'::text),    1000, md5('non_random'::text)))
 , (3, rpad(md5('non_random'::text),   10000, md5('non_random'::text)))
 , (4, rpad(md5('non_random'::text),  100000, md5('non_random'::text)))
 , (5, rpad(md5('non_random'::text),  500000, md5('non_random'::text)))
 , (6, rpad(md5('non_random'::text), 1000000, md5('non_random'::text)));

SELECT id, left(col, 10) || ' ...' AS col
     , length(col) AS char_length
     , pg_column_size(col) AS compressed
     , pg_column_size(col || '') AS uncompressed
FROM   tbl ORDER BY id;

id | col            | char_length | compressed | uncompressed
---+----------------+-------------+------------+-------------
 1 | 67ad0f29fa ... |         100 |        101 |          104
 2 | 67ad0f29fa ... |        1000 |       1004 |         1004
 3 | 67ad0f29fa ... |       10000 |        160 |        10004
 4 | 67ad0f29fa ... |      100000 |       1191 |       100004
 5 | 67ad0f29fa ... |      500000 |       5765 |       500004
 6 | 67ad0f29fa ... |     1000000 |      11487 |      1000004

SELECT pg_column_size(rpad(md5('non_random'::text), 1000000, md5('non_random'::text)));

pg_column_size
--------------
       1000004

db<>fiddle here
Note how the value is forced to be unpacked from its storage format with the noop expression: pg_column_size(col || '').
The 5th row would be too big to fit the index tuple (even with compression) and trigger the error message in the title.
The 6th row would be to big to fit even the index page and trigger the related error message:

ERROR:  index row requires 11504 bytes, maximum size is 8191

The test values generated with rpad() have repeating patterns, which allow for massive compression. Even very long strings still easily fit the max. size after compression this way.
Related:

What is the overhead for varchar(n)?

Long answer
I ran more extensive tests, tampering with storage internals to verify my understanding. Only for testing purposes!

db<>fiddle here

dbfiddle does not allow write access to system catalogs. But the queries are there to try "at home".


XML input data query I'm looking at a stored procedure that takes some XML from a web app and creates a SQL search with it. There are 11 variables but the execution plan says that this one line takes 68% of the whole execution time. The 68% is the cost relative to the batch. There are other similar @input.value processes that register as 0%, though the execution plan for this is more complex.

Can anyone tell me why or suggest a more efficient way of writing it?

SET @Message=@Input.value('(/Input/Message)[1][not(@xsi:nil = "true")]', 'int')

The query cost calculated for a query is an estimate. Even in the actual execution plan it is the estimated cost that is shown.

For some reason SQL Server Management Studio uses those estimated costs and comes up with a cost percentage for each query in a batch.

Generally speaking that percentage has nothing to do with the actual performance of the query and should (in my opinion) not be used.

Estimates are based on statics of the involved tables and columns but XML variables (and columns) does not have any statistics that describe what shape for form they have so for XML queries the estimates are even less useful then for other types of queries.

Measure the actual performance instead like duration and IO.

  suggest a more efficient way of writing it

Nothing comes to mind that would improve the performance of your query so until you actually see a performance issue with this query you could use it as is. Provided it does return what you want.


How to delete all rows in a Greenplum row oriented table In Greenplum, what is the best practice to delete all rows from a table? DELETE FROM TABLE or TRUNCATE TABLE? I dont have any child tables or foreign key constraints.

Since Greenplum uses the Postgres database engine, reasoning applicable to a Postgres database seems appropriate. I don't see any reason not to use TRUNCATE when deleting all rows in a table with no children or foreign keys (which is your case), unless you have the ON DELETE triggers that you want to fire.


Log file truncation on AG database I am POCing AlwaysON DAG for my company, and i have come into a very interesting debacle.
Seems like even though primary and replica's and all synced up, the log file on the primary DB does not get truncated automatically even with a checkpoint. It would have to wait for a log backup to be issued. Does it mean that even with AG, we still need to have scheduled TLOG backups running?

Since the databases in an AG always have to have Full Recovery Model, yes, the databases definitely need log backups scheduled frequently to avoid excessive log file growth.


Mongo slow startup after upgrade to 4.2.5 I have upgraded the MongoDB replica set, which consists of 3 members, from 4.0.11 to 4.2.5.

After upgrading, startup lasts about 5 minutes.

Before upgrading it was instant.

It is related to oplog size, because I tested with dropping oplog on new mongo 4.2 and startup was instant.

Max oplog size was 25GB, I decreased it to 5GB and the startup is still slow.

Mongo db is on AWS with EBS standard disks.

However mongo worked well until this upgrade.

Here is the part of the log output:

2020-04-07T16:36:19.391+0000 I  STORAGE  [initandlisten] Detected data files in /var/lib/mongodb created by the 'wiredTiger' storage engine, so setting the active storage engine to 'wiredTiger'.
2020-04-07T16:36:19.391+0000 I  STORAGE  [initandlisten] wiredtiger_open config: create,cache_size=7279M,cache_overflow=(file_max=0M),session_max=33000,eviction=(threads_min=4,threads_max=4),config_base=false,statistics=(fast),log=(enabled=true,archive=true,path=journal,compressor=snappy),file_manager=(close_idle_time=100000,close_scan_interval=10,close_handle_minimum=250),statistics_log=(wait=0),verbose=[recovery_progress,checkpoint_progress],
2020-04-07T16:36:19.931+0000 I  STORAGE  [initandlisten] WiredTiger message [1586277379:931910][29458:0x7f2d17521b40], txn-recover: Recovering log 2439 through 2440
2020-04-07T16:36:20.002+0000 I  STORAGE  [initandlisten] WiredTiger message [1586277380:2298][29458:0x7f2d17521b40], txn-recover: Recovering log 2440 through 2440
2020-04-07T16:36:20.086+0000 I  STORAGE  [initandlisten] WiredTiger message [1586277380:86290][29458:0x7f2d17521b40], txn-recover: Main recovery loop: starting at 2439/15997696 to 2440/256
2020-04-07T16:36:20.087+0000 I  STORAGE  [initandlisten] WiredTiger message [1586277380:87145][29458:0x7f2d17521b40], txn-recover: Recovering log 2439 through 2440
2020-04-07T16:36:20.140+0000 I  STORAGE  [initandlisten] WiredTiger message [1586277380:139996][29458:0x7f2d17521b40], txn-recover: Recovering log 2440 through 2440
2020-04-07T16:36:20.189+0000 I  STORAGE  [initandlisten] WiredTiger message [1586277380:189056][29458:0x7f2d17521b40], txn-recover: Set global recovery timestamp: (1586277366, 1)
2020-04-07T16:36:20.197+0000 I  RECOVERY [initandlisten] WiredTiger recoveryTimestamp. Ts: Timestamp(1586277366, 1)
2020-04-07T16:36:20.205+0000 I  STORAGE  [initandlisten] Starting OplogTruncaterThread local.oplog.rs
2020-04-07T16:36:20.205+0000 I  STORAGE  [initandlisten] The size storer reports that the oplog contains 260431 records totaling to 5145508040 bytes
2020-04-07T16:36:20.205+0000 I  STORAGE  [initandlisten] Sampling the oplog to determine where to place markers for truncation
2020-04-07T16:36:20.207+0000 I  STORAGE  [initandlisten] Sampling from the oplog between Apr  6 14:15:08:222 and Apr  7 16:36:06:1 to determine where to place markers for truncation
2020-04-07T16:36:20.207+0000 I  STORAGE  [initandlisten] Taking 981 samples and assuming that each section of oplog contains approximately 2654 records totaling to 52436838 bytes
2020-04-07T16:38:48.156+0000 I  STORAGE  [initandlisten] Placing a marker at optime Apr  6 14:15:16:84
…..
2020-04-07T16:38:48.156+0000 I  STORAGE  [initandlisten] Placing a marker at optime Apr  7 16:29:31:13
2020-04-07T16:38:48.156+0000 I  STORAGE  [initandlisten] WiredTiger record store oplog processing took 147951ms
2020-04-07T16:38:48.160+0000 I  STORAGE  [initandlisten] Timestamp monitor starting

The problem is when WiredTiger starts to count samples from oplog. I decreased oplog size to 5GB, and still has a lag when starting, more than 2 minutes. It was above 5 minutes when max oplog size was 25GB.
Before upgrading to 4.2, oplog size was also 25GB, but startup was instant.
Do you have any idea what could cause slow startup? Thanks.

I tried with changing following 3 WiredTiger default eviction parameters:

storage.wiredTiger.engineConfig.configString:
"eviction_dirty_target=60,
eviction_dirty_trigger=80,eviction=(threads_min=4,threads_max=4)"

Now mongo is starting immediately.
Is it safe to set eviction_dirty_target and eviction_dirty_trigger values like this? Default is : eviction_dirty_target (default 5%) and eviction_dirty_trigger (default 20%). Thanks.


Disk usage of integer column vs boolean column in Postgres I saw this question Bit vs. Boolean columns.

I'm asking myself the same for Postgres: does a single digit integer column occupy the same disk space of a boolean one? In big tables (~50 columns x ~50 million rows) which one perform best?
How can I find this out?

You can find out the storage size with

SELECT typname, typlen FROM pg_type WHERE typname IN ('bool', 'int4');

 typname | typlen
---------+--------
 bool    |      1
 int4    |      4
(2 rows)

However you need to take alignment into account:

SELECT typname, typlen, typalign FROM pg_type WHERE typname IN ('bool', 'int4');

 typname | typlen | typalign
---------+--------+----------
 bool    |      1 | c
 int4    |      4 | i
(2 rows)

c is “character” (1 byte), while i is “integer”.

If you define a table like this:

CREATE TABLE a (
   b boolean,
   i integer
);

you will get three unused “padding” bytes between the columns, so that the integer can be stored at an address that is divisible by 4.

So the boolean would take up 4 rather than 1 bytes of storage.

If you specify the columns the other way around, the space taken up by the data in each row would only be 5 bytes.

The table row itself (the “tuple”) does not only consist on the raw data, but there is a 23-byte “tuple header” for each row (see the documentation). There may be padding after the header so that the actual tuple data are aligned at a multiple of MAXALIGN (typically 8).

So if you want to optimize your table to use as little storage as possible, you need to take the order of the columns in the table into account.


Restore a database by pointing to one or more directories containing backup files and specifying a STOPAT time I'm working on prepping/testing migration of our ERP and other auxiliary systems to a new database server. This will involve restoring database backups from the old server onto the new one.

For the sake of convenience, I'd like to be able to use a script/stored procedure to restore a database by simply pointing to one or more directories containing the backup files (combination of full, differential, and log), and optionally specifying a STOPAT time, or omitting it and restoring to the most recent possible time given the backups present. I would also need to be able to specify new file locations (the MOVE option), either explicitly, or more generally "put all the data files here, and all the log files here". The tool should look at all the files in the provided directories, and determine which ones need to be restored to reach the target time.

The GUI restore tool in Management Studio can do all of this, but that obviously can't be included in a script. I'd like to be able to write a general migration script that would be conceptually similar to this:

EXEC master..RestoreDB
    @database='ERPSysDB',
    @source='\\oldserver\Backup\oldserver\ERPSysDB\FULL,\\oldserver\Backup\oldserver\ERPSysDB\DIFF,\\oldserver\Backup\oldserver\ERPSysDB\LOG',
    @datadir='D:\SQLServer\DATA',
    @logdir='L:\SQLServer\DATA';

--Do various cleanup/post processing steps in ERPSysDB here

EXEC master..RestoreDB
    @database='ERPCompanyDB',
    @source='\\oldserver\Backup\oldserver\ERPCompanyDB\FULL,\\oldserver\Backup\oldserver\ERPCompanyDB\DIFF,\\oldserver\Backup\oldserver\ERPCompanyDB\LOG',
    @datadir='D:\SQLServer\DATA',
    @logdir='L:\SQLServer\DATA';

--More post-processing in ERPCompanyDB here

--Now restore a test copy of the database that we don't back up directly on the production server,
--using production database backups instead:
EXEC master..RestoreDB
    @database='ERPCompanyDB',
    @restoreas='ERPCompanyTestDB',
    @source='\\oldserver\Backup\oldserver\ERPCompanyDB\FULL,\\oldserver\Backup\oldserver\ERPCompanyDB\DIFF,\\oldserver\Backup\oldserver\ERPCompanyDB\LOG',
    @stopat='2018-11-01T08:00:00'
    @with='MOVE ''ERPDataFile'' TO ''D:\SQLServer\DATA\ERPCompanyTestDB.mdf'', MOVE ''ERPLogFile'' TO ''L:\SQLServer\DATA\ERPCompanyTestDB.ldf''';

--Additional post-processing, database restores, etc...

Are there any existing tools that provide this functionality? I'm prepared to write my own, but it would be a fairly intricate chunk of code (particularly error/exception handling), so I don't want to reinvent the wheel. I'd prefer this to be usable within T-SQL, i.e. not PowerShell cmdlets, but CLR stored procedures would be fine (and quite possibly the easier/better way to implement this).

Here's what I put together last week, in lieu of finding anything else already in this niche.

https://github.com/dave-britten/RestoreDB

Usage is fairly simple (though there are a number of options to customize). For example, my test server reload script can just do something like this to restore the Dynamics GP system database:

EXEC master.dbo.RestoreDB @Source='+\\oldserver\Backup\oldserver\DYNAMICS', @Database='DYNAMICS', @AutoMove=1, @Replace=1

That will restore the database DYNAMICS to the latest available point in time, and automatically move all database files to the instance default data and log directories.

Two other useful options are @RestoreAs and @AutoRename, which, when used in combination, allow for easily restoring the database under a different name (e.g. a test copy), with the database files being automatically renamed (replace the old database name with the new database name).

Currently, RestoreDB requires at least SQL Server 2016, but I'd like to get it working with 2012 and later.


corrupted data in filestream table We started to observe a strange behaviour in out filestream table.

We have a couple of records that in field with data we have 0x or 0x000...
In audit trails data we have found that these broken/empty bytes are stored on insert, so we started to investigate our insert statements (by linq to sql/stored proc executed by ado.net) and found nothing.
But what is more strange, in the audit trails we have found a few records in which the data is correct, but no records with the zeros, which are stored in real table. It looks like the data was changed without the update statement.
We never disable the audit trails triggers on production database so each and every change should be logged.
It looks like these records got corrupted in other way, but we cant google anything and have no clue how to debug this situation properly.
We have checked SQL Server logs, Event Viewer but can't find anything.

Can anybody suggest any hints? We are completly stuck at this moment.

There are basically 3 options:

An app or query is changing the data
Storage is becoming corrupt (which can be due to the OS, HW, storage, etc.)
You've hit a bug in SQL Server

It sounds like you've ruled out #1 (although I'm going to come back to that in a minute - but let's rule out the other stuff first.)

To start ruling out #2, run DBCC CHECKDB on all your databases. I know, you're worried about the filestream data, but it's possible that you're starting to experience corruption in other parts of the database, too. Check all of the databases for corruption, and if anything pops up, stop troubleshooting filestream and start troubleshooting the bigger issue instead. Here's my corruption troubleshooting checklist.

To start ruling out #3, open a Microsoft support case. It's $500, and they work the problem with you until it's done. (Normally I'd say to patch SQL Server to the latest cumulative update (CU) first, but I don't think I've seen any filestream corruption issues in there.) The MS support case may not be a fast fix, but I'd wanna get that process started sooner rather than later. Because filestream is a bit of an edge case feature, it's entirely possible that you've hit a bug that other folks haven't hit yet.

Those two are pretty quick - so if they don't turn up anything, then it's time to refocus on #1 (which would still be my lead suspect.)


MySQL group concat not showing distinct values when joined to another table I have one view that gives me comma separated values and want to use that data in another view. In the first view, I am using group_concat( distinct ...) and when I run that view by itself, I can see only the distinct values as expected.

However, when I join that view to another table, I am now getting duplicates.

SELECT
group_concat( distinct tag separator ', ') AS tag,
FROM
fruit

tag is already group_concated in the first view. Running this query gives me:

apples, oranges, apples, pears, figs, oranges

Apples and oranges are duplicated. Why is this?

EDIT:

I've paired this down as much as I could. Here is the view with no joins.

SELECT
tag
FROM
fruit

Here are the rows returned:

apples, oranges
apples, pears
figs, oranges

How do I get a group_concat for the column with an output of:

apples, oranges, pears, figs

I entered that data like this

mysql> use test
Database changed
mysql> drop table if exists fruit;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table fruit
    -> (
    ->   id int not null auto_increment primary key,
    ->   tag varchar(20) not null
    -> );
Query OK, 0 rows affected (0.45 sec)

mysql> insert into fruit (tag) values
    -> ('apples'),('oranges'),('apples'),('pears'),('figs'),('oranges');
Query OK, 6 rows affected (0.05 sec)
Records: 6  Duplicates: 0  Warnings: 0

Note the output of these queries

mysql> select tag from fruit;
+---------+
| tag     |
+---------+
| apples  |
| oranges |
| apples  |
| pears   |
| figs    |
| oranges |
+---------+
6 rows in set (0.03 sec)

mysql> select distinct tag from fruit;
+---------+
| tag     |
+---------+
| apples  |
| oranges |
| pears   |
| figs    |
+---------+
4 rows in set (0.00 sec)

mysql> select group_concat(distinct tag) tag from fruit;
+---------------------------+
| tag                       |
+---------------------------+
| apples,oranges,pears,figs |
+---------------------------+
1 row in set (0.00 sec)

mysql>

According the info you just added to the question, each tag has two fruits. Please note that each tag field (holding two fruits) is unique. You should be storing the fruits as separate fields.

The GROUP_CONCAT function is for query aggregation, not set manipulation.

The only way to use the view is to do this convoluted solution

I reloaded the data like this first:

mysql> use test
Database changed
mysql> drop table if exists fruit;
Query OK, 0 rows affected (0.12 sec)

mysql> create table fruit
    -> (
    ->   id int not null auto_increment primary key,
    ->   tag varchar(20) not null
    -> );
Query OK, 0 rows affected (0.31 sec)

mysql> insert into fruit (tag) values
    -> ('apples, oranges'),('apples, pears'),('figs, oranges');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

Here are the contents now

mysql> select tag from fruit;
+-----------------+
| tag             |
+-----------------+
| apples, oranges |
| apples, pears   |
| figs, oranges   |
+-----------------+
3 rows in set (0.00 sec)

mysql> select distinct tag from fruit;
+-----------------+
| tag             |
+-----------------+
| apples, oranges |
| apples, pears   |
| figs, oranges   |
+-----------------+
3 rows in set (0.00 sec)

mysql>

Ready for the convoluted solution ???

Here is the code

SELECT CONCAT('SELECT GROUP_CONCAT(subtag) fruits FROM (SELECT ''',
REPLACE(tags,', ',''' subtag UNION SELECT '''),''') A') INTO @TagSQL
FROM (select GROUP_CONCAT(tag separator ', ') tags FROM fruit) A;
SELECT @TagSQL\G
PREPARE s FROM @TagSQL; EXECUTE s; DEALLOCATE PREPARE s;

Here is the output

mysql> SELECT CONCAT('SELECT GROUP_CONCAT(subtag) fruits FROM (SELECT ''',
    -> REPLACE(tags,', ',''' subtag UNION SELECT '''),''') A') INTO @TagSQL
    -> FROM (select GROUP_CONCAT(tag separator ', ') tags FROM fruit) A;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT @TagSQL\G
*************************** 1. row ***************************
@TagSQL: SELECT GROUP_CONCAT(subtag) fruits FROM (SELECT 'apples' subtag UNION S
ELECT 'oranges' subtag UNION SELECT 'apples' subtag UNION SELECT 'pears' subtag
UNION SELECT 'figs' subtag UNION SELECT 'oranges') A
1 row in set (0.00 sec)

Now, execute the SQL generated

mysql> PREPARE s FROM @TagSQL; EXECUTE s; DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

+---------------------------+
| fruits                    |
+---------------------------+
| apples,oranges,pears,figs |
+---------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

I TOLD YOU IT WAS CONVOLUTED !!!


Removing multiple values using if else statements? I have a program that downloads raw data from a sensor. Due to to some specifications made in the SQL code, there are values that are duplicated which needs to be deleted to maintain data integrity.

Below is a snapshot of the dataset:

Do note that is is just a snapshot of july 10th. I have 33 other devices all of them have data dates from july 10th all the way through october.

Is there a way for me to traverse the whole dataset and find a way to set conditional statements. For example, if there are 2 values that share the same timing, take the row that has the "1" value.

At the same time, i would like to point out that this dataset is data that is collected 24/7.

You can use the ROW_NUMBER() function to produce a windowed record ID for each row, grouped by RoomCode and Time as per this db<>fiddle.

For each of the semi-duplicate rows (where RoomCode and Time are the same but Occupancy is different) this produces an incrementing number based on occupancy in descending order (so 1 = 1 and 0 = 2).

Place this in a CTE and then inner join your table to the CTE based on all columns except Occupancy where the ROW_NUMBER() pseudo column is > 1.

Example:

;WITH DataCTE AS (
SELECT RoomCode, Occupancy, Time, DayFromDate,
ROW_NUMBER() OVER (PARTITION BY RoomCode, Time ORDER BY Occupancy DESC) AS RN
FROM DataPoints
)

DELETE d1
FROM DataPoints d1
INNER JOIN DataCTE d2
ON d1.RoomCode = d2.RoomCode
 AND d1.Occupancy = d2.Occupancy
 AND d1.Time = d2.Time
 AND d1.DayFromDate = d2.DayFromDate
WHERE d2.RN > 1

Result:

RoomCode    Occupancy   Time    DayFromDate
EW1A-03-08  0   07/10/2019 08:00:00     Wednesday
EW1A-03-08  1   07/10/2019 08:30:00     Wednesday
EW1A-03-08  1   07/10/2019 09:00:00     Wednesday
EW1A-03-08  1   07/10/2019 09:30:00     Wednesday
EW1A-03-08  1   07/10/2019 10:00:00     Wednesday
EW1A-03-08  1   07/10/2019 10:30:00     Wednesday
EW1A-03-08  0   07/10/2019 11:00:00     Wednesday
EW1A-03-08  0   07/10/2019 11:30:00     Wednesday
EW1A-03-08  0   07/10/2019 12:00:00     Wednesday
EW1A-03-08  0   07/10/2019 12:30:00     Wednesday
EW1A-03-08  0   07/10/2019 13:00:00     Wednesday
EW1A-03-08  0   07/10/2019 13:30:00     Wednesday
EW1A-03-08  0   07/10/2019 14:00:00     Wednesday
EW1A-03-08  0   07/10/2019 14:30:00     Wednesday
EW1A-03-08  0   07/10/2019 15:00:00     Wednesday
EW1A-03-08  1   07/10/2019 15:30:00     Wednesday
EW1A-03-08  0   07/10/2019 16:00:00     Wednesday

You may need to massage this example and include additional columns in the PARTITION BY clause to ensure you're only selecting the correct near-duplicates but this should achieve the result you're after.

P.S. Its always a good idea in questions to include DDL (table definition), DML (INSERT statement with example data) and expected results to help clarify what you're trying to achieve.


call mysql function by string name Can i call a mysql stored function from another mysql stored function or stored procedure, passing a string to the first function which is the name of the second function to call. I.e choosing the the second function via a value in field.

I suspect I can't and I will have to use a case statement, but I have to ask :)

Something like this?

CREATE FUNCTION `string_processing_chain` (function_name    VARCHAR(1000),
                                            data_to_process VARCHAR(1000))
RETURNS VARCHAR(1000)
BEGIN

RETURN ($function_name)(to_process);
END

I have never heard of something like this and I have never attempted before

Here is my best guess (using Dynamic SQL)

CREATE FUNCTION `string_processing_chain` (function_name    VARCHAR(1000),
                                        data_to_process VARCHAR(1000))
RETURNS VARCHAR(1000)
BEGIN

    DECLARE rv VARCHAR(1000);

    @sql = CONCAT('SET rv = ',function_name,'(',data_to_process,')');
    PREPARE s FROM @sql;
    EXECUTE s;
    DEALLOCATE PREPARE s;

    RETURN rv ;
END

or

CREATE FUNCTION `string_processing_chain` (function_name    VARCHAR(1000),
                                        data_to_process VARCHAR(1000))
RETURNS VARCHAR(1000)
BEGIN

    SET @rv = '';

    @sql = CONCAT('SET @rv = ',function_name,'(',data_to_process,')');
    PREPARE s FROM @sql;
    EXECUTE s;
    DEALLOCATE PREPARE s;

    RETURN @rv ;
END

Give it a Try and tell us all if it worked, please !!!

From OP I used this :

CREATE PROCEDURE `string_processing_chain`(
    function_name   VARCHAR(1000),
    data_to_process VARCHAR(1000) )
BEGIN
    SET @sql2 = CONCAT('CALL ',function_name,'(?)');
    SELECT @sql2;
    PREPARE s FROM @sql2;
    SET @a = data_to_process;
    EXECUTE s USING @a;
    DEALLOCATE PREPARE s;
END


What is the best partition technique for large table with TEXT column? MySQL: What is the best partition technique for large InnoDB table (around 1.5M rows) with TEXT type of column?

There is a FULLTEXT index on the TEXT column, which is very essential for my searching operation.

You can't partition an InnoDB table with a fulltext index, which would make intuitive sense, since it's difficult to envision how fulltext searches on (what would presumably be implemented as) multiple underlying fulltext indexes (one for each partition) would be able to give meaningful relevance and query expansion results.

Full-text search (FTS) and foreign keys are not supported by InnoDB partitioned tables. For more information, see Section 12.9.5, “Full-Text Restrictions” and Section 18.6.2, “Partitioning Limitations Relating to Storage Engines”.
— http://dev.mysql.com/doc/refman/5.6/en/online-ddl-partitioning.html

The limitation applies to all storage engines.
http://dev.mysql.com/doc/refman/5.6/en/fulltext-restrictions.html
http://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations-storage-engines.html


Log Autogrowths bigger than log size, but no shrinks to explain how I have a database with a logfile size of 1GB.  I implemented some logging of AutoGrowths to eventually setup some notifications for when a database autogrows.

What I'm experiencing though, is that SQL Server is saying there were autogrowths of the log file, but the filesize of the log file is not actually changing.  There are enough autogrowths to have grown my file to 12GB in size since the beginning of the year, but my file is still only 1GB.

Does anyone know how/why this would happen?

Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64)   Jul 12 2019 17:43:08   Copyright (C) 2017 Microsoft Corporation  Enterprise Edition (64-bit) on Windows Server 2016 Standard 10.0  (Build 14393: ) (Hypervisor)

There haven't been any autoshrinks, or manual shrinks of the log file.

The most likely explanations I can think of are:

the database has been restored from a backup or snapshot

in the case of a restore from backup, the log file would be reverted to whatever size it was when the backup was taken
in the case of a restore from a snapshot, the log file is discarded and replaced with a tiny, 2-vlf log file

the monitoring is missing shrink events, or duplicating growth events due to a bug

the default trace event "Log File Auto Shrink" will only capture automatic shrinks.  If someone uses DBCC SHRINKFILE, it will show up as an Audit DBCC Event event.  So make sure you're capturing both

Check to see if restores are regularly occurring (for instance, if this is a test or reporting database).  Otherwise, you might need to review the code for the monitoring queries, and make sure there aren't missing or duplicated events.

One way to try and gather more information would be to use DBCC SQLPERF('LOGSPACE') to determine if the size of the log is ever changing (thanks to Paul Randal for suggesting this).  You could add this to your existing every-15-minutes check, by dumping the output into a temp table (and from there you could insert it into your logging tables):

CREATE TABLE #log_space
(
    [DatabaseName] sysname NOT NULL,
    LogSizeMb float NOT NULL,
    LogSpaceUsedPct float NOT NULL,
    [Status] int NOT NULL
);

INSERT INTO #log_space
EXEC ('DBCC SQLPERF(LOGSPACE);');

This will help you track down whether the file ever actually grows.  If it does, now you'll know at what point in the day it's shrinking again.


Optimal way of loading, registering, indexing and querying a huge XMLDB I've got 35 million highly structured XML documents, with a total size of 500 GB. An XSD is available for this data, and the end goal is to transform the data into an RDB. All fields can be considered varchar, number, or date; except maybe one or two CLOB fields. The XSD structure contains a lot of 1-n relationships, and sub-nested structures, so is quite complex.

The data has to get loaded once, and then transformed into an RDB once, so speed of update/insert queries is irrelevant. Also the speed of loading/index creation is not so important, since it only has to run once. As long as it finishes within a week or two i'm happy.

At the moment I'm setting up the XMLDB by registering the XSD as Object Relational, with create objects and create tables set to true. For now, I haven't added any other significant annotations to the XSD, and I'm only loading about 50MB's of data as a test. I'm using a whole set of XMLTable/flwor queries to transform the XML data into RDB tables. I fear scaling this to 500 GB will cause queries that run for eternity.

As I'm quite new to XMLDB, and there are -a lot- of possible tweaks and settings in the XMLDB documentation, I'm wondering if there are some crucial features/annotations/optimizations.... i should be looking out for that can have an impact on the overall speed of the final queries, or other parts of the process.

This is a very broad question, but, any tips are welcome, and if there's more information that might be relevant, ask away!

Wouter got, hopefully, useful answers here:

https://community.oracle.com/message/12608939

That said, being the Oracle ACE Director for this technology. Oracle XMLDB functionality is nowadays so interwoven with the Oracle database that this is an MANDATORY (install) functionality for Oracle 12c and onwards. It is the basis for much in the database like ACL security, database security, Database Vault, Oracle APEX, Multitenent functionality, import and export, DBMS_packages like DBMS_METADATA, Oracle performance implementations (AWR XMLtype columns etc etc), DBA/Developer tools like DB Express...

XML is (alas?) everywhere nowadays. To give one example, you using it now (that is if you use a mobile phone...)


How to filter for consecuting timeline events in pure SQL? I run a LPR system on a parking lot, and I record every plate along with the camera that recorded it.

I have 2 cameras, camera 1 is on the entry and camera 2 on the exit. So for every reading I record the following on a table

Id|Camera|Plate|Timestamp

I need to run a query which returns every car who has not exited the parking lot. So the condition would be: I need to return every record which has an entry (camera = 1) not followed by an exit (camera = 2).

Which would be the best approach? I don't want to use cursors if possible.

Should I create 2 temp tables:

SELECT plate, MAX(Timestamp)
FROM records WHERE camera = 1 GROUP BY plate, MAX(timestamp)

one for each camera, then

SELECT temp1_entries except temp2_exits

and the result of the above query would be the cars who hasn't exited along with their entry timestamp.

DBMS is MariaDB, version 10.3.7.

Is this approach correct? Is there a better approach?

You don't need cursor for that.

If your id are automatically incrementing, you also don't need ordering by the timestamp, the id is enough. If not, then use timestamps. Note, timestamps have some possibility of funny effects (timezones, daylight saving times, simultaneous effects inside a single second and many similar "surprises").

This query filters those camera1 events, which are not followed by a camera2 event - either because the car is in the lot, or because they are tricking. The query handles also "funny" cases, for example, if a car would be detected by the camera on the entrance multiple times.

It does not handle the case somebody leaves the lot without entering it, but understanding the logic, it is easy to improve.

WITH Raw AS (
  SELECT RANK() OVER (PARTITION BY Plate ORDER BY Id) AS EventNo,
  Id,
  Camera,
  Plate,
  Timestamp
  FROM CameraLog
)
SELECT Prev.*
FROM CameraLog AS Prev
LEFT JOIN CameraLog AS Next
  ON Prev.Plate = Next.Plate AND Prev.EventNo + 1 = Next.EventNo
WHERE Prev.Camera = 1 AND Next.Camera <> 1;

The logic is that first we extend the table with the "event history" order of a single car by the RANK() windowing function: the Raw subquery essentially extends the CameraLog table with a new, EventNo column. This column is essentially an auto-incrementing id, but it is ordered for all the plates induvidually.

In the second step, in the main query, we join this table with itself together. Essentially, we try to find for all event the next one.

The result is that we have such a table:

columns of camera events | columns of the consecuting camera event

In this column structure, it is already easy to filter with a WHERE (last line).

Note, this query will be highly suboptimal, if the size of the tables is in the order of the available RAM in your server. If you need to handle large data tables quickly, you will need more advanced techniques.


How do you index a text column in MySQL? If I create a table with a text column

CREATE TABLE foo ( bar text );

I can't index the bar column.

MariaDB [test]> CREATE INDEX foo_bar_idx ON foo ( bar );
ERROR 1170 (42000): BLOB/TEXT column 'bar' used in key specification without a key length

If I HELP CREATE INDEX, I get,

Syntax:
CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option] ...

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string';

KEY_LENGTH not mentioned only KEY_BLOCK_SIZE, but if I use it as an option, I get

MariaDB [test]> CREATE INDEX foo_bar_idx ON foo ( bar ) KEY_LENGTH = 5;
ERROR 1911 (HY000): Unknown option 'KEY_LENGTH'

In MySQL Key Length is added as a type-modifier and placed in parenthesis colname(), you can provide it to CREATE INDEX like this,

CREATE INDEX foo_bar_idx ON foo ( bar(500) );

It's part of index_col_name, (except it's not optional on text so ignore the [])

index_col_name:
  col_name [(length)] [ASC | DESC]

Alternatively you may want an Full Text Search (FTS) index instead providing MATCH functionality

CREATE FULLTEXT INDEX foo_bar_ftsidx ON foo ( bar );

For more information see the MySQL docs on Full Text Search


Why is my DB size increasing when rebuildling an index using sort in tempDB in SQL Server? I am running Ola Hallengren's SQL Maintenance scripts using the following command:

EXECUTE [dbo].[IndexOptimize]
@Databases = 'MyDatabase'
, @FragmentationLow = NULL
, @FragmentationMedium = 'INDEX_REBUILD_OFFLINE'
, @FragmentationHigh = 'INDEX_REBUILD_OFFLINE'
, @FragmentationLevel1 = 10
, @FragmentationLevel2 = 30
, @UpdateStatistics = 'ALL'
, @OnlyModifiedStatistics = 'Y'
, @LogToTable = N'Y'
, @TimeLimit = 23400
, @MinNumberOfPages = 1000
, @Indexes = 'ALL_INDEXES'
, @MaxDOP = 7
, @SortInTempdb = 'Y'

Up until recently a number of the indices were unable to rebuild due to space constraints so I shifted tempDB to its own drive, configured tempDB to use 8 different files, and started sorting in tempDB (each tempDB file is roughly 10GB and growth set to 500MB each). Some indexes that were difficult to rebuild previously were able to complete relatively quickly, however, i have one index that I have been running for more than an hour in MyDatabase and that database is rapidly increasing in size. Nothing else is currently running and TempDB has not increased in size at all.

Can anyone help me figure out why MyDatabase is increasing so rapidly? Does anyone have any suggestions about what it could be causing this?

Running SQL Server 2012 if that helps.
Thanks

I have quoted three sections from different articles and gave the links at the bottom. Bottom line is you need space for the new index to get build plus transaction log space for rollback. If you cannot accommodate the space required you might want to consider INDEX_REORGANIZE.

When a new index structure is created, disk space for both the old (source) and new (target) structures is required in their appropriate files and filegroups. The old structure is not deallocated until the index creation transaction commits.

Index rebuild (whether online or offline, and at least as far back as 7.0) will create a new copy of the index before dropping the old copy. The pages and extents required to do this will always be allocated as needed, as with any other operation in SQL Server.

Large-scale index operations, performed offline or online, can generate large data loads that can cause the transaction log to quickly fill. To make sure that the index operation can be rolled back, the transaction log cannot be truncated until the index operation has been completed; however, the log can be backed up during the index operation. Therefore, the transaction log must have sufficient space to store both the index operation transactions and any concurrent user transactions for the duration of the index operation. For more information, see Transaction Log Disk Space for Index Operations.

Reference:

Misconceptions around index rebuilds (allocation, BULK_LOGGED mode, locking) by Paul Randal
Index Operations That Require Additional Disk Space
Transaction Log Disk Space for Index Operations


MySQL Workbench model/source synchronize issue I've been looking all over for any description of an issue like this and I haven't found anything so far.

I am trying to update my MySQL database using MySQL Workbench (5.2.40 CE). I am using the Synchronize Model option just as I have always done before to update the database with the changes I've made to the model. Main thing to note here is that the issue I'm going to describe happens even without any changes being made to the model.

The problem is when it gets to the point where it shows "Model and Database Differences", the fields which all normally line up between Model and Source are totally off.
I get everything doubled with the first set showing the Model names of my Schema and Tables with the Sources next to all of them showing N/A. Then below that it has all of the Source values filled in while the Model values all say N/A.

If I run the update as it is my database does get updated, but it also completely wipes out all the information on the database because it is dropping all the tables and re-entering them. Here is the picture of what the page looks like.

I have no idea if this is some kind of setting or bug. But on my test database after totally getting wiped out and trying it again, the Differences page shows up normally with everything 1:1 like it should, and it updated just fine.

Any clues on this would be appreciated.

MySql Workbench is powerful, but not perfect.

Some time in similar case, I've done the following to resolve the problem:

create a blank project
require a model update FROM db source
then I'll reapply the mod.

Often this happen if you just change a 'comma' [a small thing] in the db without sync it with the model, and then you change another 'comma' in the model. MySql Workbench goes crazy often as db increases in complexity and table number, but I think it's a very good product.


What are the performance benefits of segregating database objects into different tablespaces? As the question states, what are the benefits of doing this given the following information/constraints:

I will not be using partitioning
I will not be placing the tablespaces and its datafiles into different hard disk drives
My schema has lots (maybe around 50 to 100) of small tables and there are only about 5 to 10 tables that will grow in terms of number of records
Expected growth of these 5 to 10 tables can reach up to 100k rows but will not probably reach a million rows

I am planning to segregate these big tables into their own tablespaces instead of mixing it with the tablespace used for the rest of the tables and indexes. I am also planning to place all indexes on a separate tablespace, so the list of tablespace that my user will be using should look something like this:

Tablespace_BIG_Table_1...10
Tablespace_for_indexes
Tablespace_for_the_rest_of_the_tables

Aside from being "neat and organized" with my tablespaces, are there any performance benefits that I can get from this given that I am not using partitioning and only using a single disk, in addition I am only working with a relatively small set of records?

Or should I just stick with placing everything on a single tablespace?

Minimal performance benefit, the only benefit in this regard is from reduced fragmentation. But Oracle and most filesystems handle this much better these days than in the past; Oracle handles this better with "automatic segment space management" and the Oracle "filesystem" ASM, though you don't need to use the latter.

It's more a manageability choice these days; personally I wouldn't bother with separate tablespaces, but I do often create one per application. This allows you to use a transportable tablespace export if you so desire.


Can I perform a transaction across two different MySQL databases on two different servers in different tables I have a table A in database db1 in server A and table B in database db2 in server B, both of which are MySQL.

Is it possible to make a transaction, where updates in both tables are involved?

                      +-> Server A | db1 | table A -+
Transaction:  Update -+                             + -> COMMIT / ROLLBACK
                      +-> Server B | db2 | table B -+

If so, kindly provide step by step instruction on how to achieve this.

This is a duplicate question. It seems like this is a possibility with InnoDB as your engine, but other than that, unfortunately, it does not seem like there is a way. This also seems dependent on the version of MySQL, like 5.6 and above, the thread I linked to points to a documentation here.

A side note: just because this seems to a possibility, it may not be the best solution. It seems that it would be best to write the transaction, and use a script to run the transaction on both servers.


Allow MongoDB Read/Write But Disallow Delete Permission I want a user to be able to read and update, but not able to delete in any collection for MongoDB.

The command I used is:

db.createUser(
  {
   user: "user",
    pwd: "user",
    privileges: [
       { resource: { db: "icif_pattern" , collection: "" },
         actions: [ "find", "createCollection", "dbStats", "collStats" ] },

    ],
    roles: []
  }
)

But it throws an error:

Error: couldn't add user: "privileges" is not a valid argument to
createUser

I am following the information found in the following article:

Mongo user roles (MongoDB Docs)

As per MongoDB documentation here Collection-level access control allows administrators to grant users privileges that are scoped to specific collections.

Administrators can implement collection-level access control through user-defined roles. By creating a role with privileges that are scoped to a specific collection in a particular database, administrators can provision users with roles that grant privileges on a collection level.

Required Access

To create a role in a database, you must have:

the createRole action on that database resource.
the grantRole action on that database to specify privileges for the
new role as well as to specify roles to inherit from.

Built-in roles userAdmin and userAdminAnyDatabase provide createRole and grantRole actions on their respective resources.

To create a role with authenticationRestrictions specified, you must have the setAuthenticationRestriction action on the database resource which the role is created.

use admin
db.createRole(
   {
     role: "myClusterwideAdmin",
     privileges: [
       { resource: { cluster: true }, actions: [ "addShard" ] },
       { resource: { db: "config", collection: "" }, actions: [ "find", "update", "insert", "remove" ] },
       { resource: { db: "users", collection: "usersCollection" }, actions: [ "update", "insert", "remove" ] },
       { resource: { db: "", collection: "" }, actions: [ "find" ] }
     ],
     roles: [
       { role: "readWrite", db: "admin" }
     ]
   },
   { w: "majority" , wtimeout: 5000 }
)

Roles

In the roles field, you can specify both built-in roles and user-defined roles.

{ role: "<role>", db: "<database>" }

For your further ref here , here and here


Querying pg_buffercache: relation "pg_buffercache" does not exist I have PostgreSQL 9.4.7 installed in LXC container on Ubuntu 14.04. For monitoring purposes, i try to execute this from my host machine:
psql -qAtX -h *** -p *** -U postgres -c "select count(*) from pg_buffercache where isdirty", so I am expecting to get a pure decimal value for parsing. However, I get relation "pg_buffercache" does not exist error. I have already added pg_buffercache extension (CREATE EXTENSION pg_buffercache;), restarted server afterwards and checked if it is present:

postgres=# \dx
pg_buffercache | 1.0    | public     | examine the shared buffer cache

The strangest thing is, if i execute this query directly in postgresql shell, it works just fine:

postgres=# select count(*) from pg_buffercache where isdirty;
count
-------
 113

What can cause this? Other queries not using pg_buffercache work perfectly.

You need to first connect to the database that you need to check the stats from, and in that do the create extension, after which the query should work ;)


MySQL Workbench: Custom fields for Relationships I have to manage an existing DB, created "manually" without mysql-wb.

I want to draw the printable ER diagram.

When I create a relationship, workbench creates its own external key fields.

How can I create the relationship binding to my own fields?

Are you using the Place a relationship using existing columns option? Using one of the other options will create a new column in the referencing table, which is mostly useful when you're creating new structure.

You can create relationships by editing the table, going to Foreign Keys, and adding it there manually, as well.


mysql match against with same score on different input I am trying to find the nearest match against a given name with mysql. The scoring is for the first two results the same, but looking at it I would rather get the second one as it fit's the name almost.

SELECT ID, name, MATCH (`name`)
AGAINST ('Superocean Héritage 42mm' IN BOOLEAN MODE) as `score`
FROM cat_names cs
WHERE MATCH (`name`) AGAINST ('Superocean Héritage 42mm' IN BOOLEAN MODE)
ORDER BY `score` DESC

The result:

Superocean Héritage     6.3744072914123535

Superocean Héritage 42  6.3744072914123535

Superocean Héritage 46  6.3744072914123535

Is there a way to tweak the query in order to retrieve the second result correctly?

Additionally the fulltext search returns a low quality score for exact matches:

Big Bang Aero Bang
9.445959091186523
Big Bang 44 mm
6.32852840423584
Big Bang Caviar
6.32852840423584
Big Bang Jeans
6.32852840423584
Big Bang Meca-10
6.32852840423584
Big Bang Sang Bleu
6.32852840423584
Big Bang Unico
6.32852840423584
Spirit of Big Bang
6.32852840423584
Big Bang 41 mm
6.32852840423584
Big Bang Broderie
6.32852840423584
Big Bang Ferrari
6.32852840423584
Big Bang King
6.32852840423584
Big Bang Pop Art
6.32852840423584
Big Bang Tutti Frutti
6.32852840423584
Big Bang
6.32852840423584

The exact match has the lowest score.

Ah, you want a higher score for exact match?  Instead of

ORDER BY `score` DESC

do

ORDER BY `name` = 'Superocean Héritage 42mm' DESC,
         `score` DESC

The = test will be 1 for an exact match, else 0.  So, effectively, it overrules whatever the score returns.

(Alternative ways to phrase it involve IF or CASE).


NLS_NUMERIC_CHARACTERS in SQL loader control file I am using a Control File to import data from a CSV file into a table, using sqlldr to import the data, where a large number of fields are numeric values with integer and decimal part separated by dots.
The Control File works just fine because I cast the number to the input my table expects by using

"TO_NUMBER(:MyVariable, '9999999999D999999', 'NLS_NUMERIC_CHARACTERS = ''.,''')"

Although the control file works just fine I would like to know: Is it possible to use:

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ","

In this way I don't mind about differences between character used in NLS and the one used to create the file, nor i mind about the actual number of decimal significative number.

I had the same question and I find a solution that works for me!

You have to put the sentence set NLS_NUMERIC_CHARACTERS=,. in the beginning of the .bat file. This must be before you call the load in the .ctl file.


Rebuilding index has impact on mdf/ldf files SQL Server 2008 While rebuilding index have used option 'SORT_IN_TEMPDB = ON' in order to avoid unnecessary growing your user database files.

What does it exactly means?

Is complete process done on TempDB and does not grow mdf/ldf file size?

For example before reindex (database is in simple recovery mode), the largest index size in the database is 25GB

.mdf size (100GB allocated and used --> no freespace)
.ldf size (20GB)
TempDB has 200GB freespace

What will be the approximate size for .mdf & .ldf files. Are they grow or not?

When you rebuild an index the database engine creates what is called an "intermediate sort result." These intermediate results are like chunks that are combined to become your index and creating these chunks requires space. If it can the database engine will actually sort the index in memory; but if not it will store the intermediate results in the destination file group.

Using the SORT_IN_TEMPDB option asks SQL Server to store these intermediate results in TempDb instead of rebuilding them in the source database. When the TempDb resides on a different disk to the source database this can help the performance of the rebuild task and can reduce the performance impact on your database.

A requirement of rebuilding indexes is that you must have sufficient space (wherever you decide to build the index) to hold the largest index in your database: this in your example is 25GB. Which, if you use TempDb you should quite easily have.

As to whether your source database will grow, well I wouldn't rule it out but it won't grow anywhere near to what it would if you rebuilt the indexes within it. If you are concerned about this fact then take a copy of the database and put it in a test environment and try it out - it is the only way to really know for sure.

SORT_IN_TEMPDB:

http://msdn.microsoft.com/en-us/library/ms188281.aspx

I hope this helps you.


how does SQL Server know to lock view objects? Not sure whether this is a more or less appropriate place to ask this question, originally posed at Stack Overflow.

In SQL Server 2008 I have a view V over tables A and B that looks roughly like

create view V as
    select * from A
    union all
    select * from B

Reading from V causes a query to take intent shared locks on the base tables, but also takes an intent shared lock on the view object itself.

It is clear why we need the IS locks on the tables, and we can see that the IS lock on the view prevents concurrent modification to the tables underlying the view. That's fine.

The query plan contains no mention of the view. It's completely compiled out, and the resulting plan in this case is a simple concatenation of rows from the two base tables. Indeed the only mention of the view in the query plan XML is in the statement text.

If you add a second view U over the tables, reading from V does not cause any lock to be taken on U. This rules out that the engine just takes an IS lock on all views over A and B.

How does the database engine know to take a lock on the view?

Is the statement text parsed again?
Is there some other channel of information between the query planner and underlying execution to pass this information? If so, what?

If the latter, the details of the mechanism by which the storage engine knows to lock the view can fairly be considered internal. However the fact that it does this is user-visible, and I would expect it to be documented somewhere.

From Conor Cunningham, the ultimate source of anything engine- or optimizer-related:

  We track things during compile to check at runtime.  We do not parse
  things at execution for this purpose.

  Note: the internals of what we do from one release to another are not
  guaranteed.  This is beneath the officially supported surface area.

My belief is that the binary version of the execution plan (not the one that is readable and exposed to us via XML, which is only a subset of the binary version) must contain some pointer to the view(s) referenced in the original query text (and this was alluded to in the SO question). It obviously isn't parsing the query text every time. Conor implies as much above, but is careful to not reveal any details about where or how this is stored, since this could potentially change from release to release or even with a service pack or cumulative update. He probably also doesn't want to encourage any detective work. :-)


Postgres: Find previous and next row or existing value created by SERIAL colum for separate query I got a Postgres 13 db and a table with an id based on a SERIAL. After deleting rows such a sequence would have gaps.
In each row binary data is stored too and I would like to process it row after row moving backward or forward.
My idea would be to do "SELECT id FROM tbl;" and then use this as a lookup table. Another idea would be to create a view with a new continuous sequence and to get the max value and then iterate between sequence start and max value.
EDIT:
Example:
DROP TABLE IF EXISTS tbl;
CREATE TABLE tbl (
    id SERIAL PRIMARY KEY,
    name VARCHAR,
    data BYTEA
);

INSERT INTO tbl (name, data) VALUES('a', '0xff');
INSERT INTO tbl (name, data) VALUES('b', '0xff');
INSERT INTO tbl (name, data) VALUES('v', '0xff');
INSERT INTO tbl (name, data) VALUES('w', '0xff');
INSERT INTO tbl (name, data) VALUES('t', '0xff');
INSERT INTO tbl (name, data) VALUES('u', '0xff');

DELETE FROM tbl WHERE id='3' or id='5';

As you can see the row id is having gaps.
I would like to do e.g. something like that (Written in Pseudocode):
std::vector<int> vec = SELECT id FROM tbl; -- -> Store values in C++ vector named vec

for(int i=0; i<vec.size(); i++) { -- Loop through vector resp. every row in the database table
    SELECT * FROM tbl WHERE id=vec[i] -- Query every row in tbl
    //Do something with the binary data -- Process its data
}

Is there a better way to achieve this?
If it plays a role, I am using libpq.
Thanks

Seeing the loop in pseudo-code, it seems like you want a SQL cursor.
it's instantiated with DECLARE associated to a query with an ORDER BY if needed, and you can use MOVE to move forward or backward, and FETCH to fetch the current row, or several rows, and finally CLOSE when you're done.
All these are SQL statements and independent from any client-side language.


How to run sql agent jobs in a sequence order.with T-sql My requirement is to run the SQL Agent jobs (EX: job1 to job10) with T-sql in a sequence order. That means job2 starts automatically when job1 completes. This process should continue until job10 completes.

Thank you.

Have the last step of job 1 be to fire job 2, use this code:

EXEC dbo.sp_start_job N'MyJob 2'

Then, the last step of job 2 be to fire job 3, so on and so forth.


dbmail failing to send intermittently We have a SQL Agent job that runs daily that sends emais out to our drivers with their schedules for the next day.

For 85% of drivers, the emails are not getting out of the SQL server. The other 15% send without error.

I checked the logs and see this error:

  The mail could not be sent to the recipients because of the mail
  server failure. (Sending Mail using Account 3 (2020-03-22T16:00:06).
  Exception Message: Cannot send mails to mail server. (Failure sending
  mail.).  )

Does anyone have any idea what's wrong, or how to get a more detailed error?

This problem started after we migrated the server to a new datacenter.

We are running SQL Server 2014 on a Windows Server 2008 R2 host.

Adding to the SQL Mail Fail to Send quoted in the comments by LowlyDBA

From this question:

Comprehensive troubleshooting of DatabaseMail

I would first of all see what is the outcome of the following query (as you already got dbmail up and running):

--==============================================================
-- I’m doing a TOP 100 on these next several queries as they tend
-- to contain a great deal of data.  Obviously if you need to get
-- more than 100 rows this can be changed.
-- Check the database mail event log.
-- Particularly for the event_type of "error".  These are where you
-- will find the actual sending error.
--==============================================================
SELECT TOP 100 *
FROM msdb.dbo.sysmail_event_log
ORDER BY last_mod_date DESC;

--==============================================================
-- Check the actual emails queued
-- Look at sent_status to see 'failed' or 'unsent' emails.
--==============================================================
SELECT TOP 100 *
FROM msdb.dbo.sysmail_allitems
ORDER BY last_mod_date DESC;

--==============================================================
-- Check the emails that actually got sent.
-- This is a view on sysmail_allitems WHERE sent_status = 'sent'
--==============================================================
SELECT TOP 100 *
FROM msdb.dbo.sysmail_sentitems
ORDER BY last_mod_date DESC;

--==============================================================
-- Check the emails that failed to be sent.
-- This is a view on sysmail_allitems WHERE sent_status = 'failed'
--==============================================================
SELECT TOP 100 *
FROM msdb.dbo.sysmail_faileditems
ORDER BY last_mod_date DESC

Depending on the outcome of these queries, there are a few ways to troubleshoot.

Maybe the problem is not within sql? for that I use powershell to send a few test emails see if everything is ok. (change the details in the script below)

$smtpServer = "200.1.1.223"
$smtpPort = 25
$emailFrom = "donotreply@xxxxx.co.uk"
$emailTo = "mmiorelli@xxxxx.co.uk"
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Port = $smtpPort
$subject = "ccatsql"
$body = "test email from ccatsql "
$smtp.Send($emailFrom, $emailTo, $subject, $body)

check if the .Net framework 3.5 is installed - that can cause weird errors, even in sql server 2016, as quoted in

SQL Server 2016 - Database Mail doesn't work without .NET 3.5

and from my own experience, sometimes, no error will be written to any log whatsoever, if the .NET 3.5 or any underlying component is missing or corrupt.

To work around this issue (ONLY if this is the issue), you can implement any one of the following:

Create the DatabaseMail.exe.config and drop it next to the DatabaseMail.exe under the Binn folder.

You can use notepad.exe or any other editor to edit it.

Just make sure you save it by using UTF-8 encoding (in notepad.exe, select Save As... and in the Encoding combo box, select UTF-8):

Run a repair setup action of SQL Server 2016.
Manually install .Net Framework 3.5 on the machine.

please see more information on this answer.


MySQL GTID error 1236 I am try to reduce machine come to live in production.. MySQL is making bottleneck. What i trying. I update code in one machine and also MySQL restore everything is working fine after this i am making EC2 AMI and launched machine from autoscaling group. now in this instance i am not taking and any live dump. after start MySQL and make it replication but its showing me GTID error. these all processes taking 30 to 40 minutes.

  Last_IO_Error: Got fatal error 1236 from master when
  reading data from binary log: 'The slave is connecting using CHANGE
  MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary
  logs containing GTIDs that the slave requires.

any body can tell what i doing wrong. but if i take fresh dump and restore this in live machine its working. MySQL version 5.6.17.

You need to take dump from Master to replicate.

  master has purged binary logs containing GTIDs that the slave
  requires.

Master delete its binary logs and create a new binary logs file. That's the reason when you implementing replication after taking dump is working because it's getting gtid_executed and gtid_purged that is available in the current binary log.

But when you are implementing without dump, The gtid_executed and gtid_purged getting matched because Mater already purged it.

So you must have to take dump and even sometimes you need to set the gtid_purged manually on slave.

You can read this, it has explained very well.
https://www.percona.com/blog/2013/02/08/how-to-createrestore-a-slave-using-gtid-replication-in-mysql-5-6/


Drop '\user' - slash user from database Running SELECT * FROM dbName.dbo.sysusers ORDER BY name; reveals users that start with \, and this causes errors when try to add the same user without the slash.

Can't run EXEC sp_dropuser or DROP USER. This throws an error telling that user does not exist or you do not have permission.

Any ideas on how to remove the user? I believe that the \user is orphaned from a database migration from SQL Server 2000 or 2005. The current database is running on SQL Server 2016. I've tried using the GUI and T-SQL.

I've tried EXEC sp_dropuser and DROP USER using [\user], '\user',\user, QUOTENAME('\user').

I get either

  Incorrect syntax

or

  Cannot drop the user '\user', because it does not exist or you do not have permission.

errors.

Here is the row from the sysusers table.

uid              23
status           0
name             \User
sid              0x010500000000000512000000FD77B156780C942546170A3273170200
roles            NULL
createdate       2012-08-17 10:01:05.600
updatedate       2012-08-17 10:01:05.600
altuid           NULL
password         NULL
gid              0
environ          NULL
hasdbaccess      0
islogin          1
isntname         0
isntgroup        0
isntuser         0
issqluser        0
isaliased        0
issqlrole        0
isapprole        0

Try putting the name in brackets.

DROP USER [\username];

Given the problems you are having try doing this through dynamic SQL just to confirm that you are catching every possible character.  You could also try this through Object Explorer.

DECLARE @sql nvarchar(1000)
SELECT @sql = N'DROP USER [' + name + N']' FROM sys.database_principals WHERE name like '\user%'
PRINT @sql
EXEC sp_executesql @sql

@solomonrutsky brought up an interesting question.  Try running the following:

SELECT s.name AS Sysusers_Name, dp.name AS DP_Name
FROM sys.sysusers s
JOIN sys.database_principals dp
    ON s.sid = dp.sid
WHERE s.name = '\user'

See if you get any results, and if so if the Sysusers_name doesn't equal the DP_Name.  If you get a DP_Name try dropping that one instead.


Postgres functions: how to pass a type as an argument I am using this query to list the values that are part of an enum:

SELECT unnest(enum_range(null::my_enum_name)) AS enum_values;

It works.

I am trying to make it more reusable by wrapping it in a function that takes the name of the enum (my_enum_name in the example above) as a parameter, but I can't find a lot of documentation on this.

Is it possible?

It is possible:

create type mood as enum ('sad','ok', 'happy');
CREATE TYPE

create function f(p_typname text) returns setof text
as $$
begin
   return query execute
      format(
         'SELECT unnest(enum_range(null:: %s))::text AS enum_values',
         p_typname
      );
end;
$$
language plpgsql;
CREATE FUNCTION

select * from f('mood');
   f
-------
 sad
 ok
 happy
(3 rows)


SQL Query Declaration problem I have a view as shown below.The View is good running.When I attempt to run view,I got the following the error.What might the problem?

Incorrect syntax near the keyword 'Declare'

SQL Query:

create View [dbo].StokMaliyet
AS
Declare @Dolar decimal(18,3),@Euro decimal(18,3)

set @Dolar=(select Dolar from GunlukDoviz
set @Euro=(select Euro from GunlukDoviz)

 select Stok_Kodu,
 (case
 when dovtip='3' then net_maliyet
 when dovtip='2' then ((net_maliyet)*@Dolar)/@Euro
 When dovtip='0' then (net_maliyet)/@Euro
 end )  as Maliyet
 from ib_mlyt

You cannot use declare and any code manipulation in view (select statement ONLY at SQL Server of course).

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
[ WITH <view_attribute> [ ,...n ] ]
AS select_statement
[ WITH CHECK OPTION ] [ ; ]

<view_attribute> ::=
{
    [ ENCRYPTION ]
    [ SCHEMABINDING ]
    [ VIEW_METADATA ]     }

See for more details

Use stored procedure instead of view


MongoDB: operator to match arrays whose elements are all contained in a specified array The $all operator selects the documents where the value of a field is an array that contains all the specified elements.

In my situation I need the opposite: an operator that selects the documents where the value of a field is an array whose elements are all contained in the specified array.

For example, consider the following collection:

{"name": 1, "vals": ["A", "B"]}
{"name": 2, "vals": ["D", "C"]}
{"name": 3, "vals": ["A", "D"]}

I want to select all the documents whose vals field contains values in ["A", "B", "D"]; that is, documents 1 and 3 (not 2 because "C" is not contained in the specified array).

I cannot use {vals: {$all: ["A", "B", "D"]}}, nor {vals: ["A", "B", "D"]} as both of these operations select the documents whose vals field contains all the specified values, which is not what I want.

Is what I need possible?

Depending on your version of MongoDB server there are a few ways to approach this.

In MongoDB 3.6+ you can use the  $setIsSubset expression (which is true only when the first array given is a subset of the second) via the $expr operator:

> db.haystack.find({$expr:{$setIsSubset:["$vals",["A","B","D"]]}},{_id:0})
{
    "name": 1,
    "vals": [
        "A",
        "B"
    ]
}
{
    "name": 3,
    "vals": [
        "A",
        "D"
    ]
}

For earlier versions of MongoDB a similar outcome is possible (but less obvious) using double negation with the $elemMatch operator:

> db.haystack.find({$nor:[{vals:{$elemMatch:{$nin:["A","B","D"]}}}]},{_id:0})
{
    "name": 1,
    "vals": [
        "A",
        "B"
    ]
}
{
    "name": 3,
    "vals": [
        "A",
        "D"
    ]
}

Roughly translated: "find all documents where it is not the case that the vals array includes a value other than one in the provided list".


How to find a session with a specific command I have an Oracle instance with some active sessions from different users. How can I find the SID/SERIAL of a specific session given that this session is surely running a specific SELECT I know?

select sid, serial# from v$session where sql_id = (select sql_id from v$sql where sql_text = 'your SQL text');


missing scott schema in Oracle XE I have downloaded Oracle XE 11.2 and successfully installed on my Windows machine. But, I didn't find Scott schema in it. During installation, I didn't get any option where it asked to create sample schema. Could anyone suggest how to get scott schema in Oracle XE 11.2 ?

List of users :

I have imported SCOTT schema using following script

C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\scott.sql

Then I reset the password for this user using

alter user scott identified by tiger;

Now, I was able to login to scott and able to use existing table like EMP, DEPT etc..


Query logical processing phases while mixing old and new join syntax Could anyone clarify why order of tables in FROM matters if I mix old and new syntax for joining tables?
For example,

SELECT *
FROM table1 a, table2 b
INNER JOIN table3 c ON (c.a_id = a.id) -- error - a.id - invalid identifier
WHERE (a.id = b.a_id)

SELECT *
FROM table2 b, table1 a
INNER JOIN table3 c ON (c.a_id = a.id) -- ok, table1 a is the last in FROM
WHERE (a.id = b.a_id)

I'm not going to use such ugly queries, but I believe all tables specified in FROM should be visible to other parts of the query.
I don't think it's up to RDMS because SQLServer, Oracle, and Mysql behave the same way - error for first query, no error for second. I'm definitely missing something obvious, but I didn't find anything that describes why it works this way.

Thank you.

It is to do with the precedence of the comma operator. As stated in the MySQL docs

  the precedence of the comma operator is less than of INNER JOIN, CROSS
  JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join
  types when there is a join condition, an error of the form Unknown
  column 'col_name' in 'on clause' may occur

You get the same results with the rewrite below (where the position of the ON clause makes clear the logical order of join operations)

Fails

SELECT *
FROM   table1 a
       INNER JOIN (table2 b
                   INNER JOIN table3 c
                     ON c.a_id = a.id ) -- error - a.id - invalid identifier
         ON a.id = b.a_id

Succeeds

SELECT *
FROM   table2 b
       INNER JOIN (table1 a
                   INNER JOIN table3 c
                     ON c.a_id = a.id )
         ON a.id = b.a_id

In the one that succeeds a is first joined to c on c.a_id = a.id then the virtual table resulting from that join is joined to b on a.id = b.a_id which all works fine.

For the one that fails b is first attempted to be joined to c on c.a_id = a.id which is invalid as that references table a. The ON clause can only refer to the table immediately preceding it or one already brought in by a previous ON clause.

From the conditions c.a_id = a.id and a.id = b.a_id then c.a_id=b.a_id so you could change the join condition to that and it would work or you could of course also use a derived table or view to alter the precedence and ensure a,b were treated as a logically joined unit before joining onto c

SELECT *
FROM   (SELECT *
        FROM   table2 b,
               table1 a) ba
       INNER JOIN table3 c
         ON ( c.a_id = ba.id )
WHERE  ( ba.id = ba.a_id )


Server stuck in INTERNAL DDL LOG RECOVERY IN PROGRESS Recently, one of my servers had a crash. I had alot of trouble but managed to restore everything.

But,

Now, the first background thread upon startup is locked on "INTERNAL DDL LOG RECOVERY IN PROGRESS" and never stops. It does not affect the system as it does not consume any CPU / memory, it simply just starts and never stops.

Ive found many with the same problem but there is no solution for this problem.

I have run a mysqlcheck on all tables, and they are all OK.

All tables are of Innodb

Does anyone have any idea how I can solve this?

Mysql version 5.6.13-log

I have MySQL 5.6.21 on my laptop running Windows 8.1 and it is doing nothing. It still has that state. Please keep in mind that this state exists in the performance schema, in the table threads, in the column PROCESSLIST_INFO.

Please note the MySQL Documentation on PERFORMANCE_SCHEMA.THREADS.PROCESSLIST_INFO

  The statement the thread is executing, or NULL if it is not executing any statement. The statement might be the one sent to the server, or an innermost statement if the statement executes other statements. For example, if a CALL statement executes a stored procedure that is executing a SELECT statement, the PROCESSLIST_INFO value shows the SELECT statement.

There is still an active bug report for MySQL 5.6.20, 5.6.22, 5.7.6 on this. The performance schema is enabled by default in MySQL 5.6. Therefore, the only solution, at this time, would be to disable the performance schema. You would have look for future releases to see if this has been resolved.

Personally, I would not worry about this. It seems to behave like MySQL Replication's I/O and SQL threads. Those threads poll indefinitely. Log recovery of this nature would happen during MySQL startup and should not be polling after MySQL Startup and InnoDB Crash Recovery have completed. If the bug simply left the state unchanged and nothing is going on, you could go on using MySQL as usual.

To verify this, please do the following:

STEP 01 : Look in the error log and make sure

crash recovery had finished
MySQL: ready for connections appears near the bottom

STEP 02 : Log into MySQL, and run this

mysql> select * from information_schema.engines where engine='InnoDB'\G

It should look like this

*************************** 1. row ***************************
      ENGINE: InnoDB
     SUPPORT: DEFAULT
     COMMENT: Supports transactions, row-level locking, and foreign keys
TRANSACTIONS: YES
          XA: YES
  SAVEPOINTS: YES
1 row in set (0.00 sec)


Can I use mirroring to make a copy of my Database? We are moving a database off of a older physical server and putting it onto a Virtual Machine (SQL 2005 to 2014 I hope).  I need to copy a single database from the physical to virtual with as little downtime as possible. Originally I was just going to do a full backup and then restore on the new machine, but that will take almost an hour on this database.

My question is: If I can get the new virtual machine setup before the downtime, would it be effective to setup a database mirror from the old Physical box to the new Virtual box?  Then when the downtime came I would cut the mirror (database should be in sync) and realias the server to be the same name as the old physical database server?

I have never had to use mirroring so I was not sure if this was a "Smart" way to do it. If it would work the only downtime would be the name change.

The main problem with mirroring is that if you have a network outage or get disconnected mirroring will stop. So across networks it is better to use log shipping.

Additionally it sounds like you have a vldb or poor disk i.o based on your time listed in your question which is why you might prefer log shipping for the migration as you would only need to restore the last log to bring up the new database. This is ideal for minimizing downtime.

This blog, while old, highlights some points i explained


How to pass value to ON CONFLICT clause that's not a column? Basically, I'm trying to do multiple inserts in a single command in postgresql, where my ON CONFLICT has a per-insert parameter.

Here is the query (formatted for psycopg2):

INSERT INTO
    web_pages
    (url, starturl, netloc, distance, is_text,
     priority, type, addtime, state)
VALUES
    (%(url)s, %(starturl)s, %(netloc)s, %(distance)s, %(is_text)s,
      %(priority)s, %(type)s, %(addtime)s, %(state)s)
ON CONFLICT (url) DO
    UPDATE
        SET
            state           = EXCLUDED.state,
            starturl        = EXCLUDED.starturl,
            netloc          = EXCLUDED.netloc,
            is_text         = EXCLUDED.is_text,
            distance        = LEAST(EXCLUDED.distance, web_pages.distance),
            priority        = GREATEST(EXCLUDED.priority, web_pages.priority),
            addtime         = LEAST(EXCLUDED.addtime, web_pages.addtime)
        WHERE
        (
                web_pages.ignoreuntiltime < %(ignoreuntiltime)s
            AND
                web_pages.url = EXCLUDED.url
            AND
                (web_pages.state = 'complete' OR web_pages.state = 'error')
        )
    ;

I want to be able to pass multiple VALUES tuples, but %(ignoreuntiltime)s can vary per value-tuple.

Is there any way I can specify it as a additional parameter in the values tuple, or similar, for each row?

What I want is kind of:

INSERT INTO
    web_pages
    (url, starturl, netloc, distance, is_text,
     priority, type, addtime, state, not_a_column)
VALUES
    (%(url)s, %(starturl)s, %(netloc)s, %(distance)s, %(is_text)s,
     %(priority)s, %(type)s, %(addtime)s, %(state)s, %(ignoreuntiltime)s)
    ....(more value tuples here)....
ON CONFLICT (url) DO
    UPDATE
        SET
            state           = EXCLUDED.state,
            starturl        = EXCLUDED.starturl,
            netloc          = EXCLUDED.netloc,
            is_text         = EXCLUDED.is_text,
            distance        = LEAST(EXCLUDED.distance, web_pages.distance),
            priority        = GREATEST(EXCLUDED.priority, web_pages.priority),
            addtime         = LEAST(EXCLUDED.addtime, web_pages.addtime)
        WHERE
        (
                web_pages.ignoreuntiltime < EXCLUDED.not_a_column
            AND
                web_pages.url = EXCLUDED.url
            AND
                (web_pages.state = 'complete' OR web_pages.state = 'error')
        )
    ;

Not possible, because
There is currently no direct way in Postgres 9.6, because:

In the UPDATE, only the special EXCLUDED row is visible (in addition to the updated row). There is no FROM clause allowed to join in additional tables.

The EXCLUDED row is exactly the state of the would-be inserted row that was rejected by conflict. Exactly the columns of the table to be inserted into. Nothing more.

So there is no room for any additional columns not mentioned in the INSERT in the UPDATE branch of the UPSERT. Future version may allow more. Various additions have been discussed, but not yet implemented. It's a complex matter.
Clean workaround
I suggest this workaround using CTEs.

Provide rows with all columns you need in a free-standing VALUES expression. (CTE data in below example.)

Run INSERT ... ON CONFLICT ... DO UPDATE, but do not actually update any rows (WHERE FALSE). The desired side effect: excluded rows are locked anyway to make it safe for concurrent use.
With just ON CONFLICT ... DO NOTHING, conflicting rows are not locked and fair game for concurrent transactions. If there cannot be concurrent writes to the same rows of the same table, use this cheaper variant.

Run a separate UPDATE as part of the same command, where you can use all columns from the CTE data - and join in arbitrary additional tables, too, if needed.

Demo table:
CREATE TEMP TABLE tbl (tbl_id int PRIMARY KEY, note text);
INSERT INTO tbl VALUES
   (1, 'old1')
 , (2, 'old2');

Demo query:
WITH data (tbl_id, note) AS (
   VALUES
      (int '1', text 'update')   -- explicit type declarations for free standing VALUES
    , (2, 'no update')           -- going to exclude row in WHERE of UPDATE
    , (3, 'insert')              -- not going to INSERT the "note"
   )
, ins AS (
   INSERT INTO tbl AS t (tbl_id) -- "note" not inserted
   SELECT tbl_id FROM data
   ON     CONFLICT (tbl_id) DO UPDATE
   SET    note = NULL
   WHERE  FALSE                  -- never executed, but locks row.
   RETURNING t.tbl_id
   )
UPDATE tbl t
SET    note = d.note             -- now we can!
FROM   data d
LEFT   JOIN ins i USING (tbl_id)
WHERE  i.tbl_id IS NULL
AND    t.tbl_id = d.tbl_id
AND    d.note <> 'no update';    -- just to demonstrate use in WHERE

Result:
TABLE tbl ORDER BY tbl_id;

 tbl_id |  note
--------+--------
      1 | update   -- updated
      2 | old2     -- not updated because of WHERE in UPDATE
      3 |          -- inserted without "note"

Related:

How to include excluded rows in RETURNING from INSERT … ON CONFLICT


Combine - Select Into, with Insert Into Select I've read many explainations like this one here that say "Select Into ..." is to create new tables and "Insert Into ..." is to append to existing tables.

I'm automating a coworker's sql scripts. Currently these sql scripts create new tables (assuming they don't exist) using Select Into, and this is causing a dilemma. When the automation fires off the second time, I get an error because the table already exists and, consequently, the second round of data isn't inserted.

I'd prefer not to tell my coworker to rewrite his hundreds of lines of code by specifying all the column names twice in all his scripts. Is there some minimalist way I can combine the idea of the "Select Into" and the "Insert Into" into a single query, without explicitly duplicating all the column names? maybe like "Select Into ... On Error " or something like that?

EDIT: I'm using MS SQL

When the automation fires off the second time, I get an error because the table already exists and, consequently, the second round of data isn't inserted.

I would suggest you to put a condition to check the existence of the table

If exists (select 1 from sys.tables where name = '' and type = 'U')
begin -- table exists, so insert values in it
insert into table_name values ()
end
else
begin -- table does not exist, so create it
create table table_name (column datatype)
insert into table_name values ()
end

You can even use below sql to create new blank table as the where 1=0 will always be false.

Note that it wont create any indexes from old_table into new_table.

SELECT *
INTO new_table
FROM old_table where 1=0

To capture errors, you can use TRY/CATCH


Get min and max row from table From a table with the columns: id(pk), type_id, book_id, title_id, page_id, lines(int4), created_at(date)

How can I select the row with min(lines) and max(lines) for (type_id, book_id, title_id, page_id) and also the value of created_at when min(lines) and the same for max value.

Dummy data:

type_id, book_id, title_id, page_id, lines, created_at
1, 1, 1, 1, 12, 2015-04-01
1, 1, 1, 1, 10, 2015-03-01
1, 1, 1, 1, 11, 2015-03-02
1, 1, 1, 2, 3, 2015-04-01
1, 1, 1, 2, 5, 2015-03-01
1, 1, 1, 2, 6, 2015-03-04

Expected output:

type_id, book_id, title_id, page_id, min, min_created_at, max, max_created_at
1, 1, 1, 1, 10, 2015-03-01, 11, 2015-03-02
1, 1, 1, 2, 3, 2015-04-01, 6, 2015-03-04

One way to do this is using window functions:

SELECT DISTINCT
    type_id, book_id, title_id, page_id,
    first_value(lines)      OVER wmin AS min_lines,
    first_value(created_at) OVER wmin AS min_created_at,
    first_value(lines)      OVER wmax AS max_lines,
    first_value(created_at) OVER wmax AS max_created_at
FROM
    tableX
WINDOW
    wmin AS (PARTITION BY type_id, book_id, title_id, page_id
             ORDER BY lines ASC, created_at ASC),
    wmax AS (PARTITION BY type_id, book_id, title_id, page_id
             ORDER BY lines DESC, created_at ASC) ;

Tested at SQLfiddle.


Why doesn't FTS match any records? I created an indexed view with a few fields that i created a full text search based on these fields.

here is my view:

alter VIEW vFullTextSearch
WITH SCHEMABINDING
AS
    SELECT  M.Id as MessageId, M.Subject , M.Body,MR.EmailAddress, MR.FriendlyName,
            M.Subject + ' ' + M.Body + ' ' + MR.EmailAddress + ' ' + MR.FriendlyName as FTS
    FROM DBO.MESSAGE M
        INNER JOIN DBO.MessageRecipient MR ON M.Id = MR.MessageId AND MR.RecipientTypeId=1
GO

--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1_vFTS
    ON vFullTextSearch (MessageId);
GO

I indexed all the fields of the table.

I am using this SQL to find results

SELECT M.*
FROM Message AS M
    INNER JOIN FREETEXTTABLE(vFullTextSearch, *, 'Doe') AS KEY_TBL
        ON M.Id = KEY_TBL.[KEY];

but with "John Doe" being the [vFullTextSearch.FriendlyName] value in all the records of my view why am i not gettign results when searching all the fields for 'doe' ?

According to MSDN...http://msdn.microsoft.com/en-us/library/ms177652.aspx

table
    Is the name of the table that has been marked for full-text querying. table or view can be a one-, two-, or three-part database object name. When querying a view, only one full-text indexed base table can be involved.
    table cannot specify a server name and cannot be used in queries against linked servers.

Since your view has full text indexes on more than one table you will not be allowed to do this.

You could possibly use a union to check both tables in your view.

SELECT {your select columns}
FROM Message AS M
    INNER JOIN FREETEXTTABLE(dbo.Message, {your message columns}, 'Doe') AS KEY_TBL
        ON M.Id = KEY_TBL.[KEY]
    INNER JOIN MessageRecipient MR on M.Id = MR.MessageId AND MR.RecipientTypeId=1

Union

SELECT {your select columns}
FROM Message AS M
    INNER JOIN MessageRecipients MR on  M.Id = MR.MessageId AND MR.RecipientTypeId=1
    INNER JOIN FREETEXTTABLE(MR.MessageRecipients, {your MessageRecipients columns}, 'Doe') AS KEY_TBL
        ON MR.Id = KEY_TBL.[KEY];

I don't have access to a SQL installation right now, but let me know if it works.


How to SELECT TOP(1) with COALESCE to pick the top NOT NULL row for each column? I have a hierarchy of settings that have a default for everyone, can be overridden by groups, or by an individual user, for a specific screen, etc...

These are stored in a table with a Hirarchy-ID to sort on, like this:

HIERARCHY | SETTING | OPTION | PREFERENCE | COLOR |
----------+---------+--------+------------+-------+
100       | NULL    | 1      | NULL       | NULL  |
10        | NULL    | 2      | square     |       |
1         | 0       | 3      | rounded    | green |

The result I'm after is for each column, the first not-null value (and it would be great if I can also squash a NULLIF(COLOR, '') in as well). In this example the result would be:

SETTING | OPTION | PREFERENCE | COLOR |
--------+--------+------------+-------+
0       | 1      | square     | green |

Currently I'm doing a separate query for each column but I'm hoping that this can be achieved in a more elegant way?

I'm working with MS SQL Server (and need to support version 2005).

WITH cte AS ( SELECT hierarchy, setting, "option", preference, color
              FROM source s1
              WHERE NOT EXISTS (SELECT 1
                                FROM SOURCE s2
                                WHERE s2.hierarchy LIKE s1.hierarchy+'_')
              UNION ALL
              SELECT s.hierarchy,
                     COALESCE(cte.setting, s.setting),
                     COALESCE(cte."option", s."option"),
                     COALESCE(cte.preference, s.preference),
                     COALESCE(cte.color, s.color)
              FROM source s
              JOIN cte ON cte.hierarchy LIKE s.hierarchy+'_'
              )
SELECT setting, "option", preference, color
FROM cte
WHERE hierarchy LIKE '_'

fiddle


Enable MySQL trigger for all users in mysql I have setup triggers in my MySQL tables and when I applied them they were restricted to the account I used when creating the triggers, now I have multiple users in my MySQL and I want it so that the triggers will work for all users not just for the one I used when creating them. I currently tried this:

CREATE DEFINER=`%`@`%` TRIGGER after_atm_account_insert
    AFTER INSERT ON `system_database`.atm_account
    FOR EACH ROW
BEGIN
    INSERT INTO `log_database`.system_logs
    SET actionDone = 'insert',
        employee = USER(),
        logDate = NOW(),
        tableChange = 'atm_account',
        rowChange = NEW.atmID,
        new_data = concat("Account #",NEW.accountNumber,", Pin:",NEW.pin,", Security Code:",NEW.securityCode,", Withdrawal Limit:",NEW.withdrawalLimit);

END

If you think of the definer as the user that the trigger runs as then it needs the right grants to do whatever the triggers action is.

But it sounds like what you want is for other users to be able to trigger the trigger. In this case all of the users that you need to be able to invoke the trigger need the trigger privilege.

See more info here: http://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html#priv_trigger

But basically for each user you need to grant trigger.


How to use to_regclass with uppercase table names? I am using to_regclass() to check if a table exists (from here). This works for lowercase table names, but for table names with capital letters it always returns NULL, even when the table exists:

CREATE TABLE "asdf" (s text);
CREATE TABLE "FooBar" (s text);

SELECT to_regclass('asdf'); --     -> "asdf"
SELECT to_regclass('FooBar'); --   -> NULL
SELECT to_regclass('foobar'); --   -> NULL

Fiddle

Once you start using those dreaded double quotes, you have to use them always:

SELECT to_regclass('"FooBar"');

(another good example why you should avoid those dreaded quoted identifiers)


Mongodb: Determining shard key strategy on compound index I have a collection with 170 millions+ documents and it is only going
 to increase. The size of the collection is not that huge, currently
 around 70 GB.

The collection has two fields indexed on: {AgentId:1, PropertyId:1}.
 Generally one imports a huge file(millions of documents) belonging to
 a particular AgentId but the PropertyId(non numeric nullable) is
 mostly random unique value.

Currently I have two shards with shard key based on {_id: hashed}. But
 I am planning to change the shard key to  compound Index {AgentId:1,
PropertyId:1} because I think it will improve query performance( most
of the queries are based on AgentId filter). Not sure whether one can
have a nullable field in the shard key. If this is the case then app
 will make sure that the PropertyId is random no.

So looking to get a picture as to

How the data will be distributed to shards during insertion
 and how the range of a chunks are calculated during insertion?
Since the PropertyId is random value. Does the compound key fits the
  definition of monotonically increasing value?

I am a newbie to MongoDB. And wanted to know if I am on the right path?

MongoDB supports compound indexes, where a single index structure holds references to multiple fields ( As MongoDB Limits and Thresholds MongoDB imposes a limit of 31 fields for any compound index.) within a collection’s documents. The following diagram illustrates an example of a compound index on two fields:

  How the data will be distributed to shards during insertion and how
  the range of a chunks are calculated during insertion?

Starting in version 2.6, MongoDB can use  index intersection to fulfill queries. The choice between creating compound indexes that support your queries or relying on index intersection depends on the specifics of your system.

Index intersection does not eliminate the need for creating compound indexes. However, because both the list order (i.e. the order in which the keys are listed in the index) and the sort order (i.e. ascending or descending), matter in compound indexes, a compound index may not support a query condition that does not include the index prefix keys or that specifies a different sort order.

For example, if a collection orders has the following compound index, with the status field listed before the ord_date field:

{ status: 1, ord_date: -1 }

The compound index can support the following queries:

db.orders.find( { status: { $in: ["A", "P" ] } } )
db.orders.find(
   {
     ord_date: { $gt: new Date("2014-02-01") },
     status: {$in:[ "P", "A" ] }
   }
)

But not the following two queries:

db.orders.find( { ord_date: { $gt: new Date("2014-02-01") } } )
db.orders.find( { } ).sort( { ord_date: 1 } )

However, if the collection has two separate indexes:

{ status: 1 }
{ ord_date: -1 }

The two indexes can, either individually or through index intersection, support all four aforementioned queries.

  Since the PropertyId is random value. Does the compound key fits the
  definition of monotonically increasing value?

For the fastest processing, ensure that your indexes fit entirely in RAM so that the system can avoid reading the index from disk.

To check the size of your indexes, use the db.collection.totalIndexSize() helper, which returns data in bytes:

> db.collection.totalIndexSize()
4294976499

The above example shows an index size of almost 4.3 gigabytes. To ensure this index fits in RAM, you must not only have more than that much RAM available but also must have RAM available for the rest of the working set.

If you have and use multiple collections, you must consider the size of all indexes on all collections. The indexes and the working set must be able to fit in memory at the same time.

For further your ref Indexing Strategies