Setting up DRBD on an active MySQL server

Unfortunately, it is not possible to do either one.

Once you create /dev/drbd0 on both DB Servers, you have to mount the DRBD Primary on a folder. That folder cannot have anything with active file handles. The protocol I use is:

drbdadm primary drbd0
mount /dev/drbd0 /var/lib/mysql
service mysql start

You want DRBD to house MySQL Data. Here is what you must do instead

service mysql stop
mv /var/lib/mysql /var/lib/mysql_old
mkdir /var/lib/mysql
chown mysql:mysql /var/lib/mysql
drbdadm primary drbd0
mount /dev/drbd0 /var/lib/mysql
cp -R /var/lib/mysql_old /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
service mysql start

As shown, stopping mysqld is mandatory.

UPDATE 2013-03-16 21:46 EDT

There is something incredibly tough you can try

Given ServerDB1 with MySQL Running
Given ServerDB2 to introduce as DRBD partner

STEP 01) Setup DRBD on Separate ServerDB2

As I mentioned earlier, you can house mysql on a DRBD mount.

STEP 02) Setup MySQL Replication

Huh ??? How did MySQL Replication enter the picture

You need to mysqldump everything in ServerDB1
Load the mysqldump into MySQL running in ServerDB2
Setup Replication so all changes are migrated since loading of ServerDB2 was done

STEP 03) Failover Your Application

In simple English, use the IP address of ServerDB2 in your application instead of ServerDB1

You should wait and make sure you have a low peak time to do this failover

STEP 04) Setup DRBD on Separate ServerDB1

Setup DRBD
drbdadm secondary drbd0
drbdadm coonect drbd0

This is an oversimplified description but you can look up the actual code to sync a DRBD pair

Here is my old DRBD download-and-install script

DRBD_VERSION=8.3
DRBD_SUBVERSION=0
DRBD_VERSION_NUMBER=${DRBD_VERSION}.${DRBD_SUBVERSION}
cd
rm -rf drbdinstall
mkdir drbdinstall
cd drbdinstall
wget http://oss.linbit.com/drbd/${DRBD_VERSION}/drbd-${DRBD_VERSION_NUMBER}.tar.gz
gzip -d drbd-${DRBD_VERSION_NUMBER}.tar.gz
tar -xvf drbd-${DRBD_VERSION_NUMBER}.tar
cd drbd-${DRBD_VERSION_NUMBER}
make rpm
rpm -Uvh dist/RPMS/x86_64/drbd-*

You can go to www.drbd.org to find out the latest version number and update the aforemtioned script

Give it a Try !!!


What is the exact difference between Oracle (+) and comma separate tables and join tables

Write both queries and run EXPLAIN PLAN on both of them. Comparing the plans will identify any performance differences.

From a development perspective, explicit joins offer several advantages over the older syntax.

By keeping the ON clauses together with the tables they reference, it's easier to see the join itself.

Separating out the ON clauses from the WHERE predicates make it easier to understand the developer's intention. Sometimes a spaghetti collection of tables might only have one WHERE predicate after all the join predicates are removed.

It's much harder to forget a predicate with the explicit syntax. If you forget a join predicate, you're going to return a cartesian product. Two tables with a million rows each will form a trillion-row cartesian product. If you know you've forgotten a join condition because your result set is suspicious, it's much easier to identify what's missing with the explicit syntax.

I will argue, against the advice of some longbeards, that even a large number of tables is easier to manage with explicit joins and that no query favors the implicit syntax for readability. Again, this is because the join predicates are kept next to their referenced tables. If you're limiting yourself to 80 columns of text, a long query could mean a lot of flipping up and down with the implicit syntax. If you think the typing is excessive, you should solve this with your text editor and not with sloppy syntax. (Multiline editing in Sublime Text is one solution, for example.)


This is a somewhat common logical error.

('$checkin' BETWEEN start_date AND end_date) OR
('$checkout' BETWEEN start_date AND end_date));

The problem is not the way the query is written, it's that you're using the wrong criteria. A reservation can conflict while neither of these is true, simply by starting before and ending after the desired values.

What you actually need to find is the overlap.

A room is not available if it has any existing reservation with the following conditions true:

a check-in before your desired check-out and
a check-out after your desired check-in

Any reservation that starts before your desired end and ends after your desired start is a conflict.

Stated another way, a check-in after your desired check-out cannot possibly be a conflict, and a check-out before your desired check-in cannot possibly be a conflict.  All others are conflicting.

So you want to identify these reservations:

(end_date > '$checkin') AND
(start_date < '$checkout');

Note also that you must never build queries with variable interpolation, as you have done here.  It is an unsafe practice for which there are no genuine exceptions, only excuses.  Use prepared statements and placeholders.


Import 2 column CSV files to some sort of DB.... preliminary planning

A while ago I wrote a couple of blog posts on loading logfiles into SQLite for analysis. This is an incredibly easy database to use, zero administration, bindings to any language you can think of (e.g. Python and Tcl/Tk as well as exotic ones like OCaml) supports a decent subset of SQL for complex reporting and can handle surprisingly large datasets (millions of rows). You don't say what OS you're on, but on Linux it's just a yum or an apt-get away. I'd definitely choose it over MySQL for this kind of work.


How to get the Mongo log information for particular range from Mongod.log file

You should be able to extract this information using the mtools package, specifically using the mlogfilter tool. The syntax would be similar to this:

mlogfilter mongod.log --from "end -10d" > Last10DaysMongoD.log


How to speed up foreign key creation in MySQL for large tables?

A FOREIGN KEY:

Includes an index, which may help performance during JOINs, sometimes significantly.
Includes a constraint that guarantees the existence of a matching row in another table.

Some cases of adding an index require copying the table over.  Hence the "copy".  Apparently, you have such a situation.  For further discussion, please provide SHOW CREATE TABLE before the command, and the ALTER TABLE that was adding the FK.


How to tell whether mysql was built with libedit or readline?

If on Linux you can just use ldd to check which libraries it's dynamically linked against:

phil@ironforge:~$  ldd `which mysql`
        linux-vdso.so.1 =>  (0x00007ffd5add5000)
        libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007eff0a44e000)
        libreadline.so.5 => /lib/x86_64-linux-gnu/libreadline.so.5 (0x00007eff0a210000)
        libncurses.so.5 => /lib/x86_64-linux-gnu/libncurses.so.5 (0x00007eff09fee000)
        libtinfo.so.5 => /lib/x86_64-linux-gnu/libtinfo.so.5 (0x00007eff09dc4000)
        libz.so.1 => /lib/x86_64-linux-gnu/libz.so.1 (0x00007eff09baa000)
        libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007eff099a6000)
        libstdc++.so.6 => /usr/lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007eff09623000)
        libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007eff0931a000)
        libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007eff08f50000)
        /lib64/ld-linux-x86-64.so.2 (0x000055960d9f2000)
        libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007eff08d39000)
phil@ironforge:~$

In this example on my host, it's linked against libreadline.

It's also in the --help output:

phil@ironforge:~$ mysql --help | grep readline
mysql  Ver 15.1 Distrib 10.0.36-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
phil@ironforge:~$


There can only be one IP address online for the listener at a given time.  Both IP addresses are registered in DNS (by default) even though one is offline, and when a client looks up the address of the listener, it will get two addresses.  If the client is "multi-subnet failover aware," it will try both addresses and use the one that responds

If the client is not "multi-subnet failover aware," it may try to use the IP address that is offline, and accordingly will fail to connect.  So if you start having intermittent client connection failures, this is why.

To have it only register the IP address that is online, change the RegisterAllProvidersIp setting on the listeners network name resource to 0.  See Multi-subnet Clustered SQL + RegisterAllProvidersIP + SharePoint 2013


Thinking about the requirements:

"quickly get access to the data without overloading the DB"
"pull them what was made static [at] the time the view was created"

Especially the part about the data being static captured at a moment in time, I doubt a view is what you want. I think you may want something more like CDC, but that wasn't introduced until SQL Server 2008.  For SQL Server 2005, a database snapshot may meet your needs.
Dominique's answer also potentially an option.  There are multiple ways to fulfil above two requirements. Another example might be to create a stored procedure as shown below and schedule via SQL Server Agent. E.g. assuming source table:
CREATE TABLE SourceTable
(
      C1 [datatype1] PRIMARY KEY
    , C2 [datatype2]
    , C3 [datatype3]
)

and a "Snapshot table" created to match:
CREATE TABLE SnapshotOfSourceTable
(
      C1 [datatype1] PRIMARY KEY
    , C2 [datatype2]
    , C3 [datatype3]
)

then you could do something like this:
CREATE PROCEDURE dbo.UpdateSnapshotOfSourceTable
AS
BEGIN

BEGIN TRAN

BEGIN TRY
    DELETE FROM SnapshotOfSourceTable
    WHERE C1 NOT IN
    (
        SELECT C1
        FROM SourceTable
    )

    UPDATE Snap
    SET
          Snap.C2 = Source.C2
        , Snap.C3 = Source.C3
    FROM SnapshotOfSourceTable [Snap]
    INNER JOIN SourceTable [Source]
    ON Snap.C1 = Source.C1

    INSERT INTO SnapshotOfSourceTable
    (C1, C2, C3)
    SELECT C1, C2, C3
    FROM SourceTable
    WHERE C1 NOT IN
    (
        SELECT C1
        FROM SnapshotOfSourceTable
    )

    COMMIT
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT
        @ErrorMessage = 'Error when attempting to update snapshot table from source: ' + ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               )
        WITH LOG;

    ROLLBACK
END CATCH
END


That depends on what you deleted: was it the database data or the database software?
Since psql still works, I suspect that the database software is still there, and you deleted the data. Then the only way to recover is to restore from a backup.
No, there is no way to "undelete" data.


Page count differences between sys.dm_db_index_physical_stats and sys.allocation_units

Page counts shown by sys.dm_db_index_physical_stats and sys.allocation_units can vary significantly when a variety of DDL modifications are made to the object concerned.
From the Microsoft Docs page on sys.sp_spaceused

There are some situations, for example, after an index is dropped, when the space information for the table may not be current. [...] Use updateusage only when you suspect incorrect values are being returned and when the process will not have an adverse effect on other users or processes in the database. If preferred, DBCC UPDATEUSAGE can be run separately.
When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Deferred drop operations do not release allocated space immediately. Therefore, the values returned by sp_spaceused immediately after dropping or truncating a large object may not reflect the actual disk space available.

sys.dm_db_index_physical_stats has the following definition (in SQL Server 2016):
create function sys.dm_db_index_physical_stats
(
    @DatabaseId         SMALLINT        = 0,
    @ObjectId           INT             = 0,
    @IndexId            INT             = -1,
    @PartitionNumber    INT             = 0,
    @Mode               nvarchar(20)    = NULL
)
returns table
as
    return select *
        from OpenRowset
        (   TABLE
            INDEXANALYSIS,
            @DatabaseId,
            @ObjectId,
            @IndexId,
            @PartitionNumber,
            @Mode
        )

As you can see, it uses the internal table INDEXANALYSIS, whereas  sys.allocation_units uses a different source, ALUCOUNT, as shown in its definition:
CREATE VIEW sys.allocation_units AS
    SELECT au.auid AS allocation_unit_id,
        au.type,
        ip.name AS type_desc,
        au.ownerid AS container_id,
        convert(int, au.fgid) AS data_space_id,
        isnull(ct.reserved_pages, au.pcreserved) AS total_pages,
        isnull(ct.used_pages, au.pcused) AS used_pages,
        isnull(ct.data_pages, au.pcdata) AS data_pages
    FROM sys.sysallocunits au OUTER APPLY OpenRowset(TABLE ALUCOUNT, au.ownerid, au.type, 0, 0) ct
    LEFT JOIN sys.syspalvalues ip ON ip.class = 'AUTY' AND ip.value = au.type

These sources vary in their level of consistency as you can see in the output of your queries.  If you require absolute certainty about the count of pages used by each, you should do the following:

Ensure no open, uncommitted transactions exist that have modified the table/index in question.
Run EXEC sys.sp_spaceused with the @updateusage = 'updateusage' option, or DBCC UPDATEUSAGE.
Run CHECKPOINT to ensure all modifications have been written to disk.
Check the output of sys.dm_db_index_physical_stats and sys.allocation_units; the counts should now be the same.


A join that uses ordered phyical records is more efficient than others?

Most database platforms have different algorithms when joining tables. How tables (or streams of data, really) are joined depends on a number of factors, including the number of rows, the ordering of the rows and the type of join (for instance, if it's a so-called "equi-join" or not).

Assuming the following example:

SELECT a.col1, a.col2, a.col3, b.col4, b.col5, b.col6
FROM table_a AS a
INNER JOIN table_b AS b ON a.x=b.x AND a.y=b.y;

If both table_a and table_b are indexed on the columns (x, y), a highly optimal strategy would be to join those two could be to merge those two datastreams (in SQL Server and other platforms known as a Merge Join). This is particularly true if you add ORDER BY a.x, a.y;, in which case you would want to maintain the ordering from the underlying table.

As a general answer: imagine if your query was two decks of cards in your hands. How would you join them? Would you lay them out in order and merge them (a merge join), perhaps go through one card at a time from one deck and look for matching cards in the other deck (a nested loop join), or maybe subdivide all the cards into 10-15 smaller heaps and join those (more like a hash join)? How would that strategy change if one or both of your decks was ordered or not?


Getting ERROR 1045 (28000): Access denied for user even with proper setup and on different connections

The problem has been fixed.  It was not due to anything MySQL.

We have multiple DB servers, another one was assigned the same IP (10.0.0.201) and neither server picked up on it when doing an ifup.  This would explain why some worked and others didn't, as well as when we got MySQL responses when it was supposedly down.


Given the 'Could not find first log file name' error means Mysql1 has rotated away its binary logs.
You will need to reinitialize Mysql2 as if it where a new replica.
RESET SLAVE ALL on Mysql1 to ensure it forgets about its MySQL2 before beginning.


How to prevent Extended Events from tracking system fired events?

These statements look to be activity generated by SQL Server tools, management API, or a third party product.  This activity happens on regular connections, not a system session which the is_system field identifies.

You might be able to add a filter on client_app_name to exclude the unwanted activity unless some of the activity from that application or tool needs to be captured.


Strange issue with MySql 5.6 replication

Activate the general log on both servers at runtime (beware of doing it when you have a lot of load, as that will make all your queries slower):

SET GLOBAL general_log = 1;

You will find it on datadir/hostname.log, or you can change its location with the general_log_file parameter.

There, all connections and queries sent to the server will be logged. Compare what NaviCat and Workbench are doing to identify a possible problem. You can also check the contents of the binary logs with the utility mysqlbinlog and the changes applied with the commands SHOW MASTER STATUS and SHOW SLAVE STATUS\G on each server, making sure that the replication is not broken (very common in master-master replications).

Remember to disable the general log after you are done:

SET GLOBAL general_log = 0;


How to split the comma separated list in the stored procedure

The definitive article on the topic is Arrays and Lists in SQL Server 2005 and Beyond which contains code and performance test results for a variety of split functions.

You say these are column names? You should probably read another of Erland's articles on The Curse and Blessings of Dynamic SQL where he discusses both SQL injection and when dynamic SQL is a good idea.


I think you are correct. We need at least two distinct subnet to talk about network partitioning.

But if your 10 nodes are in the same subnet, what is the point ? If your network is down, you loose your cluster !
Best practice would be to have 5 nodes in one datacenter, 5 nodes in another data center.


Query to calculate start and end dates from a list of only start dates

Since you're on 2008, you don't have the LEAD function yet (I believe), so you can do it yourself with a CTE:

WITH RankedProcess AS
(
  SELECT ProcessOrder, ProcessDate, ROW_NUMBER() OVER(ORDER BY ProcessDate) AS Seq
  FROM Process
)
SELECT P1.ProcessOrder, P1.ProcessDate AS ProcessBegin, DATEADD(day, -1, P2.ProcessDate) AS ProcessEnd
FROM RankedProcess AS P1
  LEFT OUTER JOIN RankedProcess AS P2
    ON P1.Seq = P2.Seq - 1

In 2012 and later, you can just use LEAD:

SELECT ProcessOrder, ProcessDate AS ProcessBegin, LEAD(DATEADD(day, -1, ProcessDate)) OVER(ORDER BY ProcessDate) AS ProcessEnd
FROM Process

Given the update that ProcessOrder is guaranteed to be consecutive with no missing values (and I'm assuming they are in the same order as the dates), you can get away without the CTE and just use the ProcessOrder column. However, the solution with the CTE is more general, which I think is still useful since I am generally skeptical of data requirements that aren't enforced by the database.


mysql/aurora not immediately seeing results of committed transactions

SELECT * FROM tbl_user WHERE userID = 123 FOR UPDATE;
SELECT * FROM tbl_foo WHERE fk_user = 123 AND unique_column = 'abc';

You are missing FOR UPDATE on the second one?

Can you combine both SELECTs with a JOIN?  (This would help avoid a deadlock.)


There is no issue in both the query they will run properly.

you must inspect any expressions Or find the identifier that is causing this.


PostgeSQL 9.3: Is it safe to stop VACUUM FULL?

Yes, it is safe. Vacuum full will recreate the tables as new objects and only when they're done they will be put to use. If you cancel it, the new files not yet put to use will be removed and old ones will be kept.

Of course if you would cancel it by killing the process handling the vacuum there would be files left, but still the database would be intact.


How to make repairs go faster:

Use the -pr flag.  This repairs only the primary range, which prevents the unnecessary repairs of the additional ranges.  If you run your repairs on all nodes (and you should) then you'll repair everything anyway, but without repairing everything an extra time for each replica.
Focus repairs only on the tables/keyspaces that need it.  Some tables may be static enough that you don't really need to repair them.  For instance, if you don't regularly add new users, you probably don't have to repair system_auth.  Nodetool repair allows you to specify keyspaces and tables to run a repair on, and you should definitely use it.
Avoid streaming repairs from different physical data centers.  This was a big "win" for us.  Nodetool repair allows you to specify a list of hosts to include in the repairs.  If you have multiple data centers, you should limit your node to only repair itself from nodes which are "local" to it.
Focus repairs only on specific token ranges.  You probably won't always have to do this, but sometimes you only have a window that allows you repair for so long.  To programmatically repair say only one third or quarter of your data, you can further focus your repair tasks by specifying certain token ranges.  I don't typically like to do this one, because repairing all data for a table can stretch out for a few days.  But if you have only a small window to run a repair, this might help.

tl;dr :

Don't repair the same data multiple times.
Repair only where you need to.
Stream repairs only from specific, local nodes.
Focus your repairs as much as you can.


In the ANSI SQL Standard, there is no TEXT type. There are various string types defined, like CHAR and VARCHAR and many more but no mention of a TEXT type. There is a CHARACTER LARGE OBJECT and a BINARY LARGE OBJECT.

The various TEXT types found in various DBMS (Postgres, SQL Server, MySQL) are additions and have small differences between them. Their similarity is that they can hold very large strings.

SQL Server's TEXT is being deprecated. MySQL's TEXT is not deprecated. PostgreSQL's TEXT is well supported and recommended by many to be used everywhere, in place of VARCHAR(n). From Postgres documentation, Character Types:

  In addition, PostgreSQL provides the text type, which stores strings of any length. Although the type text is not in the SQL standard, several other SQL database management systems have it as well.

So, the questions:

  The question is, what does the ANSI SQL standard have to say about deprecating text, and the best alternative? Also, does MySQL/MariaDB also follow this?

are simple to answer: Nothing and No. There is no deprecation of TEXT as there is no TEXT type. TEXT isn't even a reserved keyword in ANSI SQL. One DBMS (MySQL) does not have to follow another DBMS's (SQL Server) deprecation decisions for a type that isn't in the standard.


Populate Identity column with sequential numbers restarting count on change of key field

So it was suggested that I post the answer I was able to come up with to resolve my issue.  I decided to create a new table to make sure it would work and then save that table as the original table.

SELECT
*, ROW_NUMBER() OVER(PARTITION BY YEAR_,MONTH_,DATE_,SALESREP
ORDER BY   SALESREP)-1 AS DEL_ID INTO

SALES_INFO_SNAPSHOT_TEMP FROM SALES_INFO_SNAPSHOT_TEST
WHERE DATE_ = '10/31/2018'

This did exactly what I wanted.  A column with sequential numbers beginning with 0 and was inserted with existing data into a new table. The sequential numbers restarted at each change in year_,month_,date_,salesrep.

This allowed me to determine which of four duplicate rows per SalesRep to be deleted.  There were over a thousand rows in all for date_ = '10/31/2018' so I wanted to get this right the first time.
So I was able to determine which row to salvage (the same for each SalesRep) and send a DELETE statement.  It was determined to salvage where ID = '1'.

DELETE FROM SALES_INFO_SNAPSHOT_TEMP WHERE DATE_ = '10/31/2018' AND ID IN ('0','2','3')

Worked like a charm :)


Decompose a relation to 2NF and preserve all the functional dependencies

One decomposition in 3NF (and so also in 2NF) is:

R1(AB)
R2(BCE)
R3(CD)
R4(AD)

This decomposition can be obtained with the so-called synthesis algorithm for 3NF, it is a lossless decomposition and preserves the Functional Dependencies.


ALTER DATABASE script preventing restore of MySQL database in another server

The problem rests with the character set of INFORMATION_SCHEMA.ROUTINES
Run SHOW CREATE TABLE INFORMATION_SCHEMA.ROUTINES\G and you should see
mysql> show create table information_schema.routines\G
*************************** 1. row ***************************
       Table: ROUTINES
Create Table: CREATE TEMPORARY TABLE `ROUTINES` (
  `SPECIFIC_NAME` varchar(64) NOT NULL DEFAULT '',
  `ROUTINE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `ROUTINE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `ROUTINE_NAME` varchar(64) NOT NULL DEFAULT '',
  `ROUTINE_TYPE` varchar(9) NOT NULL DEFAULT '',
  `DATA_TYPE` varchar(64) NOT NULL DEFAULT '',
  `CHARACTER_MAXIMUM_LENGTH` int(21) DEFAULT NULL,
  `CHARACTER_OCTET_LENGTH` int(21) DEFAULT NULL,
  `NUMERIC_PRECISION` bigint(21) unsigned DEFAULT NULL,
  `NUMERIC_SCALE` int(21) DEFAULT NULL,
  `DATETIME_PRECISION` bigint(21) unsigned DEFAULT NULL,
  `CHARACTER_SET_NAME` varchar(64) DEFAULT NULL,
  `COLLATION_NAME` varchar(64) DEFAULT NULL,
  `DTD_IDENTIFIER` longtext,
  `ROUTINE_BODY` varchar(8) NOT NULL DEFAULT '',
  `ROUTINE_DEFINITION` longtext,
  `EXTERNAL_NAME` varchar(64) DEFAULT NULL,
  `EXTERNAL_LANGUAGE` varchar(64) DEFAULT NULL,
  `PARAMETER_STYLE` varchar(8) NOT NULL DEFAULT '',
  `IS_DETERMINISTIC` varchar(3) NOT NULL DEFAULT '',
  `SQL_DATA_ACCESS` varchar(64) NOT NULL DEFAULT '',
  `SQL_PATH` varchar(64) DEFAULT NULL,
  `SECURITY_TYPE` varchar(7) NOT NULL DEFAULT '',
  `CREATED` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `LAST_ALTERED` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `SQL_MODE` varchar(8192) NOT NULL DEFAULT '',
  `ROUTINE_COMMENT` longtext NOT NULL,
  `DEFINER` varchar(77) NOT NULL DEFAULT '',
  `CHARACTER_SET_CLIENT` varchar(32) NOT NULL DEFAULT '',
  `COLLATION_CONNECTION` varchar(32) NOT NULL DEFAULT '',
  `DATABASE_COLLATION` varchar(32) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

In order to load the routines into your new system, you can filter it using grep
EXAMPLE
Suppose the script with the ALTER DATABASE is mydata.sql. Just do this
grep -v "^ALTER DATABASE" mydata.sql | mysql -uroot -p...

This will bypass running the ALTER DATABASE.
Why is ALTER DATABASE included ? According to the MySQL Documentation

mysqldump output can include ALTER DATABASE statements that change the database collation. These may be used when dumping stored programs to preserve their character encodings. To reload a dump file containing such statements, the ALTER privilege for the affected database is required.

The database's character set and collation must have been CHARACTER SET latin1 COLLATE latin1_swedish_ci when you dumped from it. My guess is that the dump originated from a Windows version of MySQL .


Pulling data from identical tables in different databases

This answer assumes you have a master database/table of clients and their associated database names, or some way to figure out which databases in a given instance of SQL Server have relevant databases that you want to query.

Since this is for reporting purposes, you may want to implement some form of snapshotting technique to ensure the data sets consolidate to the same point in time, on at least a per-client basis. As a nice side effect, these techniques usually also alleviate locking/blocking effects of running these types of queries directly against the production data.

Pure Dynamic SQL
Using the master list, build up a SQL statement that uses 3-part names like you're doing now, but instead, inject the database name dynamically. It's unclear from the question what format the data is going to end up in, but it sounds like you want to UNION ALL everything together, which is easily accomplished.

This may be advantageous for you now because it will require very little up-front work.

The downside is that this approach is not very flexible and quickly becomes complicated if you need to run the query when the individual database revisions are different (i.e., the query was tied to a given database revision, and when a database update was deployed, it didn't succeed for all clients). It also may have problems or complications depending on the security model you're using for these databases.

Database-Based Object(s) + Dynamic SQL
This solution creates permanent objects within every database which you can query in an external process. Usually this means a view, stored procedure, or table-valued function. Dynamic SQL will be used to inject the database names into the query as above, but this time all that's needed is to select from, or execute, the database object instead of the raw query directly.

This method gives all the advantages of protecting your code behind an interface just like it would by using views, stored procs, etc., in application code. This also solves the security issues much more easily.

The disadvantage, of course, is that this needs a little more up-front work and management, and that the queried object(s) can only be deployed in database updates, instead of independently.

My personal opinion is that the latter approach is a much better long-term solution, so in this type of scenario, that's the one I would opt for. For ad-hoc types of queries, using the pure dynamic SQL approach is fine.


You can use DMV sys.dm_fts_index_keywords_by_document to list the words for a given document, eg something like this:

SELECT DISTINCT display_term
FROM sys.dm_fts_index_keywords_by_document( DB_ID(), OBJECT_ID('dbo.Users') ) X

I had a play with this product a few years ago that does hit-highlighting, it was quite nice but I didn't go past the 30-day trial.


This is how I'd do it:

SELECT      *
FROM        #MyTable AS mt
CROSS APPLY (   SELECT COUNT(DISTINCT mt2.Col_B) AS dc
                FROM   #MyTable AS mt2
                WHERE  mt2.Col_A = mt.Col_A
                -- GROUP BY mt2.Col_A
            ) AS ca;

The GROUP BY clause is redundant given the data provided in the question, but may give you a better execution plan. See the follow-up Q & A CROSS APPLY produces outer join.

Consider voting for OVER clause enhancement request - DISTINCT clause for aggregate functions on the feedback site if you would like that feature added to SQL Server.


Problem with mysql group_concat, complex query, not getting required result

It will be something like this:

SELECT cname, ctype, tname, seats, hostel, dname,
GROUP_CONCAT(addr SEPARATOR '\n') addresses
FROM
(
    SELECT cname, ctype, tname, seats, hostel, dname,
    CONCAT('Address: ',address,',Phone:',ph_no,',Email:',email,',Website:',website) addr
    FROM college, trade, district, intake, contact
    WHERE intake.cid = college.cid
    and intake.tid = trade.tid
    and college.did = district.did
    and contact.cid = college.cid
    AND cname LIKE '%{$cname}%'
    AND tname LIKE '%{$tname}%' {$ctype}
    AND dname LIKE '%{$dname}%' {$hostel}
    ORDER BY cname ASC
) A GROUP BY cname, ctype, tname, seats, hostel, dname;

Since group_concat_max_len has a default of 1024, you may need do two more things to accommodate GROUP_CONCAT results longer that 1024

First, login to MySQL and run

mysql> SET GLOBAL group_concat_max_len = 1024 * 1024;

Then, add this to my.cnf under the [mysqld] group header

[mysqld]
group_concat_max_len=1M

GIVE IT A TRY !!!


SQL concatenate VARCHAR + VARBINARY with VARBINARY as hex

this is what i wanted to do

DECLARE @test VARCHAR(50) = 'Hello World!'
DECLARE @testvb VARBINARY(256)

SET @testvb = CAST(@test AS VARBINARY(256));

SELECT @test + CONVERT(VARCHAR(MAX),@testvb,2);
GO

|(No column name)                    |
|:-----------------------------------|
|Hello World!48656C6C6F20576F726C6421|

dbfiddle here

From this Aaron Bertrand blog post, I found out that CAST converts it back to original value whereas CONVERT has an option to keep the varbinary value:

SELECT @test + CAST(@testvb AS VARCHAR(MAX)) --converts back to Hello World!
SELECT @test + CONVERT(VARCHAR(MAX),@testvb,2) --keeps varbinary value


select tbl1.col1,tbl2.col2,tbl3.col3,
tbl4.col4,tbl5.col5,tbl6.col6,tbl7.col7,tbl8.col8,
tbl9.col9,tbl10.col10

from tbl1.col1 as tbc1

left outer join tbl2.col2 as tbc2 on tbl1.PK=tbl2.FK
left outer join tbl3 as tbc3 on tbl2.PK=tbl3.FK
left outer join tbl4 as tbc4 on tbl3.PK=tbl4.FK
left outer join tbl5 as tbc5 on tbl4.PK=tbl5.FK
left outer join tbl6 as tbc6 on tbl5.PK=tbl6.FK
left outer join tbl7 as tbc7 on tbl6.PK=tbl7.FK
left outer join tbl8 as tbc8 on tbl7.PK=tbl8.FK
left outer join tbl9 as tbc9 on tbl8.PK=tbl9.FK
left outer join tbl10 as tbc10 on tbl9.PK=tbl10.FK

where tbl1.col1='some particular column value'
Group By tbl1.col1
Order tbl1.col1;

I am writing the sql query but it will depend on your table structure . How many there is PK and FK in your each table.


Truncation vs Deletion When `session_replication_role = replica`

First and foremost you should know the name of the FOREIGN KEY constraint:
CREATE TABLE users (
    id serial PRIMARY KEY
);

CREATE TABLE posts(
    id serial PRIMARY KEY,
    user_id INTEGER,

    CONSTRAINT fk_post_users
        FOREIGN KEY (user_id) REFERENCES users(id)
);

INSERT INTO users(id) VALUES (1);
INSERT INTO posts(id, user_id) VALUES (1, 1);

Then you can DROP FOREIGN KEY and after that, TRUNCATE the table.
ALTER TABLE posts DROP CONSTRAINT fk_post_users;
TRUNCATE users;

Now, posts table remains in an inconsistent state, but you can add the foreign key again using NOT VALID option, that does not check current values.
ALTER TABLE posts ADD CONSTRAINT fk_post_users
    FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;

db<>fiddle here
Quoted from Postgres docs:

ADD table_constraint [ NOT VALID ]
This form adds a new constraint to a table using the same syntax as CREATE TABLE, plus the option NOT VALID, which is currently only allowed for foreign key and CHECK constraints. If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. The constraint will still be enforced against subsequent inserts or updates (that is, they'll fail unless there is a matching row in the referenced table, in the case of foreign keys; and they'll fail unless the new row matches the specified check constraints). But the database will not assume that the constraint holds for all rows in the table, until it is validated by using the VALIDATE CONSTRAINT option. Foreign key constraints on partitioned tables may not be declared NOT VALID at present.
The addition of a foreign key constraint requires a SHARE ROW EXCLUSIVE lock on the referenced table.
Additional restrictions apply when unique or primary key constraints are added to partitioned tables; see CREATE TABLE.


send_db_mail with attachment query can't see user databases

When you launch the query through SSMS, it will use your account, but when it is launched through SQLAgent job, it will use the account that runs the service. Can can double check that in send_request_user column of sysmail_allitems table.

Maybe they have different rights ?


Mongo database error in resource allocation

Mostly because of memory utilization. Observed mongodb log and found that the same time at which pthread_create failed, other processes were consuming memory so no room for mongodb.


Schema design for privacy settings in MongoDB

Your documents can contain some inline privacy section-document or reference to that document in some security collection (for example) where you can specify whether to access.
On the assumption of your problem it can be looks like:

{
    "status":"private", // one of allow-all, private, deny-all
    "deny":[_id('user'),...],
    "allow":[_id('user'),..]
}

where "status" is the main policy for document witch will be handled with your code.
It is just an example. It can be done with many different ways.

Cheers.


MySQL monitor prompt does not return for slave

When this happens, I immediately go to the Slave and
If you have MySQL 5.1+
SELECT id FROM information_schema.processlist WHERE user='system user';

This will show you the Process ID of the IO Thread and SQL Thread of Replication.
If you have MySQL 5.0
SHOW PROCESSLIST;

You would have look for the two rows in the display that has 'system user' as the user.
Once you find the IDs...
You can run the KILL or KILL QUERY command against the Process IDs.
CAVEAT
This is a possibility that evn the KILL command can hang if the query must come to a completion (such as a transaction that must fully commit or fully rollback).
Master is oblivious to all this.
Why does SHOW MASTER STATUS; work just fine ???
Running SHOW MASTER STATUS; simply checks for binary logs on the DB Server. There are no DB threads to read, parse, examine, and coalesce.
Running SHOW SLAVE STATUS\G has to reconcile three(3) aspects:

Aspect #1 : the Contents of master.info
Aspect #2 : the current list of relay logs
Aspect #3 : information acquired from the IO and SQL threads (live)

This reconciliation helps produce the output of SHOW SLAVE STATUS\G. It's Aspect #3 that makes SHOW SLAVE STATUS\G freeze attempts to kill the SQL thread on a long running query.


Performance Metrics After Enabling Auto-Stats

If SET AUTO_CREATE_STATISTICS ON improved performance (anecdotally) for your end users, it likely means that queries with predicates on columns without statistics have gotten better execution plans, and thus shorter runtimes.  A "better" execution plan could result in one or more of these improvements:

Fewer logical / physical reads
Lower CPU usage
Shorter query runtimes / duration

I'm not sure what metrics you're capturing.  If you're able to get to the level of individual query execution time, you could find examples of the same queries before and after the change and confirm that they have reduced runtimes (or less CPU and reads, etc).

For example, if you're logging the contents of sys.dm_exec_query_stats to a table, you could find queries that have the same sql_handle but lower CPU, reads, etc after the setting was changed.

If you are logging execution plan XML, you might be able to look for the presence, and then absence, of warnings like this:

It shows up in the XML like this:

  <Warnings>
    <ColumnsWithNoStatistics>
      <ColumnReference Database="[TestDB]" Schema="[dbo]" Table="[Hist]" Column="Num" />
    </ColumnsWithNoStatistics>
  </Warnings>

If you just have server level aggregated metrics to look at, you might still see overall reads or CPU has gone down, but it's probably a lot harder to see that for sure if it's watered down by other workloads and maintenance activities.


What am I missing for my SSRS Service account local server permissions?

As part of the troubleshooting I was doing to try and address the issue I ran Process Monitor while I was trying to start the service.

I tracked as event that also had a result of "Access Denied" which was the service trying to read files inside the directory where reporting services was installed an running from. In my case it was: "C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services"

I check the security of the folder and the service account I was using had no rights to the folder. That is why giving it local admin rights fixed it because that group did have access.

I gave my service account Modify access to the folder and its contents. After that I was able to start the service.


DBCC CHECKDB REPAIR_REBUILD taking too long

You could always run the following script to check the running tasks:

SELECT
    start_time,
    DATEADD(ms,estimated_completion_time,GETDATE()) AS 'EstimatedEndTime',
    percent_complete,
    --sqltext.TEXT,
    req.session_id,
    req.status,
    req.command,
    req.cpu_time,
    req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle)
where req.command like '%DBCC%'

Comment out the sqltext.TEXT column if you wish to view the whole statement.


How to add a user with access to a single view?

Please don't use the UI for this. It's a confusing mess.

It sounds to me like what you want is to create a user in a database, for a specific login, who only has permissions to select from one view. So, since you already have the login created:

USE your_db;
GO
CREATE USER username FROM LOGIN username;
GO
GRANT SELECT ON dbo.MyViewName TO username;
GO

EDIT here is an example of a script that will lead to the error you mention.

First, create some table in the unrelated_db:

CREATE DATABASE unrelated_db;
GO
USE unrelated_db;
GO
CREATE TABLE dbo.foo(bar INT);
GO

Now create a relatively restricted login:

USE [master];
GO
CREATE LOGIN username WITH PASSWORD='foo', CHECK_POLICY = OFF;
GO

Now create a database where the view will live, and add the login as a user:

CREATE DATABASE velojason;
GO
USE velojason;
GO
CREATE USER username FROM LOGIN username;
GO

Now create a function that will reference the table in the other database, and a synonym to the other table:

CREATE FUNCTION dbo.checkbar()
RETURNS INT
AS
BEGIN
    RETURN
    (
      SELECT TOP (1) bar
        FROM unrelated_db.dbo.foo
        ORDER BY bar
    );
END
GO
CREATE SYNONYM dbo.foo FOR unrelated_db.dbo.foo;
GO

Now create a local table:

CREATE TABLE dbo.PaymentDetails
(
  PaymentID INT
);
GO

Now create a view that references the table, the function and the synonym, and grant SELECT to username:

CREATE VIEW dbo.SomeView
AS
  SELECT
    p.PaymentID,
    x = dbo.checkbar(), -- function that pulls from other DB
    y = (SELECT bar FROM dbo.foo) -- synonym to other DB
    FROM dbo.PaymentDetails AS p;
GO
GRANT SELECT ON dbo.SomeView TO username;
GO

Now try to execute as username and select only the local column from the view:

EXECUTE AS USER = 'username';
GO
  -- even though I don't reference any of the columns
  -- in the other DB, I am denied SELECT on the view:
SELECT PaymentID FROM dbo.SomeView;
GO
REVERT;
GO

Result:

  Msg 916, Level 14, State 1, Line 3 The server principal "username" is
  not able to access the database "unrelated_db" under the current
  security context.

Now change the view to not reference any external objects, and run the above SELECT again, and it works:

ALTER VIEW dbo.SomeView
AS
  SELECT
    p.PaymentID
    --x = dbo.checkbar(),
    --y = (SELECT bar FROM dbo.foo)
    FROM dbo.PaymentDetails AS p;
GO

Short of showing us the scripts for the Payment Details, Account Details and MyView objects, maybe you can let us know if this query returns any results. You can find references to various objects through the catalog view sys.sql_expression_dependencies, but this view is not perfect - I believe it depends on all the views being refreshed (in the case where views reference other views, for example, or underlying schema has changed) in order to be accurate.

DECLARE
  @dbname   SYSNAME = N'unrelated_db',
  @viewname SYSNAME = N'dbo.SomeView';

SELECT DISTINCT
    [This object] =
    OBJECT_SCHEMA_NAME([referencing_id])
      + '.' + OBJECT_NAME([referencing_id]),
    [references this object] =
    OBJECT_SCHEMA_NAME([referenced_id])
      + '.' + OBJECT_NAME([referenced_id]),
    [and touches this database] = referenced_database_name,
    [and is a(n)] = o.type_desc,
    [if synonym, it references] = s.base_object_name
FROM sys.sql_expression_dependencies AS d
LEFT OUTER JOIN sys.objects AS o
ON o.[object_id] = d.referenced_id
LEFT OUTER JOIN sys.synonyms AS s
ON d.referenced_id = s.[object_id]
AND s.base_object_name LIKE '%[' + @dbname + ']%'
WHERE OBJECT_ID(@viewname) IN (
        referenced_id,
        referencing_id,
        (SELECT referencing_id FROM sys.sql_expression_dependencies
        WHERE referenced_database_name = @dbname)
) OR referenced_database_name = @dbname;

SQL Server isn't just going to try to access unrelated_db for the fun of it... there must be some tie to that database from the view you're trying to use. Unfortunately if we can't see the view definition and more details about the objects it touches, all we can do is speculate. The two main things I can think of are synonyms or functions that use three-part names, but seeing the actual scripts will give us a much better idea instead of guessing. :-)

You may also want to check sys.dm_sql_referenced_entities, however this function returns nothing useful in the example above.


Use if else sql statement with two selects

I just added  SELECT @UgpEntry = UgpEntry FROM OITM t1 WHERE t1.ItemCode='' and it works
 DECLARE @UgpEntry VARCHAR(50)

SELECT @UgpEntry = UgpEntry FROM OITM t1 WHERE t1.ItemCode='v6p'

IF (@UgpEntry = -1)

SELECT
t1.ItemCode as sapitemcode
     , t1.CodeBars as Barcode
     , t1.ItemName as description
     ,LEFT(t1.ItemName,20) as short_description
     ,
           (select max(p.Price)
        from ITM1 p
        where p.ItemCode = t1.ItemCode
          and p.PriceList = 1) as [price_1]
     , CASE t1.VatGourpSa when 'V0' THEN 4 when 'V1' THEN 1 WHEN 'V2' THEN 2 WHEN 'V3' THEN 3 END as TaxCode,t1.U_GRUPOA, t1.U_GRUPOB, t1.U_GRUPOC, t1.UgpEntry

FROM OITM t1
WHERE t1.ItemCode='v6p' and t1.UgpEntry = -1

ELSE

SELECT t1.ItemCode as sapitemcode
     , t1.CodeBars as Barcode
     , t1.ItemName as description
     ,LEFT(t1.ItemName,20) as short_description
     , (select max(p.Price)
        from ITM9 p
        where p.ItemCode = t1.ItemCode
          and p.UomEntry = 1) as [price_1],

           (select max(p.Price)
        from ITM1 p
        where p.ItemCode = t1.ItemCode
          and p.PriceList = 1) as [preciocaja]
     , CASE t1.VatGourpSa when 'V0' THEN 4 when 'V1' THEN 1 WHEN 'V2' THEN 2 WHEN 'V3' THEN 3 END as TaxCode,t1.U_GRUPOA, t1.U_GRUPOB, t1.U_GRUPOC, t1.UgpEntry

FROM OITM t1
WHERE t1.ItemCode='v6p' and t1.UgpEntry > -1


Normally you never have to run VACUUM (FULL). It is only necessary if the tables have become unduly bloated, either because of autovacuum being too slow or because of mass updates.

I would examine the tables you suspect to be bloated with pgstattuple to see how big free_percent is.

20% bloat is normal. Consider VACUUM (FULL) only if you have much more bloat.


How do I get useful SQL Server database performance statistics?

You can gather data you need with Data Collector, which is a part of the SQL Server Management Studio (not in all SQL Server editions though), but it’s features are limited and it won’t show you any real-time data.

It seems you need an ad-hoc solution and, if that is the case, it’s better to use some 3rd party monitoring solutions to gather data and get the job done during free trial. All these tools are great and are offering fully functional free trials:

ApexSQL Monitor

Idera SQL Diagnostic Manager

Redgate SQL Monitor

SQL Sentry


Backup from dacpac file extracted using SSTD

Have a look at the commentary here. Has the login been created on each instance using the same SID?  If not then this might be the cause of the issue.


MySQL - installed version 5.5 but mysql --version says 5.0.95

This problem may happen if you have multi MySQL instances on the same machine. There was pre-installed MySQL when the OS was installed, or using auto installation methods like yum: in this case when you type mysql it will point to the pre-installed one since the environment variable point there. you can test it by going to /mysql/install/path/bin use ./mysql --version this should show you the exact version of MySQL.


No.  Do not expect much, if any, performance boost with OPTIMIZE TABLE.

ANALYZE TABLE is slow on MyISAM, the engine you should be moving away from.  It is fast in InnoDB.  It is almost never needed in either engine.  Only if you have a particular query that defies rational analysis by a human, should you consider ANALYZE.

OPTIMIZE TABLE will rebuild the table.  That requires blocking all writes to it for the duration.

Back to your final question...  Dumping the data (not the files) will provide you with something that can be loaded into another server for the testing you suggest.  However, the dump, itself, involves some degree of locking.  This depends on which engine you are using and whether you need a "consistent" dump.

SHOW TABLE STATUS will give you a clue of Data_free.  For MyISAM, that is how much space can be recouped with OPTIMIZE.  But even a large number there does not necessarily mean much performance boost.  For InnoDB, Data_free has less meaning and its meaning depends on the value of innodb_file_per_table when the table was created.


Expanding contractions/number ranges into separate records

In the process of ETL  (extract, transform, and load) you are in the transform stage.

Not being familiar with the vendor specific tools you mention my thoughts are that your going to have to custom build an intermediate step with your own tools or development environment to "transform" the xml file.

This would be possible with most any scripting/programming language. Do you have experience with .Net, Ruby, or other programming languages?

If I were doing this project I would write a .Net utility to scan through the XML file looking for hyphens in the ref column.  I'd then parse the numbers on each side adding additional records as you've described to take the original XML from what it is to a new one in the format you want.  Once done you can continue on with the "load" part of the ETL process.

Since you can conceptualize what your trying to accomplish if you can now select a framework such as .Net or Ruby to do it in, there's a lot of guys at www.stackoverflow.com who could help you get where you're needing to go.


Selecting minimum value in top grouping only

Not sure about the efficient part, but the following does the trick. You'll want (emp_id, work_dt) indexed at least, having the time_type in there should not hurt either.

Assuming this table:

create table times(emp_id number, work_dt date, time_type number);

You can run this: (SQL Fiddle)

-- get the last work date
with last_dt as (
  select emp_id, max(work_dt) last_dt
  from times
  group by emp_id
),
-- get the last time type
last_rec as (
  select l.emp_id, l.last_dt, t.time_type
  from last_dt l
  join times t on l.emp_id = t.emp_id
              and l.last_dt = t.work_dt
),
-- get the last record for an employee
-- that isn't of the last time type
-- last_ot will be null for employees
-- who always do the same thing
last_other_type as (
  select l.emp_id
  , min(l.time_type) time_type -- "constant" per employee
  , min(l.last_dt) last_dt     -- same
  , max(t.work_dt) last_ot
  from last_rec l
  left join times t on l.emp_id = t.emp_id
              and l.time_type != t.time_type
  group by l.emp_id
)
select f.emp_id
, case when f.last_ot is null
    then (select min(work_dt) from times where emp_id = f.emp_id)
    else (select min(work_dt) from times where emp_id = f.emp_id
                                           and time_type = f.time_type
                                           and work_dt > f.last_ot)
                                           -- use >= if there can be more than
                                           -- one record per day
  end first_dt
, f.last_dt
, f.time_type
from last_other_type f

The idea is to get the latest time and type for each employee, then find the latest date where they worked on something different.

If such a date is found, return the first time they did the last type of time of work that is later than that.
If not, then the employee only did one type of work, so pick the very first date recorded.

For Oracle 12c, there's the match_recognize tool you can use to process this sort of "custom" grouping.

select emp_id, nr
, time_type
, first_dt
, last_dt
from times
match_recognize (
  partition by emp_id order by work_dt desc
  measures match_number() as nr
  , first(a.work_dt)   as first_dt
  , first(a.time_type) as time_type
  , last(a.work_dt)    as last_dt
  pattern (a+)
  define a as (a.time_type = first(a.time_type))
)
-- where nr = 1

This returns:

    EMP_ID         NR  TIME_TYPE FIRST_DT  LAST_DT
---------- ---------- ---------- --------- ---------
     12345          1         10 19-DEC-14 17-DEC-14
     12345          2         20 16-DEC-14 15-DEC-14
     12345          3         10 14-DEC-14 13-DEC-14
     54321          1         10 19-DEC-14 19-DEC-14
     54321          2         20 18-DEC-14 18-DEC-14

Filter on nr == 1 and you're done.


If I correctly understood, this should give the desired result:

create table weborders (id int, total int , ts timestamp);
insert into weborders values
(1, 0, '20200101'),
(2, 5, '20200102'),
(3, 0, '20200103'),
(4, 3, '20200104');

with ct as
(
  select   0 as row_no, id, ts
  from     weborders
  where    total > 0
  order by id desc limit 1
)
, ct2 as
(
  select   1 as row_no, id, ts
  from     weborders
  where    total = 0
  order by id desc limit 1
)
select id, ts
from
(
  select row_no, id, ts from ct
  union
  select row_no, id, ts from ct2
) a
order by row_no;

or alternatively:

select id, ts
from
(
 select 0 as row_no, id, ts
 from   weborders
 where id = (select max(id) from weborders where total > 0)
 union
 select 1 as row_no, id, ts
 from   weborders
 where id = (select max(id) from weborders where total = 0)
) a
order by row_no;

id | ts
-: | :------------------
 4 | 2020-01-04 00:00:00
 3 | 2020-01-03 00:00:00

db<>fiddle here

And another smart solution, thanks to @ypercubeᵀᴹ for this.

select distinct on (total > 0)
         id, ts
from     weborders
order by (total > 0), id desc ;

db<>fiddle here


Removing multiple values using if else statements?

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

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

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

Example:

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

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

Result:

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

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

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


Large Table Times out - Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding

The code doesn't have a specific Time-out set in the Connection String
  to the Database but on the SQL Server, the Time-out is set to 10
  minutes.

You are most probably reaching a client timeout which by default is 30 seconds for C# SQL Server client (ADO.NET?). Consider increasing this timeout until you find a decent solution that does not force the client into waiting so much time.

  We have checked the Indexes for this table and it seems ok (see image
  attached).

Typically indexes are not OK by default, but based on actual SELECT queries that hit the table. In your case you seem to issue an UPDATE and the first thing that comes into my mind is that you have a lot of indexes (5) on that table and UPDATE might need to also update most of them. Just from the name, idx_ordernumber and idx_ordernumber_Status seem to be very similar and they might be merged into a single index.

The following is mostly an approach made by a .NET programmer and it is not optimal from a DBA's perspective, but it might provide useful insight before diving into more DBA savvy stuff. Try this on a development / test database that has little activity, but a data volume similar to that from production:

Run SQL Server Profiler (yes, it is deprecated, but the tool is still very useful for quick checks)
Create a new trace and double check that you are filtering by your database, remove audit events and include events like *:Starting + *:Completed
Run your UPDATE scenario. You should see some queries and I expect that your problematic query to be more expensive (perhaps a rather large duration). Also, this step might reveal another issue: lots of queries issued by that "update" bundled into a single transaction that might take a very long time (in spite of each being very quick). This later is a client side issue, not a DB one.
You might run the query again in SQL Server Management Studio by also including Actual Execution Plan to see what is causing the rather long execution

If you still do not figure out what is the cause, edit your question and provide what you have found out: the query, the execution plan, duration etc.


unable to connect to oracle in linux terminal in oracle VM ,TNS listner not connected

You try to connect with / as sysdba, which normally connects locally, and you still get errors related to TNS and listener. Actually, there is a not well-known environment variable, called TWO_TASK, and if you set that, SQL*Plus will automatically try to connect using the value of the variable as a TNS entry.

So if you use TWO_TASK, instead of this:

sqlplus user/password@TNS

you can do this:

export TWO_TASK=TNS
sqlplus user/password

The second method will have the same result as the first, the difference is you do not need to specify the TNS on every connection, you can simply put it in a variable, it may be useful when writing scripts, though I have never used it. Another use-case for it is when you have a CDB and you want to connect to a PDB. You can not connect a PDB directly locally, but you can kind of "emulate" that with TWO_TASK.

So I have the below environment:

[oracle@o61 ~]$ . oraenv
ORACLE_SID = [oracle] ? orcl
The Oracle base has been set to /opt/oracle/base
[oracle@o61 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 22-JAN-2016 23:15:23

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                15-JAN-2016 18:17:03
Uptime                    7 days 4 hr. 58 min. 20 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/grid12102/network/admin/listener.ora
Listener Log File         /opt/oracle/base/diag/tnslsnr/o61/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.6.1)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

Try to connect with / as sysdba:

[oracle@o61 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 22 23:15:28 2016

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@o61 ~]$

So far so good, / as sysdba works like a charm. Now let's say I have a TNS entry pointing to another service, that does not exist, for example:

[oracle@o61 ~]$ tnsping mydb

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 22-JAN-2016 23:20:47

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = MYDB)))
OK (0 msec)
[oracle@o61 ~]$

tnsping say everything is OK. Well, tnsping does not check the specified service, it only checks the address and port. Still, it is a valid TNS entry anyway. If I try to connect using this TNS entry, it will obviously fail with the infamous 12514 error, as I do not have any service like this, as you could see from my lsnrctl status output. I do not even need to specify a valid username and password to get the error.

[oracle@o61 ~]$ sqlplus user/password@mydb

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 22 23:21:50 2016

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Enter user-name:

And here comes TWO_TASK, the variable that was meant to make your life easier, but it is also a great feature to play a prank on someone who does not know about it. Even if you connect / as sysdba, SQL*Plus will try to open a remote connection because of it:

[oracle@o61 ~]$ export TWO_TASK=mydb
[oracle@o61 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 22 23:29:21 2016

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Enter user-name: oracle
Enter password:
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Enter user-name:

If you have TWO_TASK set, just unset it, and try to connect again.

[oracle@o61 ~]$ unset TWO_TASK
[oracle@o61 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 22 23:31:24 2016

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>


Optimizing multiple MySQL sub-queries and joins for multiple runs

Avoid derived tables, like ypercube mentioned.

SELECT
    TRIM(W_OEINVCD.pn) as productId,
    W_OEINVCD.qtyord as qty,
    W_OEINVCH.invno as invoiceId,
    W_OEINVCH.invdate as invoiceDate
FROM
    W_OEINVCH
    JOIN W_OEINVCD
        ON W_OEINVCH.invno = W_OEINVCD.invno
WHERE
    W_OEINVCH.invdate BETWEEN '2013-08-01' AND '2013-11-06'
    AND W_OEINVCH.custid = '13651'


How can I genereate a string from the returns of a SELECT query in Mysql?

Please try this query:

select group_concat(foo_id separator ',') as ids from
(
select
 case
 when locate('-', foo_ids) > 0 then
  substr(foo_ids, 1, locate('-', foo_ids) - 1)
 else foo_ids
 end as foo_id
from foo_table
union all
select
 case
 when locate('-', foo_ids) > 0 then
  substr(foo_ids, (locate(foo_ids, '-') + 1), locate('-', foo_ids) - 1)
 else null
 end as foo_id
from foo_table
) t
where foo_id is not null

http://sqlfiddle.com/#!2/ce1b0a/7


With Postgresql v10 and WAL2JSON... Is it possible to include the database name / datname in the json?

I don't think this is possible because AFAIK database name or related oid is not stored in JSON columns. But it is always possible to retrieve current database name in a SQL session with:

select current_database();


As Colin touched on, it's hard to follow what schema problem you're trying to solve with just generalities. Unfortunately the devil's in the details with this kind of thing, since one detail could drastically change the best approach.
Looking at your database diagrams at a high level the tables A, B, and C in the first diagram look like the same entity in regards to their purpose, but varies in value of their columns, most likely?...same when comparing tables ANumeral, BNumeral, CNumeral to each other, or when comparing AStep, BStep, and CStep to each other, in your second diagram. The question is why couldn't each of these sets of tables be just one table each (or possibly even one table altogether)?...e.g. you likely could have just a single StepAttributes table that Step joins to.
I did notice in your example the only difference between two tables of the same set are the attribute data types. If that's your reasoning for making multiple tables, instead just structure your single attribute table (e.g. StepAttribute) to account for that which can be done two ways.
The first way by having three columns, one being the AttributeName, the next being the AttributeValue (which will be a string type field, like VARCHAR, to accommodate any value), and the third being the AttributeDataType which stores the native data type of the attribute so you know what to appropriately cast it to when you consume it.
The second way is you can have a denormalized attribute table that has a nullable column of each data type with generic column names, such as DateAttributeValue, IntAttributeValue, VarCharAtrributeValue etc, and a column to specify which data type the value is / column it's stored in. Some systems even design such a table like DateAttributeValue1, DateAttributeValue2, DateAttributeValue3, IntAttributeValue1, IntAttributeValue2, etc so they can support storing multiple attributes within the same row.

Thanks for updating your question with more details. So at the least it looks like each sub-table to Numeral vs Step are a bit redundant in your second design, and I understand why you normalized them that way based on your first design. You could actually combine the best of both worlds though and just have a single Plot, Simplify, and Solve table with the foreign key reference field in those tables going back to your Numeral and Step tables by storing both IDs in that column and calling it something like NuneralOrStepId. Then you'd need an additional field on those tables called like IsForNumeral or ParentType (come up with a better name than mine lol) which would distinguish which base table it joins back to. Then you have no waste in fields and reduce your redundancy as well.
It depends on how many of these sub-tables you anticipate having to generate and manage (right now 3 is completely manageable) but if you think that's going to grow into over 10 tables or so, I'd still consider my first suggestion with one generic table of AttributeName / AttributeValue pairs. You can add any additional columns to this structure that make sense as well, such as the ParentType column to distinguish which parent table the Attribute belongs to.


How to SELECT a column based on a condition on the column's name?

You can do this without dynamic SQL with some XML trickery.

Create XML of all values for each row and and use local-name() function to extract the value of the column you want.

Here is an example where you perhaps need to change the data types. I assumed integers.

create table dbo.T(Code int, U_Col1 int, U_Col2 int, U_Col3 int)

go

create function dbo.GetT
(
  @Name nvarchar(128),
  @Code int
)
returns table
as
return (
       select T.Code,
              C.X.value('(*[local-name() = sql:variable("@Name")]/text())[1]', 'int') as U_Col
       from dbo.T
         cross apply(select T.* for xml path(''), type) as C(X)
       where T.Code = @Code
       );
go

insert into dbo.T (Code, U_Col1, U_Col2, U_Col3) values
(123, 1, 2, 3),
(456, 4, 5, 6)

go

select * from dbo.GetT('U_Col1', 123);
select * from dbo.GetT('U_Col2', 456);

Note that the names of all columns in the table must be valid as element names in XML.

dbfiddle.uk


Are these database indexes correct for my search

The clustered index in your question will optimize the select query because:

1) you are requesting all columns

2) the first column satisfies the equality predicate on iAssetID

3) the second column is useful for the dtUTCDateTime inequality predicate

Since you also have a surrogate iVehicleMonitoringId key that serves no purpose other than to provide a unique value, consider changing your primary key to a clustered composite one on iAssetId, dtUTCDateTime, and iVehicleMonitoringId.  That would avoid the need for a separate index.  My assumption is you have no tables that reference this log table so the wider key shouldn't be an issue.


Trigger to cascade updates to a second table

While this trigger function is expensively incorrect and needs to be fixed, it can't explain the problem you describe. There must be some other cause, not evident from your question, yet. Some other broken trigger on table1 or table2?

This trigger updated the same row in table2 once for every row in table1 and the final value of table2.code_tranche was an arbitrary pick from table1.

Remove from schema.table1:

UPDATE schema.table2
SET   (contributor, code_tranche) = (user, NEW.code_tranche) from schema.table1
WHERE  table2.gid = NEW.gid;

And I assume you are aware that user is s synonym for current_user, returning the user name of current execution context. If it's supposed to be a column name, you have to use NEW."user" instead. (But never use reserved words as identifiers to begin with - like jjanes commented.)

Typically, this kind of trigger indicates a problem with your db design. Try to remove redundancy. Storing information in one table should suffice.


How do i disable or limit Postgres Replication creating causing too many WAL files generated?

The database generates WAL for the entire database instance.  It is the logical sender's job to sort them out to restrict and construct what gets sent.  I don't think there is a way to get the logical sender to work ahead.  You should look at why it is falling behind, not why there is a lot of WAL.


If you can group your data in a way that makes row 1-8 group 1 and the other rows group 2 then you can do a page break at the end of each group.


Correct SQL Server linked server connection settings to MySQL that will allow inserts

Just a quick observation: you using MySQL 5.1 Driver against MySQl 5.0.92

I recommend you should either

downgrade to MySQL 5.0 ODBC Driver (easier option)
upgrade to MySQL 5.1.61


You can download the latest SQL Developer from Oracle and get the specific instructions here on what is required to do the installation.

Once SQL Developer starts, the next obvious step is to connect to a database and this will depend on how Oracle is configured. While I believe this is a separate issue, for sake of completeness I'll add that by default SQL Developer will use xe for the SID in the connection wizard, which is for express edition, and that may not be sufficient to make a connection. Another way is to try orcl instead of xe since that is default for "regular" Oracle.

It is possible this has been customized depending on the installation and if so you will have to find that information from the person/group that installed Oracle if you do not already know it.

If still having problems trying to connect, look at this link to see if any of the information there is helpful: Oracle 11g listener fails with ORA-12514 and ORA-12505 errors


How to prevent a PostgreSQL trigger from being fired by another trigger?

(Obvious error in the trigger logic aside.)
In Postgres 9.2 or later, use the function pg_trigger_depth() that @Akash already mentioned in a condition on the trigger itself (instead of the body of the trigger function), so that the trigger function is not even executed when called from another trigger (including itself - so also preventing loops).
This typically performs better and is simpler and cleaner:
CREATE TRIGGER set_history
BEFORE UPDATE ON field_data
FOR EACH ROW
WHEN (pg_trigger_depth() < 1)
EXECUTE PROCEDURE gener_history();
The expression pg_trigger_depth() < 1 is evaluated before the trigger function is entered. So it evaluates to 0 in the first call. When called from another trigger, the value is higher and the trigger function is not executed.


Never mind it seems dbimport won't work in this case because the schema wasn't generated with dbexport.

It worked when I did

dbaccess newdb /tmp/schema.sql


Oracle 11g how to change nls_characterset only for one table or tablespace

The simple answer: no.
The character set is a characteristic of the entire database.


mysql data types: define as char but work as int

If there is no specific requirement for you to store the data in that manner then you SHOULD change the datatype of the column and make it to INT or Decimal as per your requirement.( This would be a suggestible approach as you will need to do a lot of calculations and there is no inbuilt function to calculate 'K' values)

However if there is not way out, you can use a replace function to replace the 'K' with a space or '000' and use INT/DECIMAL function to  convert the same for your  calculations.  Syntax would be something as below

SELECT * FROM TAX_TABLE WHERE CAST(REPLACE(TAX,'K','') AS DECIMAL) > 15

OR

SELECT * FROM TAX_TABLE WHERE CAST(REPLACE(TAX,'K','000') AS DECIMAL) > 15000


Trigger: move deleted rows to archive table

You just need to move the old data into the restrictions_deleted table before it gets deleted. This is done with the OLD data type. You can use a regulat INSERT statement and and use the OLD values as the values-to-be-inserted.

CREATE TRIGGER moveDeleted
BEFORE DELETE ON restrictions
FOR EACH ROW
EXECUTE PROCEDURE moveDeleted();

CREATE FUNCTION moveDeleted() RETURNS trigger AS $$
    BEGIN
       INSERT INTO restrictions_deleted VALUES(OLD.column1, OLD.column2,...);
       RETURN OLD;
    END;
$$ LANGUAGE plpgsql;


Same tables with same data, different SQL Server stored procedure performance

Are the two instances indentically configured?

One thing to check is if the instances have the same memory allocation.


Mysql How to update n rows using count function?

This query will update the oldest records that exceed the count of 500, so you keep 500 newest records for orgid = 1
update A
set `delete` = true
where orgid = 1 AND id IN (
SELECT id FROM  A where orgid = 1
order by createdate DESC
LIMIT 500,18446744073709551615);


Looking for application which will provide to determine project database growth and cost per day or month

You can use a third party tool Idera Diagnostic Manager to identify the growth and cost. There is a report inbuilt which forecast database and table growth depending upon how much historical data it has.
You can also set up this with the help of combination of TSQL + Powershell as described Database Space Capacity Planning by chad. It will help you in analyzing the database growth.
You can also see the Brent Ozar's answer on this as a solution as well

Estimate projected database growth


What are the current best practices concerning varchar sizing in SQL Server?

Regardless of specific datatype, you need to be able to store whatever the application requests to be stored. You cannot specify something smaller than the max size of what will actually be saved.

You also do not need, nor want, to specify a column length larger than the maximum actual size that will be stored for a variety of reasons: query memory allocation, potentially filling up the maximum row size and not leaving any room for adding columns in the future, etc.

True, variable length string and binary columns do not have the storage implication that fixed-length datatypes (string / binary / numeric / date / etc) do (although, some of those implications can be nullified via data compression or use of the SPARSE column definition option). However, as you pointed out, even if no there is direct storage implication, there is still the performance implication of overestimating required memory for queries.

Be sensible. Use only what you need. Considerations can be made if there is a high probability that column length will need to increase in the near future, but keep in mind that it is easier to expand the size of a column than reduce the size.  Yes, some work will be involved, but since that work is merely "potential", while performance implications of over-sizing are "actual", it is often best to define columns based on what you actually need, not what you maybe-kinda-sorta think you might need in the future. Many changes that are talked about never happen, and often the changes that are required cannot be foreseen. Go with what you know.

  Instead, a goal might be to limit the actual size of each data row to ~8000 bytes if possible.

I am not exactly sure what you are getting at here. SQL Server will physically limit you to just over 8000 bytes. Using LOB types — VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), XML, and the deprecated TEXT, NTEXT, and IMAGE types — allow for going beyond that initial page size limitation, but that is only due to placing a pointer (16 or more bytes, depending on the type, and depending on the size of the value being stored off-row when using the MAX types). The actual physical limit of the data page did not change.

Your goal should be to use the least amount of physical space to store what the app / business needs to store without breaking or truncating such that the incomplete value loses meaning or causes problems downstream. If you need to store a 12,000 character thingy, then use VARCHAR(MAX) because that is what is needed. If you are storing a phone number or postal / zip code, then it would be unwise to use VARCHAR(100), and irresponsible to use VARCHAR(MAX).

  some of our customer data fluctuates a little, so we generally make columns just a little wider than they need to be, say 15-20% bigger, for those columns. I was wondering if there were any other special considerations;

Don't all systems have at least some data that fluctuates? Any system that stores a person's name would qualify, right? There is a fairly large variance in length of names. And then you have someone like Prince go and change their name to a symbol and now you have an entirely different problem that is not length. This is just how things are.

But, to play devil's advocate for a moment: how can the "15-20% larger than what is needed" value not be the actual needed value? Let's say that there's a discussion about adding a new column, and someone suggests 50 characters, then someone else says, "well, 20% more is 60 so let's do 60 because someone might have 60." If it's true that a customer might have 60, then 60 is, and was always, the actual needed value, and 50 was wrong the whole time.

Of course, it would help if there was some indication as to the source of the data because:

if you make "URL" 1024 and someone needs 1060, then it needed to be 1060 (similarly, if you make URL VARCHAR and get complaints that it is messing up Unicode characters which are now allowed in domain names, then it needed to be NVARCHAR), but
if someone wants to add 1000 characters to a 500 character-limit comment field, then it still only needed to be 500. People can be less verbose in comments (a huge challenge for me ;-), but ProductSKU better be big enough to fit all of the customer's SKUs.

  I'm talking about initial table creation. A customer will tell us that they are going to start sending us a new table, and send sample data (or just the first production dataset), which we look at and make a table on our end to hold the data. We want to make the table on our end to handle future imports as well as what's in the sample. But, certain rows are bound to get longer, so we pad them. The question is how much, and are there technical guidelines?

You are making a lot of assumptions here. Sure some fields might get bigger. But then again, they might not. Or, some could get smaller. Some can change from being non-Unicode to being Unicode (once they realize that the world is getting smaller and one cannot assume that last names will only ever have basic ASCII / US English characters). Or, they could stop sending a field. Or they can add one or more fields in the future. Any combination of this and other things. So why focus only on VARCHAR columns? What if they are currently sending an INT value and in a year or two they reach the max value and starting sending a BIGINT? What if they have a "status" field with values 0 - 5. Are you just going to assume INT which is "padded" as it allows for growth, but should probably be TINYINT?

The only thing that you can safely predict is that trying to predict how your customers data will change will be wrong more often than it is correct. And being correct is a matter of luck / coincidence (if not luck, then just go play the lottery ;).

So the guideline is:

Don't waste time and energy on trying to answer an unanswerable question.
Instead, focus on getting as much information as possible regarding your customer's actual data, and go with that (i.e. data-driven decision making ;-).

You already have example data, great. But, please don't forget that you also have your customer's contact info: phone and/or email. Contact them! Ask them for their data specs (just like your system, the data currently in their system might have a max length of 35, but their system has it defined as VARCHAR(50), and their system will accept up to that length, in which case you should use 50). And, ask them if they have any near-term plans to change and of those datatypes (type and/or size).


Create Security Policy without schemabinding

The security policy documentation incorrectly states that the inline table value function must have been created using the SCHEMABINDING option. The actual behavior, however, is that the function needs to be schema bound only when the policy is schema bound (default). Since the function with cross-database references cannot be schema bound, the implication is the policy cannot be schema bound either.

Without schema binding, the onus is on you to ensure no breaking changes are made to the function or dependent objects and that the other database is always available. Also, users will need SELECT and EXECUTE permissions on the function as well as SELECT on objects accessed by the function (not necessary with SCHEMABINDING on the function). This may be a security concern depending on the sensitivity of the predicate data.

I think the general best practice is SCHEMABINDING for the above reasons but you'll need to weigh those benefits with the costs of managing replicated data.
Besides transactional replication, you could use an ETL process or Service Broker to replicate the security policy data. Not sure why the data is in a different database to begin with but another option would be to move the objects to the same database.


It takes two steps.  You must upgrade from 5.5 to 5.6 and run mysql_upgrade, then do 5.6 to 5.7.


Create a permanent table to house the computed results:

CREATE TABLE dbo.ViewResult
(
    test_id INT NOT NULL
        CONSTRAINT PK_ViewResult
        PRIMARY KEY CLUSTERED
    , tMin AS DECIMAL(18,5)
    , tMax AS DECIMAL(18,5)
    , tAvg AS DECIMAL(18,5)
    , tStDev AS DECIMAL(18,5)
);

Create a SQL Server Agent job that performs the following actions:

TRUNCATE TABLE dbo.ViewResult;

INSERT INTO dbo.ViewResult
SELECT
    test.test_id,
    MIN(samp.result) AS tMin,
    MAX(samp.result) AS tMax,
    AVG(samp.result) AS tAvg,
    STDEV(samp.result) AS tStdev
FROM dbo.test
    INNER JOIN dbo.samp ON test.test_id = samp.test_id
WHERE samp.status = 'OK'
GROUP BY test.test_id;

Schedule this Job to run as often as you need the "view" updated.

This will allow fast DML operations, while still providing reasonably fresh info about the data.


Use ANY/ALL to retrieve customer who placed the highest number of orders

Various ways to write. Your query, slightly rewritten to use JOIN and corrected to return all customers in case of ties:

select c.*, o.*
from customers as c
    join orders as o
      on o.cust_id = c.cust_id
where c.cust_id in
      ( select cust_id
        from orders
        group by cust_id
        having count(*) >=
               all ( select count(*)
                     from orders
                     group by cust_id
                   )
      ) ;

Similar, without using >= ALL:

select c.*, o.*
from customers as c
    join orders as o
      on o.cust_id = c.cust_id
where c.cust_id in
      ( select cust_id
        from orders
        group by cust_id
        having count(*) =
                  ( select top (1) count(*) as cnt
                     from orders
                     group by cust_id
                     order by cnt desc
                  )
      ) ;

The subquery simplified using TOP WITH TIES:

select c.*, o.*
from customers as c
    join orders as o
      on o.cust_id = c.cust_id
where c.cust_id in
      ( select top (1) with ties m.cust_id
        from orders as m
        group by m.cust_id
        order by count(*) desc
      ) ;

All of the above could be rewritten to use a derived table or CTE and JOIN as the subquery is uncorrelated.

Another way would be to use window functions in combination with TOP:

with
  d as
  ( select c.*, o.*,
           cnt = count(*) over (partition by o.cust_id)
    from customers as c
        join orders as o
          on o.cust_id = c.cust_id
  )
select top (1) with ties
    d.*
from d
order by d.cnt desc ;

or just window functions:

with
  d as
  ( select m.cust_id,
           cnt = count(*)
           max_cnt = max(count(*)) over ()
    from orders as m
    group by m.cust_id
  )
select c.*, o.*
from customers as c
    join orders as o
      on o.cust_id = c.cust_id
    join d
      on d.cust_id = c.cust_id
where d.cnt = d.max_cnt ;


Name for a "lookup" table with values in separate rows

Removing repeating groups (your comma delimited values) and moving this data into another table where each value is a row in that table is called normalization.  In particular, you're moving from 0 Normal Form (0NF) to a higher normal form, (1NF or higher, depending on what functional dependencies remain).

In your specific example, once you split out the favourite foods into a child table you might be in either 1NF or 3NF, depending on whether you also have a "master table" of food types.

The new child table where the favourite foods are recorded might be considered an intersection table, particularly if you also have a master table of all of the foods listed one record per type of food.  This is the typical way in which a many-to-many relationship is implemented in a relational database.

If you have this master table of food types, you would be in 3NF.  If you don't your child table might not be considered to be 3NF, just 1NF, because the list of foods is subject to update anomalies and possibly delete anomalies - depending on how your system views foods themselves, independent of their relationship to people.


Why we can backup postgresql database backup without password?

Read documentation on this: "If the server requires password authentication and a password is not available by other means such as a .pgpass file, the connection attempt will fail." This parameter doesn't mean that you can connect (and dump) without knowing password, it is just help for (backup) scripts where entering passwords isn't possible by hand.


Long Relational Algebraic expression provided than actually required

Trying to find a simpler solution is admirable, but your expression is subtly wrong.

The important part of the question is:

  ...or as a manager of the department...

Notice, that's not the same as "...or as a manager of the employee...".

Let's throw some names out there.

Ed White works for Brian Green. Ed is working on Project 123.
Project 123 is controlled by the Finance Department. Kathy Smith is the manager of the Finance Department.

So the requested result should include Project 123 in the output, because Project 123 is controlled by a department whose manager has a last name of Smith.

Your proposed solution will not include Project 123, because neither Ed nor his manager have a last name of Smith.


What does "Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION 64" in the SQL Server Log mean?

Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION 64

This message means you are getting out of memory error (OOM). SQL Server is looking to allocate 64 KB of memory, but is unable to do so.

  Microsoft SQL Server 2014 - 12.0.4213.0 (X64)

You are on an unsupported service pack level (SP1 + GDR). You should try patching up to the most recent level (SP2 CU13 at the time of writing). See SQL Server 2014 build versions. If it is a bug, it may well have been fixed.

It could also be due to general memory pressure. Check you have max server memory set correctly. For 128GB physical memory, you could probably set this to around 100GB as a starting point, depending on what else is running on the server.

Also check for other activities running at the time e.g. reports, index maintenance, log shipping, etc. Bear in mind that something could be scheduled outside of SQL Server Agent e.g. Agent on another instance, PowerShell, windows task scheduler and so on. It might even be a monitoring tool running some aggregated data collection. In any case, setting SQL Server's maximum server memory configuration parameter appropriately would address this.


Maintenance plan using external tool to execute query and stored procedures giving off no results

By default IndexOptimize is skipping indexes with less than 1000 pages. You can read more about this in the Frequently Asked Questions. Could this explain it?


Joining two subqueries with union all and join leads to suboptimal execution plan

I think that SQL Server simply does not have the appropriate optimization rules to yield the seek-into-Orders query that you are looking for in the case of a UNION ALL on both sides of the query. Such a query plan is theoretically possible, but the query optimizer is not capable of producing it for your query.

In order to reach this conclusion, I compared the full original query (including the LEFT JOIN) to an alternate formulation of the query that yields a much lower estimated cost (0.042 vs. 0.085). So SQL Server would be likely to choose this lower cost alternative if it were able to explore this plan shape for your query.

The original query plan

Here is the original plan, with an estimated cost of 0.085.

The alternate query plan

This query is semantically equivalent to the original query but has an estimated cost of 0.042, about half the cost. It is formed by promoting the second UNION ALL up to the top-level of the query. This requires us to reference t1 (the set of customers) twice, but even so yields a plan with half the cost by allowing seeks into the Orders and [Order Details] tables.

The alternate query

Here is the full query you can use to play around with this approach:

DECLARE @ids TABLE ( Id NCHAR(50) );
INSERT  INTO @ids
VALUES  ( N'AROUT' ),
        ( N'ALFKI' );

-- Define the original t1 as a CTE
WITH customers AS (
    SELECT    c.CustomerID
    FROM      dbo.Customers c
    WHERE     c.CustomerID IN ( SELECT    Id
                                FROM      @ids )
    UNION ALL
    SELECT    '0'
)
-- Join t1 to the top half of the original UNION ALL
SELECT  *
FROM Customers t1
JOIN ( SELECT    o.CustomerID
          FROM      dbo.Orders o
          LEFT JOIN dbo.[Order Details] od ON o.OrderID = od.OrderID
        ) t2 ON t2.CustomerID = t1.CustomerID
-- And then join it again to the bottom half of the original UNION ALL
UNION ALL
SELECT  *
FROM Customers t1
JOIN ( SELECT    '0' AS CustomerId ) t2 ON t2.CustomerID = t1.CustomerID
OPTION  ( RECOMPILE);

Takeaways / caveats

In general, I have found that Concatenation operators are good at getting in the way of the query optimizer and preventing it from yielding an optimal plan. A couple examples include this Connect issue where UNION ALL prevents optimized bitmap filters as well as repeated observations that Concatenation operators, particularly if nested, fool the SQL 2012 and earlier Cardinality Estimator and may yield sub-optimal plans due to poor cardinality estimates. Obviously UNION ALL is a very useful tool, but it's worth being aware that it does occasionally limit the query optimizer's ability to optimize your query.
Actually splitting the query into two separate units in this way might or might not make sense on your real use case, but it could be worth a try. (This particular query benefits from the fact that one of the two chunks can be simplified since it contains only the dummy SELECT '0'.)
The data sets involved on Northwind are so small that it's difficult to draw conclusions, so testing with your real world data set will be important.


MySQL installation and data location on Windows

I do not think that the location matters rather it is a matter of personal preference, and the MySQL installer will automatically update the my.cnf file for you.

However I would rather go with D:\mysql as the installation directory because its lowercase, has no special characters (spaces, capital letters) and is not in the Program Files directory which you may need to clean out for a new install or upgrade.

You may also need to add the D:\mysql\bin directory to the PATH environment variable so that you can run the MySQL commands from any command prompt


It's hard to guess what's going on on your system without code snipets to evaluate, but it seems you're not using the fully qualified table name [schema name].[table name]. If you don't want to use the fully qualified table name, you may create PUBLIC SYNONYMS. Ex from Oracle documentation:

CREATE PUBLIC SYNONYM offices
   FOR hr.offices;

Having said that, if you are granting DBA privileges to all users, you're not tightening up security at all! If you only want them to query some tables, grant them CONNECT and SELECT privileges. If you're felling really lazy, grant them SELECT ANY TABLE privilege, but don't grant them DBA! DBA privileges give users a lot more power than to just query tables. They'll be able to change system parameters, drop or alter any objects etc etc.


Sensible data management tool for importing and exporting of large data files

It sounds like you should check out MySQL's load data infile ....  The docs claim that it is a very fast way to load rows. (I personally have never loaded millions of rows at once).

Once the data is loaded, you may need/want to add appropriate indexes to your tables to help speed up query performance.

For getting data out of MySQL, check out select ... into outfile.


You are probably seeing an error like:

Msg 7357, Level 16, State 1, Line xx
Cannot process the object "<query text>".
The OLE DB provider "<provider>" for linked server "<server>" indicates that either
the object has no columns or the current user does not have permissions on that object.

This occurs when SQL Server tries to discover the shape of the data returned by the OPENQUERY statement. There are many fascinating details here, but the upshot is that OPENQUERY is often best used with only a single SELECT statement, as all the examples in Books Online do.

It is possible to do other things with OPENQUERY, including executing a remote stored procedure, but the procedure should only return a single result set, and there should be nothing before that SELECT that might break the way SQL Server checks the result set shape.

An alternative is to use EXECUTE ... AT, for example:

DECLARE @Script nvarchar(max) =
    N'
    <some amazing script>
    ';

EXECUTE (@Script) AT <linked_server_name>

See Books Online for the full syntax and options.

You will probably need to set the linked server's Server Options property Enable promotion of distributed transactions for RPC to false to capture results in a table using:

INSERT <table> (columns)
EXECUTE (@Script) AT <linked server>;

Note this is potentially much less efficient because the results of the EXECUTE are spooled to tempdb before being fed to the INSERT. This can be problematic if the results are large.


ORA-01427:single-row subquery returns more than one row

Without further information, I can only guess that lc_history2 is a view that somehow looks like

 select
   c,o,l,u,m,n,s
 from
   table where
     some_column = (select another_column
              from table
             where something = 'SOMEVALUE')

and the inner select returns more than one record for something = 'SOMEVALUE'


Detecting SQL Server Utilization with a query

If you really want to limit SQL Server memory, look at the Maximum server memory option.

Getting memory usage is possible, but it depends on what you really want.  Do you want to see memory usage as a percent of the "Maximum server memory" option?  If so, look at sys.dm_os_process_memory:

select convert(float, memory_utilization_percentage) / 100 as memory_usage
from sys.dm_os_process_memory

If you want physical memory use vs. total system memory, look in both sys.dm_os_process_memory and sys.dm_os_sys_info:

select * from sys.dm_os_sys_info

declare @physicalMemoryInUseKB bigint
declare @totalSystemMemoryBytes bigint

select @physicalMemoryInUseKB = physical_memory_in_use_kb from sys.dm_os_process_memory
select @totalSystemMemoryBytes = physical_memory_in_bytes from sys.dm_os_sys_info
select convert(float, @physicalMemoryInUseKB) * 1024
       / convert(float, @totalSystemMemoryBytes) as memory_usage

I don't think you can get the CPU usage unless you have the Resource Governor enabled.  If you do, look at sys.dm_os_performance_counters:

select
    case CPUUsageBase
        when 0 then 0
        else convert(float, CPUUsage) / convert(float, CPUUsageBase)
    end as 'cpu_usage'
from
(select cntr_value as [CPUUsage]
from sys.dm_os_performance_counters
where counter_name like 'CPU usage%'
and object_name like '%Workload Group Stats%'
and cntr_type = 537003264) Usage
cross join
(select cntr_value as [CPUUsageBase]
 from sys.dm_os_performance_counters
 where counter_name like 'CPU usage %'
 and object_name like '%Workload Group Stats%'
 and cntr_type = 1073939712) UsageBase


If the trigger function is written in PL/pgSQL, then you can raise errors with RAISE:

CREATE FUNCTION my_trigger() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF random() < 0.01 THEN
        RAISE EXCEPTION 'internal error';
    END IF;
    RETURN NEW;
END;
$$;