Any way of forcing MySQL to use smaller files?

Don't use a generic file-based backup and sync mechanism against a database.

Those files you mentioned will almost always change, perhaps even if you do nothing.  The two log files are always the same size, but they do not have to be that big (unless you have big LONGTEXT or LONGBLOB columns).  But changing there size is tricky, and version-dependent.

Consider "Replication" ("replicas", "slaves") as an alternative way to get redundancy, but there could be issues with writing to both copies.

Galera clustering (an add-on to MySQL; included with MariaDB) is a way to have multiples copies of a dataset continually maintained in sync, and all being writable.  Also, it can be self-healing.

MariaDB is a "client-server" app.  Your "client" can be anywhere in the world; you can have any number of clients active at the same time.  The "server" can be hosted on your machine or in the cloud.  It sounds like you want it to be hosted in the cloud and 'never' go down.  That is the normal way of doing things these days.  There is no need to clone the server's data (except as a backup).  Cloud services take care of backing up (via cloning or dumping or whatever) without you taking action.


String search is very slow with OR statement

Get rid of these silly subqueries and use UNION rather than OR:
select p.* from product p
   join product_stock c on p.id = c.product_id
where c.quantity > 0
  and p.product_number like '%T%'
union
select p.* from product p
   join product_offer o on p.id = o.product_id
where now() between o.offer_start and o.offer_end
  and p.product_number like '%T%';


When configured as a Cluster in Windows, you need to control the SQL Server Services from the Failover Manager, not SQL Server Configuration Manager. In Windows Failover Manager, you can fail the resources/services to a particular node and then carry on with your needed activity.

The database engine and SQL Agent are cluster aware by default. Other SQL Server services (SSRS, SSAS) can be added as cluster aware resources, but their default behavior is not cluster aware. As far as I know, SSIS is still the only service that cannot be made cluster aware (i.e. it has to be installed on all nodes and started/stopped with SSCM on each node).

We accidently stopped the SQL Agent from the Configuration Manager, and it showed Partially Online as you mentioned.  For the cluster to get happy again, we had to offline the Agent from Cluster Manager, and then bring it back online from the same place.  The point to be made: always control failover or instant restarts or offlines from the FCM.


How to query-and-increase a value (counter) in a thread-safe way? (avoid race conditions)

The update statements works perfectly fine without the select before! Since single statements are safe by definition, even two UPDATE queries performed at the same time only will result in the row incremented twice.

If you actually want to select the value for your PHP script, do something with it and later want to update this exact counter value, you can do the following:

BEGIN;
SELECT `counter` FROM `table` WHERE `id` = 123 FOR UPDATE;
UPDATE `table` SET `counter` = `counter`+1 WHERE `id` = 123;
COMMIT;

This starts a new transaction, then selects the rows you want to update and locks them exclusively. You can then safely update those without worrying about other clients changing their content or even accessing the locked rows. Finally you need to commit your changes.

You should also read something about isolation levels. You likely do not want a value like READ UNCOMMITTED as isolation level. Everything else should be fine for this use-case.


Mysql join two tables and limit by first input?

You can use something like this:

select * from
(select * from table1 limit 3) t1
join table2 t2 on t2.uid = t1.id

You can check it: http://sqlfiddle.com/#!2/1a534/7


How to drop and recreate catalog view in postgres

Manipulating the catalogs (the contents) is already something that is absolutely discouraged:

  Normally, one should not change the system catalogs by hand [...]

The reason for this is that you have to know the structure very well in order to know exactly what to change so that you don't corrupt your catalog.  For example, this is how an ALTER TYPE ... ADD VALUE ... used to work pre-9.1 (one of the simpler things - try to write a query for changing a column type):

INSERT INTO pg_enum(enumtypid, enumlabel)
    SELECT t_oid, 'new_value'
      FROM pg_type as t
     WHERE t.typtype = 'e'
       AND t.oid = 'your_schema.your_type'::regtype::oid;

Take a factor of 1000, multiply 'absolutely' with it, and then you get how much it is discouraged to change the structure.  Fortunately, it does not even seem possible (as user postgres, and you cannot have more privileges than that):

CREATE OR REPLACE VIEW pg_catalog.pg_stat_activity AS
SELECT [...], 'bla' AS bla
  FROM [...]
 WHERE [...];

ERROR:  permission denied: "pg_stat_activity" is a system catalog


MySQL/MariaDB stored procedure inserts even with syntax error

Nope, it's not a bug.  It's a situation that would otherwise have been an ambiguity, that got resolved in a different way in the absence of the declared variable.  MySQL Server does exactly the same thing.
Watch this:
mysql> select @@version_comment, @@version;
+------------------------------+-----------+
| @@version_comment            | @@version |
+------------------------------+-----------+
| MySQL Community Server (GPL) | 5.5.30    |
+------------------------------+-----------+
1 row in set (0.00 sec)

mysql> USE test;
Database changed

mysql> CREATE TABLE ambiguity (c1 DATETIME NOT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.22 sec)

mysql> INSERT INTO ambiguity (c1) VALUES (c1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM ambiguity;
+---------------------+
| c1                  |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql>

All you did was reference a column name from the row you were inserting, which, "at the time" you referenced it (even though there is theoretically no such concept as a flow of time or order of operations during an insert operation) the column "already contained" (had been initialized to) its default value.
It's pretty unusual that you'd do that intentionally, but it's a theoretically legitimate operation, at least in MySQL.
Best practice is to not use the same names for stored program ("local") variables as you're using for column names, because of exactly this situation, and others like it... because it's ambiguous.

“A local variable should not have the same name as a table column.   If an SQL statement, such as a SELECT ... INTO statement, contains a reference to a column and a declared local variable with the same name, MySQL currently interprets the reference as the name of a variable.”
— http://dev.mysql.com/doc/refman/5.6/en/local-variable-scope.html

...and, of course, as a column name, when possible, when there's no such variable.


Including clustered index key in non-clustered index

Yes, it is redundant.

On the other hand, it won't hurt anything (since the optimizer will ignore it and not add the data twice).

This is one of the many reasons not to take the suggested indexes at face value.


At first glance...

All you need is a GROUP BY clause with the MAX aggregate function:

SELECT id, MAX(rev)
FROM YourTable
GROUP BY id

but with inner join we can holding row wit max or sum value

SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev


Restoring MySQL Tables from .ibd, .frm and mysqllogbin files

I finally figured out and resolved my problem through a lot of trial and error. For those who do not have their original ibdata1 file, and only have their .frm and .ibd files, here's how I restored my data.

Download and install the MySQL utilities at -> http://dev.mysql.com/downloads/utilities.
Go into your command/terminal to open the MySQL utility, mysqlfrm, and use it to find the structure of your table that you need to restore. How I did that, was I cd'ed into the file location of mysqlfrm, then entered "mysqlfrm --server=user:pwd@localhost --port=3307 "path_to_.frm_file" > table_name.txt". The .txt file should be saved in the same folder as where your utilities are saved if you didn't specify where you wanted it to go.
In the text file, you'll see the CREATE TABLE statements, that include all the columns and info (basically, the original structure) of your table. Copy that CREATE statement with all that info.
In your MySQL Command, create a new database (CREATE DATABASE database_name). Make it the same name as your original database name.
Create a new table inside the new database--it doesn't have to be the same name as the folder. You can create the new table inside the command prompt, but I created my table in PhpMyAdmin, a free software tool that handles the administration of MySQL over the web. I simply clicked on the database on PhpMyAdmin, then the SQL table, and pasted the table structure from #3. (As a side note, I always received errors if I named my tables "table" in my command prompt, so try to avoid that name).
On your MySQL Command, go into your database, and enter "ALTER TABLE table_name DISCARD TABLESPACE", which will essentially remove this table's .ibd file.
Copy your original table (the table you want to restore)'s .ibd file into the newly-created table to replace the .ibd file that you just removed. Change your initial .ibd file to the newly created table's name. This will mimic the old .ibd file that you just deleted. You can find this folder in the MySQL data folder, under the newly-created database folder on your computer.
Go back to your MySQL Command, go into your database, and enter "ALTER TABLE table_Name IMPORT TABLESPACE." You'll receive a "warning" (1) error-type statement, but just ignore that.
And done! if you try to access your new table, it should contain all the data from your old table.

I hope this helped, and let me know if you have any questions or comments! Also, check out http://www.chriscalender.com/?tag=innodb-error-tablespace-id-in-file for other details.


PostgreSQL ODBC and Microsoft Access date/time in aggregate functions

I gave up on making the ODBC driver work in access to calculate this sum properly.

My solution was to create a view in PostgreSQL which calculated the sum of the hours.  See below:

    SELECT employees.empid,
    employees.startdate,
    employees.payrate,
    employees.surname,
    employees.first_name,
    emphours.weekending,
    emphours.department,
    emphours.paytype,
    sum(date_part('epoch'::text, (emphours.endtime - emphours.starttime) / 3600::double precision)) AS hours,
    sum(date_part('epoch'::text, (emphours.endtime - emphours.starttime) / 3600::double precision)) * employees.payrate AS totalpay
   FROM employees
     JOIN emphours ON employees.empid = emphours.empid
  GROUP BY employees.empid, employees.startdate, employees.payrate, employees.surname, employees.first_name, emphours.weekending, emphours.department, emphours.paytype;

Although I needed to change some access queries slightly, this seemed to be an effective workaround.

I also considered creating pass though queries but it seemed to be more work than creating the view.


DATETIME is like taking a picture of your local clock.

Check these:

SHOW VARIABLES LIKE '%zone%';

SELECT NOW();

If NOW() does not show Athens time, then something is set incorrectly.


If you have installed postgresql with brew there's one createdb that comes with it and that will connect to brew's postgresql.

Then if you have also installed Postgres.app there's another createdb at a different location that will connect to Postgres.app's postgresql.

Since OS X also ships with its own createdb in /usr/bin, that's a third createdb that expects to connect to Apple's postgresql, which is not running unless it's OS X Server Edition. According to the error message, this is the one that you're trying to use and which fails.

The solution is to use the createdb command that comes with the PostgreSQL you're actually running. Locate its directory and type the full path of createdb, as in /Applications/Postgres.app/Contents/MacOS/bin/createdb, and/or change your $PATH as suggested in postgresapp's doc.

This is a common problem for PostgreSQL on Mac OS X due the existence of many concurrent packages and of client-side programs pre-installed by Apple.


Updating an FTS indexed column returns "Invalid InnoDB FTS Doc ID"

The first thing I noticed is that the id column is not indexed. For all intents and purposes, your acting PRIMARY KEY is FTS_DOC_ID since it is the only unique key present without a PRIMARY KEY definition.

Since id is not indexed, I could see mysqld wanting to do a full table scan to fulfill the query. You should want to create a UNIQUE KEY on id as well if you intend on having two different unique keys and having two different references to one row.

Note that FTS_DOC_ID has the auto_increment attribute. There is no need to increment it manually, like you did with

UPDATE articles a1, (SELECT MAX(FTS_DOC_ID)+1 AS ftsid FROM articles) a2
set title = 'New MySQL Tutorial', FTS_DOC_ID=ftsid  WHERE id=9;

In fact, by doing this, you are actually taking the FTS_DOC_ID and changing it on id.

Looking back at your first query

UPDATE articles set title = 'New MySQL Tutorial'  WHERE id=9;

Why would this be a problem (and essentially a bug) ? Again, this goes back to id not being indexed.

When a column is not indexed, the Clustered Index of the table (where the PRIMARY KEY resides) is unaware of the presence of id. How is this so?

According to the Book

Chapter 12 Pages 260,261 (Subheading Secondary Indexes) says the following:

  What about other indexes? How are they structured, and what is their
  relationship with the data found in the clustered index?

  These additional indexes are know as "secondary indexes," and you can
  create as many of them as you need for an InnoDB table. Because our
  sample table schema definition requests an index on the last_name
  column, InnoDB creates one secondary index on this value.Each entry in
  the secondary index contains the primary key value from the clustered
  index,thereby facilitating linkage back to the data.

Your First Query

Since id is not indexed your first update failed. MySQL (eh Oracle) should have caught this problem. So, it is a bug.

Your Second Query

The update works because you accessed the Clustered Index indirectly by trying to manually update the FTS_DOC_ID column.

Your Third Query

It worked because you accessed no indexes at all, neither on id nor on body. In that instance, a full table scan would easily fulfill this request.

Epilogue

If I were you, I would report this as a bug ASAP because MySQL 5.6's FULLTEXT indexing for InnoDB is what people want these days and query plans should be accommodating it with WHERE clauses whether accessing with the Clustered index or not.


All cached logins are saved on %APPDATA%\Microsoft\SQL Server Management Studio\%Version%\SqlStudio.bin

Each time you run SSMS and save a login, it is added to SqlStudio.bin file. Now i assume your new SSMS has no saved logins, therefore simply replace SqlStudio.bin with the one from old SSMS

Steps:

Go to: %APPDATA%\Microsoft\SQL Server Management Studio\11.0\SqlStudio.bin , copy that file and then go to %APPDATA%\Microsoft\SQL Server Management Studio\14.0 and replace the existing one.


Getting all undeclared variables from query

Error list

The error list might give you what you need:

You could open View --> Error List in ssms

To get all the build + intelliSense errors:

Differences between error list & the messages tab on query execution:

Error list

  The Error List contains all syntax and semantic errors found by
  IntelliSense in the Database Engine Editor. This list of errors is
  dynamically updated as you edit Transact-SQL scripts. The list
  includes all errors that the editor has found in each Transact-SQL
  script. The editor does not stop parsing a file after encountering
  errors in a script. In SQL Server 2008, IntelliSense in the Database
  Engine Editor does not support all Transact-SQL syntax elements. The
  Error List contains only errors from the Transact-SQL syntax that is
  supported by IntelliSense.

Messages Tab

  The Messages tab at the bottom of the Database Engine Query Editor
  window displays all errors and messages that are returned by the SQL
  Server Database Engine when a Transact-SQL script is executed. This
  list does not change until you execute the script again. The Database
  Engine stops parsing a batch after it finds one or two compile errors;
  therefore, the Messages tab might not list all errors in a script.
  Source

So to answer the question:

  Is it possible to get SQL Server to report all the missing variables
  at once, or is this just how the query parser works?

Using general query excecution this is not possible as the parsing stops after one or two compile errors, but you could use the error list.


How do we find who used Dedicated Admin Connection

SQL Server doesn't maintain this information anywhere; if you're trying to catch someone abusing the DAC, either take away sysadmin from the people who clearly shouldn't have it, or at least set up some kind of polling mechanism to catch them. You can have a table like this:

CREATE TABLE dbo.DBA_DacAccess
(
  ConnectTime      datetime,
  FirstObservance  datetime2 NOT NULL DEFAULT SYSUTCDATETIME(),
  LoginName        sysname,
  HostName         sysname,
  AppName          sysname,
  Interface        nvarchar(32),
  ClientNetAddress nvarchar(48)
);

And then determine some reasonable interval to collect info about a DAC connection whenever one exists, and then run the following at that frequency (using a SQL Server Agent job, probably):

INSERT dbo.DBA_DacAccess
(
  ConnectTime,
  LoginName,
  HostName,
  AppName,
  Interface,
  ClientNetAddress
)
SELECT
  c.connect_time,
  s.login_name,
  s.[host_name],
  s.[program_name],
  s.client_interface_name,
  c.client_net_address
FROM sys.dm_exec_connections AS c
INNER JOIN sys.dm_exec_sessions AS s
ON c.session_id = s.session_id
WHERE c.endpoint_id = 1
AND NOT EXISTS
(
  SELECT 1 FROM dbo.DBA_DacAccess
  WHERE connect_time = c.connect_time
);

This will work going forward, but you have to be lucky (or be polling frequently) to catch them if they're in and out quickly, so you may want to fine tune that schedule. Also, in case you wonder why this isn't a LOGON TRIGGER, there are two reasons: (1) DAC bypasses those, by design and necessity, and (2) the rows/data in these DMVs wouldn't exist until they escaped the trigger anyway.

For events in the past, and again if you're lucky, folks who try to access the DAC from SSMS will get bitten by the background connection that tries to connect again, since that failed attempt will be written to the error log, and the IP address is appended to the end of the message (but none of the other information is included). This won't help if someone used remote desktop to the server, or an app other than SSMS that doesn't try to establish additional connections using the same ADMIN: credentials, but should be useful if they used SSMS remotely:

  04/23/2018 08:11:18
  Logon
  Unknown
  Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped,  either by logging off or ending the process. [CLIENT: 192.168.0.99]


How to export oracle 11g database partially

You can add a where clause to each table to filter rows exported:

http://www.dba-oracle.com/t_export_where_clause_selective.htm

However your referential integrity will not be maintained by this method, because the export is done per-object and filters are applied in the same way. You will need to either be very precise in how you define each where clause, which will make the export slow, or clean up the data afterwards by deleting any orphaned rows.

I would recommend creating a new schema in the same database and copying a chunk of the data into that, while maintaining the integrity during the copy. Then export the new schema.


Problem with Processing Dimensions with Multiple Facts

You need to process D1 and D5 in the same transaction. Then both will complete processing before measure groups are processed. Make sure you mark Transaction=true (the default unless you explicitly set it to false) and Parallel.

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Parallel>
    <Process>
      <Object>
        <DatabaseID>YourDB</DatabaseID>
        <DimensionID>D1</DimensionID>
      </Object>
      <Type>ProcessUpdate</Type>
    </Process>
    <Process>
      <Object>
        <DatabaseID>YourDB</DatabaseID>
        <DimensionID>D5</DimensionID>
      </Object>
      <Type>ProcessUpdate</Type>
    </Process>
    <Process>
      <Object>
        <DatabaseID>YourDB</DatabaseID>
        <CubeID>C1</CubeID>
      </Object>
      <Type>ProcessFull</Type>
    </Process>
  </Parallel>
</Batch>

I would highly recommend you digest this article.

Also are you processing all cubes as ProcessFull or only some cubes or some measure groups or some partitions. If you are processing all cubes and measure groups and partitions then don't bother with all this. Just do a ProcessFull on the database object. ProcessUpdate on dimensions is only for incremental processing which assumes you aren't processing all partitions.


Update data of one table from another with comparing columns have same initial strings

This should update for you as expected. Apologies for the number of character functions - I'm sure it could be improved via a REGEXP_SUBSTR call but I'm afraid I'm a bit busy at the moment.

MERGE INTO olddata old
USING (SELECT * FROM newdata) new
ON (  old.ename = new.ename
   OR substr(old.ename,1,length(old.ename)-length(substr(old.ename,instr(old.ename,'.'),length(old.ename)))) = new.ename)
WHEN MATCHED THEN UPDATE SET old.salary = new.salary, old.job = new.job

The first qualifier in the ON clause will match any that have the same format name and the second will take the string from OLDDATA.ENAME before the first dot to compare it to the NEWDATA.ENAME that doesn't contain the domain name.
Hope this works for you (it did for my quick tests on the data you supplied)

EDIT:
Here's my simple test:

  1* SELECT * FROM olddata
SQL> /

     EMPNO ENAME                          JOB                      SALARY HIREDATE
---------- ------------------------------ -------------------- ---------- ---------
      7369 smith.domain.com               clerk                      1300 17-DEC-80
      7645 jones.domain.com               Manager                    5000 01-JAN-80
      7549 allen.domain.com               sales                      1800 01-JAN-81

SQL> SELECT * FROM newdata
  2  /

ENAME                          JOB                      SALARY
------------------------------ -------------------- ----------
jones.domain.com               Manager                    6000
allen                          sales                      2000
smith                          clerk                      2000

SQL> MERGE INTO olddata old
  2  USING (SELECT * FROM newdata) new
  3     ON (  old.ename = new.ename
  4        OR substr(old.ename,1,length(old.ename)-length(substr(old.ename,instr(old.ename,'.'),length(old.ename)))) = new.ename)
  5  WHEN MATCHED THEN UPDATE SET old.salary = new.salary, old.job = new.job
SQL> /

3 rows merged.

SQL> SELECT * FROM olddata
  2  /

     EMPNO ENAME                          JOB                      SALARY HIREDATE
---------- ------------------------------ -------------------- ---------- ---------
      7369 smith.domain.com               clerk                      2000 17-DEC-80
      7645 jones.domain.com               Manager                    6000 01-JAN-80
      7549 allen.domain.com               sales                      2000 01-JAN-81

SQL>

EDIT:  Thanks to @Guarava for pointing out that the string operations can easily be removed. I was just overthinking it. The following MERGE is much more concise;

MERGE INTO olddata old
USING (SELECT * FROM newdata) new
ON (  old.ename = new.ename
   OR old.ename LIKE new.ename||'.%')
WHEN MATCHED THEN UPDATE SET old.salary = new.salary, old.job = new.job


Microsoft SQL Server creating an Inline function

Do I need to create a stored procedure first that way the inputs are
  recognized?

You are missing @ symbols at the beginning of each parameter.

Find out more about writing functions here

Once you know the basics this article by Jeremiah Peschka explains the benefits of inline functions and much more. Basically if your table valued function is not inline then it will execute once for every row returned by the calling query. This can be very inefficient. An inline table valued function can be referred to as a parameterised view.

Your function is a scalar function which will always execute once for each row but it could be converted to an inline table valued function with the code below.

CREATE FUNCTION dbo.GPAofStudents
  (@StudentID int,
   @ClassStartDateStart date,
   @ClassStartDateEnd date)

RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
    SELECT  @StudentID AS Student_ID,
            AVG(Class_GPA) AS GPA
    FROM    dbo.Students_Classes
    WHERE   Student_ID = @StudentID
            AND Start_Date > @ClassStartDateStart
            AND Start_Date <= @ClassStartDateEnd
            AND Class_GPA > 0;

Using the WITH SCHEMABINDING option will stop anyone from editing the schema used by your function and breaking it.


How to boost performance of virgin queries in MS SQL Server?

Your question can be basically rephrased as 'How does the query memory grant work?'. A good read on the subject is Understanding SQL server memory grant. Before a query is launched into execution it may require a memory grant for sorts and hashes and other memory hungry operations. This memory grant is an estimate. Based on current system state (number of requests running and pending, memory available etc) the system grants the query a memory grant up to the required amount. Once the memory is granted, the query starts execution (it may have to wait in the dreaded 'resource semaphore' queue before it gets the grant). At execution it memory grant is guaranteed by the system. This amount of memory can be shared with data pages (since they can always flush to disk) but never with other memory usage (ie. it cannot be subject 'steal'). So when the query starts asking for committed memory from its grant, the engine will deploy what you call 'strategy #1': data pages may be evicted (flushed if dirty) in order to give the query the memory it was promised. Now if the estimate was correct and the grant was 100% of the requested memory, the query should not 'spill'. But if the estimate was incorrect (boils down to cardinality estimates, therefore is subject to stale stats) or if the query did not got the entire grant it had asked for, the query will 'spill'. This is when tempdb comes into picture and performance usually tanks.

The only knob you have at your disposal that controls something in this process is the Resource Governor.  Since the RG can be used to specify a MIN setting for a pool, it can be used to reserve memory for a certain workload so that it actually gets the memory grant it requests. Of course, after you did the proper investigation that shows that reduced memory grants are the culprit, and of course after the impact on other workloads was evaluated. And tested, of course.

Now lets go back to your original question. If your investigation is correct (a very big if) I would like to point out two problems:

you run in production queries that require memory grants for a web site. This is a big no-no. Memory grants are indicative of analytical queries which have no place in serving HTTP requests.
your queries are probably not event getting the memory grant they request. Again, even more of a no-no for a latency critical workload as web sites are.

So what that tells me is that you have a fundamental design and architectural problem. Web sites are latency driven and should create an OLTP like workload, with no memory grants and with no memory pressure on queries. Not to mention no spills. Analytical queries should be run in offline jobs and store the pre-processed results for quick availability when HTTP requests desire them.


Turning a multiple row resultset into columns with multiple rows

You could try Pivot:  I think you'll need to order on some key that ties these records together (how do you know that the starttime of "TESR" is related to the EndTime of "TESR".  That probably needs to be in the ORDER BY clause below.

SELECT
    Title
    ,[1]
    ,[2]
    ,[3]
FROM
    (
        SELECT
            Title,
            Value,
            ROW_NUMBER() OVER (PARTITION BY Title ORDER BY Title) AS RN
        FROM
            YourResult
    ) RunInformation
    PIVOT (MIN(Value) FOR RN IN ([1],[2],[3])) AS PivotTable


Oracle data pump import parallel configuration

Two common reasons:

Each worker thread uses a seperate dumpfile and requires exclusive access to it. If you have fewer dumpfiles than the degree of parallelism you provided, the parallel threads will not be fully utilized. If you have a single dump file, only 1 worker thread can be active.
Tables that contain Basicfile LOB columns are not be exported or imported in parallel. This is a known restriction and documented in: DataPump Export/Import Of LOBs Are Not Executed in Parallel (Doc ID 1467662.1)

Workarounds include:

seperate partitions can be imported in parallel
start multiple data pump jobs and distribute data between them using the QUERYparameter.


Clearly yes - statistics are very important inputs for the optimizer!
This is what statistics are for.
Besides that configuation, data model (objects) etc. have an effect as well.
YOu can influence the optimization class to determine which optimizer techniques will be considered by the SQL Query Compiler and the DB2 cost based optimizer which is part of that.


I remember to have the same problems and it has something to do with how vBulletin is programmed. Please check regular "cronjobs" (not talking about system cronjobs you have already mentioned not to be the culprit) run by vBulletin which are triggered by visting users.

You can find them in version 4 of vBulletin in your admin panel, just below the settings for RSS-Feeds.

Another point to check is wether your vBulletin tables are in MyISAM format. vBulletin (at least until version 4) is optimized for MyISAM - if you happen to use InnoDB then some queries on vBulletin's tables (especially with the post table) will literally grind your system down to a halt.

Hope this helps.


Log backup vs differential backup if latter is broken

Under full recovery mode, will a differential backup "break" the previous log backups?

No. A differential backup will not break the log chain. Log backups rely on LSNs (Log Sequence Numbers).

A log chain is broken when :

You take adhoc log backup without using COPY_ONLY option.
When you change the recovery mode e.g. from full to simple.
When you use TRUNCATE_ONLY or NO_LOG (Both deprecated in SQL 2008) or backup to NUL (its like taking a backup and deleting it !)
When you revert the database from a database snapshot. For this scenario, you have to take a full backup - even differential wont work !

  If the DIFF Backup 1-file is corrupted somehow, could I then restore the database with just the FULL and LOG backups?

Yes, provided the log backups are not corrupted as well. Since differential got corrupted, there are chances that your subsequent backups might be corrupted.

You can even re-initialize a secondary log shipping database from a differential backup and then start applying log backups if your log backup chain is broken & provided a full backup has not ran (or you ran a full backup with COPY_ONLY option).

You have to be careful, that running a FULL backup without COPY_ONLY will reset the differential base as each differential backup will contain changes since the LAST full backup.

Read these answers as they are relevant :

Restricting users to COPY ONLY backups
Finding gaps in log backups


Use of log_bin_trust_function_creators in MySQL

This variable controls whether binary logging should trust the stored function creators for not to create stored functions that will cause unsafe events. Eg. having UUID functions.

This has been explained well in documentation:

  When you create a stored function, you must declare either that it is
  deterministic or that it does not modify data. Otherwise, it may be
  unsafe for data recovery or replication.

  By default, for a CREATE FUNCTION statement to be accepted, at least
  one of DETERMINISTIC, NO SQL, or READS SQL DATA must be specified
  explicitly. Otherwise an error occurs:

  ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
  or READS SQL DATA in its declaration and binary logging is enabled
  (you *might* want to use the less safe log_bin_trust_function_creators
  variable)

  To relax the preceding conditions on function creation (that you must
  have the SUPER privilege and that a function must be declared
  deterministic or to not modify data), set the global
  log_bin_trust_function_creators system variable to 1.

Reference: mysql-documentation (read this as it explains with example.)

About your second question, if it's dynamic and will it change if you restart server:

Yes it is a dynamic variable.
The variable will change upon restart, if you do not update the config to reflect the change.

Reference: mysql-documentation-again


How do you install MySQL 5.1.34 on CentOS 5.5?

While I would normally not recommend this
To download and install MySQL 5.1.34, run these commands at your Linux prompt AS IS...
MYSQL_VERSION=5.1
MYSQL_VERSION_RELNUM=34
MYSQL_VERSION_RELEASE=${MYSQL_VERSION}.${MYSQL_VERSION_RELNUM}
REDHAT_RELEASE=5
BINARY_VERSION=x86_64
PERL_LOCATION=ftp://ftp.de.netclusive.de/pub/parallels/Plesk/Expand/2.3.1/packages
PERL_FILE=perl-DBI-1.53-2.fc7.x86_64.rpm
PERL_URL=${PERL_LOCATION}/${PERL_FILE}
MYSQL_MIRROR_INDEX=0
MYSQL_SHARE2_LIB=MySQL-shared-compat-${MYSQL_VERSION_RELEASE}-${MYSQL_MIRROR_INDEX}.rhel${REDHAT_RELEASE}.${BINARY_VERSION}.rpm
MYSQL_SHARED_LIB=MySQL-shared-community-${MYSQL_VERSION_RELEASE}-${MYSQL_MIRROR_INDEX}.rhel${REDHAT_RELEASE}.${BINARY_VERSION}.rpm
MYSQL_DEVEL__LIB=MySQL-devel-community-${MYSQL_VERSION_RELEASE}-${MYSQL_MIRROR_INDEX}.rhel${REDHAT_RELEASE}.${BINARY_VERSION}.rpm
MYSQL_CLIENT_LIB=MySQL-client-community-${MYSQL_VERSION_RELEASE}-${MYSQL_MIRROR_INDEX}.rhel${REDHAT_RELEASE}.${BINARY_VERSION}.rpm
MYSQL_SERVER_LIB=MySQL-server-community-${MYSQL_VERSION_RELEASE}-${MYSQL_MIRROR_INDEX}.rhel${REDHAT_RELEASE}.${BINARY_VERSION}.rpm
MYSQL_SHARE2_URL="http://downloads.mysql.com/archives/mysql-${MYSQL_VERSION}/${MYSQL_SHARE2_LIB}"
MYSQL_SHARED_URL="http://downloads.mysql.com/archives/mysql-${MYSQL_VERSION}/${MYSQL_SHARED_LIB}"
MYSQL_DEVEL__URL="http://downloads.mysql.com/archives/mysql-${MYSQL_VERSION}/${MYSQL_DEVEL__LIB}"
MYSQL_CLIENT_URL="http://downloads.mysql.com/archives/mysql-${MYSQL_VERSION}/${MYSQL_CLIENT_LIB}"
MYSQL_SERVER_URL="http://downloads.mysql.com/archives/mysql-${MYSQL_VERSION}/${MYSQL_SERVER_LIB}"
cd
rm -rf MySQLInstall
mkdir  MySQLInstall
cd     MySQLInstall
wget ${PERL_URL}
wget ${MYSQL_SHARE2_URL}
wget ${MYSQL_SHARED_URL}
wget ${MYSQL_DEVEL__URL}
wget ${MYSQL_CLIENT_URL}
wget ${MYSQL_SERVER_URL}
pwd
ls -l
rpm -Uvh --force ${PERL_FILE}
rpm -Uvh --force ${MYSQL_SHARE2_LIB}
rpm -Uvh --force ${MYSQL_SHARED_LIB}
rpm -Uvh --force ${MYSQL_DEVEL__LIB}
rpm -Uvh --force ${MYSQL_CLIENT_LIB}
rpm -Uvh --force ${MYSQL_SERVER_LIB}

To go get the Latest Version of MySQL, run these AS IS...
HOWMANYBITS=64
WHATOS=5
BINARY_VERSION=000
if [ ${HOWMANYBITS} -eq 32 ] ; then BINARY_VERSION=i386   ; fi
if [ ${HOWMANYBITS} -eq 64 ] ; then BINARY_VERSION=x86_64 ; fi
MINOR_RELEASE=20
MYSQL_RELEASE=5.5.${MINOR_RELEASE}
OS_VERSION=000
if [ ${WHATOS} -eq 0 ] ; then OS_VERSION=linux2.6 ; fi
if [ ${WHATOS} -eq 5 ] ; then OS_VERSION=rhel5    ; fi
RELEASE_TAG=1
DEVEL__RPM=http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-devel-${MYSQL_RELEASE}-${RELEASE_TAG}.${OS_VERSION}.${BINARY_VERSION}.rpm/from/http://mysql.mirrors.pair.com/
SHARD2_RPM=http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-shared-compat-${MYSQL_RELEASE}-${RELEASE_TAG}.${OS_VERSION}.${BINARY_VERSION}.rpm/from/http://mysql.mirrors.pair.com/
SHARED_RPM=http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-shared-${MYSQL_RELEASE}-${RELEASE_TAG}.${OS_VERSION}.${BINARY_VERSION}.rpm/from/http://mysql.mirrors.pair.com/
CLIENT_RPM=http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-client-${MYSQL_RELEASE}-${RELEASE_TAG}.${OS_VERSION}.${BINARY_VERSION}.rpm/from/http://mysql.mirrors.pair.com/
SERVER_RPM=http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-server-${MYSQL_RELEASE}-${RELEASE_TAG}.${OS_VERSION}.${BINARY_VERSION}.rpm/from/http://mysql.mirrors.pair.com/
PERL_LOCATION=ftp://ftp.de.netclusive.de/pub/parallels/Plesk/Expand/2.3.1/packages
PERL_FILE=perl-DBI-1.53-2.fc7.x86_64.rpm
PERL_URL=${PERL_LOCATION}/${PERL_FILE}
cd
rm -rf MySQL5.5_Install
mkdir MySQL5.5_Install
cd MySQL5.5_Install
wget ${PERL_URL}
wget ${DEVEL__RPM}
wget ${SHARD2_RPM}
wget ${SHARED_RPM}
wget ${CLIENT_RPM}
wget ${SERVER_RPM}
ls -l
for RPM in `ls -lSr *.rpm | awk '{print $9}'` ; do rpm -Uvh --force ${RPM} ; done

CAVEAT
When running the download for MySQL 5.1.34, if the RPMs for the MySQL Binaries do not get retrieved, change this line
MYSQL_MIRROR_INDEX=0

to
MYSQL_MIRROR_INDEX=1

One value or the other will work.
UPDATE 2012-02-24 14:35 EDT
I just tried getting MySQL 5.1.34 (I skipped the install part) and I got this
[redwards@lw-lts-155 MySQLInstall]$ MYSQL_VERSION=5.1
[redwards@lw-lts-155 MySQLInstall]$ MYSQL_VERSION_RELNUM=34

[redwards@lw-lts-155 MySQLInstall]$ MYSQL_VERSION_RELEASE=${MYSQL_VERSION}.${MYSQL_VERSION_RELNUM}
[redwards@lw-lts-155 MySQLInstall]$ REDHAT_RELEASE=5
[redwards@lw-lts-155 MySQLInstall]$ BINARY_VERSION=x86_64
[redwards@lw-lts-155 MySQLInstall]$ PERL_LOCATION=ftp://ftp.de.netclusive.de/pub/parallels/Plesk/Expand/2.3.1/packages
MYSQL_SERVER_LIB=MySQL-server-community-${MYSQL_VERSION_RELEASE}-${MYSQL_MIRROR_INDEX}.rhel${REDHAT_RELEASE}.${BINARY_VERSION}.rpm
MYSQL_SHARE2_URL="http://downloads.mysql.com/archives/mysql-${MYSQL_VERSION}/${MYSQL_SHARE2_LIB}"
MYSQL_SHARED_URL="http://downloads.mysql.com/archives/mysql-${MYSQL_VERSION}/${MYSQL_SHARED_LIB}"
MYSQL_DEVEL__URL="http://downloads.mysql.com/archives/mysql-${MYSQL_VERSION}/${MYSQL_DEVEL__LIB}"
[redwards@lw-lts-155 MySQLInstall]$ PERL_FILE=perl-DBI-1.53-2.fc7.x86_64.rpm
[redwards@lw-lts-155 MySQLInstall]$ PERL_URL=${PERL_LOCATION}/${PERL_FILE}
[redwards@lw-lts-155 MySQLInstall]$ MYSQL_MIRROR_INDEX=0
[redwards@lw-lts-155 MySQLInstall]$ MYSQL_SHARE2_LIB=MySQL-shared-compat-${MYSQL_VERSION_RELEASE}-${MYSQL_MIRROR_INDEX}.rhel${REDHAT_RELEASE}.${BINARY_VERSION}.rpm
[redwards@lw-lts-155 MySQLInstall]$ MYSQL_SHARED_LIB=MySQL-shared-community-${MYSQL_VERSION_RELEASE}-${MYSQL_MIRROR_INDEX}.rhel${REDHAT_RELEASE}.${BINARY_VERSION}.rpm
[redwards@lw-lts-155 MySQLInstall]$ MYSQL_DEVEL__LIB=MySQL-devel-community-${MYSQL_VERSION_RELEASE}-${MYSQL_MIRROR_INDEX}.rhel${REDHAT_RELEASE}.${BINARY_VERSION}.rpm
[redwards@lw-lts-155 MySQLInstall]$ MYSQL_CLIENT_LIB=MySQL-client-community-${MYSQL_VERSION_RELEASE}-${MYSQL_MIRROR_INDEX}.rhel${REDHAT_RELEASE}.${BINARY_VERSION}.rpm
[redwards@lw-lts-155 MySQLInstall]$ MYSQL_SERVER_LIB=MySQL-server-community-${MYSQL_VERSION_RELEASE}-${MYSQL_MIRROR_INDEX}.rhel${REDHAT_RELEASE}.${BINARY_VERSION}.rpm
[redwards@lw-lts-155 MySQLInstall]$ MYSQL_SHARE2_URL="http://downloads.mysql.com/archives/mysql-${MYSQL_VERSION}/${MYSQL_SHARE2_LIB}"
[redwards@lw-lts-155 MySQLInstall]$ MYSQL_SHARED_URL="http://downloads.mysql.com/archives/mysql-${MYSQL_VERSION}/${MYSQL_SHARED_LIB}"
[redwards@lw-lts-155 MySQLInstall]$ MYSQL_DEVEL__URL="http://downloads.mysql.com/archives/mysql-${MYSQL_VERSION}/${MYSQL_DEVEL__LIB}"
[redwards@lw-lts-155 MySQLInstall]$ MYSQL_CLIENT_URL="http://downloads.mysql.com/archives/mysql-${MYSQL_VERSION}/${MYSQL_CLIENT_LIB}"
[redwards@lw-lts-155 MySQLInstall]$ MYSQL_SERVER_URL="http://downloads.mysql.com/archives/mysql-${MYSQL_VERSION}/${MYSQL_SERVER_LIB}"
[redwards@lw-lts-155 MySQLInstall]$ cd
[redwards@lw-lts-155 ~]$ rm -rf MySQLInstall
[redwards@lw-lts-155 ~]$ mkdir  MySQLInstall
[redwards@lw-lts-155 ~]$ cd     MySQLInstall
[redwards@lw-lts-155 MySQLInstall]$ wget ${PERL_URL}
--2012-02-24 14:26:08--  ftp://ftp.de.netclusive.de/pub/parallels/Plesk/Expand/2.3.1/packages/perl-DBI-1.53-2.fc7.x86_64.rpm
           => `perl-DBI-1.53-2.fc7.x86_64.rpm'
Resolving ftp.de.netclusive.de... 89.110.128.25
Connecting to ftp.de.netclusive.de|89.110.128.25|:21... connected.
Logging in as anonymous ... Logged in!
==> SYST ... done.    ==> PWD ... done.
==> TYPE I ... done.  ==> CWD /pub/parallels/Plesk/Expand/2.3.1/packages ... done.
==> SIZE perl-DBI-1.53-2.fc7.x86_64.rpm ... 625843
==> PASV ... done.    ==> RETR perl-DBI-1.53-2.fc7.x86_64.rpm ... done.
Length: 625843 (611K)

100%[=============================================================================================================================>] 625,843      767K/s   in 0.8s

2012-02-24 14:26:10 (767 KB/s) - `perl-DBI-1.53-2.fc7.x86_64.rpm' saved [625843]

[redwards@lw-lts-155 MySQLInstall]$ wget ${MYSQL_SHARE2_URL}
--2012-02-24 14:26:10--  http://downloads.mysql.com/archives/mysql-5.1/MySQL-shared-compat-5.1.34-0.rhel5.x86_64.rpm
Resolving downloads.mysql.com... 156.151.63.14
Connecting to downloads.mysql.com|156.151.63.14|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 5058961 (4.8M) [application/octet-stream]
Saving to: `MySQL-shared-compat-5.1.34-0.rhel5.x86_64.rpm'

100%[=============================================================================================================================>] 5,058,961    518K/s   in 9.7s

2012-02-24 14:26:21 (512 KB/s) - `MySQL-shared-compat-5.1.34-0.rhel5.x86_64.rpm' saved [5058961/5058961]

[redwards@lw-lts-155 MySQLInstall]$ wget ${MYSQL_SHARED_URL}
--2012-02-24 14:26:22--  http://downloads.mysql.com/archives/mysql-5.1/MySQL-shared-community-5.1.34-0.rhel5.x86_64.rpm
Resolving downloads.mysql.com... 156.151.63.14
Connecting to downloads.mysql.com|156.151.63.14|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 1827818 (1.7M) [application/octet-stream]
Saving to: `MySQL-shared-community-5.1.34-0.rhel5.x86_64.rpm'

100%[=============================================================================================================================>] 1,827,818    487K/s   in 3.7s

2012-02-24 14:26:26 (487 KB/s) - `MySQL-shared-community-5.1.34-0.rhel5.x86_64.rpm' saved [1827818/1827818]

[redwards@lw-lts-155 MySQLInstall]$ wget ${MYSQL_DEVEL__URL}
--2012-02-24 14:26:26--  http://downloads.mysql.com/archives/mysql-5.1/MySQL-devel-community-5.1.34-0.rhel5.x86_64.rpm
Resolving downloads.mysql.com... 156.151.63.14
Connecting to downloads.mysql.com|156.151.63.14|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 8693623 (8.3M) [application/octet-stream]
Saving to: `MySQL-devel-community-5.1.34-0.rhel5.x86_64.rpm'

100%[=============================================================================================================================>] 8,693,623    512K/s   in 17s

2012-02-24 14:26:46 (511 KB/s) - `MySQL-devel-community-5.1.34-0.rhel5.x86_64.rpm' saved [8693623/8693623]

[redwards@lw-lts-155 MySQLInstall]$ wget ${MYSQL_CLIENT_URL}
--2012-02-24 14:26:46--  http://downloads.mysql.com/archives/mysql-5.1/MySQL-client-community-5.1.34-0.rhel5.x86_64.rpm
Resolving downloads.mysql.com... 156.151.63.14
Connecting to downloads.mysql.com|156.151.63.14|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 7515446 (7.2M) [application/octet-stream]
Saving to: `MySQL-client-community-5.1.34-0.rhel5.x86_64.rpm'

100%[=============================================================================================================================>] 7,515,446    501K/s   in 15s

2012-02-24 14:27:03 (497 KB/s) - `MySQL-client-community-5.1.34-0.rhel5.x86_64.rpm' saved [7515446/7515446]

[redwards@lw-lts-155 MySQLInstall]$ wget ${MYSQL_SERVER_URL}
--2012-02-24 14:27:03--  http://downloads.mysql.com/archives/mysql-5.1/MySQL-server-community-5.1.34-0.rhel5.x86_64.rpm
Resolving downloads.mysql.com... 156.151.63.14
Connecting to downloads.mysql.com|156.151.63.14|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 20053095 (19M) [application/octet-stream]
Saving to: `MySQL-server-community-5.1.34-0.rhel5.x86_64.rpm'

100%[=============================================================================================================================>] 20,053,095   506K/s   in 42s

2012-02-24 14:27:46 (462 KB/s) - `MySQL-server-community-5.1.34-0.rhel5.x86_64.rpm' saved [20053095/20053095]

[redwards@lw-lts-155 MySQLInstall]$ pwd
/home/lw-staff/redwards/MySQLInstall
[redwards@lw-lts-155 MySQLInstall]$ ls -l
total 42832
-rw-r--r-- 1 redwards users  7515446 Apr  7  2009 MySQL-client-community-5.1.34-0.rhel5.x86_64.rpm
-rw-r--r-- 1 redwards users  8693623 Apr  7  2009 MySQL-devel-community-5.1.34-0.rhel5.x86_64.rpm
-rw-r--r-- 1 redwards users 20053095 Apr  7  2009 MySQL-server-community-5.1.34-0.rhel5.x86_64.rpm
-rw-r--r-- 1 redwards users  1827818 Apr  7  2009 MySQL-shared-community-5.1.34-0.rhel5.x86_64.rpm
-rw-r--r-- 1 redwards users  5058961 Apr  7  2009 MySQL-shared-compat-5.1.34-0.rhel5.x86_64.rpm
-rw-r--r-- 1 redwards users   625843 Feb 24 14:26 perl-DBI-1.53-2.fc7.x86_64.rpm

Keep in mind that the MySQL Binaries are three years old


Firstly: unless this is a replication slave, development box, or similar, that can easily be reconstructed from other sources should the worst happen, RAID0 is not recommended. It provides good performance but potentially seriously bad reliability: if any one of your four drives fails then the whole array is toast.

Edit after new information: it would appear from a quick search that people do recommend RAID0 on Azure, on the assumption that the underlying (virtual) disks are reliable due to the redundancy in the platform. I'm not convinced this is a good idea, though I've not used the platform so might not be the best person to comment there.

Secondly: without knowing anything at all about your data size, basic data structure (for the parts being tested in your benchmarks), read/write load patterns, other details of server's specification (RAM, CPU, ...), your mySQL configuration (out of the box or some tweaks done?) (if out of the box, which box?), how you are testing performance to show it is the same, and so on, we can't really help you on the performance front at all.

The only general thing we can say is if your performance is being limited mainly by CPU resource then RAID is not going to make any difference because that only affects IO performance.

Thirdly: you don't show if that RAID volume is mounted anywhere as a file system or part of an LVM arrangement. Have you done this and told mySQL to store data on that array?


ORA-00449: background process 'MMAN' unexpectedly terminated with error 7446

For some reason, it seems that the bdump and cdump directories for each instance had been deleted. I don't know who (or what) did that, but restoring them fixed the problem.


How to count matching values and print 0 for non-matching value in PostgreSQL?

You need an outer join for this:

select f.rank, count(s.s_id) as assigned
from first f
  left join second s on f.f_id = s.f_id
group by f.rank
order by f.rank;

It's important to count the rows from the outer joined table. A simple count(*) would return the wrong values. count() ignores null values and the rows for which no match exists in the second table will have a null value in s.s_id and thus these rows are counted with 0


Database design for changing number of fields

KEY-VALUE NO!!!

A table for phone numbers -- sure.  It would have userid, phone_num, and (if you like) a phone type, such as ENUM('fax', 'home', ...).  Then JOIN to the main table.

To keep unlimited, unsearchable data, have a column with a bunch of key-value stuff.  I like to do it in JSON, then compress it (in the app), and store it into a BLOB or MEDIUMBLOB.  That makes it easily accessible by the app, reasonable compact, and quite open-ended.

In the table, have only columns that you need to search on; put the rest into the extra JSON column.

More discussion:
http://forums.mysql.com/read.php?125,428546,428769#msg-428769
http://forums.mysql.com/read.php?125,402095,402218#msg-402218

Another approach is MariaDB's "dynamic columns".  This even lets you index randomly added 'columns'.

2000-3000 customers -- Yawn.


How to combine Oracle RAC with in-memory database to isloate OLAP from OLTP

RAC has shared storage, but workload can be segregated using different service names for each. Create an OLTP service name for that workload and only run the service on nodes to be used for OLTP processing. Same for analytic workloads.


Validating Excel Counts against Database Inserts

Not really.

Since Excel isn't a proper client server database, you can't ask it how many rows are there before bringing them in. You could bring them into a staging area and count rows as they come in. If they don't match and you need to handle that with a business process, the destination is untouched. If they meet your validation criteria, do a second transfer to the real destination. You can use a database table for this, or a raw file connection if you'd rather spill to the filesystem. Cache connection managers are useful if your dataset is reasonably sized.


Horizontal Scaling
Horizontal Scaling is essentially building out instead of up. You don't go and buy a bigger beefier server and move all of your load onto it, instead you buy 1+ additional servers and distribute your load across them.

Horizontal scaling is used when you have the ability to run multiple instances on servers simultaneously.  Typically it is much harder to go from 1 server to 2 servers then it is to go from 2 to 5, 10, 50, etc.

Once you've addressed the issues of running parallel instances, you can take great advantage of environments like Amazon EC2, Rackspace's Cloud Service, GoGrid, etc as you can bring instances up and down based on demand, reducing the need to pay for server power you aren't using just to cover those peak loads.

Relational Databases are one of the more difficult items to run full read/write in parallel.

I saw Damien Katz speaking about CouchDB at StackOverflow DevDays in Austin and one of his primary focuses for its creation was these parallel instances.  As this has been a focus of it since day one, it would be much more capable of taking advantage of horizontal scaling.

Vertical Scaling
Vertical Scaling is the opposite, build up instead of out. Go and buy the strongest piece of hardware you can afford and put your application, database, etc on it.

Real World
Of course, both of these have their advantages and disadvantages. Often times a combination of these two are used for a final solution.

You may have your primary database where everyone writes to and reads real time data on a large piece of hardware. Then have distributed read only copies of the database for heavier data analysis and reporting where being up to the minute doesn't matter as much.  Then the front end web application may be running on multiple web servers behind a load balancer.


Pgpool II : unable to read message length between two network interfaces

About errors above, because of the connection between pgpool and slave server, if I change slave's pg_hba.conf for pgpool host from md5 to trust, it work fine.

Two ways to fix:

ssh config among: pgpool, slave, master .
set up trust method in pg_hba.conf for pgpool .


Indexing the difference between two columns in MySQL

You could try MariaDB which supports virtual columns.

  Indexes are partially supported. Virtual columns do not support
  primary keys and indexes can only be based on PERSISTENT virtual
  columns. PERSISTENT columns can be part of a foreign key and can be
  referenced by foreign keys, but ON UPDATE CASCADE, ON UPDATE SET NULL,
  ON DELETE SET NULL are not allowed.

You could simulate this through your INSERTs or via TRIGGERs (better IMHO) and index the simulated virtual column. Then test to see does that resolve your issue (or at least improve it).

[EDIT in response to the OP's comment]

Yes, indeed, I thought that MariaDB would be a "bridge too far". I really included it for completeness. MariaDB is a drop-in replacement up and including 5.5 (in MariaDB terms). AFAICS, the 10 series is still compatible (it's like MySQL with bits added, but the divergence will only widen in the future AIUI.

The advantage of using a persisten column is that the persistent column can be indexed whereas the virtual one can't according to the docco that I quoted.

Has the idea of "simulated" virtual columns worked for you?


Kerberos authentication not working with Linked Servers in SQL Server 2012

Dealing with Active Directory is always so much fun.  The single most important thing here is to realize you are dealing with distributed data that can take time to propagate across your network.

The SQL Servers in question had their name changed as part of an upgrade procedure; we replaced an existing machine (SQL01) running SQL Server 2005 with a new machine (SQL03) running SQL Server 2012.  SQL03 was the name of the new machine when I initially set it up in the domain.  SQL01 had an existing SPN associated with a single domain account we used for several SQL Servers running 2005.  Since it is a best practice to only run a single machine under any given domain account, I created a new account and configured SQL03 to run with that account name.  After taking the original SQL01 out of service, and renaming SQL03 to SQL01, there was an SPN conflict.

I used the SetSPN.exe utility to delete the conflicting SPN (on the old domain account) - and it still didn't work.  At this point, I did nothing further and moved on to other items.  When I came back around 30 minutes later, KERBEROS authentication was working.  I simply needed to wait for the SPN change to propagate among our domain controllers.

I used SetSPN -L DOMAIN\Account and compared that output with SetSPN -Q MSSQLSvc/Machine.domain.inet:1433 to find the duplicate SPNs, and then used SetSPN -D MSSQLSvc/Machine.domain.inet:1433 DOMAIN\Account to remove the old SPNs.


You'll need to describe the other 5 tables. How is the data related? You should only ever have to filter for owner once if the data is already naturally related. For example, if I want all the questions that are mine, and their related answers, I should be able to do something like this (please ignore syntax, I have no idea what platform you're using):

SELECT Q.QuestionID, Q.Text, ..., A.AnswerID, a.Text, ...
  FROM Questions AS Q
  INNER JOIN Answers AS A
  ON Q.QuestionID = A.QuestionID
  WHERE Q.Owner = 'Foxandxss';

The point: You should only need to check in one place for the owner. If you're storing the owner in the Answers table too, and for any given QuestionID that value is the same, this is wasteful and counter-productive.

(You probably wouldn't actually repeat the question data every time, since there may be 500 answers, and you would ideally have the username mapped to a userID of some kind so that you're not repeating that wide data throughout your indexes and data. But hopefully you get the drift.)


Moved from comment to answer

I think 'belong' is a wrong word here. It usually says that transaction ends with commit or rollback. Group of DML statements belong to transaction; commit/rollback is a way to finish transaction...emphasized text


If you PARTITION the table (on anything), and have no more than, say, 30 partitions, then you could REBUILD each partition in about 3% of the total time, possibly a lot faster due to the size.  (Possibly following it with a ANALYZE PARTITION would be beneficial.)

Do not use 'optimize partition', that erroneously optimizes the entire table.

So, if you get enough reclaimed space to be worth it, set up a cron or EVENT to reorg one partition each night forever.

If you provide SHOW CREATE TABLE and some important queries, I may be able to advise on how to gain some other benefit from the partitioning.


If your vad_vaf_id does not sufficiently designate the target value, perhaps you should add a new column in your temporary table to provide that information? Something like vad_target?

+-----------------+   +-------------------+
| validation_data |   | validation_fields |
+-----------------+   +-------------------+
| vad_id          |   | vaf_id            |
| vad_met_id      |   | vaf_field         |
| vad_vaf_id      |   +-------------------+
| vad_target      |
| vad_value       |
| vad_is_treated  |
+-----------------+

When user changes his firstname, leave the field NULL. When there are multiple possible targets (i.e. with inverters), use inverter's id.


You won't get a true picture of memory usage from Task Manager if the account the service is running under has the lock pages in memory privilege (edit: as per Mark Rasmussen's comment/link). To determine how much memory is being used you can look at:

SQLServer:Memory Manager\Total Server Memory perfmon counter
DMVs

I can't recall if there is a DMV or combination of that will give you the total memory allocation but the following will show the bulk of it.

SELECT TOP(10) [type] AS [Memory Clerk Type], SUM(single_pages_kb) AS [SPA Mem, Kb]
FROM sys.dm_os_memory_clerks
GROUP BY [type]
ORDER BY SUM(single_pages_kb) DESC OPTION (RECOMPILE);

SELECT DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4 -- system databases
AND database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);

The second is the most interesting usually, buffer pool allocations by database. This is where the lions share will be used and it can be useful to understand which of your databases are the biggest consumers.


This can be quite a tall order. My original thinking was that you could simply add a new version of each OFF column, copy the data over, drop the old column, and rename the new column. However there are a boatload of complications that will make this tedious or downright impossible:

constraints (including foreign keys) which will need to be disabled and/or dropped/re-created
computed columns
indexed views and other peripheral objects pointing at the affected table with SCHEMABINDING
the blocking this type of operation will cause on large tables
the mere complexity of generating an ALTER TABLE ADD script for each column, given that in 2008 R2 it is very cumbersome to derive the right data type from the metadata (it's easier in 2012, but still no picnic)

Honestly, I think the easiest thing to do would be to generate a script of the entire database:

right-click the database > tasks > generate scripts
if you get the intro screen, check the checkbox and click next
choose all objects and click next
on the "set scripting options" tab, click "Advanced"

Change "Types of data to script" to "schema and data"

on the same tab, change the output to "New query window"

In the resulting script, before running it, change all the references to the original database name to a new database name (including the file paths), and change all the ANSI_PADDING settings in the script to ON (using find/replace). Once the new database is created, you can rename the old database and rename the new one to the old name. Ultimately you can drop the old database once you've confirmed that everything is working correctly.

Warning: do not try this in SQL Server 2014 CTP2 - In the process of answering this question, I lost some of my own work, due to this bug I just filed.


Inline expansion of inline composite expression

The (2, 3) has type record, i.e. Postgres doesn't know which (or even how many) columns it contains. You can use the .* syntax only with known ("registered") types.

You could define your own tuple type and use that instead of a record:

CREATE TYPE tuple AS (a int, b int);
SELECT 1, (CASE WHEN TRUE THEN '(2, 3)'::tuple ELSE '(4, 5)' END).*;
SELECT 1, (CASE WHEN TRUE THEN (2, 3)::tuple ELSE (4, 5)::tuple END).*;

If you absolutely need to have an expression that returns records, you can cast those to tuples afterwards only by going through a text representation:

SELECT 1, ((CASE WHEN TRUE THEN (2, 3) ELSE (4, 5) END)::text::TUPLE).*;

(online demo)


In MongoDB, we don't have to change schema, right?

MongoDB does not require you to predeclare schema, but there is still some onus for an administrator to remove unneeded indexes or data constraints. The Mongoose Node.js framework you are using includes a declarative client-side schema, but the MongoDB server is unaware of any changes to field or index usage.

By default Mongoose creates new indexes on startup (depending on the config.autoIndex option of your schema), but does not have any tracking to remove or alter index definitions once created. Since you had previously created a unique index on username, the index will need to be manually dropped if you no longer require that constraint.

The duplicate key error message indicates the index name and offending key value:

  E11000 duplicate key error index: news.users.$username_1  dup key: { : null }

Any documents missing the field in a unique index will have a value of null indexed. You cannot have two documents in the same collection missing a field in a unique index unless that index is configured as a  sparse or partial index.

You can manually drop the problematic index using the mongo shell or an admin tool. For example:
db.getSiblingDB('news').users.dropIndex({username:1})

  Additionally, I backed up the previous database of production, and copied to development, and restored it. Oddly, unlike production, creating two new users did not give error in development.

The difference in outcome will depend on whether the target database had the unique index previously created.


sp_dboption is not available in SQL Server 2012

sp_dboption was deprecated a while ago - http://msdn.microsoft.com/en-us/library/ms187310(v=sql.105).aspx

The recommended alternative is ALTER DATABASE - http://msdn.microsoft.com/en-us/library/ms174269.aspx


Change Name of Availability Group Replica

I want to change the name of an Availability Group Replica because [paraphrased] the name we want it to be is actively being used by a production reporting server [sic]

Personally the name of the nodes shouldn't really matter. Yes, I get naming conventions and whatnot, but at some point (just like which node in a cluster owns a specific role) there is diminishing returns. This is one of those times where you're going to put way more effort into changing it than I personally believe you'll get out of it.

  Not seeing a lot of documentation on this.

I'll add it to my documentation list, and because of this I'll go ahead and answer it here, hoping others may find it.

The Overall Logical Steps

Please note that this does not cover if the node is acting as a forwarder in a distributed availability group.

Remove the node from the AG if it's part of it.
Remove the node from any Read Only Routing (ROR) configurations.
Disable Always On Availability Groups from the SQL Server Configuration Manager/Powershell if it's enabled.
Remove any clustered applications from the Windows Server Failover Cluster Node (WSFC). This includes FCI's acting as part of an AG. You don't have to remove local installs of SQL Server, but you will have to follow the renaming items by dropping and adding the server name.
Evict the node from the WSFC.
Rename the computer and double check the AD computer object is successfully renamed and replicated to other DCs before continuing.
Join the node to the WSFC under the new name.
Install any clustered services or applications removed in step 4. Also enable Availability Groups from the SQL Server Configuration Manager/Powershell.
Add the node to the AG.
Update any ROR configurations.


Attempt to decompose up to second normal form (2NF) in a scenario about students and courses

There is no non-prime attribute in any of the relations that is functionally dependent on a proper subset of any candidate key. That is obvious from the functional dependencies - only the third table has to be checked, and your third functional dependency covers that.

Consequently your result is in 2NF.


Is there a limit on the number of blobs storable in SQL Server with filestream?

60.000 files and 50 GB should be absolutely no problem. You might want to read "Special Consideration for Large Environments".

A challenge could be to come up with a recovery plan.


SQL Server Stored Procs not parameterized, leading to high compilations?

It shouldn't really make a difference if the parameters are specified or if parameter location is assumed.  The compiling happens to the statements within the stored procedure.

That said, it's best practice to pass in the values properly parametrized so that if a parameter is added in the middle of the list nothing will break.


When you want to remove access but not DROP the database

Example 1: migrate one database to a new installation

Prepare new server
Backup/restore to new server
Update application connection srings
Take DB off-line on old server
Test thoroughly
Drop DB on old server

Without taking the DB off line, you limit rollback options

Example 2: Cleaning up a messy SQL Server (e.g. developers created DBs)

Take offline, see who calls...


Table structure/schema for spreadsheet-like web app (ex: Google Docs)?

I've never attempted something like this, but maybe a schema such as this would work:

Spreadsheet
    spreadsheet_id (unique key)
    name
    num_rows
    num_cols

column_types
    type_id (unique key)
    type_name

spreadsheet_rows
    spreadsheet_row_id (unique key)
    spreadsheet_id (refers to spreadsheet.spreadsheet_id)
    row_seq_num (for on-screen row-ordering)
    row_name

spreadsheet_cols
    spreadsheet_col_id (unique key)
    spreadsheet_id (refers to spreadsheet.spreadsheet_id)
    col_seq_num (for on-screen column-ordering)
    column_type_id (refers to column_types.type_id)
    column_name

spreadsheet_cells
    cell_id (unique key)
    spreadsheet_id (refers to spreadsheet.spreadsheet_id)
    row_id (refers to spreadsheet_rows.spreadsheet_row_id)
    col_id (refers to spreadsheet_cols.spreadsheet_col_id)
    cell_value (holds the actual value!)

Of course  you'd only create records in spreadsheet_cells for non-null values...


Should not shrink database to release space in production?

To elaborate on sp_BlitzErik's comment:

Generally, you should not be shrinking data or log files on a regular basis.

Shrinking the data file:

Impacts performance in the long run, as it can cause fragmentation (as it may have to move pages to create a contiguous block of free space at the end of the file);
Impacts performance while the shrink is happening;
is logged in the transaction log (and thus, can cause that to get bigger); and
will impact performance later, when more space is required and the file has to grow again.
also: risks failure if other files have used up the available disk space, and the file doesn't have room to grow again.

Shrinking the transaction log can be even more tempting, as it may normally show most of its space as unused. Most of the points above for the data file also apply to the log file (log file shrink won't be logged and cause the log file to grow), but it has some unique concerns:

The log file may require 30GB to support a job that runs every night at 2AM, but only require 500MB during the business day. So, shrinking it may be undone every day, making it pointless to do so.
The log file can suffer fragmentation (on SQL Server at least - search on "virtual log files").

Basically, the only time when shrinking either file makes sense is after unusual activity.

For the data file, if a large amount of data has been removed, and will not be repopulated over time, then it can make sense to shrink the file. If you have a database where data is remove and archived elsewhere once a year - it's being repopulated, and I wouldn't shrink the file. If you discovered a problem with that database, where one very large table was left out of the archive, and you cleaned it up and deleted 8 years of data - then, it might make sense to shrink the database.

Sometimes, you may decide to shrink a database after that annual archive, because there's hardly any free disk space on the server. This is a stopgap measure; I would make sure I had put getting more disk space in motion before I shrank the database, because you know you'll need it.

For log files, the circumstances when it makes sense to shrink them are even more limited. If your database is configured for point-in-time recovery (in SQL Server, the FULL recovery model), but you have not been taking transaction log backups, the transaction log could grow very large. Once you've got the transaction log process up and working, it probably makes sense to shrink the log file. Similarly, if you execute a single transaction that moves a lot of data, that could cause the log file to grow (it has to hold all the data to roll back the current transaction, regardless of the recovery model). If this is a process that occurs rarely (for instance, a twice-a-year load of millions of prospects into a sales database), it may make sense to shrink the log file afterwards. However, even here, allowing other files to grow into the space the transaction log required for that load may mean the load fails the next time you run it, because there's no longer enough free disk to grow the transaction log to the size it needs to be.

To summarize, shrinking data or log files does nothing to help your database run better, can make it run worse, and increases the likelihood that you'll run out of disk space down the road. If you're doing it to free space on your disk for other things, outside of the few specific cases where there's been known abnormal growth, you're only putting off the inevitable, and you better be working on getting more disk space somehow.


How do I update last row of a group in MySQL

Yes, there is a workaround, using JOIN to a derived table:

UPDATE tableX AS t
  JOIN
    ( SELECT user, MAX(date) AS date
      FROM tableX
      GROUP BY user
    ) AS c
    ON  t.user = c.user
    AND t.date < c.date
SET
    t.current = 0 ;

If (user, date) is not enough to guarantee uniqueness and there is some other id column that is either unique or primary key (or the (user, date, id) is unique, that's enough), you can do this to keep only one row per user with current=1:

UPDATE
    ( SELECT DISTINCT user_id
      FROM tableX
    ) AS d
  JOIN
    tableX AS t
    ON  t.user_id = d.user_id
    AND (t.date, t.id) <>
        ( SELECT i.date, i.id
          FROM tableX AS i
          WHERE i.user_id = d.user_id
          ORDER BY i.date DESC, i.id DESC
            LIMIT 1
        )
SET
    t.current = 0 ;


Do I need to execute "create extension pgcrypto" everytime?

pgcrypto is a regular Postgres extension. You have to install it once per database (not per DB cluster like you may have been assuming) to use it. Installation is permanent, surviving DB restarts.

If you want every new database in the same DB cluster to have it pre-installed, you can install it to the default template database template1 by connecting to it and installing it normally. Or install it in any other database and use that as template with:

CREATE DATABASE new_db TEMPLATE my_template;

See:

Creating UUID extension as the non super privileged user

One other thing that can go wrong: the search_path. See:

Is it recommended to install extensions into pg_catalog schema?
How does the search_path influence identifier resolution and the “current schema”

But if that was your problem you'd get warnings when trying to install the same extension a second time.

The only other exotic explanation I can think of: your DB might have been restored to a previous state. But you would certainly know of that, wouldn't you?

Or did you just connect to a different database by mistake? The "read replica" you mentioned?

  how to make this cryptographic functions available in such read-only environment?

If it's not replicated by your replication solution, you have to install it manually once.


You can right click on the report that you want to copy and choose the Download option.  Download it to a file share and then upload it on the new site.


NOT EXISTS with two subquery fields that match 2 fields in main query

You are not using EXISTS correctly. In an EXISTS, the selected column makes no difference, it is entirely ignored and does not even need a name. This is why I favour the syntax EXISTS (SELECT 1 all on one line, because effectively it is just extra syntax of the EXISTS not of the subquery.
In your example, you also need to correlate the subquery to the outer.
EDIT I think I know what you want now.
If you follow the logic, you need to exclude rows in your outer where there are matching rows in the inner with the same ticket and date.
Notes:

The LEFT JOIN doesn't make sense here as you are filtering on it's columns, it may as well be an INNER JOIN.
The best format for dates is yyyy-mm-dd as that will not change depending on language and region.
You should use half open intervals for datetime with a time component or you will get wrong results, so dateCol >= start and dateCol < dayAfterEnd. I haven't changed this as you may have a date-only column.
Use more sensible names for the alias
We can extract out the join logic into a CTE
Don't use column names that need quoting

;WITH cte AS (
    SELECT
       ticketNumber.keyvaluechar AS TicketNum,
       ticketDate.keyvaluedate AS TicketDate,
       itemdata.itemtypenum,
       itemdata.itemnum
    FROM
       itemdata
    INNER JOIN
       hsi.keyitem110 ticketNumber on ( itemdata.itemnum = ticketNumber.itemnum )
    INNER JOIN
       hsi.keyitem101 ticketDate on ( itemdata.itemnum = ki101.itemnum )
)
SELECT DISTINCT
   TicketNum, TicketDate
FROM
   cte
WHERE
   ticketDate BETWEEN '01-01-2021' AND '01-31-2021'
AND
  (
    cte.itemtypenum  = 178
  ) -- this is Doc Type A
AND NOT EXISTS (SELECT 1
    FROM
       cte cte2
    WHERE cte2.itemnum = cte.itemnum --Same Ticket #
        AND cte2.TicketDate = cte.TicketDate  -- Same dates
        AND cte2.itemtypenum = 183  -- this  is DOC Type B
)

Note also that according to this, your LEFT JOINs should probably be INNER JOIN

As I see that someone has mentioned NOT IN, I will just say: NOT IN is usually a very bad idea, because on nullable columns it gives incorrect data.
See here, for example, for an explanation why.


Check out this post, do you meet the minimum requirements? Is remoting enabled?
https://dbatools.io/install/

Otherwise run it with the verbose flag and let us know what you get:

Test-DbaDiskAlignment -ComputerName Test_Server -Verbose

Also what version of both the dbatools module and powershell are you using?

Powershell version:

$PSVersionTable

dbatools version:

Get-Module dbatools


I was able to do this using multiple CTEs, similar to the following:

;with cte as
(
  SELECT DISTINCT d.AppId,
    d.AppName,
    d.AppType,
    Tags = STUFF((SELECT ', ' + t.TagName
                  FROM AppTags t
                  where d.AppID = t.AppID
                  FOR XML PATH (''))
                  , 1, 1, '')
  FROM AppDetails d
  WHERE d.AppID = '1'
),
results as (
    SELECT ROW_NUMBER() OVER (ORDER BY AppID) AS RowNumber, * FROM cte
        where tags like '%test1%'
)
select *
from results
WHERE (RowNumber >  @pageStart) AND (RowNumber <=  @pageStart + @pageLength)


How to MySQLDump with For Channel to set up multi-source replication?

I raised this with Oracle BUG 96072, and they have responded with

  "I have analysed it and I consider it a welcome feature request."

So I am taking it from that, that this is an oversight, and there is currently no option to use mysqldump to build a multi-source database.

So for the moment it seems the options are:

Export MySQLdump to a file, then manually edit the file, before importing it again (needs twice the space and twice the time)
Import the first database, remove the replication settings (saving for later), import the second database. Update the replication settings for the second one, then start replication on the first one. Also far from an ideal situation.


SSIS : Null value for date keeps failing

Unfortunately, SSIS variables can't hold NULL values. NULL is allowed in data flow's columns but not on variables, this is a limitation of the underlying COM objects used by SSIS.

You will have to work-around this by supplying a default or sentinel value on your query, and later on check on this value to correctly assign a NULL. You can change your query to supply a non-null value of the correct type, for example:

SELECT
    T.Column1,
    T.Column2,
    DateColumn = ISNULL(T.DateColumn, '1900-01-01')
FROM
    YourTable AS T

And then whenever you are referencing this DateColumn inside your ForEach, make an expression to check against this value and replace with NULL.

YEAR( @[User::DateColumn]  ) == 1900 ?  NULL(DT_DATE) : @[User::DateColumn]

If you are issuing an UPDATE via OLE Command statement with the ForEach's variables then your current update should be something like:

UPDATE T SET
    col1 = ?,
    col2 = ?
FROM
    yourTableToUpdate T
WHERE
    primaryKeyID = ?

Or maybe an SP call:

EXEC dbo.UpdateSomeTable
    @ID = ?,
    @Col1 = ?,
    @Col2 = ?

You can use plain SQL to replace the value back to the NULL. I'd also recommend assigning the values to variables first then using the variables in any place you want inside the SQL. This way it's easier to see the proper order of the parameter assignation and you can also repeat them without trouble:

DECLARE @col1 DATETIME = ?
DECLARE @col2 VARCHAR(100) = ?
DECLARE @primaryKeyID INT = ?

SET @col1 = CASE WHEN YEAR(@col1) <> 1900 THEN @col1 END

UPDATE T SET
    col1 = @col1,
    col2 = @col2
FROM
    yourTableToUpdate T
WHERE
    primaryKeyID = @primaryKeyID

Or:

DECLARE @col1 DATETIME = ?
DECLARE @col2 VARCHAR(100) = ?
DECLARE @primaryKeyID INT = ?

SET @col1 = CASE WHEN YEAR(@col1) <> 1900 THEN @col1 END

EXEC dbo.UpdateSomeTable
    @ID = @primaryKeyID,
    @Col1 = @col1,
    @Col2 = @col2


How many DBMS's can be installed on a single server?

Separate.

SQL Server, Oracle, DB2 et al typically do not play nicely alongside other "server" software. They tend to monopolise system resources, particularly memory, so while you could install them side-by-side, you won't be getting the best from them. It would also be challenging to diagnose performance issues with two different DBMS's competing for resources.

Given a collection of ageing or little used applications that required a mix of database systems, virtualisation would be a tempting solution to consolidate.


How to find the same salaries and department names?

This should work:

select e1.*
  from employees e1 inner join (
    select count(employee_id) as CNT, department_id, salary
    from employees group by departmant_id, salary) t1
  on e1.department_id=t1.department_id and e1.salary=t1.salary
  where t1.CNT>1

The problem with your own query is twofold:

the return value of (select...from ..e2..) is from the very nature of your problem not unique, so the "=" relationship should either throw an error or return whichever is the first result (I'm not familiar enough with MySQL to know which applies), which doesn't have to be the one you're looking for.
filtering via "e1.employee_id =" will only ever be true for the record itself, but NOT for another record that matches your criteria

If you insist on using a filter:

SELECT * FROM employees e1
WHERE (e1.department_id,e1.salary) IN
 (SELECT e2.department_id,e2.salary
  FROM employees e2
  WHERE e2.employee_id <> e1.employee_id)


Another - ERROR: column "da2.dependency_device_name" must appear in the GROUP BY clause or be used in an aggregate function

It needs to be in the GROUP by of the "xy1" subselect, not of the outer query.

But it probably really needs to be taken out of the select list of "xy1" and not put in the GROUP BY.  Having it in both the string_agg and the GROUP BY would be legal, but seems rather pointless.

Where the error message says "or be used in an aggregate function", it means "or be used only in an aggregate function"

There many ways to form a legal query, but the question is, which of them does what you want?

It seems like you want your subquery to be this:

Select
  da2.dependent_device_name,
  da2.dependent_device_fk,
  sum(1) "Dependency Count",
  string_agg(da2.dependency_device_name,', ') "Dependency List"
from view_deviceaffinity_v2 da2
  where effective_to is null
  group by da2.dependent_device_name, da2.dependent_device_fk

And then you would not want the "effective_to" in your join condition, having filtered them out already.


I have read (and re-read your question) - Your question is "Is there any way to Rebuild (and Reorganize) the indexes automatically?  I mean is it possible to schedule them to be built daily, weekly, ... ?"

The answer is yes.  You have several options.

You can choose to set up a Sql Maintenance plan (but I would not recommend this as you have very little control over what gets rebuilt/reorgd).  The maintenance plan allows you to specify the scheduling criteria (daily, monthly, whatever) and generate a Sql Server job.

A better solution would be to use a customized script like the one that @Ali Razeghi mentioned (Ola's).  This script (and others available) let you configure options regarding when to rebuild vs reorg and only do index maintenance on the tables 'that need it'.  You can create a custom Sql Server agent job to run the custom script whenever you need to.  If you don't want to use the Sql Agent, you can also use the Windows Task Scheduler to invoke a SQLCMD bat file process to run the index maintenance scripts.

Regardless of whether you choose the maint plan route or custom sql agent job, make sure you alter the 'notification' properties of the job to notify interested parties in the event of a failure.  You'll need to create one or more Sql Operators for the notification to send error emails to.  It's also wise to go into the job step properties (advanced) and check the box to include the step output in history or even log the output to a table.  Including the step output in history may not show you everything because there is a limitation as to how much information can be stored.  Saving the output to table has no limitation (that I'm aware of)

Index maintenance is an intense IO and CPU process and should be scheduled during non-peak-usage times


Is this the right and efficient way to deal with MySQL query?

If you have two questions -- one needing times, one not needing them, then you should pick between two different queries.  That will be a lot simpler and cleaner.

For performance, have

INDEX(s_name, to_time)
INDEX(b_name, s_name, from_time)

(These two indexes should be pretty good; other indexes may work equally well.)


First of all you should find out why your log cannot be truncated:

select log_reuse_wait_desc
from sys.databases
where name = 'SUSDB';

It's certainly NOT log backup that it's waiting for, because recovery model of your db is simple.

You should also check free space on the disk where the log file is living, and if someone limited the growth of your log file:

use susdb;
select physical_name,
       size * 8 / 1024 as size_in_Kb,
       max_size * 8 / 1024 as max_size_in_Kb,
       growth,
       is_percent_growth
from sys.database_files
where file_id = 2;

exec xp_fixeddrives;

For now, please update your question with the results of 3 queries above.


You need a sufficiently large table to multiply the source table. In your example it is sufficient to do a self join:

select material, 1
from (
    select x.material, x.count
         , row_number() over (partition by x.material) rn
    from t as x
    cross join t as y
) as u
where rn <= count
order by material;

Gold               1
Gold               1
Rock               1
Rock               1
Rock               1
Silver             1
Wood               1
Wood               1

PostgreSQL also have a nifty table function generate_series that can be used:

select material, 1 from (
    select t.material, t.count
         , row_number() over (partition by material) as rn
    from t
    cross join generate_series(1,10)
) as u
where rn <= count
order by material;

instead of 10 you can determine:

select max(count) from t

and use that to avoid creating an unnecessary large table.

While educating myself I realized that generate_series can take a variable as argument:

select material, 1 from (
    select t.material, t.count
         , row_number() over (partition by material) as rn
    from t
    cross join ( SELECT  generate_series(1, cnt)
                 FROM ( SELECT MAX(COUNT) FROM t ) u (cnt)
               ) as v
) as u
where rn <= count
order by material;

Using LATERAL means we can correlate cnt with count in the outmost table, and thereby remove the need for row_number():

select material, 1
from (
    select t.material, t.count
    from t
    cross join LATERAL ( SELECT  generate_series(1, cnt)
                         FROM (
                             SELECT COUNT
                             FROM t as t1
                             WHERE t.material = t1.material
                         ) u (cnt)
                       ) as v
) as u
order by material;

If you made it this far, go read @ypercubeᵀᴹ answer. It does'nt get much simpler or better than that. Please vote for that one.


If you have the Deadlock Graph - you have quite a lot of information. Reading the deadlock graph takes a bit more skill. But not too much. The graph is showing you what a deadlock is. It's sort of like when two cars are at a point where they each need to cross a road to enter a parking lot across the street from them at the exact same spot. Say they each want to make a left turn. Car A can't take a left because Car B is in the way. Car B can't take a left because Car A is in the way.

SQL Server just blows one of the cars up for you instead of moving one of the cars a bit or driving around the other.

But I digress. The deadlock graph information and the rest of the info in the deadlock report will show you which object each session have locked and what type of lock - and also what lock the other wants.

You may see some query info, may not depending on a few things. But if you can recreate the situation you can capture some of the query info from an extended event session or a third party tool that watches queries coming in - tools like Sentry One, Idera DM or Solar Winds DPA should all show you queries as they happen - and most have a free trial period. Sentry even has their own deadlock graph, I trust the others do also but not sure.


SELECT i.c.w. a COUNT and INNER JOIN gives different results with string literals and numeric values when they are used in a WHERE clause

The commenters were very helpful. They pushed me into thinking in the right direction. Let me explain.

If you would have a table like this:

gln     status
108     closed
108     open
108     closed

And, you would execute a query like this:

SELECT COUNT(gln) FROM tableA

The result would be 3.

If you would execute a query like below:

SELECT COUNT(gln), status FROM tableA

Then you would only see 1 status, others status cells would disappear.
This disappearance could mean trouble.

A correct query should look like this:

SELECT COUNT(gln), status FROM tableA GROUP BY status

ONLY_FULL_GROUP_BY

MySQL can work in different modes. One of these mode is called: ONLY_FULL_GROUP_BY. This mode is turned off on some Database Servers.
This mode forces Database Administrators to use the GROUP BY in an aggregated query ( query with a COUNT() ).

Link: https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_only_full_group_by

@danblack was thinking in the right direction.
The query should look like this:

SELECT
    c.informationProvider as cGLN,
    p.informationProvider as pGLN,
    COUNT(p.productID) AS count,
    p.productID,
    p.productStatus
FROM tblproducts AS p
INNER JOIN tblcompanies AS c ON (c.informationProvider = p.informationProvider)
WHERE       p.productStatus IN ('requested' , 'expected', 'open')
AND c.informationProvider = '8718885110005'
GROUP BY p.productID, p.productStatus, p.informationProvider


How to reclaim space taken by an index that partially built and was terminated by a power outage

Normally we'd expect that when postgres was restarted, the crash recovery process would have removed files related to a rollback'ed index from the data directory.

Let's assume that it didn't work, or at least that it has to be checked manually.

The list of files that should be in the datadir can be established with a query like this:

select pg_relation_filenode(oid)
   from pg_class
  where relkind in ('i','r','t','S','m')
    and reltablespace=0
  order by 1;

reltablespace=0 is for the default tablespace. If the problematic index was created in a non-default tablespace, this 0 must be replaced by its OID in pg_tablespace.

i,r,t,S,m in relkind correspond respectively to indexes, tables, toast space, sequences, materialized views. All these objects have their data in files whose names match pg_relation_filenode(oid).

On disk, the data files are below $PGDATA/base/oid/ where oid is the oid of the database obtained by select oid,datname from pg_database.
If we're not talking about the default tablespace, base is replaced by PG_version_somelabel instead.

List and sort the files matching relfilenodes in that directory:

ls | grep -E '^[0-9]+$' | sort -n > /tmp/list-of-relations.txt

(that actually keeps only the first segment for relations that are larger than 1Gb. If there are lingering segments not attached to anything they should be considered separately)

and diff that file with the result of the query above.

If there are lingering data files that do not correspond to any object that the db knows about, they should appear in that diff.


Database which can be setup without admin user

The reason you have Superusers is to clean up the mess made by regular Users.

Trying to take these powers away from Superusers is like asking them to service your car and promptly throwing away all their spanners and wrenches.

You say that you don't want data deleted.

Simple solution.
Don't give people the permission to do delete data.

Argument: "But Table owners can do anything to that table"

True.  So don't give Users table-owner-level accounts.

Argument: "Superusers can do anything"

True.  So don't give Users superuser-level accounts.

Only you should have access to this, to fix other people's messes.

Review your security model.  Determine who needs what level of access to what, then apply it.  You may well get some "resistance" to start with, but stick to your convictions.  You're heading in the right direction.


select based on sum of rows compared to another field value

This is an UNPIVOT problem.To get the sum of credit items and the sum of debit items you can use a SUM of a CASE statement. To show that, we need a table first:

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE invoice_items
    (`invoice_item_id` int, `invoice_set_id` varchar(3), `credit_debit` varchar(6), `amount` int)
;

INSERT INTO invoice_items
    (`invoice_item_id`, `invoice_set_id`, `credit_debit`, `amount`)
VALUES
    (62, 'a22', 'debit', 15.00),
    (63, 'a22', 'debit', 8.00),
    (64, 'a22', 'credit', 23.00),
    (65, 'b23', 'debit', 44.00),
    (66, 'c55', 'debit', 15.00),
    (67, 'c55', 'debit', 2.00),
    (67, 'c55', 'credit', 8.00)
;

Now you can combine SUM and CASE like this:
Query 1:

SELECT invoice_set_id,
       SUM(CASE WHEN credit_debit = 'debit' THEN amount END) AS debit_amount,
       SUM(CASE WHEN credit_debit = 'credit' THEN amount END) AS credit_amount
  FROM invoice_items
 GROUP BY invoice_set_id;

Results:

| INVOICE_SET_ID | DEBIT_AMOUNT | CREDIT_AMOUNT |
|----------------|--------------|---------------|
|            a22 |           23 |            23 |
|            b23 |           44 |        (null) |
|            c55 |           17 |             8 |

From here it is fairly simple to filter out the unwanted rows. We just need to wrap the two sums in COALESCE statements to turn NULL into 0:

Query 2:

SELECT invoice_set_id
  FROM(
    SELECT invoice_set_id,
           SUM(CASE WHEN credit_debit = 'debit' THEN amount END) AS debit_amount,
           SUM(CASE WHEN credit_debit = 'credit' THEN amount END) AS credit_amount
      FROM invoice_items
     GROUP BY invoice_set_id
  )X
WHERE COALESCE(debit_amount,0) > COALESCE(credit_amount,0);

Results:

| INVOICE_SET_ID |
|----------------|
|            b23 |
|            c55 |

If your actual problem is as simple as your example and you do not need the intermediate values for anything else, you can get the result also by just changing the sign of the amount based on the credit_debit value before calculating the sum. That would look like this:

Query 3:

SELECT invoice_set_id
  FROM invoice_items
 GROUP BY invoice_set_id
HAVING SUM(CASE WHEN credit_debit = 'debit' THEN amount ELSE -amount END)>0;

Results:

| INVOICE_SET_ID |
|----------------|
|            b23 |
|            c55 |


Try "List Backup", or perhaps "list backup summary"

http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmreprt.htm#BRADV89586

and

http://docs.oracle.com/cd/E11882_01/backup.112/e10643/rcmsynta027.htm#RCMRF132


How to determine if an Index is required or necessary

I use Jason Strate's index analysis scripts (Old location). They tell you how much your existing indexes are used as well as how much missing indexes would have been used. I typically don't add indexes unless they make up more than 5 or 10% of the queries on a table.

Most importantly, though, it's about making sure the application responds fast enough for the users.

Update: Jason Strate's index analysis blog articles for newer scripts (New location)

Double Update: These days, I use sp_BlitzIndex® when performing index analysis.


Why did adding a secondary index not increase select performance?

I'll skip my comments about that CREATE TABLE statement that would be classified from wrong to insulting, like having all columns declared NULL (what would a row with (NULL, NULL, NULL, NULL) mean?) and concentrate on the question asked.

The difference between 8.6 and 8.9 seconds is negligible. For all we know, the running times of the 2 tests are almost identical. If you want to test properly, you should run longer tests and with larger size of the table (try with 15K, 150K, 1500K, 15M rows) and see whether the efficiency changes or stays the same between having or not the specific index.

You should also examine the execution plans created for the queries in the 2 cases (at various table sizes), to see what is happening behind the scenes, what indexes are used, if any, etc.

My prediction would be that as the table grows larger, the index will get more useful, as the queries will require an index seek to find the (IDs of the) rows in the wanted time range and then some additional seeks from the clustering PK index (I assume you use InnoDB.)

Without the index, a whole table scan will have to be done each time. The larger the table gets, the biggest the difference you'll see between the 2 test cases (with and without the index).

If the vast majority of your queries is similar to the following, getting rows from a small time range:

SELECT <columns_wanted>
FROM mssg
WHERE created_at >= @start_timestamp
  AND created_at < @end_timestamp ;

then I would suggest (assuming you use InnoDB) an alternative design: make the (created_at) the clustering key of the table. Since you may have 2 or more rows with exact same timestamp, you can set the primary key to (create_at, id) for this effect (as InnoDB allows only unique keys for clustering). You will also need an additional index on (id) for the AUTO_INCREMENT to be allowed. This can be declared UNIQUE or not, it's your choice:

CREATE TABLE mssg (
  mssg_id INTEGER NOT NULL AUTO_INCREMENT,
  body MEDIUMTEXT NULL,
  length VARCHAR(20) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (created_at, mssg_id),
  -- UNIQUE
    INDEX mssg_id_ix (mssg_id)             -- this is needed for the AutoIncrement
);

This way, all the rows that your queries need will be stored in consecutive pages of the clustering (PK) index and the efficiency of the queries - as long as they ask for a small time range - will not be affected by the size of the table.

Further comments, partially related to the question;

Why are all columns declared as NULL? I suggest you change them all to NOT NULL except for those columns that there is a specific reason to allow nulls.
Why mediumtext for the message? Are you going to store so large messages? It might be better for performance, if you could reduce the size, to say VARCHAR(250).
Why is the length declared as VARCHAR and not INTEGER?
Further, if it's going to be storing the length of the text, it is not needed at all. You can get the length at any time using the proper text function.
You could declare the TIMESTAMP column to get a default CURRENT_TIMESTAMP if you want (if it is not already provided by the application.)