Case when missing line logic I have a case when statement in a extract query which doesn't seem to be returning what i'm expecting

DECLARE @Val1 int, @Val2 int,@Val3 int, @Val4 int, @Val5 int

SELECT  @Val1 = 5 , @Val2= 2,@Val3= 200000, @Val5  = 2,@Val4 = 2014
SELECT  @Val1 , @Val2,@Val3, @Val4, @Val5

SELECT CASE

        WHEN  @Val1 = 5 AND @Val2  IN(1,2) AND @Val3 = 200000 AND @Val4 IS NULL  THEN 'ReturnValue1'
        WHEN  @Val1 = 5 AND @Val2  IN(1,2) AND @Val3 = 200000 AND @Val4 IS NOT NULL THEN 'ReturnValue2'
        WHEN  @Val1 = 5 AND @Val2  = 2 AND @Val3 = 200000  AND @Val5 = 2 AND @Val4 IS NOT NULL THEN 'ReturnValue3'
        ELSE NULL
    END [TestingValue]

I'm expecting the value to be ReturnValue3 however it appears to be ignoring this whole line for some reason.

Can anyone see a problem which I'm not spotting.

Your second clause is triggering as true before your third clause and as such your third clause does not get evaluated - CASE WHEN returns the first TRUE value as opposed to the last TRUE value

@Val1 = 5 TRUE

@Val2  IN(1,2) TRUE

@Val3 = 200000 TRUE

@Val4 IS NOT NULL TRUE


View scripts ran by a specific user? I read in a stackoverflow post that you can restore scripts from the past 24 hours by using this query:

Use <database>
SELECT execquery.last_execution_time AS [Date Time], execsql.text AS [Script] FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
ORDER BY execquery.last_execution_time DESC

But I want to see only queries which a specific user ran, how can I perform this?

In order to achieve it, you'll need to capture this information at run time using DMV's or Extended Events.


SSIS to Progress Openedge I have a progress openedge server which I have an ODBC connection to. I can read and write to the server through ODBC with SSIS from a MSSQL database. I've used SSIS to write to a test database but because I now want to write to live I'm more hesitant I've been trying to find documentation from progress to give me a step by step, what to look out for etc.

I can't find this, I can only find Progress to MSSQL but I've checked the collation and it seems compatible iso8859-1 to Latin. I've never worked with progress before so I'm now looking at using ABL but if using SSIS is ok I would prefer to use this method.

If anyone has used this method before I would appreciate some advice. My main concern is does the ODBC connection work well the other way around.

I don't see any issues from my understanding of databases, if I can populate the fields with data that conforms to the constraints and the conversion doesn't drop 0s or any other corruption once the data is there an application will pick it up.

The main issue I've read about is if the collation is different or incompatible I need to make sure it's converted but I'm confident this isn't an issue.

Any responses are welcome, I'm probably over thinking this.

Progress' collation tables are to control how indexes are sorted and assigned and some character handling if you are operating across several locales. Open Edge comes with a variety of collation tables for popular languages. The purpose is to help with sorting and comparisons.  You can specify collation tables upon startup.  If you do not but name a database in startup Progress will use the collation table for that database.  If you do not specify either then Progress will use a collation table called BASIC.

As for MS SQL: both databases have data in the same language, correct? And your ODBC test was from SQLServer -> Open Edge which was successful.  Some things to watch out for:

If you are using any "only in SQLServer" datatypes the data could get
corrupted.  Open Edge has a match for all types they say, but I have
not tried them all.  If you are sticking with the standard ones
VARCHAR, INT, NUMERIC, DATE, TEXT you should be fine.  If you have
any binary columns Open Edge will convert those to character, so they
will not operate the same.  The Open Edge documentation lists the
datatype equivalents (https://www.progress.com/documentation).

In case you are interested, Open Edge does have a SQLServer specific DataDirect branded ODBC driver you can use. It is intended to make connections to SQLServer easier.

Make sure you have the Open Edge driver on the MS ODBC Data Source    Administrator box you will use.
I've heard of issues with the 64 bit versions of various drivers and    the ODBC Data Source Administrator.  I do not know if they are now    fixed in late 2017, but if you have the 32-bit versions they may be    more compatible all around.

How To options:

You can use SSIS. Build your package and run it to import the data into SQL Server.  If you want to try any automation make sure that the SQL Server Agent job runs on the same server as your ODBC Data Source Administrator and the user has the privileges to all of those areas.
You can use the SQL Server Import/Export wizard to move your data. This might be the easiest way.  Setup your data source and target and import data from Open Edge.  They do note that any binary data will be converted to "datatype 23", and you have to manually go to each column definition and set the datatype in the dropdown to binary.  To play it safe, check the datatypes after you import.
Another option, if you wanted to go this way, is to use free ETL tools to move your data.  You don't say how much, how complex or how often; but ETL is another choice. This would remove the connection concerns since these packages typically come with a variety of connectors for many types of data sources.  If that interests you, you can check out this ETL wiki for options.


Very slow DELETE in PostgreSQL, workaround? I have a database on PostgreSQL 9.2 that has a main schema with around 70 tables and a variable number of identically structured per-client schemas of 30 tables each. The client schemas have foreign keys referencing the main schema and not the other way around.

I just started filling the database with some real data taken from the previous version. The DB had reached about 1.5 GB (it's expected to grow to several 10s GB within weeks) when I had to do a bulk delete in a very central table in the main schema. All concerned foreign keys are marked ON DELETE CASCADE.

It was no surprise that this would take a long time but after 12 hours it became clear that I was better off starting over, dropping the DB and launching the migration again. But what if I need to repeat this operation later when the DB is live and much larger? Are there alternative, faster methods?

Would it be much faster if I wrote a script that will browse the dependent tables, starting at the table furthest from the central table, deleting the dependent rows table by table?

An important detail is that there are triggers on some of the tables.

I had a similar problem. As it turns out, those ON DELETE CASCADE triggers were slowing things down quite a bit, because those cascaded deletions were awfully slow.

I solved the problem by creating indexes on the foreign key fields on the referencing tables, and I went from taking a bunch of hours for the deletion to a few seconds.


How to get OS user in Postgres? The title says it all. In Oracle, we can easily get the OS user using:

select sys_context( 'userenv', 'os_user' ) from dual;

I usually use that for logging purposes (know who inserted/updated what and when).
I searched for something similar for Postgres (I am new to it) but I could not find. Isn't that possible?

In general, if Postgres does not allow me to get the OS user, does it allow retreiving any kind of information outside the DB itself (e.g.: IP of host, .. etc.)?

PostgreSQL has few functions reporting information on the client environment, listed at
https://www.postgresql.org/docs/current/static/functions-info.html

Concerning the remote IP address, it provides inet_client_addr().

Concerning the client username, I don't think it has this information at all, because it's not passed at connection time, unless it happens to be the same as the database user name, but the backend can't know that in general.

If the SQL functions need that information,it could be passed up front by your applications in the form of a SET statement. Current versions of PostgreSQL allow to instantiate any namespace for custom variables.

Example with version 9.3:

test=> set session.osuser to 'daniel';
SET

test=> show session.osuser;
 session.osuser
----------------
 daniel
(1 row)

test=> select current_setting('session.osuser');
 current_setting
-----------------
 daniel
(1 row)


Tail log backup or use log backup after putting DB into single user mode? I plan to migrate existing SQL Server 2008 DB to new server (has 2017 engine) with minimum down time.  I will do full restore, then log restores (all with norecovery) on the new server up until the time of migration.  I have tested the restore process using my plan which is this:

For the final log backup, my plan now is to set the database into single user mode, then do a "regular" log backup. Do I need to do the "tail" log backup (add NORECOVERY option) while taking this last log backup? if so, then why? If the DB is set into single user mode which means that no one except me can access database, can I omit using NORECOVERY?  I use Ola's scripts to do log backups, and would rather just run my "regular" log backup job after the DB is in single user mode, copy that backup to the new server, and do the final restore.

Bottom line - is there a difference between log backup in single user mode and a tail log backup?

If the DB is set into single user mode which means that no one except me can access database, can I omit using NORECOVERY?

If you are connected to a different database, or when you eventually disconnect any other user can access the SINGLE_USER database.  A Tail Log backup leaves the database in the RESTORING state.  This guarantees that no users can accidentally read or write data to the old database after the cut-over.


Copy files using script to a network share Is there a simple script I can use to copy files in a particular folder to a network share? This is for use with SQL Server Agent so I can run it as a step after backups.

Thanks

This is (part of) a script that I use to "archive" files that were processed by SQL Server. I actually mapped the network drive instead of moving to a UNC because it was just more stable.

A few things to note:

The service account under which the SQL Agent runs must have
permissions on both the source and the target (I'm assuming the
target would be the one you'd really need to check, unless the Agent
account can't delete from the local store).
This script MOVEs the files. If you want to copy them, then I'd recommend looking into replacing the command in the last step with XCOPY

    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB',
       @type=N'LOCAL',@name=N'[Uncategorized (Local)]'

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    END

    DECLARE @jobId BINARY(16)
    EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'File Mover',
            @enabled=1,
            @notify_level_eventlog=0,
            @notify_level_email=2,
            @notify_level_netsend=0,
            @notify_level_page=0,
            @delete_level=0,
            @description=N'No description available.',
            @category_name=N'[Uncategorized (Local)]',
            @owner_login_name=N'[username]',
            @notify_email_operator_name=N'alertteam', @job_id = @jobId OUTPUT

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Archive Files',
            @step_id=3,
            @cmdexec_success_code=0,
            @on_success_action=1,
            @on_success_step_id=0,
            @on_fail_action=2,
            @on_fail_step_id=0,
            @retry_attempts=0,
            @retry_interval=0,
            @os_run_priority=0, @subsystem=N'CmdExec',
            @command=N'move \source\path\*.csv \dest\path
    ',
            @flags=0


GeoJSON MultiLineString import to MySQL with SRID/CRS 27700, 'out of range' Version 8.0.20
I'm importing a single link of the OSM road network with the intention of creating a buffer in MySQL.
This is it:
select st_srid(st_geomfromgeojson('{ "type": "Feature", "properties": { "osm_id": "200020986" }, "geometry": { "type": "MultiLineString", "coordinates": [ [ [ 440417.0, 192697.0 ], [ 440386.0, 192596.0 ], [ 440361.0, 192416.0 ], [ 440353.0, 192277.0 ], [ 440331.0, 192121.0 ], [ 440327.0, 192040.0 ], [ 440330.0, 191981.0 ], [ 440341.0, 191949.0 ], [ 440433.0, 191817.0 ], [ 440494.0, 191703.0 ], [ 440506.0, 191688.0 ] ] ] } }'),27700)
into @foo

the easting and northings are within OSGB
the srid is being defined as 27700

and yet I get back

Error Code: 3616. Longitude 440417.000000 is out of range in function st_geomfromgeojson. It must be within (-180.000000, 180.000000].

It looks like its ignoring the 27700 and trying to encode into 4326/WGS84 and clearly failing.
Any ideas please?

The way I've found to work around this to use ogr2ogr to import the eastings and northings (in a geojson file). An example code would be as follows:
ogr2ogr -f MySQL MySQL:test,user=root import_file_location \
-nln FILENAME -update -overwrite -lco GEOMETRY_NAME=MULTIPOLYGON

This will import as British National Grid coordinates, which can then be used to create buffers in MySQL.


Connecting to remote oracle db I am very new to system and db admin stuffs. Infact, I need to have my skills more improved in those areas. Anyways, I was trying to connect to a remote oracle database and use php oci8 library to talk with that db.

I installed the oracle basic package, sdk and sql plus. For testing the connectivity I ran sqlplus command from /usr/bin which prompts for username and password, If I provide the remote db username and pass it throws an error like this: ORA-12162: TNS:net service name is incorrectly specified.

Also, once I installed oci8. But it seems like oci8 functions are not recognized since the library is not properly installed? How can I check if the library is properly installed?

Update: My OS is Mountain Lion 10.8.4,
PHP version is 5.4.10,
SQL*Plus: Release 11.2.0.3.0 Production

The error  ORA-12162 is return because the client (sqlplus) doesn't manage to resolve the tns name provided:

 sqlplus user/passwd@ALIAS_TNS

The ALIAS_TNS have to be described in the tnsnames.ora file on the client side. Usually, this file is under ORACLE_HOME/network/admin.

Note that since Oracle 10g another connection syntax exist and does not require the tnsnames.ora. the syntax is:

sqlplus username/password@[//]host[:port][/service_name]


Can I extrapolate from the progress reporting in MyISAM ALTER? I'm migrating a 300 million row MyISAM table to add some indexes.

Just because it might be relevant, here's my ALTER statement:

ALTER TABLE my_table
    ADD UNIQUE INDEX my_table_abcd (a, b, c, d),
    ADD INDEX my_table_abc (a, b, c),
    ADD INDEX my_table_c (c),
    ADD INDEX my_table_ce (c, e),
    ADD INDEX my_table_d (a);

It currently says

Stage: 1 of 2 'copy to tmp table'   74.8% of stage done

and the processlist says that the progress of the ALTER statement is 37.440.

I don't remember the exact timings, but the 'Stage 1' value jumped up to 60% in the first hour or so, and has been I crawling up to 70% in the last 12 hours. In the lat 3 hours it has increased 1.5%.

I have a decision to make about whether I abort this and try changing some configuration settings or let it run. It's not linear based on previous observations. If it's slowing down exponentially then it's a waste to let it continue when I could be trying another approach.

Any ideas for bases on which to make a decision?

NB this question is relevant but not a duplicate. I'm not asking about predicting the time, I'm asking about whether the progress report has a linear relationship with time.

You have redundant indexes

Instead of your ALTER TABLE

ALTER TABLE my_table
    ADD UNIQUE INDEX my_table_abcd (a, b, c, d),
    ADD INDEX my_table_abc (a, b, c),
    ADD INDEX my_table_c (c),
    ADD INDEX my_table_ce (c, e),
    ADD INDEX my_table_d (a);

you only need to two of the indexes

ALTER TABLE my_table
    ADD UNIQUE INDEX my_table_abcd (a, b, c, d),
    ADD INDEX my_table_ce (c, e);

To verify this, download pt-duplicate-key-checker and run it against your table, and you will see its recommendation to remove those three redundant indexes. See my post Mysql might have too many indexes on how to do this.

BTW, that old post of mine you cited no longer applies because MySQL now applies all structural changes into a single temp table before loading the data into it.

As for the operation itself, please abort it and start over with my shortened version.


Error Updating SSIS package to 2014 due to script component I am trying to update a SSIS package that contains script component. The script component just gets the error description as described here.
I am using the update wizard in SSDT 2015 which should be compatible with SQL Server 2014. (ssdt)
After the Package update wizard runs, I get a warning and information for every data flow that has a script component.

Warning 0x40016044: Load table TableName: Found SQL Server
Integration Services 2012 Script Component TableName Error
Desc that requires migration!
Information 0x4001601a: Load table TableName: The
TableName Error Desc has been migrated. The package must be
saved to retain migration changes

This looks to me like the script component has been migrated successfully. I then build and deploy the package to my SSIS instance.
When I try to execute the package on the instance, via SQL agent or Integration Services Catalog, the package fails verification with the following errors.

Load table PersonDisability:Error:
Microsoft.SqlServer.Dts.Pipeline.ComponentVersionMismatchException:
The version of tablename Error Desc is not compatible with this
version of the DataFlow.  [[The version or pipeline version or both
for the specified component is higher than the current version.  This
package was probably created on a new version of DTS or the component
than is installed on the current PC.]]    at
Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostCheckAndPerformUpgrade(IDTSManagedComponentWrapper100
wrapper, Int32 lPipelineVersion)
Load table TableName:Error: The component metadata for
"TableName Error Desc" could not be upgraded to the newer
version of the component. The PerformUpgrade method failed.

Based on these, if the messages are accurate, I have a version mismatch. My instance is 2014 SP1 CU3 (12.0.4427.24) and my SSDT that I did the package update with is 2015(14.0.51128.0)
Are these versions incompatible?

VS 2010/VS 2012 => SQL Server 2012.

VS 2013 => SQL Server 2014.

VS 2015 => SQL Server 2016 (for now, soon they'll have the ability to target SQL Server 2012+)

So, you either need to redo your imports using SSDT for VS 2013/SQL Server 2014 or wait until they deliver the next release candidate for SQL Server which will have the ability to target specific releases of SSIS


extract State Entity from String using regex_substr Let's say I have the following column of city, state, and zip values

╔═══════════════════════════════════╗
║ CITY_STATE_ZIP_COLUMN             ║
╠═══════════════════════════════════╣
║ ARLINGTON WASHINGTON 98223        ║
║ MONTGOMERY VILLAGE MARYLAND 20886 ║
║ BROOKLYN NEW YORK 11233-0000      ║
║ CHARLESTON MISSISSIPPI 38921-0000 ║
║ AUGUSTA GEORGIA 30909             ║
║ CLINTON SOUTH CAROLINA 29325-0000 ║
╚═══════════════════════════════════╝

I'm trying to figure out how to write a oracle SQL statement to extract just the state.

I can figure out how extract the ZIP using the following:

SELECT REGEXP_SUBSTR(CITY_STATE_ZIP_COLUMN,'(\d)(\d)(\d)(\d)(\d)')
FROM My_Table

HOWEVER, For the life of me, I can't figure out how I can use REGEXP_SUBSTR to get ONLY the state.

Is there a more efficient way to get the state perhaps using a combination of SUBSTR and INSTR functions?

You could create a table with a list of state names, and use that to cross reference against the list.

Something like:

SELECT s.state_name
FROM states s
    INNER JOIN addresses a ON a.address LIKE '% ' || s.state_name || ' %';

Since you cannot create tables, you could use a CTE with defined values for each state name, something like:

WITH states (state_name) AS
(
    SELECT 'Alabama' FROM dual
    UNION ALL SELECT 'Georgia' FROM dual
    UNION ALL SELECT 'Alaska' FROM dual
    ...
)
SELECT s.state_name
FROM states s
    INNER JOIN addresses a ON a.address LIKE '% ' || s.state_name || ' %';

I'm not an Oracle rockstar; who knew || was concatenation


Where and with what should I be looking for a good hardware backup tool? Until now, I was doing my SQL Backups using USB Hard disk, and copying that to my local computer. After that, I went zipping and burning onto DVDs.

Now I want to push that on more professional and more robust level.

What kind of hardware is best for storing and backuping SQL databases ?

My environment looks like:

two MS-SQL-Server 2008, One production database and other replicated for reporting
My apps is not mission critical and I can afford my self down time even and for 20 hours.
My main database is around 32GB when is in backup, others is around 10GB.
I think that I do not need large and long historical data, two or tree snapshots should be OK for me.

Here are further questions:

Should I looking for hardware with TAPES?
Does NAS (network attached storage) provide a good solution ?
If I go with NAS beside RAID, to what should I pay more attention?
Which protocol is essential for NAS so SQL can do backups easily?
Today's SHOHO NAS servers is pretty cheap and available, Should I avoid them ?

Any other suggestions is much appreciated.

My opinion:
A NAS with RAID 5 would be a good solution. This would give you decent fault tolerance without costing too much space. You can even get NASes that support AD authentication, thereby taking your security up one level. This will take care of your onsite backup needs.

Tape backups will provide you with an offsite storage solution, but the price difference is much more for the tape drive and management software.

Don't go for the cheapest NAS hardware. That was a decision taken by the management at my previous company. Due to inconsistent behavior/performance they dropped the solutions after paying full price for implementing NASes in 11 divisions. Check out reviews about the NAS before buying them.


How to script statistics in Sql Server? (using T-SQL) there is a very similar question here:

Create SQL Script for Statistics

But that is not what I want.

I want to get back exactly what I have put in.
For example:

CREATE STATISTICS [_MM_STATS__745366020_7_1_4_5_2_3]
ON [dbo].[ProductShipTax]
([TaxRegionId], [ProductShipTaxID], [TaxRate], [ItemNo], [DateFrom], [DateTo])

I would like a query that would allow me to get the exact script above so that I could for example, apply it to a different database.

would this be possible?

A 2005+ compatible option that supports  this subset of the grammar is below.

CREATE STATISTICS statistics_name
ON { table_or_indexed_view_name } ( column [ ,...n ] )
    [ WHERE <filter_predicate> ]
    [ WITH
        [ STATS_STREAM = stats_stream ]
        [ [ , ] NORECOMPUTE ]
        [ [ , ] INCREMENTAL = { ON | OFF } ]
    ] ;

NB: Support for 2005 is somewhat limited however. The product itself doesn't support incremental or filtered statistics and I haven't introduced support for stats stream as it would need one of the kludgy workarounds to convert binary to string before this functionality was added to CONVERT.

DECLARE @Schema             sysname,
        @Table              sysname,
        @StatsName          sysname,
        @IncludeStatsStream BIT,
        @StatsStream        VARCHAR(MAX),
        @TwoPartQuotedName  NVARCHAR(500);

select  @Schema           = 'dbo',
        @Table            = 'T1',
        @StatsName        = 'S1',
        @IncludeStatsStream = 0;

select @TwoPartQuotedName = QUOTENAME(@Schema) + '.' + QUOTENAME(@Table);

IF @IncludeStatsStream = 1 AND @@MICROSOFTVERSION/ POWER(2,24) > 9
  BEGIN
      DECLARE @StatsResults TABLE
        (
           StatsStream VARBINARY(MAX),
           Rows        BIGINT,
           DataPages   BIGINT
        );

      INSERT INTO @StatsResults
      EXEC sys.sp_executesql
        N'DBCC SHOW_STATISTICS(@TwoPartQuotedName, @StatsName) WITH STATS_STREAM;',
        N'@TwoPartQuotedName NVARCHAR(500), @StatsName sysname',
        @TwoPartQuotedName = @TwoPartQuotedName,
        @StatsName = @StatsName;

      --Would need some other method on 2005 hence just skipping this
      SELECT @StatsStream = CONVERT(VARCHAR(MAX), StatsStream, 1)
      FROM   @StatsResults;
  END;

WITH stats AS
(
/*
Support earlier versions without these columns using trick from http://dba.stackexchange.com/a/66755/3690 */
SELECT x.*
FROM (SELECT NULL AS filter_definition, NULL AS is_incremental) AS dummy
CROSS APPLY
(
  SELECT object_id, stats_id, name, no_recompute, filter_definition, is_incremental
  FROM sys.stats
) AS x
)
SELECT '
CREATE STATISTICS ' + QUOTENAME(name) + '
ON ' + @TwoPartQuotedName + ' (' + SUBSTRING(cols, 2, 10000000) +')
'  +
ISNULL(' WHERE ' + filter_definition,'') +
ISNULL(STUFF (
    ISNULL(',STATS_STREAM = ' + @StatsStream, '') +
    CASE WHEN no_recompute = 1 THEN ',NORECOMPUTE' ELSE '' END +
    CASE WHEN is_incremental = 1 THEN ',INCREMENTAL=ON' ELSE '' END
 , 1 , 1 ,  ' WITH '  ) , '') AS [processing-instruction(x)]
FROM   stats s
       CROSS APPLY (SELECT ',' + QUOTENAME(c.name)
                    FROM   sys.stats_columns sc
                           JOIN sys.columns c
                             ON c.object_id = sc.object_id
                                AND c.column_id = sc.column_id
                    WHERE  sc.object_id = s.object_id
                           AND sc.stats_id = s.stats_id
                    ORDER  BY sc.stats_column_id
                    FOR XML PATH(''))CA(cols)
WHERE  s.object_id = OBJECT_ID(@TwoPartQuotedName)
       AND s.name = @StatsName
FOR XML PATH('');


mysqlservice command syntax I'm new to MySQL.

I've found myself in the position of having two different versions running on one host.

I'm a little unsure of how these are working together.  Do I issue >service mysqlservice 3307 start?

Where does mysql3307 come into play?

This sounds a lot like something I created back in February 2011. In fact, I placed the code for the mysqlservice script in one post : Running multiple instances on the same host (Sep 30, 2011)

The mysqlservice script is a quasi-service I created to help set up multiple mysql instances. The idea is to create

my.cnf for a specific port number
service filename to launch the service

In this case, you should see a file called /etc/init.d/mysql3307. If you do not have one, please create it. It should look like this :

#!/bin/sh
#
# readahead:    Prereads programs required for startup into memory
#
# chkconfig: 2345 4 99
# description:  This service causes the programs used during startup \
#               to be loaded into memory before they are needed,\
#               thus improving startup performance
#
#

# Sanity checks.
[ -x /usr/sbin/readahead ] || exit 0

# Check for > 384 MB
#free -m | gawk '/Mem:/ {exit ($2 >= 384)?0:1}' || exit 0

# Source function library.
#. /etc/rc.d/init.d/functions

WHICH=/usr/bin/which
SERVICE=`${WHICH} service`

MYSQL=`${WHICH} mysql`
PORT_NUMBER=3307

# See how we were called.
case "$1" in
    start)      ${SERVICE} mysqlservice ${PORT_NUMBER} start    ;;
    stop)       ${SERVICE} mysqlservice ${PORT_NUMBER} stop     ;;
    status)     ${SERVICE} mysqlservice ${PORT_NUMBER} status   ;;
    restart)    ${SERVICE} mysqlservice ${PORT_NUMBER} restart  ;;
    mycnf)      ${SERVICE} mysqlservice ${PORT_NUMBER} mycnf    ;;
    edit)       ${SERVICE} mysqlservice ${PORT_NUMBER} edit     ;;
    *)
        echo $"Usage: $0 {start|stop|restart|status|mycnf}"
        ;;
esac

After creating the file, please run

chmod +x /etc/init.d/mysql3307

Now, the service can be summoned with

service mysql3307 start

Under the hood, it will call

service mysqlservice 3307 start

You could call it that way as well. Yet, setting up /etc/init.d/mysql3307 makes the service have a name that's easy to remember.

Give it a Try !!!

CAVEAT : You have to create a my.cnf file for port 3307 called /etc/my3307.cnf

The file /etc/my3307.cnf must include

[mysqld]
port=3307
datadir=<whatever folder you have for 3307's mysql instance>

The fundamentals difference between services are

datadir
port number

You can start and stop the services independent of each other. While mysqld_multi allows you to have multiple MySQL Instances under one my.cnf via setting up mysqld groups, I created the mysqlservice quasi-service to use distinct my.cnf files for each instance. That way, you do not have to worry setting up my.cnf to know  what is common to all MySQL Instances vs. what is unique to each MySQL Instance.

If you setup more than two services in this manner, you become responsible for assigning the proper amount of RAM for buffers, caches, and threads.


Granting only SELECT permissions on specific views to a specific user I have landed in a scenario were I have to let a user inside a database which is not his. Hence I want to make sure that he is only able to SELECT from the views that I deem he can read from.

I have googled and tested, and it seems like I have found a solution, but I want to make sure that I'm right.

So below should give me UserA with full rights to DatabaseA, and only SELECT permission to the 2 views in DatabaseB.

Is this correct, or am I opening up Pandora's box?

CREATE LOGIN UserA with password = 'UserA'

use DatabaseA
GO
create user UserA for login UserA
GO
EXEC sp_addrolemember N'db_owner', N'UserA'
GO

use DatabaseB
GO
create user UserA for login UserA
GO
grant select on DatabaseB.dbo.v_ViewForUserA1 to UserA
GO
grant select on DatabaseB.dbo.v_ViewForUserA2 to UserA
GO

Yes.  What you have will do what you want.  I do question the need for UserA to have db_owner access even on database A though.  Do they have a need to be able to drop the database?  Run backups that could interrupt your backup chain?  Change recovery model (same problem)?

Generally even if a developer has complete control over a given database I would still limit them to db_datareader, db_datawriter, db_ddladmin and EXECUTE (database level).  With very few exceptions that's sufficient for a development environment.  Permissions of course get fewer the higher up the SDLC stack you go.


Should Archive Tables have their own Surrogate Identity Key Should archive tables have their own surrogate identity id/key? For a customer sales table example:

We have original table, and then an additional archive table with surrogate archive id.
Rules for primary clustered indexes are:

Ever Increasing
Narrow
Unique
Static

Reference: Effective Clustered Indexes (Red-Gate Hub)

At the last company I worked for, there was a thought, that if we accidentally missed a day or period of importing, e.g. imported data for May 3, accidentally skipped May 4 (issue with system), and imported May 5, that without the new archive id, we would have to reinsert data between the pages, causing fragmentation, and slower inserts.

With the archive id, we can just add on in an ever-increasing manner.

Just inquiring if adding archiveid column is a standard industry practice.

Original Table:

create table dbo.CustomerSalesId
(
    CustomerSalesId bigint primary key identity(1,1),
    CustomerId bigint not null,
    PurchaseDate datetime not null,
    Amount decimal (10,2) not null,
    .........
}

Archive Table:

create table dbo.ArchiveCustomerSalesId
(
    ArchiveCustomerSalesId bigint primary key identity(1,1),
    CustomerSalesId bigint,
    CustomerId bigint not null,
    PurchaseDate datetime not null,
    Amount decimal (10,2) not null,
    .........
}
create unique index ux_CustomerSalesId on ArchiveCustomerSalesId(CustomerSalesId)

Fragmentation is not as bad as you think it is. Page fullness (internal fragmentation) is much more important than the physical order of the pages (external fragementation).

Having rows inserted out of order might affect the performance of scans when the pages are read from disk. When reading pages from memory, it is not important whether the pages are in the correct order or not. Also, you are probably not working with a physical server and your disk is likely a LUN sitting on a SAN, so the advantage of page contiguity is negligible.

A good visual description of internal vs. external fragmentation can be found here: http://www.sqlservercentral.com/blogs/practicalsqldba/2012/04/05/sql-server-index-fragmentation-understanding-fragmentation/

A great article on why fragementation is not what you think can be found on Brent Ozar's blog: https://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/

That said, using an additional surrogate key just for avoiding fragmentation is not a good idea. A good clustered index is, first of all, the key that you use the most to locate the data, so that you don't have to perform additional lookups. If you alter the clustering key when you archive the data, to locate the rows you would have to use a unique nonclustered index on the column(s) of the previous clustering key. A nonclusterd index is a copy of the data that requires additional space and also requires lookups to extract columns that are not part of the index.

Wrapping up: no, you don't need an additional "archiveId" surrogate key.


What is the proper setup for PG user for web application We have a web app that detects if the DB does not exist and creates it.
Users like it, but my thought is that such user has too much privileges.

I would optimally propose to change the DB creation screen of the app to supply 2 sets of credentials - one that will be used for creating DB initially and not stored permanently (e.g. 'postgres' role)  and the other that will be used by the web app and stored in config file (e.g. 'webapp').
Furthermore, 'webapp' will need to be able to 1) add new and 2) modify existing tables, procedures, and functions (the web app automatically updates the schema as required)

So some of the questions I have are:

What are some of the best practices in above scenario when using PostgreSQL 9.x?
Should the DB created be owned by 'webapp' role or should it be owned by 'postgres' and I just grant 'webapp' the necessary privileges. In case of latter, would newly added objects be owned by 'webapp' and should I avoid that?
What are some of the approaches users in community are currently using?

The best way, really, is for the web app to have no right to create or modify tables at all. It should only have the rights to do what it needs to do to perform its day to day functions, not administration.

In particular, the web app must not run as a superuser and should preferably not own the database or tables.

If the webapp has built-in administration functions for changing tables when it's upgraded, etc, it should do so via a separate set of credentials and allow you to be prompted to enter those credentials each time you perform an upgrade. Or let you create the file with the admin credentials, run the upgrade, and delete it again.

If you can't fix the webapp to use different credentials for schema changes, then it might as well be the owner of the database and tables. Just don't make it a superuser!

If you can fix the app so it uses different credentials for schema changes, then its regular non-schema-change user should not own the tables or database. It should have only CONNECT and TEMPORARY rights on the database, USAGE rights on the schema(s), and the required rights on each table GRANTed to it. Just don't forget to REVOKE ALL FROM public ON DATABASE mydb; and REVOKE ALL FROM public ON SCHEMA public;.


Query execution involving two servers in SQL server , is really slow I am running the following query , and it takes over 1 hour to finish execution. As there are so many joins and then one union is involved, is it normal for this query to be executed in 1 hour, or is it because of bringing result from two different servers ? In fact , there is one procedure , involving this select statement , creation of the procedure also took very long.

select reg.Associateid,reg.Companyid,Sum(Componentvalue) Componentvalue
from CTSINTCOSMYPT.IndiaPayrollDB_LiveFinance0910.pr.salaryregister reg
join [MP].[SuperannuationFundStatementTemplateUploadTemp] SAPrev
on reg.Associateid=SAPrev.Associateid and reg.Companyid=SAPrev.Companyid
join CTSINTCOSMYPT.IndiaPayrollDB_LiveFinance0910.pr.associatemonthlysalreimb  salreimb
on reg.Registerid=salreimb.Registerid
where Componentid=12 and ComponentValue>0
Group by reg.Associateid,reg.Companyid
Union all
select reg.Associateid,reg.Companyid,Sum(Componentvalue) Componentvalue
from CTSINTCOSMYPT.IndiaPayrollDB_LiveFinance0910.pr.salaryregister reg
join [MP].[SuperannuationFundStatementTemplateUploadTemp] SAPrev
on reg.Associateid=SAPrev.Associateid and reg.Companyid=SAPrev.Companyid
join CTSINTCOSMYPT.IndiaPayrollDB_LiveFinance0910.pr.associatemonthlyarrreimb  salreimb
on reg.Registerid=salreimb.Registerid
where Componentid=12 and ComponentValue>0
Group by reg.Associateid,reg.Companyid

Yes you're right, joins across linked servers is slow. A simple but unflattering solve is to pull the data across from each table on other servers and insert it into a temp table (select a,b,c into #temp from server.database.schema.table) or table variable. Then use that table in your queries. A better way would be to design an ETL to periodically pull these tables onto a single server.


Temporary files on RAM on PXC? I manage a PXC 5.6 cluster and noticed that the tmpdir variable in /etc/my.cnf isn't set.

I was thinking to set it to /tmp (which is tmpfs) to explicitly make the cluster store temporary files in RAM.  Without this setting, MySQL could choose /var/tmp or /usr/tmp (which, on my system, are on-disk) to store temporary files.

1) Is there any drawback to do this on PXC? I ask because on a MySQL replication slave one must not use a RAM disk to store temporary files, since the slave will need some of the files when the machine restarts.

2) Is this really going to improve performances, or it looks like a unnecessary twiddling? The InnoDB buffer size is already large enough, and 95% of temporary tables are stocked in memory.

It is not advisable to use RAM for temporary directory. At time files can grow large enough to outflow RAM and this would be true even with plain MySQL. RAM is best managed by InnoDB buffer pool. You can setup a tmpfs that should be good enough with decent performance.


How to find what tables are stored in what data files I know that tables can span across multiple data files

The tables are already created. I did not create them myself. I'm trying to find out what table(s) are using the highest amount of IO. I was able to find out what data files are using high IO using the sys.fn_virtualfilestats function.

Question : Is there a way to find out what files the tables are associated with?

You can find directly which rowset (partition of an index/heap) belongs to which filegroup: look at data_space_id in sys.allocation_units. But is not going to give you details at the individual file-within-filegroup level. It is possible to find exactly how each rowset page lays out in each file by chasing the IAM chains manually, or by using undocumented features like DBCC IND or %%physloc%%. Not trivial, but possible.
But I would encourage you to go down a different path. Specially in the light of your comment about correlating space used in the file with IO, since the largest file by size does not necessarily imply most IO in the file (think data at rest). Instead, use sys.dm_db_index_usage_stats and, especially, sys.dm_db_index_operational_stats instead. You will get the load on each rowset, and then you can correlate this with the actual physical IO.


Finding the sum of the lowest two values from a table in MySQL Using two tables, I am trying to show the years where the average price from Table1(Green_Certs) is greater than the sum of the lowest two prices from Table2(Trading).  The problem is I don't know how to Sum the two lowest values from the second table.  This is my code so far.  Any help is appreciated. Thanks

Select Year_Traded
From Green_Certs
Where Avg(Price)>
(Select min(Price)
     From Trading);

SELECT Price
    FROM Trading
    ORDER BY Price
    LIMIT 2;

will find the 2 lowest values.

Wrap that to get the SUM (or AVG or whatever):

SELECT SUM(Price) as sum_price
    FROM ( SELECT Price
            FROM Trading
            ORDER BY Price
            LIMIT 2 )

Then (I think) use that instead of the subquery you have.


Error Converting In OpenQuery I keep getting an error of

  Conversion failed when converting date and/or time from character string.

when I run my query.  How should this be altered in order for the query to execute succesfully?

Declare @Table1 Table (name varchar(100), d1 date, amt float)
Insert Into @Table1 (name, d1, amt) Values
('Red', '01/01/2015', '14.00'), ('Red', '01/01/2016', '23.00'),
('Red', '01/03/2015', '22.00'), ('Red', '02/01/2015', '33.00'),
('Red', '03/04/2015', '10,00'), ('Red', '04/04/2016', '10.00'),
('Pink', '01/01/2015', '4.00'), ('Pink', '04/05/2016', '5.00')

DECLARE @ItemList nvarchar(500) = N'''Red''';
DECLARE @startdate date = N'''01/01/2015''';
DECLARE @enddate date = N'''12/31/2015''';

DECLARE @sql nvarchar(max);

SET @sql = N'Select *
FROM OPENQUERY(linkedserver,
''Select
name
,SUM(CAST(amt As DECIMAL(10,2)) [Total]
FROM @Table1
WHERE name IN ('+ REPLACE(@ItemList, '''', '''''') + N')
    AND d1 BETWEEN ('+ REPLACE(@startdate,'''', '''''') + N')
AND ('+ REPLACE(@enddate,'''', '''''') + N')
''
);
';

EXEC sp_executesql @sql;

EDIT
It is a postgresql database that I am querying, but created a table variable for simplicity and to show DDL

Extra quotes around dates are a problem (as an aside you cannot pass character strings into float columns, which is why your table variable insert also fails).

Using extra quotes can be done at the dynamic layer, and using CHAR(39) rather than embedding a whole load of quotes can make the code a lot more readable don the line.

As an addition, initially selecting out the dynamic SQL so that you can run the generated statement in another window to test your results is also a good thing when starting out.

DECLARE @Table1 TABLE
    (
      name VARCHAR(100) ,
      d1 DATE ,
      amt FLOAT
    );
INSERT  INTO @Table1
        ( name, d1, amt )
VALUES  ( 'Red', '01/01/2015', 14.00 ),
        ( 'Red', '01/01/2016', 23.00 ),
        ( 'Red', '01/03/2015', 22.00 ),
        ( 'Red', '02/01/2015', 33.00 ),
        ( 'Red', '03/04/2015', 10.00 ),
        ( 'Red', '04/04/2016', 10.00 ),
        ( 'Pink', '01/01/2015', 4.00 ),
        ( 'Pink', '04/05/2016', 5.00 );

DECLARE @ItemList NVARCHAR(500) = N'''Red''';
DECLARE @startdate DATE = '01/01/2015';
DECLARE @enddate DATE = '12/31/2015';

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'Select *
FROM OPENQUERY(linkedserver,
''Select
name
,SUM(CAST(amt As DECIMAL(10,2)) [Total]
FROM @Table1
WHERE name IN (' + REPLACE(@ItemList, '''', '''''') + N')
    AND d1 BETWEEN (' + CHAR(39) + CHAR(39)
    + CAST(@startdate AS NVARCHAR(500)) + CHAR(39) + CHAR(39) + N')
AND (' + CHAR(39) + CHAR(39) + CAST(@enddate AS NVARCHAR(500)) + CHAR(39)
    + CHAR(39) + N')
''
);
';
SELECT @sql;
--EXEC sys.sp_executesql @sql;


How can I use Oracle's CONNECT BY PRIOR to only get specific revisions of hierarchical records? I've got a table schema in Oracle 11 which is storing records that are linked together. The table schema is roughly like this

CREATE TABLE "SOMEDATA" (
    "ID" INTEGER NOT NULL,
    "ITEMNAME" VARCHAR2 NOT NULL,
    "LINKLEFT" VARCHAR2 DEFAULT NULL,
    "LINKRIGHT" VARCHAR2 DEFAULT NULL,
    "CREATE_TIMESTAMP" TIMESTAMP NOT NULL,
    "VALUE1" VARCHAR2 DEFAULT NULL,
    "VALUE1" VARCHAR2 DEFAULT NULL
)

The purpose of this table is to store timestamped records which may link to other records, similar to a hierarchy. My challenge is build a query that at any given specific timestamp, and for a given itemname, recursively fetch the latest versions of the rows that chain to the right of the item. E.g. in the following data:

+----+----------+----------+-----------+---------------------+------------+----------+--+
| Id | Itemname | Linkleft | Linkright |  create_timestamp   |   value1   |  value2  |  |
+----+----------+----------+-----------+---------------------+------------+----------+--+
|  1 | item1    | (null)   | item2     | 23/08/2014 00:00:00 | Test       | Data     |  |
|  2 | item2    | item1    | item3     | 23/08/2014 00:01:25 | Some       | More     |  |
|  3 | item3    | item2    | (null)    | 23/08/2014 00:05:00 | Even       | MoreData |  |
|  4 | item2    | item1    | item3     | 24/08/2014 00:00:00 | Changed    | Data     |  |
|  5 | item1    | (null)   | item2     | 25/08/2014 12:37:02 | New        | Better   |  |
|  6 | item3    | item2    | item4     | 27/08/2014 00:00:00 | Lipsum     | Lopsum   |  |
|  7 | item4    | item3    | (null)    | 27/08/2014 00:01:00 | Additional | Stuff    |  |
+----+----------+----------+-----------+---------------------+------------+----------+--+

For the query ITEMNAME='item1' and CREATE_TIMESTAMP=23/08/2014 14:58:59, I need to return records #1,2 and 3. This is because these are the latest linked records that link to the latest record for 'item1' at that particular point in time. However, for the query ITEMNAME='item1' and CREATE_TIMESTAMP=01/01/2015 12:00:00, I need to return records #5,4,6 and 7, because at that time, these records were the latest versions.

I've been playing about with Oracle's CONNECT BY PRIOR to build something like

SELECT * FROM "SOMEDATA" START WITH "ITEMNAME"="item1" CONNECT BY PRIOR "LINKRIGHT"="RIC"

This obviously chains the records together, but pays no attention to timestamps and returns all values for rows which match.

It seems that hierarchical records is a nicely solved problem, but not in the case where you want to also store the historical values for records in the hierarchy and query for any time. Is this sort of thing possible in this table structure, or am I heading down the wrong track entirely?

how about:

with preresult as (
SELECT id,itemname,
   linkleft,
   linkright,
   create_timestamp,
   value1,
   value2,
   ROW_NUMBER ()
      OVER (PARTITION BY itemname ORDER BY create_timestamp DESC)
      rownumber
   FROM somedata
   WHERE create_timestamp < :timestamp)
select * from preresult where rownumber < 2

and enter as :timestamp the desired date and time

hth,
macfly.


Deny insert/delete for all users in SQL Server 2005 In our database we have about 20 users, that is used be some program (and users are added with this program). Now there was new requirement to use 2 database and do not allow delete data from one database.

What would be easiest way to deny insert/delete access for some tables (not all)? All users have db_datareader, db_datawriter.

It would be confusing if you granted someone db_datawriter and then denied them delete rights.  So if you can, remove them from the db_datawriter and then grant them the rights they need:

create role role_MyApp
grant select, insert on Table1 to role_MyApp
...
exec sp_adduserrole 'role_MyApp, 'User1'


Run and Save multiple SELECT statements Within a Postgres function I would like to run a number of SELECT commands. For each SELECT command I would like to output written to separate text files.

What is the best way to implement this?

PostgreSQL version: 9.3.5.0

 BEGIN

 RETURN QUERY SELECT text
    FROM eightks
    WHERE other_events = true
    AND text ~* '(\y(chief executive officer)\y)'
    AND text ~*'(\y(cancer)\y)'
    \o /Users/XXXX/desktop/test.txt;

 RETURN QUERY SELECT text
    FROM eightks
    WHERE other_events = true
    AND text ~* '(\y(chief executive officer)\y)'
    AND text ~* '(\y(sudden)\y)'
    \o /Users/xxxx/desktop/sudden.txt;

  END

Here is the follow ERROR message

    edgar=# \i test3.sql;
    psql:test3.sql:21: ERROR:  syntax error at or near "RETURN"
    LINE 2: RETURN QUERY SELECT text
    ^

Here is also the structure of the table if it helps;

     edgar=# \d eightks
           Table "public.eightks"
       Column    |   Type   | Modifiers
   --------------+----------+-----------
    id           | integer  |
    doc_id       | bigint   |
    text         | text     |
    tsv          | tsvector |
    other_events | boolean  |

Thanks very much

You are confusing PL/PgSQL with SQL.

You do not need the RETURN QUERY, nor is it valid in normal SQL.

Simply SELECT text FROM eightks ...

Additionally, if you want to direct output to a particular file with psql's \o, you must run the \o command before the statement you want to write the output for.

Finally - I wonder if you should be looking into the \copy command, if you're trying to write query output to files.

\copy (SELECT ....) TO '/some/file' WITH (FORMAT CSV)


Auditing AS 400 DB2 library user access As part of the testing to check for authorized users having access to applications and libraries on AS 400 systems, I need help to figure out a list of users having access to applications hosted on the AS 400 systems, need to understand is there way to list down users grouped to access specific libraries for certain applications and all users given access to and revoked access and also the privileges assigned to each users, over a period of time for the testing.

This depends on the specific application. Most AS400 DB2 applications have initial  Application Administration which configures privileges assigned to each application user. The administration role is normally a DBA or programming staff responsibility. To extract a list of Groups and/or users accessing specific functions speak to the DBA or Application Administrator.


Using slash in MySQL database I am using mysql with php Yii framework.When doing data modelling I came accross some doubt.I have a table for user details like this.

CREATE TABLE IF NOT EXISTS `tbl_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(80) NOT NULL DEFAULT '',
  `lastname` varchar(80) NOT NULL DEFAULT '',
  `gender` varchar(6) NOT NULL DEFAULT '',
  `email` varchar(45) NOT NULL DEFAULT '',
  `company_name` varchar(80) NOT NULL DEFAULT '',
  `contact_no` varchar(45) NOT NULL DEFAULT '',
  `address` varchar(120) NOT NULL DEFAULT '',
  `state` varchar(45) NOT NULL DEFAULT '',
  `country` varchar(45) NOT NULL DEFAULT '',
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=98 ;

Here my problem is I want the state,country should be like this so that I can use the fields like state/city

 `state/city` varchar(45) NOT NULL DEFAULT '',
  `state/province` varchar(45) NOT NULL DEFAULT '',

So is it safe to use like this or I have to use only one option there?Any help and suggestions will be highly appriciable.

Currently on MySQL 5.5+ you can use such characters on both schema names and table names, just have in mind that there are known bugs caused by such characters being confused with directory paths on the MySQL internals code base.

Up until MySQL 5.5 you would find it to not be possible.
From this link: (of the old documentation, that's not available anymore)

  Database and table names cannot contain “/”, “\”, “.”, or characters that are not permitted in file names

One way or another, using backslashes is just bad-practice, I recommend that you use an underscore character instead.


ERROR 1698 (28000): Access denied for user 'root'@'localhost' I discovered that my phpmyadmin cannot login with my root password. So I updated the password as follow:

# mysql
mysql> UPDATE user set authentication_string=password('secret') where user='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

After doing this I tried to connect with my new root password:

# sudo -u www-data mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

I don't understand why I am still denied.

mysql> show grants for 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select USER(),CURRENT_USER();
+----------------+----------------+
| USER()         | CURRENT_USER() |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+
1 row in set (0.00 sec)

I tried with:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';

and it worked, but I have absolutely have no idea of why any other methods do not work.

What is bizarre is that before I could connect in root with # mysql. Now I have to do # mysql -uroot -p. What did I changed?

I've got a similar issue, the solution:

0. login to MySQL

$ sudo mysql -u root
use mysql;

1. Change Password

UPDATE user SET authentication_string=PASSWORD('yourpass') WHERE User='root';

2.Change Plugin

UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE User='root';

3. Finish It

flush privileges;
exit

Extra info

If you get errors like: "ERROR 1819 (HY000): Your password does not satisfy the current policy requirements" you can view the password rules with:

SHOW VARIABLES LIKE 'validate_password%';


How to restore multiple backups I'm using ExpressMaint and Windows Scheduled Tasks to create weekly full and daily differential backups of some MS SQL Server 2008 R2 Express databases

Weekly fulls

expressmaint -S (local)\SQLExpress -D ALL -T DB -R D:\Backup\Reports -RU WEEKS -RV 1 -B D:\Backup\Data -BU WEEKS -BV 4 -V -C

Daily Diffs

expressmain -S (local)\SQLExpress -D ALL -T DIF -R D:\Backup\Reports -RU WEEKS -RV 1 -B D:\Backup\Data -BU days -BV 7 -V -C

When I come to restore these to a certain point, I have to restore each backup individually. Is there any way I can chain a series of backups into a single restore that will be re-played in the correct order?

When I try this, I get the error

  An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

  ADDITIONAL INFORMATION:

  The media loaded on "C:\Foo\Bar_FullBackup_20110130_2346.bak" is formatted to support 1 media families, but 2 media families are expected according to the backup device specification.
  RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3231)

  For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=3231&LinkId=20476

Edit

I am using the restore dialog from the tasks menu when you right click on a database. From there, I select "Restore from device" and Add the .bak files I wish to restore from.

If I only add a single .bak file, I'm OK, if I add multiple files, I receive the error above.

Using SSMS, you cannot chain a restore of the backups in one operation. You would have to do multiple restores. You'll want to use T-SQL in order to be more efficient.

--Restore the most recent full backup
RESTORE DATABASE <mydb>
FROM DISK = 'Path to full backup'
WITH NORECOVERY, STATS=10 --If only restoring the full, change to RECOVERY

--Restore the most recent diff backup
RESTORE DATABASE <mydb>
FROM DISK = 'Path to diff backup'
WITH RECOVERY, STATS=10

More info on RESTORE: http://msdn.microsoft.com/en-us/library/ms186858.aspx


Estimated Execution Plan SQL Server Sort? I'm running a query and its taking a age to execute, looking at the execution plan I can see that 51% of the cost is in the SORT? when in the actual query i am not ORDERING BY anything.

Anyone got any ideas how to get this SORT percentage down or remove it all together?
Here is the query I'm running.

SELECT Time_ID,
       Site_Type_ID,
       Abandoned_ID,
       WorkType_ID,
       SUM (staging.dbo.measure.ring_time) AS Ring_Time,
       SUM (staging.dbo.measure.hold_time) AS Hold_Time,
       SUM (staging.dbo.measure.talk_time) AS Talk_Time,
       SUM (staging.dbo.measure.acw_time)  AS ACW_Time,
       COUNT(*)                            CallCount
FROM   measure
       INNER JOIN DataMartEnd.dbo.Time_Dim
         ON measure.StartTimeDate BETWEEN Time_Dim.Time_Start AND Time_Dim.Time_End
       INNER JOIN datamartend.dbo.Site_Type_Dim
         ON measure.DBID = Site_Type_Dim.Site_Type_Code
       INNER JOIN datamartend.dbo.Abandoned_Call_Dim
         ON measure.Abandoned = Abandoned_Call_Dim.abandoned_value
       INNER JOIN DataMartEnd.dbo.Work_Type_Dim
         ON measure.DBID = work_type_dim.MIG_Site_ID
            AND Work_Type_Dim.Work_Type_Code = measure.Queue
            AND measure.StartTimeDate BETWEEN Work_Type_Dim.DimEffectiveStartDtm AND Work_Type_Dim.DimEffectiveEndDtm
GROUP  BY Abandoned_ID,
          WorkType_ID,
          Site_Type_ID,
          time_id

Thanks for the help.

Group by Abandoned_ID, WorkType_ID , Site_Type_ID, time_id

One way to implement a group by is to sort the input. Stream Aggregate Showplan Operator:

  The Stream Aggregate operator requires input ordered by the columns within its groups. The optimizer will use a Sort operator prior to this operator if the data is not already sorted due to a prior Sort operator or due to an ordered index seek or scan.

QED.

Further reading: Craig Freedman blog entry on Stream Aggregate and the alternative Hash Aggregate.


TSQL - use columns as date range How can I retrieve the cto column using an @variable?

For example, for @Variable = '2019-03-02' the expected output is'MAR 1 - MAR 7'.

It's kind of a barebone question, but...

@DateToUse is the date you are searching for.

@StringOutput is the cto column.

I'm using the ID field as a tie-breaker in order to protect against duplicate or overlapping ranges in from/to fields.

DECLARE @DateToUse DATE
DECLARE @StringOutput VARCHAR(50)

SET @DateToUse = '3/2/2019'

SET @StringOutput = (SELECT TOP (1) cto FROM dbo.MyTable WHERE @DateToUse BETWEEN from AND to ORDER BY ID)

PRINT @StringOutput


database design for multiple and all pepople Assume I writing an email applicaiton.
Now I want to send some messages to specified person, some messages to multiple person, some messages to specified group and some to all members.
I am wondering in the best practice I have to treat every mail like a one instance mail, I mean I if I am sending mail to all I have to assign one record for every person or I can define a type field and from the code I must handle with codes.

Mail Master table

MailID (PK)
Mail content

Emp Master table

EmpID (PK)
EmpName

Mail-Employee Mapping table

Mail-Employee_ID (auto increament id PK)
MailID (FK)
EmployeeID (FK)

Group_Master table

GroupID (auto increament PK)
GroupName

Group_Employee_Mapping table

Group_Employee_ID (auto increament)
GroupID (FK)
EmployeeID (FK)
(GroupID+Employee are unique so Composite PK)

I think tables and their roles are self explanatory.

Some rule for Group Master:-

Group_Employee_Mapping is predefined in system :you have already mapped all employee and its group in this table.One employee can belong to many group .

If Group='All' don't store "All" employee in Group_Employee_Mapping Table.As this is unnecessary

If user select "All" select all employee from Employee Table ,send them mail and store each employee and mailid in "Mail-Employee Mapping" table

If user select any specific group then select those employee from Group_Employee_Mapping table,send them mail and store them in Mail-Employee Mapping


SQL query to convert Column to Row using Pivot/Unpivot My Select Table. I tried Pivot query to get my expected result but I was struct to generate the expected result.

WITH PersonOptions(ID,[Year], [Text], Value1, Value2) AS
(
              SELECT 'A',2000, 'X',10,2
    UNION ALL SELECT 'A', 2001, 'X',21,1
    UNION ALL SELECT 'A', 2000, 'Y',13,3
    UNION ALL SELECT 'A', 2001, 'Y',2,3
    UNION ALL SELECT 'B', 2000, 'X',0,2
    UNION ALL SELECT 'B', 2001, 'X',5,1
    UNION ALL SELECT 'B', 2000, 'Y',6,4
    UNION ALL SELECT 'B', 2001, 'Y',9,5
    UNION ALL SELECT 'C', 2000, 'X',3,5
    UNION ALL SELECT 'C', 2001, 'X',2,8
    UNION ALL SELECT 'C', 2000, 'Y',9,2
    UNION ALL SELECT 'C', 2001, 'Y',8,3
)
select *  from PersonOptions

ID  Year    Text    Value1  Value2
A   2000    X        10       2
A   2001    X        21       1
A   2000    Y        13       3
A   2001    Y        2        3
B   2000    X        0        2
B   2001    X        5        1
B   2000    Y        6        4
B   2001    Y        9        5
C   2000    X        3        5
C   2001    X        2        8
C   2000    Y        9        2
C   2001    Y        8        3

Expected Output:

               Value1          Value2
ID  Text    2000    2001    2000    2001
A    X       10      2       21      1
A    Y       13      3       2       3
B    X        0      2       5       1
B    Y        6      4       9       5
C    X        3      5       2       8
C    Y        9      2       8       3

one way of approaching this is by doing two pivots and joining them together. see below. I also believe your expected output example is slightly wrong based on the input data, but that doesn't matter anyway.

WITH PersonOptions(ID,[Year], [Text], Value1, Value2) AS
(
          SELECT 'A',2000, 'X',10,2
UNION ALL SELECT 'A', 2001, 'X',21,1
UNION ALL SELECT 'A', 2000, 'Y',13,3
UNION ALL SELECT 'A', 2001, 'Y',2,3
UNION ALL SELECT 'B', 2000, 'X',0,2
UNION ALL SELECT 'B', 2001, 'X',5,1
UNION ALL SELECT 'B', 2000, 'Y',6,4
UNION ALL SELECT 'B', 2001, 'Y',9,5
UNION ALL SELECT 'C', 2000, 'X',3,5
UNION ALL SELECT 'C', 2001, 'X',2,8
UNION ALL SELECT 'C', 2000, 'Y',9,2
UNION ALL SELECT 'C', 2001, 'Y',8,3
)
select * INTO #t from PersonOptions

;WITH pvt1 AS
(
    SELECT pvt.ID, pvt.Text, pvt.[2000], pvt.[2001]
    FROM
    (
        SELECT t.ID, t.Year, t.Text, t.Value1
        FROM #t t
    ) x
    PIVOT
    (
        MAX(Value1) FOR Year IN ([2000], [2001])
    ) pvt
), pvt2 AS
(
    SELECT pvt.ID, pvt.Text, pvt.[2000], pvt.[2001]
    FROM
    (
        SELECT t.ID, t.Year, t.Text, t.Value2
        FROM #t t
    ) x
    PIVOT
    (
       MAX(Value2) FOR Year IN ([2000], [2001])
    ) pvt
)
SELECT *
FROM pvt1
INNER JOIN pvt2 ON pvt2.ID = pvt1.ID AND pvt2.Text = pvt1.Text

DROP TABLE #t


Create a SQL Server Redirect (Alias / Linked Server) I currently have a database on a named SQL server instance called \Mobius and database name of Mobius

We need to move the database to the default (parent) instance but don't necessarily want to update our connection strings in all our applications (initially) so that a call to \Mobius will point to having the database of the same name.

Is there a way this can be done?

This might be what you are looking to do but as the answer notes you will only be able to have a single instance running after the change.

Pointing all connections to the default instance


Hardening further mysql with sqlmap suggestion I just ran this tool called sqlmap. It gave me this few list as below.

sqlmap identified the following injection points with a total of 0 HTTP(s) requests:
---
Parameter: ID (GET)
    Type: boolean-based blind
    Title: AND boolean-based blind - WHERE or HAVING clause
    Payload: ta=asaa&ID=74 AND 3387=3387&na=0&bc=C

    Type: UNION query
    Title: MySQL UNION query (NULL) - 6 columns
    Payload: ta=asaa&ID=74 UNION ALL SELECT NULL,NULL,NULL,NULL,NULL,CONCAT(0x71766a7171,0x78587565526f656b4e4b,0x716a707871)#&na=0&bc=C

    Type: AND/OR time-based blind
    Title: MySQL > 5.0.11 AND time-based blind
    Payload: ta=asaa&ID=74=74 AND SLEEP(5)&na=0&bc

I tested on the union query doest not give any result. But booleand-based blind do give the result and time based-based blind kind of stop my application? What can I do to improvise?

There isn't much you can do to "improvise", you need to fix the issue at hand: you're vulnerable to SQL injections.

The most straightforward solution to this is to implement parameterized queries in your code. This will involve changing all the queries manually, but will protect you.

You can read up on it from the article What's the Right Way to Prevent SQL Injection in PHP, but here's an excerpt from the article:

  For example, consider the following login script:

  $sql = "SELECT * FROM UserTbl WHERE Username = '$username' and Password = '$password'";

  Let’s use the same script, but I’ll define the SQL query with parameter placeholders:

  $sql = "SELECT * FROM UserTbl WHERE Username = ? and Password = ?";

  Now, I’ll define an array that holds the parameter values:

  $params = array($_POST['Username’], $_POST['Password’]);

  When I execute the query, I pass the $params array as an argument:

  $stmt = sqlsrv_query($conn, $sql, $params);


Invalid characters in xml I have just downloaded a large xml file and most of characters appear as "�". I changed the encoding but there was no improvement. Anyone knows what this might be due to?

One fishy thing: the file does not start as a usual xml, but rather as: "� m�Y �/opt/cds-invenio/var/www/dumps/.HepNames-records.xml"

The administrator give me a tip: "clearly it's Content-Type: application/x-gzip", which I did not understand.

Thanks for the support.

Sounds like the file you downloaded is actually not an XML file yet. It is actually a compressed XML file, and was probably compressed using the GZip algorithm.

To see your data you will have to decompress it:

rename the file to an .xml.gz extension (if it doesn't have one already)
use any decompression program to decompress your XML data (7zip is good/free)
open the resulting XML file in your favourite text editor!


Column not found in dbase file When I try to import a shapefile using db2se, I specify the input columns to be loaded in the syntax as the COLUMN NAME

db2se import_shape <dbname> -userId <username>
-pw <password> -fileName <shapefile to be loaded> -inputAttrColumns N(Cat_Zones) -srsName WGS84_SRS_1003 -tableSchema GEXPDBA -tableName TPOLYGON_STAGING -tableAttrColumns VC_FLD_1 -createTableFlag 0 -spatialColumn POLYGON_OB -typeSchema db2gse -typeName st_multipolygon -messagesFile C:\ABC1.txt -client 1

GSE2112N  The column "CAT_ZONES" cannot be found in the dBASE file.

But if I replace it with the POSITIONAL parameter, it works.

db2se import_shape <dbname> -userId <username>
-pw <password> -fileName <shapefile to be loaded> -inputAttrColumns P(1) -srsName WGS84_SRS_1003 -tableSchema GEXPDBA -tableName TPOLYGON_STAGING -tableAttrColumns VC_FLD_1 -createTableFlag 0 -spatialColumn POLYGON_OB -typeSchema db2gse -typeName st_multipolygon -messagesFile C:\ABC1.txt -client 1

GSE0000I  The operation was completed successfully.

Any reason why this would happen. The shapefile info is as below.

db2se shape_info -filename <shapefile to be loaded>

Shape file information
----------------------
File code                  = 9994
File length (16-bit words) = 2533850
Shape file version         = 1000
Shape type                 = 5 (ST_MULTIPOLYGON)
Number of records          = 2636

Minimum X coordinate = -173.109762
Maximum X coordinate = -66.954002
Minimum Y coordinate = 18.915497
Maximum Y coordinate = 71.398048
Shapes do not have Z coordinates.
Shapes do not have M coordinates.

Shape index file (extension .shx) is present.

Attribute file information
--------------------------
dBase file code                = 3
Date of last update            = 2013-09-03
Number of records              = 2636
Number of bytes in header      = 97
Number of bytes in each record = 305
Number of columns              = 2

Column Number  Column Name      Data Type       Length  Decimal
-------------  ---------------  --------------  ------  -------
            1  Cat_Zones        C ( Character)     254        0
            2  Country          C ( Character)      50        0

Coordinate system definition: "GEOGCS["GCS_WGS_1984",DATUM["D_WGS_1984",SPHEROID
["WGS_1984",6378137.0,298.257223563]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.01
74532925199433],METADATA["World",-180.0,-90.0,180.0,90.0,0.0,0.0174532925199433,
0.0,1262]]"

It was a case sensitivity issue. Using inputAttrColumns N(\"Cat_Zones\") solved the problem.


Why does a Set Returning Function (SRF) run slower in a FROM clause? This is a database-internals question. I'm using PostgreSQL 9.5, I'm wondering why Set Returning Functions (SRFs), also known as Table Valued Functions (TVFs) run slower when in a FROM clause, for instance when I execute these commands,

CREATE TABLE foo AS SELECT * FROM generate_series(1,1e7);
SELECT 10000000
Time: 5573.574 ms

It's always substantially slower than,

CREATE TABLE foo AS SELECT generate_series(1,1e7);
SELECT 10000000
Time: 4622.567 ms

Is there a general rule that can be made here, such that we should always be running Set-Returning Functions outside of a FROM clause?

Let's start by comparing execution plans:

tinker=> EXPLAIN ANALYZE SELECT * FROM generate_series(1,1e7);
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series  (cost=0.00..10.00 rows=1000 width=32) (actual time=2382.582..4291.136 rows=10000000 loops=1)
 Planning time: 0.022 ms
 Execution time: 5539.522 ms
(3 rows)

tinker=> EXPLAIN ANALYZE SELECT generate_series(1,1e7);
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Result  (cost=0.00..5.01 rows=1000 width=0) (actual time=0.008..2622.365 rows=10000000 loops=1)
 Planning time: 0.045 ms
 Execution time: 3858.661 ms
(3 rows)

Okay, so now we know that SELECT * FROM generate_series() is executed using a Function Scan node, while SELECT generate_series() is executed using a Result node. Whatever is causing these queries to perform differently boils down to the difference between these two nodes, and we know exactly where to look.

One other interesting thing in the EXPLAIN ANALYZE output: note the timings. SELECT generate_series() is actual time=0.008..2622.365, while SELECT * FROM generate_series() is actual time=2382.582..4291.136. The Function Scan node starts returning records around the time the Result node finished returning records.

What was PostgreSQL doing between t=0 and t=2382 in the Function Scan plan? Apparently that's about how long it takes to run generate_series(), so I'd wager that's exactly what it was doing. The answer starts to take shape: it seems like Result returns results immediately, while it seems like Function Scan materializes the results and then scans them.

With the EXPLAIN out of the way, let's check the implementation. The Result node lives in nodeResult.c, which says:

 * DESCRIPTION
 *
 *      Result nodes are used in queries where no relations are scanned.

The code is simple enough.

Function Scan lives in nodeFunctionScan.c, and indeed it appears to take a two phase execution strategy:

/*
 * If first time through, read all tuples from function and put them
 * in a tuplestore. Subsequent calls just fetch tuples from
 * tuplestore.
 */

And for clarity, let's see what a tuplestore is:

 * tuplestore.h
 *    Generalized routines for temporary tuple storage.
 *
 * This module handles temporary storage of tuples for purposes such
 * as Materialize nodes, hashjoin batch files, etc.  It is essentially
 * a dumbed-down version of tuplesort.c; it does no sorting of tuples
 * but can only store and regurgitate a sequence of tuples.  However,
 * because no sort is required, it is allowed to start reading the sequence
 * before it has all been written.  This is particularly useful for cursors,
 * because it allows random access within the already-scanned portion of
 * a query without having to process the underlying scan to completion.
 * Also, it is possible to support multiple independent read pointers.
 *
 * A temporary file is used to handle the data if it exceeds the
 * space limit specified by the caller.

Hypothesis confirmed. Function Scan executes upfront, materializing the function's results, which for large result sets results in spilling to disk. Result doesn't materialize anything, but also supports only trivial operations.


MySQL Latest Deadlock Explanation Can someone explain this deadlock to me? It seems as if there are 2 inserts happening within milliseconds of each other with the same Primary Key. I'm not sure how this can cause a deadlock. At most a duplicate key error. Please let me know.

   LATEST DETECTED DEADLOCK
    ------------------------
    141210 19:12:08
    *** (1) TRANSACTION:
    TRANSACTION 5D33C809, ACTIVE 1 sec inserting
    mysql tables in use 1, locked 1
    LOCK WAIT 4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
    MySQL thread id 10092312, OS thread handle 0x7fbc69898700, query id 16041618913 xxxxx.net 10.x.x.x prod_user update
    INSERT INTO `shipments` (`applicant_id`, `courier_id`, `created_at`, `shipment_type_id`, `status_time`, `tracking_number`, `updated_at`) VALUES (4621087, 3, '2014-12-10 19:12:07', 1, '2014-12-10 18:42:00', '5068214830', '2014-12-10 19:12:07')
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 2536 page no 18587 n bits 552 index `index_shipments_on_ci_ai_tn_sti` of table `prod`.`shipments` trx id 5D33C809 lock_mode X locks gap before rec insert intention waiting
    Record lock, heap no 191 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
     0: len 4; hex 80000003; asc     ;;
     1: len 4; hex 80468328; asc  F (;;
     2: len 10; hex 33353032373538363034; asc 3502758604;;
     3: len 4; hex 80000001; asc     ;;
     4: len 4; hex 8044fe80; asc  D  ;;

    *** (2) TRANSACTION:
    TRANSACTION 5D33C810, ACTIVE 1 sec inserting, thread declared inside InnoDB 1
    mysql tables in use 1, locked 1
    4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
    MySQL thread id 10098192, OS thread handle 0x7fafa1434700, query id 16041619033 xxxxx.net 10.x.x.x prod_user update
    INSERT INTO `shipments` (`applicant_id`, `courier_id`, `created_at`, `shipment_type_id`, `status_time`, `tracking_number`, `updated_at`) VALUES (4621087, 3, '2014-12-10 19:12:08', 1, '2014-12-10 18:42:00', '5068214830', '2014-12-10 19:12:08')
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 2536 page no 18587 n bits 552 index `index_shipments_on_ci_ai_tn_sti` of table `prod`.`shipments` trx id 5D33C810 lock mode S locks gap before rec
    Record lock, heap no 191 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
     0: len 4; hex 80000003; asc     ;;
     1: len 4; hex 80468328; asc  F (;;
     2: len 10; hex 33353032373538363034; asc 3502758604;;
     3: len 4; hex 80000001; asc     ;;
     4: len 4; hex 8044fe80; asc  D  ;;

    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 2536 page no 18587 n bits 552 index `index_shipments_on_ci_ai_tn_sti` of table `prod`.`shipments` trx id 5D33C810 lock_mode X locks gap before rec insert intention waiting
    Record lock, heap no 191 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
     0: len 4; hex 80000003; asc     ;;
     1: len 4; hex 80468328; asc  F (;;
     2: len 10; hex 33353032373538363034; asc 3502758604;;
     3: len 4; hex 80000001; asc     ;;
     4: len 4; hex 8044fe80; asc  D  ;;

    *** WE ROLL BACK TRANSACTION (2)

From the output the two inserts are doing gap locking on the index index_shipments_on_ci_ai_tn_sti. Without knowing the table definition, I think the index is a UNIQUE index.

Here is a decent blog explaining the reason UNIQUE constraints can cause deadlocks on concurrent inserts. Snippet here:

  Mysql innodb engine performs row locking on inserts. If column A has a unique key constraint, and we are adding the value "bbb" for column A in an insert statement, mysql needs to lock any gap in the index between the two current records where "bbb" will be inserted at.

More information on gap locking can be found in the docs: http://dev.mysql.com/doc/refman/5.6/en/innodb-record-level-locks.html


mysql implementation of ORDER command Sorry for a silly question, I tried query with WHERE clause and ORDER at the end, which actually took very long time to implement, even thought WHERE clause was on indexed field. It looks like Mysql firstly performs ORDER on entire table and after that SELECT, which is not preferred behavior. Am I right? How can I cause it first perform SELECT and then ORDER.

Thanks!

Addendum:

mysql> explain SELECT *
               FROM `products`
               WHERE (`products`.user_id = 1111)
                 AND ( (`products`.`type` = 'type1' ) )
               ORDER BY products.id DESC
               LIMIT 1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: products
         type: ref
possible_keys: index_products_on_user_id, index_products_on_type
             , index_products_on_type_and_status
          key: index_products_on_user_id
      key_len: 5
          ref: const
         rows: 2570
        Extra: Using where; Using filesort

There are a chapter in the MySQL documentation about ORDER BY optimisation

I'm unable to find a MySQL reference, but for comparison here is the SQL Server logical query processing phases. SELECT has to be done before ORDER BY.

Without code, I'm guessing that you have unsuitable indexes for the query you are running

Edit, after code added

You probably need an index on user_id, type, product_id DESC to cover the both WHERE predicates and the ORDER BY: you have no suitable index currently so a filesort operation is generated.

Also, you have SELECT * which requires an extra lookups and processing to get all columns, no matter what index is used (unless the table is just 3 columns)


How do I include zeros in a count(column)? I am attempting to build a report for a client. I need a display of all the values in Tab1, Col1 (a validation table), a count of each time that occurred, and a percentage of the total. Here's what I'm looking for:

Number of records       Type        Percentage of total
2                       A           2
3                       B           3
0                       C           0

I have the basic query worked out, I just don't know how to include the zero count! Here's what I have so far:

select count(b.Col1) "Number of Records",
a.col2 "Type",
to_char(round(ratio_to_report(count(a.Col1)) over()*100)) || '%' as "Percantage of Total"
from Tab1 a,
Tab2 b
where Tab2.Col1 = Tab1.Col1(+)
group by a.Col1;

Thanks in advance.

EDIT:
Attempting this:

select count(b.Col1) "Number of Records",
a.col2 "Type",
to_char(round(ratio_to_report(count(b.Col1)) over()*100)) || '%' as "Percantage of Total"
from Tab2 b
left join Tab1 a on tab2.col1 = tab1.col1 -- or (from tab2 left join tab1) if that is what's intended
group by a.Col1;

Gave me the same results as my query.

This:

select COALESCE(count(b.Col1), 0) AS "Number of Records",
a.col2 "Type",
to_char(round(ratio_to_report(count(b.Col1)) over()*100)) || '%' as "Percantage of Total"
from Tab1 a
left join Tab2 b on tab1.col1 = tab2.col1
group by a.Col1;

Also gives me the same results.

EDIT 2:

Table A columns: ID, Code, Description

Table B Columns: ID, RecordNumber, TableA_ID

Table B is a "master" and Table A is one of the many "details". Basically I need a count of TableA instances in TableB.. including the zeros.

Query that gave me my desired results:

select coalesce(count(b.TabA_id),0) as "num records",
a.desc "type",
to_char(round(ratio_to_report(count(b.TabA_id)) over()*100)) || '%' as "Percentage of Total"
from TabB b
right join TabA a on b.TabA_id = a.id
group by  b.TabA_id, a.id
order by a.desc;

There's not much information you provide, so this is only a guess.

Your WHERE clause Tab2.Col1 = Tab1.Col1(+) may be backward, with regard to the (+) ... I would recommend switching it to a LEFT JOIN regardless, just to make it more readable..

select
  count(b.Col1) "Number of Records", --- CHANGED a.Col1 to b.Col1
  a.col2 "Type",
  to_char(round(ratio_to_report(count(b.Col1)) over()*100)) || '%' as "Percantage of Total"
from Tab1 a
left join Tab2 b on tab1.col1 = tab2.col1 -- or (from tab2 left join tab1) if that is what's intended
group by a.Col1;

If you are still not getting the 0 counts, then you should try a coalesce statement.

select
  COALESCE(count(b.Col1), 0) AS "Number of Records", --- COALESCE HERE .. CHANGED a.Col1 to b.Col1
  a.col2 "Type",
  to_char(round(ratio_to_report(count(b.Col1)) over()*100)) || '%' as "Percantage of Total"
from Tab1 a
left join Tab2 b on tab1.col1 = tab2.col1
group by a.Col1;

You are not referencing table b anywhere in your sql, though - please explain why you need to join to it if you're not going to be using its data?


Hosting SQL Server and SSIS/SSRS in same VM I have SSIS / SSRS and large number of databases currently being hosted on the same prod server (SQL1, SQL Server 2008R2). Since SQL1 already has SSRS/SSIS installed, would it be a good option to create a new prod server (SQL2 i.e SQL Server 2008R2, for example) to consolidate all the databases, and focusing SQL1 as the server for only SSIS and SSRS?

Please advise.

Thank you

This totally depends how healthy the server is SQL1,2008R2.

You will need to analyse first the usage of Databases, SSIS or SSRS and other parameters hosted on the server.

Also, what resources you have in terms of RAM, CPU, Disk(Raid levels) etc. available on the server along with how they are being getting used.

It would be a good start if you separate SSIS/SSRS on different server, assuming you have performed a load analysis on this server which might prompted you to think,in terms of for performance and scalability. Databases like to have a room of their own in terms of RAM/CPU (again this depends upon the usage of applications attached to it), thus other services like SSRS seems not a good choice to be hosted on same.

Generally, monitoring CPU, memory, paging and I/O on a server will give you an good indication of whether a server is getting a little too busy. You may have to do various calculation here and there and come to a conclusion which should answer you're question accordingly.

Therefore, it totally depends how the server is behaving currently and how it is expected to grow in future.


Does "allow remote connections to this server" in sql server impact SSIS packages? I'm wondering if anyone has experience with this setting "allow remote connections to this server" in sql server. I am partly responsible for a few hundred sql servers which run SSIS in various ways. Some run it with ssis packages deployed through integration services catalog, and others run the SSIS package from a batch server or even manually from their laptop running visual studio, using a package that connects to our sql server.
A security consultant is saying we need to disable this on every server, no questions asked, but I'm hearing conflicting information. Some would imply any SSIS packages deployed on another server or laptop and connecting to this one, would be denied now. Others say it actually means connections FROM this server, so it would impact ssis packages called from batch servers. Others say it is FROM still, but for some reason wouldn't impact SSIS packages.
Any recommendations on what I should do? Rather than test every possible scenario to appease the security consultant, I would like to be able to rule out a few "always true" situations where we would keep this setting checked. Otherwise I will have to test hundreds of environments just to disprove him. Any help appreciated.

I basically asked the same question a few years ago, take a look at my thread here: What does the setting allow remote connections actually do.


query optimization delete nested query Can the following query be optimized ?

DELETE FROM COLOR_TAGS
WHERE COLOR_PROJECTID = $id
AND NoteID IN(
SELECT ID FROM note_note
WHERE BibID=$bid AND isdel=0);

Add composite indexes to your tables:

On COLOR_TAGS, index (COLOR_PROJECTID, NoteID)

On note_note, index (isdel, BibID, ID).


Virtualized SQL Server hits 100% CPU and 0 Active Memory We recently experienced an odd crash with one of our server. We know what caused it, but we don't know why SQL Server responded the way it did.

SQL Server 2016

Here is what happened:

Friday: Changed MAXDOP from 4 to 2 without checking how much of an impact this would have on compiled queries.

Saturday Morning: Server progressively uses more CPU starting at 2 AM until it hits 100% at 5 AM and Active RAM immediately drops to 500MB from 44GB (what?!?!). Server is rebooted, all seems well.

Monday Morning: at 1:05 AM when one of the nightly batch job starts, the server hits 100% CPU and drops active memory to 500 MB by 1:15 AM.

There are a significant number of large queries that run every morning on this server as part of our nightly batch processing. Looking at the Query Store I can see they were all compiled for 4 cores before the change to MDOP on Friday.

So, all the large queries that used to have 4 cores had to be recompiled for 2, which made all of them much slower during one of the busiest time frames for this server.
I understand how this would significantly affect performance on the server, but I don't understand why the server became completely unresponsive, and had so little active memory.

Does anyone have some experience with this kind of behaviour?

Check out this interesting post from David Klee: VM Memory Counters Lie for SQL Server VMs

In particular, this quote is very relevant to your situation:

  SQL Server uses memory as a working set I/O buffer, and memory blocks in the SQL Server buffer pool are most commonly used as a read cache. Memory blocks that are repeatedly read only show up in the active counters if that block has been read from or written to in the last sample interval, which is usually 20 seconds. Therefore, SQL Servers that have an appropriate (or too high, but that’s a different story) amount of memory assigned to the VM will have most of the commonly accessed data in memory, but those blocks are rarely accessed in their entirety over that short of a sample window. The VM active memory counters will show a very low active value, but this SQL Server could actually be very busy.

This explains your first issue (related to "active" memory).  In essence, the metric you were looking at isn't reliable, so it doesn't tell us much of substance about the situation.

The other question, related to unresponsiveness, is harder to pin down without seeing wait stats from the time of the outage, or specifics of the recompiled execution plans.

If I were going to speculate, a plausible theory might be that:

queries now running at MAXDOP 2 (instead of 4) take significantly longer to complete
queries are arriving at the server at the same rate as before
so now there are more queries running at once (since they take longer to complete)
the server starts to experience thread starvation (threadpool waits) because there are so many concurrent queries running
if the queries require memory grants, they could also start queuing up for memory (resource_semaphore waits)

All of these things would combine to make your server seem unresponsive.  It might also cause the "active memory" in the VM to drop, since memory pages might be accessed less (due to everything waiting on CPU resources).


"To many rows being processed at one. Reduce and try again." error in SQL Server Management Studio I get this error when trying to update some rows from SQL Server Management Studio v18.5:

Msg 50000, Level 16, State 1, Procedure UPDATElimit, Line 7 [Batch
Start Line 0] To many rows being processed at one.  Reduce and try
again. Msg 3609, Level 16, State 1, Line 1 The transaction ended in
the trigger. The batch has been aborted.

How can I bypass this limitation?
Trigger code:
ALTER TRIGGER [dbo].[UPDATElimit]
on [dbo].[table]
FOR UPDATE
AS
BEGIN
    IF (select count(*) from inserted) > 1000
    BEGIN
        RAISERROR('To many rows being processed at one. Reduce and try again.', 16, 1)
        ROLLBACK
    END
END

To clarify on @McNets comment: The error isn't produced by SQL Server itself. Somebody created a trigger on the table, a trigger that fires on UPDATE. This trigger apparently does a check for number of rows and if above a certain threshold, it generates this error and does a rollback.
You should try to find who is responsible for the trigger and take it from there. Perhaps it is OK to do an exception to that trigger rule in this particular case? Etc.


Star join query optimization - Change partitioning, use columnstore? I am trying to know the best possible way to improve performance of a query given to me by my client. It contains a few tables which are joined and one of table is called dwh.fac_sale_detail which contains 1.5 billion rows.

This table dwh.fac_sale_detail is partitioned based on one of its columns called TradingDateKey1.  It actually stores data in yyyymmdd format but it is of INT Datatype.

This has TradingDateKeys from 2005 to 2015, but the partitions are created until year 2014 only.

One of the guys in another team advised the following and I am trying to follow his advice but I am new to creating or altering of partitions and don't know if this will actually make any difference to query performance:

What he said in his own words is "The FactSalesDetail table is currently about 1.5 Billion Rows and is currently partitioned on TradingDate into 10 partitions by year with about 150 Million Rows per partition. It would be a good idea to further partition the latest year into monthly partitions and apply a columnstore index on all the partitions. Applying the index on each partition will be a once-off and you should only need to maintain the index for the current partition going forward."

Here is the query plan for the query I am trying to optimize.

Please also see the attached screenshots for a better understanding:

Thanks for adding the query plan; it is very informative.  I have a number of recommendations based on the query plan, but first a caveat: don't just take what I say and assume it's correct, try it out (ideally in your testing environment) first and make sure you understand why the changes do or don't improve your query!

The query plan: an overview

From this query plan (as well as the corresponding XML), we can immediately see a few useful pieces of information:

You are on SQL 2012
This is a classic star join query and you are getting the benefit of the in-row bitmap filter optimization that was added in SQL 2008 for such plans
The fact table contains about 1.5 billion rows, and just over 500 million of those rows match the dimension filters
The query requests 72GB of memory, but is only granted 12GB of memory (presumably, 12GB is the max that will be granted to any given query, meaning your machine likely has ~64GB of memory)
SQL Server is performing a sort-stream aggregate that takes 500 million rows down to just 600,000 rows.  The sort is exceeding it's memory grant and spilling to tempdb
We have warnings for plan-affecting converts due to either explicit and implicit conversions in your query
The query uses 32 threads, but the initial seek into your fact table has an enormous thread skew; just 2 of the 32 threads do all of the work.  (At subsequent steps in the query plan, however, the work is more balanced.)

Optimization: columnstore or not

This is a tough question, but on balance I would not recommend columnstore for you in this case.  The primary reason is that you are on SQL 2012, so if you are able to upgrade to SQL 2014 I think it might be worth trying out columnstore.

In general, your query is the type that columnstore was designed for and could benefit greatly from the reduced I/O of columnstore and the greater CPU efficiency of batch mode.

However, the limitations of columnstore in SQL 2012 are just too great, and the tempdb spill behavior, where any spill will cause SQL Server to abandon batch mode entirely, can be a devastating penalty that might come into play with the large volumes of rows you are working with.  If you do go with columnstore on SQL 2012, be prepared to baby-sit all of your queries very closely and ensure that batch mode can always be used.

Optimization: more partitions?

I don't think that more partitions will help this particular query.  You are welcome to try it, of course, but keep in mind that partitioning is primarily a data management feature (the ability to swap in new data in your ETL processes via SWITCH PARTITION and not a performance feature. It can obviously help performance in some cases, but similarly it can hurt performance in others (e.g., lots of singleton seeks that now have to be performed once per partition).

If you do go with columnstore, I think that loading your data for better segment elimination will be more important than partitioning; ideally you will probably want as many rows in each partition as possible in order to have full columnstore segments and great compression rates.

Optimization: improving cardinality estimates

Because you have a huge fact table and a handful of very small (hundreds or thousands of rows) set of rows from each dimension table, I would recommend an approach where you explicitly create a temporary table containing only the dimension rows that you plan to use.  For example, rather than join to Dim_Date with a complicated logic like cast(right(ALHDWH.dwh.Dim_Date.Financial_Year,4) as int)  IN  ( 2015, 2014, 2013, 2012, 2011  ), you should write a pre-proccessing query to extract only the rows from Dim_Date that you care about and add the appropriate PK to those rows.

This will allow SQL Server to create statistics on just the rows you are actually using, which may yield better cardinality estimates throughout the plan.  Because this pre-processing would be such a trivial amount of work compared to the overall query complexity, I would highly recommend this option.

Optimization: reducing thread skew

It's likely that extracting the data from Dim_Date into it's own table and adding a primary key to that table would also help to reduce thread skew (an imbalance of work across threads).  Here's a picture that helps show why:

In this case, the Dim_Date table has 22,000 rows, SQL Server estimated that you are going to use 7,700 of those rows, and you actually only used 1,827 of those rows.

Because SQL Server uses statistics in order to allocate ranges of rows to threads, the poor cardinality estimates in this case are likely the root cause of the very poor distribution of rows.

Thread skew on 1,872 rows may not matter much, but the painful point is that this then cascades down to the seek into your 1.5 billion row fact table, where we have 30 threads sitting idle while 600 million rows are being processed by 2 threads.

Optimization: getting rid of the sort spill

Another area I would focus on is the sort spill. I think that the primary problem in this case is poor cardinality estimates.  As we can see below, SQL Server thinks that the grouping operation being performed by the combination of a Sort and Stream Aggregate will yield 324 million rows.  However, it actually yields just 643,000 rows.

If SQL Server knew that so few rows would come out of this grouping, it would almost certainly use a HASH GROUP (Hash Aggregate) rather than a SORT GROUP (Sort-Stream) in order to implement your GROUP BY clause.

It's possible that this may fix itself if you make some of the other changes above in order to improve cardinality estimates.  However, if it doesn't you could try to use the OPTION (HASH GROUP) query hint in order to force SQL Server to do so. This would let you evaluate the magnitude of the improvement and decide whether or not to use the query hint in production.  I would generally be cautious about query hints, but specifying just HASH GROUP is a much lighter touch than something like using a join hint, using FORCE ORDER,  or otherwise taking too much of the control out of the query optimizer's hands.

Optimization: memory grants

One last potential problem was that SQL Server estimated that the query would want to use 72GB of memory, but your server was not able to provision this much memory to the query.  While it's technically true that adding more memory to the server would help, I think there are at least a couple other ways to attack this problem:

Get rid of the Sort operator (as described above); it's really the only operator consuming any substantial memory grant in your query
Split your query up into multiple batches; it may be the case that you can run the query once per partition, for example.  This could reduce the size of the sort, keep it in memory, and potentially improve performance significantly. A side benefit could be that you might get better utilization of threads if you access just one partition since this does impact the way that SQL Server allocates threads to partitions in some cases.