About real time statistics oracle 19c only for particular table Reg Real-Time statistics which extends online statistics gathering(12c) to also include conventional DML statements.
Real-time statistics helps the optimizer generate more optimal plans.Whereas bulk load operations gather all necessary statistics, real-time statistics augment rather than replace traditional statistics.

Oracle introduced new parameters

_optimizer_gather_stats_on_conventional_dml and _optimizer_use_stats_on_conventional_dml which are true by default and
_optimizer_stats_on_conventional_dml_sample_rate at 100%

How does real time statistics works?

By default the _optimizer_gather_stats_on_conventional_dml is true so its automatically kicks off

When a DML operation is currently modifying a table (conventional), Oracle Database dynamically computes values for the most essential statistics if the above parameter is on.

Consider a example of table that is having lot of inserts and rows are increasing. Real-time statistics keep track of the increasing row count as rows are being inserted. If the optimizer performs a hard parse of a new query, then the optimizer can use the real-time statistics to obtain a more accurate cost estimate.

USER_TAB_COL_STATISTICS and USER_TAB_STATISITICS has columns NOTES tell real time statistics have been used. "STATS_ON_CONVENTIONAL_DML".

Execution Plan shows

|Id| Operation                        | Name|Rows|Bytes|Cost (%CPU)|Time| Pstart|Pstop|
---------------------------------------------------------------------------------------
| 0| INSERT STATEMENT                 |     |    |     |910 (100)|        |     |     |
| 1|  LOAD TABLE CONVENTIONAL         |SALES|    |     |         |        |     |     |
| 2|   OPTIMIZER STATISTICS GATHERING |     |918K|  25M|910   (2)|00:00:01|     |     |
| 3|    PARTITION RANGE ALL           |     |918K|  25M|910   (2)|00:00:01|   1 |  28 |
| 4|     TABLE ACCESS FULL            |SALES|918K|  25M|910   (2)|00:00:01|   1 |  28 |

Also the explain plan in the query used will tell in note section
    Note

    -----
    - dynamic statistics used: stats for conventional DML

LOAD TABLE CONVENTIONAL and OPTIMIZER STATISTICS GATHERING are the new parameters that have been added to the explain plan for this new feature.

can we apply real-time statistics only for a particular set of table . if yes, how can we achieve them ? , as we know getting real time statistics for any dml operation thats occuring for more that 100k rows is very costly operation

I tried finding out the way, but was not successfull

First up, you should only change underscore parameters when instructed to do so by support. But more importantly for these parameters, Real-Time Statistics is only available on specific database offerings. See the license guide for details.

Changing these parameters is likely to be a violation of your license agreement!

  LOAD TABLE CONVENTIONAL and OPTIMIZER STATISTICS GATHERING are the new
  parameters that have been added to the explain plan for this new
  feature.

These operations are not specific to Real-Time Statistics. OPTIMIZER STATISTICS GATHERING is for any operation that captures stats during execution. create table as select is an example that has done this for a while now.

LOAD TABLE CONVENTIONAL means the database does a conventional (not direct-path) insert.

You can disable Real-Time Statistics for:

A specific statement with the no_gather_optimizer_statistics hint
A table by locking stats for that table (though this will stop the background job gathering stats too!)

For example, only the first of these three inserts has the OPTIMIZER STATISTICS GATHERING operation:

set long 10000
set serveroutput off
cl scr
create table t as
  select 1 c1 from dual
  connect by level <= 100;

insert /*+ monitor */into t
with rws as (
  select level x from dual
  connect by level <= 10
)
  select * from rws;

select dbms_sqltune.report_sql_monitor (report_level=> 'basic +plan')
from   dual;

SQL Plan Monitoring Details (Plan Hash Value=2403765415)
======================================================================================================================================================
| Id |             Operation             | Name |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity | Activity Detail |
|    |                                   |      | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |   (# samples)   |
======================================================================================================================================================
|  0 | INSERT STATEMENT                  |      |         |      |         1 |     +0 |     1 |        0 |      |       |          |                 |
|  1 |   LOAD TABLE CONVENTIONAL         | T    |         |      |         1 |     +0 |     1 |        0 |    4 | 32768 |          |                 |
|  2 |    OPTIMIZER STATISTICS GATHERING |      |       1 |    2 |         1 |     +0 |     1 |       10 |      |       |          |                 |
|  3 |     VIEW                          |      |       1 |    2 |         1 |     +0 |     1 |       10 |      |       |          |                 |
|  4 |      CONNECT BY WITHOUT FILTERING |      |         |      |         1 |     +0 |     1 |       10 |      |       |          |                 |
|  5 |       FAST DUAL                   |      |       1 |    2 |         1 |     +0 |     1 |        1 |      |       |          |                 |
======================================================================================================================================================

insert /*+ monitor no_gather_optimizer_statistics */ into t
with rws as (
  select level x from dual
  connect by level <= 10
)
  select * from rws;

select dbms_sqltune.report_sql_monitor (report_level=> 'basic +plan')
from   dual;

SQL Plan Monitoring Details (Plan Hash Value=2403765415)
======================================================================================================================================
| Id |            Operation             | Name |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Activity | Activity Detail |
|    |                                  |      | (Estim) |      | Active(s) | Active |       | (Actual) |   (%)    |   (# samples)   |
======================================================================================================================================
|  0 | INSERT STATEMENT                 |      |         |      |         1 |     +0 |     1 |        0 |          |                 |
|  1 |   LOAD TABLE CONVENTIONAL        | T    |         |      |         1 |     +0 |     1 |        0 |          |                 |
|  2 |    VIEW                          |      |       1 |    2 |         1 |     +0 |     1 |       10 |          |                 |
|  3 |     CONNECT BY WITHOUT FILTERING |      |         |      |         1 |     +0 |     1 |       10 |          |                 |
|  4 |      FAST DUAL                   |      |       1 |    2 |         1 |     +0 |     1 |        1 |          |                 |
======================================================================================================================================

exec dbms_stats.lock_table_stats ( user, 'T' );

insert /*+ monitor */into t
with rws as (
  select level x from dual
  connect by level <= 10
)
  select * from rws;

select dbms_sqltune.report_sql_monitor (report_level=> 'basic +plan')
from   dual;

SQL Plan Monitoring Details (Plan Hash Value=2403765415)
======================================================================================================================================
| Id |            Operation             | Name |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Activity | Activity Detail |
|    |                                  |      | (Estim) |      | Active(s) | Active |       | (Actual) |   (%)    |   (# samples)   |
======================================================================================================================================
|  0 | INSERT STATEMENT                 |      |         |      |         1 |     +0 |     1 |        0 |          |                 |
|  1 |   LOAD TABLE CONVENTIONAL        | T    |         |      |         1 |     +0 |     1 |        0 |          |                 |
|  2 |    VIEW                          |      |       1 |    2 |         1 |     +0 |     1 |       10 |          |                 |
|  3 |     CONNECT BY WITHOUT FILTERING |      |         |      |         1 |     +0 |     1 |       10 |          |                 |
|  4 |      FAST DUAL                   |      |       1 |    2 |         1 |     +0 |     1 |        1 |          |                 |
======================================================================================================================================

  as we know getting real time statistics for any dml operation thats
  occuring for more that 100k rows is very costly operation

How do you know this?


Is Galera Arbitrator really required? I have three nodes(as recommended, in different switches) forming a Galera Cluster. Is there any real requirement of 'Garbd'? I don't see much reasons for using this apart from avoiding split-brain or taking backups?

Another doubt regarding garbd is that suppose I have 3 nodes in the cluster, and adding a garbd node will make the cluster size of 4, which is an even-numbered quorum. Will this not cause a split-brain situation?

If you have minimum of three active nodes in your cluster, you don't need to add garbd.

You should use a garbd with the following conditions:

When you have an even number of nodes and you can't add new nodes (because of cost or ...) to avoid split-brain situations.
when you want to backup data (SST method) to a server.

More information:
http://galeracluster.com/documentation-webpages/arbitrator.html


A workaround for restoring HierarchyId in SQL server 2014 After installing VS 2015 CTP 4, HierarchyID stopped working in SQL Server 2014. This is the bug listed here.
I am looking for a workaround.
To that end, I have tried reinstalling everything associated with SQL Server 2014 and Visual Studio 2015 CTP.
Additionally, I noticed the following (before the reinstall):

After re-installing, I now have version 12.0.2000.8 installed, and its 6.80 MB.
Additionally, I know that I have a working version of the SQL Server type assembly as I can run the following C# code without error:
        var root = SqlHierarchyId.Parse("/");
        var parent = SqlHierarchyId.Parse("/1/");
        var child = SqlHierarchyId.Parse("/1/1/");
        Console.WriteLine(child.ToString());

While in SQL, the following errors:
    declare @Root hierarchyid = '/',
    @Parent hierarchyid = '/1/',
    @Child hierarchyid = '/1/1/';

    print @Root.ToString();
    print @Parent.ToString();
    print @Child.ToString(); -- Throws exception

Is there a way to force SQL Server 2014 to use a particular assembly for this type? Additionally, if I could track down the actual source of the file that is the source of the error I would have a chance at restoring that file/set of files. Is there a way to get the error message to show the file that it originated from?

I am looking for a workaround.

For me, based on conversations I've seen about this elsewhere, you have four options:

Perform a system restore to a point prior to the Visual Studio CTP installation.
Wait for Connect item #1063935 to be addressed - it may be in a future Cumulative Update or Service Pack for SQL Server, or it may be in a patch or some kind of update for the Visual Studio preview. It is not addressed explicitly in Cumulative Update #6, but applying that can't hurt to try.
Back up your databases somewhere, and remove everything Visual Studio, .net & SQL Server related (the hard way, not through the Control Panel), and install SQL Server again.
If 3. doesn't work, back up your databases somewhere, and nuke & pave your OS.

If 1. is not possible, then you need to take further action in the future to prevent having to do these other much harder things:

Either don't install Visual Studio, SQL Server etc. CTP or preview versions on your critical / primary workstation (virtual machines are a much better target for this sort of thing); or,
start taking better system backups so that if you do decide to install such things you can recover.

This can be an expensive lesson that, sadly, most people learn the hard way.


Generally speaking you can use COALESCE

SELECT *
FROM TABLE
WHERE COALESCE(date_active,date_created) = 'value'

However this is not sargable. Depending on indexes available you may find the following

SELECT *
FROM TABLE
WHERE date_active = 'value' OR
      (date_active IS NULL AND date_created = 'value')

Or even

SELECT *
FROM TABLE
WHERE date_active = 'value'
UNION ALL
SELECT *
FROM TABLE
WHERE  (date_active IS NULL AND date_created = 'value')

Performs better


Recovering a dropped column? I have a PostgreSQL table:

CREATE TABLE essays (
    id serial primary key,
    content text
);

I inserted a few rows, then ran:

ALTER TABLE essays DROP COLUMN content;

How can I recover the data? I'm willing to use any means.

You may use the pageinspect module to get at the raw pages. The column contents are not blanked by the DROP COLUMN, they're still there.

For each data page, select * from heap_page_items(get_raw_page('tablename', pageno)) will give the control information for each row, especially the start offset of the row.

To understand the structure of each row, you want to study the Database Page Layout in the doc. Offsets of columns are not found on disk, you get at a specific column by skipping over the columns before it. If there is only one column with a fixed size like in your case, it's not too complicated to retrieve contents page by page, row by row.

Now if the table had UPDATEs or DELETEs, the pages will contain a mix of live and old contents, also called dead rows.

And if some of the text contents were large enough to be TOASTed (about 2000 bytes), which means stored indirectly out of the main page in a compressed form, they're going to be visually unreadable and quite harder to extract. That part seems unfeasible to me without writing some C code interfaced with PostgreSQL.


PostgreSQL Hot Standby I have two PostgreSQL servers running on my local Ubuntu machine (port 5437 is master, port 5438 is standby).

While following the tutorial on
http://wiki.postgresql.org/wiki/Hot_Standby

I get this in my log2:

LOG:  database system was interrupted; last known up at 2013-02-23 20:48:03 PST
LOG:  creating missing WAL directory "pg_xlog/archive_status"
LOG:  entering standby mode
LOG:  restored log file "000000010000000000000002" from archive
LOG:  redo starts at 0/2000020
LOG:  consistent recovery state reached at 0/20000C4
LOG:  database system is ready to accept read only connections
cp: cannot stat `/home/ga/p1/000000010000000000000003': No such file or directory
cp: cannot stat `/home/ga/p1/000000010000000000000003': No such file or directory

and the last line repeats indefinitely ...

The file is missing. With WAL, when would the new file be created?

It sounds like PostgreSQL is set to recover from log shipping rather than by connecting as a replication user.  Please double and triple check your recovery.conf and if that doesn't work, then post it here.

The approach you are taking is a valid approach though, and it means that the recovery will just wait for the next segment until it arrives creating the message you are seeing, but it must be transferred using whatever recovery command you have configured in the master's postgresql.conf.


How do I add Median to this query? I would like to add median to the following query, the median obviously will for each type of bedroom, unittypeid (already in the group by), I tried working with PERCENTILE_CONT but I couldnt figure out how to make it work.
CREATE TABLE #TempListings
(
    ListingId int,
    Price money,
    UnitTypeId int,
    BedroomsAvailable int
)
INSERT INTO #TempListings VALUES(1, 1000, 1, 1)
INSERT INTO #TempListings VALUES(2, 2000, 1, 1)
INSERT INTO #TempListings VALUES(3, 3000, 1, 1)

INSERT INTO #TempListings VALUES(4, 1000, 1, 2)
INSERT INTO #TempListings VALUES(5, 2000, 1, 2)
INSERT INTO #TempListings VALUES(6, 3000, 1, 2)

INSERT INTO #TempListings VALUES(7, 1000, 2, 1)
INSERT INTO #TempListings VALUES(8, 2000, 2, 1)
INSERT INTO #TempListings VALUES(9, 3000, 2, 1)

INSERT INTO #TempListings VALUES(10, 1000, 2, 2)
INSERT INTO #TempListings VALUES(11, 2000, 2, 2)
INSERT INTO #TempListings VALUES(12, 3000, 2, 2)

SELECT BedroomsAvailable,
    COUNT(listingid) AS Count,
    MIN(price) AS MinPrice,
    MAX(price) AS MaxPrice,
    AVG(price) AS AveragePrice,
    STDEV(price) as StandardDeviation,
    UnitTypeId
FROM #TempListings
GROUP BY BedroomsAvailable, UnitTypeId
ORDER BY UnitTypeId

DROP TABLE #TempListings

I am using sqlserver 2019 if it matters

You wanted inly explained how the syntax works.
But as complete Query

CREATE TABLE #TempListings
(
    ListingId int,
    Price money,
    UnitTypeId int,
    BedroomsAvailable int
)
INSERT INTO #TempListings VALUES(1, 1000, 1, 1)
INSERT INTO #TempListings VALUES(2, 2000, 1, 1)
INSERT INTO #TempListings VALUES(3, 3000, 1, 1)

INSERT INTO #TempListings VALUES(4, 1000, 1, 2)
INSERT INTO #TempListings VALUES(5, 2000, 1, 2)
INSERT INTO #TempListings VALUES(6, 3000, 1, 2)

INSERT INTO #TempListings VALUES(7, 1000, 2, 1)
INSERT INTO #TempListings VALUES(8, 2000, 2, 1)
INSERT INTO #TempListings VALUES(9, 3000, 2, 1)

INSERT INTO #TempListings VALUES(10, 1000, 2, 2)
INSERT INTO #TempListings VALUES(11, 2000, 2, 2)
INSERT INTO #TempListings VALUES(12, 3000, 2, 2)

INSERT INTO #TempListings VALUES(13, 5000, 2, 1)
INSERT INTO #TempListings VALUES(14, 6000, 2, 1)
INSERT INTO #TempListings VALUES(15, 7000, 2, 1)

INSERT INTO #TempListings VALUES(16, 8000, 2, 2)
INSERT INTO #TempListings VALUES(17, 9000, 2, 2)
INSERT INTO #TempListings VALUES(18, 10000, 2, 2)
GO

WITH #myselect
 AS (SELECT
         BedroomsAvailable
         ,ListingId
         ,UnitTypeId
         ,Price,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Price)
         OVER (PARTITION BY UnitTypeId) AS MedianCont
    FROM #TempListings)
SELECT BedroomsAvailable
    ,COUNT(listingid) AS Count
    ,MIN(price) AS MinPrice
    ,MAX(price) AS MaxPrice
    ,AVG(price) AS AveragePrice
    ,STDEV(price) as StandardDeviation
    , MIN(MedianCont) MedianCont
    ,UnitTypeId
FROM #myselect
GROUP BY BedroomsAvailable, UnitTypeId
ORDER BY UnitTypeId
GO

BedroomsAvailable | Count |  MinPrice |   MaxPrice | AveragePrice | StandardDeviation | MedianCont | UnitTypeId
----------------: | ----: | --------: | ---------: | -----------: | ----------------: | ---------: | ---------:
                1 |     3 | 1000.0000 |  3000.0000 |    2000.0000 |              1000 |       2000 |          1
                2 |     3 | 1000.0000 |  3000.0000 |    2000.0000 |              1000 |       2000 |          1
                1 |     6 | 1000.0000 |  7000.0000 |    4000.0000 |  2366.43191323985 |       4000 |          2
                2 |     6 | 1000.0000 | 10000.0000 |    5500.0000 |  3937.00393700591 |       4000 |          2

db<>fiddle here


What's the difference between DISTINCT_SCAN and IXSCAN in explain output? In Mongo 3.4 explain("executionStats").distinct(...), for some queries
the winning plan contains an IXSCAN step, and for other queries it contains an DISTINCT_SCAN step.

What is the difference between the two? Is one of them faster than the other?

They are different stages for different purposes. I believe DISTINCT_SCAN is only present when there is an index in the field. An IXSCAN stage basically means that the query planner scans an index as one of the stages.

For example:

> db.test.createIndex({a:1})
{
  "createdCollectionAutomatically": true,
  "numIndexesBefore": 1,
  "numIndexesAfter": 2,
  "ok": 1
}

Doing find() on an indexed field will trigger the presence of an IXSCAN stage:

> db.test.explain().find({a:1})
{
  ...
    "winningPlan": {
      "stage": "FETCH",
      "inputStage": {
        "stage": "IXSCAN",
        "keyPattern": {
          "a": 1
        },
        "indexName": "a_1",
        ...

While doing distinct() on the indexed field will trigger the DISTINCT_SCAN stage:

> db.test.explain().distinct('a')
{
  ...
    "winningPlan": {
      "stage": "PROJECTION",
      "transformBy": {
        "_id": 0,
        "a": 1
      },
      "inputStage": {
        "stage": "DISTINCT_SCAN",
        "keyPattern": {
          "a": 1
        },
        "indexName": "a_1",
        ...

Note that in both cases, the index {a: 1} is used. Without the presence of the index, you will see a COLLSCAN stage instead.

One is not "faster" than the other. They're different stages serving different purposes.


Windows Authentication fails with "Cannot generate SSPI context" We have a Windows Server 2012 with SQL Server 2012 instance.

Its services used to run under an Active Directory account from an old domain, from Windows 2000 Server. A few years ago we replaced our AD to Windows Server 2012 and created a new domain.

Now we decided to replace the service account to the new domain. When that change was done, Windows Authentication failed to work. SQL Server shows message "Cannot generate SSPI context".

If I log in the server using MSTSC, Windows Authentication works. But if I try to log from my local PC, it fails.

I've read some support as https://support.microsoft.com/en-us/kb/811889, but it's kinda old and I'm unable to properly understand it.

Any idea what's causing this error, and how to solve it?

You can find a more information on Kerberos from the CSS team here. As well Microsoft released a tool that can help you identify Kerberos issues, download that here.

To troubleshoot, check your error log that shows the startup of your instance after you changed the service account to that new domain. In a perfect world you will see a message like this:

  The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) MSSQLSvc/<servername>

If it fails it will show a similar message just with a failure and should provide an error code. If not check your Windows Event log for similar events around startup time of SQL Server.

To fix the issue you need to follow the KB article you provided. It is not old and is still current, check the "Applies to" section where it list all the editions of SQL Server 2012.


Query best possible matches and order them I'm trying to write a query along these lines:

select *
from tbl
where
       col1 = 1
   and col2 = 2
   and col3 = 3
order by
   ...
;

I want first all the results where all 3 WHERE conditions match (3/3), then all the results where any 2 conditions match (2/3), and finally the results where any 1 condition matches (1/3).

Each of these 3 result sets needs to be ordered by (col4, col5, col6).

Can I do that in a single query?

For example:

sample http://img708.imageshack.us/img708/1646/sampletableresult1.jpg

Script to create test data:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') AND type in (N'U'))
    DROP TABLE [dbo].[MyTable]
GO

CREATE TABLE dbo.MyTable
(
    col1 INT
    , col2 INT
    , col3 INT
    , col4 INT
    , col5 INT
    , col6 INT
)
GO

INSERT dbo.MyTable (col1, col2, col3, col4, col5, col6)
SELECT 1,2,3,2,1,1 UNION ALL
SELECT 1,2,30,1,1,1 UNION ALL SELECT 1,20,30,1,1,1 UNION ALL
SELECT 10,20,3,1,1,1 UNION ALL SELECT 10,2,30,1,1,1 UNION ALL
SELECT 10,2,3,1,1,1 UNION ALL SELECT 10,20,30,1,1,1 UNION ALL
SELECT 1,2,3,1,1,1 UNION ALL SELECT 1,2,3,1,2,2 UNION ALL
SELECT 1,2,3,1,2,3 UNION ALL SELECT 1,20,3,1,1,1
GO

SELECT col1,
       col2,
       col3,
       col4,
       col5,
       col6
FROM TableX
WHERE col1 = 1
   OR col2 = 2
   OR col3 = 3
ORDER BY (CASE WHEN col1 = 1 THEN 1 ELSE 0 END) +
         (CASE WHEN col2 = 2 THEN 1 ELSE 0 END) +
         (CASE WHEN col3 = 3 THEN 1 ELSE 0 END) DESC,
         col4, col5, col6

or, for MS-Access:

ORDER BY IIF(col1 = 1,1,0) +
         IIF(col2 = 2,1,0) +
         IIF(col3 = 3,1,0) DESC,
         col4, col5, col6


Modify DEFINER on Many Views I have am having problems backing up my databases after an update.  I have been poking around on my system trying to figure out why.  One query I ran returned this result.

Got error: 1449: The user specified as a definer ('cittool'@'%') does not exist when using LOCK TABLES

After some investigation it appears that the definer for these views is an old developer account that has been purged from the system.  The databases and views with this problem are used very infrequently, and most being kept around for archival purposes.

There is about 40 views with a definer that no longer exists.  Is there an easy way to change the definer to a different account on everything at once?  Is there a way to get mysqldump to simply dump all the views out to a file so I could edit that file and recreate the views?

You can use ALTER VIEW in conjunction with the information schema. You mentioned dumping it out to a text file, so perhaps something like this:

SELECT CONCAT("ALTER DEFINER=`youruser`@`host` VIEW `",table_name,"` AS ", view_definition,";")
FROM information_schema.views WHERE table_schema='databasename'

Mix this with the mysql command line (assuming *nix, not familiar with windows):

> echo "*abovequery*" | mysql -uuser -p > alterView.sql
> mysql -uuser -p databasename < alterView.sql

Sidenote: You can't alter the information_schema entries directly.
Note2: This works for only one database at a time, if you leave off WHERE table_schema you need to insert USE commands between each.


Need some expert eyes to look at my schema I am trying to make an online site for local movies. Will be written in PHP and will be using MySQL as the database.

What I want is to create a mini IMDB type site, with movies, people(casts/crew), and show which movies are showing at theaters. (Genres not required).

Have learned from experience that getting the database schema wrong can cause a lot of headache and time later on, especially when it comes to complex queries. So, would appreciate if I could get some advice from DBA experts.

So far, this is what I have come up with.

Database Schema

movies:

id | title | synopsis | release_date | run_time

people:

id | first_name | middle_name | last_name | biography | dob | gender

movies_people:

id | type (cast/crew) | movie_id | people_id | role | lead_role (yes/no) | order

trailers:

id | movie_id | desc | src | src_type | order

images:

id | type | resource_id | src | desc

theaters:

id | name | location | longitude | latitude | city | phone | email | website

movies_showing:

id | movie_id | theater_id | start_date | end_date | description (time, price, etc.)

Example Query: Retrieve all currently showing movies along with all the theaters they are showing at.

I have the following steps in mind.

Get the DISTINCT IDs of all the movies from the movies_showing table as per a date constraint(Today, This week).
Loop through the result, and inside the loop do a query again on the movies_showing table where the movie_id is the ID in the loop, to get all the theaters for that movie.

But this would result in a lot of queries hitting the DB.

Good start so far. Here are some notes:

a movie can be released on different dates in different cities
please add your primary keys, so we can see them
are images for movies? people? why no order column?
movies schedules are more complex than you've got so far. You will want to calculate show times based on some recurrence rules (see stackoverflow for recurrence posts)
what is 'location' in theatres? the address?

Regarding your questions at the bottom, don't loop. You can almost always do everything using SQL and its set logic. Make sure you understand joins:

http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/


SQL Server 2012 - Multiple named Instances with Always On I have two named SQL Server 2012 Standalone Instances on each server. I would like to know what the best way to set up Always On .

INST1(Principal) on SERVER1 --> Replica on INST2

INST2(Principal) on SERVER2 --> Replica on INST1

OR

INST1(Principal) on SERVER1 --> Replica on INST2

INST2(Principal) on SERVER1 --> Replica on INST2

Thank you very much for your time and I really appreciate it!!!

It doesn't really matter which one you pick. If it were my servers though, I would use the same instance names on each server. So INST1 would appear on SERVER1 and SERVER2, and then you'd AG INST1 with INST1.

But I don't like stacking instances on a server as it makes it hard to troubleshoot and do maintenance. If these are virtual machines, setup another virtual machine to host the other instance, or in your case setup two more virtual machines.


How to make sure a record is only retrieved once If I have a table called Coupons that contains 10,000 records. How should I design the schema or what query should I use so that my API never gets the same record twice. If I simply ask the DB for last record I think it's possible another instance could ask the DB at the same time for that record.

Should I look into record locking ? Are there any performance implications with this? The table is going to be accessed a lot. Since there are 10,000 records it's ok if some records are locked since the DB can return a record that isn't locked.

Coupon table so far is just

id [int]
coupon [varchar]
project [int](fk to projects table)

Mysql 5.6

Edit: I am just serving coupons. As soon as I pull from DB it is considered used to me. I don't have an ID to look up either. I want to literally recreate that ticket machine at the DMV that puts people in order of who to be served. Once a number is pulled no one else will get that number. I'm concerned if code tries to SELECT last coupon simultaneously and gives out same number twice.

I'm assuming this is the following case:

You can look up coupon multiple times (example: verifying the coupon exist)
Once you use the coupon something the coupon becomes invalid

Easiest way is to have two fields called isUsed int, ExpirationDate datetime
Then create two APIs that do the following:

CheckCoupon (coupon id)

This checks against the table for coupon id, ExpirationDate, isUsed. If the stored procedure returns 1, the coupon is valid, else invalid.

Create a stored procedure called usp_check_coupon

SELECT count(*)
FROM Coupon
Where id = @id
and ExpirationDate > [today's_date]
and IsUsed = 0

UseCoupon (coupon id)

Marks the coupon as used. Before updating IsUsed to 1, check to make sure IsUsed is 0.

Create a stored procedure called usp_use_coupon

DECLARE @isused int =
    SELECT count(*)
    FROM Coupon
    Where id = @id
    and ExpirationDate > [today's_date]
    and IsUsed = 0

IF (@isused == 1)
BEGIN
    UPDATE COUPON
    SET IsUsed = 1
    WHERE ID = @id

   SELECT 1 --Updated Successfully
END

SELECT 0 --Updated Failed

Then on the API, if usp_use_coupon returns 0, that means the coupon is used or expired. If 1, it has been processed.


How to reduce size of a large SQLite database with series data I'm considering to use SQLite database for a C# application which deals with large volumes of data series. The data is currently in several CSV files of up to 20GBs in size each and of the following format:

2019.07.31 00:00:03.855,1.11568,1.11571,3,0

I was expecting to reduce the size considerably when migrating to SQLite but to some reason am getting some different results.

The example string in CSV format takes 44 bytes (43 chars + new line). As far as I understood the SQLite types definitions described at sqlite.org it should take about the same in the database:
23 byte datetime text + 8*2 reals + 1*2 integers = 41 bytes

The dateTime can be stored as 8-byte integer (c# DateTime.Ticks) which should reduce the size to about 26 bytes per one line which is almost twice less than in CSV format.

For an experiment I tried importing 142,157 lines of data. The CSV file is about 6kk bytes in size but the resulting DB file is about 5kk which is almost the same. Compression (available in DB Browser for SQLite) does not change anything.

The table schema is:

CREATE TABLE "Data" ( "dateTime" INTEGER, "value1" REAL, "value2" REAL, "value3" INTEGER, "value4" INTEGER )

An example row looks like this:

637001280038550000 1.11568 1.11571 3   0

Is it the SQLite internals which make that big overhead in size?
How can I reduce the size?

As a side note, setting Primary Key on the dateTime field reduces the size of the example data file by 0.3kk bytes but adding unique constraint increases it by 3kk bytes making it about 8kk in total which is much bigger than the CSV one.

Update

output from sqlite3_analyzer

w/t PK and contraints
Size of the file in bytes......................... 5242880
Bytes of user payload stored...................... 4349676     83.0%
Bytes of payload.................................. 4349852     83.0%
Bytes of metadata................................. 858115      16.4%

with PK
Size of the file in bytes......................... 4976640
Bytes of user payload stored...................... 3212420     64.5%
Bytes of payload.................................. 3212622     64.6%
Bytes of metadata................................. 1733838     34.8%

with PK and Unique constraint
Size of the file in bytes......................... 8134656
Bytes of user payload stored...................... 3212420     39.5%
Bytes of payload.................................. 3212622     64.6%
Bytes of metadata................................. 1733838     34.8%
*** Page counts for all tables and indices separately
TABLE............................................. 1214        61.1%
SQLITE_AUTOINDEX_TABLE_1.......................... 771         38.8%

So it turns out that the internals take 16%, PK takes additional 19% but still reduces the total size, and the unique contraint takes almost 40%!

The question remains valid. Is it possible to recude the size of the database or that is all one can get from it?

There are some ways. Here are two that seem appropriate for your case:
WITHOUT ROWID tables
The ROWID is an internal primary key that SQLite tables have by default, taking 64 bit per record. Since you were talking about a UNIQUE constraint for the datetime column, this column seems a good candidate for the primary key (which always has a unique constraint attached). So you can create a SQLite table without ROWID, as follows:
CREATE TABLE "Data" (
    "dateTime" INTEGER PRIMARY KEY,
    "value1" REAL,
    "value2" REAL,
    "value3" INTEGER,
    "value4" INTEGER
) WITHOUT ROWID;

Storing delta values
I have never tried this in SQLite in practice, but: some data is more compactly stored by storing only the difference to the previous row. In this case, the datetime values are great for this, if they can be stored in order into the database. In that case, you would use three columns:

A sequentially numbered integer primary key.
A datetime integer column, only filled for every (say) 1000 records.
A datetime_delta column, storing the difference from the last row with a filled datetime column.

SQLite stores integer values individually into 2-9 bytes (incl. one storage mode byte), depending on their value. Assuming an average of 4 bytes for the sequential primary key and an average of 2.5 bytes for the delta values would save 2.5 bytes compared to the current 9 byte integer. Savings are larger if you have multiple columns in your table that profit from delta storage, as you need the sequential primary key only once. For example, if value1 … value4 are measurement values that tend to grow and shrink over time, they are also good candidates.
After loading a row at primary key value n, you would also have to retrieve the row at primary key value n DIV 1000 (meaning integer division) to have the base for your delta values.


Catalogue versioning Apologies if this is not the right SE site to ask.

I'm designing an application for catalogue management.
Any element in the hierarchy has to be versioned, i.e. when I change a property of it, a new version is created. The new version can also have different children than the previous. The user can switch between versions when she manages the catalogue, so I'll need to list all the existing versions of a child. When switching version, all the descendant will be updated).

My first thought was something like

| A            |  | B            |  | C            |
|--------------|  |--------------|  |--------------|
| ID  (k)      |  | ID  (k)      |  | ID  (k)      |
| VER (k)      |  | VER (k)      |  | VER (k)      |
| ...          |  | A_ID (fk)    |  | B_ID (fk)    |
|              |  | A_VER (fk)   |  | B_VER (fk)   |
|--------------|  | ...          |  | ...          |
                  |--------------|  |--------------|

Where the entities' key is always something like (ID, VER) and the parent's fk is (P_ID, P_VER).

Is this a good way to implement it?

Update: It's apparently more complicate than that. The meaning of the versioning is that elements have a validity time and we need to keep the older version. E.g. a client buys product with price x. After 1 January it's gonna cost y.
New clients after 1 January get new price y, but old client keeps paying x.

Adding a timestamp column is probably the best option here.

To be quite honest your requirements sound like a cross between a nightmare and a Version Control System.  I would highly recommend looking at popular solutions like Git instead of reimplementing a DVCS with a relational database.  Your application could effectively be a nice skin around Git.  If you have to do this via a relational database then I think versioning by time stamps is going to be your best chance of success.

The basic strategy is to put a time stamp on every entity.  Then when you need to go to the most recent version you select all the entities through the relational chain with the most recent time stamp.  When you need to get a specific version you grab all the most recent versions of the entities that are at or older than the version time stamp you want.

Versioning by time stamp prevents you from copying all the older entities to a new version id even if those entities haven't changed.  It also saves you the madness of keeping track of those version ids.

This might lead to slow query times but you can play with it to see if it suits your needs.  To reiterate I don't think a relational database is the best solution, but sometimes life forces us to put the square peg in the round hole.  If that is your lot in life I wish you the best.

Edit: Versioning is related to historical pricing

I agree that time stamps are the way to go given that versioning is related to historical pricing.  To handle that you just need an Effective Start Date column.  To find the rate at any point in time you just need to find the most recent date that isn't in the future.  For example:

declare @Date DATE = SYSUTCDATETIME(); -- Or any other date for historical/future data
;with [CurrentPrice_CTE] AS -- You don't really need this semi-colon but I added it as a defensive habit.
(
    select
      [PriceId] -- Price table's PK
      max([EffectiveStartDate]) AS [EffectiveStartDate]
    from [dbo].[Price]
    where
      [EffectiveStartDate] <= @Date
)
select
  [p].* -- Select the columns you need
from [dbo].[prices] [p]
    inner join [CurrentPrice_CTE] [cp] as [cp].[PriceId] = [p].[PriceId] and [cp].[EffectiveStartDate] = [p].[EffectiveStartDate]

Just make sure you make the start date unique for each product or that code will return more results than you want.  To be totally honest this sounds like a much less complicated problem than having to reversion the entire database every update.  :)


Would using timestamps solve consistency issues with snapshot isolation? I'm considering using Snapshot Isolation for some large queries being send from an Access 2007 frontend. These queries are occasionally causing ASYNC_NETWORK_IO waits, and some blocking as well. I'm working to try to solve this with application design, but since I can't control how the Jet engine loads data from the server, I haven't been able to completely eliminate the problem.
I know that one major concern with using snapshot isolation is the problem of updating dirty records, such as expressed in this blog post.
In my database, all of the tables have timestamp fields, which I would think would prevent the problem of updating dirty records. It might cause some update conflicts, but that seems like an acceptable outcome, while getting incorrect data is not. Am I right that having timestamps is a solution for potential data integrity problems, or could I still run into trouble?
EDIT: Max Vernon asked for an example. In the article I linked to, Craig Freedman uses this as an example:

We can demonstrate this outcome using SQL Server.  Note that snapshot
isolation is only available in SQL Server 2005 and must be explicitly
enabled on your database:

alter database database_name set allow_snapshot_isolation on

Begin by creating a simple table with two rows representing two
marbles:

create table marbles (id int primary key, color char(5)) insert
marbles values(1, 'Black') insert marbles values(2, 'White')

Next, in session 1 begin a snaphot transaction:

set transaction isolation level snapshot begin tran update marbles set
color = 'White' where color = 'Black'

Now, before committing the changes, run the following in session 2:

set transaction isolation level snapshot begin tran update marbles set
color = 'Black' where color = 'White' commit tran

Finally, commit the transaction in session 1 and check the data in the
table:

commit tran select * from marbles

Here are the results:

id          color
1           White
2           Black

This is an issue without using any sort of rowversioning. My question is, do timestamps solve this problem? Do they leave similar issues outstanding? Or does SQL Server's rowversioning when you enable Snapshot Isolation solve the problem and make timestamps unnecessary? I'm assuming it doesn't, based on Freedman's example.

I would suggest turning on READ_COMMITTED_SNAPSHOT at the database level - that will get rid of a large amount of the concurrency issues you are seeing with the Access front end.

Having TIMESTAMP fields in the tables that you are having issues with will help Access, but not with the ASYNC_NETWORK_IO waits.  Those waits are showing up from the various combo boxes and list boxes you have in the front-end.  Access does not request the entire dataset for those items until the combo-box or list-box etc actually needs all the data.

TIMESTAMP fields will be used by Access to determine if a record has been updated by some other process between the time Access fetched the record, and the time it (or the user) attempted to update the record.  If there is no TIMESTAMP field, Access must inspect all the fields in the dataset prior to running the update statement to see if anything has changed.


Proper remote access control management in PostgreSQL I want to create environment that allows the limited number of machines connect to my database server.
For debugging needs all IP address have the remote access to PostgreSQL server

listen_addresses = '*'
host all all 0.0.0.0/0 md5

I want to limit access. What is the canonical way to do that?
I though of using proxy server to connect from local machines to PostgreSQL server, but I'll appreciate other ideas.

Just change pg_hba.conf from

host all all 0.0.0.0/0 md5

to rules you need. Columns are :
type (leave host),
database,
username,
IP,
access method.
For example, if you need to allow only 1.2.3.4 and 5.6.7.8 to connect any database using any username:

host all all 1.2.3.4/32 md5
host all all 5.6.7.8/32 md5
host all all 0.0.0.0/0 reject

Use
    service postgresql reload
(or smt like that, depending on your system) to apply changes.


IF condition on temporary table takes long time for query execution SELECT actualTable.column1, actualTable.column2, actualTable.column3
FROM table1 actualTable LEFT JOIN (
    SELECT column1, column2
    FROM table2
    GROUP BY column1
    ORDERBY ASC
) temp
WHERE IF(temp.column1 IS NOT NULL, temp.column1, actualTable.column1)
AND IF(temp.column2 IS NOT NULL, temp.column2, actualTable.column2)
GROUP BY actualTable.column1;

Above query is for reference. I have used 6 tables and added necessary joins.
The problem is that IF condition on Temporary table in Where cluase takes time. If i remove it then it takes less time to execute.

It is improper to "group by col1" but fetch col2 unless they col2 is either directly derivable from col1 or is an "aggregate".

The ORDER BY in the derived table will be ignored (once the two syntax errors are fixed).  Why do you have it?

table2, as the query stands, needs INDEX(col1, col2).

Break the query up into two in order to avoid the IF clauses.  Something like

SELECT ... JOIN ...
UNION
SELECT ... LEFT JOIN WHERE temp.col1 IS NULL AND ...

That will need parens around it before doing the outer GROUP BY.


AlwaysOn AG: Current Host Server - Owner Node - Primary Instance I'm building up a SQL Server AlwaysOn AG and I'm on the step "failover", manual and automatic:

I noticed that in the Windows Server Failover Cluster a "Current Host Server" exists. My knowledge says that this is the "master" of the cluster that the cluster hosts. But now I noticed that the "Owner Node", which hosts the Availability Group(-role) a other node could be. (The owner node is also the primary instance of the AG, right?)

Now for me there exist two questions for the normal operating of an AlwaysOn AG:

Q1: Should the "Current Host Server" the same as the Owner Node of the AlwaysOn AG?

Q2: Imagine if I want to maintain a host. Shall I first failover the AG (via SSMS or with ALTER AVAILABILITY GROUP...) to the second node/another node and at second a failover of the WSFC-Current Host?

Maybe someone could help me with this questions and give me some advice.

Thanks

Should the "Current Host Server" the same as the Owner Node of the AlwaysOn AG?

That's the node that is currently hosting the  core cluster resources, it isn't the same and is just another role. Different roles can be owned by different nodes. In this case, they do not need to be the same - and generally most people don't care who owns the core cluster resources in a WSFC that only has SQL Server. It does make a difference in certain situations but for the vast majority, it makes no real difference.

  Imagine if I want to maintain a host. Shall I first failover the AG (via SSMS or with ALTER AVAILABILITY GROUP...) to the second node/another node and at second a failover of the WSFC-Current Host?

If you're going to be maintenance to a host, I'd move all roles (drain) off of that node. So, yes, everything should be moved from it.


MySQL Error 1064 (42000). Insert failure on MySQL table i am making chatting software in which whenever a new friend is made the details are added in current users friend table. similar insert queries are working for other purposes but when used for this it shows syntax error.
so please help me out. also do tell me while showing error it shows whatsapp_friends as table name which is my other table but not 9636354343_whatsapp_friends.

INSERT INTO 9636354343_ whatsapp_friends  (friend_no ,friend_name )
VALUES (1111111111,'gurmeet');

Please check here to see why posting images of text is not a good idea (can't cut and paste!).
Always put the error code in your question's Title. Easier to search, easier to find!
Also, use the "Code sample" tool for code, not the "Block Quote".
Finally, and to answer your question, your SQL appears to have an error - the name of the table you are querying is "9636354343_ whatsapp_friends" and not "9636354343_whatsapp_friends" - i.e. your code as posted has a space between the first underscore and the "whatsapp" part.  Always give a SHOW CREATE TABLE my_table\G (as text) with table definitions.

In MySQL, you can have 64 characters in identifiers! That_is_more_than_enough_dont_you_think? (=40 characters!). I know what you did was an error, but MySQL (and other servers) do allow you to have spaces in identifiers, but putting the names in backticks (`) or double-quotes (") or whatever.

I cannot stress enough what madness this is - hic sunt leones!. Developers will always be asking "[Is that | Should that be] a space? "Is it a typo"? What happens if you accidentally put in two spaces - can be tricky to read and especially debug! Continue as you are doing, and use underscores to split identifiers to make them more readable!

Also, if you are in a multi-developer shop, pick a few conventions (you can Google best practices) and stick to them! Table names (singular or plural). Upper or Lower case - some servers enforce, others don't (with and/or without escaping).

I (for example, and this is just me) always use singular table names (with the obvious exception of order) and lower case for my identifiers, UPPER case FOR my SQL; - it makes it easy to pick out the language parts from your own objects.

Finally, no matter what you do, you should always use the comments facility available for tables and columns to let other developers know what the table/field is for and if they need to know anything special about the name, if it's not in accord with your convention!

p.s. welcome to the forum! p.p.s you can let people know you've replied to them by using @ in the text of your comment, i.e. for me, it's @Vérace!


Need to generate PK value programatically in multi-threaded application Suppose We create a table, with identity disabled:

CREATE TABLE TestIdentity (
    Id    int NOT NULL,
    abc   varchar(10) NULL,
    CONSTRAINT PK_ident_test PRIMARY KEY CLUSTERED (id)
);

Note : I have no rights to make Primary Key to identity-enabled, so I have to calculate next Id value from the application programme.

Suppose from multiple connections data is inserted in this table, Id field value is generated by C# application code, the problem is in multi-threaded application
multiple requests might generate the same id to insert row. In that case SQL server, PK constraint violates.

What is the best solution to reduce PK constraint violation?

You could try this technique will single thread the inserts into this table, but should guarantee uniqueness.  Your C# code would need to do the following in a transaction.  Select the current max Id and hold the lock so other users will wait.  Add +1 to the Id and insert that row.  Commit the transaction

BEGIN TRANSACTION
select @Id = max(Id) from TestIdentity WITH (TABLOCKX,HOLDLOCK)
set @Id += 1
insert into TestIdentity... new row...
COMMIT


DB2: The transaction log for the database is full, But there is no 'First active log file' First, I wish to say that I have very little experience with DB2. However, I've been tasked with tracking down the problem and solution to this problem.

This is a DB2 database, and our testers are saying that they get:

  The transaction log for the database is full.. SQLCODE=-964, SQLSTATE=57011, DRIVER=4.13.127 SQL Code: -964, SQL State: 57011

I did increase the number of LOGPRIMARY to '40'. However, it appears that the transaction logs are still full.  I then decided to 'prune' the transaction logs. The procedure I have is to Prune all prior to the active transaction log. However, I cannot determine the active log:

$ db2 get db config for MyDB | grep -i 'log'
 Log retain for recovery status                          = NO
 User exit for logging status                            = NO
 Catalog cache size (4KB)              (CATALOGCACHE_SZ) = 278
 Log buffer size (4KB)                        (LOGBUFSZ) = 1997
 Log file size (4KB)                         (LOGFILSIZ) = 1024
 Number of primary log files                (LOGPRIMARY) = 40
 Number of secondary log files               (LOGSECOND) = 12
 Changed path to log files                  (NEWLOGPATH) =
 Path to log files                                       = /home/db2/db2inst1/db2inst1/NODE0000/SQL00001/LOGSTREAM0000/
 Overflow log path                     (OVERFLOWLOGPATH) =
 Mirror log path                         (MIRRORLOGPATH) =
 First active log file                                   =
 Block log on disk full                (BLK_LOG_DSK_FUL) = NO
 Block non logged operations            (BLOCKNONLOGGED) = NO
 Percent max primary log space by transaction  (MAX_LOG) = 0
 Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0
 Percent log file reclaimed before soft chckpt (SOFTMAX) = 520
 HADR log write synchronization mode     (HADR_SYNCMODE) = NEARSYNC
 HADR spool log data limit (4KB)      (HADR_SPOOL_LIMIT) = 0
 HADR log replay delay (seconds)     (HADR_REPLAY_DELAY) = 0
 First log archive method                 (LOGARCHMETH1) = OFF
 Archive compression for logarchmeth1    (LOGARCHCOMPR1) = OFF
 Options for logarchmeth1                  (LOGARCHOPT1) =
 Second log archive method                (LOGARCHMETH2) = OFF
 Archive compression for logarchmeth2    (LOGARCHCOMPR2) = OFF
 Options for logarchmeth2                  (LOGARCHOPT2) =
 Failover log archive path                (FAILARCHPATH) =
 Number of log archive retries on error   (NUMARCHRETRY) = 5
 Log archive retry Delay (secs)         (ARCHRETRYDELAY) = 20
 Log pages during index build            (LOGINDEXBUILD) = OFF
 Log DDL Statements                      (LOG_DDL_STMTS) = NO
 Log Application Information             (LOG_APPL_INFO) = NO
$

I do note that the transaction log files do appear in /home/db2/db2inst1/db2inst1/NODE0000/SQL00001/LOGSTREAM0000/.

Is transaction logging correctly setup ?  Would increasing the logs be the answer?  Or should I push back on the users to encourage them to do more commits?

You really should be reading manuals, not "pruning logs". Your database is set up for circular logging, which means there is nothing to "prune". If transactions occasionally require extra log space, increase the value of LOGSECOND. Too many primary logs will slow down the database startup, as they are preallocated. Secondary logs are allocated as needed, they will remain in the active log directory until the database is restarted.

"Pruning logs" will not affect the amount of active log space (with one exception that I will mention later): it is dictated purely by the sum of LOGPRIMARY and LOGSECOND, multiplied by LOGFILSIZ. If you were using the archive log mode, you would be able to remove older archived log files to free up some disk space, but the amount of active log space would not change. Even then, you would delete logs created prior to the previous backup, not prior to the first active log file.

Only if your archived logs and active logs share the same file system (which is not a good idea in itself), the uncontrolled accumulation of archived logs may prevent the active log space to grow, when necessary, to its configured maximum size.


SQL Server Decimal(9, 0) vs INT One of our customers uses for some columns the datatype DECIMAL(18,0) in his SQL Server 2008R2 database. Because the columns grow quite slowly, he recently proposed to change the datatype to DECIMAL(5,0) to regain some storage.

According to the MSDN library, the storage space of the DECIMAL(5,0) datatype is, just like the DECIMAL(9,0) datatype, 5 bytes. INT is 1 byte smaller, but can store everything in the range of -2^31 to 2^31 instead of the -99,999 to 99,999 which DECIMAL(5,0) can store. Even the largest DECIMAL which fits into 5 bytes (DECIMAL(9,0)) can store only integers in the range -999,999,999 to 999,999,999 (which is less than half of the range INT offers in 4 bytes).

I can think of two "benefits" of using DECIMAL over INT:

The ability to add scale afterwards, without using more storage space
The ability to scale the precision up to 38 digits, without altering data type

but these aren't real benefits in my opinion:

Adding scale to integers does only make sense in very few cases (in most cases where scale does make a difference, it could also be added beforehand)
SQL Server sees every precision / scale combination as a different data type, so the datatype isn't left alone when increasing the precision or scale.

This makes me wonder: what is the added benefit of a DECIMAL(5,0) datatype for integers?

I agree that there are no real benefits in terms of storage space as long as you are comparing DECIMAL(9, 0) vs INT or DECIMAL(18, 0) vs BIGINT. (Within a single byte.)

In terms of processing, like @Andriy says the DECIMAL will naturally divide into a type that doesn't lose the fractional part, if that's important to you.

On the other hand, working with native INT types is much faster from a numerical standpoint if you are doing a lot of SUM()s or comparisons (such as searching on the values) as they are pipelined more efficiently by the CPU. An int comparison is two assembly opcodes (MOV, CMP) but any decimal comparison will be many, many more.


SSL errors with Couchdb on Ubuntu 14.04 I am beginning to set up CouchDB on an Ubuntu 14.04 Server (yes, it expires next year) and I'm having troubles setting up SSL connections. For the moment, I don't have any CA signed certs for it, so I've been following the steps on the CouchDB docs. To list my changes to the config file: I have essentially kept them as the default except I set the bind address to 0.0.0.0 to be accessible over the network, and I moved my database_dir and view_index_dir to another path. For the SSL part, I followed the documentation precisely step-by-step, and restarted the couch service. From localhost, I get the response I expect:

user@ubuntu:/etc/couchdb# curl -k https://127.0.0.1:6984/
{"couchdb":"Welcome","uuid":"43cf4aa1c38ef1feb9ec71ab93dddea3","version":"1.6.1","vendor":{"name":"Ubuntu","version":"14.04"}}

Now, when I try to access the server over the network (which right now is just me accessing a VM from the host, having tried a bridged and host-only adapter in VBox):

courtney@host:~$ curl -k https://192.168.10.38:6984/
curl: (35) OpenSSL SSL_connect: SSL_ERROR_SYSCALL in connection to 192.168.10.38:6984

At this point, I'm not sure what I'm missing. Is the documentation inaccurate? Or perhaps some packages I'm missing? I installed couch from the PPAs

Solved the issue. I'm not sure what the cause was, but this was an error that was specific to me using doing TLS and couch in a VM. I spun up a VPS and tried it there and the problem was completely gone.


Can a offline database be in use? I have a SQL Server 2014 DB where the log file filled up the disk. Since it was a test db, we decided to just delete the whole database. We took it offline, but when we try to drop it, we get a message that it cannot be dropped since it is in use.

How can it be in use when it is offline?
How can I get it dropped? (Preferable without taking it online.)

The problem was that the person who set the database to OFFLINE also set it to SINGLE_USER, and his session was still open and "using" the database, even if it was OFFLINE. When he closed that session we were able to drop the database.

Thx to everyone in the comments for helping.


Transactional Replication - Replication Order of Related Tables ** This was previously asked on Stackoverflow - I removed it from there and recreated here **

I have an issue with SQL Server Transactional Replication. My topology is as follows (and this cannot be changed, so I'm trying to work within the limitations that it has).

I have a publisher database (SourceDB) that is used by a new application. Some of the data entered there needs to be made available to an old application that is being replaced by the new one; the old application uses TargetDB, (the data in the TargetDB will be read only and used for reporting and downstream feeds). Both databases contain broadly similar data, but structured very differently. Data needs to be available as close to real time as possible. We have a remote distributor.

So we have set up a proxy version of SourceDB which acts as a subscriber (SourceProxyDB). This contains the subset of tables required for replication. Hanging off these tables are triggers which will transform the data and enter it into TargetDB.

SourceDB and the remote distributor are both SQL Server 2012 SP1; TargetDB is SQL Server 2008R2 SP2.

So basically:

data entered in SourceDB
transactional replication moves it to SourceProxyDB
triggers transform and enter data in TargetDB

So far, so good. For the most part this has been working well... until we started seeing some issues as follows:

There is a parent-child table relationship in SourceDB that needs to get into TargetDB
Typically one parent record is created followed by around 130 child records
Each child record contains a reference to the parent record's ID, which is an identity column
Sometimes we see the parent record arrive first in SourceProxyDB, followed by the child records as expected
Sometimes we see the parent record arrive out of order; either after all the child records arrive, or in the middle of the set of child records
There is no pattern to this
When the child records arrive first, they all contain a reference to the parent record which follows them, implying that the records are entered in the correct order in SourceDB but replicated in the wrong order
All child records always arrive in the correct sequential order; the only record out of order is the parent one

What we know/have tried so far:

confirmed that the parent and child records are added to SourceDB in a single transaction
used SQL Profiler to confirm that the parent and child records are added to SourceDB in the correct order
used SQL Profiler to confirm that the parent and child records are replicated to SourceProxyDB in the wrong order
added a FK relationship to SourceDB to tie the parent and child records together
marked the FK relationship 'False' for 'Not for Replication'
reinitialized replication for the parent and child tables (note - after doing this, the FK didn't appear in SourceProxyDB, which made me suspicious)
set SubscriptionStreams to 1

... and I'm still periodically seeing the parent record replicated either after or in the middle of the associated child records at SourceProxyDB.

We're not seeing any FK errors or violations at the subscriber (SourceProxyDB) - as noted above, the FK didn't make it to SourceProxyDB after replication was re-initialized. The nature of the logic in the triggers requires the parent record to be available to the child records however so we are seeing logic errors.

I was under the impression that transactional replication would preserve the order of insertion of records, particularly those with FK relationships.

I'm at a real loss as to where to look next. Any insights as to why this is happening and any possible solution(s) will be gratefully received; I've been tearing my hair out for days over this now...

Looks like the pertinent part (in this case) was to ensure that both the parent and child tables are contained within a single publication; once we did this, then everything started to behave as it should, and 30 or 40 tests later we haven't seen any failures.

At a guess... this means that both tables' log entries will be read by the same log reader job on the distributor, and so will be read in the correct order. Having separate publications allows separate log reader jobs to run in parallel, leading to the situation we were seeing.


Group By Jalali Date in Postgresql I want GROUP BY jalali's month, all date save as gregorian format in my database, I found following sql query but group by gregorian date
SELECT COUNT(reserverow.id), EXTRACT(MONTH FROM reserverow.used_date) as "Month"
FROM reserverow
WHERE reserverow.used_date > '2000-09-28 01:00:00'
GROUP BY EXTRACT(MONTH FROM reserverow.used_date)

The easiest way to do this would be to use Team Appir's jalali_utils extension for PostgreSQL. Then you could write your query like this:
SELECT COUNT(reserverow.id), EXTRACT(MONTH FROM reserverow.used_date) as "Month"
  FROM reserverow
 WHERE reserverow.used_date > '2000-09-28 01:00:00'
 GROUP BY JALALI_PART('month', reserverow.used_date)


What is an ODBK file and how to restore that? I have recently come across a situation where I am required to restore an ODBK file. Basically this is a database file of Intergy Server (An EHR platform). I don't know how to restore that file into a supported DB engine and how can I view the data. Upon searching I came to know that this is an OpenEdge DB file.
The server is running a Postgres engine as well but there is no table related to that EHR.

ODBK isn't any abbreviation officially used by OpenEdge. But if it indeed is a backup file from Progress OpenEdge you need to use prorest:
prorest newdatabase backedupfile

Start by reading this article


SSRS using external images through tls 1.2 leaves src(unknown) One of our SSRS reports loads images from external sources and one of these sources has been locked down to tls 1.2 (which is fair and we're expecting more of our clients to be doing this in the future)

The tls 1.2 update has been applied to the server holding the ReportServer database and the two Reporting Services endpoints held on seperate machines.

All three machines have been tested locally and can all load the secured image in their browsers but when a report is ran using SSRS it fails

I've set up a report for testing which has no database connection, it is simply a non secured image link and a tls 1.2 secured image link,

The error I get is: Warning        [rsInvalidImageReference] The ImageData for the image ‘Image2’ is invalid. Details: The request was aborted: Could not create SSL/TLS secure channel.

This leaves the image as src(unknown) in the actual report

So I feel like I've missed something in the installation of the tls 1.2 patch

Anyone have any ideas what might be going on?

I ran into this issue when attempting to run images from IIS Server which was locked down to TLS1.1 and TLS1.2

The issue was two fold

I had to update the Registry to add the following keys

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v2.0.50727]
"SystemDefaultTlsVersions"=dword:00000001

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v2.0.50727]
"SystemDefaultTlsVersions"=dword:00000001

per this article https://support.microsoft.com/en-us/help/3154519/support-for-tls-system-default-versions-included-in-the--net-framework

I also had to use the following Entry in the configuration file for Reporting Services

  [Install Location]/Reporting
  Services/Bin/ReportingServicesService.exe.conf
      inside the runtime element

  Default SQL Server 2016 Location: "C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\bin\ReportingServicesService.exe.config"

<AppContextSwitchOverrides value="Switch.System.ServiceModel.DisableUsingServicePointManagerSecurityProtocols=false;Switch.System.Net.DontEnableSchUseStrongCrypto=false" />

After all of this all I had to do is restart the reporting service and it was fine

Note that i do have tls 1.1 and 1.2 locked down my SSRS and SQL Server (not sure if this is also a requirement for this fix)

My System Sql Server 2016 and Windows Server 2012 R2


Policies and Procedures Database I'm trying to create a database for work purpose, and I need you guys to help me. I don't know if my idea possible or not but i want to give a chance to apply it.

As the title I want to make database for all policies and procedures in our organization. The purposes of this database are whenever my manager want to review any policy he can search for the policy by keyword, or title, or the code of each policy, also in order to make any change on that policy, he can review, edit the policy and print it. While he doing some changing on a specific policy I want to keep the original policy as well, so if I want to make a historical report for that policy I can generate all changes happens on that policy, the original one, the first change, the second change and so on.

What I want to is creating a " Master Table " Consist of all updated Policies for "  4 Departments " and I want to create a separate tables for each department. And then create a relationship between all those table, (all of them link with the Policy code column -if possible-). So, whenever the user enter data from the displayed form, this data will be record in master table, and the entered data will be saved in the related department table ( if he enter a data related to the department 1, the data will be saved in the master table and the department 1 table, and etc. ). So the result of all of these are whenever I want to make a report for all entered policies I can generate it from the master table, and whenever I want to make a separate report for each policy I can do that too.

My question is, if this database possible to be create, how i can create it, where I faced a lot of problems while I tried to create it, and if it's not possible, What should I do in order to get that features that my manager want?

Waiting your response.

The Features that I want to be exist are:
1- Display the policy in proper formatting (Making a Report)
2- Editing an existing policy.
3- Adding New Policy.
4- Make a historical report for each policy.
5- Searching bar to be search by keyword, title, policy code, and column name
6- Printing that policy
7- all document should be different by the unique version number.

If this were my project, I would run away from MS Access. A content management system or wiki would be a better choice - formatting and versioning are built in and you don't have to code it from the ground up.


SQL Server Profiler 2005 on remote SQL Server 2008 Express? Is it able to trace remote SQL Server 2008 using SQL Profiler 2005? If yes, anyone knows what else should I do, if during loging in appeares this message "Unable to locate trace definition file for microsoft sql version 10.0.0"?

I tried to go on by this link (http://blogs.msdn.com/b/sqlserverfaq/archive/2011/01/14/unable-to-read-trace-definition-file-microsoft-sql-server-tracedefinition-n-n1-n2-xml.aspx), but my SQLPath looks good. Any suggestions?

Those messages about 'unable to read trace definition' and the fix are always from a newer SQL Profiler version trying to connect to an older SQL Server version. You are trying the other way (from older SQL Profiler to newer SQL Server) and it won't work. AFAIK You need to use SQL Profiler 2008 or newer.


Should MySQL database be in dedicated server? The application has about 10 databases. Both application and databases are in the same server since is client does not want any time delay that could possibly occur if the database is in a dedicated server.

We have been experiencing application slowing down lately but when we check (after the users done for the day of course), everything works super fast. We have done load testing and everything seems fine which make me wonder if the lack of dedicated server takes up too much of CPU.

The argument against a dedicated server is invalid, if the microseconds that account for the delay when talking to a different server matter you would not be asking about MySQL.

But there is one thing you could check before going out and buying hardware: A good database is trying to utilize as much main memory as possible for caching. Make sure your system is not swapping memory out to disk when your application is running simultaneously on the same server.


Mariadb/docker error : Can't init tc log I tried to dockerize MariaDB. It was running fine until it crashes and this is the log:

2016-11-21 15:25:21 140091445393344 [Note] mysqld (mysqld 10.1.14-

MariaDB-1~jessie) starting as process 1 ...
2016-11-21 15:25:21 140091445393344 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2016-11-21 15:25:21 140091445393344 [Note] InnoDB: The InnoDB memory heap is disabled
2016-11-21 15:25:21 140091445393344 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-11-21 15:25:21 140091445393344 [Note] InnoDB: Memory barrier is not used
2016-11-21 15:25:21 140091445393344 [Note] InnoDB: Compressed tables use zlib 1.2.8
2016-11-21 15:25:21 140091445393344 [Note] InnoDB: Using Linux native AIO
2016-11-21 15:25:21 140091445393344 [Note] InnoDB: Using SSE crc32 instructions
2016-11-21 15:25:21 140091445393344 [Note] InnoDB: Initializing buffer pool, size = 256.0M
2016-11-21 15:25:21 140091445393344 [Note] InnoDB: Completed initialization of buffer pool
2016-11-21 15:25:21 140091445393344 [Note] InnoDB: Highest supported file format is Barracuda.
2016-11-21 15:25:21 140091445393344 [Note] InnoDB: 128 rollback segment(s) are active.
2016-11-21 15:25:21 140091445393344 [Note] InnoDB: Waiting for purge to start
2016-11-21 15:25:21 140091445393344 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.29-76.2 started; log sequence number 49590423178
2016-11-21 15:25:21 140090688898816 [Note] InnoDB: Dumping buffer pool(s) not yet started
2016-11-21 15:25:21 140091445393344 [Note] Plugin 'FEEDBACK' is disabled.
2016-11-21 15:25:21 140091445393344 [Note] Recovering after a crash using tc.log
2016-11-21 15:25:21 140091445393344 [ERROR] Can't init tc log
2016-11-21 15:25:21 140091445393344 [ERROR] Aborting

I was thinking that it was a disk problem but no, I have 14G available.

I ended up deleting the tc.log file in /var/lib/mysql. When I started mysql again, it created a new tc.log and started up.

sudo rm -f /var/lib/mysql/tc.log


How to determine if users have the default password in Oracle 12c? I know that I can query the "dba_users_with_defpwd" table, but I've seen reports that for some accounts (mainly SYS and SYSTEM) even if you provide a new non-trivial password during install, they will be shown as having the default password.

Is there any way to verify the users with default passowrds other than manually comparing the "sys.users$" and "sys.defalut_pwd$" tables?

NOTE: I don't have direct access to the environment, I just receive the user tables to check for compliance with the company's rules.

SYS and SYSTEM accounts are incorrectly listed in DBA_USERS_WITH_DEFPWD in 12c (Doc ID 2173962.1)

  SYS and SYSTEM have that flag set a database create time, and the flag
  doesn't clear until the first password change AFTER the database is
  created. So it may or may not be a default password, but it if it
  isn't default, it's one that hasn't been changed since the database
  was created and therefore should be changed.

If you do not have access to this, then:

https://mandysandhu.com/2017/10/20/oracle-12c-syssystem-accounts-are-incorrectly-listed-in-dba_users_with_defpwd/


Pick last Vendor Number (From Master table ) who sold Item number(From detail) We have a master table for Receipts which has

RCPSEQ, RCPNUMBER, PONUMBER, VENDORNUM, DATERCPT

AND Detail table which has:

RCPSEQ, PONUMBER, ITEM

I want to write a query to pick the Item number and the VendorNum who recently sold us that Item. Problem is grouping. with below query I am getting multiple PONUMBEr for same item and date.

SELECT
    p1.PONUMBER,
    P2.ITEMNO,
    (P2.MxDTAR) AS DATEM
FROM
    dbo.PORCPL P1
    join (select ITEMNO,   max(DTARRIVAL)as MxDTAR from PORCPL group by ITEMNO) as P2
        on P1.ITEMNO=p2.ITEMNO
order by
    ITEMNO

You can use ROW_NUMBER() function to rank each item by it's date, being 1 the most recent one.

;WITH TimingRankingByItem AS
(
    SELECT
        P.*,
        TimeRanking = ROW_NUMBER() OVER (PARTITION BY P.ITEMNO ORDER BY P.DTARRIVAL DESC)
    FROM
        PORCPL AS P
)
SELECT
    T.*
FROM
    TimingRankingByItem AS T
WHERE
    T.TimeRanking = 1

Or using GROUP BY you should join by both the item number and the date, but if you have 2 max dates with the same item you will see both.

;WITH MaxDateByItem AS
(
    SELECT
        P.ITEMNO
        MaxDate = MAX(P.DTARRIVAL)
    FROM
        PORCPL AS P
    GROUP BY
        P.ITEMNO
)
SELECT
    P.*
FROM
    MaxDateByItem AS M
    INNER JOIN PORCPL AS P ON
        M.MaxDate = P.DTARRIVAL AND
        M.ITEMNO = P.ITEMNO


Redesigning a table that has a lot of columns (not always related) I'm new to database stuff so I apologize if I say anything ridiculous..

I work for a company that runs a website for a certain industry. We're not very large at the moment but our parent company wants to push our product out to more places. I'm worried about this because our database was never designed with ANY plan in mind (e.g, normalization doesn't exist, there are maybe 70 unused tables out of about 400- plenty of more unused columns on the tables we do use, etc.,) and we're running on a really shitty database program (pervasive SQL).

The company has at least recognized the need to move away from pervasive. We're planning to go to mariadb. To go along with this, there is a rare opportunity to potentially redesign some parts (or all of) the database but no one really cared to look into it- and that's how I ended up here.

SO, all that being said I just want to know if I'm on the right track here with my redesign so far.

Let's say we have a table that holds basic settings for companies on our system. Some of these settings are relevant to notifications (i.e what events will trigger e-mails for them), some are related to billing, some to how they create orders / what info they see on their orders, etc., This table has 334 columns and a LARGE amount of them only store a CHAR(1) which can be Y/N. (Side note: am I correct in assuming BIT will take up less space?)

What I've been doing is separating the parts (i.e, settings for orders go in their own company_order_settings table which has the company's id in it). The only thing is at this point I already have ~8 tables just for these different settings...is this normal? In general these things will (usually) be accessed separately so I'm not concerned about large joins. This large number of purely settings related tables will also happen when I refactor the user tables (which hold info on the users at said companies- in a similar number of columns).

If anyone wants more info let me know.

Not really an answer - but too big for a comment. Just a few ideas/discussion.

"our database was never designed with ANY plan in mind" - I hear yeh, sister!

Been there, done that. Once worked with a Btrieve (precursor to Pervasive) system that had 35,000 (not a typo - that's thirty five thousand) fields in one table. Agree with @timpone - hesitant about tables with > 30 fields. Tables should be like women - tall and slim, not short and fat :-).

It's a lot of work to redesign. Start with core functionality - what's the minimum amount of tables required? I would consider PostgreSQL if I were you, but like @Austin, that's just my opinion.

Read up on the different RDBMSs - check out their functionality. Is there anything that Btrieve has that they do/don't? As far as I can see, the main thing that Btrieve brought to the party was arrays - they are a breach of the relational model and should be avoided at all costs in an RDBMS.

You say "a LARGE amount of them only store a CHAR(1) which can be Y/N. (Side note: am I correct in assuming BIT will take up less space?)". With space being so cheap these days, this is not really  an issue.


How to best encapsulate monetary values in a database I am working on a multi-currency application. For the purpose of simplifying this question, we will not be considering currency conversion (hence I have deliberately left AsAt : datetime out of the equation). In a number of places I need to store monetary values (e.g. 1.23 USD). I understand that I need to store both the amount (1.23), and the currency (CurrencyID with FK to Currency table having ISOAlpha3Code = USD). My question is, how should I best encapsulate and isolate these two data from the other columns in the table (TableX) to which the monetary value relates?

As I see it, I have three options:

I store two columns in TableX, one being the Amount (numeric(p,s)) and one being the CurrencyID (FK). I add an XOR check constraint on the two columns to TableX.
(DBMS dependent) I create a CLR UDT (or equivalent) representing Currency, storing both Amount and CurrencyID. I store one Currency type column on TableX containing the monetary value.
I create a MonetaryAmount table with three columns: MonetaryAmountID (surrogate PK), CurrencyID (FK), and Amount. I add MonetaryAmountID (FK) to TableX.

I dislike #1 because I have used this pattern before and nobody has understood that the two data are part of the one complex datum; also, it's not normalized. I dislike #2 because it breaks 1NF, it is DBMS specific, and it will likely be difficult/expensive to enforce referential integrity on Currency. I'm rather ambivalent towards #3 - it feels over-engineered and will probably be computationally expensive, but it is correctly normalized.

#3 also has the added complexity of whether to place a unique constraint on the (CurrencyID, Amount) combination, thereby making it an ad-hoc reference table and better normalizing it. I believe it would be best not to do this, as whilst MonetaryAmounts may correlate, they are distinct in nature.

Any experience you can share (or alternatives you can suggest) would be appreciated. I would especially like to hear from genuine database architectural gurus.

From a database (and client) point of view, you need to do a few things.

The first thing you need to consider is that you have to use integers for everything and have a number of implied decimals for each currency (eg: $2.00 is actually 200 with 2 implied decimals. The $2.00 part is a client formatting issue and a database maths issue.). Floating point never comes into it - you don't want to go near the mathematical implications involved in using floating point.

My advice: Have a currency table with the currency_id (PK), currency_name and decimals fields. Any other table then just needs a currency_id and amount column to relate to the original table. Formatting is then just a join between the two. Maths between any tables with the same currency is then just straight integers.

Maybe add a few more columns to the currency table for leading and trailing symbols (eg: '$' for prefix, '.' for decimal spacer) for formatting.


MySQL gives "Out of Memory" error when inserting a large file. From where is this filesize limitation arising? I'm using a MySQL database to store files.

The table I'm using is structured as follows:

+--------------+----------+------+-----+---------+-------+
| Field        | Type     | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+-------+
| AttachmentID | int(11)  | NO   | PRI | NULL    |       |
| Data         | longblob | NO   |     | NULL    |       |
+--------------+----------+------+-----+---------+-------+

The INSERT command I'm using is as simple as possible:

INSERT INTO table (AttachmentID, Data) VALUES (attachmentID, attachmentData);

Obviously, where attachmentID is an int, and attachmentData is a large byte array.

Having read several guides on how to store files in a database, I increased the max_allowed_packet setting in the config file to "512M", more than enough for the files that I would actually be inserting.

This worked fine for several files of 30MB - 40MB. However, now that I am inserting larger files in the 90MB+ range, I am recieving an "Out of Memory" exception, with a number of bytes needed equal to the size of the file I tried to insert.

The server is a virtual server, with 4GB allocated and nothing else running that could be interfering.

Why do I get an Out of Memory error for the larger files, and not the smaller?
Where is this file-size limitation arising?

I've included my config file below:

[client]
port        = 3306
socket      = /tmp/mysql.sock

[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
skip-locking
key_buffer_size = 256M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 8
max_allowed_packet = 512M

ft_min_word_len=2

ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.so

log-bin=mysql-bin
binlog_format=mixed
server-id = 1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

EDIT: I can successfully INSERT a file of size 41,586 KB, but not one of 44,119 KB. The limit is somewhere between them.

I saw an interesting answer to a question about the biggest blob you may have

Here is the statement I saw in ServerFault

innodb_log_file_size and innodb_log_buffer_size combined must be larger than ten times your biggest blob object if you have a lot of large ones

I did not see any InnoDB Settings in your my.cnf

I see you disabled the InnoDB Built-in and you plugged in another InnoDB

See if you can adjust innodb_log_file_size and innodb_log_buffer_size to accommodate.

Since you only have 4GB of RAM reserved, try making your InnoDB Log Files Much Bigger

Change innodb_log_file_size to 1G in /etc/my.cnf

service mysql stop
sleep 30
rm -f /var/lib/mysql/in_logfile[01]
service mysql start (recreates ib_logfile0 and ib_logfile1)

Give it a Try !!!


What units are 'reads' and 'writes' in dm_exec_query_stats MSDN describes the various logical / physical read and write columns in dm_exec_query_stats as:

  Number of physical reads performed the last time the plan was executed.

Those columns are all 64 bit ints (bigint), so obviously larger numbers represent more reads / writes.

But what are the units of this column? Database pages? Kilobytes? Just an arbitrary number?

Example: for a reasonably expensive query in my database dm_exec_query_stats.total_logical_reads = 40412.

40412 of what?

Reads and writes are expressed in terms of "the number 8K pages." It should be documented better on the page you reference, but you can piece this together from other areas of the documentation, e.g. from Reading Pages:

  A logical read occurs every time the Database Engine requests a page from the buffer cache. If the page is not currently in the buffer cache, a physical read first copies the page from disk into the cache.

And from Understanding Pages and Extents:

  In SQL Server, the page size is 8 KB.


How to correctly return a query result only if it isn't NULL? I'm writing a PL/pgSQL function that creates a cursor for a query that I need to check whether it returns something.

What I'm doing is this:

Run the query
Check whether it returns something.
If it doesn't, double a parameter and run the query again.
Else, return all rows from the query.

I found that checking if the query returned something with a cursor was the best choice since it's a very long query (joining 5 tables and a lot of columns) and a SELECT ... INTO didn't seemed right, because I'd have to create a TYPE since the query has columns from one table and a column for a distance calculation.

The problem is that I'm currently using the cursor only to check if the query returned something inside a loop where I open it and close it. Once the query returns something, I exit the loop and return the query. I can tell right away that this is an ugly workaround for what I need. Maybe someone can help me with this issue.

Here's some code that shows what I'm currently doing.

CREATE FUNCTION store_distance(
    latitude double precision,
    longitude double precision,
    radius double precision,
    tries integer
)
RETURNS TABLE(
    store_id store.id%type,
    store_name store.name%type,
    distance double precision
)
AS
$$
DECLARE
    cur_stores CURSOR FOR
        SELECT
            store.id,
            store.name,
            get_distance(latitude, longitude, store.latitude, store.longitude) distance
        FROM
            store
        WHERE
            store.latitude BETWEEN (latitude - radius) AND (latitude + radius)
            AND store.longitude BETWEEN (longitude - radius) AND (longitude + radius)
        ORDER BY
            distance ASC;
    count int := 0;
    storerow RECORD;
BEGIN
    LOOP
        IF count = tries THEN
            EXIT;
        END IF;
        OPEN cur_stores;
        FETCH cur_stores INTO storerow;
        IF FOUND THEN
            EXIT;
        END IF;
        radius := radius * 2;
        count := count + 1;
        CLOSE cur_stores;
    END LOOP;
    RETURN QUERY
    SELECT
        store.id,
        store.name,
        get_distance(latitude, longitude, store.latitude, store.longitude) distance
    FROM
        store
    WHERE
        store.latitude BETWEEN (latitude - radius) AND (latitude + radius)
        AND store.longitude BETWEEN (longitude - radius) AND (longitude + radius)
    ORDER BY
        distance ASC;
END;
$$ LANGUAGE PLPGSQL;

So my purpose is to give coordinates, radius and the number of tries and to try and find stores within that search box. If no stores are found, I double the radius and try again until something is returned by the query or until the number of tries are reached.
The RETURN TABLE part is basically because I want to return the distance, so RETURN SETOF store was no use.

I don't think you need a cursor here at all. To shorten your code, you could just use a view. To improve performance, a materialized view should get you furthest. Postgres 9.3 has built-in features, but you can easily implemented it in older versions yourself.

Consider this simplified form:

CREATE FUNCTION store_distance(_lat    double precision
                              ,_long   double precision
                              ,_radius double precision
                              ,_tries  integer)
RETURNS TABLE(
   store_id   store.id%type
  ,store_name store.name%type
  ,distance   double precision) AS
$func$
DECLARE
   _ct  int   := 0;
   _pos point := point(_lat, _long);
BEGIN
   LOOP
      EXIT WHEN _ct >= _tries
           OR   EXISTS (
            SELECT 1 FROM store s
            WHERE  point(s.latitude, s.longitude) <@ circle(_pos, _radius));

      _radius := _radius * 2;
      _ct     := _ct + 1;
   END LOOP;

   RETURN QUERY
   SELECT s.id, s.name
         ,get_distance(_lat, _long, s.latitude, s.longitude)
   FROM   store s
   WHERE  point(s.latitude, s.longitude) <@ circle(_pos, _radius);
   ORDER  BY 3;
END
$func$ LANGUAGE plpgsql STRICT;

I made the function STRICT to disallow NULL input, which could result in an endless loop.

Note how I use circles with the "Contained" operator <@ instead of a boxes. One would assume calculations to be slightly more expensive than with boxes, but it hardly matters at all once you support your query with a GiST index like:

CREATE INDEX store_point_gist_idx ON store
USING gist (point(latitude, longitude));

You might consider to store lat / lon as point to begin with and replace the index on an expression with a simpler one on the column. Works either way, just make sure the query matches the index so it gets used. Big difference for big tables.

You may be interested in this closely related answer on SO I posted last year - with a lot more explanation and links.


query slows down exponentially as table grows I have the following query running on mysql 5.6 on my CENTOS server

select tb1.field2, tb1.field1, tb2.field1
from dns.table1 tb1, dns.table2 tb2
where tb1.field2 = tb2.field2
  and tb1.field1 != tb2.field1

All relevant columns are indexed.  On tables of 10 million rows, it finishes within 400 seconds, however, on 100 million rows, it finishes within 10 hours!

table schema:

CREATE TABLE `my_table` (
  `table_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `field2` varchar(255) NOT NULL,
  `field1` varchar(255) DEFAULT NULL,
  `field3` longtext,
  `updated_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`table_id`),
  UNIQUE KEY `field2` (`field2`),
  KEY `field1` (`field1`)
) ENGINE=InnoDB
  AUTO_INCREMENT=1
  DEFAULT CHARSET=utf8
  ROW_FORMAT=COMPRESSED
  KEY_BLOCK_SIZE=8 |

Both innodb tables are of equal sizes.

Why? I suspect file swapping within mysql, what can be optimized on mysql? Would increasing the buffer pool help?

The compound INDEX(field2, field1) would be a "covering" index.  This would avoid a huge number of lookups, thereby making it much faster.  Note:  This will not work if you also SELECT field3.  (InnoDB and MyISAM would both benefit in this case.)

Keep your UNIQUE(field2) because of the uniqueness constraint.


How Merge Replication Works I'm not DBA at all but I need to do something to synchronize four SQL server databases. I just discover there is a Merge Replication system in SQL Server 2008. At this moment I'm reading the specification and the documentation but it's not clear if this tool is able to do real synchronization or is just merging data from my master database to my slave database. By real synchronization I mean the data is synchronized from my master to my slaves but also from my slaves to my master.

https://technet.microsoft.com/en-us/library/ms151329(v=sql.105).aspx

Merge replication supports bidirectional subscriptions, with changes propagated from the subscribers to the publisher.

Other types of replication support this scenario: Transational Replication with updateable subscriptions (deprecated) and Peer-To-Peer Transactional Replication (more complicated to maintain, requires Enterprise Edition).

Keep in mind that Merge Replication keeps track of changes using triggers on each published table (article), which can determine substantial overhead to all write operations against those tables. Merge Replication also requires a unique uniqueidentifier column on each article: if you don't have one, it wil add it for you (more space needes, heavy fragmentation after adding the column).

Make sure you test performance before going down this road.


change analyzer for an elasticsearch index? I'd like to change the analyzer for an existing index in ElasticSearch, but I can't figure out the syntax (and I'm not grokking the error message).

GET /ccc_test/_mapping

{
  "ccc_test": {
    "mappings": {
      "test_article": {
        "properties": {
          "id": {
            "type": "text"
          },
          "language": {
            "type": "text"
          },
          "text": {
            "type": "text"
          },
          "title": {
            "type": "text"
          },
          "url": {
            "type": "keyword"
          }
        }
      }
    }
  }
}

Then I close the index with:

POST /ccc_test/_close

And then I'm trying to update the analyzer for the "title" field to Portuguese:

PUT /ccc_test/_mapping/test_article
{
  "properties" : {
    "title" : {
      "type" : "text",
      "analyzer" : "portuguese"
    }
  }
}

But I get this error:

{
  "error": {
    "root_cause": [
      {
        "type": "remote_transport_exception",
        "reason": "[instance-0000000004][172.17.0.8:19760][indices:admin/mapping/put]"
      }
    ],
    "type": "illegal_argument_exception",
    "reason": "Mapper for [title] conflicts with existing mapping in other types:\n[mapper [title] has different [analyzer]]"
  },
  "status": 400
}

So I'm not clear what has a different analyzer.  The ccc_test index only contains one type (test_article). I'm on ElasticSearch 5.6

It turns out that you can't do what I want to.  The correct answer is:

Create a new index with the mapping you want
Use "reindex" to copy the data from the old index to the new one
Drop the old index, but create an alias with the name of the old index that points to the new index (because ElasticSearch does not allow you to rename an index.)

So in detail

Create a new index with the mapping you want

POST /ccc_test_new
{
  "mappings": {
    "test_article": {
      "properties": {
        "id": {
          "type": "text"
        },
        "language": {
          "type": "text"
        },
        "text": {
          "type": "text"
        },
        "title": {
          "type": "text"
        },
        "url": {
          "type": "keyword"
        }
      }
    }
  }
}

Use "reindex" to copy the data from the old index to the new one

POST /_reindex
"""{
  "source" : {
    "index" : "ccc_test"
  },
  "dest" : {
    "index" : "ccc_test_new",
    "version_type" : "external"
  }
}

Drop the old index, but create an alias with the name of the old index that points to the new index

POST /_aliases
{
    "actions" : [
      {
        "add" : {
          "index" : "ccc_test_new",
          "alias" : "ccc_test"
        }
      },
    {
        "remove_index" : {
          "index": "ccc_test"
      }
    }
  ]
}


MS Access: Update data from one table to another based on a choice Help!
I have a patient form where their referring doctor is chosen and that ID for the doctor is stored in a variable. When I click "Save record" I need it to copy the data form the ReferringDoctors table (name, address, etc) and copy it to the PatientDetails table in corresponding variables based on which doctor has been chosen. I can't figure it out. Help!

Using Access 2013

Re-edit:
The whole idea here it that I have three separate tables - 1 for the DoctorInfo (just address data), 1 for the PatientInfo (just address data), and 1 for the PatientVisit info (why they came that day). The goal is to be able to click a button on the PatientVisit form to generate a letter in Word that includes the address from the doctor, the patient's address and the information from the patient visit.

So far, I have all the information I need stored in the three tables, but I have no clue how to grab the information to generate the letter. For example if Patient 1 is referred by Doctor A on the current visit, how do I generate a letter that has that information? I assumed I would need to copy the info from one table to another to eventually pass it to Word for it to use to generate the letter. Totally wrong?

Not sure what you want to achieve so let's start with saying that if you already have a table with the doctor's details, you shouldn't add them to the patientdetails table. If you need to display the data somewhere you can use joins in your query.

Thoughts:

Never duplicate data (unless you have to, these are only special cases)
You can always query data if they are linked (let's say you add the referring doctors id to this table)

example query

select * from PatientDetails join ReferringDoctors on PatientDetails.ReferringDoctorsID=ReferringDoctors.ID

Please provide more info, your current table structure, why you want to copy the info etc.


Safe Cast in Crystal Reports Comparison Using Crystal Reports version 14.0.3.613RTM

With SQL Server 2012 onward, if a field has both number and string data, I can use try_convert to compare, i.e.

TRY_CONVERT(int, resource_code) > 999999999

How do I handle this in Crystal Reports?

I tried:

ToNumber({sch_resource.resource_code})

But if resource_code isn't a number, CR barfs.

Thanks to the comment by beccasue, I found the solution:

Using a Nested If with IsNumeric and ToNumber worked.

Example of nested Crystal Reports if statement copied from a SO question:
Nested if else in Crystal Reports

if {table1.id} <> 1 then
   (if {table1.name} <> "a" then
      var1 := "Hello"
   else
      var1 := "Hi";)
else
   var1 := "Bye";