Why default character_set_server is latin1? I am using MySQL 5.5 and when I show variables about charset, I have

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

Should I need to change the character_set_database and character_set_server to utf8?

Think about it:

You are storing data in the database as latin1
You are data is handled internally by mysqld as latin1

If data coming from the OS or from the connection is utf8, how is mysqld going to treat it?
Rather than guessing or hoping for the best, you could change the incoming character set behavior. With the exception of information_schema and mysql, take all your databases and set the default character set to utf8:
ALTER DATABASE dbname CHARACTER SET utf8;

If you have a specific colllation to go with it, do this:
ALTER DATABASE dbname COLLATE 'utf8_general_ci';

Here are the collations to choose from:
mysql> select * from information_schema.collations where CHARACTER_SET_NAME = 'utf8';
+--------------------+--------------------+-----+------------+-------------+---------+
| COLLATION_NAME     | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN |
+--------------------+--------------------+-----+------------+-------------+---------+
| utf8_general_ci    | utf8               |  33 | Yes        | Yes         |       1 |
| utf8_bin           | utf8               |  83 |            | Yes         |       1 |
| utf8_unicode_ci    | utf8               | 192 |            | Yes         |       8 |
| utf8_icelandic_ci  | utf8               | 193 |            | Yes         |       8 |
| utf8_latvian_ci    | utf8               | 194 |            | Yes         |       8 |
| utf8_romanian_ci   | utf8               | 195 |            | Yes         |       8 |
| utf8_slovenian_ci  | utf8               | 196 |            | Yes         |       8 |
| utf8_polish_ci     | utf8               | 197 |            | Yes         |       8 |
| utf8_estonian_ci   | utf8               | 198 |            | Yes         |       8 |
| utf8_spanish_ci    | utf8               | 199 |            | Yes         |       8 |
| utf8_swedish_ci    | utf8               | 200 |            | Yes         |       8 |
| utf8_turkish_ci    | utf8               | 201 |            | Yes         |       8 |
| utf8_czech_ci      | utf8               | 202 |            | Yes         |       8 |
| utf8_danish_ci     | utf8               | 203 |            | Yes         |       8 |
| utf8_lithuanian_ci | utf8               | 204 |            | Yes         |       8 |
| utf8_slovak_ci     | utf8               | 205 |            | Yes         |       8 |
| utf8_spanish2_ci   | utf8               | 206 |            | Yes         |       8 |
| utf8_roman_ci      | utf8               | 207 |            | Yes         |       8 |
| utf8_persian_ci    | utf8               | 208 |            | Yes         |       8 |
| utf8_esperanto_ci  | utf8               | 209 |            | Yes         |       8 |
| utf8_hungarian_ci  | utf8               | 210 |            | Yes         |       8 |
| utf8_sinhala_ci    | utf8               | 211 |            | Yes         |       8 |
+--------------------+--------------------+-----+------------+-------------+---------+
22 rows in set (0.03 sec)

You could also run
mysql> show collation where charset='utf8';
+--------------------+---------+-----+---------+----------+---------+
| Collation          | Charset | Id  | Default | Compiled | Sortlen |
+--------------------+---------+-----+---------+----------+---------+
| utf8_general_ci    | utf8    |  33 | Yes     | Yes      |       1 |
| utf8_bin           | utf8    |  83 |         | Yes      |       1 |
| utf8_unicode_ci    | utf8    | 192 |         | Yes      |       8 |
| utf8_icelandic_ci  | utf8    | 193 |         | Yes      |       8 |
| utf8_latvian_ci    | utf8    | 194 |         | Yes      |       8 |
| utf8_romanian_ci   | utf8    | 195 |         | Yes      |       8 |
| utf8_slovenian_ci  | utf8    | 196 |         | Yes      |       8 |
| utf8_polish_ci     | utf8    | 197 |         | Yes      |       8 |
| utf8_estonian_ci   | utf8    | 198 |         | Yes      |       8 |
| utf8_spanish_ci    | utf8    | 199 |         | Yes      |       8 |
| utf8_swedish_ci    | utf8    | 200 |         | Yes      |       8 |
| utf8_turkish_ci    | utf8    | 201 |         | Yes      |       8 |
| utf8_czech_ci      | utf8    | 202 |         | Yes      |       8 |
| utf8_danish_ci     | utf8    | 203 |         | Yes      |       8 |
| utf8_lithuanian_ci | utf8    | 204 |         | Yes      |       8 |
| utf8_slovak_ci     | utf8    | 205 |         | Yes      |       8 |
| utf8_spanish2_ci   | utf8    | 206 |         | Yes      |       8 |
| utf8_roman_ci      | utf8    | 207 |         | Yes      |       8 |
| utf8_persian_ci    | utf8    | 208 |         | Yes      |       8 |
| utf8_esperanto_ci  | utf8    | 209 |         | Yes      |       8 |
| utf8_hungarian_ci  | utf8    | 210 |         | Yes      |       8 |
| utf8_sinhala_ci    | utf8    | 211 |         | Yes      |       8 |
+--------------------+---------+-----+---------+----------+---------+
22 rows in set (0.00 sec)

mysql>

To see the individual charset of a database run this:
mysql> show create database sample;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| sample   | CREATE DATABASE `sample` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

As for the settings, you could try this:
Add the lines to my.cnf
[mysqld]
character_set_database=utf8
character_set_server=utf8

then restart mysql
I discussed this back on Aug 01, 2011 : Character Set Encoding in a Table
CAVEAT (For MySQL DB Servers in Windows)
These commands
ALTER DATABASE dbname CHARACTER SET utf8;
ALTER DATABASE dbname COLLATE 'utf8_general_ci';

do not work in the Windows Version of MySQL because of the way Windows locks files. The file needed is called db.opt which is located in the database subfolder in datadir.
You may have to do the following:

mysqldump that database (no database create info, just table creation and INSERTs)
drop that database
create database with the specific charset and collation
reload in the dump into it

EPILOGUE
No matter what you do, please perform any changes on a Dev/Staging Server to see if you get the desired effects
UPDATE 2012-12-05 11:00 EDT
Your Questions

Should I really change it?

To guarantee the proper treatment of the data, you may want to make sure you have apples-to-apples. Data prepared as one charset and loading it into a table with the database possibly aligning the data as if it sees another charset would probably not display the data with the charset mysqld sees when retrieved and sent back to a DB Connection.  Try loading the database on a Dev/Staging Server and experiment with setting default charsets.

Why some defaults use utf8 but some default use latin1 ?

This would depend on the OS version of the MySQL Binary. Windows versions may have latin1 while Linux Versions may use utf8.


How to compare performance of two versions of a function? I just saw this function definition:

create function dbo.f (@a int,  @b int)
returns integer
as
begin
return  case when
        not exists (Select * from t1 where t1.col1 = @a)
        AND @b > 0
        then 1 else 0 end
end
GO

Seeing a not exists I thought attention full table scan and tried to improve it

create function dbo.f (@a int,  @b int)
returns integer
as
begin
return  case when
        exists (Select * from t1 where t1.col1 = @a)
        OR @b > 0
        then 0 else 1 end
end

GO

My feeling is, that this transformation could have been done by an optimizer. It seems to be straight forward, but how can I be sure if he does?

Comment on Igor's answer:
(comparison fixed thanks to Matts comment)

This inspires me to the following:

create function dbo.f (@a int,  @b int)
returns integer
as
begin
 IF @b <= 0
   RETURN 0

 IF exists (Select * from t1 where t1.col1 = @a)
   RETURN 0

  RETURN 1
end
GO

One thing you could do to compare these two versions is to rename one of them to f_old and execute them both inside the same batch, while including the actual execution plan and IO statistics.  From there, it is a simple matter of comparing the plans and IO stats of each.

One thing you need to be wary of is that in your first statement you had IF @b > 0.  In your second statement you have IF @b < 0.  What happens if @b = 0?  You have not covered that possibility.

Finally, I imagine you will find that your thoughts regarding NOT EXISTS vs. EXISTS is correct.  EXISTS will process until the condition is met.  NOT EXISTS requires a table scan (if not indexed) and an index scan (if indexed).  On a small table, you will not see a huge performance gain, but it is always best to design your logic around EXISTS, as opposed to NOT EXISTS.

Matt


Omit made up columns from results So I have a complicated INSERT query which puts information into a relational table based on information pulled from another table and with given variables.

Here's the query:

INSERT INTO relational (class_id, teacher_id, student_id)
VALUES (
    (SELECT `id`,
        (`name` = "John Smith") as exact_score,
        (`name` RLIKE "[[:<:]]John Smith[[:>:]]") as reg_score,
        (MATCH (`name`) AGAINST ("John Smith")) as match_score
    FROM `teachers`
    WHERE (`title` RLIKE "[[:<:]]John Smith[[:>:]]" || MATCH (`title`) AGAINST ("John Smith"))
    ORDER BY
        CASE WHEN exact_score = 1 THEN exact_score END DESC,
        CASE WHEN reg_score > 1 THEN reg_score END DESC,
        CASE WHEN reg_score = 0 AND exact_score = 0 THEN match_score END DESC),
1, 1);

The regex is required because the name might be Mr. John Smith or John Smith PH.d, and the match is required because the name might be jon smith When the stored name is actually just John Smith.

The select query yields the results I am looking for 90% of the time, but the only real column that I need from it is id. The problem I'm running into is with the INSERT's expected column count vs. the column count that is returned by the query.

Right now the INSERT basically looks like this:

INSERT INTO relational (class_id, teacher_id, student_id) VALUES ({{returned id (desired)}}, {{exact score (undesired)}}, {{reg score (undesired)}}, {{match score (undesired)}}, 1, 1);

The last 1, 1 is provided by variables.

My question is, is there a way to select columns for the sake of ORDER BY, but then to omit them from the results?

There is no need to use VALUES; simplify the insert to

INSERT INTO t (class_id, teacher_id, student_id)
    SELECT id, 1, 1 FROM ...

To get rid of extra columns, make an extra level of SELECT:

SELECT a, b
    FROM (
        SELECT a, extra, b FROM ...
         ) AS x

But, you probably won't have any extra columns if you do

ORDER BY
    IF (`name` = "John Smith", 2,
        IF (`name` RLIKE "[[:<:]]John Smith[[:>:]]", 1,
            MATCH (`name`) AGAINST ("John Smith")
       )   )


Insert into table is slow in the night but very fast during day time I have an SSIS package that does an insert into a table and is taking 90 minutes to run but when I run the same package during day time its very slow.

during whole insert process in the night, it is waiting on PAGEIOLATCH_EX wait and during the day time it doesn't wait on anything and runs fast.

I couldn't understand why this is happening. Also during night, there is processes that runs on other database which is not related to this database and that runs for almost 4 hours. Not sure if that is impacting disk and causing slowness overall.

Here's the SQL query...

INSERT INTO dbo.Test (
    Source_System_Cd
    , Account_Id
    , Portfolio_Num
    , Instrument_ID
    , Market_Value_Dt
    , Broker_Dealer_Cd
    , Advisor_Firm_Cd
    , Advisor_Rep_Cd
    , Advisor_Rep_Cd_1
    , Advisor_Rep_Cd_2
    , Advisor_Rep_Cd_3
    , Advisor_Rep_Cd_4
    , Advisor_Rep_Cd_5
    , Strategy_id
    , Strategy_Nm
    , Managed_Product_Fl
    , External_Security_ID
    , Instrument_Type_ID
    , Share_Qty
    , Market_Val
    , Strategy_Family_Id
    , Strategy_Manager_Id
    , Portfolio_Manager_Id
    , Product_Id
    )
SELECT DISTINCT p.Source_System_Cd
    , p.Account_Id
    , ISNULL(p.Portfolio_Num, - 1) AS Portfolio_Num
    , p.Instrument_ID
    , p.Market_Value_Dt
    , p.Broker_Dealer_Cd
    , p.Advisor_Firm_Cd
    , p.Advisor_Rep_Cd
    , aa.Advisor_Rep_Cd_1
    , aa.Advisor_Rep_Cd_2
    , aa.Advisor_Rep_Cd_3
    , aa.Advisor_Rep_Cd_4
    , aa.Advisor_Rep_Cd_5
    , p.Strategy_id
    , p.Strategy_Nm
    , p.Managed_Product_Fl
    , p.External_Security_ID
    , p.instrument_type_id
    , p.share_qty
    , p.market_val
    , p.Strategy_Family_Id
    , p.Strategy_Manager_Id
    , p.Portfolio_Manager_Id
    , p.Product_Id
--select count(*)
FROM dbo.Test_Stage p
LEFT JOIN Advisor.Account_Test aa
    ON aa.Source_System_Cd = p.Source_System_Cd
        AND aa.Advisor_Firm_Cd = p.Advisor_Firm_Cd
        AND aa.Account_Id = p.Account_Id
LEFT JOIN advisor.Test h
    ON p.Source_System_Cd = h.Source_System_Cd
        AND p.Account_Id = h.Account_Id
        AND p.Portfolio_Num = h.Portfolio_Num
        AND p.Instrument_ID = h.Instrument_ID
        AND p.Market_Value_Dt = h.Market_Value_Dt
WHERE p.Source_System_Cd = 2
    AND h.Source_System_Cd IS NULL
    AND p.Market_Value_Dt = @Business

O drive has the data file of this database and during the whole insert process, O drive is 95% busy and i'm also seeing some IO latency in the disk sub system.

dbo.Test table has 300 million rows.

can anyone please help on this ?

The data isn't in memory when it runs at night, based upon the PAGEIOLATCH wait that you mentioned, but is in memory during the day. Your Page Life Expectancy is probably taking a nosedive at night due to all of the other maintenance that is going on. Having to get the data from disks and especially slow disks is much slower than if it were in memory already. Seems like you're going to need to add more memory since getting the data pages from disk is so slow due to the I/O issues you are experiencing.


Where is the documentation for version 1.0 of PostgreSQL wire protocol The current documentation refers only to version 3 of the wire protocol.
However, there is the following note in the documentation:

For descriptions of the earlier protocol versions, see previous releases of the PostgreSQL documentation

However, I can't find anything about previous versions of the wire protocol.

It is all in the Git repository, here is a link to the source. You can actually read the SGML source pretty well.


Search one array element with another I have got tables:

children_tab
id
name
lastname

parent_tab
id
name
lastname

children_parent_tab
children_id
parent_id

There is also parent view that return in field children_ids elements from children_parent_tab

I have to search parents by children ids

Let's say that parent contain:

1|name1|lastname1|{1}
2|name2|lastname2|{5}
3|name3|lastname3|{3,7}

SELECT * FROM parent p WHERE ARRAY[1,2,3,4,5]::int[]  @> children_ids

will return only parent with id 1 and 2 but without that one with id 3. How that query can be changed to return also parent with id 3 because I'm looking for parent of child with id 3?

You seem to run the query on the view. Should be substantially faster based on underlying tables. Like:

SELECT DISTINCT parent_id
FROM   children_parent_tab
WHERE  children_id = ANY ('{1,2,3,4,5}'::int[]);

Details depend on undisclosed exact table definitions and requirements.

Be that as it may, you can fix the query you presented with the "overlap" operator && instead of the "contains" operator @>:

SELECT * FROM parent p WHERE ARRAY[1,2,3,4,5]::int[] && children_ids;

This way you get all parents of any of the children in the given array.


Changing the login name for user dbo I have a database where the owner is Login1 but the username dbo is assigned to Login - login2. I understand in order to change the login name for the username dbo to sa I will have to use the below
EXEC sp_changedbowner 'sa' but the problem here is that this will change the databasowner from Login1 to sa. How do I only change the dbo login name to sa and keep the database owner to Login1

To the best of my knowledge you can't change the dbo user without changing the entry in sys.databases.  This is a case where the same piece of data (the owner of the database) is set it in two places.  While they do occasionally become out of sync they mean the same thing.

If you need someone to have database owner access then just add them to the db_owner role. Really the main difference between dbo and members of db_owner is that members of db_owner can be denied permissions while the actual dbo can't.


Subtracting Days From Current Date I know this is probably very basic, but I'm trying to create a calculated field that subtracts X number of  from Current Date, based off of the value of another field:

CASE WHEN DaysPD = 30 THEN 1
     WHEN DaysPD = 180 THEN 6
     WHEN DaysPD = 360 THEN 12
 END AS MonthsPD

CASE WHEN RateCode = 'AC' THEN RateResetDate - MonthsPD

In this example, if the RateResetDate = '03/08/2019' and MonthsPD = 6, then I would want the calculation to be '03/08/2019' - 6 Months = '10/8/2018'

You can use the DATEADD function to do that:

CASE WHEN [RateCode] = 'AC'
      THEN DATEADD(MONTH,
            CASE WHEN DaysPD = 30 THEN -1
                 WHEN DaysPD = 180 THEN -6
                 WHEN DaysPD = 360 THEN -12
             END,
           [RateResetDate])
    END AS [AdjustedDate]

Alternatively, you can just pass in the days if you want:

CASE WHEN [RateCode] = 'AC'
      THEN DATEADD(DAY, -DaysPD, [RateResetDate])
    END AS [AdjustedDate]


sp_BlitzIndex only results in one row (with priority -1) Not sure why, but when I run sp_BlitzIndex (v4.5), it always results in only one row (even if set @GetAllDatabases = 1).  This is the "header" row that shows Priority = -1.  What am I doing wrong?  I don't see any errors in the results or message tabs.  I'm using SQL 12.0.5000.0

Below is a copy of the messages:

Starting run. sp_BlitzIndex(TM) v4.5 - November 15, 2016
Create temp tables.

CCLocalDemo
Inserting data into #IndexColumns for clustered indexes and heaps
Inserting data into #IndexColumns for nonclustered indexes
Inserting data into #IndexSanity
Updating #IndexSanity.key_column_names
Updating #IndexSanity.partition_key_column_name
Updating #IndexSanity.key_column_names_with_sort_order
Updating #IndexSanity.key_column_names_with_sort_order_no_types (for create tsql)
Updating #IndexSanity.include_column_names
Updating #IndexSanity.include_column_names_no_types (for create tsql)
Updating #IndexSanity.count_key_columns and count_include_columns
Preferring non-2012 syntax with LEFT JOIN to sys.dm_db_index_operational_stats
Inserting data into #IndexPartitionSanity
Updating index_sanity_id on #IndexPartitionSanity
Inserting data into #IndexSanitySize
Adding UQ index on #IndexSanity (database_id, object_id, index_id)
Inserting data into #MissingIndexes
Inserting data into #ForeignKeys
Updating #IndexSanity.referenced_by_foreign_key
Update index_secret on #IndexSanity for NC indexes.
Update index_secret on #IndexSanity for heaps and non-unique clustered.
Populate #IndexCreateTsql.
Populate #PartitionCompressionInfo.
Update #PartitionCompressionInfo.
Gathering Computed Column Info.
@Mode=0 or 4, we are diagnosing.
check_id 1: Duplicate keys
check_id 2: Keys w/ identical leading columns.
check_id 11: Total lock wait time > 5 minutes (row + page) with long average waits
check_id 12: Total lock wait time > 5 minutes (row + page) with short average waits
check_id 20: >=7 NC indexes on any given table. Yes, 7 is an arbitrary number.
check_id 21: >=5 percent of indexes are unused. Yes, 5 is an arbitrary number.
check_id 22: NC indexes with 0 reads. (Borderline)
check_id 23: Indexes with 7 or more columns. (Borderline)
check_id 24: Wide clustered indexes (> 3 columns or > 16 bytes).
check_id 25: Addicted to nullable columns.
check_id 26: Wide tables (35+ cols or > 2000 non-LOB bytes).
check_id 27: Addicted to strings.
check_id 28: Non-unique clustered index.
check_id 30: No indexes with includes
check_id 31: < 3 percent of indexes have includes
check_id 32: filtered indexes and indexed views
check_id 33: Potential filtered indexes based on column names.
check_id 40: Fillfactor in nonclustered 80 percent or less
check_id 40: Fillfactor in clustered 80 percent or less
check_id 41: Hypothetical indexes
check_id 42: Disabled indexes
check_id 43: Heaps with forwarded records or deletes
check_id 44: Large Heaps with reads or writes.
check_id 45: Medium Heaps with reads or writes.
check_id 46: Small Heaps with reads or writes.
check_id 47: Heap with a Nonclustered Primary Key
check_id 50: Indexaphobia.
check_id 60: XML indexes
check_id 61: Columnstore indexes
check_id 62: Spatial indexes
check_id 63: Compressed indexes
check_id 64: Partitioned
check_id 65: Non-Aligned Partitioned
check_id 66: Recently created tables/indexes (1 week)
check_id 67: Recently modified tables/indexes (2 days)
check_id 68: Identity columns within 30 percent of the end of range
check_id 69: Column collation does not match database collation
check_id 70: Replicated columns
check_id 71: Cascading updates or cascading deletes.
check_id 80: Most scanned indexes (index_usage_stats)
check_id 81: Top recent accesses (op stats)
check_id 90: Outdated statistics
check_id 91: Statistics with a low sample rate
check_id 92: Statistics with NO RECOMPUTE
check_id 93: Statistics with filters
check_id 99: Computed Columns That Reference Functions
check_id 100: Computed Columns that are not Persisted.
Insert a row to help people find help
Returning results.

V4.1 is the last working version.  It is an issue in v4.2 - v4.8(current).  It works for some databases, but not all, when run with no parameters.  For example, it works in master, but fails in a Dynamics GP company database. @Mode = 4 still works fine though.

I tested versions 4.1 - 4.8 on SQL Server 12.0.4100.1 and 12.0.5511.0


Store logging in separate or existing database? In a .NET WebForms project we want to log certain actions in a database for a year. The estimate is that the log will have a maximum size of 1GB (20.000 records per day of 10 fields) that will probably be queried a maximum times of 2 per day.

We are discussing if it is advisable to put the logging table in a new database (on perhaps even a separate server), or use a new table in our existing database.

The advantages of using our existing database are that we do not need to make a new connection in the code nor administrate a new database. The disadvantage might be that the database cannot handle it.

What are your insights? Do you have any advice?

I would go for the same database, for the reasons you state in your question. If you're worried about performance or growth, create a new "logging" filegroup and put the logging table(s) on that. If things grow out of hand in the future, you can move the .ndf (the physical file that contains the new filegroup) to a different physical drive.

Even if you stick to the same server and add a new database, the effect is similar. You don't have to create a new connection in your client application - all you have to do is reference the table by a three-part name, [database].[schema].[table].

Edit: also, consider if the logged information should "follow" the rest of the database, or if it's specific to a certain server or environment.


Min no. of replicas to set up SQL Server Always ON over a Failover Cluster on a Windows Cluster AO: Always ON
FCI: Failover Cluster Instance
WSFC: Windows Server Failover Cluster

I'm scratching the surface of AO. I currently have one SQL 2012 FCI on a WSFC. Which means one Active node at a given time. I cannot set up an AO with this, can I? Logically speaking I need “at least two” active (up & running) SQL Server instances to set up. And in Microsoft's example they used a total of 5 nodes, and the minimum would be 3 for a AO on FCI + WSFC right?

Here is what my FCI looks like.

There is no point in having AO when only 1 instance of SQL is running at a given time. Right ?

If yes what would I need to set up AO?

A new Node in the Cluster or
Any other Stand alone SQL Should(same
version) do as well?

If by AlwaysOn you mean "Availability Groups" (which is typically the case), then you can most definitely use your failover cluster instance as a replica.

For this to be an Availability Groups design that is actually useful in the sense of AGs, then you would most likely want another server to host another replica in the Availability Group (for whatever it is that you're looking for:  HA, DR, reporting, etc.).  All of the servers that are going to be participating in the Availability Group need to reside in the same WSFC.

  There is no point in having AO when only 1 instance of SQL is running at a given time

The replicas in an Availability Group are individual SQL Server instances (whether it be a FCI or standalone instance), so each of the replicas are "running" in a normal condition.

  what would I need to set up AO?

Technically speaking?  You would just need another instance of SQL Server to have as an additional replica for the AG.

Realistically speaking?  We can't answer that right now with the current state of the question.  We'd need to know exactly what you want your Availability Group for.


Unistalled MySQL 5.6 and installed MySQL 5.7 - Restore Database and Tables All of the data resides in "C:\ProgramData\MySQL\MySQL Server 5.6\data".  There are 46 folders inside of this folder.  There is one folder for every old database I had.  For example the first three database are "509", "blog" and "bsp".  Inside each of the folders, named after the database, are several files:

auth_history.frm
auth_hisotry.ibd
comments.frm
comments.ibd
db.opt
familymembers.frm
familymembers.MYD
familymemeber.MYI

etc.

Only three tables show in MySQL Workbench, sakila,sys and world.  These may be databases that come along with a complete install.

I'd like to get the old schemas and data back.  How do I do that?  I've tried several things, but reading binary files is not my strongest point.

All help is really appreciated.  Several schemas had hundreds of tables and lots of data.  Nothing is larger than 1/2 Gig.

I'm currently running MySQL 5.7 on a Windows 10 Pro machine.

The "user" that logged into Workbench did not have permissions to see the rest of the databases.

Also, did you run mysql_upgrade?


LGWR is switching frequently in every 3,4,7 minutes. what should i do I am running Oracle 11g R2 single instance on Linux 6.7.
Data guard is also configured.

In a database I have 4 groups of redologs, each group has 2 members and each logfile size is 200mb.

The problem I am facing is that LGWR is switching every 4 minutes. The output size of archivelogs are maximum 90mb to 100mb.

What should I do to solve this problem?

The output size of archivelogs are maximum 90mb to 100mb.

Its not necessary to have archived log file should be same.

  during night session logs get filled upto 180mb out of 200mb. but during morning session logs get maximum to 90mb to 100mb

Its not necessary to have log switch occur when online redo log file is 100% full. There is also an algorithm inside Oracle which determines the timing for the log switch.
After the log switch, the archiver process will copy only the actual information from the
redo log files. Since the redo logs are not 100% full after the log switch this results in uneven, smaller files than the original redo log files.

Some of the factors which may enforce frequent log switch are listed below:
Online redo log file size
LOG_BUFFER_SIZE parameter.
Current load of the database
 Logfile space reservation algorithm

Details are given in an Oracle knowledge based document.(MOS account required)
Archived redolog is (significant) smaller than the redologfile. (Doc ID 1356604.1)


Why won't pg_basebackup output go to log? I run a nightly pg_basebackup from a cron job (Postgres 9.3 on debian "wheezy"), but the backup has been failing. It actually creates output, but the .tar file is corrupt and won't extract. I went to my log file, only to find it is 0 bytes. Could anybody tell me what is wrong with my setup? Why isn't the log file saving the output of pg_basebackup?

#!/bin/sh
PATH=$PATH:/usr/bin
export PATH

BASEBACKUP_LOG=/var/log/postgresql/basebackup.log
[ -f $BASEBACKUP_LOG ] && mv -f $BASEBACKUP_LOG $BASEBACKUP_LOG.old

BACKUP_PATH=/path/to/backup/$(date +%F)
pg_basebackup -D $BACKUP_PATH -Ft -z -v 2>&1 | ts '%F %T %Z' &> $BASEBACKUP_LOG

The way I'm reading the pg_basebackup line, I think it should redirect stderr to stdout, pipe the combined stream to the ts function (prepend timestamp to a line) and write that to the path at $BASEBACKUP_LOG. Am I missing something obvious here?

On advice given below, I added an explicit reference to the path for ts & pg_basebackup (both reside in /usr/bin). There was no change in behavior.

You specified #!/bin/sh in your script's shabang line. This gives you a POSIX shell. Under POSIX style shells you can only specify numbers for redirection, as noted in the documentation under section 2.7 Redirection.

&> is a "bashism", shown here in the bash documentation under io-redirection.

This can cause undefined behavior under POSIX compliant shells acting as /bin/sh, and by running shellcheck -s sh on the contents of the script in the above question, as stated under the warning for SC2039, which warns that &> is non-standard, and might fail under different contexts.

You might have better luck, if you specify #!/usr/bin/env bash, or if you change the way that you're doing the redirection, if you want to keep using the #!/bin/sh shell.

As for the $BACKUP_PATH, you might want to double-quote it to prevent any sort of weirdnesses with globbing or word splitting that can happen, which could also possibly explain the zero size tar file.

Hope that helps. =)


SQL Free space management I have 1.8TB AX database.

File Size : 1851427.75 MB

Free Space : 502837.13 MB

If free spaces will be used in the future, I don't want to shrink. Because it's taking a long time. (and index fragmentation)

Daily observations result:

Day Free space 492.435,75 MB (26%)
Day Free space 485.750,25 MB (26%)
Day Free space 502.875,75 MB (27%)

Sometimes the free space is reduced, sometimes data file size grows.

Why there is growth when there is free space, how SQL decides?

SQL Server tracks free space within data files and uses that when additional space is needed. Data files will grow only when there is not enough existing free space to satisfy allocation needs.  Shrinking is not necessary for this space to be reused and should be generally be avoided for the reasons you mentioned.

If you observe occasional file growth even though sufficient free space exists, this may be due to short term space needs. For example, rebuilding a clustered index will require about 120% of the table size as free space during the operation that is freed afterward.


Count/Statistic for number of SHOW INDEX queries in MySQL/MariaDB 5.5 We have a lot of SHOW INDEX FROM queries coming from our ORM layer.
Usually there is a mechanism to prevent this but it doesn't seem to work
at the moment. To better analyze the problem it would be helpful if we
could see how many such queries happened on a server.

Is there a counter
or a statistic which can provide us this information?

We use MariaDB 5.5.


Restore Error event id 17066 and 3624 while restoring diff backup I am getting the following error while restoring the Diff back up after restoring the Full backup in Norecovery mode.

I am unable use dbcc checkdb as the database in restoring db.Issues happens only in three instance where there is totally 6 instance. In other instance restore is working fine.

Error:

  Msg 5052, Level 16, State 1, Line 4

  ALTER DATABASE is not permitted while a database is in the Restoring
  state.

  Msg 5069, Level 16, State 1, Line 4

  ALTER DATABASE statement failed.

  Location:     filesystem.cpp:526

  Expression:   newTree.IsInArchive ()

  SPID:         98

  Process ID:   1956

  Msg 3013, Level 16, State 1, Line 22

  RESTORE DATABASE is terminating abnormally.

  Msg 3624, Level 20, State 1, Line 22

  A system assertion check has failed. Check the SQL Server error log
  for details.

  Typically, an assertion failure is caused by a software bug or data
  corruption. To check for database corruption, consider running DBCC
  CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini
  dump will be sent to Microsoft. An update might be available from
  Microsoft in the latest Service Pack or in a QFE from Technical
  Support.

Help me in resolving this issue.

A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB

This error can be caused by transient, timing-related errors, or by in-memory or on-disk data corruption.

This message also says that the backup set which you are trying to restore as differential backup is actually not consistent and SQL Server cannot bring database to a logical point if you try to apply this backup. The assertion checks are either SQL Server bugs or something corrupt in database.

You can bring database online and can run regular dbcc checkdb command and post the output

You need to also check your differential backup is consistent and you can do that by below command

restore verifyonly from disk='backup_location\backup.bak'

run above command and paste the result in question

  I am unable use dbcc checkdb as the database in restoring db.Issues happens only in three instance where there is totally 6 instance. In other instance restore is working fine.

Since database is in recovery mode you cannot run any command on it that is why every command you run on it fails

Lastly can you also add output of

sp_readerrorlog


Oracle insert through dblink performance issue I'm trying to do an insert as select to a table via dblink. The query looks like this:

    Insert into MARS_SUPERVISORS_PUB@EDHPUB
     select
        {...}
     from edhper a, edhper b
     where {...
              a.idn not in (select s.tarcfsra idn from cfsra s where s.srtcfsra = 'MAPSSPA' and s.stscfsra = 'A')
           ...}

The select itself works quite fast here is its execution plan shows all the joins, but the insert takes very very long and the plan consists only of "REMOTE" steps :

INSERT STATEMENT REMOTE, GOAL = ALL_ROWS
 LOAD TABLE CONVENTIONAL
  SORT AGGREGATE
   FILTER
    REMOTE
    REMOTE
  FILTER
   HASH JOIN
    REMOTE
    REMOTE
   REMOTE

I wonder if Oracle tries to push both tables a and b through the database link and then joins it there.
Can I instruct the optimizer to do a select inside the current DB and pass only the results to @EDHPUB?

P.S/

If I create a test table locally, insert this select there and then do an insert like:

Insert into MARS_SUPERVISORS_PUB@EDHPUB
select * from TEMP_TBL

it works fast. For me it looks like an evidence that in my previous example Oracle really tries to push unjoined tables thtough dblink.

UPDATE/
I figured out that the insert takes long because of a a.idn not in ... condition.
without it the plan looks like this:

INSERT STATEMENT REMOTE, GOAL = ALL_ROWS
 LOAD TABLE CONVENTIONAL
  SORT AGGREGATE
   FILTER
    REMOTE
    REMOTE
  HASH JOIN
   REMOTE
   REMOTE

The driving_site hint, as suggested by Alex Poole would be an interesting solution to consider/investigate. I'm not sure how/if it works for remote inserts though.

If the hint doesn't work, you can use a local temporary table. Insert the data locally into your temporary table. Then INSERT INTO tab@remote FROM temp should perform well.


New table or new column Apologies in advance if this is a dumb question.

Background:
I am a student intern, I've been given the task of adding a new feature the companies client administration system. Basically it involves "purging" subscriptions, they want selected subscriptions to be removed from the client admin system but remain in the database for recording purposes. We currently have around 600,000 subscriptions. They wish to "purge" 10,000 subscriptions right away and another 30,000 over the next few months, so around 6% of the subscriptions table.

What I am wondering is what is the best way to accomplish this. I have come up with two options;

a) Add a new table to the database to store the "purged" subscriptions with 3 columns, purgedId, subscriptionId which is a fk referencing the subscription, and purgedDate. Then in any sql that references the subscription table left outer join the purged table and use "where purgedId = null".

b) Add a new column to the subscription table to store the purgedDate and add "where purgedDate = null" to all of my subscription sql.

Option a) seems like a waste, to add a whole new table to store essentially one attribute.
Option b) seems like a waste, to add a column that is only used by 6% of the rows in the table.

On another note, is an outer left join the best way to exclude the purged subscriptions?

Thanks in advance for any assistance you are able to provide and once again sorry if this is an elementary question.

I'd go with option 3, which will require less overall work:

Make a lookup table that contains a list of purged records and dates, like in your first option
Rename your current table to something like dbo.Tablename_master
Create a VIEW named dbo.Tablename which consists of:

.

SELECT <fields>
FROM dbo.Tablename_Master
WHERE PrimaryKeyField NOT IN (SELECT PrimaryKeyField FROM PurgeTable)

Now you don't need to modify any existing code, since all references to your original table go through the view instead.  This will be complicated if you have any existing constraints but they should be easy to work around.


How to set slow query log in MariaDB my.cnf? It should be easy, but when I put these lines:

log_slow_queries = 1
long_query_time = 1
log-slow-queries = /var/log/mysql/slow_query.log
log_queries_not_using_indexes

in [mysqld], MariaDB fails to restart.

I looked at the docs, but could not figure out what is wrong in my configs.

Also , I've set the file permission to /var/log/mysql/slow_query.log

# ls -al /var/log/mysql/slow_query.log
-rw-rw-r-- 1 mysql mysql 744682 Dec  4 23:46 /var/log/mysql/slow_query.log

However, I can set the parameters in command line:

SET GLOBAL slow_query_log_file = '/var/log/mysql/slow_query.log';
SET GLOBAL LONG_QUERY_TIME = 1;
SET GLOBAL slow_query_log = 'ON';

and see that they are set:

+---------------------+--------------------------------------------------------------------------------------------------------------+
| Variable_name       | Value                                                                                |
+---------------------+--------------------------------------------------------------------------------------------------------------+
| log_slow_filter     | admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk |
| log_slow_rate_limit | 1                                                                                |
| log_slow_verbosity  |                                                                                |
| slow_launch_time    | 2                                                                                |
| slow_query_log      | ON                                                                                |
| slow_query_log_file | /var/log/mysql/slow_query.log                                                                                |
+---------------------+--------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

and even the slow queries are being written to the log. But I want to make the config persistent by writing them to my.cnf. So appreciate your help.

You said you added this

log_slow_queries = 1
long_query_time = 1
log-slow-queries = /var/log/mysql/slow_query.log
log_queries_not_using_indexes

That's the old way MySQL 5.0 sets the slow query log.

To activate the slow query log for MySQL 5.6 and MariaDB, you must use the following

slow_query_log
slow_query_log_file

Therefore, you need this in my.cnf

slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysql/slow_query.log
log_queries_not_using_indexes


Why isn't pg_restore --create working? I backed up a database called app_data, on a 9.3 server, with this basic command:

pg_dump -n public -F custom app_data > app_data.pg.dump

Then I try to restore it on another server (running 9.4) like this:

pg_restore -C -d postgres app_data.pg.dump

But it puts all the tables in the postgres database. The man page says it will create and use a new database, app_data.

  -C --create

  Create the database before restoring into it. [...]

  When this option is used, the database named with -d is used only to issue the initial DROP DATABASE and CREATE DATABASE commands. All data is
             restored into the database name that appears in the archive.

That's not what it's doing. The name in the archive is app_data:

bash-4.2$ pg_restore -l app_data.pg.dump
;
; Archive created at Tue Dec 15 04:16:52 2015
;     dbname: app_data
...

Am I doing something wrong?

Yes that behaviour is something that I should report to postgres as use of different flags doesnt seem to have an effect and it just doesnt work. To solve it use:

create DB

createdb -h HOST -U USER -W DB_NAME

restore and in case of Error Stop

egrep -v 'EXTENSION.*plpgsql' DUMPFILE.sql | psql -h HOST -U USER -W  -d DB_NAME -v ON_ERROR_STOP=1


SQL Server suspended queries - tempDb pagelatch_up 2:1:2 I have performance issue on MS SQL 2014 db.
Store procedure that does SELECT has input parameters as custom Table Value Parameter type.
There are 50-100 sessions at the time and executing store proc can take 1s to 14s.
I can execute SP with simple input parameter and it can take 1s if I repeat the same it can take up to 14s, it almost if I am feeling lucky or not.
Running diagnostic queries I can see PAGELATCH_UP on many calls to this store procedure and there is a chain of one session id waiting on another session id.
PAGELATCH_UP is in the format of resource 2:1:2 2:6:2 2:9:2 depends from the time of capture.
So 2:: is tempDb :1: or :6: are tempDb files, there is 8 of them and this is 128GB 32core machine.
For the last one ::2 I could found out it is Global Allocation Map (GAM).
In all blogs people have usually have issue with resource ::1 or ::3 (PFS and SGAM), but haven't seen anything regarding GAM.
There is a recommendation from Microsoft how to resolve contention in tempDB which focuses on PFS and SGAM:
https://support.microsoft.com/en-gb/help/2154845/recommendations-to-reduce-allocation-contention-in-sql-server-tempdb-d
Would you suggest increasing number of tempDb files from 8 to 12, 16, ...32?
Is it issue with table-valued parameters, and this is the reason for pagelatch_up as this is select statement in SP?
Any other replacement for Table Value Parameters?

Is it an issue with table-valued parameters, and this is the reason for pagelatch_up as this is a select statement in SP?
Table-valued parameters create an activity on the tempdb database
for this reason, your stored procedure might be caused to tempdb allocation contention.
The main symptoms of this issue is to see the following wait types in the tempdb database;

PAGELATCH_UP
PAGELATCH_SH
PAGELATCH_EX

In general, you can solve this problem to create multiple tempdb data files because of the
your SQL Server version you might be used to
-T1117 and -T1118 flags.
Any other replacement for Table Value Parameters?
On the other hand, using the Memory-Optimized Table Variables can solve this type of problems
using the Memory-Optimized Table Types and Table Variables
Would you suggest increasing the number of tempDb files from 8 to 12, 16, ...32?
This is the best practice:
The number of the TempDB data files should match the number of logical processors, up to eight files, on the machine where the SQL Server instance is installed.
However, this value can change according to your requirements.


Influxdb and Grafana combine multiple SELECT I have some network counters in Influx that I graph with Grafana.

I'm trying to combine stats from a couple ethernet interfaces and combine them to show aggregate bandwidth.

These two separate queries work on their own, but I can't figure out how to write it to combine these two.

SELECT 8 * non_negative_derivative(mean("value"), 1s )
FROM "inoctets"
WHERE "host" = 'myhost1' AND $timeFilter
GROUP BY time(1s) fill(null)

SELECT 8 * non_negative_derivative(mean("value"), 1s )
FROM "inoctets"
WHERE "host" = 'myhost4' AND $timeFilter
GROUP BY time(1s) fill(null)

Edit:  FWIW I'm running Influx 1.1.1 and Grafana 4.1.1

To combine aggregated measurements under different tags is a bit tricky. I managed to find this post which has the answer.

The key is to use sub-queries (InfluxDB 1.2+) to capture each tag's measurements separately. Then, since the field names are the same, they need to be renamed. Finally you can manipulate them as needed. Here's your example:

SELECT 8 * (non_negative_derivative(mean("inoctets1"), 1s )
          + non_negative_derivative(mean("inoctets4"), 1s ))
AS "inbytestotal"
FROM (
    SELECT "value"
    AS "inoctets1"
    FROM "inoctets"
    WHERE "host" = 'myhost1' AND $timeFilter
), (
    SELECT "value"
    AS "inoctets4"
    FROM "inoctets"
    WHERE "host" = 'myhost4' AND $timeFilter
)
GROUP BY time(1s) fill(null)


Trigger is failing with no errors at all, why? I need to UPDATE territory_id column at pdone.reps table by using and filtering results from veeva_new.userterritory so based on the answer I got here I am trying to build my own and this is how it looks like:

USE `veeva_new`;

DELIMITER $$

DROP TRIGGER IF EXISTS veeva_new.userterritory_ai$$
USE `veeva_new`$$
    CREATE DEFINER=`root`@`%` TRIGGER `veeva_new`.`userterritory_ai` AFTER INSERT ON `userterritory` FOR EACH ROW
    BEGIN
    SET @TerritoryVerification=(select reps.veeva_rep_id from pdone.reps where reps.veeva_rep_id=NEW.userid);

    IF (@TerritoryVerification>0) THEN
        SET @TerritoryLastMod=(SELECT reps.lastSyncAt FROM pdone.reps WHERE reps.veeva_rep_id=NEW.userid LIMIT 0,1);

        IF (NEW.lastmodifieddate > @TerritoryLastMod) THEN
            UPDATE pdone.reps
            SET reps.territories_id = NEW.territoryid, reps.lastSyncAt = NEW.lastmodifieddate, reps.updatedAt=now()
            WHERE reps.veeva_rep_id=NEW.userid;
        END IF;
    END IF;
    END$$
DELIMITER ;

But it's not working since insertion on veeva_new DB just stop with no errors, I just see this in general.log:

 11 Query COMMIT
           11 Query SELECT sync_time FROM _sync_history WHERE object_name='userterritory' ORDER BY sync_time DESC LIMIT 1
150703 12:48:42    11 Query START TRANSACTION
           11 Query INSERT INTO _sync_history (object_name, sync_time) VALUES('userterritory', '2015-07-03 17:18:26')
           11 Query INSERT INTO userterritory (id,lastmodifieddate,userid,territoryid) VALUES('04S80000000uZi9EAE','2009-08-24 17:56:49','00580000001sYARAA2','04T800000008yHOEAY'),('04S80000000uoFIEAY','2009-09-24 19:20:02','00580000002Ka2yAAC','04T800000008yHOEAY')$
           11 Query SET @UserVerification=(select reps.veeva_rep_id from pdone.reps where reps.veeva_rep_id=NEW.userid)
           11 Query ROLLBACK
           11 Quit

This is the schema for veeva_new.userterritory table:

CREATE TABLE `userterritory` (
  `id` varchar(18) NOT NULL,
  `userid` varchar(18) DEFAULT NULL,
  `territoryid` varchar(18) DEFAULT NULL,
  `lastmodifieddate` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

reps.territories_id has a constraint against reps.territories and this is the schema for reps.territories table:

CREATE TABLE `territories` (
  `veeva_territory_id` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `description` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `district` int(11) DEFAULT NULL,
  `inactive` tinyint(1) NOT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`veeva_territory_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

What is wrong on my trigger?

Try with:

USE `veeva_new`;

DELIMITER $$

DROP TRIGGER IF EXISTS veeva_new.userterritory_ai$$
USE `veeva_new`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `veeva_new`.`userterritory_ai` AFTER INSERT ON `userterritory` FOR EACH ROW
BEGIN
    # [ VARIABLES DECLARATION ]
    DECLARE vUserID VARCHAR(18) DEFAULT '';
    DECLARE vTerritoriRep VARCHAR(45) DEFAULT '';
    DECLARE vTerrytoriLastMod DATETIME DEFAULT '0000-00-00 00:00:00';
    # [ VARIABLES ASIGNATION ]
    SET vUserID         =NEW.userid;
    SET vTerritoriRep   =(select IFNULL(reps.territories_id,'N/A') from pdone.reps where reps.veeva_rep_id=vUserID LIMIT 0,1);
    SET vTerrytoriLastMod=(SELECT reps.lastSyncAt FROM pdone.reps WHERE reps.veeva_rep_id=vUserID LIMIT 0,1);

IF (NEW.territoryid != vTerritoriRep) THEN
    UPDATE pdone.reps
    SET reps.territories_id = NEW.territoryid,
        reps.lastSyncAt = IF(NOW()>vTerrytoriLastMod,NOW(),reps.lastSyncAt),
        reps.updatedAt=now()
    WHERE reps.veeva_rep_id=vUserID;
END IF;

END$$
DELIMITER ;

Assuming veeva_rep_id = NEW.userid (from userterritory).

This will update reps.territories_id when the NEW.territoryid is updated, reps.lastSyncAt when reps.lastSyncAt be greater than @TerritoryLastMod and reps.updatedAt = now(), current timestamp.


Access Query - Count distinct values I have a dataset similar to the one below in Access:

I am wanting to create a query which generates a table summarising how many sites each company is at. I.e. it will return something like this:

Alpha - 2

Bravo - 2

Charlie - 2

Delta - 2

I have tried creating a query which simply counts the Site field but this counts the same site multiple times if it is repeated. My aim is to count each site only once.

Does anyone have a simple way to achieve this in Access' Query Design?

The key concept here is that you must create more than one query.  Not all results can be obtained by a single query.  It is possible to accomplish this using SQL subqueries (a.k.a. nested queries) so that the overall query can be saved and edited as a single unit, but subqueries are not supported by the Query Designer--they must be edited in SQL View.

Some database systems support aggregate functions which can directly count distinct values in one query.  Access does not support such a function, so you must first write a query which gets distinct values for the Site column.  A query can always refer to another query in the same manner it refers to a table.

Even though the question specifically mentions Design View (Query Designer), it is traditional on Stack Exchange sites to share queries as SQL text.  It is much too tedious to constantly paste screen shots.  Also, SQL text can be copied and pasted directly into SQL view.  At the least you should know/learn how to switch into SQL view so that you can copy and paste there.  For basic SQL statements, just switch back to Design View.

SELECT Company, Site FROM table GROUP BY Company, Site

or

SELECT DISTINCT Company, Site FROM table

The DISTINCT keyword corresponds to the Design View property Unique Values = True.  Save that query as, for example, CompanySites.

Now count values in the saved query:

SELECT Company, Count(Site) As Sites FROM CompanySites

You may want to consider normalizing the database by adding separate [Company] and [Site] tables with appropriate primary and foreign keys.


How to import a .sql file in MySQL? I am trying to import a .sql file using MySQL Workbench and I get this error:

ERROR 1046 (3D000) at line 28: No database selected

I have first created an empty database called with the same name as the .sql file but it doesn't work.
I have also tried to do it with the mysql command client using:

mysqldump -u root database > file.sql

But it says a have an error in my SQL syntax. Furthermore, I don't know the path where I have to set the file.sql.

Export:

mysqldump -u username –-password=your_password database_name > file.sql

Import:

mysql -u username –-password=your_password database_name < file.sql


Should I separate table with a lot of columns and 3 index column? I'm new to database stuff so I apologize if I say anything ridiculous.
I have a table with 300 columns, which stores the score of 300 questions. There is also an "id-user" columns which is the primary key, and three more columns named "weekScore", "monthScore" and "yearScore". Every time a user answer a particular question the id will found and score will added to the particular column, and the score added to weekScore, monthScore and yearScore too. As you see there is a lot of queries go to this table.
Question: I want to index three score columns (weekScore, monthScore and yearScore). is it better to separate these three columns and make another table because of the indexing?

Or I change the design to this, and index three columns (weekScore, monthScore and yearScore).

I'm doing a bit of guessing here, but I imagine you have a table like:

CREATE TABLE ANSWERS
( USER_ID ...
, TIME_OF_TEST ...
, QUESTION1 ...
, QUESTION2 ...
...
, QUESTION300 ...
,    PRIMARY KEY (USER_ID, TIME_OF_TEST)
);

and now you want to add attributes for  weekScore, monthScore and yearScore.

I think it is a bad idea to add those attributes to this table, and in fact I suggest you rethink your whole design. I would have thought that there is an entity TEST that acts as a placeholder for a number of questions, but since you don't mention it I'm going to ignore that. I would suggest something like:

CREATE TABLE QUESTIONS
( QUESTION_ID ...
, WORDING_OF_QUESTION ...
, SCORE ...
, CORRECT_ANSWER ...
....
)

Dependent of what kind of questions there are, you might even normalize it further with a table QUESTION_ITEMS

CREATE TABLE ANSWERS
( USER_ID ...
, QUESTION_ID ...
, ANSWER ...
, TIME_OF_ANSWER ...
);

It is difficult giving any more specific advise without knowing more details, but hopefully it will give you some ideas.

One common mistake is to think of the database as a spread-sheet that represents the report (your first suggestion above). I suspect that naming a relation table in SQL is the reason for much of this confusion.

For the new attributes weekScore, monthScore and yearScore, I would start by determining them in runtime, i.e. don't store them anywhere. You can create a view for them, and if it turns out to slow, make a table out of it that you update with a batch-process.


postgres npgsql DDL statement success or failure I am using npgsql with a postgres DB, writing a DB updater that will need to execute a series of (usually) DDL statements like "ALTER TABLE ADD COLUMN blah..." and the like. The simple way to do this is with Command.ExecuteNonQuery, however I need to know if each statement succeeded or not. ExecuteNonQuery returns "the number of rows effected" but it appears that this is always 0 for DDL regardless of success or failure, since the content of the table is not altered, only the structure.

What is the simplest way of determining the success or failure of arbitrary DDL (and update) commands read in from a text file? Can/Should/Must I wrap each statement in a procedure with an exception handler? Is there a simpler, more straightforward way?

One issue I see with wrapping in a procedure is that the procedure itself must then be part of the DB, and the update process is no longer as independent. I would like to avoid this. Of course, my updater could create the procedure if needed, and remove it after, but this seems cumbersome. Am I missing something obvious?

________Edit for clarification:

@Laurenz Albe is of course completely correct, if there is a syntax error, key duplication, missing column referenced, etc., an exception will be thrown. This probably covers nearly all cases. However, I have encountered issues where the SQL is valid but may or may not actually do anything useful - for example, if there is a WHERE clause, I may be expecting some (particular) number of 'rows effected'. The absence of an exception merely tells me that the statement was valid. When can or cannot I rely on the return value of ExecuteNonQuery, or is there a simple alternative?

If a statement fails, you'll get an exception.

There is no way to miss that unless you catch and ignore exceptions, which is never ok.


Rules in SQL Server 2005 The syntax for rules in SQL Server 2005 is given:

CREATE RULE [ schema_name . ] rule_name
AS condition_expression

Whenever using rule, for example:

CREATE RULE list_rule
AS
@list IN ('1389', '0736', '0877');

Why are we using a variable like @list in it? It doesn't correspond to the table name or anything.
Is that used for storing the values given in the IN statement or anything else?

I just need an explanation for this. I know that rules are now deprecated but I am using SQL Server 2005.

From MSDN

  *condition_expression* includes one variable. The at sign (@) precedes
  each local variable. The expression refers to the value entered with
  the UPDATE or INSERT statement. Any name or symbol can be used to
  represent the value when creating the rule, but the first character
  must be the at sign (@).

http://msdn.microsoft.com/en-us/library/ms188064.aspx


Rolling up multiple rows into a single row and column from two tables Here is my code

SELECT
              register cr.id,
              register_cr.first_name,
              register_cr.father_name,
              register_cr.surname,
              register_cr.address,
              cr_record.charge,
              cr_record.date,
              cr_record.charge_type,
              cr_record.fir_no
FROM
             register_cr as register_cr
INNER JOIN
             cr_record as cr_record ON register_cr.id = cr_record.cri_reco_id

The GROUP_CONCAT allows this form of transformation:

SELECT
              register_cr.id,
              register_cr.first_name,
              register_cr.father_name,
              register_cr.surname,
              register_cr.address,
              GROUP_CONCAT(DISTINCT cr_record.charge SEPARATOR "\n"),
              GROUP_CONCAT(DISTINCT cr_record.date SEPARATOR "\n"),
              GROUP_CONCAT(DISTINCT cr_record.charge_type SEPARATOR "\n"),
              GROUP_CONCAT(DISTINCT cr_record.fir_no SEPARATOR "\n")
FROM
             register_cr as register_cr
INNER JOIN
             cr_record as cr_record ON register_cr.id = cr_record.cri_reco_id
GROUP BY register_cr.id


Difference between Clustered Columnstore Index Table in Azure SQL and Table in Azure Data Warehouse What is the essential difference between a Clustered Columnstore Index table in Regular Azure SQL,  and a table in Azure Data Warehouse?

They both have columnar storage,  no foreign keys, no primary keys , etc. Seems structurally they are the same.
This is what our team is trying to understand.
So what would be the difference?

(IMHO) Azure SQL Data Warehouse is a Massively Parallel Processing (MPP) engine with a shared nothing architecture designed to handle hundreds of terabytes of data.  It does this by dividing your data (either by a distribution key you specify or round robin algorithm) across 60 distributions split across n nodes, depending on the performance tier you are at.  It's really a big data platform and I wouldn't bother using if you have less than say 1-10TB, or your long-term data growth exceeds 4TB.  A recent update (known as Gen 2) adds new service tier levels, increased concurrency and improved caching amongst other things.  I believe they've just announced a cheaper tier at Ignite; details to follow.  SQL DW has a reduced T-SQL surface area and is kind of expensive to keep running 24x7 so you might make use of its pause feature.

Azure SQL DB certainly a powerful database with tier-based service levels but it's capped at 4TB making it a different proposition to SQL DW.  It's targeted at smaller OLTP or BI dbs or warehouses that won't exceed 4TB.  Microsoft have just announced Azure SQL DB Hyperscale in public preview allowing up to 100TB.

Other than the distribution element, I'm not aware of any columnstore-specific differences between the two products.  Bear in mind the massive compression you get with columnstore when estimating the size of your data.

HTH


LEFT JOIN subquery fails in the presence of an EXISTS clause in the main query I have this query:
SELECT c.id contact_id, engagement.sent
FROM contact c
LEFT JOIN (
    /* ➊ Inner query */
    SELECT eq.contact_id contact_id, COUNT(*) sent
    FROM mailing_job j
    INNER JOIN mailing_event_queue eq ON eq.job_id = j.id
    WHERE j.end_date > NOW() - INTERVAL 3 MONTH
    GROUP BY eq.contact_id
  ) engagement ON engagement.contact_id = c.id

 WHERE
  /* ➋ various conditions on table 'c' */
 do_not_email = 0 AND is_opt_out = 0 AND is_deceased = 0 AND is_deleted = 0
   AND contact_type = 'Individual'

   /* ➌ */
   AND EXISTS (
      SELECT gc.contact_id
      FROM group_contact gc
      WHERE gc.contact_id = c.id AND gc.group_id IN (30,386,14,6,214,5,88,361,334,18,9,17,240,7,13,10,292,291,290,12)
    )

   /* ➍ AND c.id=1 */
ORDER BY c.id

The inner query ➊ when run on its own yields results, e.g. sent: 15  for contact 1. This is the correct, expected data.
However, running the whole query gives NULL for the whole sent column! Which is vexing me.
Eliminating the WHERE clauses at ➋, or eliminating the EXISTS at ➌, or including the commented out ➍ clause causes it to behave and return sent: 15 for the test contact.

I'm on MariaDB 10.3.
EDIT
Hoping to add clarity. Fundamentally this is weird -I think- and possibly a bug in MariaDB, though I hope not!
The Inner query ➊ returns rows, e.g.
contact_id  sent
1           15
...

The main query also picks up contact with ID 1.
Now in all my years, when I've left joined a table on a primary key (i.e. c.id) where a record in the subquery matches, it's never changed its values.
The fact that those values come and go with changing other WHERE clauses, but in ways that still mean the outer query generates the matching contact ID, I think, is weird.
I'm really hoping I've done something stupid, but I can't see it.

This looks like a bug. Please send a bug report to MariaDB. Some details:
First, make sure it is a bug.
Query A produces a single row (assuming that contact (id) has a unique (or primary key) constraint:
-- Query A
SELECT c.id contact_id
FROM contact c

 WHERE
  /* ➋ various conditions on table 'c' */
 c.do_not_email = 0 AND c.is_opt_out = 0 AND c.is_deceased = 0 AND c.is_deleted = 0
   AND c.contact_type = 'Individual'

   /* ➌ */
   AND EXISTS (
      SELECT gc.contact_id
      FROM group_contact gc
      WHERE gc.contact_id = c.id AND gc.group_id IN (30,386,14,6,214,5,88,361,334,18,9,17,240,7,13,10,292,291,290,12)
    )

   /* ➍ */
   AND c.id=1
ORDER BY c.id ;

Query B produces one row (with value 15 in sent)
-- Query B
/* ➊ Inner query */
SELECT eq.contact_id contact_id, COUNT(*) sent
FROM mailing_job j
INNER JOIN mailing_event_queue eq ON eq.job_id = j.id
WHERE j.end_date > NOW() - INTERVAL 3 MONTH
  AND eq.contact_id = 1
GROUP BY eq.contact_id ;

Query C produces a single row but with value NULL (or anything different than the 15 in Query B).
-- Query C
SELECT c.id contact_id, engagement.sent
FROM contact c
LEFT JOIN (
    /* ➊ Inner query */
    SELECT eq.contact_id contact_id, COUNT(*) sent
    FROM mailing_job j
    INNER JOIN mailing_event_queue eq ON eq.job_id = j.id
    WHERE j.end_date > NOW() - INTERVAL 3 MONTH
    GROUP BY eq.contact_id
  ) engagement ON engagement.contact_id = c.id

 WHERE
  /* ➋ various conditions on table 'c' */
 c.do_not_email = 0 AND c.is_opt_out = 0 AND c.is_deceased = 0 AND c.is_deleted = 0
   AND c.contact_type = 'Individual'

   /* ➌ */
   AND EXISTS (
      SELECT gc.contact_id
      FROM group_contact gc
      WHERE gc.contact_id = c.id AND gc.group_id IN (30,386,14,6,214,5,88,361,334,18,9,17,240,7,13,10,292,291,290,12)
    )

   /* ➍ */
   AND c.id=1
ORDER BY c.id ;

If the above do happen, it's almost surely either a bug or corrupted data in tables/indexes.
Other things to check / add to the report:

Execution plans for all queries (use EXPLAIN SELECT ...)
Complete CREATE TABLE for all tables involved, including indexes. You can use SHOW CREATE TABLE tablename ; for that.
Your exact version of MariadDB (try select version(); ).
Try dropping and recreating the indexes on the tables involved if possible (not the tables!) and then test again.
If you are not running the latest 10.3, try upgrading to the latest 10.3 if possible, and test again.
If you have available servers with more recent major versions (10.4 or 10.5), try recreating the tables with the same data and test again.
Try to remove conditions (as you did) or rewrite C and test if it produces different plan and correct or wrong results, eg.:

remove each one of these:
  do_not_email = 0 AND is_opt_out = 0 AND is_deceased = 0 AND is_deleted = 0
  AND contact_type = 'Individual'

remove the ORDER BY

remove subqueries 3 and/or 4.

add a condition in b subquery:
  LEFT JOIN (
      /* ➊ Inner query */
      SELECT eq.contact_id contact_id, COUNT(*) sent
      FROM mailing_job j
      INNER JOIN mailing_event_queue eq ON eq.job_id = j.id
      WHERE j.end_date > NOW() - INTERVAL 3 MONTH
        AND eq.contact_id = 1
      GROUP BY eq.contact_id
    ) engagement ON engagement.contact_id = c.id

rewrite the whole query as:
  -- Query Z
  SELECT z.contact_id, engagement.sent
  FROM
      (
      SELECT c.id contact_id
      FROM contact c

       WHERE
        /* ➋ various conditions on table 'c' */
       c.do_not_email = 0 AND c.is_opt_out = 0 AND c.is_deceased = 0 AND c.is_deleted = 0
         AND c.contact_type = 'Individual'

         /* ➌ */
         AND EXISTS (
            SELECT gc.contact_id
            FROM group_contact gc
            WHERE gc.contact_id = c.id AND gc.group_id IN (30,386,14,6,214,5,88,361,334,18,9,17,240,7,13,10,292,291,290,12)
          )

         /* ➍ */
         AND c.id=1
      ) AS z
      LEFT JOIN (
          /* ➊ Inner query */
          SELECT eq.contact_id contact_id, COUNT(*) sent
          FROM mailing_job j
          INNER JOIN mailing_event_queue eq ON eq.job_id = j.id
          WHERE j.end_date > NOW() - INTERVAL 3 MONTH
          GROUP BY eq.contact_id
        ) engagement ON engagement.contact_id = z.contact_id
      ;

combine any or all of the above rewrites and test execution plans and results and add to the report the ones (queries and plans) that fail to produce correct results.

Testing in the latest subversions is recommended, as this may be a bug that has been fixed.


Generating a numeric pattern in query result set I'm looking to add an artificial grouping to my result set. I can't utilize ROW_NUMBER or RANK.

Take the following result from SELECT Col1, Col2 FROM myTbl:

Col1     Col2
----     ----
x        y
x        z
y        x
z        x
x        z
y        x

I want to add a column that will count to 3 and then repeat so I can subdivide the result set if need be:

Divider  Col1     Col2
-------  ----     ----
1         x        y
2         x        z
3         y        x
1         z        x
2         x        z
3         y        x

Of course the easy way would be to loop through the result set with a cursor or temp table and add it on, but when the result set is 4 million rows, performance will be terrible.

Anyone know how to add this Divider column directly into a SELECT statement?

In reality, the query will retrieve the result set ordered by date but I still want the divider to count off in a fixed manner.  If I would try to partition and use row_number the sequence would be unpredictable.  Suppose I wanted to take the larger result set and spread it out over a set of queues but I want them evenly distributed by date to make sure each queue is processing the most recent data. If I would order by date and subdivide only one of the queues would get the most recent data.

I'm not actually returning the date in the result set; I am only ordering by it.

Based on your comments you will be ordering the results by date. In that case you could use

ROW_Number () over( order by date)

Which would output the row numbers.  You could then use the SQL Modulo function, % and perform a %3 which would give you 1,2,0 repeating as the row output which gives you the grouping you want.


Did PostgreSQL write amplification reduction WARM efforts get released in 10 / 11? The MariaDB vs PostgreSQL debate continues to rage in my firm :-( and its fuelled by this somewhat famous uber blog post. I found some interesting discussions about write amplification mitigations here. My question is, did anything like this get release as part of PostgreSQL 10 or 11? I would search the documentation, but I'm not sure what it would be called.

No, nothing major along these lines has been released.


One time data conversion in Oracle tables with excel data First of all I have very basic knowledge in back end

I need to do an one time data conversion in an Oracle table with a excel sheet data.

Excel sheet have data in below format

OldData NewData
12130   12130
12810   12810
17330   17331

So what I need to do is , I have to compare old data with one of the column in an Oracle table(say a colum called Modifydata in masterdaataTable) and if any column value matches with OldData value in excel , then replace that value with Newdata value from Excel sheet.

I have

OldData NewData
12130   12130
12810   12810
17330   17331

Note:

I have tried using external tables. Since I don't have the privileges to create directory and all, it didn't work out.
I have tried "'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\foldername\spreadsheetname.xls;'," logic also.But seems this will work only for MS SQL DB.

If you do not have access to create Directory, then you can use SQL Loader to get data into the database (provided you have Oracle client installed on your system). An outline of the steps is provided below:

a.) Create a table with the same structure as your excel file

b.) Convert your Excel File to .csv format file

c.) Create a Loader Control File. Refer the link SQL Loaer Reference Guide

d.) Go to command line and execute sqlldr command with appropriate format

e.) Verify the bad file and err logs to find if there are any rejections and run them again.

f.) run the update between new table and your existing table.

In case you do not have Oracle Client available, then you can use the data import facility provided in various IDEs like Toad and SQL Developer.


Disk Size Considerations for a SQL Server 2008 R2 database What's the rule of thumb for Disk size considerations for hosting a mirrored database that supports about 100 writes every second during peak. Current database full back up size is approx 150 MB.

Considering (separate) drives for

Data
Log
TempDb

Database size from exec sp_spaceused is (will take care of huge DB size, it's bloated because of mirroring I reckon)

database_name   database_size   unallocated space
-------------------------------------------------
APP_PROD        88026.31 MB     0.00 MB

reserved    data        index_size  unused
--------------------------------------------
122168 KB   118992 KB   2088 KB     1088 KB

Many thanks

You will need exactly the same disk space for your mirrored (secondary) database as for the primary database. They are the same and the secondary database will have the same growth rate as your primary database and should be provisioned the same.

SP_SPACEUSED will show you how much space your primary database is now using (you should make it larger and not rely on autogrowth), and you can grab growth information from the default trace to give you some limited information about how often the database has grown, you can then infer the growth rate from the autogrowth parameters.

DECLARE @path NVARCHAR(260);

SELECT
   @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT
   DatabaseName,
   [FileName],
   SPID,
   Duration,
   StartTime,
   EndTime,
   FileType = CASE EventClass
       WHEN 92 THEN 'Data'
       WHEN 93 THEN 'Log'
   END
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE
   EventClass IN (92,93)
ORDER BY
   StartTime DESC;


Is Synonyms to tables cost effective within SQL Server 2016 For one of our new projects we are thinking of using lots of Synonyms to tables which refer to another Database and we are fully aware on what it does and how we are going to use them. But what we don't know is, are there any hidden overheads on overall SQL server performance? If not at all.

According to this post -

  As a synonym is an abstraction/alternative name for an already
  existing database object, in the case of a table, index behaviour is
  identical to that of the underlying object i.e. when execution plans
  are generated, the same plan is generated irrespective of using the
  table name or corresponsing synonym.

You can verify the execution plans when using both the regular object and the synonym to see if they generate the same plan.

This post has a similar question:

  How does the use of synonyms effect performance?

  More specifically, are synonyms replaced with fully qualified names in
  execution plans? It would seem to me that any synonym has to be
  resolved to the actual object so any query or procedure that doesn't
  have a cached plan is going to have an extra step in mapping the
  synonym to an actual object. I'm thinking there would be a very small
  performance hit in this. Perhaps a slightly larger hit when the
  synonym references an object on a different database and slightly
  larger still when on a different server.

The answer given was

  They are swapped out in the Bind phase of query execution same as a
  view would be expanded. This happens before the Optimization phase
  where the execution plan is generated so while you'll still see a
  reference to the synonym in the query text you will only see
  references to the objects the synonyms point to in the execution
  plan's operators.

  Any performance hit, if you want to even call it that, that might be
  associated with using synonym is not at all worth being concerned
  about.

I'm sure Paul White (or another expert will provide a definitive response).


Always On Availability Group ApplicationIntent=ReadOnly Not routing to Secondary Version: SQL 2012 SP3
Two Node. 1 Primary 1 Secondary

This is how nodes are set up.

Using SSMS connection options when I set connection property ApplicationIntent=ReadOnly I am not getting routed to secondary node. Yes I am using listener name as Server name.

I did not create any read-only routing list because I only have 2 nodes.

Question 1

Am I wrong that even with only 2 nodes I need read-only routing list?

Question 2

What is the difference between these 2 settings about Readable Secondary? Secondaries can only serve read-only traffic, so why route other connections with selecting Yes?

As per books online:

  Read-intent only

  Only read-only connections are allowed to secondary databases of this
  replica. The secondary database(s) are all available for read access.

  Yes

  All connections are allowed to secondary databases of this replica,
  but only for read access. The secondary database(s) are all available
  for read access.

You have to have a read-routing list, even with only two instances, if you want to offload reads to a secondary replica.

The following script is an example of setting this up (you would change the AG name to whatever your AG is, and the replicas to your servernames (using the fully qualified domain name, and adjusting the port if required).

ALTER AVAILABILITY GROUP YOURAGNAME MODIFY REPLICA ON 'REPLICA1' with (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://REPLICA1.FQDN:1433'));
ALTER AVAILABILITY GROUP YOURAGNAME MODIFY REPLICA ON 'REPLICA2' with (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://REPLICA2.FQDN:1433'));

ALTER AVAILABILITY GROUP YOURAGNAME MODIFY REPLICA ON 'REPLICA1' with (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('REPLICA2', 'REPLICA1')));
ALTER AVAILABILITY GROUP YOURAGNAME MODIFY REPLICA ON 'REPLICA2' with (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('REPLICA1', 'REPLICA2')));

As regards the flags:

ReadIntent allows for connections only passed along with the
ApplicationIntent=ReadOnly flag
Yes allows for any connection to the
database (meaning you could connect directly with SSMS and run
queries)


INDEX on a table where I'm only filtering on a row_number() I've got the following table (scaled down to a MWE):
Fiddle here.

CREATE TABLE weekly
( yearnum int,
  weeknum int,
  location varchar2(5),
  weekly_count int,
  weekly_sum int);

INSERT INTO weekly values (2012,1,'X',5,10);
INSERT INTO weekly values (2012,2,'X',6,8);
INSERT INTO weekly values (2012,3,'X',7,14);
INSERT INTO weekly values (2012,4,'X',5,12);
INSERT INTO weekly values (2012,3,'Y',1,22);
INSERT INTO weekly values (2012,4,'Y',5,100);
INSERT INTO weekly values (2012,1,'Z',100,1022);
INSERT INTO weekly values (2012,2,'Z',120,1205);
INSERT INTO weekly values (2012,3,'Z',60,700);

I need the average of the last 26 entries by location, so I've got this:

SELECT
  t.location,
  sum(t.weekly_sum)/sum(t.weekly_count) AS avg_26
FROM
(
  SELECT
    w.location,
    w.weekly_sum,
    w.weekly_count,
    row_number() over (partition BY w.location
                        ORDER BY w.yearnum DESC,w.weeknum DESC) rn
  FROM weekly w
) t
WHERE t.rn <= 26
GROUP BY t.location;

My problem is that it appears this is always going to be a full table scan of weekly.  Is there any index or strategy I could do to improve this, since I can only filter it by the row_number() result?

If we can declare location, yearnum, and weeknum as NOT NULL, you can create a composite index on location, yearnum, weeknum that can be used instead of doing the full table scan.  Here is a sqlfiddle showing that approach.  Note that I applied a hint to force the index to be used since there isn't enough data in the example for a full scan of the index to be less expensive than a full scan of the table.  The index full scan path would probably be more efficient in the scaled up example assuming each location has data for many more than the past 26 weeks.  That's probably not going to be an order of magnitude improvement, though, unless the table is substantially larger than the index.


Why do I not see Configure Distribution when I right-click on replication? I'm using SSMS 2014 for SQL Server 2008 R2.

Why don't I see 'configure distribution' when I right-click on Replication?

I found that the distribution and publication wasn't correctly disabled. When I tried to run the disable the publishing and Distribution (as seen in my image above) I was getting an error that the process couldn't complete because the distribution database was still in use. so I used

SELECT spid FROM sys.sysprocesses WHERE dbid = db_id('distribution')

to find the spid that was using it and then used

KILL <the spid>

I was then able to fully disable the publishing and Distribution and now I do see the configure distribution option.


MySQL database takes too long to load page first time MySQL database takes too long to load page for the first time, especially if I leave the page for an hour or so. Subsequent refreshes after the first one, load immediately. The first time can take 10 seconds or more.

I am just using below web page for simple testing -

select count(*) from $tablename

Could the dB be on idle or something (its seems like it is) and how can I get the first page loading quickly. Could it be a caching setting for the dB? New to this work. Thanks for any help.

in the case of MySQL 5.6 until 8.0, innodb is ready for caching after you restart or start MySQL server with innodb_buffer_pool_dump_now and innodb_buffer_pool_load_now, with this you could save pages cached into buffer pool and then you could load again, avoiding a query takes too long of time to load

Regards


Why can't I establish a connection to the local oracle database from within the pre-built oracle virtual machine? I downloaded DeveloperDaysVM2016-06-02_13.ova from http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html and successfully loaded it into Virtual Box.

I logged in as "oracle" user and I can see the desktop.

From within the virtual machine, I opened Firefox and navigated to:

http://localhost:8080/ords/hrrest/employees/ and successfully received a list of employees in JSON format. So I know the database is up and running.

Now I am trying to connect to this database via the "ojdbc6.jar" Java connector/driver from Netbeans (see image below).  However,

when I enter in

username:hr
password:oracle
SID:orcl12c
URL: jdbc:oracle:thin:@localhost:1521:orcl12c

I get an error message indicating

  "Unable to add connection. Cannot establish a connection to
  jdbc:oracle:thin:@localhost:1521:orcl12c using
  oracle.jdbc.OracleDriver (ORA-01017: invalid username/password; logon
  denied"

But this doesn't make any sense because I was able to successfully login from the terminal:

sqlplus hr

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 7 17:21:07 2016

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

Enter password: oracle
Last Successful login time: Thu Jul 07 2016 16:33:31 -04:00

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

SQL>

Why can't I login via the java connector from netbeans?

I also checked

lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-JUL-2016 17:22:12

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                05-JUL-2016 16:48:08
Uptime                    2 days 0 hr. 34 min. 4 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           orcl12c
Listener Parameter File   /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/vbgeneric/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vbgeneric)(PORT=8081))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "orcl12c" has 2 instance(s).
  Instance "orcl12c", status UNKNOWN, has 1 handler(s) for this service...
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "orcl12cXDB" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "ords" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@vbgeneric oracle]$

So what am I doing wrong? Everything seems to be up and running correctly in this pre-built virtual machine.

This is the same story over and over again. Oracle uses the multitenant architecture in this VM and they set the environment variable TWO_TASK. This causes a lot of confusion.

When you connect as sqlplus hr, the value of TWO_TASK is automatically appended to the connection string, and you are connecting to SERVICE_NAME=ORCL and not SID=ORCL12 or SERVICE_NAME=ORCL12. The user hr was created in the ORCL pluggable database. With SID=ORCL12 or SERVICE_NAME=ORCL12, you connect to the root container, where this user does not exist, hence you receive the ORA-01017 error.

So instead of the connection string:

jdbc:oracle:thin:@localhost:1521:orcl12c

Use this for connecting:

jdbc:oracle:thin:@localhost:1521/orcl

Note the /orcl instead of :orcl12c.

: specificies an instance connection (SID), while / speficies a service connection (SERVICE_NAME). You have to specify the service name when connecting to a pluggable database, because multiple pluggable databases share the same instance, and the name of the service is what differentiates them.


MSSQL Server Upgrade, go for latest or version before that A dilemma probably faced by quite a few DBAs. Does one upgrade to the latest version, in this case SQL SERVER 2016, or play it safe and upgrade to 2014, which hopefully has most bug fixes in place by now?

I will only be upgrading in Standard Edition and from what I've read there isn't much between 14 & 16 in terms of functionality, enterprise is a different story.
The only things of interest to me at the moment in 2016 are probably the query store features and possibly the tabular BI stuff.

I have read 2016 does use fewer resources and depending on the tasks it executes faster, which is obviously a big bonus.

At the moment I am leaning towards 2016 as opportunities to upgrade are few and far between, just worried about potential bugs and applying them in 24/7 up time environment with no maintenance window.

Would be great to hear if anyone else has faced the same/similar upgrade path, and ultimately what decision they took.

I will probably get a down vote as there is no right answer...but its a question that must be asked a lot..

You're asking for opinions, so here's mine...

Upgrading Sql Server is a labor intensive process with tons of planning and testing.  I like to minimize the number of times I have to do that.  If I were in your shoes, I would definitely opt for 2016 and test, test, test in non-production environments.


Query performance of index scans slower than parallel seq scan on Postgres I've been trying to debug a performance problem on PostgreSQL 10 on AWS Aurora.
Specifically I'm trying to understand performance problems on "cold" queries where the buffer cache does not contain cached pages.
I have a table with an index and when the planner uses the index the query is substantially slower (spending more time on IO from what I can tell). However, when I drop an index the query is faster, it uses a parallel sequential scan.
The Schema of the table:
CREATE TABLE max_test_2018_3
(
   timestamp_         timestamp,
   person_alias       varchar(128),
   visitor_id         varchar(128),
   session_id         varchar(24),
   page_title         varchar(1024),
   location_host      varchar(256),
   location_path      varchar(1024),
   location_query     varchar(1024),
   location_fragment  varchar(1024),
   referrer_host      varchar(256),
   referrer_path      varchar(1024),
   referrer_query     varchar(1024),
   referrer_fragment  varchar(1024),
   duration           integer,
   location_secure    boolean,
   referrer_secure    boolean,
   id                 uuid,
   person_id          uuid,
   custom_properties  jsonb
);

The index DDL is:
CREATE INDEX max_test_2018_3_ix   ON max_test_2018_3
  USING btree  ( location_path varchar_pattern_ops);

And the query is:
explain (analyze, buffers)
SELECT O.person_id, 1 as step1, min(timestamp_) AS timestamp_
FROM max_test_2018_3 O
WHERE O.location_path  like '/tax-calculator%'
GROUP BY O.person_id

Check the following two explains - note that both explains show minimal or no buffer hits. One shows the execution with the index and the other shows execution after the index has been dropped:
No index -> https://explain.depesz.com/s/N4uo
With index -> https://explain.depesz.com/s/69WS
The plan that includes the index scan takes 193 seconds vs 43 seconds for a sequential scan.
Why is step 2 in the "With-index" plan so slow?  It reads in only 118372 buffers (about 118 MB of data), yet this step took 193 seconds.  Why?  When the buffer cache is warmed up the same with-index query takes a couple seconds.  So we must concede that the IO is responsible for this extra time.
I've run this experiment several times on multiple similar datasets.  Here is another example that demonstrates index scan operations that require IO and do not benefit from buffer cache.
No index -> https://explain.depesz.com/s/g6JU
with Index ->https://explain.depesz.com/s/Sw6Q
Again, you can see that the index scan is unreasonably slow.  The planner attempts to use the index because it believes it will speed up the query, yet such queries are consistently slower.
With regard to parameters that affecting the planner I've set the following two:
random_page_cost = 1
seq_page_cost = 1

The rationale being that for an SSD drive the random_page_cost does not require seeking and so can be set to the same value as seq_page_cost.
The table is vacuumed with statistics up-to date.  The table max_test_2018_3 contains approximately 18 million rows and is about 9GB on disk.  The above index on that table is 440 MB.
Why do the plans that use an index scan perform so poorly?
I/O Timing enabled Plans

with index             https://explain.depesz.com/s/84kE no
no index(parallel scan) https://explain.depesz.com/s/CRBp

Tweeking parallelism settings
The explains above have default parameter settings with respect to parallelism. The only two exceptions are:
max_parallel_workers = 8
max_parallel_workers_per_gather = 4

As per the suggestions, I've experimented with with the following settings:
parallel_setup_cost = 10
parallel_tuple_cost = 0.001

Defaults for these are 1000 and 0.1 respectively.  These changes did not affect the plans and PostgreSQL still chose to use the index even though a parallel sequential scan is faster.
The index size is 440MB, far larger than the default min_parallel_index_scan_size of 512kB

All your time is spent doing table I/O, which is to be expected in a test with a cold cache.

The index scan takes longer, but only because it is not parallelized. The sequential scan uses more total time, but since it is run on 5 cores in parallel, it finishes faster.

PostgreSQL v10 can perform parallel index scans, so the remaining riddle is why it doesn't. A side riddle is why the sequential scan uses so many parallel workers.

I suspect that you messed up the configuration parameters. The relevant parameters are:

max_parallel_workers_per_gather: limits how many workers can be used. You must have set this to 4 or more.
min_parallel_table_scan_size: if a table is bigger than that, a parallel worker is planned. If the table size exceeds 3n-1 times that value, n parallel workers are planned. So either your table is very big, or you reduced the parameter. Alternatively:
The storage parameter parallel_workers on the table overrides the calculation based on min_parallel_table_scan_size as described above, so maybe you set that.
Finally, min_parallel_index_scan_size governs when a parallel index scan is considered. Either the index is small, or you lowered the parameter.

I'd expect that the index scan will be faster once you set these parameters to good values.


SQL Server Transactional Replication between SQL server 2019 Standard and SQL server 2016 Web Edition We have SQL Server 2016 Web Edition on production currently. Since this edition supports replication as Subscriber only, I set up a new server with SQL Server 2019 Standard edition. I want to configure SQL Server 2019 as Publisher and SQL Server 2016 as subscriber. To initialize data for Publisher database on SQL server 2019, I created a backup on SQL Server 2016 and restored it on SQL Server 2019. Since our database is very large, I tried to initialize replication from a backup. So I did reverse backup-restore again by creating a backup of Publisher on SQL Server 2019 and restoring it on Subscriber on SQL Server 2016. But this did not work because [SQL Server 2019 backups cannot be restored by any earlier version of SQL Server] (https://docs.microsoft.com/en-us/sql/relational-databases/databases/copy-databases-with-backup-and-restore?view=sql-server-ver15). Could you please tell me what is the best method for initializing the replication in this case?
Thank you very much for reading my question!

After doing some research I go with Initialize a Subscription Manually


SQL server database Role management - Azure SQL MI In my database I have multiple users with SQL server authentication login and AD login.

For ex: User 1 and user 2 are from same team(ABC), if I create a role db_abc

User 1 needs read access to table a and table b.
User 2 needs read access to table a, b and additionally to table c.

How do I manage this case? Should permissions be given at user level than to roles.?
What is the best practice in managing users and roles?

What is the best practice in managing users and roles?

Create custom roles and grant permissions to the roles; then add users to the roles.

eg:

create role reader_abc
grant select on dbo.a to reader_abc
grant select on dbo.b to reader_abc
grant select on dbo.c to reader_abc

create role reader_ab
grant select on dbo.a to reader_abc
grant select on dbo.b to reader_abc

alter role reader_abc add member alice
alter role reader_ab add member joe

You can also assign permissions to an entire schema:

grant select on schema::dbo to report_users;

Or the whole database:

grant select to report_users;