Since you're running 9.3, you could potentially use an event trigger to catch the tag DROP TABLE and prevent that for certain users.

Denish Patel has a example in his blog post here Preventing Human Errors in Postgres. Search for the string "Function to prevent single table drop". That should give you enough of a start to modify for your specific needs.


Yes.
You can change the default collation of SQL Server 2008 R2 express instance and individual databases, but it is a complex task.
Sadly, there is no visual option to do it via SSMS.

SQL Server 2008 supports setting collations at the following levels:

Server

Database

Column

Expression

The default installation settings are
determined by the Windows system
locale. The server-level collation can
either be changed during setup, or by
changing the Windows system locale
before installation. more...

Setting and Changing the Server Collation - SQL Server 2008

Make sure you have all the information or scripts needed to re-create your user databases and all the objects in them.

Export all your data using a tool such as the bcp Utility. For more information, see Importing and Exporting Bulk Data.

Drop all the user databases.

Rebuild the master database specifying the new collation in the SQLCOLLATION property of the setup command

Create all the databases and all the objects in them.

Import all your data.

Setting and Changing the Database Collation - SQL Server 2008

Set the COLLATION option in the CREATE DATABASE statement while creating a new database.

Similarly, set the COLLATION options in the ALTER DATABASE statement to change the collation of an existing database.
ALTER DATABASE [database_name] COLLATE SQL_Latin1_General_CP1_CI_AS;

Setting and Changing the Column Collation

Some of the column collations will remain the same even after you alter the database collation. In that case, you have to modify the collation of the individual columns.


Query parent table and get child tables columns

As long as the set of inherited column values is unique across all tables, there is a simple solution with a NATURAL join (one of the rare use cases for this clause!):

SELECT * FROM ONLY parent NATURAL FULL JOIN child;

Since NATURAL is (per documentation):

  ... shorthand for a USING list that mentions all columns in the two tables that have the same names.

You get each column only once without explicit SELECT list, and rows from the parent table are extended with NULL values for added columns - exactly the way you desire.

This even works with NULL values in any of the columns.

To also see the origin of each row in the result, insert another row to demonstrate the difference:

INSERT INTO child(col1) VALUES(1);

Then:

SELECT COALESCE(p.tableoid, c.tableoid)::regclass AS tbl, *
FROM   ONLY parent p NATURAL FULL JOIN child c;

 tbl   | col1 | col2
-------+------+----
parent | 1    | NULL
child  | 1    | NULL
child  | 2    | 2

SQL Fiddle with more test rows.

You can join to multiple child tables this way if all additional column names in all child tables are unique. Else you'll have to spell out an explicit SELECT list and explicit join conditions.

The set of inherited column values has to be unique across all child tables.


Why does UPDLOCK cause SELECTs to hang (lock)?

As documented in Books Online, UPDLOCK takes update locks and holds them to the end of the transaction.

Without an index to locate the row(s) to be locked, all tested rows are locked, and locks on qualifying rows are held until the transaction completes.

The first transaction holds an update lock on the row where name = 1. The second transaction is blocked when it attempts to acquire an update lock on the same row (to test if name = 2 for that row).

With an index, SQL Server can quickly locate and lock only those rows that qualify, so there is no conflict.

You should review the code with a qualified database professional to validate the reason for the locking hint, and to ensure appropriate indexes are present.

Related information: Data Modifications under Read Committed Snapshot Isolation


Check out these article series on SQLServerCentral.com:

Stairway to Server Management Objects (SMO)

Stairway to SQL PowerShell

These are a series of articles that provide great foundational skills right through to some advanced concepts and practises with specific focus on SQL Server and SMO.


How to improve performance on PostgreSQL when using multiple concurrent processes?

You sort of answer your own question when you say you have no pooling but...

This is not an answer out of the box, with all client/db stuff you may need to do some work to determine exactly what is amiss

backup postgresql.conf changing

log_min_duration_statement to 0
log_destination = 'csvlog'              # Valid values are combinations of
logging_collector = on                # Enable capturing of stderr and csvlog
log_directory = 'pg_log'                # directory where log files are written,
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
debug_print_parse = on
debug_print_rewritten = on
debug_print_plan output = on
log_min_messages = info (debug1 for all server versions prior to 8.4)

Stop and restart your database server ( reload may not pick up the changes ) Reproduce your tests ensuring that the server time and client times match and that you record the start times etc.

copy the log file off an import into editor of your choice (excel or another spreadsheet can be useful for getting advance manipulation for sql & plans etc)

now examine the timings from the server side and note:

is the sql reported on the server the same in each case

if the same you should have the same timings

is the client generating a cursor rather than passing sql

is the query arriving on the server when you believe it should do

is one driver doing a lot of casting/converting between character sets or implicit converting of other types such as dates or timestamps.

and so on

The plan data will be included for completeness, this may inform if there are gross differences in the SQL submitted by the clients.


How to store data that depends on dynamic number of parameters

maybe you want a table that has a composite type as part of the key.

create extension hstore;

create table stats (client int, key hstore, value text , primary key (client, key));

insert into stats values
  (1,'"product"=>"gum","store"=>"711"','USD 0.49')
 ,(1,'"product"=>"soda","store"=>"711"','USD 0.99')
 ,(2,'"vehicle"=>"prefect 351","fuel"=>"nitromethane"','950kW')
 ,(2,'"vehicle"=>"prefect 351","fuel"=>"ULP-97RON"','600kW')
;

select * from stats where client=2 and key=hstore(array['vehicle','prefect 351','fuel','nitromethane']);

or perhaps with the value column as hstore also, perhaps he wants to store "loudness" and "power"


Awful performance on joined queries while great separately

A stretch but give this a try
Just deals with one t.[IntField] at a time and union
Now you have 1/3 only comparing to 1/3 so it may be 9 times as fast
And the query optimizer may miraculously decide not to do a loop join

SELECT t1.[IntField]
     , CONVERT(varchar,t1.[KhDateTimeField],121) AS 'TDateTimeField'
     , t1.[FloatField1], t1.[FloatField2], t2.[FloatField3]
FROM
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField1], t.[FloatField2]
     , dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table1] t
WHERE t.[TDateTimeField] >  CAST('2016-03-01T00:00:00' AS DATETIME)
  AND t.[TDateTimeField] <= CAST('2016-03-10T00:00:00' AS DATETIME)
  AND t.[DateInt] >= 20160303
  AND t.[DateInt] <= 20160310
  AND t.[IntField] = 206
) t1
INNER JOIN
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField3]
     , dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table2] t
WHERE t.[TDateTimeField] >  CAST('2016-03-01T00:00:00' AS DATETIME)
  AND t.[TDateTimeField] <= CAST('2016-03-10T00:00:00' AS DATETIME)
  AND t.[DateInt] >= 20160303
  AND t.[DateInt] <= 20160310
  AND t.[IntField] = 206
) t2
ON t1.TDateTimeField = t2.TDateTimeField
UNION
SELECT t1.[IntField]
     , CONVERT(varchar,t1.[KhDateTimeField],121) AS 'TDateTimeField'
     , t1.[FloatField1], t1.[FloatField2], t2.[FloatField3]
FROM
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField1], t.[FloatField2]
     , dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table1] t
WHERE t.[TDateTimeField] >  CAST('2016-03-01T00:00:00' AS DATETIME)
  AND t.[TDateTimeField] <= CAST('2016-03-10T00:00:00' AS DATETIME)
  AND t.[DateInt] >= 20160303
  AND t.[DateInt] <= 20160310
  AND t.[IntField] = 207
) t1
INNER JOIN
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField3]
    , dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table2] t
WHERE t.[TDateTimeField] >  CAST('2016-03-01T00:00:00' AS DATETIME)
  AND t.[TDateTimeField] <= CAST('2016-03-10T00:00:00' AS DATETIME)
  AND t.[DateInt] >= 20160303
  AND t.[DateInt] <= 20160310
  AND t.[IntField] = 207
) t2
ON t1.TDateTimeField = t2.TDateTimeField
UNION ... 208

If it works then you can just make two stored procedures where you pass in the 5 conditions for the base select and the syntax becomes pretty simple.

Materializing is the safe path.  But use #temp over table.  An index on [IntField] may help but with only 3 unique values it may not help.  Even with materializing make some stored procedures to reduce syntax.


Deploying and rolling back changes in an Availability Group database

There's not really a practical method. I can really think of three ways of rolling back a failed deployment:

BEGIN \ ROLLBACK TRANSACTION: Normally, this is what I'd recommend - it's cleanest, and you wouldn't have to suspend data movement to do it. However, since you need to be able to view the changes from another machine, it's not really an option.
Rollback script: With this, you pre-script the commands required to return the database to the state it was in prior to the deployment. It's not a true rollback, and it may be impractical on a large database in some circumstances.
RESTORE DATABASE: As you mentioned, there are hassles for this on a large database in an AG.


Why are the system tables in MySQL and MariaDB utf8 and not utf8mb4?

By Definitions as per MySQL Reference Manual:

  utf8mb4: A UTF-8 encoding of the Unicode character set using one to
  four bytes per character.

  utf8mb3 : A UTF-8 encoding of the Unicode character set using one to
  three bytes per character.

Since, utf8mb3 (also known as UTF8) can not store all languages like Chinese or Emoticons, it is recommended to use utf8mb4 when it comes to store application data.

For simple example, You want to extend your existing business into China.  if you have used utf8mb4, your application is always ready to adopt your business extension while in case of utf8mb3(UTF-8), you need an character set upgrade to your application.

While storing system's data MySQL does not store information (supplementary character) which requires utf8mb4, this could be the reason, why MySQL itself is not using utf8mb4.


Can't list columns or query in MS Access 2003 linked server in SQL Server 2008 R2 using Microsoft Jet 4.0 OLE DB Provider?

1. Set your SystemDB File in the Registry

Note that you can only specify ONE of these damn things.

On 32-bit Windows

If your Windows installation on which your SQL Server instance installed is 32-bit which is doubtful...use the following key to set the path to your MS-Access Workgroup file (*.mdw):

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\SystemDB

On 64-bit Windows

On the other hand if your Windows installation on which your SQL Server Instance is installed, is 64-bit, (more likely) set the path to your MS-Access Workgroup file (*.mdw) here:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Jet 4.0\Engines\SystemDB

2. Create your linked server to MS Access

USE [master]
GO

-- Create a new linked table
EXEC sp_addlinkedserver
@server='LinkedServerToMSAccessMDB',
@provider='Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'E:\DB_local\MyMDB.mdb'
GO

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname='LinkedServerToMSAccessMDB',
@useself='FALSE',
@locallogin=NULL,
@rmtuser='someuser',
@rmtpassword='daspasswd'
GO

3. Make sure that your database credentials are passed to the Workgroup File (.mdw) when running a query from the (.mdb)

Now if you want to make sure that the password is being passed correctly after running this....in the Object Explorer panel, in <server-name>\Server Objects\Linked Servers, right click it and click refresh...your new LinkedServerToMSAccessMDB should now appear in the list, right click it and select Properties

In the window that appears next in the Select a page panel, click Security and then in the For a login no defined in the list above, connections will: radio button list, select the Be made using this security context: (if you don't want to be particular about what network users have access to it....otherwise use the mappings) and then in enter the username and password you would normally use to access MS Access using your .mdw file in the Remote login: and With password: text boxes.

4. Run a Query

After you click OK you should now be able to run a select on the .mdb file so that you can create a VIEW in a schema for some specified user.

SELECT [Field1]
  FROM [LinkedServerToMSAccessMDB]...[T_Table1]
GO


If Value column doesn't change, then for each row only latest else of your case is true. Syntax itself is correct, and it should work as you want. I'd first do SELECT to double check that constants in CASE correspond to what you have in the table:

SELECT DISTINCT
CASE Path
WHEN '\Folder1' THEN '0'
WHEN '\Folder2' THEN 'xyz'
WHEN '\Folder3' THEN '0'
END as val
FROM Database.[dbo].[Table]

It should return up to 3 rows ('0','xyz', and NULL). If it returns 1 row only(NULL), then you don't have records where the value of Path column equals to any of '\Folder1','\Folder2','\Folder3'.

Note : the syntax of CASE you use compares value of Path column with constants using =. You might have wanted to use another logic, for instance LIKE. Then use different form of CASE :

SELECT DISTINCT
CASE
WHEN Path LIKE '\Folder1%' OR Path LIKE '\Folder3%' THEN '0'
WHEN Path LIKE '\Folder2%' THEN 'xyz'
END as val
FROM Database.[dbo].[Table]


Oracle log miner permission: which permission authorize user to use logMiner as scheduled JOB?

You need EXECUTE_CATALOG_ROLE to be assigned to the user.

  You must have been granted the EXECUTE_CATALOG_ROLE role to use the LogMiner PL/SQL packages and to query the V$LOGMNR_CONTENTS view.

For details:Using LogMiner to Analyze Redo Log Files


How do you join tables at different points based on data in the first table?

Dont know why you have a subselect at all, that query can be reduced to this:

SELECT * FROM shift AS a
LEFT JOIN shift AS b ON a.replaced_id = b.id
LEFT JOIN schedule AS s ON a.schedule_id = s.id AND s.date >= '2015-12-05'
left JOIN shift_info AS si ON s.shift = si.initial AND si.date <= '2015-12-05'
WHERE a.user_id = 58;

Generally you want join conditions applied in the ON clause, particularly when they affect the actual join, as is the case with LEFT JOIN. I think this fulfills what you are asking as well (since the schedule and shift_info start at the same start time).


If you pop open sp_BlitzCache, you should see parameters for:

@OutputDatabaseName NVARCHAR(256) = NULL ,
@OutputSchemaName NVARCHAR(256) = NULL ,
@OutputTableName NVARCHAR(256) = NULL ,

You seem to have tacked the schema name onto the table name. That's probably what's causing the problem.


At least three times on the sp_configure page, you have "Setting Server Configuration Options".

In the table for the row "config_value"
Under "Advanced Options"
At the bottom

This page also shows what happens if you run sp_configure with a blank @configname

  If not specified, the complete list of options is returned.

This applies from SQL Server 2000 to SQL Server 2008 pages


How to group same columns from same two tables, but from two different result sets and/or conditions

Keep in mind that queries like this operate thus:

JOIN all the tables together, generating a 'large' temp table with all combinations that have not been filtered out.
Then do the aggregates (SUM, GROUP_CONCAT, etc)

A common problem is that SUMs and COUNTs are inflated because of all the combinations.  In your case, GROUP_CONCAT may have duplicates; this can be handled by saying GROUP_CONCAT(DISTINCT ...).

A common solution is to focus on the aggregates, using a minimum of JOINs to get the aggregates right, then JOIN to the rest of the tables.

Another solution that sometimes works is to replace a JOIN with a subquery (in the SELECT part) that computes "looks up" a value (such as mapping an id to a name).

You must be using an older version of MySQL; new versions don't like you to say GROUP BY one-column while listing 7 columns in the SELECT.

Sorry, this is only "handwaving", but your question is also missing details, such as details of "when I added...", SHOW CREATE TABLE, specifying which table each column belongs to, etc.


Remember that the WITH clause is used to name sub-queries, and sub-query logic can be abused somewhat. The overhead for WITH shouldn't end up being much more than using a regular sub-query, but still don't try to have a ridiculous amount of nesting going on. So long as you have a good picture of how deep you're going with things, you should be fine.


How to avoid duplicate entries with SELECT statement?

SELECT a.*                                -- select all rows
FROM tableX  a                            -- from table
WHERE a.tuser = 1                         -- where either tuser = 1
   OR NOT EXISTS                          -- or there is no other
      ( SELECT *                          -- row
        FROM tableX  b                    -- in the table
        WHERE b.tuser = 1                 -- with tuser = 1
          AND b.id = a.id                 -- and same "id",
          AND b.id_user = a.id_user       -- same "id_user"
          AND b.id_word = a.id_word       -- and same "id_word"
      ) ;


Taking a closer look at the execution plan XML, notice these problematic statistics:

<Wait WaitType="ASYNC_NETWORK_IO" WaitTimeMs="1328" WaitCount="403"/>

<QueryTimeStats CpuTime="353" ElapsedTime="1853"/>

The query spent 1.3 seconds waiting on the results to be consumed by the application.  The query only ran for 1.8 seconds total.  So the main problem here is that the application is consuming these 127k results row-by-row.  The query itself runs fairly quickly.

Forrest McDaniel has a good blog post that demonstrates this problem: Two Easy ASYNC_NETWORK_IO Demos

The remainder of the answer addresses the "logical reads" portion of your question.

The reason for all of those logical reads on the OUTER APPLY'd table (DBVAREKT) is here:

The "Index Seek" there is executed once for each row on the upper input to the NESTED LOOPS join.  So there are 127,329 seeks into that index (ID1), even though in the end only 302 matching rows are returned.

The optimizer wouldn't normally choose to do that many seeks into the index, but it only thought there would be 82 rows on the upper input.  Doing 82 seeks is definitely more reasonable.

The general approach to solving this problem would be to avoid doing a NESTED LOOPS join on that particular table, since that is the source of the problem.  To that end, you could use join hints, but it's a little hard for me to tell where the hints should be applied.

Randi mentioned a possible rewrite of the query that would place some of the data into a temp table, essentially breaking the query up into smaller chunks that the SQL Server optimizer can do a better job with.  You could break this up at the OUTER APPLY as follows:

SELECT
       1756,
       L.MADTYPE,
       DBM.VNR1
INTO #results
FROM dbo.STDORDRE S
INNER JOIN dbo.STDORD STO ON sto.DATO = s.DATO
                           AND sto.KUNDE = s.KUNDE
LEFT JOIN dbo.STDORDML L ON L.ONR = s.ONR
CROSS APPLY (SELECT dbo.MCS_ClarionDateToSQL(D.DATO) SQL_DATO,
                    DATEPART(dw, dbo.MCS_ClarionDateToSQL(D.DATO)) DP,  D.VNR1, D.DATO, D.LINE, D.KATALOGNR, D.VFAKTOR, D.MADTYPE FROM dbo.DBMENU D WHERE D.SNR = s.VARENR AND D.LINE = L.MENULINE) DBM
INNER JOIN dbo.DBKUNDE kun ON kun.NR = s.KUNDE
INNER JOIN dbo.DBKUNGRP dbk ON dbk.NR = kun.GRP
INNER JOIN dbo.DBVARE varm ON varm.NR = s.VARENR
INNER JOIN dbo.DBVARE var ON var.NR = DBM.VNR1
LEFT OUTER JOIN dbo.MENORDRE MEN ON MEN.KUNDE = s.KUNDE
                                AND MEN.DATO  = DBM.DATO
                                AND MEN.LINIE = DBM.LINE
                                AND MEN.NR    = s.MNR
WHERE 1 = 1
  AND ( kun.UDSKREVET = 0
                     OR ( kun.UDSKREVET = 1
                          AND kun.UDSDATO >= 79627 ))
                   AND varm.TYPE = 9
                   AND varm.KPFIX = 0
                   AND s.ML = 1
                   AND DBM.DATO BETWEEN 79627 AND 79777
                   AND sto.TYPE = 1;

SELECT
       1756,
       L.MADTYPE,
       DBM.VNR1
FROM #results RES
OUTER APPLY (SELECT MTY.PREC MTY_PREC,
                    MTY.NR   MTY_NR ,
                    VAR1.PREC VAR1_PREC,
                    var1.VAR_PKG_ID VAR1_VAR_PKG_ID,
                    VAR1.NR VAR1_NR ,
                    VAR1.TYPE VAR1_TYPE,
                    VAR1.GRP VAR1_GRP
             FROM dbo.DBVAREKT VKT
              LEFT JOIN dbo.DBVARE VAR1 ON VAR1.PREC = VKT.TO_VARE_PREC
                                               AND (ISNULL(VKT.MADTYPE,0) <> 0  )
              LEFT JOIN dbo.DBMTYPE MTY ON MTY.PREC = VKT.TO_MADTYPE_PREC
            WHERE 1 = 1
              AND VKT.MADTYPE = RES.MADTYPE
              AND VKT.VARENR  = RES.VNR1
                      ) OA;


SQL Server Management Studio 17.1 Mirroring bug

The previous Connect item has been re-created in the Azure Feedback forum as:

SSMS 17.2 (and 17.1) Mirror configuration fails because mirror database is restoring

If you are experiencing this issue, you may wish to Upvote the Azure item.

Also note that the problem shows up in the recently released SSMS 17.2, 17.4 and even 17.9 as well.


Why is the speed of executing statements dependent on the network connection?

There is obviously some communication going on between SQL Server and the client that directly is dependent on the number of statements executed.

Yes, there is. By default, SQL Server sends a TDS DONE_IN_PROC message after every statement in a stored procedure. The message communicates status and row count information for the completed statement to the client.

You can suppress the sending of these messages using the T-SQL command:

SET NOCOUNT ON;

The following is an extract (my emphasis) from the Books Online entry for this command:

  For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

Related Q & A: Why does a simple loop result in ASYNC_NETWORK_IO waits?


Using varchar_pattern_ops in a multi-column index in Postgres

The two-column btree index will help with the like 'foo%' query, but probably not dramatically so.  It helps because it can be executed as an index-only scan, and so it can compute the LIKE portion within in the index without ever having to visit the table.  The index scan will jump to the first entry > '01/01/2012', and then traverse from there to the logical end of the index.  At each entry it will test the LIKE condition.  For the ones that pass that condition, it will check the visibility map to see if the table page holding that tuple is all visible.  If it is all visible, it will increment the counter and move on.  If not, it will have to visit the table page to see if the tuple is visible.

How much of a help this will be is hard to predict, as it depends on the size of the index, the table, your RAM, what mix of queries you are running (which changes which kind of data is likely to be found in the cache), among other things.

It will not help with the `like '%foobar' query.  From the outside looking in, there is no reason it couldn't help in the same way.  But PostgreSQL's index machinery just hasn't been made clever enough (yet) to recognize and implement that possibility.


Should I store relation counts as their own columns, or will indexes be fast enough?

Several comments:

We can't guess what's fast enough for you. As the comment above says, it depends on the speed of your hardware, but it also depends on how well your data fits in RAM, it depends on how frequently the query is run, it depends on how much data exists, it depends on how fast you need it to be.
Your app might need to denormalize or cache this value, but we don't know if your app operates at a scale where it needs this yet. Be conservative with such optimizations. They might be needed, but if they are not needed, it just adds complexity to your app for no reason.

You don't need a join to count rows in the likes table for a given post, as @nbk points out. Probably you do need to use a join if you want to count the likes for many posts, but we can't guess if you need to do that.

Given the attributes of the likes table you've shown, why do you even need this table? You don't record the person who added each like, so you can't implement a feature to let users reverse their like. You also don't record any other attributes, such as the datetime of the like, or the type of like (e.g. facebook's like, heart, care, wow, sad, angry). Why bother having the table?

If you wonder if it will still be fast enough once it reaches millions of rows, then test it with millions of rows. This is part of your job as a software developers: come up with a way to test things, don't guess or expect someone else to tell you the right answer. You can load a table with millions of random rows for likes, and then try out your query. This will be more accurate than a Stack Overflow answer, because it will be running on your server's hardware. We don't have to make assumptions about the speed or capacity of your environment.


Howto avoid textdata of trace gets truncated?

SSMS always truncates long strings unless typed as XML in which case you can set it to allow unlimited.

The workaround I usually use (from Adam Machanic's workaround to a connect item (internet archive link)) is below
SELECT (SELECT YourTraceDataColumn AS [processing-instruction(x)] FOR XML PATH(''), TYPE)
FROM YourTraceTable

This adds a few leading (<?x) and trailing  (?>) characters but otherwise leaves the data intact and without characters being replaced by XML entities.


Row-based Permission in SQL Server Newbie Question

If you want every student get access only to the rows reguarding that sudent, you can accomplish this by adding one more field to your students table where for each student you'll be store his AD account, then create a view that will join all you need including your students table and using a filter like this

where ... and user = dbo.students.ad_account

So every student will get access to his rows only. Here USER is a built-in T-SQL function that will return student's AD account. Certainly you should create logins for your students using windows authentication;  you do not need to map them individually, it will be sufficient to add one windows group yourDom\STUDENTS where every student is a member of yourDom\STUDENTS


Syntax changes between DB2 for i (DB2/400) v5r4 and v6r1

Here is documentation for DB2 for i on i5/OS V6 R1.

Here is specific to DB2 itself.

Here is specific to DB2 SQL.


ST_Distance_Sphere() is special optimization on a sphere. If you want to calculate using an spheroid just use ST_Distance(). From the docs on ST_Distance()

  ST_Distance() detects arguments in a geographic (ellipsoidal) spatial reference system and returns the geodetic distance on the ellipsoid. The only permitted geographic argument types are Point and Point, or Point and MultiPoint (in any argument order). If called with other geometry type argument combinations in a geographic SRS, an ER_NOT_IMPLEMENTED_FOR_GEOGRAPHIC_SRS error occurs.

An ellipsoid is a spheroid where two axes have the same length.


You can create a copy of the original Events table, e.g. EventAudit, with an additional field for the date the change was made and perhaps a field identifying the user who made the change, and insert old values to that table. It needn't have a primary key (although it could): EventID would be a foreign key from Events.

This design would enable reporting on changes the same way the original tasks are reported, and you can index any number of columns in the table for that purpose. You may need to consider database size/disk space used by this table; if there are any BLOBs in Events you would probably want to insert them into EventAudit only if they have changed. Otherwise I suggest copying the entire row before the change.


Query returns all values instead of the row of the one with the max value

The difference in what you linked is the asker wants the max value per a specific field (i.e. aggregate of a GROUP BY), where you rather want the whole row that contains the max value of a specific field (i.e. take the top 1 row ordered by an aggregate).
To achieve what you want you need to use a window function to order the records by the unitprice field and then filter on that the top 1 of that result set. Here's an example query to achieve this:
WITH CTE_Product_Sorted AS -- CTE of the product dataset with a SortId generated from the order of the unitprice field
(
   SELECT *, ROW_NUMBER() OVER (ORDER BY unitprice DESC) AS SortId -- Generates a unique ID for every record, in the order of unitprice, from greatest to least
   FROM product
)

SELECT *
FROM CTE_Product_Sorted
WHERE SortId = 1 -- Filter out every other record except the one with the greatest unitprice

Note when you use the ROW_NUMBER() window function, if there's a tie in the logic of the ORDER BY clause, the ordering between the tied records is non-deterministic (random). Depending on the case, this is ok, or when it's not then a unique field needs to be added to the ORDER BY clause as a fallback (e.g. ORDER BY unitprice DESC, productid ASC results in the product that was created first to win in the case of a tie by unitprice.)


MySQL JDBC does not behaves as the MySQL Workbench

Maybe the problem is that the Workbench is running your statements one at a time without you being noticed, while JDBC exactly sends to the database what you exactly ask it to send.

See this answer, where a parameter is used on the connection driver to allow many SQL statements over a single JDBC statement object.


Very slow merge replication synchronization

There are many factors that can affect replication. Some common factors that I've come across are related to slow networking, blocking and storage issues. But the one that may be a factor is the performance problem with virtual log files. If you are initializing a database with a large amount of data and the default growth factors are in place, then sql server will grow the data 1mb at a time and the log 10 percent at a time. To check for the VLF issue, run dbcc loginfo. If that command returns over 100 records, I would be concerned. If it returns thousands of records, then it will have a real impact on performance. There are plenty of articles written on this subject. The basic fix is to adjust the autogrowth settings on all data and log files to reasonable sizes, then shrink the log file and initialize it back to the original size. I would check all of the databases, including the distribution database. There could also be other reasons why the distribution database is slow to distribute the transactions. I've experienced this on several transactional replication systems in the past and was suprised at the impact of correcting this every time.

I would also suspect locking at the publisher. Identify the spids involved in the replication and use the dynamic management view sys.dm_os_waiting_tasks to determine which waits are involved in these sessions. This will help identify what these subscriptions are waiting on.


How do I force one record to have a true value for a boolean column, and all others a false value?

Note: This answer was given before it was clear the asker wanted something MySQL specific. This answer is biased towards SQL Server.

Apply a CHECK constraint to the table that calls a UDF and makes sure its return value is <= 1.  The UDF can just count the rows in the table WHERE isDefault = TRUE.  This will ensure that there is no more than 1 default row in the table.

You should add a bitmap or filtered index on the isDefault column (depending on your platforom) to make sure this UDF runs very quickly.

Caveats

Check constraints have certain limitations.  Namely, they are invoked for every row that is modified, even if those rows have not been committed yet in a transaction.  So if you start a transaction, set a new row to be the default and then unset the old row, your check constraint will complain. That is because it will execute after you make the new row the default, find there are now two defaults, and fail. The solution then is to make sure you unset the default row first before setting a new default, even if you are doing this work in a transaction.
As gbn pointed out, UDFs may return inconsistent results if you are using snapshot isolation in SQL Server.  However, an inline UDF does not run into this issue, and since a UDF that just does a count is inline-able, this is not an issue here.
The strength of a database engine's processing power is in its ability to work on sets of data at once.  With a UDF-backed check constraint, the engine will be limited to applying this UDF serially to every row that is modified.  In your use case it is unlikely that you will perform mass updates to the PostalCodes table, however this remains a performance concern for those situations where set-based activity is likely.

Given all these caveats, I recommend using gbn's suggestion of a filtered unique index instead of a check constraint.


In a relational database, there is no such thing as a "natural order".  If you care about the order of your results, you have one and only one option-- specify an ORDER BY clause.

Without looking at the query plan, I would guess that your gender table is the driving table of a nested loop join which happens to return all the male rows first.  It would be perfectly valid, though, for the order of results to change if the optimizer decides to use a different plan, if the underlying data changes, if you add and remove rows over time, etc.  If you care about the order, specify an ORDER BY clause.

Adding an ORDER BY does not necessarily force the database to do any more work.  It is entirely possible that it simply causes the database to choose a query plan that returns data in the order you want as a side effect.


You could use a CROSS JOIN from score_sort to all the students and then a LEFT join to score:

SELECT st.student_id,
       AVG(COALESCE(sc.value, default_value)) AS average_score
FROM
(
    SELECT score.subject_id, subject.name, SUM(score.value) AS score_sum
    FROM score
    JOIN subject on subject.id = score.subject_id
    WHERE subject.name != 'skip me'
    GROUP BY score.subject_id
    ORDER BY score_sum DESC
    LIMIT 5
) AS score_sort AS sort
  CROSS JOIN student AS st
  LEFT JOIN score AS sc
    ON  sort.subject_id = sc.subject_id
    AND   st.student_id = sc.student_id
GROUP BY st.student_id ;

A different approach would be to alter your query to count the available scores each student has (in the 5 top subjects):

SELECT score.student_id,
       (SUM(score.value) + (5 - COUNT(score.subject_id)) * default_value) / 5
           AS average_score
FROM
(
    SELECT score.subject_id, subject.name, SUM(score.value) AS score_sum
    FROM score
    JOIN subject on subject.id = score.subject_id
    WHERE subject.name != 'skip me'
    GROUP BY score.subject_id
    ORDER BY score_sum DESC
    LIMIT 5
) AS score_sort
JOIN score ON score_sort.subject_id = score.subject_id
GROUP BY score.student_id ;

But even with this, you would still not get any result for the students that have no score in any of the 5 top subjects.


Database model with users, roles and rights

First of all, what type of security model do you plan to implement? Role-based Access Control (RBAC) or Discretionary Access Control  (DAC)?

  RBAC in the Role-Based Access Control
  (RBAC) model, access to resources is
  based on the role assigned to a user.
  In this model, an administrator
  assigns a user to a role that has
  certain predetermined right and
  privileges. Because of the user's
  association with the role, the user
  can access certain resources and
  perform specific tasks. RBAC is also
  known as Non-Discretionary Access
  Control. The roles assigned to users
  are centrally administered.

  DAC In the Discretionary Access
  Control (DAC) model, access to
  resources is based on user's identity.
  A user is granted permissions to a
  resource by being placed on an access
  control list (ACL) associated with
  resource. An entry on a resource's ACL
  is known as an Access Control Entry
  (ACE). When a user (or group) is the
  owner of an object in the DAC model,
  the user can grant permission to other
  users and groups. The DAC model is
  based on resource ownership.

see source

1) In RBAC: you need ElementType table to assign rights to role (users are assigned to role(s)). RBAC defines: "What can this role/user do". Administrator assigns rights for roles and permissions to roles, assigns users to role(s) to access resources.
2) In DAC: users and roles have rights to elements via access control list (ownership). DAC defines: "who has access to my data". User (owner) grants permissions to owned resource.

Any way I suggest this data model:

CREATE TABLE ElementType
(
    Id (PK)
    Name
    ...
)

CREATE TABLE ElementBase
(
    Id (PK)
    Type (FK to ElementType)
    ...
)

(one to one relationship)

CREATE TABLE Element_A
(
    Id (PK, FK to ElementBase)
    ...
)

CREATE TABLE Element_B
(
    Id (PK, FK to ElementBase)
    ...
)

1) RBAC (many-to many relationship)

CREATE TABLE ElementType_To_Role_Rights
(
    RightId (PK)
    RoleId  (FK to Role)
    ElementTypeId (FK to ElementType)
    ...
)

2)  DAC (many-to many relationship)

CREATE TABLE ElementBase_To_Actor_Rights
(
    RightId (PK)
    ElementBaseId (FK to ElementBase)
    ActorId (FK to Actor)
    ...
)

CREATE TABLE Actor
(
    Id (PK)
    Name
)

CREATE TABLE User
(
    Id (PK, FK to Actor)
    Password
    ...
)

CREATE TABLE Role
(
    Id (PK, FK to Actor)
    ...
)


The linked server "lives" in the clustered SQL Server instance, so whichever node the SQL Server happens to be running on, the linked server should run fine....as long as you've installed the required ODBC/OLEDB driver(s) on each node.

Looking at that error, I'm assuming the linked server doesn't work at all on any node. I suspect you'd have the same problem on a standalone SQL Server too - it seems to me to be a driver issue. Whose OLEDB driver are you using?

I'm sure you've read the following IBM KB:
http://www-01.ibm.com/support/docview.wss?uid=swg21195578


Improving the queries in a view to query custom types

Four comments:

You should mention that you are only querying for composite types.
You don't need to join with pg_namespace, you can just cast the schema's oid to regnamespace.
You could turn the subselect into a join for potentially better performance and readability.
The NOT EXISTS clause shouldn't be in the subselect, but in the main query.


Managing Sql Server 2012 Standard Edition

You could use remote desktop to connect to the server where 2012 is installed, then run the tools from there.


Changing varchar(max) to varchar(255) requires rebuilding table

The script that SSMS generates is not the best one.

You should be able to use simple ALTER TABLE:

ALTER TABLE TableName
ALTER COLUMN ColumnName VARCHAR(255) NOT NULL

Obviously, make sure in advance that the text that you store in this column would fit into 255 characters limit.

I'd strongly recommend to try it in test environment first to get an idea how long it would take. I'm not sure, and it may depend on the version and edition of your server, but it is likely that this kind of change would be performed not as a metadata change, which means that for a terabyte of data it would take a significant amount of time.

This question (Why does ALTER COLUMN to NOT NULL cause massive log file growth?) has a lot of details about ALTER COLUMN.


How do I figure TOTAL DURATION for specific group

You just need to take the difference, in minutes, between the lowest date and the highest date, per card:

SELECT [CARD #], duration = DATEDIFF(MINUTE,
  MIN([REDEEMED DATE]), MAX([REDEEMED DATE]))
FROM dbo.table_name
GROUP BY [CARD #];


Why is a .bacpac file so small compared to a .bak file of the same database?

A

.bacpac file = Schema + Data. Note that Data is BCP'ed out using Native format (not readable by Human).

You can rename the .bacpac to .zip to see the actual contents.
You can use ...DAC\bin\sqlpackage.exe commandline to extract the .bacpac contents programatically.
It is a snapshot that includes User data + Schema from SQL Server or Azure SQL Database.

-

Backup (commonly have .bak extension) = A FULL database backup contains a complete copy of database and provides a single point-in-time to which the database can be restored. A full backup contains some transaction log records so that the restored component (database, file, or filegroup) can be made transactionally consistent.

A BACPAC is not a substitute for a FULL backup. Its just a snapshot that can be used to move a database from one server to another (or to cloud) and archiving an existing database in an open format.

From my test, below are the results


Depends on the data. Stanford's NER (GPL license) can pull out date/time, names of individuals and companies, locations, monetary amounts, in popular human languages.

A lot of data and work went into building the models, and if you need to build a custom model then you'll need a lot of data and work.


Is there any difference between 'LIMIT 0, 1' and 'LIMIT 1'?

As the documentation says:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).
With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1).

The LIMIT clause is used in the SELECT statement to constrain the number of rows in a result set. The LIMIT clause accepts one or two arguments. The values of both arguments must be zero or positive integer constants.
The following illustrates the LIMIT clause syntax with 2 arguments:
SELECT * FROM tbl
LIMIT offset, count;

Let’s see what the offset and count mean in the LIMIT clause:

The offset specifies the offset of the first row to return. The offset of the first row is 0, not 1.
The count specifies maximum number of rows to return.

When you use LIMIT with one argument, this argument will be used to specifies the maximum number of rows to return from the beginning of the result set.
SELECT * FROM tbl
LIMIT count;
The query above is equivalent to the following query with the LIMIT clause that accepts two arguments:
SELECT * FROM tbl
LIMIT 0, count;
The LIMIT clause often used with ORDER BY clause. First, you use the ORDER BY clause to sort the result set based on a certain criteria, and then you use LIMIT clause to find lowest or highest values.


combining 2 sql queries based on different conditions

That's a simple OR condition:

select *
from person
where (state = 'ALIVE' and d1 <= DATE '2015-08-08')
   or (state = 'DEAD' and d2 > DATE '2015-08-08');

The parentheses combining the AND operators are required, otherwise you'd get a wrong result.


sp_add_jobstep add more than 4000 Character in @command parameter

'This' is a varchar string.

N'This' is an nvarchar string.

I'll guess that, after @tblUpdateStats_List, the remainder of your string was more than 4000 characters.

I did some experiments (in SQL 2016), and it looks like when a varchar string is implicitly converted to nvarchar, it is converted to nvarchar(4000), not nvarchar(MAX).

I ran the following query:

DECLARE @myNVar NVARCHAR(MAX);
SET @myNVar = 'ABCDEFG';

SET @myNVar = 'QQQ' + REPLICATE(@myNVar, 1000)
             +<literal>
             +'QQQ';

SELECT LEN(@myNVar), SUBSTRING(@myNVar, 6990, 25), RIGHT(@myNVar, 20);

With <literal> initially being an nvarchar string constructed as follows:

The character 'Z', repeated 3 times
the character 'z', repeated 1027 times
the character 'X', repeated 3 times
EOL
The resulting string copied, and pasted in 3 more times (wit the fourth EOL removed)

This string was 4,138 characters long (on Windows, where EOL is CR+LF), so the total string length should have been 3 + 7000 + 4138 + 3, or 11,144 characters

When run with the literal as an nvarchar string, got the following results:

11144     ABCDEFGABCDEFGZZZzzzzzzzz     zzzzzzzzzzzzzzXXXQQQ

So, correct length, and expected values at end of replicated string, at start of literal, and at end of literal.

Then, I changed the literal to a varchar string, and got this:

11006     ABCDEFGABCDEFGZZZzzzzzzzz     zzzzzzzzzzzzzzzzzQQQ

So, length shows 3 + 7000 + 4000 + 3 = 11006 - what we'd expect if the varchar value was truncated down to just 4,000 characters. And, we don't see XXX before the final QQQ, also as we'd expect under these circumstances.

FYI - If I CAST the varchar literal to nvarchar(MAX), my results went back to the first ones, so an explicit cast avoids this problem.


How to select records with two columns matching values in one table in PostgreSQL?

Ordering by name and father_name is the first step, but I assume you don't want records where no other matching records are found. This would work:

select e1.id, e1.name, e1.father_name
  from employee as e1
  inner join employee as e2
    on e1.name = e2.name
   and e1.father_name = e2.father_name
   and e1.id != e2.id
  group by e1.id, e1.name, e1.father_name
  order by e1.name, e1.father_name

Here is a working demo.


Package Check Failed: Oracle DB 11g on RHEL 7

From My Oracle support, Doc ID 1304727.1

  Red Hat Enterprise Linux 7 - 11.2.0.4 only

  Oracle Unbreakable Enterprise Kernel:3.8.13-35.3.1.el7uek.x86_64 or higher
  Red Hat compatible kernel: 3.10.0-123.el7.x86_64 or higher

So, unless you are installing 11.2.0.4, you will have compatibility issues on RHEL7. Either install 11.2.0.4 and up on RHEL7, or use a lower compatible version.

More information:

  Red Hat Enterprise Linux 6.x  - Database 11.2.0.3 and higher

  Red Hat kernel 2.6.32-71.el6.x86_64 or later
  Oracle Unbreakable Enterprise Kernel (UEK R1) 2.6.32-100.28.5.el6.x86_64 or later


Oracle Database Server TNS Listener Remote Registration Vulnerability (CVE-2012-1675)

So a scanner program shown some nice text message without any facts (timestamp, addresses, how it determined the listener was vulnerable).
Yet you say you saw nothing on the server.

The listener logs service registrations. It also logs rejected tries with VNCR enabled.

So here is my listener without the parameter set:

[oracle@o61 ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = o61.balazs.vm)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle
[oracle@o61 ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 26-OCT-2018 22:45:22

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/o61/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=o61.balazs.vm)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=o61.balazs.vm)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                26-OCT-2018 22:45:22
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/o61/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=o61.balazs.vm)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

Now I set VNCR (not even in listener.ora, but dynamically):

[oracle@o61 ~]$ lsnrctl show valid_node_checking_registration

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 26-OCT-2018 22:46:35

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=o61.balazs.vm)(PORT=1521)))
LISTENER parameter "valid_node_checking_registration" set to
The command completed successfully

[oracle@o61 ~]$ lsnrctl set valid_node_checking_registration on

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 26-OCT-2018 22:46:43

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=o61.balazs.vm)(PORT=1521)))
LISTENER parameter "valid_node_checking_registration" set to on
The command completed successfully

From a different host, but the same subnet (which does not matter know because this level of this parameter rejects requests even from the same subnet):

[oracle@o62 ~]$ nslookup o61.balazs.vm
Server:         192.168.1.2
Address:        192.168.1.2#53

Name:   o61.balazs.vm
Address: 192.168.1.61

[oracle@o62 ~]$ nslookup o62.balazs.vm
Server:         192.168.1.2
Address:        192.168.1.2#53

Name:   o62.balazs.vm
Address: 192.168.1.62

[oracle@o62 ~]$ ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 52:54:00:fc:79:ce brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.62/24 brd 192.168.1.255 scope global eth0
       valid_lft forever preferred_lft forever

From a database instance, I try to register that instance into the listener on the other host:

[oracle@o62 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 26 22:49:12 2018

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system set remote_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=o61.balazs.vm)(PORT=1521)))';

System altered.

Then, still no services:

[oracle@o61 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 26-OCT-2018 22:49:50

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=o61.balazs.vm)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                26-OCT-2018 22:45:22
Uptime                    0 days 0 hr. 4 min. 28 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/o61/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=o61.balazs.vm)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

And in the log of the listener I see this:

[oracle@o61 ~]$ tail -10 /u01/app/oracle/diag/tnslsnr/o61/listener/trace/listener.log
Fri Oct 26 22:49:18 2018
Listener(VNCR option 1) rejected Registration request from destination 192.168.1.62
26-OCT-2018 22:49:18 * service_register_NSGR * 1182
TNS-01182: Listener rejected registration of service ""
Listener(VNCR option 1) rejected Registration request from destination 192.168.1.62
26-OCT-2018 22:49:21 * service_register_NSGR * 1182
TNS-01182: Listener rejected registration of service ""
Fri Oct 26 22:49:50 2018
WARNING: Subscription for node down event still pending
26-OCT-2018 22:49:50 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=o61.balazs.vm)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186647552)) * status * 0

As you can see the registration attempt was rejected, works as intended. I still find it difficult to believe that you configured VNCR correctly and the scanner found the listener vulnerable.

Maybe the name of the parameter was mistyped, or the listener was not restarted. Or the scanner reports a false positive for whatever reason.

Anyway you can simply test and confirm just as I did above.


I assume this is a MyISAM table. These are not crash safe. InnoDB tables on the other hand, are.

There could be several reasons, such as:

disk failure
VM reboot or crash
mysql daemon crash - open MyISAM tables are likely to crash if the mysql daemon crashes

See also: Why do MySQL tables crash? How do I prevent it?
The MySQL error log is by default located in the datadir. By default, the file name is the name of the host with the suffix '.err'.
I don't think so, not unless PHP managed to crash the mysql daemon.

You may be able to repair the table by issuing a mysql command like:

REPAIR TABLE your_database_name.noticia;

Or, from the command shell, you can run something like this to check and repair all tables:

mysqlcheck -u root -p --auto-repair --check --all-databases


Partitioning a table vs moving entries to another table

Partitioning is helpful with large tables and management purposes for your DBA.  If you have 50GB of data you should start looking into partitioning just for perfomrance alone, as Kim Tripp stated.

However, as a DBA I find partitioning great for management of large tables and reporting data that comes from a OLTP source, something which I think you are describing.  Partitioning will allow you to also create different indexes, different fill factors, and utilize indexed views on top of them to gather massive performance boosts for aggregation type functions utilizng "Indexed Views over Partitioned Tables".

In my recommendation, get familiar with partitioning.  It can save you disasters.  I once inhereted a table that was 6 billion rows and growing at 2-3GB a day with only 20GB free on the drive.  I was able to create a new partition for new data, copy out all the old data to different databases on RAID 5, and update the code to refer to views; all without client downtime in a 24/7 shop.  Partitioning is essential as data sets grow larger.


SSAS (Analysis Services) 2014 - Cumulative Update 1 for SP1

Late yesterday (7/8/2015), the Microsoft SQL Server Engineering Team confirmed that the Analysis Services hotfixes were indeed missing from the release of this CU. They intend to fix this in CU2 for SQL Server 2014 SP1.  More information in the update message on the bottom of their blog post: link to SQL Release Services blog entry


SQL Server non-clustered indexes are sorted in ascending order by default, unless you specify that they must be in descending order, when you define them.

(Also worth noting that if a table is a clustered index, that clustered index will be included in all non-clustered indexes as well.)

When it uses that index, it will usually do a seek into the index at the correct spot, using the index's B-tree structure, otherwise it will scan the entire index if the index doesn't satisfy the query.


Records creates double entry, should only be one

If you link Student-->Course-->Module, you would avoiding having Module Z causing both Course A & B to show up for that student.

Alternatively, you could slightly modify the current to have Student-->(Module & Course). If your Enrollment table includes all three identifiers, it would be trivial to determine if the student is enrolled in Module Z because they are also enrolled in a specific class, or because they are enrolled in the module directly, rather than via a class.


postgres function definition not saving top whitespace

The function body is the only part that is actually saved as string, conserving all white space, code comments etc. Stored in pg_proc.prosrc. It will be parsed etc. at execution time.

The rest of the command is executed immediately and translates to an entry in system catalog pg_proc. What clients like DataGrip later present is the reverse engineered representation derived from system catalog information. Original insignificant whitespace is completely lost in the process. The format is what the client chooses to make it. (DataGrip does not seem to do a great job in the example.)

To preserve original format, you'll have to save your create statement somewhere. Like in the COMMENT on the function - I suggest another layer of dollar quoting, or you'll have to escape single quotes:

COMMENT ON FUNCTION apqp.apqp_list_gentex_tool_report_getlist(text, integer) IS
$com$
create or replace function
apqp.apqp_list_gentex_tool_report_getlist(_omni_search text    DEFAULT ''::text
                                        , _cust_id     integer DEFAULT 0
                                         )
 returns TABLE(reason       text
             , program     text
             , mold_number text
             , rev               text
             , stone_part_number text
             , part_description  text
             , cust_request_num  text
             , status text
             )
language sql
as $$ -- and so on
$com$;

Or a code repository or a user table in the DB or whatever. DataGrip has built-in ways to save and handle files:

https://blog.jetbrains.com/datagrip/2019/01/30/working-with-files-in-datagrip/


Is there a way to do a textual search through all stored procedures for a table name?

Not 100% reliable (could yield false positives, or miss things due to dynamic SQL etc):

SELECT s.name, p.name, m.definition
  FROM sys.schemas AS s
  INNER JOIN sys.procedures AS p
  ON s.[schema_id] = p.[schema_id]
  INNER JOIN sys.sql_modules AS m
  ON p.[object_id] = m.[object_id]
  WHERE LOWER(m.definition) LIKE N'%update%tablename%';

You can't replace directly (and I think you'd have a real hard time building a catch-all replace expression anyway, since T-SQL is not exactly rigidly structured), but you can script out the stored procedures you found, replace CREATE with ALTER and run that to apply changes.

I have written a much more expansive stored procedure that I use for searching across all objects in all databases, including even jobs, and I explain several caveats and show an even better way than LOWER() to handle case sensitivity (you may want the search to be case sensitive; the above assumes you don't care and want all matches regardless of case):

https://sqlblog.org/2011/10/06/a-handy-search-procedure


CHECKSUM=1 is not an option that is supported by InnoDB.  When used with MyISAM, it causes the CHECKSUM TABLE command to run faster because the storage engine maintains the calculated checksum whenever the table data is changed, instead of having to calculate it on demand... but otherwise it doesn't actually have a purpose (such as safety, data integrity, or error detection or correction, even though it sounds like it might).

How the option originally got set is difficult to guess... either these tables were converted from MyISAM to InnoDB at some point, or perhaps you created them with some kind of graphical MySQL client that added the option to be "helpful."

These warnings should be safe to disregard, but it is easy enough to just edit the file with a quality text editor (such as Notepad++) and remove CHECKSUM=1 from each place where it appears.  If the warnings go away, that was the issue.  Removing the option should have no negative consequence, since it isn't doing anything anyway.

http://dev.mysql.com/doc/refman/5.6/en/create-table.html


How to replace a CLOB column by a varchar2(4000) column?

I would suggest adding a temp column rather than renaming the existing one. In Oracle 10g I created a test table:

CREATE TABLE  "TEST"
   (    "A" CLOB
   )

I then inserted data so that the results of length calls are as follows:

select length(a) from test

Result:

"LENGTH(A)"
12308
12308
12308
12308
12308

I then altered the table as follows:

alter table "TEST" modify
("A" VARCHAR2(4000) NOT NULL)
/

As expected receive the following error:
ORA-22859: invalid modification of columns

Change the column using the following script:

ALTER TABLE TEST ADD temp VARCHAR2(4000);
UPDATE TEST
   SET temp = DBMS_LOB.SUBSTR (a, 4000),
       a = NULL;

ALTER TABLE TEST DROP COLUMN A;
ALTER TABLE TEST RENAME COLUMN temp TO A;

Reissue the length sql:

"LENGTH(A)"
4000
4000
4000
4000
4000


Indirect constraints (foreign keys amongst three/four tables)

Your business rules will dictate your schema modeling. It is important you establish all these rules first.
If a user can belong to multiple Group (group 1 , group 2, group3) than it is the rule. If you don't want a user to be able to be inserted in a group that belongs to another company, you need to program this logic at the application layer. FK itself only checks the key exists in parent on insertion. (it does not verify for you if that user is from group 1 or 2). If you want to enforce this, i would recommend using triggers and using a userGroup table:

USER ------------(n:1)------------ COMPANY
USER ---(n:m)--- UserGROUP --(n:m)---- Group---(m:1)--- COMPANY

UserGROUP (group_id,user_id, roleId)
(PK on group_id, userId to enforce 1 user role per group)

Trigger On insert in UserGROUP:
 if user.company_id = group_company_id, then insert else "error".


Mariadb on Centos 7 not changing max connections based on my.cnf

You might find that the limit is not where you think.

I've had trouble with open file limits and that's set by Centos itself.
Please see:

How to permanently raise ulimit 'open files' and MySQL 'open_files_limit'

Some key points from that link:

The default ulimit on open files is 1024.
MySQL sets its open_files_limit to whatever the system's ulimit is set to.
MySQL cannot set it's open_files_limit higher than ulimit.

You can raise the ulimit temporarily or permanently, globally or for specific users; see the link for details.

There are more resources like that around.


How to create SQL Stored Procedure with parameters of type List

There is no type you can use with SQL Server that would let you pass in and use a list as you've suggested.

You have two options:

Dynamic SQL

Pass the list in as a string value ('(12, 34, 13, 29)'). Build a nvarchar string with your SQL statement, with that value concatenated in:

SET @stmt = N'Select *
  From Table
 Where State   in ' + @StateList + N'
    or Lob     in ' + @LobList + N'
    or Company in ' + @CompanyList

Then, EXECUTE sp_executesql @stmt to run the dynamic SQL.

Use table-valued parameters

SQL Server does allow table variables to be passed, as table-valued parameters. You can pass an 'IEnumerable' as the value in your C# code (see the docs at the link for more data). Obviously, you'd need to re-write your query:

SELECT *
  FROM Table tbl
 WHERE EXISTS (SELECT 1 FROM @StateTbl where State = tbl.State)
    OR EXISTS (SELECT 1 FROM @LobTbl where Lob = tbl.Lob)
    OR EXISTS (SELECT 1 FROM @CompanyTbl where Company = tbl.Company)
;

or

SELECT tbl.*
  FROM Table tbl
         LEFT JOIN @StateTbl st ON (tbl.State = st.State)
         LEFT JOIN @LobTbl lob ON (tbl.Lob = lob.Lob)
         LEFT JOIN @CompanyTbl co ON (tbl.Company = co.Company)
 WHERE COALESCE(st.State, lob.Lob, co.Company) IS NOT NULL
;


I work for Tokutek. The answers here are mostly good. As Justin mentions, you need the right index, and your schema probably does not have the right index. I am happy to hear TokuDB was a little faster than InnoDB, but for table scans, assuming the table is not aged, it can go either way.

Here is a talk I gave on indexing that you may find helpful: http://www.youtube.com/watch?v=vaGAoK66ctM.

The first half is on indexing, the second half is a bit of a technical dive into fractal trees on a whiteboard. Hopefully this helps you with index design. I highly recommend understanding clustering secondary indexes, which TokuDB provides.

On other points. RolandoMySQLDBA is mostly correct on how InnoDB and TokuDB perform. Here is how to think about the performance of TokuDB. While the dataset fits in memory, TokuDB's fractal trees do not have any inherent advantage over InnoDB or other B-Tree based storage engines. The bottleneck, or rather the cliff, hits when data is big and does not fit in main memory. Where InnoDB's write performance, and write performance of other B-tree based storage engines, fall off a cliff, TokuDB's performance stays level. THAT is the indicator that you are getting something out of TokuDB. TokuDB will not take some well running existing system and supercharge its performance. TokuDB will take systems work well in memory but start to break down when falling out of memory, and ensures those systems perform well as data grows. That is what is going on with benchmarks that Percona shows, that is what is going on with iiBench (http://www.tokutek.com/resources/benchmarks/#iiBench).

Combine this write performance with TokuDB's compression, and suddenly clustering indexes, as explained in the indexing talk, become relatively cheaper. Maintaining more better indexes becomes cheaper. Use better indexes, a lot of I/O from queries may dissappear, and query throughput improves. This is how one can benefit from TokuDB.


Does SQL Server 2016 support foreign keys in an in-memory table from a disk bound table

No, not at this time - A foreign key between a disk based table and memory optimized table is not allowed.
You will get below error :

Msg 10778, Level 16, State 0, Line 3
Foreign key relationships between memory optimized tables and non-memory optimized tables are not supported.


select permission was denied on the object 'sysjobs'

You use the "SQL Server Agent Fixed Database Roles" in msdb (MSDN link).

And a "how to" for SSMS: "How to: Configure a User to Create and Manage SQL Server Agent Jobs (SQL Server Management Studio)"

Note: you need a user (MSDN) in one of these roles: no need to GRANT rights to sqlagentoperator


Are there tips for writing Oracle DDL Triggers for auditing? Or should I just use AUDIT?

Why do you want to log DDL on your DB? just for auditing?
a trigger is highly not recommended because that if you have an error you can't even disable the trigger.
consider this scenario:

Trigger fails and cashes all DDL operation on the table
You try to disable the trigger
The trigger is invoked (back to step 1)

I suggest you'll take a look at Oracle DB vault and audit vault
http://docs.oracle.com/cd/E14472_01/doc.102/e14459/toc.htm

It got some really nice features for monitoring and controlling administration commands


JOIN to read first table and get possible relationship in the second table

Based on your updated question:

SELECT group_title, IF(user_id IS NULL, 0, 1)
FROM groups
LEFT JOIN (SELECT user_id, group_id
          FROM group_members WHERE user_id=1)gMembers USING(group_id)

sqlfiddle


Can't see connexion to Postgresql using php

using pg_pconnect creates a permanent connection not like pg_connect I was using.


Any DBMS with table structures and data defined separately?

If you have set up separate logins (passwords) for every tenant maybe you could consider using one "main" schema and building a database view(s) for each table filtered by condition "where login = current_user()" ("login" column must be properly handled in your inserts).
Then you can grant access to users only to views.

This way you could filter data by logged user and also you minimize your work dedicated to keeping database schema/structures up-to-date - you have to update only 2 structures: database tables and database views.

But honestly I would rather advise you to automate process of deploying changes to databases - store every change to database schema in script files and then automate executing scripts on databases  (some shell/bash scripts where parameters are database names to handle).


Stop pg_restore from returning false errors?

That is not a meaningless error: you used --create to tell pg_restore to create the database, but it is already present. This does not make a lot of sense, since with --dbname=mydb you tell pg_restore to connect to mydb to issue that CREATE DATABASE statement.

So if the database doesn't exist, you get an error because you cannot connect to it, and if it exists, you get an error because you cannot create it.

You should simply omit the --create option, then you are rid of the error.

This is all not new in PostgreSQL v12, so the error would occur with older versions of PostgreSQL as well. Also, the public schema has always been created automatically with CREATE DATABASE.


This shows you the amount of undo size used by each session that is currently active.

select s.sid,
       s.username,
       sum(ss.value) / 1024 / 1024 as undo_size_mb
from  v$sesstat ss
  join v$session s on s.sid = ss.sid
  join v$statname stat on stat.statistic# = ss.statistic#
where stat.name = 'undo change vector size'
and s.type <> 'BACKGROUND'
and s.username IS NOT NULL
group by s.sid, s.username;


In MongoDB, this is called GridFS, but s3cmd can only upload files from your operational system filesystem.
You need to make a script to query GridFS, create a temporary file, then use scm3 to upload it.

Read this article describing how to use GridFS on Node.JS: https://mongodb.github.io/node-mongodb-native/api-articles/nodekoarticle2.html

If you want to understand how to make a GridFS to S3 script, see this Perl script: https://github.com/mediaprojects/gridfs-to-s3

And if you have Perl installed, you can just run the script.


Make sure the database is actually running, and if not, start it. If you have an init script, then it's typically something like:

sudo service oracle start

This has been asked before for Oracle 11g - the answers and comments to that question may be helpful here:
ORA-01034: ORACLE not available in oracle 11g


How are multiple my.cnf's loaded by MySQL?

The standard config file format applies. It is on group and key basis.

So if you have:

[mysql]
some_variable = 1

[client]
some_variable = 3

and after another file has

[mysql]
some_variable = 2

Then some_variable under mysql will be 2 but under client it will stay 3.

  The only relevant part for MySQL server is the [mysqld] though.

The easiest way to determine which files are being read is to strace the mysql process on startup.

Start MySQL with the normal init script and run ps -ef|grep mysqld.
Copy /usr/sbin/mysqld
Then run strace on the command grepping .cnf.

You should see something like this:

$ strace /usr/sbin/mysqld --basedir=/usr --datadir=/databases/mysql/data --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysql/mysql-error.log --pid-file=/databases/mysql/data/server.local.pid --socket=/var/run/mysqld/mysqld.sock --port=3306 2>&1 |grep .cnf
stat("/etc/my.cnf", 0x7fffa6edeb40)     = -1 ENOENT (No such file or directory)
stat("/etc/mysql/my.cnf", {st_mode=S_IFREG|0644, st_size=5454, ...}) = 0
open("/etc/mysql/my.cnf", O_RDONLY)     = 3
stat("/usr/etc/my.cnf", 0x7fffa6edeb40) = -1 ENOENT (No such file or directory)
stat("/root/.my.cnf", {st_mode=S_IFREG|0644, st_size=73, ...}) = 0
open("/root/.my.cnf", O_RDONLY)         = 3
stat("/root/.mylogin.cnf", 0x7fffa6edeb40) = -1 ENOENT (No such file or directory)
stat("./auto.cnf", {st_mode=S_IFREG|0660, st_size=56, ...}) = 0
open("./auto.cnf", O_RDONLY)            = 13
stat("/root/.mylogin.cnf", 0x7fffa6edeb40) = -1 EACCES (Permission denied)


Suppress some output from PL/SQL script in Toad for Oracle

If you were running via SQL*Plus, you could use set feedback off to suppress those messages.  TOAD supports a subset of SQL*Plus commands so I'm not certain that it would support turning feedback off but I would expect that it would.

Personally, though, I'd strongly prefer moving away from a reliance on dbms_output here.  Whatever you build is going to get screwed up as soon as someone adds a couple of dbms_output debugging commands to one of the procedures that you're testing.  Plus, you're already in a database-- it seems to make a lot more sense to have a test_results table somewhere that you insert data into and then run queries against (i.e. get the count by status) once the test suite is done.  If you store those historically and throw in some timestamps, that gives you the ability to do things like look at performance changes over time and things of that nature.


SQL Server prevents me from changing compatibility level

You have SQL Server 2005 installed and not 2008. You'll need to install 2008 or newer and migrate your databases over to the new instance.


Does DBCC TRACEON() invalidate cached execution plans?

sp_configure doesn't invalidate all stored procedure plans. sp_configure used to configure certain values which do cause plans to be invalidated. For example on my test system with nothing else running, and filtering just for cached procedure plans:

--Purposely empty all buffers and cache. DO NOT RUN ON PROD!!!!!!
dbcc dropcleanbuffers;
--Just run something to put something in the cache
exec sp_who;
exec sp_who2;
exec sp_configure;

select count(1) from sys.dm_exec_cached_plans where objtype='Proc';
--returns 14
EXEC sp_configure 'media retention', '3';
reconfigure with override;

select count(1) from sys.dm_exec_cached_plans where objtype='Proc';
--returns 14

--This assumes maxdop is at the default of 1
EXEC sp_configure 'max degree of parallelism', '1';
reconfigure with override;

select count(1) from sys.dm_exec_cached_plans where objtype='Proc';
--returns 0

Which is expected as it's only the last sp_configure usage that would invalidate an execution plans (other config value changes will do this as well).

DBCC may work the same way for some Trace flags, but having run through the same test using all of the ones I usually use I can't find any that do.

If you have a particular trace flag you want to use, and a test system, then this should be easy to test using something like this:

select count(1) from sys.dm_exec_cached_plans
--this number of cached objects before turning on trace flag
dbcc traceon (3604,-1)
select count(1) from sys.dm_exec_cached_plans
--If this number is lower, then something's been removed!


Identify MySQL create version from data files

Open the .frm file with a hex editor. Go to offset 0x33 and read four bytes. Convert to decimal, keeping your endianness in mind.

Examples:

c0c30000 --> c3c0 --> 50112 --> version 5.1.12
64c50000 --> c564 --> 50532 --> version 5.5.32
b2860100 --> 186b2 --> 100018 --> version 10.0.18 (MariaDB)
19870100 --> 18719 --> 100121 --> version 10.1.21 (MariaDB)

Sources:

MySQL Internals Manual
a percona.com blog entry


Why would I NOT use the SQL Server option "optimize for ad hoc workloads"?

The SQL Server development team work on the principle of least surprise - so SQL Server generally has new features disabled in the interests of maintaining behaviour as previous versions.
Yes, optimize for adhoc workloads is great at reducing plan cache bloat - but always test it first!

Kalen Delaney tells an interesting anecdote that she asked one of her Microsoft engineer friends whether there would be circumstances where it would not be appropriate to enable this. He comes back several days later to say - imagine an application that has a LOT of different queries, and each query runs exactly twice in total. Then it might be inappropriate. Suffice to say there's not many apps like that!
If the majority of your queries are executed more than once (not exactly twice); it would likely be inappropriate. The general rule would be to turn it if there are many one-time-use adhoc queries on the database; however, there are still not many apps like that.


SELECT  *
FROM    Table t
WHERE   1 = 1
        AND EXISTS (Define criteria here)

The above is a select statement. It returns rows stored in a table.
To make this an UPDATE statement, use exactly the same syntax, except instead of using SELECT syntax, use UPDATE syntax.
UPDATE  t
SET     Column1 = 'SomeNeatValue'
        ,Column2 = 'AnotherNeatValue'
FROM    Table t
WHERE   1 = 1
        AND EXISTS (Define criteria here)

How you identify which records to select/update is on the developer. If you need assistance, I'd start at www.w3schools.com and bone up on query syntax.


SQL Server Always-On Availability Groups Patching

This is an unsupported configuration as per the docs

  Mixing versions of SQL Server instances in the same AG is not supported outside of a rolling upgrade and should not exist in that state for extended periods of time as the upgrade should take place quickly. The other option for upgrading SQL Server 2016 and later is through the use of a distributed availability group.

What does this mean practically? It might be completely fine - there may be zero compatibility issues and it could go swimmingly. It also might not. Microsoft has not tested the configuration you are running if you choose to mix versions between instances. At that point for me personally, the risks far outweigh the advantages.

It's also worth noting that using the rolling upgrade process as defined in the link I posted reduces downtime to a minimal figure anyway. If that's still not good enough, why not build two new servers and a new AG, and migrate to them, rather than patching? It's a lot more work but you should be able to minimise downtime even further.


How do I resolve this data source connection issue with Cognos?

What we ended up doing was rebuilding our environment since it was only "development" anyway. We installed DB2 Connect rather than the Runtime-Client so that way we could catalog the iSeries databases and the LUW databases for Cognos to use.

We also could have used a gateway pattern where we could have set up DB2 Connect as a "Gateway Server" and cataloged the iSeries databases there and then cataloged the databases from the Gateway Server on the Runtime Client. That would have gotten us around our original issue too.

Interestingly enough, we were steered away from using a Gateway Server when we rebuilt our environment. No reason why. Even though IBM supports it, they don't recommend it. Only thing I can think of is that it introduces another failure point.


One of the more likely reasons for this is (as you confirmed) Bulk Insert.  By default, Bulk Insert does not fire triggers.  You can change this behavior in the options for the bulk insert when your application/code is setting up the operation.

How to do that can vary by the specifics of your code, Powershell, .Net, etc.  But this other answer may be able to help.  https://stackoverflow.com/questions/12366510/trigger-not-firing-for-multirow-bulk-insert


Insert into specific table if the user session group

This look s like a job for Dynamic SQL

For this example, let's use david

STEP 01 : Look up the id of the username

SELECT id INTO @userid FROM users WHERE username = 'david';

STEP 02 : Create the SQL string to insert based on the id of username

SET @m = 'Whatever my message is supposed to be';
SELECT
CONCAT('INSERT INTO ',B.group,' (username,message) VALUES (''',B.username,''',''',@m,''')')
INTO @dynamic_sql
FROM information_schema.tables A INNER JOIN users B
ON A.table_name = B.group AND A.table_schema = DATABASE()
WHERE B.id = @userid;

STEP 03 : Compile and Execute the SQL

PREPARE s FROM @dynamic_sql;
EXECUTE s;
DEALLOCATE PREPARE s;

or in your case, just use the formatted SQL in PHP


Back in the old days (as best I recall), tables that small theoretically didn't need indexes at all. However, clustered indexes have a couple of other advantages.

Better space management - tables without a clustered index basically don't release disk space, even when rows are deleted.
Less space needed - Since a clustered index is built into the table's structure, replacing a non-clustered index with a clustered index usually frees up much of the space the non-clustered index took up.

And, of course - with 500 row tables, both of those things should be too little to really notice.

On the con side, it's possible that your non-clustered index managed to be a covering index for some queries, so the actual table didn't need to be touched to get results. So, some queries could actually be slower.

And, of course - with 500 row tables, that should be too little to really notice.

I agree with BradC's comment, however - if the table does start to grow much bigger than expected, I'd rather have a clustered index on it than not. And, if you enforce a standard of putting a clustered index on every table, then you're less likely to wind up with a heap with 400MB of data, and 300MB of unused space....


Presumably your trigger firing on UPDATE executes an UPDATE on the same table, which itself fires the triggers, and so on recursively until the stack is full and the stack depth limit exceeded error is raised.

Here's a bare bones example demonstrating the problem:

create table tst(id int primary key, col int, trigger boolean);

create function trg() returns trigger  as $$
 begin update tst set trigger=true where tst.id=NEW.id; return NEW; END
$$ language plpgsql;

create trigger t before update on tst for each row execute procedure trg();

insert into tst(id) values(1),(2);

update tst set col=1 where id=1;
ERROR:  stack depth limit exceeded
HINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.
CONTEXT:  SQL statement "update tst set trigger=false where tst.id=NEW.id"
PL/pgSQL function trg() line 1 at SQL statement
SQL statement "update tst set trigger=false where tst.id=NEW.id"

The mistake in the above case is to issue an UPDATE of the table already being updated, inside the trigger. The correct way is to assign directly NEW.trigger, as in:

create function trg() returns trigger  as
$$
 begin NEW.trigger=true; return NEW; END
$$ language plpgsql;


I've never tried this but here's some ideas I have:

You'll need to keep track of all cities, so you will need a City table.
You also need to know the distance between two cities that are directly connected to each other, so you will need a cities_distances table like this:

cities_distances
----------------
  From_City_ID
  To_City_ID
  Distance

This will let you keep track of how much distance it is from one city to another. Note that this is directional - The distance from A to B might not be the same as from B to A if the highway between them has a lane for each direction, and one lane has a detour around some natural feature (like a small lake, or difficult parts of terrain). Within a city, this is also important because you'll have one-way streets.

If you don't care about the direction of the distance between the two cities, you can just rename those two columsn CityA_ID and CityB_ID.

As for writing a query that gets the distance between any two cities, it might be easier to do that in code using Dijkstra's algorithm (because this is really just a graphing problem) than in query, but it might be possible using recursive queries. Or, you could generate a pre-calculated table that stores all this information and just look the data up in that.


How to remove multiple redundant indexes?

The question cannot be answered without seeing the SELECTs.

Here are some partial answers...

The only obviously redundant queries are pick_id and item_id_4. The rule is simple:  If all the column(s) of an index are exactly the first column (s) of some other index, then toss it.  (Note: This applies only to non-unique indexes.)
The next set of apparent duds are (is_*).  These all seem to be true/false "flags".  The Optimizer won't use such (with rare exceptions).  It will, on the other hand, consider INDEX(is_foo, ...).  That is a composite index starting with a flag.  (And, no, the low cardinality of the first column is not bad.)
5 columns in an index is pushing the max that it "should" have.  I would be tempted to keep one of the 5-column indexes starting with (item_id,module_id,item_type,is_file).  The ones I tossed would still be benefit, at some level, by the one I keep.
Reordering the columns depends on what the SELECTs look like.  If the user can request "any" combination of columns to filter, then punt.  Well, not totally punt...  Make a few combinations of 2 or 3 columns.
INDEX(a,b,c) is not the same as INDEX(a,b), INDEX(c).  Some queries benefit from one; some from the other.

See also: Cookbook on generating indexes


Mysql and money: precision problems with FLOAT(10,2)

Known problem with IEEE floats.  Use DECIMAL

To fix

add new column of correct data type
copy data over (check for conversion errors here)
drop bad column
rename new column to correct name

Why?  Murphy's Law.


How can I get performance using PostgreSQL CTE recursive?

I found the solution. I was using the recursive expression how parameter to join and it was do several lops on the table used on join, a better aprouch is before join with this table(sadt), do the join with recursive expression(downlots "table") and after, using result, do join with the sadt, with that, the query jump from 8sec to 8ms.
Follow the solution:

EXPLAIN ANALYZE SELECT sl.sadt_lot_id, array_agg(s.sadt_id)
FROM sadt_lot sl
LEFT JOIN (WITH RECURSIVE downlots as (
        SELECT sl1.sadt_lot_id, 0 AS level, sl1.sadt_lot_id as root_id
        FROM sadt_lot sl1
        WHERE sl1.parent_lot_id IS NULL
    UNION
        SELECT sl2.sadt_lot_id, d.level + 1, d.sadt_lot_id as root_id
        FROM sadt_lot sl2
        INNER JOIN downlots d ON d.sadt_lot_id = sl2.parent_lot_id
)SELECT * FROM downlots) d ON d.sadt_lot_id = sl.sadt_lot_id
LEFT JOIN sadt s ON s.sadt_lot_id = d.root_id
WHERE sl.parent_lot_id IS NULL
group by sl.sadt_lot_id
ORDEr By sl.sadt_lot_id

Query Plan

Sort  (cost=1935.35..1935.56 rows=82 width=36) (actual time=8.230..8.234 rows=82 loops=1)
Sort Key: sl.sadt_lot_id
Sort Method: quicksort  Memory: 75kB
->  HashAggregate  (cost=1931.72..1932.74 rows=82 width=36) (actual time=8.085..8.197 rows=82 loops=1)
    Group Key: sl.sadt_lot_id
    ->  Hash Right Join  (cost=469.73..1839.25 rows=18493 width=8) (actual time=0.328..6.273 rows=10742 loops=1)
          Hash Cond: (s.sadt_lot_id = downlots.root_id)
          ->  Seq Scan on sadt s  (cost=0.00..645.78 rows=12678 width=8) (actual time=0.007..1.406 rows=12493 loops=1)
          ->  Hash  (cost=465.72..465.72 rows=321 width=8) (actual time=0.242..0.242 rows=82 loops=1)
                Buckets: 1024  Batches: 1  Memory Usage: 12kB
                ->  Hash Right Join  (cost=432.42..465.72 rows=321 width=8) (actual time=0.049..0.232 rows=82 loops=1)
                      Hash Cond: (downlots.sadt_lot_id = sl.sadt_lot_id)
                      ->  CTE Scan on downlots  (cost=428.41..444.05 rows=782 width=12) (actual time=0.007..0.167 rows=96 loops=1)
                            CTE downlots
                              ->  Recursive Union  (cost=0.00..428.41 rows=782 width=12) (actual time=0.006..0.143 rows=96 loops=1)
                                    ->  Seq Scan on sadt_lot sl1  (cost=0.00..2.99 rows=82 width=12) (actual time=0.004..0.018 rows=82 loops=1)
                                          Filter: (parent_lot_id IS NULL)
                                          Rows Removed by Filter: 14
                                    ->  Hash Join  (cost=4.23..40.98 rows=70 width=12) (actual time=0.030..0.031 rows=5 loops=3)
                                          Hash Cond: (d.sadt_lot_id = sl2.parent_lot_id)
                                          ->  WorkTable Scan on downlots d  (cost=0.00..16.40 rows=820 width=8) (actual time=0.000..0.002 rows=32 loops=3)
                                          ->  Hash  (cost=2.99..2.99 rows=99 width=8) (actual time=0.069..0.069 rows=14 loops=1)
                                                Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                                ->  Seq Scan on sadt_lot sl2  (cost=0.00..2.99 rows=99 width=8) (actual time=0.004..0.061 rows=96 loops=1)
                      ->  Hash  (cost=2.99..2.99 rows=82 width=4) (actual time=0.039..0.039 rows=82 loops=1)
                            Buckets: 1024  Batches: 1  Memory Usage: 11kB
                            ->  Seq Scan on sadt_lot sl  (cost=0.00..2.99 rows=82 width=4) (actual time=0.014..0.028 rows=82 loops=1)
                                  Filter: (parent_lot_id IS NULL)
                                  Rows Removed by Filter: 14
Planning time: 0.225 ms
Execution time: 8.300 ms


SQL 2014 Availability group of Same DB produces different size Backup from Node A to Node B

In AGs writes can only occur on the primary. Since the AlwaysOn Availabiliy Groups feature was introduced, we got new options to make the backups strategy more complete, but also more complex. Taking an advantage of secondary replicas, we can offload both, the FULL and even the Transaction Log backups from the Primary Replica to the Secondary, leaving the Primary replica dedicated to serve the production application.

For example i am attaching here a 'Backup preferences' Window screen for your better understanding.

Note

1)Full and differential backups always run on the primary replica.
2)Full copy only and transaction log backups run either on the primary replica, if the primary replica is set as the preferred replica for running backups, or on the secondary replica with the highest backup priority.
3)For successful backup of availability databases, verify that the secondary replica can communicate with the primary replica, and that the replicas are either Synchronized or Synchronizing.

As per mssqltips blog documentation  Here

Microsoft has provided us with a system function called sys.fn_hadr_backup_is_preferred_replica. This function will return 0 if the current instance is not the preferred backup location or 1 if it is the preferred replica for backups according to the preference and priority settings

IF sys.fn_hadr_backup_is_preferred_replica(@DatabaseName) = 1
BEGIN
  --Perform backup
END

The AG behavior of the “Backup Priority” setting:

The replicas with the lower value will be in the end of the queue.
Those replicas will only be used if no other is available.
The replicas with the higher priority value will be on the top of the backup replica choice.
In the case of have two or more replicas with the same priority value (weight) SQL Server will give priority based on the alphabetical order.

For Your further refence Understanding backups on AlwaysOn Availability Groups & Here


How can we configure the Streaming Replication(master-slave) on single SAN storage in PostgreSQL?

I try to answer below, but:

DON'T DO THIS!

You are planning a disaster recovery setup, but when the disaster hits the shared storage (the SAN), you will stand there with no usable database.

You can do that, yes, just as @a_horse_with_no_name describes in his comment.  You have to have two separate data directories for it, the master pointing to one and the slave to the other.  Setting up the replication is the same as with completely separated machines.

Also, plan regularly taking and testing backups as part of your disaster recovery process.


Could I get some feedback on my first normalisation design?

Community wiki answer:

A given athlete can play in multiple sports, so you should probably separate that. Also I do not understand the ranking part attached to countries. This should be at best a view, not a table.

As a general advice, do not store ages for individuals, but date of birth, because this does not change while the age changes each year.

You should just store the result (position) number for each athlete and competition, not medals that will be derived from it.

You also need to take into account ex-aequos. Also, even for a given competition, a given athlete may participate in multiple steps (semi-finals, final, etc.) so you may need to cater for that.


I strongly suspect this is not an issue of MySQL "not checking" the username, but rather permissions which are too broad.

Start with SELECT * FROM mysql.db WHERE Db = 'yourdb'; and ensure that only the desired permissions are set for your DB.


Multiple Schemas for increasing MySQL (5.7) Performance

PROBLEM #1
You innodb_thread_concurrency is too small. SUGGESTION: It should either be 0 or 64. Please read my post from Aug 14, 2017 (MariaDB 10.1.22 to use more RAM rather than CPU) on why.
PROBLEM #2
I don't see innodb_read_io_threads and innodb_write_io_threads mentioned. By default, both are 4. SUGGESTION: I would double both to 8 (Percona Server's default). Please see my post from Jun 20, 2013(MySQL using too much CPU).
PROBLEM #3
Why is your innodb_log_file_size 30G ? InnoDB Redo Logs are written sequentially. IMHO, writing sequential files on an SSD is not that great (See my post from Feb 06, 2014 : MySQL on SSD - what are the disadvantages? and Sep 25, 2014 : What is the best storing device for DB transaction log?). SUGGESTION #1: You could configure InnoDB to have the Redo Logs on a separate disk. SUGGESTION #2: Please make innodb_log_file_size 1G for now. Then, go figure out the proper size by reading my post from Jan 17, 2013 (MySQL 5.5 - Innodb - innodb_log_file_size higher than 4GB combined?) where I point to two posts from Percona's mysqlperformanceblog:

Jun 03, 2006 : Choosing proper innodb_log_file_size
Nov 21, 2008 : How to calculate a good InnoDB log file size

GIVE IT A TRY !!!
As for the one off-topic: Server with 16GB RAM and LA of 8.00 ??? Of course, not !!! Try these suggestions and see if it really helps.


Your best bet is to use :

Discover, Diagnose, and Document ALL Your SQL Servers On Your Coffee Break using SQL Power Doc written by  kendal vandyke

This is written in Powershell.


Minimum value on most recent date per account MS ACCESS SQL

This would be a good place to use window functions if your RDBMS supports them. The example below is T-SQL so if you are not on SQL Server you may have to adapt the syntax and/or keywords.

Window Functions are ANSI Standard starting with SQL:2003, and as such you likely have access to them with any modern RDBMS. If you are on SQL Server read about their implementation of Window Functions here.

SELECT CHACCTBS, CHRQDATE, CHSCORE
FROM
(
    SELECT
         MIN(CHSCORE) OVER(PARTITION BY CHACCTBS) As CHACCTBS
        ,CHRQDATE
        ,CHSCORE
        ,ROW_NUMBER() OVER(PARTITION BY CHACCTBS ORDER BY CHRQDATE DESC, CHSCORE) As Limiter
    FROM RiskScore
) X
WHERE Limiter = 1

The subquery returns your MIN and other columns, and creates a filter by assigning row numbers by your partition and ordered by descending dates. You then take the first row of each partition in the WHERE clause.


If your database has 16 MB of free space, and the drive it's on has plenty of free space, then don't worry about it. As long as there is sufficient space and you haven't disabled the database's ability to autogrow, then the database will grow the data file when it needs to. Of course you should set your data file's autogrow to some realistic increment, balancing duration it takes to grow with the frequency it will have to grow. The defaults of 1MB or 10% are not realistic at all, and should have been changed ages ago. If you have instant file initialization enabled you can lean towards larger, but I have no idea what a good balance for you might be.

You can check autogrow settings here:

SELECT name, type_desc, size, growth, is_percent_growth
  FROM [database_name].sys.database_files;

Ideally you will create the database with sufficient size to avoid autogrowth at all, since this operation can be expensive (especially if you don't have instant file initialization) and can block all other activity for the duration of the growth event. You may want to manually expand the file now or during a maintenance window to avoid this happening during peak activity. So proactively you can do this (e.g. to change the auto growth setting to 100 MB):

ALTER DATABASE [database_name]
  MODIFY FILE (NAME = N'database_name_data', FILEGROWTH = 102400KB);

If your drive has 16 MB of free space, then you need to free up some space somehow, or move the database to a drive that has more space. It's possible that the drive has been taken up by an overgrown log file, probably because you're in full recovery model and have never taken a log backup. In this case you can either:

Switch to simple recovery mode, run CHECKPOINT, and then DBCC SHRINKFILE to make the log a reasonable size. Resist the temptation to shrink the file to 1 MB; you don't want to get into a tug-of-war with your disk space.
Stay in full, then BACKUP LOG, and then DBCC SHRINKFILE with the same caveats as (1).

Now, it could also be that you're in simple mode and the log has ballooned to support some atypically large transaction. So you may still be able to do (1) without any worry, but if you're going to be running these large transactions again, or not doing (1) or (2) regularly, then you're only "solving" the problem temporarily. Get a bigger disk or use full recovery model and manage your log much more effectively.

That all said, this is just a guess; maybe your drive is actually being taken up by a large (and useless) page file, or your MP3 collection, or videos from your last vacation.