pcp_attach_node not working in pgpool 2 I've been working on pgpool2 setup with 2 DB node and I need to attach on the node, which os currently down. when I run

postgres@pg-pool:~$ pcp_attach_node -p 9898 -n 1 -U postgres

I get following error.

  pcp_attach_node -p 9898 -n 1 -U postgres pcp_attach_node: error while
  loading shared libraries: libpcp.so.1: cannot open shared object file:
  No such file or directory

I am using:

- pgpool-II-4.0.1
- ubuntu 18.04 LTS
- postgresql 10

I have installed:

postgresql-contrib
gcc
make
libpq-dev

pgpool installation:

./configure
make
make install

Contents of /usr/local/lib

-rwxr-xr-x  1 root root     959 Mar 18 07:55 libpcp.la*
lrwxrwxrwx  1 root root      15 Mar 18 07:55 libpcp.so -> libpcp.so.1.0.0*
lrwxrwxrwx  1 root root      15 Mar 18 07:55 libpcp.so.1 -> libpcp.so.1.0.0*
-rwxr-xr-x  1 root root  244296 Mar 18 07:55 libpcp.so.1.0.0*

Your help would be great

As @pifor mentioned the issues was $LD_LIBRARY_PATH . Following code has fixed my issue.

Thank you @pifor

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib


Permissions Needed To Attach AdventureWorks Database I am trying to add the AdventureWorks database in MS SQL Server Management Studio (SSMS) 2008 R2. As a new person to this area, or noob, this seemingly simple task has caused me much hardship and grief.

Initially I have tried: Right clicking on Databases, 'attach'... which immediately gets me the error:

Cannot show requested dialog.
Additional information:
Parameter name: nColIndex Actual value was -1. (Microsoft.SqlServer.GridControl)

So I've looked up other ways to add this database. I realise that there are many questions similar to the one I will ask below. I have tried their solutions to no avail, including:

Running SSMS as administrator (right clicking on program, and 'Run as Administrator'...). I do the same process as above, and get the same error as above.
Running an SQL script (by writing the script after clicking 'New Query'),

EXEC sp_attach_db    @dbname = 'DBName',
             @filename1='F:\AdventureWorks2008_Data.mdf',
             @filename2='F:\AdventureWorks2008_Log.ldf'

With (2), I get the error:

Msg 262, Level 14, State 1, Line 1
CREATE DATABASE permission denied in database 'master'.

Based on some websites (like this), the error is caused when there's a Permissions issue. But as I understand, I am an administrator.

The server I log onto is the local one, so the Server name is the same as my User name (through Windows Authentication), plus "\SQLEXPRESS".

Why can't I add the AdventureWorks database? What am i doing wrong? How can I get it working?

You have not given your admin user the correct permissions yet. You must explicitally tell SQL Server that this user is trusted. Running the program as an admin gives the program itself the highest level of permissions, but does nothing for the authentication process when executng a query.

To add your administrator account as a SQL Administrator:

Open 'SQL Server Surface Area Configuration'  (Under Start -> MS SQL Server -> Configuration Tools)
Click "Add New Administrator"
Move the user from the left to the right, then save changes.

In 2008 and later, follow the instructions given here.


MySQL/MariaDB stored procedure compiles with typos I just asked a similar question to this, but that turned out to be because MySQL column names are case insenstive and I was creating an ambiguity. I took the advice given and prefixed all my params with p. The problem is that if I type the param value with a misspelling the SP still compiles, but then fails at run time. Is there something I can do about this?

Here is an example:

DELIMITER $$

USE `MobiFit_Dev` $$

DROP PROCEDURE IF EXISTS `User_Signup` $$

CREATE DEFINER = `root` @`localhost` PROCEDURE `User_Signup` (
    pEmail VARCHAR (250),
    pHashedPassword BINARY(60),
    pFirstName VARCHAR (100),
    pLastName VARCHAR (100),
    pGender ENUM ('Male', 'Female'),
    pDateOfBirth DATE,
    pHeight DECIMAL (3, 2),
    pCurrentWeight DECIMAL (4, 1)
    -- CALL User_Signup('lee@gmail.com', 'password', 'Lee', 'Brooks', NULL, NULL, NULL, NULL);
    -- CALL User_Signup('lee@gmail.com', 'password', 'Lee', 'Brooks', 'Male', CURRENT_DATE(), 1.85, 101.3);
)
BEGIN
    DECLARE vRoleId INT;

    SELECT
        id INTO vRoleId
    FROM
        Role
    WHERE `Code` = 'CUSTOMER' ;

    INSERT INTO USER (
        Email,
        HashedPassword,
        RoleId,
        FirstName,
        LastName,
        Gender,
        DateOfBirth,
        Height,
        CurrentWeight,
        CreatedAt
    )
    VALUES
        (
            pEmail,
            pHashedPassword,
            vRoleId,
            pFirstName,
            pLastName,
            pGender,
            pDateOfBirth,
            pHeight,
            pCurrentWeigh, << Note the typo, this still compiles
            UTC_TIMESTAMP()
        ) ;
END $$

DELIMITER ;

The explanation here is related to the same situation found in the other question... the expression here is at least potentially ambiguous, so it can't reasonably be validated at define time.  It could refer to something that's not yet defined.

MySQL validates the syntax of stored procedures at define-time, but it doesn't validate anything that's potentially ambiguous or things that could become valid by the time the program is actually running.

In a stored procedure:

-- unambiguous

SET foo      -- definition rejected if 'foo' is not a valid local or system variable
    = bar;   -- definition rejected if 'bar' is not a valid local variable

-- potentially ambiguous, probably intended to be a column name if 'foo' isn't a variable
-- could be a reference to a not-yet-created or altered base or temp table

UPDATE buzzfizz SET foo = bar; -- checked for syntax only, server assumes you know best

The tables referenced in a stored procedure don't even have to exist when the procedure is declared, or could have a different structure when the procedure is run... after all, you can create tables (base tables and temporary tables) within stored procedures.  If a procedure could only be declared against already-existing objects, this would be a significant lack of flexibility.  You can also "mask" a base table by creating a temporary table with the same name, which would have messy implications here.

Stored functions and events abide by the same general rules, although things are a little different there since these programs do have differences from procedures... for example, you can't create a base table within a stored function, because stored functions aren't allowed to implicitly or explicitly cause a COMMIT.

Triggers are somewhat different, because they are parsed and cached as soon as they are declared, so if their definition doesn't match the table they're defined against (i.e., referencing a nonexistent column in the table via the NEW or OLD alias), the trigger is rejected... but references to other tables are not validated, since the other table might not yet exist and things that could be "column names" could indeed refer to columns or could refer to be other objects.

Views are somewhat different.  They're validated at define time, and the referenced objects have to exist.  That's why mysqldump generates SQL statements to create a dummy table with the same name and approximately the same column definitions as each view, only to later cause those tables to be dropped and replaced with the view itself.  If not for this, views couldn't be restored from a backup if they referenced other views that did not yet exist.  You can change the underlying objects out from under a view, which will cause an error, later when you try to access the view... but you can repair a view that you've broken in this way by putting the table back into its original condition.  Views, of course, have no local variables and can't create or alter tables or manipulate transactions.


Oracle Standby Database Techniques for faster application of redo logs I am quite new with the concept of standby databases in Oracle and I am not really a DBA, so help me out.

I have been reading a bit on the concept of Oracle standby databases and I am pretty much interested in logical databases. Currently, we have a potential significant slowdown in our standby database during our system's weekly DB maintenance.

During this maintenance, millions of records (usually the oldest records) will be deleted from several tables that have a number of records ranging from tens of millions to hundreds of millions. Usually, these records will be deleted using a single delete statement with a where clause referring to the oldest records.

From my understanding of how logical standby databases apply redo logs (do correct me If I am wrong), redo logs are process to create transactions which will then be applied to the standby database. So, when batch deleting millions of records in the primary database, what will happen in the standby database is something like a sequential deletion of these millions of records, which in turn is quite slow.

This leads me to my question. Can you share some thoughts/ideas on how to speed up the application of redo logs from the primary database to the standby database to avoid the issue I have raised above? The solution could be a different standby database configuration or a change in schema structures or a change in the way records are deleted.

Note that our tables should not be partitioned and are well-indexed. Standby database should be logical to enable querying for report generation.

Thanks for sharing your thoughts.

I am not very proficient with this topic but I think there are the following options, the first three of them need extra
licenses. You should to check if my recommendations contradict your DR requirements.

The problem of of the logical dataguard is that one SQL statement on the primary server can cause a lot of SQL statement
on the standby server.

Example: the statement

delete from mytable where entry_date <=to_date('2014-01-01','YYYY-MM-DD')

may cause the deletion of 1 000 000 rows in the primary database in a more or less efficient way. This statements causes
the execution of 1 000 000
statements on the standby side of the form

delete from mytable where id=:id

where id is the primary key field of mytable. This kind of deletion is the most imperformant way to delete rows.

If one uses s physical dataguard the situation is much better: the redo records transmitted to the standby contains the
change records that must be written to the datablock
and the standby applies these changes. This is a lot of less activity on the standby side.

You should run the dataguard in an asynchronous mode (maximum performance mode) so that the SQL-execution on the standby side
does not influence the performance on the primary site.
There is a parameter in sql_apply, PRESERVE_COMMIT_ORDER, which is TRUE by default and can be set to FALSE to simplify
log apply.

Buy additional licenses:

Use partitioning and truncate partitions instead of deleting rows.
If  if  is possible to organize your database in such a way then this is a very good solution because the truncation of a
partition does generate almost no redo information. On the standby side the truncate command will be executed in the same
way as on the primary side. An additional license is necessaary to use partitioning.
use physical standby with active datafguard.  Active dataguard allows running the database in read only mode while redos
are applied. a physical standby is more performant as a logical one as explained above.
An additional license is necessary to use active dataguard.
You can combine 1.1) and 1.2). This will be the most performant way.

create a solution using physical standby
If you don't want to bou additional licenses you can tinker around with your own solution.

mimic active dataguard: mix "open read only / apply redo" phases with your physical dataguard: snapshot database

apply the redo to your standby database
stop redo apply
open it as snapshot database
do your reporting
close your database
convert back to physical standby and continue with a)

you can improve solution 2.1): before step  "apply the redo to your standby database" that you have gathered during the reporting phase you
can make an incremental backup (with respect of the scn of the restore point) from the primary and apply this to the
standby site this may save you a lot of redo apply.

improve your sql apply solution
I assume that you have one table that is heavily affected by your batch job. I call it mytable.

before you start the batch process exclude mytable from the sql apply process. after your job has finished
reinstantiate the table and include it in sql apply again. This makes sense if the percentage of deleted rows in the
table is rather high.
exclude mytable from sql apply and replicate the data of mytable to the primary database  by other methods. This
replication method should handle the deletes of the batch job in a suitable way. One possibility maybe Advanced
Replication, especally procedureal replication. Another may be oracle Streams.


Kill CLR stored procedure process I have a CLR stored procedure which exports a large table into a text file.

The procedure works fine but. When I manually stop its execution from SQL Management Studio I cannot delete the file it created ().

  Error Deleting File or Folder Cannot delete XXXXXXXXXXX: It is being
  used by another person or program. Close any programs that might be
  using the file and try again.

I looked for a SQL process (with sp_who2 active) and for a Windows process (with tasklist) on the SQL Machine but I can't identify the process that keeps the file locked.

Please tell me how to kill the process (without restarting SQL server)

You can use handle.exe from SysInternals to see which process has a handle open to your file. But I wouldn't be too surprised if it indeed is the SQL Server process itself (as others have noted in the comments).

First make sure, that your procedure actually closes the file correctly, when being canceled.

You didn't show the code for the procedure, but it is not too far fetched to assume, that you don't close your file (stream) properly in the face of an exception - which is what your procedure will get from SQL Server when being interrupted by a user (more specifically a ThreadAbortException).

Note though, that even if you do handle this correctly, there are still possibilities (e.g. error handle takes "too long") for this cleanup code to fail or not execute to completion, hence leaving the file open.

You may want to check this blog entry for further information.


mysql: unknown variable 'table_definition_cache=400' I added lines to ~/.my.cnf which worked fine for weeks. Now I receive complaints when I run myqsl CLI.

System specs:

MAC OS X - El Capitan 10.11.13

Mysql Version:

mysql  Ver 14.14 Distrib 5.6.29, for osx10.11 (x86_64) using  EditLine wrapper

Contents of ~/.my.cnf:

[client]
user = root
password = ***REDACTED***
host = 127.0.0.1

table_definition_cache = 400
table_open_cache = 400

These are the error messages I get when I type 'mysql' in bash:

  mysql: unknown variable 'table_definition_cache=400'

  mysql: unknown variable 'table_open_cache=400'

I only get the second error message if I comment out the first setting.

I needed these settings because otherwise I get these errors intermittently:

Lost connection to MySQL server at 'sending authentication information', system error: 32

UPDATE:

This link suggested the solution: https://stackoverflow.com/questions/17813630/mysql-5-6-headaches-on-mac-osx

I needed to put the settings in the [mysqld] section of the config file.

In order to solve this please put these configurations under [mysqld] section of config file. For reference check here

[mysqld]
table_definition_cache = 400
table_open_cache = 400


Stack trace of oracle sql execution Is there a way to get the stack trace of the SQL query that is given in awr, I mean a way to check the flow of the query from which package or procedure that it has been triggered? tkprof and awr both show only the SQL and the elapsed time, parse and fetches.

You mentioned AWR, so you are licensed to use ASH also. You can find the session(s) executing the specific SQL in DBA_HIST_ACTIVE_SESS_HISTORY view based on the sql_id. The PLSQL_ENTRY_OBJECT_ID and PLSQL_ENTRY_SUBPROGRAM_ID columns contain information about the PL/SQL call stack.

This however may not be complete if you have several levels of PL/SQL packages, procedures or functions built on top of each other.

If you know the sql_id and that the SQL statement will be executed in the future, you can set a trace event to dump an errorstack for this specific statement (which can cause quite big overhead and create huge trace files, but I could not come up with a more effective method).

Given the below example:

create or replace package mypackage as
  procedure myprocedure;
end;
/

create or replace package body mypackage as
  procedure myprocedure as
    x number;
  begin
    select count(*) into x from user_objects;
  end;
end;
/

execute mypackage.myprocedure;

select distinct sql_id, sql_text from v$sql where lower(sql_text) like '%user_objects%'  and lower(sql_text) not like '%v$%';

SQL_ID        SQL_TEXT
------------- ---------------------------------
4fjk3rakk6x48 SELECT COUNT(*) FROM USER_OBJECTS

You can set the below event for the above SQL:

alter system set events 'sql_trace[SQL: 4fjk3rakk6x48] errorstack(1)';

The next time this SQL statement executes, the trace and errorstack dump will be triggered:

execute mypackage.myprocedure;

The trace file created by this will contain the PL/SQL call stack like this:

----- Error Stack Dump -----
----- Current SQL Statement for this session (sql_id=4fjk3rakk6x48) -----
SELECT COUNT(*) FROM USER_OBJECTS
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x870c5950         5  package body BP.MYPACKAGE
0x8706e1b8         1  anonymous block

Above shows that there was an anonymous block calling BP.MYPACKAGE at the 5th line, which is exactly the position of the SQL statement in procedure MYPROCEDURE.


Does column size increase break SELECT *? I searched a little bit on this topic before realizing it is somewhat tricky (for me, at least) to come up with an effective search phrase for it because it overlaps with other common topics.

So here is my question spelled out:

Suppose I have a varchar column that is going to be altered (increased in size).

And I also know that there is code (I know this is bad practice! but that code is what it is at this stage - lobbying developers to change it is a later step) that uses SELECT *.

Will the altered column break queries that use SELECT *, or will that code continue to function?

Thanks in advance for any help.

jrdevdba

"Maximum row size for mysql" is the terminology you are probably looking to search for - that finds a useful stack overflow page at https://stackoverflow.com/questions/10349361/mysql-how-to-workaround-the-row-size-limit-of-66-kbytes

That suggests a maximum row size (excluding blob types which are stored off-page) of 64Kb, and has a couple of useful looking links with more detail.


Grouping by sequential relationships with Postgres This is quite similar to this question about continuous ranges, and this question about grouping by sequential numbers, but differs in that the sequences are not numeric. Given the following relationships as key pairs

a -- b -- c      e -- f -- g
     |   /
     |  /
      d

This is the table with example data (also on SQLFiddle):

CREATE TABLE relationships (
   name varchar(1),
   related varchar(1)
);

INSERT INTO relationships (name, related) VALUES
('a', 'a'),
('a', 'b'),
('b', 'b'),
('b', 'a'),
('b', 'c'),
('b', 'd'),
('c', 'c'),
('c', 'b'),
('c', 'd'),
('d', 'd'),
('d', 'c'),
('d', 'b'),

('e', 'e'),
('e', 'f'),
('f', 'f'),
('f', 'e'),
('f', 'g'),
('g', 'g');

What is the most efficient way to produce an output that looks like this:

| group |    members    |
------------------------|
|   1   |  {a, b, c, d} |
|   2   |  {e, f, g}    |

or this:

| name |  group |
-----------------
|  a   |   1    |
|  b   |   1    |
|  c   |   1    |
|  d   |   1    |
|  e   |   2    |
|  f   |   2    |
|  g   |   2    |

I have thought about doing this operation outside of Postgres, but it seems like there must be a way to achieve this result with either a window function or PL/pgSQL.

Ugly, but it works!

First, define array_agg_mult from this question

CREATE AGGREGATE array_agg_mult (anyarray)  (
    SFUNC     = array_cat
   ,STYPE     = anyarray
   ,INITCOND  = '{}'
);

and then run the query

WITH summary AS (
  SELECT name, array_agg(related) AS touches
  FROM relationships
  GROUP BY name
),

grouped AS (
  SELECT name, (
    SELECT array_agg(uniques) FROM (
      select distinct unnest(array_agg_mult(sub.touches)) AS uniques
      ORDER BY uniques
    ) x
  ) my_group

  FROM summary LEFT JOIN LATERAL (
    SELECT touches
    FROM summary r
    WHERE summary.touches && r.touches
    GROUP BY name, touches
  ) sub ON true
  GROUP BY summary.name
  ORDER BY summary.name
)

SELECT DISTINCT my_group, row_number() over() as group_id
FROM grouped
GROUP BY my_group;

Which produces the following:

| my_group  |  group_id |
| {a,b,c,d} |      2    |
| {e,f,g}   |      1    |

SQLFiddle here - http://sqlfiddle.com/#!15/c8a5b/20. I'm a novice with this kind of query, so please let me know if there is a more efficient way to do this!


Creating an index of wildcard DNS names I want to have a table column which will be filled with wildcard-type DNS entries, like

*.example.org
*.popularisp.net

such that I can make fast queries against actual reverse DNS hostnames, matching foobar.example.org to the first one above and 12-34-56-78.routers.popularisp.net to the second, for instance.

What is the best way to achieve this in a PostgreSQL index?

as dns domains are parsed from the end, it's useful to also store them that way - e.g:
For bar.foo.co.uk you'll keep uk.co.foo.Bar.

It makes both indexing and prefix searches simple and fast.
In the example above you'll search for uk.co.foo.% - a simple search for regular index.

Note: it's the same way many directory (ldap,dns, etc. ) searches work.


Total Memory capped far below available memory We are testing out a new install of SQL Server 2012 on a virtual server and the memory allocated to SQL Server seems to be capped around 16 GB even though much more is available. What could cause this?

I've tried bumping up the SQL minimum server memory setting, but the change had no effect. I've listed some of the server details below.

SQL Server 2012 Enterprise
Windows Server 2008 R2 Enterprise x64
29.93 GHz (8 processors)
77 GB RAM
Min Server Memory: 48 GB
Max Server Memory: 56 GB
Database size: 44 GB

PerfMon info:

Working Set: ~17 GB
Page life expectency: ~7,500
Target Server Memory: ~73 GB
Total Server Memory: ~15.7 GB

SQL Server is not guaranteed to allocate the min server memory. If the load on the server never needs the minimum SQL Server will work with less - BOL http://msdn.microsoft.com/en-us/library/ms178067.aspx

As long as your configurations allow for the use of all memory you have allocated (they do), SQL Server will use what it needs, and generally no more. Since you are running a VM I would make sure you allocate 10% for the OS, remove the minimum as it can cause performance problems and let it run. If your CPU is pegged at 100%, queries are slow, and SQL Server is not allocating all of its available memory then it would be time to further troubleshoot.


How to check a database for a specific page I am working on repairing a database, I have about 80 errors that I need to fix and there seem to be three types of problems that I don't know how to fix other than doing a REPAIR_WITH_DATA_LOSS.

I do have a previous backup which does not have issues, and I have read about doing page level restores. However in my attempts to do that so far I have been unsuccessful using SSMS and TSQL. Is it possible to query the "good" database to see if it contains these pages? For example can I run something to look for page "1:10429855"?

Also is there a way to see what that same page is associated to or is on the "bad" database?

  Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID
  -4342159331760996352 (type Unknown), page (31205:-867600381). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 2057 and -4.

  Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID
  6691734499630645248 (type Unknown), page (23723:203812287). Test
  (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 2057 and -11.

  Table error: Object ID 1518653945, index ID 1, partition ID
  72057594166444032, alloc unit ID 71875645566156800 (type LOB data).
  The off-row data node at page (1:10429953), slot 0, text ID
  954357841920 is referenced by page (1:10429855), slot 4, but was not
  seen in the scan.

  Object ID 1518653945, index ID 1, partition ID 72057594166444032,
  alloc unit ID 72057594253410304 (type In-row data): Errors found in
  off-row data with ID 954358824960 owned by data record identified by
  RID = (1:10488687:10)

To get information about a particular page in SQL server, you'll need to use the undocumented DBCC PAGE command. Or if you are using SQL Server 2012 and higher, you can also query the sys.dm_db_database_page_allocations DMV.

Regarding problems with page restores, unless you are on Enterprise Edition, page restores have to be performed OFFLINE. (i.e. you must not RECOVER the database and perform the log restore or full restore with the NORECOVERY option which will leave the database in a state that allows page restores in either edition)

Here's a quick blog post I found that introduces the sys.dm_db_database_page_allocations DMV: SQL SERVER 2012 – sys.dm_db_database_page_allocations – dynamic management function

For a quick overview of the DBCC PAGE command, you can read Paul Randal's blog post about it: How to use DBCC PAGE

And an introduction for how to do page restores in Enterprise and Standard edition: How to Restore a Page in SQL Server Standard and Enterprise Edition


What do I have to do in order to be able to receive notifications? In the event of a database error in an application, what do I have do in order to be able to receive notifications?

Should I create an alert in SQL Server and use the RAISERROR command within the application?

I'd say that's a pretty way to go about it.  You can create a new user-defined error message using sys.sp_addmessage, which will in turn store it in sys.messages.  And then when you add your alert, you can specify to alert on a specific @message_id.  This can be seen through the definition of sp_add_alert.

Then when you have this alert in place and the error message is raised, you should get a notification provided that side is all set up correctly.


.NET executes a SQL query and Active Monitor shows multiple rows blocking each other I use .NET to execute SQL operations on SQL Server 2014, here's the code used:

using(SqlConnection conn = new SqlConnection(connectionString)){
    //https://stackoverflow.com/questions/1880471/capture-stored-procedure-print-output-in-net
    conn.InfoMessage += new SqlInfoMessageEventHandler(logSqlMessages);
    conn.Open();
    using(SqlCommand stmt = new SqlCommand{
        Connection = conn,
        CommandText = sql,
        CommandTimeout = 30000 // The time in seconds to wait for the command to execute. The default is 30 seconds.
        //,CommandType = CommandType.StoredProcedure
    })
    {
        affectedRecords = stmt.ExecuteNonQuery();
    } // using stmt
} // using conn

When I look at Active Monitor, there are tens of rows referencing  the same operation. They all have the same session_id, some of them have Task State running and most of them are suspended. Some of them have LastWaitTime CXPACKET and most are PAGEIOLATCH_SH.

I also ran a query on SQL Server and same behavior happened on Active Monitor.

Maybe it's a normal behavior of it, but it's odd that a SELECT operation creates multiple rows and blocks itself like that. Any idea of what may be causing it?

Multiple rows in activity monitor for the same SPID means your query has been chosen to be executed in parallel across multiple threads.

Each row on Activity Monitor actually represents one ECID, not one SPID.

Activity monitor only exposes the SPID column (session_id), but there is an additional column exposed in sys.sysprocesses called ECID (Execution Context ID) - this is a unique identifier for each thread the query is utilising.

The sysprocesses system view is deprecated, but you can find ECID by another name (exec_context_id) in the sys.dm_os_tasks view (as well as other task-related views).

Here is a sample query that captures all of the execution context IDs associated with queries running in a particular session, and what, if anything, they are waiting on:

SELECT
    dot.session_id,
    dot.exec_context_id,
    dot.task_state,
    dowt.wait_type,
    dowt.wait_duration_ms,
    dowt.blocking_session_id,
    dowt.resource_description
FROM sys.dm_os_tasks dot
    LEFT JOIN sys.dm_os_waiting_tasks dowt
        ON  dowt.exec_context_id = dot.exec_context_id
            AND dowt.session_id = dot.session_id
WHERE dot.session_id = 51
ORDER BY exec_context_id;

In SQL 2016 two new columns were added to sys.dm_exec_requests - DOP and parallel_worker_count - these can be used to check the whether the request is running in parallel or not.

Another key item to note is that the CXPACKET wait is inherently a parallelism wait - this by itself tells us the query is using more than one thread.

The PAGEIOLATCH_SH is a wait indicating those threads are reading data from disk into memory.


How to set SSIS flat file connection row delimiter to blank line? I have a text file with the following format:

DataColumn1
DataColumn2
DataColumn3

DataColumn1
DataColumn2
DataColumn3

DataColumn1
DataColumn2
DataColumn3

Each row with text in the file delimits a column of data, and each row with no data delimits a row.

I'm trying to import this data into an MS SQL database table using SSIS, but I'm struggling with the flat file connection manager being able to understand this delimiter formatting the upstream process I'm depending on gives me.

You can't specify a ROW (even empty) as a row delimiter. The problem is not really with the flat file connection. Actually there is another problem here than just row delimiter - you need to combine multiple rows of input and "pivot" them to create one row in the output table.
This calls for multiple steps in your package:

create a temp table with one col to hold the input file.
input the raw data into that temp table.
write some SQL code (i would encapsulate it in a stored proc) to process the raw input and build rows of data. this needs to be done in using a cursor. you loop thru each input and build a record until you reach an empty input. then you write out as output. continue until no more records in temp table.

Hope this helps.


mysql read slave to fetch entire database on start I am building a standart infrastructure, where writes are going to master db, and reads are going to slave dbs. The idea is that when I need more read power - I just spawn more MySQL slave servers.

Now I am aware of standart procedures of how to create a new slave. And they are a little to slow ... What I am looking for is to create an server image that I could spawn when I need more read power and that image would automatically sync to master and become its slave for reads.

At the moment I think of doing it like this.

Have a database backups every 4 hours or so/
Write a script that would run first time server is started.
The script would download the latest backup and import it in a regular way ... and bla bla bla ...

Maybe someone has done something similar and could share some resources ?

If you have three or more Slaves, I have a nice suggestion: Use one of the Slaves as a Volunteer to be cloned.

Here is a Topology

MAS --+--> SLV1
      |
      +--> SLV2
      |
      +--> SLV0

Say you want to Spawn SLV3. You could use SLV0 as a Clone

STEP01) Install MySQL Binaries on SLV3 (Same version as all other Slaves)
STEP02) On SLV0, STOP SLAVE;
STEP03) On SLV0, service mysql stop
STEP04) scp -r SLV0:/etc/my.cnf SLV3:/etc/my.cnf
STEP05) scp -r SLV0:/var/lib/mysql SLV3:/var/lib/mysql
STEP06) On SLV0, service mysql start
STEP07) On SLV3, chown -R mysql:mysql /var/lib/mysql
STEP08) On SLV3, change server_id in /etc/my.cnf to be unique from all other Slaves
STEP09) On SLV3, service mysql start

That's it.

This is the same paradigm followed by Percona XtraDB Cluster (PXC). When it comes to PXC, introducing a New Slave in PXC is as simple as adding the MasterIP to my.cnf and starting MySQL. All of the above steps are executed internally by PXC using Quorom Selection to choose which Slave becomes the Donor (a.k.a. Volunteer to be Cloned) as well as one of three methods for copying data (xtrabackup, rsync, mysqldump) This copying method is known as SST (State Snapshot Transfer).

If all of the Application-Level Data are stored in InnoDB only, you should look into using PXC. If you have a mix of InnoDB/MyISAM or all MyISAM, the above 9 steps are to be scripted by you.


How to get all dates belonging to a timestamp range I have a tsrange as input. For example:

tsrange('2019-03-04 20:00'::timestamp, '2019-03-06 20:00'::timestamp)

I want to generate all dates belonging to this range.

I started with the following, probably naive, approach:

select generate_series(lower(r), upper(r), '1 day')::date
from   tsrange('2019-03-04 20:00'::timestamp, '2019-03-06 20:00'::timestamp) r;

This seems to work great, it gives:

+-----------------+
| generate_series |
+-----------------+
| 2019-03-04      |
| 2019-03-05      |
| 2019-03-06      |
+-----------------+

However, there is are exceptions which are not handled well. Take the following range as an example:

tsrange('2019-03-04 00:00'::timestamp, '2019-03-06 00:00'::timestamp)

This is a range from 2019-03-04 00:00 inclusive to 2019-03-06 00:00 exclusive. In other words, the date 2019-03-06 does not belong to this range.

When repeating the solution above, I get the following results:

+-----------------+
| generate_series |
+-----------------+
| 2019-03-04      |
| 2019-03-05      |
| 2019-03-06      |
+-----------------+

As you can see, 2019-03-06 is in the output, while it shouldn't.

I perfectly understand why this is (we asked for it in the generate_series call; it is not aware that 2019-03-06 is exclusive in our range).

A solution could be:

select generate_series(
         date_trunc('day', lower(r)),
         case when upper(r)::time = '00:00' then date_trunc('day', upper(r)) - interval '1 day'
              else                               date_trunc('day', upper(r))
         end,
         '1 day'
       )::date
from   tsrange('2019-03-04 00:00'::timestamp, '2019-03-06 00:00'::timestamp) r;

Now I get:

+-----------------+
| generate_series |
+-----------------+
| 2019-03-04      |
| 2019-03-05      |
+-----------------+

This seems to work fine. But this solution feels hackish. It also makes the assumption that the upper bound of the range is exclusive (which is the default). If the upper bound is inclusive, the original solution was fine. We can add tests on that:

select generate_series(
         date_trunc('day', lower(r)),
         case when not(upper_inc(r)) and upper(r)::time = '00:00' then date_trunc('day', upper(r)) - interval '1 day'
              else                                                     date_trunc('day', upper(r))
         end,
         '1 day'
       )::date
from   tsrange('2019-03-04 00:00'::timestamp, '2019-03-06 00:00'::timestamp, '[]') r;

Is something better possible?

If you move the generate_series() to the FROM clause you can use a WHERE condition to limit the rows:

select t.dt::date
from  tsrange('2019-03-04 10:00'::timestamp, '2019-03-06 20:00'::timestamp) r,
      generate_series(lower(r), upper(r), '1 day') as t(dt)
where t.dt < case
                when upper(r) = date_trunc('day', upper(r))
                     then upper(r)::date
                else upper(r)::date + 1
             end;

dt
----------
2019-03-04
2019-03-05
2019-03-06

If the upper(r) is at midnight, then the generated date (t.dt) needs to be smaller than upper(r). If it's not, generated date needs to be smaller than the next day after the upper end of the range.

This query is completely independent of the hour in the actual range.

select t.dt::date
from  tsrange('2019-03-04 00:00'::timestamp, '2019-03-06 00:00'::timestamp) r,
      generate_series(lower(r), upper(r), '1 day') as t(dt)
where t.dt < case
                when upper(r) = date_trunc('day', upper(r))
                     then upper(r)::date
                else upper(r)::date + 1
             end;

dt
----------
2019-03-04
2019-03-05


How to perform builk update within mysql stored procedure I have been trying to perform a bulk update using a single update query but unable to do so within the stored procedure.
Following is the bulk update query that run flawlessly

UPDATE `batch_requests_detail`
SET `processing_status` = 5
WHERE `batch_requests_detail_id`  IN (1,2,3);

Where as when I use it within the stored procedure it didn't work

CALL Update_Batch_Request_Detail_Bulk('1,2,3',10);

CREATE DEFINER=`root`@`%` PROCEDURE `Update_Batch_Request_Detail_Bulk`(
IN _batch_request_detail_ids VARCHAR(5000),
IN _processing_status INT
)
BEGIN
    UPDATE `batch_requests_detail`
    SET `processing_status` = _processing_status
    WHERE `batch_requests_detail_id`  IN (_batch_request_detail_ids);
    END$$

DELIMITER ;

Assuming that _batch_request_detail_ids = "1,2,3", try this :

CREATE DEFINER=`root`@`%` PROCEDURE `Update_Batch_Request_Detail_Bulk`(
IN _batch_request_detail_ids VARCHAR(5000),
IN _processing_status INT
)
BEGIN
     /**************** Set statement added***********/
    SET @sql =concat("UPDATE batch_requests_detail
    SET processing_status = ",_processing_status,"
    WHERE batch_requests_detail_id  IN (",_batch_request_detail_ids,");");
    PREPARE stmt1 FROM @sql;
    EXECUTE stmt1;
    END$$

DELIMITER ;


Invalid object name 'master.dbo.MSreplication_options' when restoring DB I've tried Googling for an answer to this but have got nowhere.

I have one SQL Server instance whose replication is set up to be a publisher and a distributor of FooDatabase.  I have a second instance whose replication is set up to be a subscriber of FooDatabase on the first instance.  Replication updates work.  What I want to do is backup the subscription FooDatabase, delete it on the subscriber instance, and restore the backup.  However, when I do this and try to restore the backup, I get the following error:

  Restore failed for Server 'DAVEG1525-162'.  (Microsoft.SqlServer.SmoExtended)

  System.Data.SqlClient.SqlError: Invalid object name 'master.dbo.MSreplication_options'. (Microsoft.SqlServer.Smo)

I've checked and dbo.MSreplication_options does exist as a system table in the master database on the production instance, but not on the subscription instance.

My questions are: if it doesn't exist on the subscription instance, why is it being included on the backup made from the subscription instance of SQL Server? and how can I fix this problem?

As far as I can tell, the table master.dbo.MSreplication_options, amongst various other replication-related tables, are supposed to exist on any SQL Server 2008 r2 install whether or not you've set up replication.  Somehow the one on my SQL Server instance got dropped.  I eventually found this handy script on Google (cached!) which 'fixes up' various replication things, including recreating master.dbo.MSreplication_options if it doesn't already exist:

-- ***************************************************************************
-- Copyright (c) 1997 - 2003 Microsoft Corporation.
-- All Rights Reserved
--
-- @File: ReplicationFixup.sql
--
-- Purpose:
--  Procedures/extended stored procedures (XPs)/functions that are owned by replication and are created on master database
--
-- Notes: Created 2001/02/07 10:28 (RMak)
--
-- History:
--
--     @Version: Yukon
--
-- @EndHeader@
--
exec sys.sp_configure 'update',1
reconfigure with override

set ANSI_NULLS off

use master
go

-- Make sure that we remove procedures that got accidentally installed in
-- master by an 80 sp2 QFE
if object_id('dbo.sp_MSreplremoveuncdir', 'P') > 0
    drop procedure dbo.sp_MSreplremoveuncdir

if object_id('dbo.sp_MSdeletefoldercontents', 'P') > 0
    drop procedure dbo.sp_MSdeletefoldercontents

-- drop extended procedures that were created in master

if object_id('xp_mergexpusage', 'local') is not null
    execute sys.sp_dropextendedproc 'xp_mergexpusage'

if object_id('xp_mergelineages', 'local') is not null
    execute sys.sp_dropextendedproc 'xp_mergelineages'

if object_id('xp_mapdown_bitmap', 'local') is not null
    execute sys.sp_dropextendedproc 'xp_mapdown_bitmap'

if object_id('xp_ORbitmap', 'local') is not null
    execute sys.sp_dropextendedproc 'xp_ORbitmap'

if object_id('xp_firstonly_bitmap', 'local') is not null
    execute sys.sp_dropextendedproc 'xp_firstonly_bitmap'

if object_id('xp_varbintohexstr', 'local') is not null
    execute sys.sp_dropextendedproc 'xp_varbintohexstr'

if object_id('xp_intersectbitmaps', 'local') is not null
    execute sys.sp_dropextendedproc 'xp_intersectbitmaps'

if object_id('xp_displayparamstmt', 'local') is not null
    execute sys.sp_dropextendedproc 'xp_displayparamstmt'

if object_id('xp_printstatements', 'local') is not null
    execute sys.sp_dropextendedproc 'xp_printstatements'

if object_id('xp_makecab', 'local') is not null
    exec sys.sp_dropextendedproc 'xp_makecab'

if object_id('xp_unpackcab', 'local') is not null
    exec sys.sp_dropextendedproc 'xp_unpackcab'

if object_id('sp_repldone', 'local') is not null
    exec sys.sp_dropextendedproc 'sp_repldone'

if object_id('sp_repltrans', 'local') is not null
    exec sys.sp_dropextendedproc 'sp_repltrans'

if object_id('sp_replcounters', 'local') is not null
    exec sys.sp_dropextendedproc 'sp_replcounters'

if object_id('sp_replhelp', 'local') is not null
    exec sys.sp_dropextendedproc 'sp_replhelp'

if object_id('sp_replddlparser', 'local') is not null
    exec sys.sp_dropextendedproc 'sp_replddlparser'

if object_id('sp_replcmds', 'local') is not null
    exec sys.sp_dropextendedproc 'sp_replcmds'

if object_id('sp_replflush', 'local') is not null
    exec sys.sp_dropextendedproc 'sp_replflush'

if object_id('sp_replpostcmd', 'local') is not null
    exec sys.sp_dropextendedproc 'sp_replpostcmd'

if object_id('sp_replincrementlsn_internal', 'local') is not null
    exec sys.sp_dropextendedproc 'sp_replincrementlsn_internal'

if object_id('sp_replupdateschema', 'local') is not null
    exec sys.sp_dropextendedproc 'sp_replupdateschema'

if object_id('sp_replsetoriginator_internal', 'local') is not null
    exec sys.sp_dropextendedproc 'sp_replsetoriginator_internal'

if object_id('sp_replsetsyncstatus', 'local') is not null
    exec sys.sp_dropextendedproc 'sp_replsetsyncstatus'

if object_id('sp_replpostsyncstatus_int', 'local') is not null
        exec sys.sp_dropextendedproc 'sp_replpostsyncstatus_int'

if object_id('xp_dsninfo', 'local') is not null
    exec sys.sp_dropextendedproc 'xp_dsninfo'

if object_id('xp_enumdsn', 'local') is not null
    exec sys.sp_dropextendedproc 'xp_enumdsn'

if object_id('xp_oledbinfo', 'local') is not null
    exec sys.sp_dropextendedproc 'xp_oledbinfo'

if object_id('xp_repl_encrypt', 'local') is not null
    exec sys.sp_dropextendedproc 'xp_repl_encrypt'

if object_id('xp_repl_convert_encrypt', 'local') is not null
    exec sys.sp_dropextendedproc 'xp_repl_convert_encrypt'

if object_id('xp_repl_help_connect', 'local') is not null
    exec sys.sp_dropextendedproc 'xp_repl_help_connect'

if object_id('xp_replproberemsrv', 'local') is not null
    exec sys.sp_dropextendedproc 'xp_replproberemsrv'

go

--
-- Create table dbo.MSreplication_options in master if needed
--
if object_id(N'dbo.MSreplication_options', 'local') is null
BEGIN
    -- table does not exist
    raiserror('Creating table MSreplication_options',0,1)

    CREATE TABLE dbo.MSreplication_options
    (
        optname sysname NOT NULL,
        value bit NOT NULL,
        major_version int NOT NULL,
        minor_version int NOT NULL,
        revision int NOT NULL,
        install_failures int NOT NULL
    )

    exec dbo.sp_MS_marksystemobject N'dbo.MSreplication_options'
END
ELSE
BEGIN
    -- table exists
    -- drop index if needed (this index was used in Sphinx)
    if exists (select * from sys.indexes where object_id = object_id(N'dbo.MSreplication_options')
                        and name = N'ucMSreplication_options')
    begin
        drop index dbo.MSreplication_options.ucMSreplication_options
    end
END
GO

IF NOT EXISTS (SELECT * FROM MSreplication_options WHERE optname = 'transactional')
    INSERT INTO MSreplication_options VALUES
        ('transactional',0,0,0,0,0)
IF NOT EXISTS (SELECT * FROM MSreplication_options WHERE optname = 'merge')
    INSERT INTO MSreplication_options VALUES
        ('merge',0,0,0,0,0)
IF NOT EXISTS (SELECT * FROM MSreplication_options WHERE optname = 'security_model')
BEGIN
    DECLARE @major_version  int,
            @minor_version  int,
            @revision       int

    -- @@microsoftversion is set as 0xMMmmRR[RR] wher M=Major, m=minor and R=revision
    --  SELECT @major_version   = CONVERT(int, SUBSTRING(CONVERT(varbinary(4), @@microsoftversion), 1, 1)),
    --      @minor_version  = CONVERT(int, SUBSTRING(CONVERT(varbinary(4), @@microsoftversion), 2, 1)),
    --      @revision       = CONVERT(int, SUBSTRING(CONVERT(varbinary(4), @@microsoftversion), 3, 2))
    SELECT @major_version   = 90,
            @minor_version  = 0,
            @revision       = 0

    INSERT INTO MSreplication_options (optname, value, major_version, minor_version, revision, install_failures)
        VALUES ('security_model', 1, @major_version, @minor_version, @revision, 0)
END

UPDATE MSreplication_options
    SET major_version = 90
GO

-- Startup procs have to be created in master
if object_id('sp_MSrepl_startup', 'local') is not null
    drop procedure sp_MSrepl_startup

raiserror('Creating procedure sp_MSrepl_startup', 0,1)
go

create procedure dbo.sp_MSrepl_startup
as
    exec sys.sp_MSrepl_startup_internal
go

exec master.dbo.sp_MS_marksystemobject sp_MSrepl_startup

-- If a distributor is installed, mark the sp as a startup sp.
if exists (select * FROM master..sysservers WHERE  srvstatus & 8 <> 0)
    exec dbo.sp_procoption 'sp_MSrepl_startup', 'startup', 'true'
go

if object_id('sp_MScleanupmergepublisher', 'local') is not null
    drop procedure sp_MScleanupmergepublisher
go

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON

raiserror('Creating procedure sp_MScleanupmergepublisher', 0,1)
go
create procedure dbo.sp_MScleanupmergepublisher
as
    exec sys.sp_MScleanupmergepublisher_internal
go

-- If there are any merge published databases installed on this server,
-- mark sp_MScleanupmergepublisher as a startup proc

if exists (select * from master..sysdatabases where (category & 4) <> 0)
    exec dbo.sp_procoption 'sp_MScleanupmergepublisher', 'startup', 'true'

--
-- Functions that used to be created in master and have now moved to resource
-- use exec to drop, otherwise "drop function" gives syntax error on SQL 7, which did not have UDFs yet
--

if object_id('fn_varbintohexstr', 'local') is not null
     exec('drop function dbo.fn_varbintohexstr')

if object_id('fn_varbintohexsubstring', 'local') is not null
     exec('drop function dbo.fn_varbintohexsubstring')

go

--
-- procedures that used to be created in master and are obsolete now
-- drop the local procedures in master
--
if object_id(N'dbo.sp_addpublisher', 'local') is not null
    drop procedure dbo.sp_addpublisher

if object_id(N'dbo.sp_fetchshowcmdsinput', 'local') is not null
    drop procedure dbo.sp_fetchshowcmdsinput

if object_id(N'dbo.sp_getagentoffloadinfo', 'local') is not null
    drop procedure dbo.sp_getagentoffloadinfo

if object_id(N'dbo.sp_gettypestring', 'local') is not null
    drop procedure dbo.sp_gettypestring

if object_id(N'dbo.sp_helpmergecleanupwait', 'local') is not null
    drop procedure dbo.sp_helpmergecleanupwait

if object_id(N'dbo.sp_helpsubscriptionjobname', 'local') is not null
    drop procedure dbo.sp_helpsubscriptionjobname

if object_id(N'dbo.sp_mergecompletecleanup', 'local') is not null
    drop procedure dbo.sp_mergecompletecleanup

if object_id(N'dbo.sp_mergepreparecleanup', 'local') is not null
    drop procedure dbo.sp_mergepreparecleanup

if object_id(N'dbo.sp_MSaddpubtocontents', 'local') is not null
    drop procedure dbo.sp_MSaddpubtocontents

if object_id(N'dbo.sp_MSareallcolumnscomputed', 'local') is not null
    drop procedure dbo.sp_MSareallcolumnscomputed

if object_id(N'dbo.sp_MSchunkgeneration', 'local') is not null
    drop procedure dbo.sp_MSchunkgeneration

if object_id(N'dbo.sp_MScleanup_metadata', 'local') is not null
    drop procedure dbo.sp_MScleanup_metadata

if object_id(N'dbo.sp_MScleanuptask', 'local') is not null
    drop procedure dbo.sp_MScleanuptask

if object_id(N'dbo.sp_MScompletecleanup', 'local') is not null
    drop procedure dbo.sp_MScompletecleanup

if object_id(N'dbo.sp_MScomputearticlescreationorder', 'local') is not null
    drop procedure dbo.sp_MScomputearticlescreationorder

if object_id(N'dbo.sp_MScomputeunresolvedrefs', 'local') is not null
    drop procedure dbo.sp_MScomputeunresolvedrefs

if object_id(N'dbo.sp_MSdelete_specifiedcontents', 'local') is not null
    drop procedure dbo.sp_MSdelete_specifiedcontents

if object_id(N'dbo.sp_MSdeletecontents', 'local') is not null
    drop procedure dbo.sp_MSdeletecontents

if object_id(N'dbo.sp_MSdeletepushagent', 'local') is not null
    drop procedure dbo.sp_MSdeletepushagent

if object_id(N'dbo.sp_MSenumchanges_direct', 'local') is not null
    drop procedure dbo.sp_MSenumchanges_direct

if object_id(N'dbo.sp_MSenumchanges_pal', 'local') is not null
    drop procedure dbo.sp_MSenumchanges_pal

if object_id(N'dbo.sp_MSenumpartialchanges_direct', 'local') is not null
    drop procedure dbo.sp_MSenumpartialchanges_direct

if object_id(N'dbo.sp_MSenumpartialchanges_pal', 'local') is not null
    drop procedure dbo.sp_MSenumpartialchanges_pal

if object_id(N'dbo.sp_MSexternalfkreferences', 'local') is not null
    drop procedure dbo.sp_MSexternalfkreferences

if object_id(N'dbo.sp_MSget_subtypedatasrc', 'local') is not null
    drop procedure dbo.sp_MSget_subtypedatasrc

if object_id(N'dbo.sp_MSgettypestringudt', 'local') is not null
    drop procedure dbo.sp_MSgettypestringudt

if object_id(N'dbo.sp_MShelpsubscriptionjobname', 'local') is not null
    drop procedure dbo.sp_MShelpsubscriptionjobname

if object_id(N'dbo.sp_MSinsertcontents', 'local') is not null
    drop procedure dbo.sp_MSinsertcontents

if object_id(N'dbo.sp_MSis_col_replicated', 'local') is not null
    drop procedure dbo.sp_MSis_col_replicated

if object_id(N'dbo.sp_MSload_replication_status', 'local') is not null
    drop procedure dbo.sp_MSload_replication_status

if object_id(N'dbo.sp_MSmakedynsnapshotvws_longdef', 'local') is not null
    drop procedure dbo.sp_MSmakedynsnapshotvws_longdef

if object_id(N'dbo.sp_MSpreparecleanup', 'local') is not null
    drop procedure dbo.sp_MSpreparecleanup

if object_id(N'dbo.sp_MSquiescecheck', 'local') is not null
    drop procedure dbo.sp_MSquiescecheck

if object_id(N'dbo.sp_MSquiesceforcleanup', 'local') is not null
    drop procedure dbo.sp_MSquiesceforcleanup

if object_id(N'dbo.sp_MSquiescetriggersoff', 'local') is not null
    drop procedure dbo.sp_MSquiescetriggersoff

if object_id(N'dbo.sp_MSquiescetriggerson', 'local') is not null
    drop procedure dbo.sp_MSquiescetriggerson

if object_id(N'dbo.sp_MSreplcheck_connection', 'local') is not null
    drop procedure dbo.sp_MSreplcheck_connection

if object_id(N'dbo.sp_MSscript_security', 'local') is not null
    drop procedure dbo.sp_MSscript_security

if object_id(N'dbo.sp_MSscript_validate_subscription', 'local') is not null
    drop procedure dbo.sp_MSscript_validate_subscription

if object_id(N'dbo.sp_MSscriptmvastable', 'local') is not null
    drop procedure dbo.sp_MSscriptmvastable

if object_id(N'dbo.sp_MSscriptmvastableidx', 'local') is not null
    drop procedure dbo.sp_MSscriptmvastableidx

if object_id(N'dbo.sp_MSscriptmvastablenci', 'local') is not null
    drop procedure dbo.sp_MSscriptmvastablenci

if object_id(N'dbo.sp_MSscriptmvastablepkc', 'local') is not null
    drop procedure dbo.sp_MSscriptmvastablepkc

if object_id(N'dbo.sp_MSsubst_filter_name', 'local') is not null
    drop procedure dbo.sp_MSsubst_filter_name

if object_id(N'dbo.sp_MSupdate_replication_status', 'local') is not null
    drop procedure dbo.sp_MSupdate_replication_status

if object_id(N'dbo.sp_MSupdatecontents', 'local') is not null
    drop procedure dbo.sp_MSupdatecontents

if object_id(N'dbo.sp_replicationoption', 'local') is not null
    drop procedure dbo.sp_replicationoption

if object_id(N'dbo.sp_replproberemoteserver', 'local') is not null
    drop procedure dbo.sp_replproberemoteserver

if object_id(N'dbo.sp_replsetoriginator_pal', 'local') is not null
    drop procedure dbo.sp_replsetoriginator_pal

if object_id(N'dbo.sp_verify_publication', 'local') is not null
    drop procedure dbo.sp_verify_publication

if object_id(N'dbo.sp_MSarticletextcol', 'local') is not null
    drop procedure dbo.sp_MSarticletextcol

if object_id(N'dbo.sp_MSexists_file', 'local') is not null
    drop procedure dbo.sp_MSexists_file

if object_id(N'dbo.sp_MSfixlineagemismatch', 'local') is not null
    drop procedure dbo.sp_MSfixlineagemismatch

if object_id(N'dbo.sp_MStextcolstatus', 'local') is not null
    drop procedure dbo.sp_MStextcolstatus

if object_id(N'dbo.sp_MSread_resolver_clsid', 'local') is not null
    drop procedure dbo.sp_MSread_resolver_clsid

if object_id(N'dbo.sp_MSsubscriptions', 'local') is not null
    drop procedure dbo.sp_MSsubscriptions

go

After I ran this, I was able to import my backed-up DB on the subscriber without error.


at least one member of a list within another list when querying over a join Assume the following layout in a MySQL database

Activity (id,etc) [id is primary key]
AcitivityMapper (activityID, targetYearID)  [composite key]
Years (targetYearID, targetYearName) [id primary key, name is unique]

I would like to, given a list of "year names", find the activities that can be mapped to at least one of the year names

Extra notes: using the above layout, an activity can be mapped to multiple years

Example:

Activity (1, bla1)
Year (1, year1)
Year (2, year2)
Year (3, year3)
Mapper( 1, 1)
Mapper (1, 2)

If given a list containing "year1" or "year2" i want to get activity 1

How would I do this using an SQL query?

Extra Note:

I'm using hibernate in java on spring. The objects I have are as follows:

My objects would be as follows:

@Entity @Table(name="Activity")
class Activity {
 @Id @Column(name="id") private Integer id;
 @ManyToMany @JoinTable(name="ActivityMapper",joinColumns={@JoinColumn(name="id")},inverseJoinColumns{@JoinColumn(name="targetYearID")}) private List<TargetYear> targetYears = new ArrayList<>();
}

@Entity @Table(name="Years")
class TargetYear {
 @Id @Column(name="targetYearID") private Integer id;
 @Column(name="targetYearName") private String name;
}

SELECT DISTINCT
   a.id,
   a.etc
FROM years y
JOIN activitymapper am
   ON (y.targetyearid = am.targetyearid)
JOIN activity a
   ON (a.id = am.activityid)
WHERE y.targetyearname IN ('Year1', 'Year2')

Should do it. We're going from years to activity through activitymapper, selecting each activity only once (DISTINCT).


is there a work around for when you want to put an OR inside a filtered index? is there a work around for when you want to put an OR inside a filtered index?

create index FIDX_tblbOrders_sdtmOrdCreated_INCL
on dbo.tblBOrder(sdtmOrdCreated)
INCLUDE (sintMarketID,
         strCurrencyCode,
         sintOrderStatusID
         )
WHERE ((sintMarketId=1)
AND ( (sintOrderStatusId < 9) OR (sintOrderStatusId > 14)))

I am trying to create the index above,
because I am NOT interested in any situation
where sintOrderStatusId IN (9-14)

Of course I can create a view or indexed view, but I was trying to avoid that.

just adding more info:
sintOrderStatusId is a smallint NOT NULL and the possible values range from 1 to 30.
the 9 to 14 are to be avoided, therefore the filtered index.

Unfortunately, there seems to be no way to create a negative filter for an index, without resorting to creating a materialized view.  If it were possible to create a negative filter such as the one you'd like, it would be quite difficult for the query-optimizer to "pick" the index for use, drastically increasing the time required to find a good plan.

Depending on the query patterns for this table, you could simply create two indexes; one for less than 9 and one for greater than 14.  Either of these indexes may be chosen by the query optimizer for simple WHERE clauses such as WHERE StatusID = 6

CREATE TABLE dbo.TestNegativeFilter
(
    TestNegativeFilter INT NOT NULL
        CONSTRAINT PK_TestNegativeFilter
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , StatusID INT NOT NULL
);
GO

CREATE INDEX IX_TestNagativeFilter_LessThan9
ON dbo.TestNegativeFilter(StatusID)
WHERE (StatusID < 9);

CREATE INDEX IX_TestNagativeFilter_GreaterThan14
ON dbo.TestNegativeFilter(StatusID)
WHERE (StatusID > 14);

Another way to accomplish this might be:

CREATE INDEX IX_TestNegativeFilter_9_to_14
ON dbo.TestNegativeFilter(StatusID)
WHERE (StatusID IN (9, 10, 11, 12, 13, 14));

SELECT *
FROM dbo.TestNegativeFilter tnf
EXCEPT
SELECT *
FROM dbo.TestNegativeFilter tnf
WHERE tnf.StatusID IN (9, 10, 11, 12, 13, 14);

This uses the index filtered on 9 to 14 to exclude rows.

On my test rig, a simple covering index returns rows by far the quickest:

CREATE NONCLUSTERED INDEX IX_TestNegativeFilter_StatusID
ON dbo.TestNegativeFilter(StatusID)
INCLUDE (TestNegativeFilter);

SELECT *
FROM dbo.TestNegativeFilter tnf
WHERE tnf.StatusID NOT IN (9, 10, 11, 12, 13, 14);

Alternatively, using a variation on the approach used in your own answer:

CREATE INDEX [IX dbo.TestNegativeFilter StatusID not 9-14]
ON dbo.TestNegativeFilter (StatusID)
WHERE StatusID <> 9
AND StatusID <> 10
AND StatusID <> 11
AND StatusID <> 12
AND StatusID <> 13
AND StatusID <> 14;

Despite the filter being written as conjunctions, it supports queries written in any of the following ways (the first being slightly more efficient):

StatusID NOT IN (9, 10, 11, 12, 13, 14)
StatusID < 9 OR StatusID > 14
StatusID NOT BETWEEN 9 AND 14


Best practices for change management with indexes Our IT shop is first starting to build a group of DBA's. All of us (myself included) have come over from the application development/architecture world, so the DBA world is still fairly new to us.

Along with building a DBA group, we are looking to build change manage procedures and processes (hopefully based on best practices) for when we need to move changes.

I've found the following post which is helpful for mostly trigger, stored procedure, and/or DDL changes. But it doesn't necessarily address indexes or vendor databases.

We have a mix of both our own and vendor databases. In our case some of the vendors (though not all) are working with our company to build the database(s) and applications. We are in the process of performance testing our applications now before they "go live". Thus we are analyzing indexes (or the lack thereof) pretty heavily.

As we come across indexes that we feel should be made, how do we best deal with change management with regard to these, both for our own databases as well as for any vendors?

What do you do in your shop? I'm worried less about tools then about the process.

  EDIT: So far, I'm appreciating the feedback, comments, and answers for this question. I have noticed that some of the answers are a bit
  tool specific. I'm looking for more "agnostic" practices, if that can
  be had.

  However if agnostic is not possible, then for tool sets, we use IBM
  DB2 LUW (and that actually on AIX) mostly. We have some DB2 on Windows
  and DB2 for i (IBM's i5/OS), but we are mostly AIX DB2. We do use
  source control, specifically Subversion.

  Again, looking for general best practices, but above is what we use
  that would be vendor specific.

  EDIT:
  Current Decision: We intend to track our reasoning as well as our changes. So we are going to open an issue in our issue-tracking
  software (which in our case is JIRA). Now we can add in documentation
  as to what priority the change has, data that backs up what the change
  should be, the change, and the results of the change from another
  environment where the change was tested.

  We then also intend to keep track of our changes in scripts in SVN
  (much like was suggested below). This way we can track what version of
  what exists where. This can be recorded in our JIRA issue (and in any
  other auditing software we use, ie. pasted links). We can know with
  more certainty what change went to what environment and why. We can
  then also track if the index was something we added beyond the vendors
  implementation or ahead of their implementation, etc.)

I would strongly recommend that you treat your database basically the same way as you treat your application code. You can script your database out to it's component parts and check those into source control and then use the same labels & versions there that you use for your apps.

To get the objects into source control there are a number of tools you can use. Microsoft has a tool that is nicknamed Data Dude. It works with Visual Studio. They're also preparing to release a new tool called SQL Server Database Tools (SSDT), again, working with Visual Studio. My company, Red Gate Software, makes a tool that works with SSMS called SQL Source Control.

In terms of process, I wrote several chapters for the book Red Gate Guide to Team Development. It's available as a free download (or if you want to kill a tree you can purcahse one from Amazon). I go into a lot more details about working with databases in development teams there.


Real time much greater than `EXPLAIN ANALYZE`'s execution time (index scan) I want to fetch up to 100 rows based on their id. The id is the primary key of the table.

The query that I had written looks like this:

select * from table where id = any ($1);

where $1 is interpolated as an array of ids.

When using EXPLAIN ANALYZE I get the following plan (explain link):

                                                                QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..44.98 rows=17 width=553) (actual time=100.048..834.209 rows=17 loops=1)
   ->  Index Scan using instagram_id_index_1000 on profiles_1000  (cost=0.43..44.98 rows=17 width=553) (actual time=100.046..834.163 rows=17 loops=1)
         Index Cond: (id = ANY ('{34491540,28977916,33241270,33609141,31043380,29364420,30247037,33311491,36267571,32886281,32366574,32569254,33038689,31089076,29416100,30455309,31570597}'::integer[]))
 Planning time: 424.512 ms
 Execution time: 834.280 ms
(5 rows)

When I actually execute it (with \timing) I get results in the 2-5 seconds range! I really cannot wrap my mind around such abysmal performance. The execution time provided by EXPLAIN ANALYZE was already high in the first place.

Some context:
1) the database is local, so there is no network latency
2) the table I'm querying on is a materialized view
3) I also tried the where id in (...) variation and nothing changed
4) I tried to programmatically loop over the ids and run a separate query for each and it produces better results (around 1.5s)

Is there something that can be done here? I cannot believe that Postgres' performance in my case is this bad. The server also has 8 cores, is it possible to parallelize this query as much as possible?

Firs the displayed query plan says:

  Limit  (cost=0.43..44.98 rows=17 width=553) ...

Obviously you added a LIMIT clause to the query, which is not in the query you show. If you didn't do the same in you second test, you may already have your answer: The same query with LIMIT (and without ORDER BY) can stop as soon as enough rows are found and can be much faster. Can also lead to a different query plan to begin with.

In principle

EXPLAIN ANALYZE measures the time spent by the Postgres process. Measurement is taken on the server. It adds a bit of overhead as compared to just executing the SQL command alone. For very cheap commands, that overhead may be more than the query cost. Overhead also depends on used options to some degree. You can minimize overhead with EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF). (The TIMING option of EXPLAIN is in no way related to psql's \timing; used to toggle additional measurements per plan node.)

The manual:

  The ANALYZE option causes the statement to be actually executed, not
  only planned. Then actual run time statistics are added to the
  display, including the total elapsed time expended within each plan
  node (in milliseconds) and the total number of rows it actually returned.

About the TIMING option:

  Include actual startup time and time spent in each node in the output.
  The overhead of repeatedly reading the system clock can slow down the
  query significantly on some systems, so it may be useful to set this
  parameter to FALSE when only actual row counts, and not exact times,
  are needed. Run time of the entire statement is always measured, even
  when node-level timing is turned off with this option. This parameter
  may only be used when ANALYZE is also enabled. It defaults to TRUE.

\timing in psql, on the other hand, measures the time between sending the command and receiving results - including network latency. Measurement is taken on the client. That can be many times the execution alone. Seems to be your case.

Due to the mentioned overhead above, the opposite might be observed in cases with low latency and minimal data volume sent over the wire (especially with psql executed on the same server): \timing can report faster times.

There is a third good option: use the shell command time (also measuring on the client, obviously). See:

How can I time SQL-queries using psql from command line?

Asides:

  When I actually execute it (with \timing)  ...

You are aware that EXPLAIN ANALYZE does actually execute the given query, with all side effects - as opposed to just EXPLAIN.

  I tried to programmatically loop over the ids and run a separate query for each and it produces better results (around 1.5s)

A loop can produce query plans customized to each actual input value, based on statistics for the value. While a large IN or a large input array will produce a plan based on generic estimates. See:

How to use index for simple time range join?

You can achieve similar effects as with your loop with a LATERAL join from a (derived) table or with many SELECT statements tied together with UNION ALL or with a loop in a (plpgsql) server-side function - without adding many times the network latency and/or planing overhead. Related:

Optimizing a Postgres query with a large IN
Can spatial index help a "range - order by - limit" query
Optimize GROUP BY query to retrieve latest row per user


Failed to drop column - value too large I'm trying to remove column from DB and got a strange error:

  Error starting at line 147 in command:
      alter table config drop column dte
      Error report:
      SQL Error: ORA-12899: value too large for column "OWNER"."CONFIG"."DTE" (actual: 3, maximum: 1)
      12899. 00000 -  "value too large for column %s (actual: %s, maximum: %s)"

  *Cause: An attempt was made to insert or update a column with a value
                 which is too wide for the width of the destination column.
                 The name of the column is given, along with the actual width
                 of the value, and the maximum allowed width of the column.
                 Note that widths are reported in characters if character length
                 semantics are in effect for the column, otherwise widths are
                 reported in bytes.

  *Action:   Examine the SQL statement for correctness.  Check source
                 and destination column data types.
                 Either make the destination column wider, or use a subset
                 of the source column (i.e. use substring).

I don't have triggers on this table. Could anyone advice on it?
Thanks!

UPD: we were able to delete it using
alter table ... set unused ...
and
alter table drop unused columns
after it. But still have no idea why simple drop doesn't work.

You can do it by increasing column size, then drop .
(Because drop column statement try to validate the data, then drop )
like ..

SQL> ALTER TABLE OWNER.CONFIG
     MODIFY(DTE VARCHAR2(500 BYTE));

SQL> ALTER TABLE OWNER.CONFIG DROP COLUMN DTE ;

example:-

SQL> select * from v$version ;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> ALTER TABLE ORCL.FORM DROP COLUMN CURRENT_STEP;
ALTER TABLE ORCL.FORM DROP COLUMN CURRENT_STEP
*

ERROR at line 1:
ORA-12899: value too large for column "ORCL"."FORM"."CURRENT_STEP" (actual:
6, maximum: 2)

SQL> ALTER TABLE ORCL.FORM
  2  MODIFY(CURRENT_STEP VARCHAR2(500 BYTE));

Table altered.

SQL> ALTER TABLE ORCL.FORM DROP COLUMN CURRENT_STEP;

Table altered.


Oracle Create Materialized View Script - Takes 12 minutes in DEV Schema, 5+ days in QA Schema I have a materialized view in my Oracle 10g database that I need to rebuild.  So I'm doing something like this...

drop materialized view ViewName;

CREATE MATERIALIZED VIEW ViewName
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
AS
SELECT ....
FROM ....

This creation of the materialized view takes 12 minutes to run in our DEV schema and at one time ran 5+ days in our QA schema without completing.  There are no locks on the tables when running the script in the QA schema.  Both of the schemas arre also on the same table space.  The DB definition is exactly the same.

Any ideas on why it is taking so long to execute the DDL statement to create the MV in our QA schema?  Or any ideas what's preventing it from being executed?

Thanks

Start by focusing on the query used by the materialized view.  Examine why the query is running slow and you will solve why the materialized view creation is running slow.  If this is the case it may require another question on the query itself.  In the unlikely event that the queries run in the same amount of time, let us know.


Join multiple tables without overhead on the DB Server using PDO I have multiple tables called light_readings, temp_readings, co2_readings and humidity_readings.

The column format of the tables are as below:

light_readings:

Id, light_value, unitid, date_time

temp_readings:

Id, temp_value, unitid, date_time

co2_readings:

Id, co2_value, unitid, date_time

humidity_readings:

Id, humidity_value, unitid, date_time

I am using PDO to fetch these values to my webapp, but retrieving these using multiple SQL queries is putting too much load on my DB server. I need to get the latest readings of all the tables in just one row if possible.

I tried using UNION, but even that takes up some load on the server

(SELECT temp_value FROM temp_readings where unitid='SS0007' order by Id desc limit 1)
UNION ALL
(SELECT humid_value FROM humid_readings where unitid='SS0007' order by Id desc limit 1)
UNION ALL
(SELECT co2_value FROM co2_readings where unitid='SS0007' order by Id desc limit 1)
UNION ALL
(SELECT light_value FROM light_readings where unitid='SS0007' order by Id desc limit 1)

Could someone assist me with this?

You need to rethink the schema...

Plan A:  Have temp, humid, etc, as columns in a single table.  If the date_time is the same from all the readings, then that does not need repeating.

Plan B:  Have those values in a JSON string in a single column in a single table.

Plan C:  Have a 3-column table:  (unitid, reading_name, value) and fetch the 4 values from 4 rows, but in a single SELECT.

In any case, Id is probably not needed.  The PRIMARY KEY can be (or start with) (unitid, date_time)


Problem joining two tables in MySQL I'm new to MySQL. I'm having trouble in joining two tables:

SELECT e.resourceName
 FROM resources_in_room r
 JOIN resource e ON r.resourceId=e.resourceId WHERE r.roomId=2;

Table resources in room:

CREATE TABLE  ``resources_in_room` (
  `id` bigint(20) NOT NULL auto_increment,
  `Quantity` int(11) default NULL,
  `resourseId` int(11) default NULL,
  `roomId` int(11) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `resourseId` (`resourseId`,`roomId`),
  KEY `FK36360B9B821B4697` (`roomId`),
  KEY `FK36360B9BC0C935AD` (`resourseId`),
  CONSTRAINT `FK36360B9BC0C935AD`
    FOREIGN KEY (`resourseId`) REFERENCES `resource` (`ResourceId`),
  CONSTRAINT `FK36360B9B821B4697`
    FOREIGN KEY (`roomId`) REFERENCES `room` (`RoomId`)
)

Table resource:

CREATE TABLE  `resource` (
  `ResourceId` int(11) NOT NULL auto_increment,
  `Description` varchar(255) default NULL,
  `Quantity` int(11) default NULL,
  `ResourceName` varchar(255) default NULL,
  PRIMARY KEY  (`ResourceId`)
)

its resourseId in your table definition and you are using resourceId in query.

This will work

SELECT e.resourceName FROM resources_in_room r join resource e on r.resourseId = e.resourceId where r.roomId=2;


Cannot connect to database remotely (not even locally when using ip address) I can login using this command:
sqlplus test/test@127.0.0.1:1521/local
But not this one: (where 192.168.0.240) is my IP
sqlplus test/test@192.168.0.240:1521/local

I am absolutely sure that my ip address (server's adress) is 192.168.0.240. So why am I getting the following error? ORA-12541: TNS:no listener

I get a connection timeout when trying to connect from other computers.

lsnrctl statusgives me the following:

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Produ
ction
Start Date                23-OCT-2013 15:27:35
Uptime                    0 days 21 hr. 37 min. 20 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\pc2\product\11.2.0\dbhome_2\network\admin\liste
ner.ora
Listener Log File         c:\app\pc2\diag\tnslsnr\pc2-PC\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "local" has 1 instance(s).
  Instance "local", status READY, has 1 handler(s) for this service...
Service "localXDB" has 1 instance(s).
  Instance "local", status READY, has 1 handler(s) for this service...
The command completed successfully

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

You have the localhost ip in the listener.ora (127.0.0.1), change this or add another line with the NIC ip.


importing myisam 5.0 database into a 5.5 innodb server I have MySQL 5.0 and 5.5 servers (but not a 5.1 server).  The 5.0 server is myisam only, the 5.5 default is innodb.  I need to move a database from the 5.0 server to the 5.5 server.  The database is not big, the dump is about 32M.  Both servers are Linux.

I ran a test by importing the 5.0 database into the 5.5 (after editing dumpfile from MYISAM to INNODB) server and then diffing mysqldumps from both servers.  If I use
--order-by-primay for the dump, the diffs are only what I would expect (version, engine, dump time, etc).

Any gotchas that I am missing?  Can I import a myisam 5.0 database into a 5.5 innodb server without bad surprises?

A mysqldump will attach ENGINE=MyISAM and ENGINE=InnoDB to every CREATE TABLE statement.

You should be fine.

WARNING : There is one gotcha !!!

The mysql database itself (where the grant tables reside) have different column layouts between versions of mysql.

You can dump all databases from MySQL 5.0 EXCEPT the mysql database.

It is much better to port the mysql grants using pt-show-grants.

Here is my personal emulation of what pt-show-grants does

mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql


Query Output Window not honoring fixed length Query below has fixed length values for data set being returned in a string, now the problem is that fixed lengths are not being honored in the query results. But the output text file produced by DB mail looks correct. See examples below:

     SELECT
          LEFT(CONVERT(char(9), machine) + SPACE(9),9)
          + LEFT(CONVERT(char(30), part_no) + SPACE(30), 30)
          + LEFT(CONVERT(char(7), ordered) + SPACE(7), 7)
          + LEFT(CONVERT(char(7), picked)  + SPACE(7), 7)
          + LEFT(CONVERT(char(7), allocated)  + SPACE(7), 7)
          + LEFT(CONVERT(char(7), in_stock)  + SPACE(7), 7)
          + LEFT(CONVERT(char(7), sh_bin_qty)  + SPACE(7), 7)
          + LEFT(CONVERT(char(7), short)  + SPACE(7), 7)
          + min_bin

Raw Output Results

Text File Results

It is honoring the fixed character length.  However, it does not appear to be using a monospaced font so it looks like it isn't.  The character length is fixed for the column.  The character width is font dependent.  DB Mail is using a monospaced font.

https://en.wikipedia.org/wiki/Monospaced_font

  A monospaced font, also called a fixed-pitch, fixed-width, or
  non-proportional font, is a font whose letters and characters each
  occupy the same amount of horizontal space.[1] This contrasts with
  variable-width fonts, where the letters and spacings have different
  widths.

Notice that the space taken up by an M is more than taken up by an I


Reset Variable when row value is changed Below is an example of a SELECT statement. I have a variable @total that is set to 0, which works fine if I am doing a WHERE clause but I need the final total for each agent. Currently how this works is that each agent's records display and their total at the end. My question is how can I reset the variable when the agentID changes to the next agent and only display the final count.

select agentID, points,
@total:= @total + points AS Total, comment
from attendance, (select @total:= 0) v group by agentID, incurredDate

This is what I am trying to achieve.

=======================
agentID    |    total
=======================
brwilson   |      2
vimunson   |      5

http://sqlfiddle.com/#!2/d53222/8

select agentID,points,
case when (if(@prev_agent != agentID, @total := points, @total := @total + points)) is null then null
when (@prev_agent := agentID) is null then null
else @total end as running_total
, comment
from attendance, (select @total:= 0.0, @prev_agent:=null) v
order by agentID, incurredDate

see it working live in an sqlfiddle

In case you wonder, why I use this case when construct, please refer to this post.

P.S.: When using variables like this, you should have a order by clause. Group by in MySQL uses an implicit order by when none is specified. But remember using order by when you don't have a group by.

EDIT: Actually your group bydoesn't fit here, it seems to me. I changed it to a order by. And I changed your points column to datatype decimal(5,2), cause you have .5 in your sample data, which would become 1 when you continue to use int.

EDIT:

To get the total for each agent, just wrap above query in another query like this:

select agentID, MAX(running_total)
from (
    select agentID,points,
    case when (if(@prev_agent != agentID, @total := points, @total := @total + points)) is null then null
    when (@prev_agent := agentID) is null then null
    else @total end as running_total
    , comment
    from attendance, (select @total:= 0.0, @prev_agent:=null) v
    order by agentID, incurredDate
) sq
GROUP BY agentID

see it working live in an sqlfiddle


Mysql : what are the steps for creating a database schema for a big project with many tables? I am creating a project with lots of tables like one for taxes, one for employees, customers etc. So how should I go about it?
For eg: building the ER model first or something like that

These are the steps I take:

Start with a piece of paper, write down the entities and their relations to each other.  Write the relations in plain language, e.g. Customer places zero to many Orders.
Once you have an idea of how many entities you will have and their rough relations, formalize that drawing into a hierarchy diagram - this is usually just the entities and their relations to each other, not any column or key information.
Starting at the top of your hierarchy, identify the properties of each entity and identify potential primary keys (candidate keys). There may be more than one per entity.  Do not create/assign "id" or other columns at this point unless they are being created by the calling application (InvoiceNumber,etc) and/or generally accepted practice for things like Person,Address.  "Codes" provided by outside authorities (ISO, others) are not only acceptable but preferred - CountryCd,CurrencyCd, etc.
Again starting at the top of your hierarchy, choose a primary key from your list of candidate keys and migrate that primary key through all other related entities.  Identify and list all required alternate keys.
Create your preliminary ER diagram.  I would use IDEF1X as it is easy to read and commonly accepted in the real world.  If you are unfamiliar with IDEF1X, please read this primer https://www.softwaregems.com.au/Documents/Documentary%20Examples/IDEF1X%20Notation.pdf
Once you have your draft diagram complete, you can identify instances where a surrogate ("Id") may be appropriate.  My rule of thumb for determining when a surrogate is appropriate is a) subtypes that group separate entities b) very wide keys where no element is required for referential integrity later c) the data needs to be obscured for security/privacy reasons.  Once you choose a surrogate, it becomes the primary key and your existing primary key must become an alternate key.  That last sentence is key.  If you use a surrogate and do not have an alternate key, you will not have a relational database.
If there are rules which must be enforced through transactions or check constraints, a brief description of the rule can be assigned and labeled to the appropriate entities.
You now have a logical model which can be used to build the physical model.  The physical model will include data types and any instances where you might implement things differently than the logical model (different tables for optional columns, etc.).
Now you can start writing your DDL.

I would also recommend that when you are designing your database that instead of using an "Id" column as a surrogate in every instance, you find if there is an appropriate/accepted shorthand that could be used instead.  So if each Order has a Status of "Received/In Process/Shipped/Canceled", when you create the reference table perhaps use a StatusCode like "R/P/S/C" instead of meaningless numbers "1/2/3/4".  Not only is CHAR(1) less storage than a typical INT, any queries/code written against the database are much easier to understand and you're less likely to commit the sin of using the wrong digit for the wrong column.


multiple insert queries after a set of with's I am trying to have a reproducible way to populate a database with some test data.

Suppose (an overly simplified example with) three base tables: name, city, job, and two relationship tables: name-city, and name-job. I need to create an entry into each of three base tables and use the aforementioned entries to create entries in the TWO relationship tables.

What I have already is a way of using a series of with queries to create entries in the 3 base tables and insert values into ONE relationship table.

with x as
(INSERT INTO "public"."name" VALUES(DEFAULT) RETURNING "id"),
y as
(INSERT INTO "public"."job" VALUES(DEFAULT) RETURNING "id"),
z as
(INSERT INTO "public"."city" VALUES(DEFAULT) RETURNING "id")

INSERT INTO "public"."name-job"("name", "job")
select x.id, y.id from x,y;

I would really like to add a second insert statement

INSERT INTO "public"."name-city"("name", "city")
select x.id, z.id from x,z;

after the first insert but don't know how. I have tried separating the two insert statements with commas, and enclosing them in parentheses and then separating them with commas, and some other ways but nothing works.

It is not entirely an option to use a brand new statement with the second insert because I need the same values of x, y, z to be used. Given my lack of expertise/experience, it is entirely possible I am missing something obvious... so any ideas on how I can do this, ideally, without highly sophisticated tools, would be most welcome.

FWIW, I'm using Postgres (10.x)

You need to put the INSERT INTO "name-job" into another CTE. You could also put the last insert into "name-city" into a CTE as well, and add a SELECT to get all the inserted ids (and anything else you need from the inserted rows) back.

WITH
  x AS
  (INSERT INTO public.name VALUES (DEFAULT) RETURNING id),
  y AS
  (INSERT INTO public.job VALUES (DEFAULT) RETURNING id),
  z AS
  (INSERT INTO public.city VALUES (DEFAULT) RETURNING id),
  xy AS
  (INSERT INTO public."name-job" (name, job)
   SELECT x.id, y.id FROM x,y
   RETURNING name, job),
  xz AS
  (INSERT INTO public."name-city" (name, city)
   SELECT x.id, z.id FROM x,z
   RETURNING name, city)
SELECT x.id, y.id, z.id, xy.*, xz.*
FROM x, y, z, xy, xz ;

If you want to insert more than one row in the tables, the query would need to be more complicated (use joins and not implicit cross joins).
The naming of the tables and columns is going to be a constant trouble and pain. I have found it much easier in Postgres if all table and column names are lower case and no special characters are used (like the dash you have used). Only underscores and digits.  You can avoid the need of using double quotes everywhere that way.


Creating a table with CHECK constraints in MySql workbench I created a table ORDER using Mysql Workbench as follows:
CREATE TABLE IF NOT EXISTS `rr`.`ORDER` (
  `Order No.` INT UNSIGNED NOT NULL COMMENT 'The order of the inspected equipment',
  `Order_Type` CHAR(8) NOT NULL COMMENT 'External or Internal work',
  `Engine_Description` VARCHAR(45) NOT NULL COMMENT 'Description of the Engine',
  `Equipment_Quantity` INT UNSIGNED NOT NULL COMMENT ' Number of the inspected equipment',
  `Required_Task` VARCHAR(8) NOT NULL COMMENT 'Whether Repair or Overhaul',
  PRIMARY KEY (`Order No.`))
ENGINE = InnoDB
COMMENT = '\n';

So I need to know how to make a constraint where if the value of the Order_Type field is 'External' then the value of  automatically the value of Engine_Description  will be NULL or else a value will be added?
Also, if I want to do the same for two different entities?

This can be done very elegantly using CHECK constraints in MySQL >=  8.0.16 as follows (fiddle available here):
Your table (changed slightly)
CREATE TABLE IF NOT EXISTS orders
(
  order_no INT,
  order_type CHAR(8) CHECK (order_type IN ('e', 'i')) ,
  engine_description VARCHAR(45) DEFAULT 'The default',  -- NOT NULL constraint removed
  CONSTRAINT ot_ed_ck
    CHECK ((order_type = 'i') OR (order_type = 'e' AND engine_description IS NULL))
);

Populate it:
INSERT INTO orders
VALUES
(1, 'i', 'safadf'), (2, 'i', 'xcvzxv'), (3, 'e', NULL);

Then
SELECT * FROM orders;

Result:
order_no    order_type  engine_description
       1             i              safadf
       2             i              xcvzxv
       3             e                null

So far, so good.
Then we try to do this:
INSERT INTO orders VALUES ('4', 'e', 'xxxxx');

and we get the error message:
Check constraint 'ot_ed_ck' is violated.

Which is exactly what we want!
To cover the case of or else a value will be added? in the question, by which I presume you mean a DEFAULT - if we specifically exclude engine_description from the list of values to be INSERTed as follows:
INSERT INTO orders (order_no, order_type) VALUES (5, 'i');

and we SELECT:
SELECT * FROM orders

We obtain:
order_no    order_type  engine_description
      1              i              safadf
      2              i              xcvzxv
      3              e                null
      5              i         The default   <<----- Default!

I would strongly urge you to upgrade to MySQL 8 - it's now at version 8.0.22, so any major version change wrinkles will have been ironed out by now - you get all sorts of goodies - like CHECK constraints that I just outlined, COMMON TABLE EXPRESSIONs (available from 8.0.1, aka the WITH clause) and also GENERATED columns (5.7.5).
If you cannot upgrade to version 8, then you'll have to use TRIGGERs (as per @nbk's answer), but you asked for constraints so that's what I supplied.
Also, what exactly do you mean by:

Also, if I want to do the same for two different entities?

If you mean constraints between two fields in the same table (intra-table), you have the example above, if you mean between two tables (inter-table), then on any server (bar Firebird), you'll have to use TRIGGERs!
Finally, a word of advice - do NOT use SQL keywords as table or column names (in this case Order)- it's confusing, makes debugging more difficult and is not good practice.  Normally, I use singular table names, but I make an exception (obviously) for ORDERs - a table is a set and should be singular (IMHO).
p.s. welcome to the forum!


How many passes of a table are done with UNION ALL? I am trying to run a single query that will make a single pass through a table, and was wondering if by using UNION ALL to make counts of total row unioned with counts of unique rows so I would be unioning a query with from the same table.

Would this operation require a double passing of the table?

Each query in UNION ALL is executed separately, union only "merges" the result sets so if each query needs to read entire table, it will be done twice. You can check that with EXPLAIN in MySQL or similar commands in other RDBMS.

One usage for UNION (ALL) is actually optimizing queries with OR conditions, where separate queries can use indexes better than one "bigger" query.

In your case it seems you might still do it with only one query. Just quessing here from what you wrote but did you consider using

SELECT count(col) AS totalCount, count(distinct col) AS uniqueCount FROM ...

?

Or WITH ROLLUP modiffier for GROUP BY? (It is MySQL - other RDBMS will probably have something similar)


How do I write a Postgres SQL command based on metadata of the tables themselves? I want to retrieve all the records that were modified in the past hour of running the command. It is purely a meta data command (with no regard to the content of any tabular column data). I want to identify tables and rows of those tables that had some DML modification with a specified temporal duration.

Does Postgres need special configuration to track such detailed meta data before I run a command to return the info I am looking for?

What query would I run to get the results I am looking for?

Postgres 9.5 introduced a feature to record commit timestamps. The documentation:

  track_commit_timestamp (bool)

  Record commit time of transactions. This parameter can only be set in
  postgresql.conf file or on the server command line. The default value
  is off.

And functions to work with it.

Once you have activated the setting and restarted the DB server, Postgres starts tracking commit timestamps. Then, for example, to get all rows from a table that were changed in any way during the last 4 hours:

SELECT * FROM tbl
WHERE  pg_xact_commit_timestamp(xmin) >= now() - interval '4 hours';

Related:

How list all tables with data changes in the last 24 hours?
Atomically set SERIAL value when committing transaction


How to group by when querying duplicate records? I have a table with duplicates and I want to present it to the stakeholders so they can help me determine the source of duplication.  Currently I can print 1 row for each duplicate entry, but I want to show all attributes for each.  Here's the query I'm using:

select *, count(*), max(id) from foos group by name having count(*) > 1 order by name;

I'm not sure how to alter the query to get each record where another record shares a name.  Thanks for any guidance.

SELECT
  *
FROM
  foos
  INNER JOIN
  (
    SELECT
      name
    FROM
      foos
    GROUP BY
      name
    HAVING
      COUNT(*) > 1
  ) AS dup
  ON foos.name = dup.name
ORDER BY
  name


Approximation of table size for MySQL cluster I want to make some approximation of table size for MySQL cluster.
Please read my reasoning and tell me, if I am right or am I making mistake somewhere.

I have simple table with PK (int) and additional column (int).
In MySQL int = 4 bytes, so one row is about 12 bytes (4*2 for columns and 4 for PK index).
If I have for example 1'000'000 rows this will be 12'000'000 bytes ~= 11.5MB.
Am I right in this calculations? Can I store such table with so little space?

You will need some more space for row overhead and PK storage. More information you can find in MySQL Documentation.

For the most detailed information you can use ndb_size.pl utility.


innodb_log_file_size set to a lower value than the size of ib_lofile0 and iblogfile1 I am trying to run an application from my MariadDB installation, that needs a larger value for innodb_log_file_size. So I have set it to innodb_log_file_size=256M in /etc/my.cnf.d/server.cnf.

Now when I check for the set size with show variables like 'innodb_buffer%'; it tells me that the size is 134217728 which is 128M. So it doesn't seem to acknowledge my change...

Funny enough though, when I run my application (which is Rancher by the way), the iblogfiles are instantiated with a size of 256M.

Can anybody tell me why this is the case? My application doesn't seem to work properly and I'm not quite sure whether this is related...

The InnoDB buffer pool is different from the InnoDB log file, i.e. the undo log (a.k.a. the redo log), whose size is controlled by the innodb_log_file_size and innodb_log_files_in_group variables.

The buffer pool is a cache for frequently used data and indexes, so that MariaDB doesn't have to read them from disk. On the other hand, the undo log stores old versions of data being modified by write transactions.

If you need to change the size of the buffer pool, the variable to modify is innodb_buffer_pool_size. This is a dynamic variable starting with MariaDB 10.2, so can also be modified without restarting by executing a command like SET GLOBAL innodb_buffer_pool_size=32G;