ERROR 1064 (42000): You have an error in your SQL syntax - Cannot understand what the error means

I'm not an expert in Mariadb but I think you cannot do it. From the documentation on generated columns

Generated columns can be referenced in the INSERT, UPDATE, and DELETE statements.
However, VIRTUAL or PERSISTENT generated columns cannot be explicitly set to any other values than NULL or DEFAULT. If a generated column is explicitly set to any other value, then the outcome depends on whether strict mode is enabled in sql_mode. If it is not enabled, then a warning will be raised and the default generated value will be used instead. If it is enabled, then an error will be raised instead.


The service account for Agent is irrelevant. All Agent does it to tell SQL Server to produce a backup file. It is SQL Server that produces the backup file, hence it is the service account for the SQL server service that matters. You can't do anything about that.
So, either run SQL Server using a domain account; or use the virtual service account and assign privileges on the share to the domain computer account.


How are SQLServer Timestamp/RowVersion Values Created?

Referencing the documentation for rowversion (Transact-SQL) - (highlighting mine)

  Each database has a counter that is incremented for each insert or
  update operation that is performed on a table that contains a
  rowversion column within the database. This counter is the database
  rowversion. This tracks a relative time within a database, not an
  actual time that can be associated with a clock. A table can have only
  one rowversion column. Every time that a row with a rowversion column
  is modified or inserted, the incremented database rowversion value is
  inserted in the rowversion column.

  You can use the rowversion column of a row to easily determine whether
  the row has had an update statement ran against it since the last time
  it was read. If an update statement is ran against the row, the
  rowversion value is updated. If no update statements are ran against
  the row, the rowversion value is the same as when it was previously
  read.

As far as being unique - it 'should' be unique across all tables in a database.  Here is the 'should' part - there is a caveat in the documentation:

  Duplicate rowversion values can be generated by using the SELECT INTO
  statement in which a rowversion column is in the SELECT list. We do
  not recommend using rowversion in this manner.

You can check the current database rowversion by using @@DBTS.  This example shows rowversion in action.

set nocount on
drop table if exists mytest
go
CREATE TABLE MyTest (myKey int PRIMARY KEY
    ,myValue int, RV rowversion);
GO
INSERT INTO MyTest (myKey, myValue) VALUES (1, 0);
GO
select @@DBTS
go
INSERT INTO MyTest (myKey, myValue) VALUES (2, 0);
GO
select @@DBTS
go
update MyTest set myValue = myValue
go
select @@DBTS
go

------------------
0x0000000000009481

------------------
0x0000000000009482

------------------
0x0000000000009484


Find ID columns with null value in oracle database

You can do something like this to dynamically generate a query against the tables with a nullable ID column to see whether they actually contain nulls or not:

declare
  l_cnt pls_integer;
begin
  for tabs in (SELECT c.owner, c.table_name FROM dba_segments s, dba_tab_cols c
               where  s.segment_name = c.table_name
               and    c.column_name = 'ID'
               and    c.nullable = 'Y'
               and    s.tablespace_name = 'T_ECOS') loop
    execute immediate 'select count(*) from ' || tabs.owner || '.' || tabs.table_name ||
      ' where id is null and rownum = 1' -- to short-circuit the count as soon as a null entry is found
      into l_cnt;

    if l_cnt > 0 then
      dbms_output.put_line(tabs.table_name);
    end if;
  end loop;
end;
/


Which SQL Developer Version will be compatible with Oracle 9i

Since SQLDeveloper is a Java program using a jdbc driver, the restrictions are made by the version of the jdbc driver. The v10 and v11 drivers can connect to v9 without any problems. For older version databases you might need to get the v8 or v9 jdbc drivers.


No, unless you

logged access in your tables
had a trace (SQL Profiler) running
have a logon trigger
...

You have to explicitly monitor this somehow.

If you need to do it now, you could make the database single user and see who calls...


Your problem is you were deleting from a heap, i.e. your table has no clustered index, so the space was not released, you could solve this in follow ways as described here: DELETE (Transact-SQL)

  When rows are deleted from a heap the Database Engine may use row or
  page locking for the operation. As a result, the pages made empty by
  the delete operation remain allocated to the heap. When empty pages
  are not deallocated, the associated space cannot be reused by other
  objects in the database. To delete rows in a heap and deallocate
  pages, use one of the following methods.

Specify the TABLOCK hint in

  the DELETE statement. Using the TABLOCK hint causes the delete
  operation to take an exclusive lock on the table instead of a row or
  page lock. This allows the pages to be deallocated. For more
  information about the TABLOCK hint, see Table Hints (Transact-SQL).

Use TRUNCATE TABLE if all rows are to be deleted from the table.

Create a clustered index on the heap before deleting the rows. You
can

  drop the clustered index after the rows are deleted. This method is
  more time consuming than the previous methods and uses more temporary
  resources.

  Note

  Empty pages can be removed from a heap at any time by using the ALTER
  TABLE  REBUILD statement.

So what you can do now is rebuild the table into another database as you have no space in current database. And then create a clustered index on it to avoid the same situation in future


1 to 1 (or possibly 0) relationships are not cross referenced, they are implemented as your 2nd example, with one of both entities related to the other one (and not the other way around).

Linking both ways would be redundant for one of the relationships, as you just need 1 to know the nature of the link. Also, in databases that don't support deferrables foreign keys or multiple inserts in one statement (thanks to  @ypercubeᵀᴹ for this note), you won't be able to insert the rows without disabling the foreign key first.

If the existance of stuff depends on more and more depends on stuff then there is a problem of the chicken and the egg. Which one really represents the entity you are trying to store? Do you really need 2 different tables for them? Does your entity exist only when both records are created?

1 to 1 relationships always raise an eyebrow when designing. Maybe if you had a particular case scenario to share we could delve more in detail. The only few exceptions I've implemented 1 to 1 relationships are when we inherited previous databases with tables that had 100+ columns, and we split them up for performance and storage issues, but not for pure designing reasons.


Yes, you have to fill in the notification schedule. This site defines to whom the mail will be sent on which time and day. If you shut down your database at a time, where no mail-address is set in the schedule, OEM will not send any mails.

You can fill in multiple mail addresses in the field for the schedule to have multiple persons receiving alert mails.

If you want the same addresses to be used every time, set the start and end so that 24 hours are covered and select all weekdays.

For testing the SMTP-Server, there is a link on the site, where you define one to send a test-mail.

The purpose of the notification schedule is e.g. for having different persons to be notified during working hours than at night.


My understanding of pg_restore (which you'll have to use because you created a pg_dump with format=custom) is that if you supply the --dbname=... flag you will restore into that database (without dropping it).

If you supply the --clean command the db and objects will be dropped before being recreated.

If no --dbname=... flag is supplied, the creation commands will spool to stdout, which you can redirect to a file and load using psql.

To test, try:

pg_dump --format=c --no-privileges --no-owner --file=/tmp/`hostname`-`date +%d%m%Y`.backup --username=admin --host=127.0.0.1 database_name

pg_restore your_dump_file > createdb.sql

psql -d database_name -U postgres -f createdb.sql


dm_exec_describe_first_result_set_for_object for multiple result sets

Does a procedure exist to return metadata for all result sets in a stored procedure?

No and Yes.

No

There is no pure T-SQL means of accessing more than the first result set. Even OPENROWSET and OPENQUERY have the same limitation:

  Although the query might return multiple result sets, OPEN(ROWSET|QUERY) returns only the first one.

For the record, I am neither saying nor implying that there is any common technical reason for this limitation. I am just pointing out that the limitation is not confined to sp_describe_first_result_set, sys.dm_exec_describe_first_result_set, and sys.dm_exec_describe_first_result_set_for_object.

Yes

The only way to capture info -- result set meta-data and even the results -- for result sets 2 - n is via application code. You would first execute the queries / stored procedure(s) by using SqlCommand.ExecuteReader(CommandBehavior) with a CommandBehavior of KeyInfo. Then, you can get the result set meta-data using the SqlDataReader.GetSchemaTable method, and calling the SqlDataReader.NextResult method to cycle through the result sets. Just keep in mind that while doing this via app code does not have the limitations of not working with Dynamic SQL and temporary tables, it does actually run the SQL code, and if you have DML statements and only want the result set meta-data without causing any data changes, then you will have to wrap the SQL you are testing in a BEGIN TRAN / ROLLBACK TRAN.

The type of application can be a regular Windows App, Console App, Web App, etc, or it can even be a SQLCLR function / stored procedure.

With regards to doing this via SQLCLR, a stored procedure already exists that does what is described here. It is called DB_DescribeResultSets and is part of the SQL# library (which I am the author of, and while there is a Free version, DB_DescribeResultSets is only available in the Full version).


Assuming pro1, criterium1 should be AA1, this produces the results you are looking for:

SELECT pos.id, pro.id, pos.criterium1, pos.criterium2
   , pro.criterium1, pro.criterium2
FROM Positions pos
JOIN Products pro ON pos.criterium1 = pro.criterium1
   OR (pos.criterium2 = pro.criterium2
   AND pos.id NOT IN (
      SELECT posx.id FROM Positions posx
      JOIN Products prox ON posx.criterium1 = prox.criterium1
   ))
ORDER BY 1;

Test query (only tested in Oracle):

WITH Products AS (
   SELECT 1 id, 'AA1' criterium1, '910' criterium2 FROM dual UNION ALL
   SELECT 2 id, '106'  criterium1, 'BB2' criterium2 FROM dual UNION ALL
   SELECT 3 id, 'AB1'  criterium1, 'XXY' criterium2 FROM dual
   ),
   Positions AS (
   SELECT 1 id, NULL  criterium1, '910' criterium2 FROM dual UNION ALL
   SELECT 2 id, '106' criterium1, 'CCC' criterium2 FROM dual UNION ALL
   SELECT 3 id, 'XXX' criterium1, 'BB2' criterium2 FROM dual UNION ALL
   SELECT 4 id, 'AA1' criterium1, 'XXY' criterium2 FROM dual UNION ALL
   SELECT 5 id, NULL  criterium1, '123' criterium2 FROM dual
   )
SELECT pos.id, pro.id, pos.criterium1, pos.criterium2
   , pro.criterium1, pro.criterium2
FROM Positions pos
JOIN Products pro ON pos.criterium1 = pro.criterium1
   OR (pos.criterium2 = pro.criterium2
   AND pos.id NOT IN (
      SELECT posx.id FROM Positions posx
      JOIN Products prox ON posx.criterium1 = prox.criterium1
   ))
ORDER BY 1;


Is it possible to raiserror within a For Delete Trigger that rolls back the deletes

In order for that to work as you've coded it above, you need a rollback after your RAISERROR or, as the comments correctly state, wrap in a TRY/CATCH.

By itself, it's not going to be treated the same as an actual statement or batch terminating error. For example, this will work the way you expect:

create table T1 ( i1 int  );
create table t2 (i2 int primary key);
go

create trigger T1_ForDelete on T1
for delete
as
    insert into t2 (i2)
    SELECT i1
    FROM DELETED
go

insert into T1 (i1) values (1);
insert into t2 (i2) values (1);

delete from T1;
go

SELECT * from t1

In the above code the trigger will error, and cause the delete (outer) transaction to rollback. By itself, RAISERROR is only really transferring control and/or sending messages up the stack (so to speak)

BOL describes this behavior:

  When RAISERROR is run with a severity of 11 or higher in a TRY block,
  it transfers control to the associated CATCH block. The error is
  returned to the caller if RAISERROR is run:

  Outside the scope of any TRY block.

  With a severity of 10 or lower in a TRY block.

  With a severity of 20 or higher that terminates the database
  connection.

So RAISERROR outside the scope of a TRY block simply returns the error to the caller and is not treated as a statement-terminating error regardless of the severity you define.


Are you using a  simple recovery mode? if you do not need the full mode this could help.

How big is the Log file now? maby it got huge again according to a full recovery mode, try to shrink it again if you change to the simple recovery mode and check it then.


I just wanted to share how we dealt with this - in case this helps others. Based on suggestions from some DBAs in Postgres User Groups, I created a cron job that executes twice a month. We use daily partitions. So the cron job executes a bash script. The bash script connects to the database using psql and calls a function which takes in the table that needs to be partitioned and the date interval to create the partitions for. Here is the function I used to create partitions. This has worked without any issues for us.

CREATE OR REPLACE FUNCTION public.create_partition_tables(p_parent_table text, p_partition_start_date date, p_partition_end_date date)
RETURNS void AS
    $body$
    DECLARE
        partition_date date;
        v_tablename text;
    BEGIN
        for partition_date in
            select date(generate_series(p_partition_start_date, p_partition_end_date, interval '1 day'))
        loop

            SELECT tablename INTO v_tablename FROM pg_catalog.pg_tables WHERE schemaname='public' and tablename = p_parent_table || '_p' ||to_char(partition_date, 'yyyy_mm_dd');
            IF v_tablename IS NOT NULL THEN
                CONTINUE;
            END IF;

            execute format('create table public.' || p_parent_table || '_p' ||to_char(partition_date, 'yyyy_mm_dd')|| ' partition of public.'
            || p_parent_table || ' for values from (%L) to (%L)', partition_date, date(partition_date + interval '1 day') );
        end loop;
    END;
    $body$
LANGUAGE 'plpgsql' VOLATILE STRICT


Connecting to server which located remotely throws timeout exception

In your manual procedure you state that you specify "name" in the server name, not an instance.

However in your connection string you specify

Data Source =tcp:<IPAddress of RDP Machine>\<ServerName>;

Which indicates <servername> should be used as an instance name.

You should probably remove \<servername> part of your connection string.

You should also verify the necessary ports are open on the firewall and make sure SQL Server is configured to allow remote connections as well as make sure tcp/ip is in the enabled protocols.

There is a decent MSDN blog post covering a lot of other steps you can take to troubleshoot your connectivity issues: Troubleshoot Connectivity/Login failures (18456 State x) with SQL Server


Display summary results based on empty value in another column

COUNT(expr) will count the number of rows where expr is not null, which can be used to figure out which groups have any nulls:

select officecode
     , case when count(*)>count(auditdate) then 'Incomplete'
            else 'Complete' end audit_status
from foo
group by officecode;

OFFICECODE | AUDIT_STATUS
:--------- | :-----------
DEF        | Complete
ABC        | Complete
XYZ        | Incomplete

dbfiddle here

you can get the same result with nvl2 (or decode, etc):

select officecode
     , case when sum(nvl2(auditdate,0,1))>0 then 'Incomplete'
            else 'Complete' end audit_status
from foo
group by officecode;


Store time taken to run query in a table

A good start would be to look at the exec_query_stats view.
You can get the execution count and total elapsed time and a lot more, but this is only from queries that have been cached.

SELECT dest.TEXT AS [Query],
       (deqs.total_elapsed_time+0.0)/deqs.execution_count average_elapsed_time,
       deqs.*
  FROM sys.dm_exec_query_stats AS deqs
 CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
 ORDER BY 2 DESC

You might want to issue a CHECKPOINT and DBCC DROPCLEANBUFFERS after you've installed your new views to clear the cache.


Follow up to: SQL Server 2008 R2: Problems after computer name change

You could resort to Powershell for that.

I have tested this on a server with multiple instances and this should work:

foreach ($instance in (get-itemproperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances)
{
    if ($instance -eq  "MSSQLSERVER")
    {
    $serverinstance = "."
    }
    else
    {
    $serverinstance = ".\" + $instance
    }

    Invoke-Sqlcmd -Query "select @@version;" -ServerInstance $serverinstance
}

I replaced your query with something safer on my environment though.

You could use -InputFile to source an sql file


MySQL table proper indexes for performance optimization

"billions of records" and id int(10) unsigned NOT NULL AUTO_INCREMENT -- Beware!  That will top out at about 4 billion.
To speed up the inserts, see if you can batch them something liek this:
insert into `productcatalog`
    (`id`, `SerialNumber`, `BasePrice`, `BatchCode`, `Type`, `ItemCode`,
        `ArrivalDate`, `InsertTimestamp`, `BrandID`, `Model`, `Description`)
    values
    ('1','345618761','23','25','ACC','ABCD','2021-02-14 12:24:44','1613371259','37','RX209',NULL),
    ('2','345618761','24','25','RAF','ABCD','2021-02-15 12:55:17','1613373031','45','GA317',NULL),
    ('3','569014575','43','21','DAT','TPRS','2021-02-13 12:56:34','1613373082','34','PX452',NULL);

For the SELECTs, then indexes (with the columns in the order specified) should help:
INDEX(SerialNumber, ID, ItemCode, BasePrice)  -- for queries 1,2
INDEX(ItemCode, SerialNumber, ArrivalDate)    -- for query 3

Query 1 will (I think) use SerialNumber, ID from the first index and stop after one or two rows.
Query 2 is "covering", so even it needs to scan several rows, it would have to bounce between the index's BTree and the data's BTree.
Query 3 -- The = columns need to be first and the "range" column (Arrival Date) last.
The following changes won't improve performance; I just think they are clearer.  Instead of
AND ItemCode!="ABCD"
AND ItemCode!="PQRS"
AND ItemCode!="MNOP"

AND (ArrivalDate>='2019-01-01 00:00:00' AND ArrivalDate<='2020-12-31 23:59:59')

I prefer these:
AND ItemCode NOT IN ("ABCD", "PQRS", "MNOP")

AND ArrivalDate >= '2019-01-01'
AND ArrivalDate  < '2019-01-01' + INTERVAL 1 YEAR

The following will explain some of my suggestions:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql
This seems odd for a "price":  decimal(4,1)


Estimate average and median efficiently in Postgres?

If an estimate is good enough, then statistical sampling is your friend. I'd probably use a sample size calculator to determine how many rows I need, then write some code to randomly insert that many keys into a table. A join, a function, and you're done.

If you've never done anything like this before, you'll probably want to do some background reading. When I had to do that stuff, I used a handbook from nist.gov.  (And you'll probably be surprised at how small a sample you need.)


Oracle SQL 12c How to correctly use compound foreign keys in compound primary keys?

ORA-02256: number of referencing columns must match referenced columns

  Cause: The number of columns in the foreign-key referencing list is not equal to the number of columns in the referenced list.
  Action: Make sure that the referencing columns match the referenced columns.

The problem is the foreign key should reference both columns used in the composite primary key.

Add the FLIGHT_DATE column to the RESERVATIONS table and change your code:

CONSTRAINT FK_reservations_flight_ID FOREIGN KEY (FLIGHT_ID)
    REFERENCES FLIGHT(FLIGHT_ID,FLIGHT_DATE)
);

To:

CONSTRAINT FK_reservations_flight_ID FOREIGN KEY (FLIGHT_ID,FLIGHT_DATE)
    REFERENCES FLIGHT(FLIGHT_ID,FLIGHT_DATE)
);


mysql: deleted performance_schema, is it a problem?

The MySQL Performance Schema is a feature for monitoring MySQL Server execution at a low level. The Performance Schema is available as of MySQL 5.5.3.

From MySQL Manual there are some of the features of the performance_schema database.

  The Performance Schema provides a way to inspect internal execution of
  the server at runtime. It is implemented using the PERFORMANCE_SCHEMA
  storage engine and the performance_schema database. The Performance
  Schema focuses primarily on performance data. This differs from
  INFORMATION_SCHEMA, which serves for inspection of metadata.

  The Performance Schema monitors server events. An “event” is anything
  the server does that takes time and has been instrumented so that
  timing information can be collected. In general, an event could be a
  function call, a wait for the operating system, a stage of an SQL
  statement execution such as parsing or sorting, or an entire statement
  or group of statements. Currently, event collection provides access to
  information about synchronization calls (such as for mutexes) file and
  table I/O, table locks, and so forth for the server and for several
  storage engines

  Performance Schema events are distinct from events written to the
  server's binary log (which describe data modifications) and Event
  Scheduler events (which are a type of stored program).

For detailed information look this Link Performance Schema.


Is there a query to set a table's engine to innodb if it's not already?

This will mass convert all MyISAM tables to InnoDB

SQLSTMT="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQLSTMT="${SQLSTMT} FROM information_schema.tables WHERE engine='MyISAM' AND"
SQLSTMT="${SQLSTMT} table_schema NOT IN "
SQLSTMT="${SQLSTMT} ('information_schema','mysql','performance_schema')"
SQLSTMT="${SQLSTMT} ORDER BY (data_length+index_length)"
echo "SET SQL_LOG_BIN = 0;" > ConvertMyISAMToInnoDB.sql
mysql -uroot -p -ANe"${SQLSTMT}" > ConvertMyISAMToInnoDB.sql
mysql -uroot < ConvertMyISAMToInnoDB.sql

Why does running ALTER TABLE mydb.mytable ENGINE=InnoDB; take long ?

Under the hood it runs something like this:

CREATE TABLE mydb.mytabletmp LIKE mydb.mytable;
INSERT INTO mydb.mytabletmp SELECT * FROM mydb.mytable;
DROP TABLE mydb.mytable;
ALTER TABLE mydb.mytabletmp RENAME mydb.mytable;

The delay comes when building a brand new Clustered Index (home of PRIMARY KEY entries)

If you want to totally automate the conversion of just one table, you need this:

SET @mydb = 'mydb';
SET @mytb = 'mytable';
SET @newengine = 'InnoDB';
SELECT IF(dbtb2='.',CONCAT('ALTER TABLE ',dbtb1,' ENGINE=InnoDB'),
CONCAT('SELECT ''',dbtb1,' is Already ',engine,''' as \"No Need to Convert\"'))
INTO @ConvertEngineSQL FROM (SELECT CONCAT(A.db,'.',A.tb) dbtb1,
CONCAT(IFNULL(B.db,''),'.',IFNULL(B.tb,'')) dbtb2,engine
FROM (SELECT table_schema db,table_name tb,engine FROM information_schema.tables
WHERE table_schema=@mydb and table_name=@mytb) A LEFT JOIN
(SELECT table_schema db,table_name tb FROM information_schema.tables
WHERE table_schema=@mydb and table_name=@mytb AND engine=@newengine) B
USING (db,tb)) AA;
SELECT @ConvertEngineSQL;
PREPARE st FROM @ConvertEngineSQL; EXECUTE st; DEALLOCATE PREPARE st;

Here is a sample from my PC's MySQL setup

mysql> show create table codewaggle.mytable\G
*************************** 1. row ***************************
       Table: mytable
Create Table: CREATE TABLE `mytable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SET @mydb = 'codewaggle';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @mytb = 'mytable';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @newengine = 'InnoDB';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT IF(dbtb2='.',CONCAT('ALTER TABLE ',dbtb1,' ENGINE=InnoDB'),
    -> CONCAT('SELECT ''',dbtb1,' is Already ',engine,''' as \"No Need to Convert\"'))
    -> INTO @ConvertEngineSQL
    -> FROM (SELECT CONCAT(A.db,'.',A.tb) dbtb1,
    -> CONCAT(IFNULL(B.db,''),'.',IFNULL(B.tb,'')) dbtb2,engine
    -> FROM (SELECT table_schema db,table_name tb,engine FROM information_schema.tables
    -> WHERE table_schema=@mydb and table_name=@mytb) A LEFT JOIN
    -> (SELECT table_schema db,table_name tb FROM information_schema.tables
    -> WHERE table_schema=@mydb and table_name=@mytb AND engine=@newengine) B
    -> USING (db,tb)) AA;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @ConvertEngineSQL;
+----------------------------------------------+
| @ConvertEngineSQL                            |
+----------------------------------------------+
| ALTER TABLE codewaggle.mytable ENGINE=InnoDB |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> PREPARE st FROM @ConvertEngineSQL; EXECUTE st; DEALLOCATE PREPARE st;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

Query OK, 0 rows affected (1.70 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

mysql> SET @mydb = 'codewaggle';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @mytb = 'mytable';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @newengine = 'InnoDB';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT IF(dbtb2='.',CONCAT('ALTER TABLE ',dbtb1,' ENGINE=InnoDB'),
    -> CONCAT('SELECT ''',dbtb1,' is Already ',engine,''' as \"No Need to Convert\"'))
    -> INTO @ConvertEngineSQL
    -> FROM (SELECT CONCAT(A.db,'.',A.tb) dbtb1,
    -> CONCAT(IFNULL(B.db,''),'.',IFNULL(B.tb,'')) dbtb2,engine
    -> FROM (SELECT table_schema db,table_name tb,engine FROM information_schema.tables
    -> WHERE table_schema=@mydb and table_name=@mytb) A LEFT JOIN
    -> (SELECT table_schema db,table_name tb FROM information_schema.tables
    -> WHERE table_schema=@mydb and table_name=@mytb AND engine=@newengine) B
    -> USING (db,tb)) AA;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT @ConvertEngineSQL;
+-----------------------------------------------------------------------+
| @ConvertEngineSQL                                                     |
+-----------------------------------------------------------------------+
| SELECT 'codewaggle.mytable is Already InnoDB' as "No Need to Convert" |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> PREPARE st FROM @ConvertEngineSQL; EXECUTE st; DEALLOCATE PREPARE st;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

+--------------------------------------+
| No Need to Convert                   |
+--------------------------------------+
| codewaggle.mytable is Already InnoDB |
+--------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

Give it a Try !!!


You can use a recursive CTE similar to the following to get the whole ancestor list (parents and children) for a given TradeID:
WITH RecursiveCTE_Children AS
(
    SELECT GeneratedFrom, TradeID
    FROM MyData

    UNION ALL

    SELECT RC.TradeID AS GeneratedFrom, MD.TradeID
    FROM RecursiveCTE_Children AS RC
    INNER JOIN MyData AS MD
        ON RC.TradeID = MD.GeneratedFrom
),
RecursiveCTE_Ancestors AS
(
    SELECT GeneratedFrom, TradeID
    FROM MyData

    UNION ALL

    SELECT MD.GeneratedFrom, RC.GeneratedFrom AS TradeID
    FROM RecursiveCTE_Ancestors AS RC
    INNER JOIN MyData AS MD
        ON RC.GeneratedFrom = MD.TradeID
)

SELECT TradeID
FROM RecursiveCTE_Children
WHERE GeneratedFrom = 32845

UNION

SELECT GeneratedFrom
FROM RecursiveCTE_Ancestors
WHERE TradeId = 32845


Does MySQL Workbench lock database during migration process?

EDIT:

I guess I deserved the -1 for my brevity and incomplete (i.e. wrong) answer. I was on the phone and had to keep it quick.

Let me try again.

I did a dry run for you on two test servers using the MySQL Workbench 6.3 CE migration wizard.

With the default options the migration is -non- locking. This vanilla method has a downside however. If you migrate while other DML transactions are going on, you might get an inconsistent data set on the destination, as it will not migrate a consistent snapshot as seen in the statement below. This risk is designed to achieve the non locking nature.

Here is an excerpt of the general log for the vanilla run:

151105  7:51:09 10398 Connect   root@my_host_name.com as anonymous on
    10398 Query SET NAMES 'utf8'
    10398 Query USE `test_db_01`
    10398 Query SELECT count(*) FROM `t2`
    10398 Query USE `test_db_01`
    10398 Query SELECT count(*) FROM `t1`
    10398 Quit
    10399 Connect   root@my_host_name.com as anonymous on
    10399 Query SET NAMES 'utf8'
    10399 Query USE `test_db_01`
    10399 Query SELECT count(*) FROM `t2`
    10399 Query USE `test_db_01`
    10400 Connect   root@my_host_name.com as anonymous on
    10400 Query SET NAMES 'utf8'
    10400 Query USE `test_db_01`
    10400 Query SELECT count(*) FROM `t1`
    10400 Query USE `test_db_01`
    10400 Prepare   SELECT `id`, `intcol1`, `intcol2`, `intcol3`, `intcol4`, `charcol1`, `charcol2`, `charcol3`, `charcol4`, `charcol5`, `charcol6`, `charcol7`, `charcol8`, `charcol9`, `charcol10` FROM `t1` ORDER BY `id`
    10399 Prepare   SELECT `id`, `intcol1`, `intcol2`, `intcol3`, `intcol4`, `charcol1`, `charcol2`, `charcol3`, `charcol4`, `charcol5`, `charcol6`, `charcol7`, `charcol8`, `charcol9`, `charcol10` FROM `t2` ORDER BY `id`
    10399 Execute   SELECT `id`, `intcol1`, `intcol2`, `intcol3`, `intcol4`, `charcol1`, `charcol2`, `charcol3`, `charcol4`, `charcol5`, `charcol6`, `charcol7`, `charcol8`, `charcol9`, `charcol10` FROM `t2` ORDER BY `id`
    10400 Execute   SELECT `id`, `intcol1`, `intcol2`, `intcol3`, `intcol4`, `charcol1`, `charcol2`, `charcol3`, `charcol4`, `charcol5`, `charcol6`, `charcol7`, `charcol8`, `charcol9`, `charcol10` FROM `t1` ORDER BY `id`
    10400 Close stmt
    10399 Close stmt
    10399 Quit
    10400 Quit

Another option would be to use mysqldump. This will be -non- locking but, also create a consistent snapshop of the database.

Mysqldump --databases [db name] --user [user] --password --opt --triggers --routines --events --single-transaction | mysql -u[user] -p -h[host_address]


How to add DATETIME value for session user

I see two problems with

$query = "INSERT INTO log (user, timein) VALUES ($user , NOW())";

Problem #1 : You need quotes around $user

Problem #2 : You need use timein instead of NOW()

Now, you need to make a decision.

If you want to supply the time yourself, then the query should be

$query = "INSERT INTO log (user, timein) VALUES ('$user','$tin')";

If you want the DB Server's clock time, then the query should be

$query = "INSERT INTO log (user, timein) VALUES ('$user',NOW())";

thus eliminating the need for $tin= $_POST["timein"];


I think that counter is NULL because you don't assign it a value. And after you increment it, nothing changes because NULL + 1 = NULL. Also your UPDATE will not work if MCOUNT is not 0.

Unrelated: I suggest to use a foreign key instead of CHECK.


SQL Express has many disadvantages. If you've already paid for a real license you should use it. You don't have to consolidate everything in one instance, but I prefer not using Express...

See http://msdn.microsoft.com/en-us/library/cc645993%28v=SQL.110%29.aspx

Max Compute Capacity
Max memory utilized
Max Database size
AND
No SQL Agent!


PostgreSQL extension installation, best practices

I can immediately think of two extensions which have nothing to compile, only files to be copied.  I would look to their Makefiles for inspiration.

https://github.com/glynastill/table_log_pl/blob/master/Makefile

https://api.pgxn.org/src/first_last/first_last-0.1.1/Makefile


@id as part of the execution variable @sql is doing nothing. It is not tied to the declared and set variable unless you build the string around it, i.e. concatenate it to the string like this:

set @sql = 'insert into   #temphold(dateadded,dateupdated,id) '
          +'select   getdate(),getdate(),COALESCE(' + @id + ','''') '

Notice the + either side of the @id variable.

At the end of the day, @sql is just a string until it's executed using the EXEC() command. Simply treat it as such until it compiles as fully qualified T-SQL.


How can I make sure the SQL Server query optimizer uses the exact tables in the query

There are only two possible ways that the execution plan for your query might not read from table1 directly. I will use the following indexed view created in the AdventureWorks sample database to illustrate:

CREATE VIEW dbo.IV
WITH SCHEMABINDING
AS
SELECT p.ProductID, cnt = COUNT_BIG(*)
FROM Production.Product AS p
JOIN Production.TransactionHistory AS th ON
    th.ProductID = p.ProductID
GROUP BY
    p.ProductID;
GO
CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.IV (ProductID);

1. Table1 is an indexed view

In this case, the optimizer will make an estimated-cost-based decision whether to read from the indexed view or to expand the view and read from the base tables it references. Example:

SELECT
    ProductID,
    cnt
FROM dbo.IV;

The optimizer chooses to read from the indexed view directly:

If the optimizer chose instead to expand the view, we could use the NOEXPAND table hint to prevent that. This hint is required in non-Enterprise SKUs to access indexed views directly.

The EXPAND VIEWS query hint forces expansion of the view, leading to a plan that reads from the base tables:

SELECT
    ProductID,
    cnt
FROM dbo.IV
OPTION (EXPAND VIEWS);

2. An indexed view exists which matches the query

SQL Server Enterprise Edition contains a feature which can match queries to indexed views, where the indexed view is not referenced in the query:

SELECT p.ProductID, cnt = COUNT_BIG(*)
FROM Production.Product AS p
JOIN Production.TransactionHistory AS th ON
    th.ProductID = p.ProductID
WHERE
    p.ProductID BETWEEN 1 AND 100
GROUP BY
    p.ProductID;

Despite not mentioning our indexed view, the execution plan does:

About indexed view matching

This matching is only possible where the query processor has guarantees that the rewrite will always produce correct results. These guarantees include the fact that any changes to the tables referenced by the indexed view will also be reflected in the indexed view.

Any INSERT, UPDATE, DELETE or MERGE query that affects the Product or TransactionHistory tables will have extra operations added to the execution plan to make the appropriate changes to our indexed view.

The query optimizer does not keep track of any tables that might be created by the user from the base tables - they will not be maintained to reflect base table changes, and any indexed views created on these new tables will reflect only changes to those tables, not the originals. There is no magic here - the relationship between an indexed view and its base tables is very explicit.

The thin table example

To take an example that appears to match your question, say we create a 'thin' extract from the Product base table, containing only the ProductID column:

CREATE TABLE dbo.ThinProduct
(
    ProductID integer NOT NULL

    CONSTRAINT PK_ThinProduct
    PRIMARY KEY (ProductID)
);

INSERT dbo.ThinProduct
    (ProductID)
SELECT
    p.ProductID
FROM Production.Product AS p;

Now we drop the original indexed view and create a new one that references the thin table instead:

DROP VIEW dbo.IV;
GO
CREATE VIEW dbo.IVthin
WITH SCHEMABINDING
AS
SELECT p.ProductID, cnt = COUNT_BIG(*)
FROM dbo.ThinProduct AS p
JOIN Production.TransactionHistory AS th ON
    th.ProductID = p.ProductID
GROUP BY
    p.ProductID;
GO
CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.IVthin (ProductID);

A new query that references the thin Product table directly, can use the new indexed view:

SELECT p.ProductID, cnt = COUNT_BIG(*)
FROM dbo.ThinProduct AS p
JOIN Production.TransactionHistory AS th ON
    th.ProductID = p.ProductID
WHERE
    p.ProductID BETWEEN 1 AND 100
GROUP BY
    p.ProductID;

A query that references the original Product table cannot use this new indexed view, because there are no guarantees that IVthin will stay in step with any changes to the Product table (it will reflect changes to the ThinProduct table):

SELECT p.ProductID, cnt = COUNT_BIG(*)
FROM Production.Product AS p
JOIN Production.TransactionHistory AS th ON
    th.ProductID = p.ProductID
WHERE
    p.ProductID BETWEEN 1 AND 100
GROUP BY
    p.ProductID;

The execution plan shows base table access - it cannot use the indexed view:

Summary

Indexed view matching can only be performed where the appropriate guarantees are enforced by the engine. The scenario outlined in the question cannot occur as outlined there. The SSIS package must be referencing indexed views that can be expanded or issuing queries that can be matched to an indexed view for a query reference to table1 to resolve to anything other than that named object.

I appreciate this answer may not help you except in a general sense, but the discussion around the question has gone on for quite some time without clear specifics emerging. The question could be made easier for answerers to address specifically by including the actual SSIS query, table and indexed view definitions, and actual execution plans.


Are there any performance differences with a GUID vs INT clustered indexed column, especially with joins on those columns?

I understand a GUID is 4x the data size of an INT, but I was asking my question to really understand why and what's going on "under the hood" that causes GUIDs to perform worse, even after the indexes are rebuilt and there's minimal fragmentation differences between the two types.

I would say that the issue is primarily just what you started out with here, in what I quoted: UNIQUEIDENTIFIER is 4x bigger than INT (16 bytes compared to 4). This hurts performance in three ways (I am ignoring fragmentation since the question explicitly accounts for that):

Every bit compared is another thing to do. More things to do for the CPU takes more time. That time won't be noticed on modern systems when looking at 100 rows. But over 1 million rows? Now you are talking about (in terms of bytes), comparing 16 million things vs. comparing only 4 million things.

Memory is not free: neither in terms of money, nor in terms of time spent allocating and deallocating. In order to join rows, data pages containing those rows must be read into memory (the buffer pool). There is not an infinite amount of memory (especially if you are using Standard Edition or Express Edition). The more memory you take up in one operation is that much less left over for other concurrent operations. But even if memory were infinite, it still takes time to load those values into memory so that they can then be compared. Loading 16 million bytes naturally takes longer than loading 4 million bytes.

Disk is not free: even with SSD, physical I/O is still a cost you pay, which is why things are cached in memory. Data pages are only 8kb. If you have a 16 byte value, it will reduce the number of rows that can fit on that 8kb data page as compared to having a 4 byte value. The more data pages that a table takes up, the more physical I/O is needed to read and write the same number of rows.
A related issue is with indexes. You will likely index the columns that you are joining on. Same concern as with the main table: fewer index rows per page means more pages required for the index, meaning more time spent reading and writing to every index affected by any given operation (new row in a table will need to create that new row in all indexes on that table (minus filtered indexes that would exclude it), and assuming we are talking about the clustered index, the clustered index key is copied to all of the non-clustered indexes)


Get Nested/Hierarchical Data from SQL Table

IMHO you don't need a recursive solution, you can get it by using a simple JOIN.

DECLARE @Period datetime = '20170531';

SELECT     t.TagID, t.TagName, tm.TaskID
FROM       TagAssignment ta
INNER JOIN TagTaskMapping tm
ON         tm.TagID = ta.TagID
INNER JOIN Tag t
ON         t.TagID = tm.TagID
WHERE      ta.Period = @period
ORDER BY   tm.TagID, tm.TaskID;
GO

TagID | TagName | TaskID
----: | :------ | -----:
    2 | Level 5 |      1
    2 | Level 5 |      2
    2 | Level 5 |      3
    3 | Level 3 |      1
    3 | Level 3 |      3

dbfiddle here


This question is more complicated than how SGA relates to cpu utilization.  Many other factors can cause a high CPU usage even if there is adequate SGA.  Start looking at the other Oracle diagnostics for the usage of the shared pool, large pool and buffer.
Even more important you need to investigate the application front end, number of sessions, type of data operations your users are up to...
What kind of SQL is getting fed to the database, does it use bind variables, does it require full table scans?
Oracle tuning is more successful when you look at the whole picture. Tell us more...


MySQL Enterprise Connector/PHP Plugin for php 5.4

Now a little update:

I tried mysqlenterprise.so for php 5.4, 5.5 and 5.6 and i'm sure it won't work cause it's a compatibility issue.

In PHP Plugin Pack 3.0.22 there's only mysql-enterprise-plugin-1.0.1-php5.3 and unfortunately it works only on php 5.3. It caused by the change of php_body_write to php_output_write on PHPAPI main/output.c after 5.4 release. I checked if there's a new extension but can not find anything.

Only solution seems to open a support ticket.

As shown in documentation you need to load your mysqlnd before mysqlenterprise.so. Mostly this error is about wrong configuration or choosing wrong version for extensions.

Did you compile your php enabled with mysqlnd or installed mysqlnd extension?

First check your php configuration on command line with php -i for controlling your extensions loaded successfully.
You can read more clean you grep result with mysql and check your extension list with grep -Hrv ";" /etc/php.d/ | grep -i "extension="

/etc/php.d directory can be different in different installations.

If you can't see your extensions details, check your php configuration ini files under extension folderand make sure you have mysqlnd.ini. For mysql enterprise ini file, you can create one mysqlzz_enterprise.ini or just add it in mysqlnd.ini file.

If this not solve your problem, try to uninstall php and make sure you installed mysqlnd, then add your extension to your ini file, check your extension ini files as in documentation tells.

Example configure options for php source build:

./configure --with-pdo-mysql=mysqlnd --with-mysql=mysqlnd


How to restrict SQL server profiler to show my symmetric key password while executing query from C# code?

Don't execute the statement as a string by using sp_executesql

Execute the statement directly:

OPEN SYMMETRIC KEY sym_RegAna_Default DECRYPTION BY ASYMMETRIC KEY asym_RegAna_Default WITH PASSWORD = 'admin@123'

That way, SQL Server is able to parse the statement and will notice that it's a command that contains sensitive information and will not show it in any trace output.

it will show up as:

--*OPEN SYMMETRIC KEY----------------------------

Please be aware that this applies to ALL statements that contain passwords. So also when you create the keys.


Is a Self-defining Table of Domains Normalized?

This is a called a "self-referencing table", or "single-table hierarchy". You normally query them with a recursive CTE because you want the children, not just on a single conditional.

Note, normally you place the fk of the root as null

CREATE TABLE foo(pk,fk,description)
AS VALUES
    ( 1 , null , 'domains' ),
    ( 2 , 1 , 'people' ),
    ( 3 , 1 , 'cars' ),
    ( 4 , 2 , 'tom' ),
    ( 5 , 2 , 'smith' ),
    ( 6 , 3 , 'vmw' ),
    ( 7 , 2 , 'betty' ),
    ( 8 , 3 , 'ford' );

WITH RECURSIVE t(pk, fk, description, level) AS (
    SELECT pk, fk, ARRAY[description], 0
    FROM foo
    WHERE fk IS NULL
    UNION ALL
        SELECT foo.pk, foo.fk, t.description || foo.description, t.level+1
        FROM t
        JOIN foo ON (foo.fk = t.pk)
)
SELECT *
FROM t;

 pk | fk |      description       | level
----+----+------------------------+-------
  1 |    | {domains}              |     0
  2 |  1 | {domains,people}       |     1
  3 |  1 | {domains,cars}         |     1
  4 |  2 | {domains,people,tom}   |     2
  5 |  2 | {domains,people,smith} |     2
  6 |  3 | {domains,cars,vmw}     |     2
  7 |  2 | {domains,people,betty} |     2
  8 |  3 | {domains,cars,ford}    |     2
(8 rows)

Now you can find all {domains,cars} like this.

WITH RECURSIVE t(pk, fk, description, level) AS (
     SELECT pk, fk, ARRAY[description], 0
     FROM foo
     WHERE fk IS NULL
     UNION ALL
      SELECT foo.pk, foo.fk, t.description || foo.description, t.level+1
      FROM t
      JOIN foo ON (foo.fk = t.pk)
    )
    SELECT *
    FROM t
    WHERE description @> ARRAY['domains','cars'];
 pk | fk |     description     | level
----+----+---------------------+-------
  3 |  1 | {domains,cars}      |     1
  6 |  3 | {domains,cars,vmw}  |     2
  8 |  3 | {domains,cars,ford} |     2
(3 rows)

But the real advantage comes with just a bit more complexity.

INSERT INTO foo(pk,fk,description) VALUES
  ( 9, 8, 'Mustang' );

Now rerunning that same query you ge t

 pk | fk |         description         | level
----+----+-----------------------------+-------
  3 |  1 | {domains,cars}              |     1
  6 |  3 | {domains,cars,vmw}          |     2
  8 |  3 | {domains,cars,ford}         |     2
  9 |  8 | {domains,cars,ford,Mustang} |     3
(4 rows)

Mustang is under ford, and it's all in the domain of cars.

You may also want just the last item of the array in the result set, or a trail of pkids. All of those are minor variations of this same pattern utilizing Recursive CTEs.

All examples tested with PostgreSQL


Postgres, levenshtein distance and nested queries

I suggest to use a trigram index provided by the additional module pg_trgm.  Combine that with the length of the string to get a valid pre-selection.

Drop the columns T4,T16 and T64 (faster in a single statement), and run VACUUM FULL or CLUSTER.
Install pg_trgm. Details here:

Full Text Search With PostgreSQL

Create a GiST index on tl and length(tl).

There are a details to be observed. Related answers on dba.SE involving pg_trgm:

https://dba.stackexchange.com/search?q=pg_trgm


What are the advantages and disadvantages of storing binary data in a SQL table vs a network drive?

One advantage is that if the program that needs to access the data does not have access to the file system, it's not a problem! If the database is accessed by multiple applications on different systems, they probably shouldn't have file-system access to your database server. You could always store a path and they create a web service to allow these applications to request a file on a specific path, but that's a bit more work so you'd better be sure it's worth it. Keeping the binary data in the database and returning it in the record set is simpler.

A disadvantage is that your database will now require more storage space and this will impact backups and other things. Sometimes having large amounts of binary data in a database can also have an impact on performance. Some DBAs handle this by moving BLOB data to tables in separate schemas that are managed differently from the main "data" schemas. While such techniques may work, you might need a deeper understanding of database management and tuning to get it working well, if you have LOTS of binary data.


How to do a junction table referencing constraint?

In questionnaire, add a key that includes the ID and the type so that it can be referenced.
CREATE TABLE questionnaire
             (question_id integer,
              question_type integer,
              ...
              PRIMARY KEY (question_id),
              UNIQUE (question_id,
                      question_type));

In mapping add columns for the types. Use check constraints to enforce they are 1 or 2 respectively. In the foreign key constraints, let the pair of ID and type reference the additional key of questionnaire.
CREATE TABLE mapping
             (question_id_type1 integer,
              question_type_type1 integer
                                  DEFAULT 1,
              question_id_type2 integer,
              question_type_type2 integer
                                  DEFAULT 2,
              ...
              CHECK (question_type_type1 = 1),
              CHECK (question_type_type2 = 2),
              FOREIGN KEY (question_id_type1
                           question_type_type1)
                          REFERENCES questionnaire
                                     (question_id,
                                      question_type),
              FOREIGN KEY (question_id_type2
                           question_type_type2)
                          REFERENCES questionnaire
                                     (question_id,
                                      question_type));


What is the correct name for "relational role"?

I don't believe there is a word for this exact thing. This is because the RDBMS design does not force you to define your field as being of one role or another. A column can serve multiple roles simultaneously if you want it to. Although in practice it is almost universally better to seperate the keys from the data in your design, it is not actually required.


Upgrade SQL Server 2016 R Service (in-database)

I faced the very same problem (when trying to follow the steps described in Use sqlBindR.exe to Upgrade an Instance of R Services). I could not find any SQLBindR.exe.

A friend of mine pointed out to me, that I had to explicitly install "Microsoft R Server", not as part of the SQL Server install bits and pieces, but as a separate download.

All options about how you can download Microsoft R Server are described in a blog post at MSDN, Run Microsoft R Server for Windows by Heidi Steen.

Download and install was done in some minutes. SQLBindR took some more minutes.
Voilá: Now the following script is showing me, what I was looking for. :-)

declare @v varchar(100);
exec sp_execute_external_script @language=N'R'
, @script = N'v <- toString(getRversion())'
, @params = N'@v varchar(100) output'
, @v = @v out;

print @v;

-- returns 3.3.2


Is it possible to have both a subquery and a function in a WITH clause?

Here is an example:

with
  function f1 return number is begin return 1; end;
  t1 as (select * from dual connect by level <= 3)
  select f1 from t1;
  /

        F1
----------
         1
         1
         1


Postgres or MapReduce to Match Reciprocal Needle Pairs from a Stack of Needles?

I think it could be helpful for you to think of MapReduce as (essentially) a distributed query engine.  I know it isn't one-to-one, but with SELECT and aggregate functions such as sum(), max(), etc being very much like a Map() operation, and GROUP BY being very much like a Reduce(), there is quite a bit of similarity.

In your case, on the same hardware, I think the only benefit you will be able gain from using MapReduce is the distribution to multiple cores.  Remember that query processes in PostgreSQL only use a single core per query, so there is a lot of waste of your 64 cores if you're running this one query at a time.

Parallelization

Perhaps you'd be able to break up the query and run it over only segments of your locations table, and then run these queries in parallel using a connection pooler like pgBouncer?

For some good info about access data in sequential chunks (pagination), check out this amazing blog post by Markus Winand.

An example of how you might break this into chunks might be (warning:untested SQL ahead)

SELECT
id
FROM
(SELECT * FROM locations LIMIT x OFFSET y) AS locs_fragment
WHERE
a_lat BETWEEN SYMMETRIC (38.5565 - 0.00055533333334) AND (38.5565 + 0.00055533333334) AND
a_lon BETWEEN SYMMETRIC (-77.2797222222222 - 0.00055533333334) AND (-77.2797222222222 + 0.00055533333334) AND
criteria_1 BETWEEN SYMMETRIC (42.7 - 1.5) AND (42.7 + 1.5) AND
criteria_2 = 23 AND
criteria_3 = 'ABCD' AND
(criteria_4 = '0014980726' OR criteria_4 IS NULL) AND
criteria_5 = 'A' AND
criteria_6 = 1;

Note that the sub-select SELECT * FROM locations LIMIT x OFFSET y, given no additional criteria, will extract rows in ctid order. By selecting appropriate LIMIT and OFFSET values for a rnage of queries, you can essentially run the query in parallel.

Denormalization

Don't rule out denormalization as an option as well. In this case, it can help you to much more quickly get you the results you want. Of course, denormalization will only really help if your query criteria will be fairly stable over time, so that you can effectively pre-calculate results sets.

I'd say there are two decent options:

(1)  Use partial indexing, where a given index is designed around some set of criteria, as

CREATE INDEX idx_locations_criteria_set_0
ON locations (id)
WHERE
a_lat BETWEEN SYMMETRIC (38.5565 - 0.00055533333334) AND (38.5565 + 0.00055533333334) AND
a_lon BETWEEN SYMMETRIC (-77.2797222222222 - 0.00055533333334) AND (-77.2797222222222 + 0.00055533333334) AND
criteria_1 BETWEEN SYMMETRIC (42.7 - 1.5) AND (42.7 + 1.5) AND
criteria_2 = 23 AND
criteria_3 = 'ABCD' AND
(criteria_4 = '0014980726' OR criteria_4 IS NULL) AND
criteria_5 = 'A' AND
criteria_6 = 1;

With such an index defined, if you make a query with a matching predicate, an index scan is performed rather than having to perform all the calculations for the SELECT statement.

(2)  Another denormalization option is to use triggers during INSERT on your locations table to verify if certain conditions are met, and when they are met, store the id of that locations entry in another table, thus making a simple table scan all you need to do at a later time to find matching entries.

Both of these cases will require additional storage space, as well as some minor INSERT overhead for predicate checking, but it allows you to distribute your predicate checking over time, rather than being forced to do it all at once when a query is issued.

Conclusions

As with any advice on the internet, take mine with a grain of salt! :P  I've done some work in both of these areas, but that doesn't make me an expert in your application area. Run some small scale tests on either approach to see what works best for your application.


First step - take a backup of both databases (and other files my.cnf &c.)

Second step - you have your mysqldump of your old database.

Third step - edit the old mysqldump file and change the line

CREATE DATABASE old_database_name (may have backticks around name - also I'm assuming that old_database_name is the same as the current_database_name).

to

CREATE DATABASE staging (or name of your choice, but not one that
already exists in the current server schemas - SHOW SCHEMAS; will show you this information.

Fourth step - Using the MySQL client (or MySQL Workbench), create an empty schema on the current database also called staging (CREATE SCHEMA staging;)

Fifth step - "restore" the staging database to the current server, using the instructions from here.

In this case, it will be

mysql -u root -p[root_password] staging < old_dumpfilename.sql

So, you now have the current and old databases on the current server, BUT in different schemas.

Sixth step - Your main worry now is how to resolve any conflicts of PRIMARY KEYs.

You can check this by running

SELECT staging.old_table_PK from staging.old_staging_table_name
WHERE staging.old_table_PK
IN (SELECT current.current_table_PK FROM current.curent_table_name);

You need to specify the schema name because you are running cross schema queries. The above query will give you any records in both tables which have the same PRIMARY KEY. It will be up to you to resolve any conflicts using your system's logic.

Perform this query for all tables in your current database.

Seventh step - If there are no conflicts, then perform

INSERT INTO current.current_table_name SELECT * from staging.staging_table_name;

This should get you most of the way there. There may be typos in my answer, since I didn't have suitable data to perform a large scale check - but you can do any necessary testing yourself.

REMEMBER - BACKUP before you MESSUP!


DB2 developers can't create table in own schema but SQL and Oracle devs can. Justified?

They probably do this because storage allocation on the mainframe is much more complex than for unix or windows platforms.

zOS is basically a very modern sophisticated piece of harware and operating system that is emulating a 1960s mainframe. In much the same ways as the latest Intel chips are emulating a 1980s 386 chip.

The problem is that the very low level close to the wire disk I/O which was necessary to get high bandwidth and and quick access times on the original hardware is "baked in" to the OS and many applications. So all disk storage for legacy apps deals with channels and volumes. A volume is a "pretend" 3330 disk with 8GB or 32GB of storage usually allocated as a piece of a real nTerabyte raid system.

To define tables properly in zOS DB2 you need to create tables spaces which in turn requires you to allocate storage on specific volumes this is fairly messy as four or five entities need to be linked together TABLE -> TABLESPACE -> Volume Group -> Disk Volumes + Index -> IndexTablespace -> Volume Group -> Disk Volumes.

Basically your DBAs are saving you a lot of grief by not letting you play with the matches.

P.S. If you use the modern half of zOS (Unix Systems Services) this all becomes irrelevant but it's going to be a long slow migration!


information_schema is full of NULLs after database import

mysql doesn't find a LEFT JOIN with both tables.
You can use:
SELECT kcu.*,tc.* FROM information_schema.KEY_COLUMN_USAGE kcu
LEFT JOIN  information_schema.TABLE_CONSTRAINTS tc ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY'
AND tc.TABLE_SCHEMA = 'testdb';

SELECT kcu.*,tc.* FROM information_schema.TABLE_CONSTRAINTS tc
RIGHT JOIN information_schema.KEY_COLUMN_USAGE kcu ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY'
AND tc.TABLE_SCHEMA = 'testdb'

I think it is a bug, which you can report, or else tell me and i report it


How to implement Sql Server 2016 mobile publisher on existing Report Server

SQL Server Mobile Reports (nee Datazen) is only available on SSRS 2016.


Difference between backup methods: Oracle and MySQL

Because MySQL is not as mature.

But there are options, such as Percona's (free) hot backup for innodb:

http://www.percona.com/software/percona-xtrabackup/

There is also MySQL Enterprise Backup from Oracle, but it is not free.


How to find givien index by name in PostgreSQL

As a horse_with_no_name provided, you can search the pg_indexes meta-table to find an index by name with the query:
SELECT *
FROM pg_indexes
WHERE indexname LIKE '%NameOfIndexYoureSearchingFor%';

Regarding your second question in the comments on how to delete an index in PostgreSQL, you can script it out with a query like this:
DROP INDEX NameOfIndexYouWantToDrop;

PostgreSQL documentation on DROP INDEX for more information.


Oracle symmetrical one-way replication for cheap HA: unfashionable or technically unfeasible?

These days, Data Guard is included with Enterprise Edition.If you have enough spare capacity on each of the servers, I believe they can work as a physical standby for each other.

Logical and possibly bi-directional replication is more complex, and you would have to consider the types of objects and replication in the solution. Most solutions does not easily support the replication of DML etc.

Note that Data Guard configured as physical standby might still consume a considerable amount of resources and depending on the configuration, may impact on production performance on both servers.


The execution plan is likely to be changing.

Grab a copy of a fast plan and a slow plan and compare.

By using a plan guide you may be able to force the query to use the one plan for all occasions (after testing of course).


This behavior (allowing NULL values in FK columns) conforms to the SQL standard. The manual:

  The CREATE TABLE command conforms to the SQL standard, with exceptions listed below.

And there is no exception listed for FK constraints.

To disallow that some columns of a multi-column FK are NULL, use MATCH FULL instead of the default MATCH SIMPLE behavior. The manual:

  MATCH FULL will not allow one column of a multicolumn foreign key to
  be null unless all foreign key columns are null; if they are all null,
  the row is not required to have a match in the referenced table.

Or just define some or all column NOT NULL. More here (as @ypercube commented):

MATCH FULL vs MATCH SIMPLE in foreign key constraints


Reuse alias that shares an existing column name

For that you need user defined variables

SELECT
  item_id,
  @sum1 := value_a + value_b as value_sum,
  CONCAT('VALUE SUM: ', @sum1 + 5) as label,
  @sum2 := value_a + value_b as value_sum2,
  CONCAT('VALUE SUM: ', @sum2) as label2
 FROM item i
   LEFT JOIN item_sums isu USING (item_id);

Result
item_id     value_sum   label           value_sum2  label2
1           4           VALUE SUM: 9    4           VALUE SUM: 4
2           5           VALUE SUM: 10   5           VALUE SUM: 5
3           11          VALUE SUM: 16   11          VALUE SUM: 11

sample
  SELECT
      item_id,
      value_sum,
      CONCAT('VALUE SUM: ', value_sum + 5) as label,
      value_sum2,
      CONCAT('VALUE SUM: ', value_sum2) as label2
  FROM
  (SELECT
      item_id,
      value_a + value_b as value_sum,
      value_a + value_b as value_sum2
   FROM item i
       LEFT JOIN item_sums isu USING (item_id)) t1;

sample2


Do too many subquries create multiple sleeping processes in MySQL?

One connection = one process (or thread).  Period.  Subqueries, UNIONs, and PARTITIONing do not create subprocesses.

What causes Sleeping processes:

Connections that are busy with non-SQL work.  (Usually 'Time' is 0 or small.)
Connections that don't go away.
Connection pooling at the client.

Sleeping processes are relatively harmless.  No need to kill them.  All connections (sleeping or not) are capped at max_connections, after which, trying to get a new connection gets an error.

On the other hand, sometimes the client(s) have parameters that lead to an unreasonable number of connections.  Apache has what used to be called MaxClients, which (in my opinion) should not be set higher than 20.


AlwaysOn Availability Groups Dev, QA, and Staging Environments

It's a balancing act between risk and cost. The least risk highest cost is to have the exact same everything between production and your development environments (Dev, QA, Staging, Whatever you want to call them). Everything the same means same hardware, same san, same network, same QoS (if in place), same everything.

The highest risk, lowest cost is to have the minimum possible for dev (less environments (or no environments, I've seen developers just use production), less hardware, no replication, etc). and have an educated guess for what will happen in production.

Risks involve things like how long will it take for a task to finish on live AND replicate, what does that do to the log file? Will this script work in an availability group? Can it complete in the time required for a maintenance window? Will the replication complete within a maintenance window? What happens when we do Windows Patching?

Costs involve hardware, but also time. A lot of companies have some sort of process to restore (and sanitise) data from production back to dev/test environments. How long will it take to restore back from a live environment to a dev environment (assuming you do)? How does that process work? How is it different between a single instance dev environment and an AAG environment?
 What's the cost in maintaining and developing against multiple AAGs?

As you can see there are many different things going in to your question, and it's not a one case fits all scenario. The less your dev environment looks like live, the cheaper it is to build and maintain, but the risk is you don't know what is going to happen when something goes live.

My preference is to always have 1 environment that replicates live as close as possible, and that everything you do on live is tested there first. From there you need to work out the risk/cost ratio, or to put it another way, how much is the business willing to spend to reduce it's risk, and what risk is acceptable (there's always risk).


Since you've got many tables with varying numbers of columns, #1 would be out since you'd have a massive table with the aggregate of all your columns, and lots of nulls.

Between #2 & #3 I think you have a decision to make regarding the design complexity you want to manage. My view is that it would be easier to maintain an exact archive replica for a given table, and store the whole rowstate (with modified time). Think of a case where you update more than one column of a row. In that case #2 would log an entry for the change of the columns separately, even though it was the same transaction. I'd go w #3 for reducing complexity, and capturing point in time row state.


Constraint for a unique column if other column has same value

If I've understood you correctly, what you want is something like this:

CREATE TABLE Example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    UNIQUE KEY uix_name_example (name)
);

The id field is the PRIMARY KEY and its value is taken care of by the server. A UNIQUE KEY on name ensures no duplicates are allowed - and NOT NULL ensures (hopefully) meaningful values.

INSERT INTO Example (name) VALUES ('Peter Parker'); -- specify name, but not id - it's automatic
INSERT INTO Example (name) VALUES ('Billy Joe McAlister');
INSERT INTO Example (name) VALUES ('Bill Clinton');

Check your table:

mysql> SELECT * FROM Example;
+----+---------------------+
| id | name                |
+----+---------------------+
|  3 | Bill Clinton        |
|  2 | Billy Joe McAlister |
|  1 | Peter Parker        |
+----+---------------------+
3 rows in set (0.03 sec)

A subsequent attempt to INSERT the same name (Peter Parker) fails.

mysql> INSERT INTO Example (name) VALUES('Peter Parker');
ERROR 1062 (23000): Duplicate entry 'Peter Parker' for key 'uix_name_example'
mysql>

If you want to allow people with the same name, but different ids, then you just leave out the UNIQUE KEY uix_name_example (name) line in the table definition.

mysql> INSERT INTO Example VALUES(1, 'Peter Parker');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql>

If this isn't a satisfactory explanation, please expand your question.

(EDIT in response to OP's comment and clarification)

What you want is a UNIQUE KEY (or INDEX) on the combined id and name fields - also known as a composite index (or key). The syntax for this is as follows:

CREATE TABLE Example2 (
    id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    UNIQUE KEY uix_example (id, name)
);

Duplicate ids with different names allowed to be inserted.

mysql> INSERT INTO Example2 VALUES(1, 'Peter Parker');
Query OK, 1 row affected (0.12 sec)
mysql> INSERT INTO Example2 VALUES(1, 'Billy Power');
Query OK, 1 row affected (0.08 sec)

Same name allowed to be inserted with different ids

mysql> INSERT INTO Example2 VALUES(1, 'Mary Hanlon');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO Example2 VALUES(2, 'Mary Hanlon');
Query OK, 1 row affected (0.06 sec)

But, what is not allowed is to insert same id and same name.

mysql> INSERT INTO Example2 VALUES(1, 'Peter Parker');
ERROR 1062 (23000): Duplicate entry '1-Peter Parker' for key 'uix_example'
mysql>

Hopefully this resolves your question.


Short version: seek is much better

Less short version: seek is generally much better, but a great many seeks (caused by bad query design with nasty correlated sub-queries for instance, or because you are making many queries in a cursor operation or other loop) can be worse than a scan, especially if your query may end up returning data from most of the rows in the affected table.

It helps to cover the whole family for data finding operations to fully understand the performance implications.

Table Scans: With no indexes at all that are relevant to your query the planner is forced to use a table scan meaning that every row is looked at. This can result in every page relating to the table's data being read from disk which is often the worst case. Note that for some queries it will use a table scan even when a useful index is present - this is usually because the data in the table is so small that it is more hassle to traverse the indexes (if this is the case you would expect the plan to change as the data grows, assuming the selectivity measure of the index is good).

Index Scans with Row Lookups: With no index that can be directly used for a seek is found but an index containing the right columns is present an index scan may be used. For instance, if you have a large table with 20 columns with an index on column1,col2,col3 and you issue SELECT col4 FROM exampletable WHERE col2=616, in this case scanning the index to query col2 is better than scanning the whole table. Once matching rows are found then the data pages need to be read to pickup col4 for output (or further joining) which is what the "bookmark lookup" stage is when you see it in query plans.

Index Scans without Row Lookups: If the above example was SELECT col1, col2, col3 FROM exampletable WHERE col2=616 then the extra effort to read data pages is not needed: once index rows matching col2=616 are found all the requested data is known. This is why you sometimes see columns that will never be searched on, but are likely to be requested for output, added to the end of indexes - it can save row lookups. When adding columns to an index for this reason and this reason only, add them with the INCLUDE clause to tell the engine that it doesn't need to optimise index layout for querying based on these columns (this can speed up updates made to those columns). Index scans can result from queries with no filtering clauses too: SELECT col2 FROM exampletable will scan this example index instead of the table pages.

Index Seeks (with or without row lookups): In a seek not all of the index is considered. For the query SELECT * FROM exampletable WHERE c1 BETWEEN 1234 AND 4567 the query engine can find the first row that will match by doing a tree-based search on the index on c1 then it can navigate the index in order until it gets to the end of the range (this is the same with a query for c1=1234 as there could be many rows matching the condition even for an = operation). This means that only relevant index pages (plus a few needed for the initial search) need to be read instead of every page in the index (or table).

Clustered Indexes: With a clustered index the table data is stored in the leaf nodes of that index instead of being in a separate heap structure. This means that there will never need to be any extra row lookups after finding rows using that index no matter what columns are needed [unless you have off-page data like TEXT columns or VARCHAR(MAX) columns containing long data].

You can only have one clustered index for this reason[1], the clustered index is your table instead of having a separate heap structure, so if you use one[2] chose where you put it carefully in order to get maximum gain.

Also note that the clustered index because the "clustering key" for the table and is included in every non-clustered index on the table, so a wide clustered index is generally not a good idea.

[1] Actually, you can effectively have multiple clustered indexes by defining non-clustered indexes that cover or include every column on the table, but this is likely to be wasteful of space has a write performance impact so if you consider doing it make sure you really need to.

[2] When I say "if you use a clustered index", do note that it is generally recommended that you do have one on each table. There are exceptions as with all rules-of-thumb, tables that see little other than bulk inserts and unordered reads (staging tables for ETL processes perhaps) being the most common counter example.

Additional point: Incomplete Scans:

It is important to remember that depending on the rest of the query a table/index scan may not actually scan the whole table - if the logic allows the query plan may be able to cause it to abort early. The simplest example of this is SELECT TOP(1) * FROM HugeTable - if you look at the query plan for that you'll see that only one row was returned from the scan and if you watch the IO statistics (SET STATISTICS IO ON; SELECT TOP(1) * FROM HugeTable) you'll see that it only read a very small number of pages (perhaps just one).

The same can happen if the predicate of a WHERE or JOIN ... ON clause can be run concurrently with the scan that is the source if its data. The query planner/runner can sometimes be very clever about pushing predicates back towards the data sources to allow early termination of scans in this way (and sometimes you can be clever in rearranging queries to help it do so!). While the data flows right-to-left as per the arrows in the standard query plan display, the logic runs left-to-right and each step (right-to-left) is not necessarily run to completion before the next can start. In the simple example above if you look at each block in the query plan as an agent the SELECT agent asks the TOP agent for a row which in turn asks the TABLE SCAN agent for one, then the SELECT agent asks for another but the TOP agent knows there is no need doesn't bother to even ask the table reader, the SELECT agent gets a "no more is relevant" response and knows all the work is done. Many operations block this sort of optimisation of course so often in more complicated examples a table/index scan really does read every row, but be careful not to jump to the conclusion that any scan must be an expensive operation.


Best approach instead of granting permissions all the time to logins

Is there a way to create a single role, where I can only crate the
  role, and assign the users mapped to this login, to the role?

Yes you can do exactly as you said, create a role, add it as a member to the roles you are interested in and grant it permissions the same way you did it with a single user:

create role MyCustomRole;

ALTER ROLE [db_datareader] ADD MEMBER MyCustomRole--[User_Customer] --DR
GO
ALTER ROLE [db_datawriter] ADD MEMBER MyCustomRole--[User_Customer] --DW
GO
ALTER ROLE [db_ddladmin]   ADD MEMBER MyCustomRole--[User_Customer] --DDL
GO
GRANT EXECUTE TO MyCustomRole--[User_Customer] --EXECUTE
GO

Now you can add your users to this role:

alter role MyCustomRole add member [User_Customer];

To control what permissions your user has now just impersonate it:

execute as user = 'User_Customer';

select *
from sys.fn_my_permissions(null, 'database');

revert;

One tip for SQL Server 2014:

I saw you want to deny view any database to your logins.

After such a deny your logins will not see databases in Object Explorer, I mean all the databases included those where they are mapped to.

I think it will render their work more complicated. I don't know what exactly reasons led you to this decision, but if you really need it maybe Contained databases appeared in 2012 is the solution for you.

When you make a database contained, it's database itself that authenticates your users.
This way you do not need logins anymore, every user can be created in its own database directly and will "see" only that database in Object Explorer.

More on contained databases here:
SQL Server 2012 Contained Database Feature


type: index means it's an index scan. That is, it's scanning through an entire index of that table.

An index scan often goes along with Using index because the latter indicates that the query is able to use the index to satisfy the query, without touching the rows of the table. Using index would be more clearly labeled Using only index.

There are as many entries in an index as there are rows in the table, therefore the rows reported by an index-scan is the same number as rows reported during a table-scan.

The number reported by rows is an estimate, based on InnoDB's statistics collected about the table. It can be inaccurate by 10% or even more. Don't rely on this being a precise number, but treat it as an indicator in the order of magnitude as the true number of rows (or index entries).

You may have no secondary indexes on your tags table, but you have a primary key, which implicitly has an index. The type: eq_ref indicates that the query is using the primary key to find the respective row during the join. Lookups by primary key are good.

To read more about the EXPLAIN output, read: http://dev.mysql.com/doc/refman/5.6/en/explain-output.html


If indeed it is only one page that is affected, you should be able to perform a page level restore to get the database back. Full details are here:
http://www.sqlpassion.at/archive/2015/10/13/how-to-perform-a-page-level-restore-in-sql-server/

This does assume that you have a recent full backup of the database.


MySQL - Problems caused by SQL Security definer or invoker

i got same issue
on my case, my resolution re-create the view with change this part from
CREATE ALGORITHM=UNDEFINED DEFINER=`sst_user`@`10.148.0.0/255.255.240.0` SQL SECURITY DEFINER

to this
CREATE SQL SECURITY INVOKER VIEW

and test select the view from user.


How full is my innodb shared table space?

It's in ibdata1?  How much free space do you currently have?  What is the output of

 SHOW TABLE STATUS;

And

 SHOW CREATE TABLE  -- for the largest table

Is this 'your' machine?  Or a Cloud server?  Do you have access to another machine with a big disk?

Knowing how "full it is" won't necessarily help you avoid running out of disk space.

With answers to the above questions, we may be able to plot a path that does not end in disaster.


Can MySQL handle such big size database and can full text search really work for me for such
  big database

What is big?

SERIOUSLY.

Let's have a look at modern hardware. Databases typcially are large when they are larger than the available RAM.

100gb - hm.

128gb memory was not particularly large last year.

Get a decent modern XEON, put 256GB memory into it.

Just checking - supermicro.com, website has a "featured motherboard". "Supports up to 256 GB DDR3 ECC". Ok, 128gb won't be that expensive.

I have a 64gb database server here that is 5 years old. I have a desktop server that has 64gb memory (micro atx board). Last time I checked, lower mid range servers (two sockets) go to 512gb memory.

That means that by all accounts, 100gb is not large.


Have you tried:

select Name, max(Mark) AS Mark
from Students
group by Name


In order to clean up files in Azure blob storage requires PowerShell or going through some app like Azure Explorer. So it is likely that Ola's backup script does not support it since there is no native T-SQL command or procedure to access blob storage in that manner.

When you are deleting files you will need to account for any that may still have an active lease. BOL has code to handle dropping the active leases.


How to include a semicolon (";") in a MySQL pager command

Try to set delimiter to something else than ;

     mysql> DELIMITER endl
     mysql>

Then you can set pager with the semicolon:

     mysql> pager pager perl -nle 'print "    $_";'endl
     PAGER set to 'pager perl -nle 'print "    $_";''

Then you can come back to ;

     mysql> DELIMITER ;


Can I retrieve all database objects owned by a particular user?

This should get you what you're looking for:

;with objects_cte as
(
    select
        o.name,
        o.type_desc,
        case
            when o.principal_id is null then s.principal_id
            else o.principal_id
        end as principal_id
    from sys.objects o
    inner join sys.schemas s
    on o.schema_id = s.schema_id
    where o.is_ms_shipped = 0
    and o.type in ('U', 'FN', 'FS', 'FT', 'IF', 'P', 'PC', 'TA', 'TF', 'TR', 'V')
)
select
    cte.name,
    cte.type_desc,
    dp.name
from objects_cte cte
inner join sys.database_principals dp
on cte.principal_id = dp.principal_id
where dp.name = 'YourUser';

This will get objects that are owned by your particular user (substitute 'YourUser' of course).  The types of objects this query pulls are:

FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
P = SQL Stored Procedure
PC = Assembly (CLR) stored-procedure
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function
TR = SQL DML trigger
U = Table (user-defined)
V = View


Cannot insert the value NULL into column

I didn't take a really hard look at this but you're defining the DBName, UserName, and LoginType columns in @DB_USers as sysname. The type sysname is defined as NOT NULL.

What is SYSNAME data type in SQL Server?

As to why you're getting a NULL value you're going to have to check the dataset you're running against.


Binary logs have different start positions upon the creation of a new binary log. In fact, a binary log position actual represents the numbers of bytes written in the binary at the end of the most recent SQL statement recorded.

The following is the list of binary log starting positions for each major release of MySQL 5

For MySQL 5.5 : 120
For MySQL 5.5 : 107
For MySQL 5.1 : 106
For MySQL 5.0 : 98

Included in these initial bytes is a static value called the magic number, a base-64 value. I wrote about this back on Dec 26, 2011 : mysql emits BINLOG rows even though binlog_format=STATEMENT

Although the BINLOG magic number is always in the same position, you cannot use binary logs from one version to newer versions under certain circumstances. For example, binary logging events could change and not be understood. Row-based binary logging and MySQL Replication can break on a MySQL 5.6 Slave replicating from an older master when handling DATETIME values. I wrote about this recently

Jun 18, 2015 : mysql master slave replication scenario
Jan 02, 2015 : mysql replication master 5.5 slave 5.1 error on create database

There is also the possibility of a corrupt binary log. I wrote about this on Feb 04, 2011 : MySQL master binlog corruption

Your solution is to

net stop mysql
Erase binary logs (and the binary log index file)

IF you do not know where they are, locate my.ini on your PC
Open my.ini in Notepad
look for the option log-bin or log_bin
look for the option datadir
If log-bin only has a filename, look inside the folder specified by datadir
If log-bin includes a path and a filename, look inside the folder specified by log-bin
Open the desired folder in Windows Expolrer
Remove the binary logs
There should be a file whose file extension is .index. Delete this as well

net start mysql

Please DO NOT ERASE ib_logfile0 or ib_logfile1 when you have binary log issues.


Get a parent and the children using the parent name

You can either adjust your join condition
SELECT child.id
  FROM servers AS parent
  LEFT JOIN servers AS child
    ON (child.parent_id = parent.id
     OR child.id = parent.id)
  WHERE parent.name = 'kvm01';

or use UNION / UNION ALL to retrieve the parent record separately:
SELECT child.id
  FROM servers AS parent
  LEFT JOIN servers AS child
    ON child.parent_id = parent.id
  WHERE parent.name = 'kvm01'
UNION ALL
SELECT parent.id
  FROM servers AS parent
  WHERE parent.name = 'kvm01';


How to create projection for proper using of UPPER function?

Here is small example of how you can improve the query speed.

One way i do it is by using bucket columns based on default value at load time.(this can make your load slower thou).

see sample code

drop table test_tbl;
CREATE TABLE
    test_tbl
    (
        id INT,
        name VARCHAR(20),
        bkt_col int default(
        CASE
            WHEN name=upper(name)
            THEN 1
            WHEN name!=upper(name)
            THEN 0
        END)
    );

Create the projection to attend you predicate, segmented on replicated is up to you table type/size

CREATE PROJECTION public.test_tbl
(
 id,
 name,
 bkt_col
)
AS
 SELECT test_tbl.id,
        test_tbl.name,
        test_tbl.bkt_col
 FROM public.test_tbl
 ORDER BY test_tbl.bkt_col,
          test_tbl.id,
          test_tbl.name
SEGMENTED BY hash(test_tbl.bkt_col, test_tbl.id, test_tbl.name) ALL NODES KSAFE 1;

Insert some data

insert into  test_tbl(id,name)  values (1,'BOB') ;
insert into  test_tbl(id,name)  values (1,'bob') ;
id  name  bkt_col
--  ----  -------
1   bob   0
1   BOB   1

To query it use this sql, this way you avoid upper at run time and your stats will be well used.

SELECT
    *
FROM
    test_tbl where bkt_col = 1 and name IN ('BOB')


why when i run my sql code, the terminal stop on some wired number?

Your code above does not create the trigger, it just puts it to the SQL buffer, because this is how SQL*Plus works. One side effect of this is regular SQL statements not executing, or not getting the prompt back. Use a slash / after the END TRIGGER01; clause:

CREATE TRIGGER TRIGGER01
AFTER INSERT ON WAREHOUSE
REFERENCING NEW AS NEWWAREHOUSE
FOR EACH ROW
WHEN (NEWWAREHOUSE.STATE = 'MAINE')
BEGIN
INSERT INTO WAREHOUSE_MAINE VALUES(:NEWWAREHOUSE.SHIPPEREIN,:NEWWAREHOUSE.WAREHOUSENAME);
END TRIGGER01;
/
INSERT INTO WAREHOUSE VALUES('108-34-9999', 'MoonHouse', 'MAINE', 'kapa');
SELECT * FROM WAREHOUSE_MAINE;


The software isn't on edelivery.oracle.com either, as Oracle removed 12.1.0.1 as soon as 12.1.0.2 was released. Almost as if 12.1.0.1 was a stop-gap release.

You'll have to open a support ticket with Oracle, asking for it.

12.1.0.2 was a significant bugfix release, as well as adding lots of new features (JSON storage, rapid home provisioning) - upgrade to it ASAP if you've already got 12c in production.


Is it possible to backup my ubuntu postgresql database by copying the /var/lib/postgresql/9.1/main file and pasting it back in?

Yes, as long as you install version 9.1 (minor version doesn't matter). And normally you don't need to copy/move them anywhere, the installation will notice there is already a database system set up and will use it. Of course taking a backup is never a bad idea.

Then again, the reason for it being offline should be clear from PostgreSQL logs. Memory allocations can be too high, or some other thing, and this is reported in the log.


Postgresql filtering by value over rolling time period

Schematically:

SELECT DISTINCT t1.customer
FROM test t1, test t2
WHERE t2.purshase_ts BETWEEN t1.purshase_ts AND t1.purshase_ts + INTERVAL 4 minute
  AND t1.customer = t2.customer
GROUP BY t1.customer, t1.purshase_ts
HAVING SUM(CASE WHEN t2.amount > 2 THEN 1 ELSE 0 END) = COUNT(t2.amount)

or

SELECT DISTINCT t1.customer
FROM test t1
WHERE NOT EXISTS (SELECT 1
                  FROM test t2
                  WHERE t2.purshase_ts BETWEEN t1.purshase_ts AND t1.purshase_ts + INTERVAL 4 minute
                    AND t1.customer = t2.customer
                    AND t2.amount <= 2)

If time roll is to be "full", use 1st query adding relevant restriction:

SELECT DISTINCT t1.customer
FROM test t1, test t2
WHERE t2.purshase_ts BETWEEN t1.purshase_ts AND t1.purshase_ts + INTERVAL '4 minute'
  AND t1.customer = t2.customer
GROUP BY t1.customer, t1.purshase_ts
HAVING SUM(CASE WHEN t2.amount > 2 THEN 1 ELSE 0 END) = COUNT(t2.amount)
   AND COUNT(t2.amount) = 5

or

SELECT DISTINCT t1.customer
FROM test t1, test t2
WHERE t2.purshase_ts BETWEEN t1.purshase_ts AND t1.purshase_ts + INTERVAL '4 minute'
  AND t1.customer = t2.customer
GROUP BY t1.customer, t1.purshase_ts
HAVING SUM(CASE WHEN t2.amount > 2 THEN 1 ELSE 0 END) = COUNT(t2.amount)
   AND MAX(t2.purshase_ts) - MIN(t2.purshase_ts) = INTERVAL '4 MINUTE'

fiddle


It was because of Commit while using  Cursor.

Adding WITH HOLD to the cursor definition resolved the Issue.


Health checking via Database Mail in SQL Server

This is more just a note on the philosophy rather than the technique.

If you did this, I would make sure to set some limits on it so that it only sent out emails when you were outside those limits.

For example, if you're CPU is spiking to 100% frequently, I would send out the email.  However, if there's no problems with the CPU, I would explicitly, intentionally not send the email.

If you get a daily email from your database, you're going to start ignoring them after a while.  But if you only get them when there's something that you think you should check for, you're more likely to look at the emails.

Also, I'd make those limits configurable (and possibly even make flags so that specific queries can be turned on or off).

Regarding your question of multiple selects inside a single email, it sounds like there are two options:

Get the results for each query and format them into a string that contains everything you want.
Merge all the different results into a single query and then set your @query to that. (Alternatively, sticking all the results into a temporary table and querying the temp table.)

It is possible, I should note, to stick a query in the @query as well as sticking the results of a second query in the @body (per here and here), but that only allows you two statements.

I would try option one inside of a stored procedure.  The trick would be formatting the table into the string.  However, it's the most likely to work correctly.


Can I add new tables to a transactional replication subscriber

if it's possible to create new stored procedures and tables on the
  reporting database which will not be dropped as part of the
  replication sync?

Yes, you can create new stored procedures and tables on the reporting database.

When creating a publication, you choose the tables and other database objects that you want to publish. Objects that are included in the publications get replicated. You are free to add other objects in the subscriber database.

Read this document which details about publication and limitations:
Publish Data and Database Objects


Generally when someone wants to create a user to run an application, but not own the tables, they create a group that has select on the necessary tables and views and execute on the necessary stored procedures. They can then either create synonyms for the objects that they need or create public synonyms for the objects that everyone needs. You might want to have a separate roll for admin users compared with regular users.

Once you do that then when you create a user they would need connect or create session and the role that was created for all users. If they have temporary tables they either need the create table privilege or to just have the tables created for them.

Generally giving each user a separate login directly to the database is something that was done with client server apps. Most web based apps handle the logins on the app server and don't give direct access to the database for users. They just pool the connections and have business logic to control which user can use which function. You may want to rethink why you are creating individual users directly in the database.


Transfer rows into a new table, forcing to generate unique primary keys till its insertable

use a BEFORE INSERTTRIGGGER to generate UNIQUE numbers.
With SET @NEW_ID = 1; you define the first new value
Schema (MySQL v8.0)
CREATE table playerinventory ( id int PRIMARY KEY ,    amount INT , level int);
CREATE table newinventory ( id bigint PRIMARY KEY ,    amount INT , level int);

INSERT INTO playerinventory VALUEs (5,1,1),(6,2,2),(7,3,3),(8,4,4);

DELIMITER $$
CREATE TRIGGER before_newinventory_insert
BEFORE INSERT
ON newinventory FOR EACH ROW
BEGIN

    SELECT MAX(id) + 1
    INTO @NEW_ID
    FROM newinventory;
    IF @NEW_ID IS NULL THEN
        SET @NEW_ID = 1;
    END IF;
    set NEW.id = @NEw_ID;
END $$

DELIMITER ;

INSERT INTO newinventory SELECT * FROM playerinventory;

Query #1
SELECT * FROM newinventory;

| id  | amount | level |
| --- | ------ | ----- |
| 1   | 1      | 1     |
| 2   | 2      | 2     |
| 3   | 3      | 3     |
| 4   | 4      | 4     |

View on DB Fiddle


Dynamic memory with SQL is good for letting the memory expand as required and taking the pressure off the disk subsystem as SQL can hold more in memory.  It's a bit of a swizz though in the real world as you are supposed to set the "Lock Pages In Memory" (LPIM) setting to stop the OS from taking memory back from SQL, which can be bad, so you end up creating a one-way street.  You set the max server memory setting (in SQL) to be higher than the startup memory (Hyper-V setting), ideally you match it to the Max mem setting (Hyper-V) of the VM.  Thus SQL starts and takes whatever you set at startup and then it grows over time to reach the maximum setting.  The thing is it always does this.  It's not like a safety net, having the extra headroom "just in case" SQL needs it, it will always take it eventually and then because of LPIM it won't give it back.  You might as well have set the memory to static at the max memory level because that's what it's going to get anyway.

I'm still not sure of the benefit of Dynamic memory for SQL and I've read a lot of white papers and articles, everyone affiliated with MS seems to recommend it but with LPIM and my own real-world observations, I can't see the point of it.  The behaviour with SQL is not "dynamic" it just takes and keeps.


Unexpected query execution plan on MySQL 8 when using Datetime range

For a closer analysis we would need the table definitions for both tables and indexes as well as the row counts, but I think the problem is that you use key2 for joining and mysql can then not use it for filtering of the datetime (since the first part of the index, the key2, is different for the rows in the join result).

Can you try to create an index on datetime alone? Maybe optimize the query like this afterwards:

select sub.name
from A join
(select name,key1,key2 from B where B.datetime between '2020-05-28 00:00:00' AND '2020-05-29 00:00:00') sub
on A.key1 = sub.key1 and A.key2 = sub.key2
where A.active = True
and A.type = 1;

This way it could use the most restrictive filtering first (which I assume will be the datetime column on table B) and join the few rows it has with the many in table A.


mariadb Unknown system variable 'server_uuid'

MariaDB doesn't have that variable, MySQL has. MariaDB is not a drop-in replacement for MySQL. The error you see makes me think that replication from 10.1 to 5.7 is not possible.

One could investigate more, but take a look at this compatibility table: there is not an explicit incompatibility statement, but at least they don't guarantee that replicating from 10.1 to 5.7 is possible.

Even if you choose to assume that such replication is possible and find a way to set it up, MySQL supports some syntax that MariaDB 10.1 does not support - not because it's more advanced, but because they took different directions. Such statements will break replication.


How to decrese MySQL Innodb crash recovery time (force MySQL to do it faster)

First of all there is a documented bug that has seen the recovery performance of 5.7 degrade, this is discussed here https://bugs.mysql.com/bug.php?id=80788 which seems to have been fixed in 5.7.19

Otherwise, these suggestions might help:

https://www.percona.com/blog/2016/06/07/severe-performance-regression-mysql-5-7-crash-recovery/

https://www.percona.com/blog/2014/12/24/innodb-crash-recovery-speed-mysql-5-6/

Although these are on the Percona blog it is not specific to Percona. There are other performance suggestions on the blog, but as you already realise these largely relate to the setting of innodb_log_file_size

Disclosure: I work for Percona.


The index won't show up if you haven't touched it since you started your SQL Server. By touched I mean seeked, scanned, or modified.


How to get Postgres database name at Linux terminal

You can use:

psql -l

This requires a login but you can use the same credentials as you would for connecting to the target database


How to implement if (t1.ID=t2.ID) returned nothing do the stuff

From what I understand you want a LEFT JOIN (it really helps if definitions of tables, sample data and expected results is included in the question):

SELECT t2.id, coalesce(t1.name, '---') as name
     , CASE WHEN t2.id = t1.id THEN 'EQUAL' ELSE 'NOT EQUAL' END
FROM t2
LEFT JOIN t1
    ON t2.id = t1.id

coalesce returns the leftmost column that is not null, in this case t1.name if it's not null and --- otherwise. If you want to avoid a join (not sure why) you can use a sub select:

SELECT t2.id, coalesce((SELECT t1.name
                        FROM t1
                        WHERE t2.id = t1.id), '---') as name
FROM t2;


sql agent job hangs without any errors or warnings

I have a similar problem in SQL Server 2008. We have hourly schedule which pulls data from multiple Sybase Adaptive and Sybase IQ servers and populates the SQL Server (Using System DSN, Linked Server). Every Friday after 2 AM those jobs hang. After a little bit of research on my Windows Server which has the SQL Server I found that the McAfee weekly schedule runs every Friday from 2 AM.

As a short term solution, the anti virus scan can be cancelled and restart the SQL Server Service to clear the hang.

For long term, our security team has advised us to remove McAfee and load Kaspersky.

A note here.. during the anti virus scan only those jobs hang which pulls data from Sybase (as mentioned above), all other jobs will run perfectly fine.


Your example query appears to be incomplete (e.g. your aliases t and cl don't correspond to anything in the example). Ignoring that for a second and using your example query, this is probably what you're after:
SELECT t.status_id AS status_id, cl.category_code + '-' + s.status_name AS status_type
FROM SomeTable AS t
LEFT JOIN firm_return AS s
    ON t.firm_id = s.firm_id
    AND s.enabled = 'D'
... -- The rest of your query logic here (e.g. the table for the cl alias)

If you care about NULLS and want to handle them with a default value then you can use the ISNULL() function around the logic for your status_type column like so: ISNULL(cl.category_code + '-' + s.status_name, 'SomeDefaultValueGoesHere') AS status_type


Tables with MD_ prefix were found in my Oracle DB - is there any info about them?

These tables use for Migration workbench.
Please see this link for more information