Nested query not recognize external table i need to a query that show week by week the total amount of imports of any company and in addition add a ranking position depending on that amount.

I've made this query but the problem is the inner select does not recognize the w1 table.

MySql give me this error:

#1054 - Unknown column 'w1.Year' in 'where clause'

SELECT w1.CodC, c.NameC, w1.Week, w1.Year, (
    SELECT @rn:=@rn+1
    FROM(
        SELECT w2.CodC as Code, Sum(w2.Imports) As Amount
        FROM Weekly w2
        WHERE   w2.Year=w1.Year AND
                w2.Week<=w1.Week
        GROUP BY w2.CodC
        order by Amount DESC
    ) tab1,
    (
        SELECT @rn:=0
    ) tab2
) AS RkAmount
FROM Weekly w1
LEFT JOIN Companies c on c.CodC=w1.CodC
ORDER BY w1.Year DESC, w1.Week DESC, RkAmount ASC

How can i proceed?

Thanks a lot

Edit

The structure of Weekly table is:

CodC - Week - Year - Imports

and contains one entry for each society for each week if the import is >0.

Table Companies is only a Company description (in this query usefull only for name decoding).

And finally what i want is to Rank Companies by Imports sum from the beginning of the year.

Here an example:

CodC - W - Year - Import
AAAA - 1 - 2014 - 1000
BBBB - 1 - 2014 - 500
AAAA - 2 - 2014 - 700
BBBB - 2 - 2014 - 200
CCCC - 2 - 2014 - 2000

What i wanna output is

CodC - NameC - W - Year - RkAmount
CCCC - CompC - 2 - 2014 - 1
AAAA - CompA - 2 - 2014 - 2
BBBB - CompB - 2 - 2014 - 3
AAAA - CompA - 1 - 2014 - 1
BBBB - CompB - 1 - 2014 - 2

I hope this example has been more explanatory.

The reason for the error is explained in MySQL documentation in part E.4. Restrictions on Subqueries as:

Subqueries in the FROM clause cannot be correlated subqueries. They are materialized in whole (evaluated to produce a result set) during query execution, so they cannot be evaluated per row of the outer query.

Changing query a bit like the following seems to solve your problem:
SELECT t.CodC, c.NameC, t.Amount, t.Year, t.Week,
    IF(t.Week!=@week OR t.Year!=@year, @rank:=1, @rank:=@rank+1) AS rank,
    @week:=t.Week, @year:=t.Year
FROM
    (SELECT CodC, Sum(Imports) As Amount, Year, Week
        FROM Weekly
        WHERE Year=2014
        GROUP BY CodC, Year, Week
        ORDER BY Year DESC, Week DESC, Amount DESC) t
    JOIN (SELECT @rank:=0, @week:=-1, @year:=-1) t2 ON 1
    LEFT JOIN Companies c ON c.CodC=t.CodC

It basically adds an artificial rank column by checking year and week values on the ordered rows.
Edit
Previous query is given to order by weekly sum because of misunderstanding. Following query will give what you want
SELECT t2.*,
    IF(t2.Week!=@week OR t2.Year!=@year, @rank:=1, @rank:=@rank+1) AS rank,
    @week:=t2.Week, @year:=t2.Year
FROM
    (SELECT t1.*, SUM(w2.Imports) AS cnt
    FROM
        (SELECT w1.CodC, c.NameC, w1.Week, w1.Year
        FROM Weekly w1
            LEFT JOIN Companies c on c.CodC=w1.CodC
            ORDER BY w1.Year ASC, w1.Week ASC) t1
            LEFT JOIN Weekly w2 ON t1.CodC=w2.CodC AND w2.Year=t1.Year AND w2.Week<=t1.Week
            GROUP BY t1.CodC, t1.Week, t1.Year
            ORDER BY t1.Year DESC, t1.Week DESC, cnt DESC
            )t2
    JOIN (SELECT @rank:=0, @week:=-1, @year:=-1) t3 ON 1

Fiddle


Export remote Postgres table to CSV file on local machine I have read-only access to a database on a remote server. So, I can execute:

COPY products TO '/tmp/products.csv' DELIMITER ',';

But on that server I don't have permissions to create/save a file, so I need to do this on my local machine.

When I connect to the remote database, how can I execute a command to save the file on my local machine instead of the remote server?

Or, how can I execute a Linux command to connect to the remote database, execute a query, and save the output as a file to my local machine?

Both the approaches already suggested appear to be unnecessarily complicated.

Just use psql's built-in \copy command, which works just like server-side COPY but does a copy over the wire protocol to the client and uses client paths.

Because it's a psql backslash command you omit the trailing semicolon, eg:

\copy products TO '/tmp/products.csv' CSV DELIMITER ','

See the \copy entry in the manual for the psql command and the COPY command documenation for more detail.

Just like COPY you can use \copy with a (SELECT ...) query instead of a table name when copying data out (but not in).

A generally inferior alternative that can be useful in a few limited situations is to use:

psql -t -P format=unaligned -P fieldsep_zero=on -c 'SELECT * FROM tablename'

and use the -o flag or shell output redirection to write the output to a file. You should almost always use \copy in preference to this.


Update Table contents from another table I want to update the products table from my store db from a temp table.. the production table 'Products' needs to update all columns from the temp table 'tmpprod' based on StockCode and not change any other columns in the Products table

UPDATE Products
JOIN tmpprod
ON Products.StockCode = tmpprod.StockCode
    SET Products.CategoryCode = tmpprod.CategoryCode,
        Products.CategoryName = tmpprod.CategoryName,
        Products.SubcategoryID = tmpprod.SubcategoryID,
        Products.SubcategoryName = tmpprod.SubcategoryName,
        Products.BarCode = tmpprod.BarCode,
        Products.Weight = tmpprod.Weight,
        Products.ManufacturerSKU = tmpprod.ManufacturerSKU,
        Products.ProductName = tmpprod.ProductName,
        Products.WarrantyLength = tmpprod.WarrantyLength,
        Products.Manufacturer = tmpprod.Manufacturer,
        Products.DBP = tmpprod.DBP,
        Products.DBP5 = tmpprod.DBP5,
        Products.RRP = tmpprod.RRP,
        Products.StandardRRP = tmpprod.StandardRRP,
        Products.AvailabilityTotal = tmpprod.AvailabilityTotal,
        Products.AvailabilityAdelaide = tmpprod.AvailabilityAdelaide,
        Products.AvailabilityBrisbane = tmpprod.AvailabilityBrisbane,
        Products.AvailabilitySydney = tmpprod.AvailabilitySydney,
        Products.AvailabilityMelbourne = tmpprod.AvailabilityMelbourne,
        Products.AvailabilityPerth = tmpprod.AvailabilityPerth,
        Products.ImageUrl = tmpprod.ImageUrl,
        Products.ImageFilename = tmpprod.ImageFilename,
        Products.DescriptionLink = tmpprod.DescriptionLink

and...

CREATE TABLE `Products` (
    `StockCode` text,
    `CategoryCode` text,
    `CategoryName` text,
    `SubcategoryID` int(11) DEFAULT NULL,
    `SubcategoryName` text,
    `BarCode` text,
    `Weight` double DEFAULT NULL,
    `ManufacturerSKU` text,
    `ProductName` text,
    `WarrantyLength` int(11) DEFAULT NULL,
    `Manufacturer` text,
    `DBP` int(11) DEFAULT NULL,
    `DBP5` int(11) DEFAULT NULL,
    `RRP` double DEFAULT NULL,
    `StandardRRP` double DEFAULT NULL,
    `AvailabilityTotal` text,
    `AvailabilityAdelaide` text,
    `AvailabilityBrisbane` text,
    `AvailabilitySydney` text,
    `AvailabilityMelbourne` text,
    `AvailabilityPerth` text,
    `ImageUrl` text,
    `WebsiteUrl` text,
    `ImageFilename` text,
    `DescriptionLink` text,
    `DateAdded` datetime DEFAULT CURRENT_TIMESTAMP,
    `ForSale` int(11) NOT NULL DEFAULT \'0\'
) ENGINE=InnoDB DEFAULT CHARSET=latin1'

and...

'CREATE TABLE `tmpprod` (
    `StockCode` text,
    `CategoryCode` text,
    `CategoryName` text,
    `SubcategoryID` int(11) DEFAULT NULL,
    `SubcategoryName` text,
    `BarCode` text,
    `Weight` double DEFAULT NULL,
    `ManufacturerSKU` text,
    `ProductName` text,
    `WarrantyLength` int(11) DEFAULT NULL,
    `Manufacturer` text,
    `DBP` int(11) DEFAULT NULL,
    `DBP5` int(11) DEFAULT NULL,
    `RRP` double DEFAULT NULL,
    `StandardRRP` double DEFAULT NULL,
    `AvailabilityTotal` text,
    `AvailabilityAdelaide` text,
    `AvailabilityBrisbane` text,
    `AvailabilitySydney` text,
    `AvailabilityMelbourne` text,
    `AvailabilityPerth` text,
    `ImageUrl` text,
    `WebsiteUrl` text,
    `ImageFilename` text,
    `DescriptionLink` text,
    `DateAdded` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1'

According to Postgres docs (9.6):

UPDATE Products as P
SET P.CategoryCode = T.CategoryCode,
    P.CategoryName = T.CategoryName,
    P.SubcategoryID = T.SubcategoryID,
    P.SubcategoryName = T.SubcategoryName,
    P.BarCode = T.BarCode,
    P.Weight = T.Weight,
    P.ManufacturerSKU = T.ManufacturerSKU,
    P.ProductName = T.ProductName,
    P.WarrantyLength = T.WarrantyLength,
    P.Manufacturer = T.Manufacturer,
    P.DBP = T.DBP,
    P.DBP5 = T.DBP5,
    P.RRP = T.RRP,
    P.StandardRRP = T.StandardRRP,
    P.AvailabilityTotal = T.AvailabilityTotal,
    P.AvailabilityAdelaide = T.AvailabilityAdelaide,
    P.AvailabilityBrisbane = T.AvailabilityBrisbane,
    P.AvailabilitySydney = T.AvailabilitySydney,
    P.AvailabilityMelbourne = T.AvailabilityMelbourne,
    P.AvailabilityPerth = T.AvailabilityPerth,
    P.ImageUrl = T.ImageUrl,
    P.ImageFilename = T.ImageFilename,
    P.DescriptionLink = T.DescriptionLink
FROM tmpprod as T
WHERE P.StockCode = T.StockCode;

Although I used an alias, it is not indispensable.


Is it require to fill all tables inside of the database? Now,I created upper that table and in order to test I will give 3 textboxes values as below.

SqlCommand cmd = new SqlCommand
("Insert Into dbo.PrnInf(PrnName, PrnSurName, PrnEgn )     Values
('"+txtPrnName.Text+", "+txtPrnSurName.Text+","+txtPrnEgn.Text+"')", conn);
cmd.ExecuteNonQuery();

However,I'm getting the error in the "cmd.execute".Also,I defined the primary key and identity specification on the primary key.
the moral of the story,is it require to fill all tables inside of the database?

Best Regards,

BAD PRACTICE ALERT!

You need to delimit each string. Not the whole lot.

Note where the single quotes are:

("Insert Into dbo.PrnInf(PrnName, PrnSurName, PrnEgn )     Values
('"+txtPrnName.Text+"', '"+txtPrnSurName.Text+"','"+txtPrnEgn.Text+"')", conn);

However, this can lead to SQL Injection. So...

GOOD PRACTICE!

Parameterise your query

SqlCommand cmd = new SqlCommand
    ("Insert Into dbo.PrnInf(PrnName, PrnSurName, PrnEgn )     Values
    (@PrnName, @PrnSurName, @PrnEgn)", conn);
cmd.Parameters.AddWithValue("@PrnName", txtPrnName.Text);
cmd.Parameters.AddWithValue("@PrnSurName", txtPrnSurName.Text);
cmd.Parameters.AddWithValue("@PrnEgn", txtPrnEgn.Text);
cmd.ExecuteNonQuery();


How to trace SQL queries which are crashing SQL Server We have a SQL Server 2008 database server (it happens to be running under MS Failover Clustering, but I don't think that is relevant here).

Our application runs Hibernate for DB access, and since we upgraded recently from v3.1 to 3.6, we have been experiencing SQL Server crashing regularly (every 24-48 hours, but sometimes more frequently).

The specific problem in question seems to be memory related.  Just before the server crashes (and is then auto restarted by the failover cluster manager it seems), we get a load of these errors:

Error: 701, Severity: 17, State: 130.
There is insufficient system memory in resource pool 'internal' to run this query.

also occasional (but regular) messages of

Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

Error: 17312, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

I'm also getting some app level errors such as

java.sql.SQLException: A time out occurred while waiting to optimize the query. Rerun the query.

and then the exciting and possibly instructive error:

The query processor ran out of internal resources and could not produce a query plan.
This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions.
Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

The load on the server hasn't changed so there's no reason it should now be running out of memory when it previously wasn't indicating a problem with queries being sent to it.

Now to the question - how do I trace the queries that are causing this error (and thus presumably all of the problems)?  It seems like since our Hibernate upgrade, it's been firing some huge queries at SQL Server, and that's broken it.  As it happens, I have some ideas as to what they might be, but it would be good to be able to trace them.

I can of course run SQL Server profiler, but once this is done (and produced an enormous amount of data - it's a busy OLTP database), how do I filter to find the problematic queries?

Thanks!

Follow the steps outlines in How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server. The remedy action will depend on your findings. You can also read How to Identify Microsoft SQL Server Memory Bottlenecks which is more accessible.

One word of caution though: is unlikely you will find individual queries to blame. Tracking downs memory problems is more subtle than that. Keep in mind that when you're running out of resources and a query throws an out-of-memory error it may well be that the query that throws the error is just the victim, not the culprit.


How to simplify SSAS deployment from DEV to PRO environment Here is my issue: In my company, there is a team of developper who creates cubes, dimensions(...) in SSAS in a DEV environment (let's call it SSASDEV). This environment is bounded to a SQL Server database in DEV (let's call it SQDEV).
My job is to deploy their work from DEV environment to PRO environment. This PRO environment (SSASPRO) is based on another SQL Server database (SQPRO).

For now, the developper team script the SSAS, send me the XMLA script, I need to change every security rule defined and the connection string specified in this XMLA (security rules because it's based on different Roles depending on the environment and the connection string because of the SQL Server database).
It's a heavy work to do for each deployment so I want to automatize this.

The only ways I found is

(1) - to script the DEV cube and apply the XMLA on PRO (what is done now).

(2) - to synchronize cubes (which means, unprocess, re-apply security rules and modify connectionstring).

I can not imagine I'm the only one in this situation in the entire world!
Does anyone has a tip or a hint for me? Does another simpliest way exists and I missed it?
Is my intern organization (differents databases for each environment) not logical for SSAS?

I work with SSAS 2008R2 and 2012

Thanks a lot for your responses!

I will tell you the way we deploy in my place SSAS projects between different environments. We use a set of Powershell scripts that:

Part 1:

get the last version from the build server and deploy the file to generate the last working version of XMLA;
take the generated XMLA from the build server and copy it to the QA server;

Part 2:

Now, on the QA server, we change manually the server name, the database name (set as parameters to the PS script), we verify that all required scripts are there;
one PS script runs the resulted XMLA on the current QA server, so the SSAS database is created (not processed though);
a XMLA script that commands the full process of the cube and its dimensions;

All of these steps are done as automatically as possible, with as little intervention from the developer/QA guy as possible. We do, though, insert some eyeballing in between, just to keep our minds safe. I won't say that it's the best, but it works and gets the job done. Before the last step I'm sure you can insert any XMLA script to remove existing roles and create new ones. And to change a PS script to execute some new SQL or XMLA script is not hard at all. I suppose that the roles and permissions don't change that much, so they can be scripted and reused.

PS: this is an environment with domain users and fixed servers (so we can use shares, domain accounts and all that fluff). If you have another environment it might be a more difficult to get it done.


Address Windowing Extensions Overhead I just found another SQL Server (2008r2) with AWE enabled on a 64-bit OS/SQL combination.  Has anyone done an research as to the overhead created by enabling AWE on the 64-bit OS/SQL combination?  It is mostly a question of curiosity, though it would be nice to be able to put into the analysis documentation.  Thanks!

Has anyone done an research as to the overhead created by enabling AWE on the 64-bit OS/SQL combination?

This is one of the Myths that Paul Randal clearly debunks in his famous series - A SQL Server DBA myth a day

  On 64-bit systems (2005+): AWE is not required (and in fact enabling it does nothing).

Also refer to Fun with Locked Pages, AWE, Task Manager, and the Working Set… by Bob Ward :

  The AWE APIs for 32bit and 64bit SQL Server systems are used for different purposes. In 32bit it is really to extend memory access beyond 4Gb or to enable the AWE feature. For 64bit systems, it is to possibly gain performance and to “lock pages” for the buffer pool.

and

  In fact, the code for SQL Server for 64bit systems ignores awe enabled sp_configure option. It is a “no-op” for 64bit SQL Server systems.


professional services (consultants) resource allocation system I am developing a resource allocation system using PHP and mysql. I have consultants and projects and I need to allocate the consultants on to various projects for various periods of time on various dates.

There are three types of allocation options.

Straight forward allocation where a consultant is assigned to a project from Jan 1 to July 1st.
Multiple allocations, where a consultant will work on the same projects on random dates. Like Jan 1st to Jan 15th, than March 1st to 31st March and finally June 15th to July 1st.
Recurring assignments, where there is a start date and the end date and a consultant is working Monday and Tuesday for 6 months on one project and Wednesday, Thursday and Friday on another project for 3 or 6 months whatever.

Here is what I have done so far.

I have a user table which contains all the consultants.
I have a project table which contains the projects.
I have an assignment table that contains the information about which
consultant is working on which project. It also contains the starting and end date of the assignment
Now I have a fourth table - assignment_days. This contains the data of which days/dates the consultant is working on a project.

Here is my problem. I am thinking of saving all the days/dates in this table. This table contains: id, assignmentid, projectid, consultantid, dates/days and completed. So if there is an assignment for 6 months than I will insert 182 days/dates (records) in this table.
I am not sure if I am doing it correctly or if it is the most efficient way to do it. If this is not the way to do it. Can you suggest a different solution?

And here is the final output i am trying to achieve

This is the list of all the Mondays dates from today onwards for 6 months (which will go to 12 months eventually). It has a number of days a consultant is working on a particular project and at the bottom is the percent allocation or utilization of a resource in a week.

I sure can use someone elses point of view here.

Thanks.

Google "open source project management software" - I got 49 million hits. There appear to be interesting options here, here and here.

There is very little that is truly original in software. What I would advise you to do is to download (a few of the) projects which look interesting, then check out their data models - that's the beauty of Open Source - look at the ideas of others and then adapt them to your own particular situation.

Many of the projects appear to be right up your alley, with many time assignment and/or management options which appear to be the core of your question.


This should do the job :

SELECT
  ph1.sku,
  ph1.id as ph1Id,
  ph2.id as ph2Id,
  ph1.histDate,
  ph2.amount as oldAmount,
  ph1.amount as newAmount
FROM priceHistory as ph1
LEFT JOIN priceHistory as ph2 ON
ph2.id =
(SELECT max(id) FROM priceHistory AS ph3 WHERE ph3.id < ph1.id and ph1.SKU = ph3.SKU)
WHERE ph1.amount <> ph2.amount OR
      ph2.amount is null

Here is the fiddle.


How to Upgrade Redis Database from 5.x to 6.x This instance was running Redis version 5.0.8 open source. The Redis packages have been updated to the current 6.0.3 and now the server reports Loading RDB produced by version 5.0.8 on startup. What is the proper method for upgrading the RDB to the version of the server? I have checked the admin documentation and there's nothing mentioned. Also, I checked the 6.0 release notes and nothing there either.

The upgrade is automatic on first run of Redis after upgrading the packages. Subsequent times that Redis starts it reports Loading RDB produced by version 6.0.3.


Why so many loops in pkey index scan? My query executes very slowly despite all indexes in place:

SELECT * FROM "entry"
    INNER JOIN "entrytag" ON ("entry"."id" = "entrytag"."entry_id")
    WHERE "entrytag"."tag_id" = 323456
    ORDER BY "entry"."date"
    DESC LIMIT 10'

the explain shows too many loops, why? how to fix this?

Limit  (cost=1241.85..1241.87 rows=10 width=666) (actual time=23576.449..23576.454 rows=10 loops=1)
  ->  Sort  (cost=1241.85..1242.10 rows=99 width=666) (actual time=23576.446..23576.447 rows=10 loops=1)
        Sort Key: entry.date DESC
        Sort Method: top-N heapsort  Memory: 31kB
        ->  Nested Loop  (cost=0.87..1239.71 rows=99 width=666) (actual time=0.168..22494.187 rows=989105 loops=1)
              ->  Index Scan using entrytag_tag_id_row_idx on entrytag  (cost=0.44..402.17 rows=99 width=4)
                  (actual time=0.093..535.664 **rows=989105** loops=1)
                    Index Cond: (tag_id = 323456)
              ->  Index Scan using entry_pkey on entry  (cost=0.43..8.45 rows=1 width=666)
l time=0.020..0.021 rows=1 **loops=989105**)
                    Index Cond: (id = entrytag.entry_id)
Planning time: 0.829 ms
Execution time: 23576.504 ms

Indexes i have on table entry:

('id', 'date', ...other irrelevant cols)
('date', ...other irrelevant cols)

On association table entrytag:

(tag_id, entry_id)
(tag_id, row)  -- this index is used according to the explain

PostgreSQL v 9.5. There are many rows, the db is quite big. The same queries for other tags (with the same number of entries) take fractions of seconds and no such huge row and loop counts.

The issue is here

Index Scan using entrytag_tag_id_row_idx on entrytag
    (cost=0.44..402.17 rows=99 width=4)
    (actual time=0.093..535.664 rows=989105 loops=1)

Your statistics are off for

WHERE "entrytag"."tag_id" = 323456

Your planner thinks there are far fewer tag_id=323456 then there are. You may want to try ANALYZE entrytag;, and trying again. Or upping statistics.

ALTER TABLE entrytag
  ALTER COLUMN tag_id
  SET STATISTICS 1000;

Then try ANALYZE entrytag; and trying again. Sounds like a typical case of bad statistics.

You may want to improve your schema, or denormallize. You're joining, selecting, and ordering million rows. It's not going to be instant.


what is the difference of sequece of index in MySQL I am now using this command to select in MySQL 5.7:
SELECT game_id
        FROM platform_reward_record
        WHERE hub_sync = 0
        GROUP BY game_id
        LIMIT 100

when I create index like this:
ALTER TABLE `packet_db`.`platform_reward_record`
ADD INDEX `idx_hubsync_gameid` (`game_id`, `hub_sync`);

the query is very slow, but using this index:
ALTER TABLE `packet_db`.`platform_reward_record`
ADD INDEX `idx_hubsync_gameid` (`hub_sync`, `game_id`);

it is fast, why the field sequence affected whe slect speed? What should I learn from it?

Think of an index as an ordered data structure. Technically it's more than that, but when it comes to query optimisation, usually this is all you need to know.
To explain why this matters in your case, I'll use a common analogy: a phonebook. A phonebook is a dataset ordered by (surname, name), so it's actually an index made of paper.
Because it's ordered, looking for Tom Baker is reasonably fast. Finding all Bakers would also be reasonably fast (unless they're thousands... but that's because you're a human, not a machine). Finding all Toms would be impossible. Or better said, it's possible, but you have to scan ALL entries from the beginning to the end. It's slow, it's like not using an index at all. If the index were ordered by (name, surname), you could find all the Toms but not all the Bakers, and you'd still be able to find Tom Baker.
So now you should understand why, in general, the order of columns of an index matters. But let's see why it matters in your case.
You want to restrict your search to a certain hub_sync, and then group the rows by game_id. In this order! The opposite wouldn't work: once you grouped the rows you lose the hub_sync information.
So first you apply the WHERE, and you need to use the first row of the index for that (remember: you can quickly find the Bakers, not the Toms). Then you can group the rows by the second row of the index.
As a side note, you can use DISTINCT instead of GROUP BY. It's not faster, but it makes it more clear what you want to achieve.


Treat database field as a flat file source We have a partner sending us csv data to a web service of ours that gets sent to a messaging system that is SQL Server based.

I am writing data to disk and then importing to another database by using this file as a flat file source in an SSIS Data Flow task.

Although this works I'd like to avoid using the file system and treat the database field as a flat file source.

Is this possible?

I'm very new to SSIS packages and searching for this scenario is very difficult because of the number of hits just relating to importing a csv file.

My first inclination would be to use a script component (e.g. C#) as source in a Data Flow Task.  The script would just read the column contents, parse the individual records and fields, and send records down the pipeline.  The individual fields (perhaps strongly-typed) can be defined in the output record definition of the designer.

https://msdn.microsoft.com/en-us/library/ms136060.aspx contains an example of parsing a CSV file and processing a SQL query.  You can combine the two examples for your needs.  Below is an example of this. I don't have time to test right now but it should get you started.

public class ScriptMain:
    UserComponent

{
    IDTSConnectionManager100 connMgr;
    SqlConnection sqlConn;
    SqlDataReader sqlReader;

    public override void AcquireConnections(object Transaction)
    {
        connMgr = this.Connections.MyADONETConnection;
        sqlConn = (SqlConnection)connMgr.AcquireConnection(null);

    }

    public override void PreExecute()
    {

        SqlCommand cmd = new SqlCommand("SELECT CsvContents FROM dbo.CsvImportTable;", sqlConn);
        sqlReader = cmd.ExecuteReader();

    }

    public override void CreateNewOutputRows()
    {

        while (sqlReader.Read())
        {
            {
                string csvContents = sqlReader.GetString(0);
                CreateNewOutputRowsFromCsv(csvContents);
            }
        }

    }

    public void CreateNewOutputRowsFromCsv(string csvContents)
    {

        string nextLine;
        string[] columns;

        char[] delimiters;
        delimiters = ",".ToCharArray();

        nextLine = textReader.ReadLine();
        while (nextLine != null)
        {
            columns = nextLine.Split(delimiters);
            {
                MyAddressOutputBuffer.AddRow();
                MyAddressOutputBuffer.AddressID = columns[0];
                MyAddressOutputBuffer.City = columns[3];
            }
            nextLine = textReader.ReadLine();
        }

    }

    public override void PostExecute()
    {

        sqlReader.Close();

    }

    public override void ReleaseConnections()
    {

        connMgr.ReleaseConnection(sqlConn);

    }

}


SQL Deadlocks - Same Stored Procedure Called Concurrently I am working with a relatively complex system, where static tables are updated from multiple data sources as they are needed, because dynamically loading the data takes 4-5 seconds and we prefer to display results to the user quickly.

The process:

User or application opens a specific order
A stored procedure is called to retreive the data (lets call it dbo.Get_Data)
dbo.Get_Data checks event logs to see if there have been any changes since the last time it looked for the data
If it finds a new event, it runs the expensive query to update the data
Data is returned

IF (    SELECT [LastStaticUpdateEvent] < [LastSaveEvent]
        FROM [dbo].[Events]
        WHERE [OrderNumber] = @OrderNumber
    )
    BEGIN
       -- Update Static table
       UPDATE [Static]
       SET [Static].[A] = [App].[A]
          ,[Static].[B] = [App].[B]
       FROM [dbo].[AppData]            AS [App]   -- View with many joins (4-5 secs)
       INNER JOIN [dbo].[StaticResuts] AS [Static]
           ON [Static].[OrderNumber] = [App].[OrderNumber]
       WHERE [App].[OrderNumber] = @OrderNumber

       -- Update Event Log
       UPDATE [dbo].[Events]
       SET [LastStaticUpdateVent] = SYSDATETIME()
       WHERE [OrderNumber] = @OrderNumber

    END

SELECT * FROM [dbo].[StaticResults]

The problem is that this data can be requested concurrently.  If User A and User B both call dbo.Get_Data before the first UPDATE is complete, a deadlock occurs.

Is there a good method or pattern to have the 2nd call wait until the 1st is finished before continuing with its lookups?

Is there a good method or pattern to have the 2nd call wait until the 1st is finished before continuing with its lookups?

The easiest way to have a block of code that can be run by only one session at a time is to use an Application Lock.  You can use SQL Server's locking engine, but instead of locking a specific row, page, or table, you create a lock with a custom name.  eg

begin transaction;

--begin exclusive section
exec sp_getapplock @Resource = 'Get_Data Exclusive Lock', @LockMode = 'Exclusive';

--do stuff in only one session

--end exclusive section
exec sp_releaseapplock @Resource = 'Get_Data Exclusive Lock';

commit transaction;


Finding Database Default Locations I am working on creating a script to restore database from a backup file to default location of an instance, which is set away from system databases installation folder.

Currently, I'm using to find default DATA Location

 SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
    FROM master.sys.master_files
    WHERE database_id = 1 AND file_id = 1

and default LOG Location

 SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'mastlog.ldf', LOWER(physical_name)) - 1)
    FROM master.sys.master_files
    WHERE database_id = 1
        AND file_id = 2
    )

Is there any other way to find database default location with the help of query?

Thank you for time.

Most SQL Servers are configured for the non-system databases to exist apart from the system databases.  That is what is normally meant by the default path.

You can lookup some code for finding this at:

http://www.codeproject.com/Articles/684020/Checking-MS-SQL-Server-database-files-default-path

Also, Alex Aza has provided a popular post over at StackOverflow. The key is to get the data from the registry keys where they are stored.  Here is a snippet from Alex's longer script:

declare @DefaultData nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
   N'Software\Microsoft\MSSQLServer\MSSQLServer',
   N'DefaultData', @DefaultData output

declare @DefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
   N'Software\Microsoft\MSSQLServer\MSSQLServer',
   N'DefaultLog', @DefaultLog output


SSIS Blocking itself with IsolationLevel read Uncommited and TransactionOption supported My Project components have IsolationLevel = ReadUncommitted and TransactionOption = supported.

My query is a select on table Y verifying if not exists in table X and the result is written to table X.

SSIS opens two ids in SQL Server, a select and a bulk. The bulk is being blocked by the select.

I tried to use NOLOCK andSET ISOLATION LEVEL READ UNCOMMITTED but they do not work.

I was able to resolve this checking the "Check Constraint" on "OLEDB Destination" and I don't know why.

to check how many transactions you have open

select @@opentran

if all you want to do is not to be affected by blocking - and you are aware of the possible side effects - dirty reads -

you can either
before the select add this line:

set transaction isolation level read uncommitted

or within your select
you can use the hint WITH (NOLOCK), example:

select a,b,c, from mytable with (nolock)

instead of sp_who
please run the following code, and post the results here:

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT es.session_id AS session_id
,COALESCE(es.original_login_name, 'No Info') AS login_name
,COALESCE(es.host_name,'No Info') AS hostname
,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch
,es.status
,COALESCE(er.blocking_session_id,0) AS blocked_by
,COALESCE(er.wait_type,'MISCELLANEOUS') AS waittype
,COALESCE(er.wait_time,0) AS waittime
,COALESCE(er.last_wait_type,'MISCELLANEOUS') AS lastwaittype
,COALESCE(er.wait_resource,'') AS waitresource
,coalesce(db_name(er.database_id),'No Info') as dbid
,COALESCE(er.command,'AWAITING COMMAND') AS cmd
,sql_text=st.text
,transaction_isolation =
    CASE es.transaction_isolation_level
        WHEN 0 THEN 'Unspecified'
        WHEN 1 THEN 'Read Uncommitted'
        WHEN 2 THEN 'Read Committed'
        WHEN 3 THEN 'Repeatable'
        WHEN 4 THEN 'Serializable'
        WHEN 5 THEN 'Snapshot'
    END
,COALESCE(es.cpu_time,0)
    + COALESCE(er.cpu_time,0) AS cpu
,COALESCE(es.reads,0)
    + COALESCE(es.writes,0)
    + COALESCE(er.reads,0)
    + COALESCE(er.writes,0) AS physical_io
,COALESCE(er.open_transaction_count,-1) AS open_tran
,COALESCE(es.program_name,'') AS program_name
,es.login_time
FROM sys.dm_exec_sessions es
    LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id
    LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id
    LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid
    LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id
    LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address
    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st

where es.is_user_process = 1
  and es.session_id <> @@spid

UNION

SELECT es.session_id AS session_id
,COALESCE(es.original_login_name, 'No Info') AS login_name
,COALESCE(es.host_name,'No Info') AS hostname
,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch
,es.status
,COALESCE(er.blocking_session_id,0) AS blocked_by
,COALESCE(er.wait_type,'MISCELLANEOUS') AS waittype
,COALESCE(er.wait_time,0) AS waittime
,COALESCE(er.last_wait_type,'MISCELLANEOUS') AS lastwaittype
,COALESCE(er.wait_resource,'') AS waitresource
,coalesce(db_name(er.database_id),'No Info') as dbid
,COALESCE(er.command,'AWAITING COMMAND') AS cmd
,sql_text=st.text
,transaction_isolation =
    CASE es.transaction_isolation_level
        WHEN 0 THEN 'Unspecified'
        WHEN 1 THEN 'Read Uncommitted'
        WHEN 2 THEN 'Read Committed'
        WHEN 3 THEN 'Repeatable'
        WHEN 4 THEN 'Serializable'
        WHEN 5 THEN 'Snapshot'
    END
,COALESCE(es.cpu_time,0)
    + COALESCE(er.cpu_time,0) AS cpu
,COALESCE(es.reads,0)
    + COALESCE(es.writes,0)
    + COALESCE(er.reads,0)
    + COALESCE(er.writes,0) AS physical_io
,COALESCE(er.open_transaction_count,-1) AS open_tran
,COALESCE(es.program_name,'') AS program_name
,es.login_time
FROM sys.dm_exec_sessions es
    INNER JOIN sys.dm_exec_requests ec2 ON es.session_id = ec2.blocking_session_id
    LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id
    LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id
    LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid
    LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id
    LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address
    CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) AS st
where es.is_user_process = 1
  and es.session_id <> @@spid

this will show you what is actually causing the blocking.
this code comes from here:

how to find the T-SQL of a sleeping session that is blocking some other process?


How to optimize: having COUNT(*)>1 Without considering the indexes,
Is there a way to re-write this query and get the same result quicker?

select fo.OrderNo, fo.OrderItemSeqNo, COUNT(*)
from Facts.FactBackOrder fo
group by fo.OrderNo, fo.OrderItemSeqNo
having COUNT(*)>1

When I re-write it as the the followig, it even gets worse.

;WITH R1 AS (

select ROW_NUMBER() OVER (PARTITION BY fo.OrderNo, fo.OrderItemSeqNo ORDER BY fo.OrderNo, fo.OrderItemSeqNo) AS N
       ,fo.OrderNo
       , fo.OrderItemSeqNo
from Facts.FactBackOrder fo
)

SELECT * FROM R1 WHERE N = 2

OR THIS:

;WITH R1 AS (

select COUNT_BIG(*) OVER (PARTITION BY fo.OrderNo, fo.OrderItemSeqNo) AS N
       ,fo.OrderNo
       , fo.OrderItemSeqNo
from Facts.FactBackOrder fo
)

SELECT * FROM R1 WHERE N > 1

It is a fact table in a datawarehouse.
the indexes are on the picture below.

An Indexed view in this situation would use much less space than an index.
I have this situation in a similar indexed view -- you can see the code below:

CREATE view [dbo].[VProcessStatus]
WITH SCHEMABINDING
AS
select processstatus,
      StatusCount = COUNT_BIG(*)
      FROM DBO.tbltaxtransaction
      GROUP BY processstatus
GO

GRANT SELECT ON [dbo].[VProcessStatus] TO [mydomain\itdevelopment] AS [dbo]
GO

CREATE UNIQUE CLUSTERED INDEX [IDX_VProcessStatus] ON [dbo].[VProcessStatus]
(
    [processstatus] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [pscheme_ProcessStatus]([processstatus])
GO

when I look for the space used by this view and the underlying table:

sp_spaceused 'tbltaxtransaction'
go
sp_spaceused 'VProcessStatus'
go

I get the following result:

The only extra is that this indexed view is partitioned according to the PARTITION schema and PARTITION functions below:

CREATE PARTITION SCHEME [pscheme_ProcessStatus] AS PARTITION [pfunc_ProcessStatus] TO ([FG01], [FG02], [FG03], [FG04], [FG05])
GO

CREATE PARTITION FUNCTION [pfunc_ProcessStatus](int) AS RANGE LEFT FOR VALUES (1, 2, 3, 9)
GO

I have now moved from dev to production.
the table FactBackOrder has 4,183,289 rows.

We're in sql server 2012.
Microsoft SQL Server 2012 (SP1) - 11.0.3412.0 (X64)
    Mar  2 2014 01:25:09
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.3  (Build 9600: )

But I don't want to use Columnstore indexes - these tables are updated very often.
Maybe in sql 2014?

the original query plan of the query I wanted to improve the performance of is:

the only way I found out to improve the orginal query was to add an index to the table.

create index idx_FactBackOrder_OrderNo_OrderItemSeqNo
on Facts.FactBackOrder (OrderNo,OrderItemSeqNo) with (online=on)

When I then run the query, I notice it takes longer than the original without index.
the new query plan, after creating the index is as follows:

I immediately drop the newly created index:

drop index idx_FactBackOrder_OrderNo_OrderItemSeqNo on Facts.FactBackOrder

I then create an indexed view:

--drop view [dbo].[VfactBackOder]

CREATE view [dbo].[VfactBackOder]
WITH SCHEMABINDING
AS
select fo.OrderNo,
       fo.OrderItemSeqNo,
       COUNT_BIG(*) as the_number
from Facts.FactBackOrder fo
group by fo.OrderNo, fo.OrderItemSeqNo
GO

create the clustered index for the indexed view:

CREATE UNIQUE CLUSTERED INDEX [IDX_VfactBackOder] ON [dbo].[VfactBackOder]
(
    OrderNo, OrderItemSeqNo
)
go

The when I run the query using the newly created indexed view instead of the original query referencing the underlying tables:

select fo.OrderNo,
       fo.OrderItemSeqNo,
       fo.the_number
from VfactBackOder fo
where fo.the_number > 1

I get a non satisfactory result.
then I add another index to the view:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[VfactBackOder] ([the_number])
INCLUDE ([OrderNo],[OrderItemSeqNo])
go

Now the query flies like a rocket.
the new query plan is:

And looking at the space used by the indexed view and the underlying table:

sp_spaceused 'Facts.FactBackOrder'
go
sp_spaceused 'VfactBackOder'
go

query is performing well enough, customers happy here.
thanks for all the comments.


Unable to Restart the SQL server Agent from SSCM In my machine SQL Server Agent is not expandable with a label (Agent XPs disabled). In this case I run this code to enable it.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO

Then Restart the Agent on SQL server Configuration Manager, it throws this error:

  The Request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details

When I checked the Windows event log, I saw this error:

  Failed to initialize SQL Agent log, The system cannot find the path specified.

What is the resolution?

Since we identified it was a permissions issue via the comments, I would suggest you adjust the account running the SQL Agent service to something different if this is a server.  Microsoft does a great job to outline the permissions needed for the SQL Agent service account.  That article also highlights a few pitfalls regarding some minimal permission grants you want to do so users outside of the sysadmin role can properly utilize Proxy accounts, etc.

If it's your local PC, it likely doesn't matter that you adjust this account nearly as much since that service isn't configured for automatic startup by default.

Glad you got it sorted.


SSIS - executing package from SQL Server by stored procedure How to execute SSIS package from stored procedure? At this moment I am able to do this by using Windows authentication. However, I must do it using SQL Server authentication, but SQL Server requires Windows authenticated user.

If you are using SSISDB (which I think you are?), you can Execute a set of standard stored procedures that are in the SSISDB. These are outlined in the MSDN Article here.

This article also states the permissions that are required to run these procedures. It sounds like you need to set up a SQL Server login which has these permissions. How to describe this is located here. You will need to do this with your existing Windows Authenticated user if it has permission to allocate permissions (ALTER ANY LOGIN or ALTER LOGIN permissions).

You may also need to create a user who is mapped to this login as described here.

Your question is not very specific so without further information about a particular problem you are having, it is difficult to give a clear answer.


Managed backup to Azure does not delete backup after retention period I did set up SQL Server managed backup for our databases. I did define retention days to 4, but i just checked the azure storage account where the managed backups are and found that there is still all the files from the day i did set up managed backup (2015/12/1).

SQL i used on every user database:

    Use msdb;
    GO
    EXEC smart_admin.sp_set_db_backup
            @database_name='TestDB'
            ,@retention_days=4
            ,@credential_name='MyCredential'
            ,@encryption_algorithm ='AES_128'
            ,@encryptor_type= 'Certificate'
            ,@encryptor_name='MyBackupCert'
            ,@enable_backup=1;

We are using SQL Server 2014 Enterprise with Azure VM.

Set up tutorials and information about the managed backups: MSDN

Is SQL server managed backup to Azure supposed to automatically delete backups after given retention days have passed or do i need to implement custom job for that?

I ended up making a Sql server agent job that runs this with powershell mode:

# Ps1-script to keep azure storageaccount for managed backups clean

Import-Module Azure

$storageAccount = "STORAGEACCOUNTNAME"
$storageKey = "STORAGEKEY"
$blobContainer = "CONTAINERNAME"

$CleanupTime = [DateTime]::UtcNow.AddDays(-4)
$context = New-AzureStorageContext -StorageAccountName $storageAccount -StorageAccountKey $storageKey
Get-AzureStorageBlob -Container $blobContainer -Context $context |
Where-Object { $_.LastModified.UtcDateTime -lt $CleanupTime -and $_.BlobType -eq "PageBlob" -and $_.Name -like "*.bak"} |
Remove-AzureStorageBlob
$CleanupTime = [DateTime]::UtcNow.AddDays(-4)
Get-AzureStorageBlob -Container $blobContainer -Context $context |
Where-Object { $_.LastModified.UtcDateTime -lt $CleanupTime -and $_.BlobType -eq "PageBlob" -and $_.Name -like "*.log"} |
Remove-AzureStorageBlob

This does delete all .bak and .log files older than 4 days. It does not however work on blobs that does have active lease on them.


Help with SQL Query to XML output I have the following query:

SELECT
    [ID] AS [NewID]
    , (
       CASE WHEN LEN([MiddleName]) > 0 THEN
          [LastName] + ', ' + [FirstName] + ' ' + LEFT([MiddleName],1) + '.'
       ELSE
          [LastName] + ', ' + [FirstName]
       END
    ) AS [Name]
    , [Title] AS [ProfTitle]
    , CONVERT(CHAR(10),[DepartureDate],111) AS [BorDepart]
    , [LocationOfficeNumber] AS [OfficePhone]
    , [Location]
    , [LocationSection] AS [Sec]
    , [PracticeGroup] AS [PracticeTL]
    , [Email]
FROM [dbo].[Borrowers]

With that, I'd like to find out how I can write a FOR XML query that translate to this output:

<sydney>
    <import>
        <template id="Borrower" name="Borrowers">
            <record searchfield="NewID" searchvalue="NewID">
                <field id="NewID">
                    <![CDATA[NEWID]]>
                </field>
                <field id="Name">
                    <![CDATA[NAME]]>
                </field>
                <field id="ProfTitle">
                    <![CDATA[Professional Title]]>
                </field>
                <field id="BorDepart">
                    <![CDATA[YYYY/MM/DD]]>
                </field>
                <field id="OfficePhone">
                    <![CDATA[office phone]]>
                </field>
                <link id="Location" linkfield="Code">
                    <![CDATA[011]]>
                </link>
                <link id="Sec" linkfield="Term">
                    <![CDATA[SECTION]]>
                </link>
                <link id="PracticeTL" linkfield="Term">
                    <![CDATA[SUBSECTION]]>
                </link>
                <field id="Email">
                    <![CDATA[email]]>
                </field>
            </record>
        </template>
    </import>
</sydney>

I've tried all kinds of combinations with FOR XML PATH and FOR XML EXPLICIT and cannot find any answer to it. The closest I came is with this:

SELECT (
    SELECT
       'Borrower' AS '@id'
       , 'Borrowers' AS '@name'
       , (
          SELECT
             'NewID' AS '@searchfield'
             , 'NewID' AS '@searchvalue'
             , [ID] AS 'NewID' --AS [Field!1!CDATA]

             , (
                CASE WHEN LEN([MiddleName]) > 0 THEN
                    [LastName] + ', ' + [FirstName] + ' ' + LEFT([MiddleName],1) + '.'
                ELSE
                    [LastName] + ', ' + [FirstName]
                END
             ) AS [Name]
             , [Title] AS [ProfTitle]
             , CONVERT(CHAR(10),[DepartureDate],111) AS [BorDepart]
             , [LocationOfficeNumber] AS [OfficePhone]
             , [Location]
             , [LocationSection] AS [Sec]
             , [PracticeGroup] AS [PracticeTL]
             , [Email]
          FROM [dbo].[Borrowers]
          FOR XML PATH('record'), TYPE
       )
    FOR XML PATH('template'), TYPE
) AS 'import'
FOR XML PATH('sydney'), TYPE

Please any help is welcome since I've been stuck with this for a week and cannot find anything.

P.S.: Here's the schema for the Borrowers table:

Cheers,

Alvaro Costa

Here is a working FOR XML EXPLICIT example.  They are a bit harder to code, but I tend to build them up, section by section so they're not so bad:

USE tempdb
GO

SET NOCOUNT ON
GO

IF OBJECT_ID('dbo.Borrowers') IS NOT NULL DROP TABLE dbo.Borrowers
CREATE TABLE dbo.Borrowers
(
    ID                      INT IDENTITY PRIMARY KEY,
    FirstName               VARCHAR(30),
    MiddleName              VARCHAR(30),
    LastName                VARCHAR(30),
    Title                   VARCHAR(30),
    DepartureDate           DATE,
    LocationOfficeNumber    VARCHAR(30),
    Location                VARCHAR(30),
    LocationSection         VARCHAR(30),
    PracticeGroup           VARCHAR(30),
    Email                   VARCHAR(30)
)
GO

INSERT INTO dbo.Borrowers( FirstName, MiddleName, LastName, Title, DepartureDate, LocationOfficeNumber, Location, LocationSection, PracticeGroup, Email )
VALUES
    ( 'Alvaro', 'X', 'Costa', 'Mr', GETDATE(), '000-000', 'Location 1', 'Location Section 2', 'Practice Group 3', 'alvaro@nospam.com' ),
    ( 'w', '', 'Bob', 'Mr', GETDATE(), '000-007', 'Location 2', 'Location Section 3', 'Practice Group 4', 'wBob@nospam.com' )
GO

SELECT
      1             AS Tag
    , NULL          AS Parent
    , NULL          AS [import!1]
    , NULL          AS [template!2!id]
    , NULL          AS [template!2!name]
    , NULL          AS [record!3!searchfield]
    , NULL          AS [record!3!searchvalue]
    , NULL          AS [record!3!sort!HIDE]

    , NULL          AS [field!4!id]         -- NewID
    , NULL          AS [field!4!!CDATA]

    , NULL          AS [field!4!id]         -- Name
    , NULL          AS [field!4!!CDATA]

    , NULL          AS [field!4!id]         -- ProfTitle
    , NULL          AS [field!4!!CDATA]

    , NULL          AS [field!4!id]         -- BorDepart
    , NULL          AS [field!4!!CDATA]

    , NULL          AS [field!4!id]         -- OfficePhone
    , NULL          AS [field!4!!CDATA]

    , NULL          AS [link!5!id]          -- Location
    , NULL          AS [link!5!linkfield]
    , NULL          AS [link!5!!CDATA]

    , NULL          AS [link!5!id]          -- Sec
    , NULL          AS [link!5!linkfield]
    , NULL          AS [link!5!!CDATA]

    , NULL          AS [link!5!id]          -- PracticeTL
    , NULL          AS [link!5!linkfield]
    , NULL          AS [link!5!!CDATA]

    , NULL          AS [field!4!id]         -- Email
    , NULL          AS [field!4!!CDATA]

UNION ALL

SELECT
      2
    , 1
    , NULL
    , 'Borrower'
    , 'Borrowers'
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL

UNION ALL

SELECT
      3
    , 2
    , NULL
    , NULL
    , NULL
    , 'NewID'
    , 'NewID'
    , ID
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL

FROM dbo.Borrowers

UNION ALL

SELECT
      4
    , 3
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , ID
    , 'NewID'
    , ID
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL

FROM dbo.Borrowers

UNION ALL

SELECT
      4
    , 3
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , ID
    , NULL
    , NULL
    , 'Name'
    , LastName
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL

FROM dbo.Borrowers

UNION ALL

SELECT
      4
    , 3
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , ID
    , NULL
    , NULL
    , NULL
    , NULL
    , 'ProfTitle'
    , Title
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL

FROM dbo.Borrowers

UNION ALL

SELECT
      4
    , 3
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , ID
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , 'BorDepart'
    , CONVERT( CHAR(10), DepartureDate, 111 )
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL

FROM dbo.Borrowers

UNION ALL

SELECT
      4
    , 3
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , ID
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , 'OfficePhone'
    , LocationOfficeNumber
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL

FROM dbo.Borrowers

UNION ALL

SELECT
      5
    , 3
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , ID
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , 'Location'
    , 'Code'
    , Location
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL

FROM dbo.Borrowers

UNION ALL

SELECT
      5
    , 3
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , ID
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , 'Sec'
    , 'Term'
    , LocationSection
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL

FROM dbo.Borrowers

UNION ALL

SELECT
      5
    , 3
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , ID
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , 'PracticeTL'
    , 'Term'
    , PracticeGroup
    , NULL
    , NULL

FROM dbo.Borrowers

UNION ALL

SELECT
      4
    , 3
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , ID
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , 'Email'
    , Email

FROM dbo.Borrowers
ORDER BY [record!3!sort!HIDE]
FOR XML EXPLICIT, ROOT('sydney')

HTH


ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT Most of the forum and example online always suggest to have both ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT set to ON whenever someone is asking snapshot, row versioning or similar question.

I guess the word SNAPSHOT in both setting get a little confusing. I thought that, in order for database engine to use row versioning instead of locks for READ_COMMITTED default behavior, the database READ_COMMITTED_SNAPSHOT is set to ON regardless of what ALLOW_SNAPSHOT_ISOLATION setting.

The ALLOW_SNAPSHOT_ISOLATION setting is set to ON only to allow snapshot isolation when starting a transaction (e.g. SET TRANSACTION ISOLATION LEVEL SNAPSHOT) regardless of READ_COMMITTED_SNAPSHOT setting.

The only reason to have these two settings set to ON is when it needs to have READ COMMITTED row versioning AND  snapshot isolation.

My question is, is my understanding incorrect in some way? And that these two setting have to be always set to ON together (especially for READ COMMITTED row versioning)?

Your understanding is correct.  It does get a little confusing.

Kim Tripp (one of the programmers of SQL Server and a integral part of SQLSkills) goes through exactly what you stated in the MCM videos on Snapshot Isolation.  Fast fwd to 41:45 in the video to get to the part where she answers your question.

If you use ALLOW_SNAPSHOT_ISOLATION make sure you use SET TRANSACTION ISOLATION LEVEL SNAPSHOT in your code, otherwise you will not get any of the benefits.

If you set SET READ_COMMITTED_SNAPSHOT ON, then there is no need to modify any code.  MS SQL Server automatically applies snapshot isolation for that table.

I haven't tested to see what happens if you ask for a different isolation level in your code, I suspect it will overwrite this option but test it first.

A quick look at performance overhead using Snapshot Isolation.

Good article on how snapshot isolation can change the expected behavior of your app.  It shows examples of how a update statement and a select statement might return totally different and unexpected results.


How can a fill factor of 0 or 100 be the same? As of my understanding, Fill factor of 80 implies that 20 percentage of each leaf-level page will be empty to enable future growth.
I can't correlate how fill factor of 0 and 100 can be the same!
Am I missing something?

It is legacy from SQL Server 2000

0 and 100 were different back then

100 meant "fill all pages including all b-tree index levels"
0 meant "leave some space at higher levels in the b-tree index"

Since SQL Server 2005, both mean "fill all pages including all b-tree index levels"

Quotes from BOL (My bold)

SQL Server 2000:

  A fill factor value of 0 does not mean that pages are 0 percent full. It is treated similarly to a fill factor value of 100 in that SQL Server creates clustered indexes with full data pages and nonclustered indexes with full leaf pages. It is different from 100 in that SQL Server leaves some space within the upper level of the index tree.

SQL Server 2005

  A fill factor of 0 or 100 creates clustered indexes with full data pages and nonclustered indexes with full leaf pages, but it leaves some space within the upper level of the index tree. Fill factor values 0 and 100 are identical in all respects.

What I can't find is something to show this explicitly in "behaviour changes"


ON DELETE CASCADE not working Trying to implement a simple user follower system in MySQL, using a junction table:

CREATE TABLE users (
    id int NOT NULL AUTO_INCREMENT,
    email varchar(255) NOT NULL,
    username varchar(25) NOT NULL,
    password varchar(25) NOT NULL,
    apikey varchar(45) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE(email),
    UNIQUE(username),
    UNIQUE(apikey)
);

CREATE TABLE followers (
    id_follower INT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    id_following INT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    PRIMARY KEY (id_follower, id_following)
);

I insert records for followers like this(using Python):

db.cursor.execute(
'''INSERT into FOLLOWERS(id_follower, id_following)
values ((SELECT ID FROM USERS WHERE apikey = %s), %s)''',
(request.apikey, int(request.userid)))

I want it so that if a follower account is deleted, all follows for that account will be deleted, and if an account being followed is deleted, all follows for that account will be deleted as well. The ON DELETE CASCADE doesn't seem to be working, if I delete accounts, all follower records remain in the followers table. Am I not using it correctly?

You declared the FK constraints "inline" and you fell into a mysql trap. You can check with SHOW CREATE TABLE followers that there is no FK now! This known behaviour is mentioned in the mysql documentation:

  MySQL does not recognize or support “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. MySQL accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification.

It should really say that the syntax is recognized as valid SQL but is ignored and the foreign key constraints are not created.

To solve the issue, the CREATE TABLE statement should have been:

CREATE TABLE followers (
    id_follower INT NOT NULL,
    id_following INT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    PRIMARY KEY (id_follower, id_following),
    CONSTRAINT follower_fk
        FOREIGN KEY (id_follower)
        REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT following_fk
        FOREIGN KEY (id_following)
        REFERENCES users (id) ON DELETE CASCADE
);

If you already have data in the table, you can add the constraints with an ALTER TABLE statement:

ALTER TABLE followers (
    ADD CONSTRAINT follower_fk
        FOREIGN KEY (id_follower)
        REFERENCES users (id) ON DELETE CASCADE,
    ADD CONSTRAINT following_fk
        FOREIGN KEY (id_following)
        REFERENCES users (id) ON DELETE CASCADE
);


how to identify the usage of an specific sql login I'm examining a Database Server which have several sql logins.there is a specific sql login with sysadmin server Role which i can't find where exactly is its usage. On sp_who2 result this login is used with BACKGROUND status which is running commands Like LOG WRITER ,RECOVERY WRITER,LAZY WRITER,XE TIMER,TRACE QUEUE TASK,TRACE QUEUE TASK &c.

Now I want to know where is the usage of this SQL Login, because I want to change its password.

As all the sessions for that user are system processes per your comment, this is the system itself, i.e. the "sa" account and/or the SQL Server Service account using Trusted Authentication.


Mongodb error after db repair command I was working with MongoDB using Pymongo Libraries. Suddenly MongoDB stopped. I restarted it. After a while it stopped again. So I searched and tried the following repair command:

mongod --dbpath f:/data/db --repair

The process kept running. But I stopped it using the keyboard interruption.
Now When I am trying to use start the server using my configuration, the window is getting flashed on my screen.
I do not understand what might be the problem as I could see anything in my log file.

Here are the trace log:https://gist.github.com/JafferWilson/0c6538a0124112f253c7f8e70bdde15e
Kindly, let me know what I can do so that I can start and get my data again. I have GBs of data on MongoDB, and month of hardwork will be wasted if not getting started. Please let me know the solution, if any.

My mongoDB is on the Windows 8 server.

OK.. You have managed to get your self in middle of mess. As it says:

  UnsupportedFormat: Unable to find metadata for
  table:index-3--243586308771972589 Index: {name: id, ns:
  admin.system.version} - version too new for this mongod. See
  http://dochub.mongodb.org/core/3.4-index-downgrade for detailed
  instructions on how to handle this error

Index meta information "says" that it's version is beyond >3.4, what of course it cannot be. Problem index is index of _id at admin.system.version -collection. We can "loose" this collection without problems because we know exactly what we have there. It would be easy, if you had enabled 'directoryPerDB' and especially 'directoryPerIndexes'.. BUT there is tool to fix things in wiredTiger, what I have never used but here is story how to do it. Find out files of admin.system.version -collection, rename those files and try to start mongod again.


Is Oracle 10g smart enough to work on a WHERE clause identically as the equivalent INNER JOIN? Way back when Michael Jackson was still alive, I made this question:

https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause

And now I'm performance-auditing a system in which the developer used the first form, instead of an INNER JOIN. The DBMS is Oracle 10.2.0.4 on Solaris/SPARC. I could, of course, recommend the change in syntax solely because it's better form, but if it won't positively impact performance it's kind of pointless.

So, my question is: is it possible there will be a change in query execution if I ask them to change the query syntax to INNER JOIN?

Is it possible?  Sure.  Is there likely to be an improvement in performance?  No.  If there is a change in performance (barring cases where you discover that a join is missing or otherwise fix a query), it'm more likely that the old implicit join syntax will be more efficient.  But that's pretty unlikely.

Behind the scenes, when you have a query using the SQL 99 inner join\'left outer join` syntax, Oracle actually transforms that to use the old style syntax before optimizing it.  When Oracle first introduced the SQL 99 syntax, there were occasionally bugs in this transformation process.  By the time you get to 10.2.0.4, the odds that you'd encounter one of those bugs is pretty low.  But if you look at enough queries, you might find one that is more efficient using the old syntax.


Can't force mysql to use index There are many similar questions about force index but mine is something different because i successfully use index at local dev server but can't on production server.

I have a table with more than 20 millions records MyISAM.

CREATE TABLE `cs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `upload_id` int(10) unsigned NOT NULL,
  `amount` decimal(9,2) DEFAULT NULL,
  `user_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `upload_id` (`upload_id`)
  KEY `for_where` (`user_id`,`amount`,`upload_id`),
) ENGINE=MyISAM AUTO_INCREMENT=20547623;

i need to run following query:

SELECT COUNT(DISTINCT `t`.`id`)
  FROM `cs` `t`
  LEFT OUTER JOIN `uploads` `Upload` ON (`t`.`upload_id`=`Upload`.`id`)
WHERE (((t.user_id=1) AND (upload_id IN (1,2,3)))
  AND (((Upload.term IS NOT NULL) AND (Upload.term<>"")) AND (t.amount>0)))

query took more than 20 seconds and EXPLAIN says that it "using where"

+----+-------------+--------+-------+---------------------------+-----------+---------+--------------------+--------+-------------+
| id | select_type | table  | type  | possible_keys             | key       | key_len | ref                | rows   | Extra       |
+----+-------------+--------+-------+---------------------------+-----------+---------+--------------------+--------+-------------+
|  1 | SIMPLE      | Upload | range | PRIMARY                   | PRIMARY   | 4       | NULL               |      3 | Using where |
|  1 | SIMPLE      | t      | ref   | user_id,for_where,upload_id | upload_id | 4       | mydb.Upload.id | 381427 | Using where |
+----+-------------+--------+-------+---------------------------+-----------+---------+--------------------+--------+-------------+

but if i'll drop key upload_id or just add IGNORE INDEX (upload_id) it will use 'for_where' key and 'Using index condition' and query take only 1,5 sec.

+----+-------------+--------+--------+-----------------+----------+---------+----------------------+--------+-----------------------+
| id | select_type | table  | type   | possible_keys   | key      | key_len | ref                  | rows   | Extra                 |
+----+-------------+--------+--------+-----------------+----------+---------+----------------------+--------+-----------------------+
|  1 | SIMPLE      | t      | range  | user_id,for_where | for_where | 11      | NULL                 | 693667 | Using index condition |
|  1 | SIMPLE      | Upload | eq_ref | PRIMARY         | PRIMARY  | 4       | adrevyii.t.upload_id |      1 | Using where           |
+----+-------------+--------+--------+-----------------+----------+---------+----------------------+--------+-----------------------+

it's good result for me so i try to roll out this on production server but no luck. It always say 'Using where' and still run more than 20 sec.

I was check for differences but can't find one. Both, local and production table use MyISAM, similar indexes and same data with one exception — production table have 2 millions records more.

My local server version: 5.6.13 Mac OS
My production server version: 5.1.69-0ubuntu0.11.10.1 (Ubuntu)

Why this happens? How i can force production server to use index? May be some server settings can affect it? Is it because version? Can it be fixed?
Is it possible to speed this query up more?

Your query can be simplified to:

SELECT COUNT(DISTINCT t.id)
FROM cs AS t
  JOIN uploads AS u
    ON t.upload_id = u.id
WHERE t.user_id = 1
  AND t.upload_id IN (1,2,3)
  AND u.term > ''
  AND t.amount > 0 ;

and also:

SELECT COUNT(*)
FROM cs AS t
WHERE t.user_id = 1
  AND t.upload_id IN (1,2,3)
  AND t.amount > 0
  AND EXISTS
      ( SELECT *
        FROM uploads AS u
        WHERE t.upload_id = u.id
          AND u.term > ''
      ) ;


How to determine if pg_dumpall is still running Environment:

Postgres 8.4

Debian Squeeze LTS

Recently we switched from a backup solution around hot_backups to using pg_dumpall.

This solution used a pid file as long as the hot_backup was running, once finished, it would remove the pidfile.

For (re)storing the Dump on another server we would use a script to check for the pid, and transfer the Dumpfile if no PIDfile exists.

As the hot_backup was quite easy to spot in case something went wrong (f.e. more than 24h dumptime) Issues which arised were promptly figured out and fixed.

As we now use pg_dumpall i am in the research of how to better determine if the dump is finished or still ongoing.

Currently we use the same PID approach as before.

However i would like to have a query/command/something to check if the dump is still running or not from within postgres.

You can see if a dump is running from within PostgreSQL by checking for pg_dump or pg_dumpall as the application_name in pg_stat_activity. It's not perfect though, since the client has to connect to each DB in turn, so there might be a period with no visible connection. I haven't verified whether pg_dumpall maintains its connection throughout, while running the sub pg_dump sessions, or not.

You can use the handy flock command to maintain pid files at the shell level.

Personally, though, I strongly recommend simply rotating your dumps. Dump to a temporary file, and only when the dump is complete replace the old dump with the new one using the mv command. That way there is no window during which the dump file is invalid. If you replace the dump file while it's being read, anything reading the file will continue to read the old dump file it has open and won't care that it's been replaced.


Postgres 9.6.6 on Ubuntu 16.04, lots of errors We're running perfsonar on a group of our servers, and while doing some maintenance work found that we

get giant logfiles under /var/log/postgresql
table bloat on the database of the perfsonar scheduler

I assume the toolkit is using table locking when it does ANYTHING on the database, and always cancels the autovacuum run:

2019-04-07 06:33:43 NZST [471-1] ERROR:  canceling autovacuum task
2019-04-07 06:33:43 NZST [471-2] CONTEXT:  automatic vacuum of table "pscheduler.public.run"
2019-04-07 06:34:33 NZST [981-1] ERROR:  canceling autovacuum task
2019-04-07 06:34:33 NZST [981-2] CONTEXT:  automatic vacuum of table "pscheduler.public.run"

A manual run of vacuum analyze roughly halved the use of disk space under /var/lib/postgresql/9.6/main/base/  ...

I've found several web pages talking about autovacuum tuning, but I'm wondering if it wouldn't make sense to disable autovacuum and run a vacuum analyze from a cron job.

Is there a best practice/recommendation for this kind of situation?

And maybe ask the perfsonar project to clean up their code in the long run ;}

Having talked about this on freenode (thanks Andrew!) I modified two of the default settings in postgresql.conf, and that made the issues go away without causing other ill effects.

autovacuum_vacuum_cost_delay = 0ms
deadlock_timeout = 5s

I think the consensus is still that pscheduler's attempts to get table locks so frequently is poor design, but the workaround above seems to be doing the trick for the time being.


Storing application settings with different datatypes I'm developing a web application and I plan to store application settings in the database in name,value fields (one row = one setting).

These settings range from directory paths to simple yes/no flags. Obviously, I can't store a path (string) into a boolean field.

Is my only option to make the value field a string and then interpret the value as necessary per setting, or is there some other strategy for this?

(I'm using PostgreSQL, but the answer should probably apply to any database server.)

The simplest way I can think of which keeps the types is the following: create as many columns in your application_settings table as many different types you have.  This means normally a boolean, a numeric, a date and a text field.  So your table would look like

CREATE TABLE application_settings (
      id serial PRIMARY KEY
    , setting_name text NOT NULL -- possibly UNIQUE
    , boolean_setting boolean
    , numeric_setting numeric -- or this can be an integer, too
    , date_setting date -- again, this can be timestamp, too
    , text_setting text
);

If you plan to keep only one row of each setting (no historical data, then set the setting_name field to UNIQUE.  And if you keep exactly one value in a row (which is what I'd expect), then you can add a check constraint like

, CHECK (
    (boolean_setting IS NOT NULL)::integer +
    (numeric_setting IS NOT NULL)::integer +
    (date_setting IS NOT NULL)::integer +
    (text_setting IS NOT NULL)::integer
    = 1
  )

Depending on how you pass the values into this table, you may or may not need some sort of validation as well.  This may involve checking setting names against the type of values provided and a list or a range of valid inputs.


Change mariadb's log directory MariaDB was installed using yum on Centos7 using repository http://yum.mariadb.org/10.3/centos7-amd64.  MariaDB's log files are sent to directory /var/lib/mysql.  How can I change the location to another location such as /var/log/mysql/?

[michael@developserver mysql]$ mysql -u root -p
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 10.3.14-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SET GLOBAL general_log_file='/var/log/mysql/developserver.log';
ERROR 1231 (42000): Variable 'general_log_file' can't be set to the value of '/var/log/mysql/developserver.log'
MariaDB [(none)]> show global variables like '%general_log_file%';
+------------------+-------------------+
| Variable_name    | Value             |
+------------------+-------------------+
| general_log_file | developserver.log |
+------------------+-------------------+
1 rows in set (0.002 sec)

MariaDB [(none)]>

There are a few reasons this can happen.

1) The directory does not exist:

mysql> SET GLOBAL general_log_file='/this/directory/does/not/exist/developserver.log';
ERROR 1231 (42000): Variable 'general_log_file' can't be set to the value of '/this/directory/does/not/exist/developserver.log'
mysql>

2) The directory exists, but you do not have permission to write to it & create the new file:

root@ubuntu-xenial:~# chmod 000 /var/log/newmysqllogdirectory
root@ubuntu-xenial:~# mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.26-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SET GLOBAL general_log_file='/var/log/newmysqllogdirectory/foo.log';
ERROR 1231 (42000): Variable 'general_log_file' can't be set to the value of '/var/log/newmysqllogdirectory/foo.log'
mysql>


MS Access database in a network I would like to ask if MS Access database supports working in networks & multi-user access?

Access most certainly supports a network and multiuser operation right out of the box. You don't have to change your code or really do anything special.

However to keep in mind while Access is tremendously easy to use on a network, and many people often utilize access with multiple users on a network, it has its limitations.

There are many issues to consider, but the basic concept is that your network has to be very solid, I can't say the network has to be above average, but Access as a product in a network environment is quite sensitive to poor connections.

So if you're just talking a couple computers on the Office Network and you share a folder on one of the computers or your server, then access can work quite well on this environment.  However if your network is a wide area or has users connecting over the Internet, then such setups tend to be quite problematic.

As a general rule, it's recommended that you split your application into two parts.  You place the program part on each computer, and this task is not require any extra programming skills on your part, but it does help enormously in terms of reliability.

I explain this concept of splitting and how you run access in a multi user environment in the following article of mine:

http://www.kallal.ca/Articles/split/index.htm

And a primer on the different kinds of networks, and some of the obstacles as you may encounter are outlined in the following article of mine:

http://www.kallal.ca//Wan/Wans.html


help in correcting the syntax error of the below create table with as with multiple select Please help to correct the syntax of the below. I am from a math background trying to use Oracle. The command helps me permute the columns and put them in one table.

create table newsample  AS
(
select(
(select h1.E1,h1.E2,h1.E3,h1.E4 from sample1 h1),
(select h1.E1,h1.E2,h1.E4,h1.E3 from sample1 h1),
(select h1.E1,h1.E3,h1.E2,h1.E4 from sample1 h1),
(select h1.E1,h1.E3,h1.E4,h1.E2 from sample1 h1),
(select h1.E1,h1.E4,h1.E2,h1.E3 from sample1 h1),
(select h1.E1,h1.E4,h1.E3,h1.E2 from sample1 h1),
(select h1.E2,h1.E1,h1.E3,h1.E4 from sample1 h1),
(select h1.E2,h1.E1,h1.E4,h1.E3 from sample1 h1),
(select h1.E2,h1.E3,h1.E1,h1.E4 from sample1 h1),
(select h1.E2,h1.E3,h1.E4,h1.E1 from sample1 h1),
(select h1.E2,h1.E4,h1.E1,h1.E3 from sample1 h1),
(select h1.E2,h1.E4,h1.E3,h1.E1 from sample1 h1),
(select h1.E3,h1.E1,h1.E2,h1.E4 from sample1 h1),
(select h1.E3,h1.E1,h1.E4,h1.E2 from sample1 h1),
(select h1.E3,h1.E2,h1.E1,h1.E4 from sample1 h1),
(select h1.E3,h1.E2,h1.E4,h1.E1 from sample1 h1),
(select h1.E3,h1.E4,h1.E1,h1.E2 from sample1 h1),
(select h1.E3,h1.E4,h1.E2,h1.E1 from sample1 h1),
(select h1.E4,h1.E1,h1.E2,h1.E3 from sample1 h1),
(select h1.E4,h1.E1,h1.E3,h1.E2 from sample h1),
(select h1.E4,h1.E2,h1.E1,h1.E3 from sample1 h1),
(select h1.E4,h1.E2,h1.E3,h1.E1 from sample1 h1),
(select h1.E4,h1.E3,h1.E1,h1.E2 from sample1 h1),
(select h1.E4,h1.E3,h1.E2,h1.E1 from sample1 h1)))

  Error at Command Line:4 Column:49
  Error report:
  SQL Error: ORA-00907: missing right parenthesis
  00907. 00000 -  "missing right parenthesis"
  *Cause:
  *Action:

CREATE TABLE "SYSTEM"."SAMPLE1"
       (    "E1" VARCHAR2(20 BYTE),
      "E2" VARCHAR2(20 BYTE),
      "E3" VARCHAR2(20 BYTE),
      "E4" VARCHAR2(20 BYTE)
       ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
     NOCOMPRESS LOGGING
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "SYSTEM" ;

You can get the result by using UNION ALL .
Take help of following example for understanding:

create table empnew as select * from employees union all select * from employees;

As @a_horse_with_no_name perfectly mentioned !


Using MySQL InnoDB as an Archive My site has a main MySQL InnoDB table that it does most of its work on. New rows get inserted at a rate of 1 million per week, and rows older than a week gets moved over to an archive table on a daily basis. These archived rows are processed once a week for stuff like finding trends.

This archive table consequently grows at 1 million new rows every week, and querying it can get really slow. Is MySQL suited for archiving data, or is my strategy very flawed?

Please advise, thank you!

I would be very tempted to store in a no-SQL data store, like Mongo or Couch.  Writes are incredibly fast, scales well, etc.

You might even archive in a mongo collection, then store "processed" results in an RDBMS, which you can then query very quickly with SQL.

To stay in MySQL, you're looking at some sort of partitioning scheme to get this to scale at all.


Which is better: more rows or more columns in a student attendance table? I want to create an attendance table for student attendance. Which structure should I go for?

I have two options.

attendance(student_id,date,is_absent,reason)
attendance(student_id,d1,d2,d3,d4,...,d30,d31,is_absent,reason)

Scenario: assuming 100 students in which 50 are absent all 31 days for about 5 months. In case 1 with the assumed scenario the number of total rows is 50 absent * 31days * 5month  =7750
At the same time case 2 will create 100 rows irrespective of number of absentees and the respective data is filled if the student is absent. So in total in case 2 100 * 5 months = 500 rows and 500 * 31 cols = 15500 cells (scenario doesn't matter in rows and cols but the number of cells filled is 50 absent * 31 days * 5 month = 7750)

Which one should I go for and would it be fast in querying when I query for attendance report or anything similar?
PS: MySQL as database and queries are sent from PHP

There are several disadvantages to using the "month-based row" approach, and I do not see any advantage.

Using a row to represent an instance of attendance for a student makes logical sense. Using a row to represent a month's worth of attendance is arbitrary--why a month instead of a year or a 6-week cycle?
Think about how you'll be querying this data, or how you'll write a parameterized report for your users--when you're asked to find how many students were absent on a specific day, you'll need to parse out the month to find the right record, then look at different columns depending on the day of the month. Needless complication.
Right now, it looks like you want to record whether they are absent and the reason, but that will change eventually. You'll need to add a comment to explain the reason, whether it was approved, or some such, and you don't want to add 31 columns to your table every time that happens.


Does Ordering BY primary key help query performance when joining table to query Wondering about performance when joining a table with a query. Does the ORDER BY help?
For example if I am joining a user_stat table where the primary key is user_id, order_type with a selection of orders.
SELECT user_stats.*, order_stat.num_orders as orders_in_range FROM
  user_stats INNER JOIN (
    SELECT user_id , order_type, count(order_id) as num_orders FROM orders
    WHERE order_date > '2020-01-01 00:00:00'
    GROUP BY user_id, order_type
    !!! ORDER BY user_id, order_type !!!
  ) as order_stat ON user_stats.user_id = order_stat.user_id AND user_stats.order_type = order_stat.order_type

I understand that there are other ways to write this. I am just wondering if the ORDER BY speeds things up, slows them down or has no effect. Or if there is some way to let the optimizer know that they are in order so can do things faster?
I am using mySQL 5.7

In general, specifying an ORDER BY clause is an extra operation that can result (and generally does) in more work for your queries, even queries that JOIN two tables together with an ORDER BY clause that sorts the data to the order of the JOIN predicates.
The query optimizer is (usually) smart enough to know when to internally sort the data to most efficiently JOIN two tables together, and will do so dependent on the indexes of those tables (because indexing already sorts the data) that it's going to use for the query.
So it's theoretically possible that the query optimizer might choose an index that is sorted differently (and/or results in a different sort operation) than the one you specified in your ORDER BY clause which will result in extra work for the query to be processed and the data to be served. And in the best case, generally, you're not adding any benefit by specifying an unneeded ORDER BY clause even if it's not causing any harm.


What makes SQL Server prefer page locking over row locking? I'm probably dumb for asking this, but I'm wondering WHY would SQL Server prefer page locking over row locking when running DML queries.

In my specific scenario, I'm seeing in various deadlock graphs that queries that should S lock 400-500 rows are choosing to page lock instead - entering the deadlock in the first place. Note that I have not disabled row locks in our indexes. Moreover, I don't really think I'm missing indexes as the only suggestion coming from SQL are of the "create a nonclustered index on every column in this table" sort.

To be clear: I'm not asking for help on solving our deadlocks (not yet at least), I just want to understand if I'm missing something to try and foresee how SQL Server will behave.

There are many reasons why page locks might be used over row locks, but the most basic answer is memory management. 400-500 row locks might be far less attractive than locking a smaller number of pages to accomplish the same task.

It's also possible that the way the query is written, or the way the index is designed is responsible for the type of locks taken.

See my posts here for greater detail:

Bad Estimates And Locking
Index Key Column Order And Locking


MySQL: columns with mixed character encodings, and finding columns which have multibyte data I have some rather large CSV files that I am loading into my MySQL 5.7 database.  The files are several gigabytes in size, several million lines long, and have large column widths that must be used in joins (sometimes as long as ~500 characters).

The data is all standard English characters, and most of the columns can fit into a single byte character set like latin1.  However, several of the columns require unicode for things like trademark/registered/copyright symbols, measurement symbols (inches, feet, radius, etc), and therefore I've been using utf8mb4 on all tables.

The problem with doing this is twofold.  It blows up our index sizes, so in some cases, we can't create an index on a column(s) because the width becomes greater than 3072.  Additionally, it seems to be having a significant performance impact, presumably because the data size is 4x.

What I'd like to do is use latin1 on all columns in the table, and only utf8mb4 on columns that need it.  This leads to my questions -

What's the best way to identify for sure which columns are actually storing multibyte characters?  Can I detect that somehow, either within my CSV prior to loading (using python/pandas maybe?), or from within the database?  The files are stored as utf8.  They are currently loaded into a utf8mb4 table.  If I could easily scan the table and say "this column contains no multibyte data", I could change it to latin1.

Second, will I run into problems if I try to create composite indexes with columns using different encodings?  Say column A is utf8mb4 and column B is latin1.  Is there anything wrong with creating an index on these two columns?  ie: CREATE INDEX my_index
ON my_table(A, B);.  I'm assuming there's no issue doing that.

The data size is not 4x.  English text, even in utf8mb4, takes only one byte per character.  Trademark(etc) symbols are multi-byte.  Then ones you mentioned are only 2 bytes.  Emoji and some Chinese is where 4 bytes become necessary.

Don't create indexes on large columns.  Don't create indexes until you have the queries -- derive the optimal indexes from the queries.

It is perfectly fine (in MySQL, at least) to have one column be latin1 and another be utf8mb4 (etc).  And both of those can be in the same index.

I suggest making a couple of passes over the data.  First bring everything in with utf8mb4, no indexes, wide columns (eg TEXT).  Then analyze what you got -- SELECT MAX(CHAR_LENGTH(col2)), ...; test for non-latin1, etc.  For the second pass, re-do the schema to be closer to max len, etc.


Access .sql script files on other partition or hdd I'm running a Linux Mint 19.1 - Tessa OS, which for you who might not know it is based on Ubuntu 18.04 - Bionic Beaver. On this I have installed postgresql 11 which work well. There is one problem though. For organisational reasons code files are stored in project directories on a separate partition of my hdd. These directories, or actually the complete partition can not be accessed from within the psql environment, even though the user account has full access to the partition normally.

To begin with, auto-completing paths with tab doesn't work for the partition and typing the path manually gives a Permission denied message. I've tried adding a su or sudo argument before the \i but it doesn't work either. Nor does starting the session from within the partition.

E.g.

user@laptop:/$ sudo -u postgres psql
[sudo] password for user:
psql (11.5 (Ubuntu 11.5-3.pgdg18.04+1))
Type "help" for help.

postgres=# \i /media/user/Storage/Projects/DB_project/script1.sql
/media/user/Storage/Projects/DB_project/script1.sql: Permission denied
postgres=# su \i /media/user/Storage/Projects/DB_project/script1.sql
/media/user/Storage/Projects/DB_project/script1.sql: Permission denied
postgres-# ;
ERROR:  syntax error at or near "su"
LINE 1: su
        ^
postgres=#

For now I have to copy the files I want to use to my /home/ directory, or any directory in that tree, and then make sure to paste any updated files back in the Projects directory tree.

Any ideas on how to solve this so I don't need to copy files back and forth all the time?

Turns out this was much easier to fix than I though. Being completely new to both SQL and postgres I foolishly assumed that psql had something to do with the problem. As it turns out the directory /media/user in which Storage is mounted did not have Read & Write permissions set for other users, so even though I would have been allowed to access the file inside Storage I could not get these.

Changing the permission in /media/user solved the problem. In the future I'll make sure that all directories in a path are accessible to me.

Thanks again for the help and advice.


What happens if log files storage is full? I'm currently studying oracle 11g,i had this question in mind but never found the answer even in the docs.
What happens if log files storage is full?
does the DB stop or block any DML or DDL execution? Does it delete ancient log files and run like nothing happened ?
Thank you !

What happens if log files storage is full?

Short answer: The database hangs and will not accept any further changes.  Read activity may or may not be able to continue.
Changes (insert, updates, etc.) are written to the Online Redo logs.  There are always a number of these arranged as a Circular Buffer and, once one of them is full, the database simply "moves on" to write into the next, "available" one.
The Online Redo Logs that are full are picked up the by the Archiver process, which dumps the redo data into an Archived Redo Log (ArchiveLog) file and clears the OnLine Redo Log, making it available for use again.  This usually happens in plenty of time, before the database needs to use that particular Online Redo Log file again.
If the disk containing the Archivelogs fills up, nothing happens - straight away.
The Archiver process will simply wait until it gets enough free space to write the ArchiveLog file and will not do anything else until it can.
Meanwhile, the database continues to fill up the OnLine Redo Logs with changes, periodically moving from one to the next, as they fill up.
The Crunch Point comes when the Archiver is "stuck" and the Online Redo Logs are all full up as well.  Now your database simply hangs.  It cannot accept any further changes because it has nowhere to record them.
By this time, the Alert log will be full of error messages saying that the Archiver is stuck.
You should only remove ArchiveLog files manually using RMAN.
RMAN knows which files it has to keep hold of so that it can recover your database and so won't get rid of anything you need.  Just deleting files by hand, you might.
Better still, you can tell the database to put the ArchiveLog files into the Fast Recovery Area, in which case Oracle database can manage them automatically.  You still need to setup and configure RMAN.


"Credential retrieval failed" during recovery of an Oracle database When starting up automatic instance INSTANCE:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/19/2015 15:40:19
RMAN-04006: error from auxiliary database:
    ORA-12638: Credential retrieval failed

I created an Oracle 12c database with 1 CDB and 2 PDB and told Oracle to create a user for me and also added this user in the registry to have access to the eventlog. I don't have any problem in restore but recovery is giving the above error. I am doing PIT restore and then recovery using these commands:

shutdown immediate;
startup;
RUN {
configure default device type to SBT_TAPE;
set until SCN = 2259403;
restore pluggable database PDB1;
recover pluggable database PDB1 auxiliary destination='C:\oracle\12.1.0\dbhome_1\temp';
[STDIN]: }
alter pluggable database PDB1 open resetlogs;
EXIT;

I've just solved the same error on Oracle12c under Windows on my test enviroment.
The Oracle service was running under oracle user local account but I was trying the PITR with my domain user. Even if my domain user was member of ORADBA group I've got the same nasty ORA-12638 error.

I've changed the user account of the Oracle Service and got it finally working.
I guess if you use SYSTEM account it will work as well.

Regards,
Daniel.


Commit Insert but Hold Lock I have implemented what is effectively a reliable queue through the following:

CREATE TABLE Queue (MessageID UNIQUEIDENTIFIER, Message varchar(255))

--Enqueue Command
DECLARE @MessageID UNIQUEIDENTIFIER = NEWID()
INSERT INTO Queue (MessageID, Message) VALUES (@MessageID, 'message data')

--Process
BEGIN TRAN -- actually done via ADO
SELECT Message FROM Queue WITH (ROWLOCK, XLOCK, READPAST) WHERE MessageID = @MessageID
-- at this point, the client does some computation, which could fail
DELETE Message FROM Queue WHERE MessageID = @MessageID
COMMIT TRAN --again, from ADO

Question being: how can I take a lock on the original insert, but still keep the insert committed if the processing transaction fails?

The queue is inserted and read from by multiple clients and there is also a recovery client which attempts to find any orphaned messages by:

SELECT MessageID FROM Queue WITH (READPAST)

and it is on rare occurances catching the message after it is inserted but before it is locked for processing.

The database is running on SQL Server 2012, the client is a .Net 4 client using ADO.NET.

This isn't really a reliable queue pattern

You have a long running transaction that depends on the client processsing time
A client time out won't release locks because connection pooling and you don't have SET XACT_ABORT

Typically, you'd set a row as "In Processing" with an UPDATE using ROWLOCK, HOLDLOCK, READPAST hints. This allows multiple clients to not block each other and you can

Then, when the client has done it's stuff, you can DELETE the row in a separate transaction. Or, flag "Has Error" on the same row as needed.

See these for more:

https://stackoverflow.com/a/940001/27535 (SO)
FIFO queue table for multiple workers in SQL Server


DECIMAL and NUMERIC datatype in Postgres what is the use of decimal and numeric datatype in postgreSQL. As per the reference the following is the explanation given to these datatypes.

decimal,numeric --> It is a user specified precision, exact and range up to 131072 digits before the decimal point and up to 16383 digits after the decimal point.

The above statement shows the description of decimal and numeric datatype. But, still I didn't understand what is the
    exact use of these data type and where it is used instead of other datatypes.

Answer with example is much appreciated...

From the documentation

  The types decimal and numeric are equivalent. Both types are part of
  the SQL standard.

float, real, ... and the rest of the floating point types are inexact. This happens in all programming languages. As you have a type like BigDecimal in Java to represent exact numbers, in PostgreSQL you have decimal and numeric.

They are commonly used to represent currency.


Difference between character_maximum_length and character_octet_length Maybe there is an answer for this somewhere but, I couldn't find any efficient answer on Google for this question, therefore, I will ask this here.

I wanted to get some information about my Stored Procedure parameters dynamically, so I wrote this script:

SELECT  PARAMETER_NAME,
        DATA_TYPE,
        CHARACTER_MAXIMUM_LENGTH,
        CHARACTER_OCTET_LENGTH
FROM    INFORMATION_SCHEMA.PARAMETERS
WHERE   SPECIFIC_NAME = @ProcName

The question is what the difference between CHARACTER_MAXIMUM_LENGTH and CHARACTER_OCTET_LENGTH. Could you give me example of when they would have different results?

Thanks in advance.

As described in the MSDN documentation CHARACTER_OCTET_LENGTH is the length in bytes, and CHARACTER_MAXIMUM_LENGTH is the length in characters.

For parameters of type char or varchar they will be the same, but for parameters of type nchar or nvarchar they will be different, with OCTET-LENGTH being twice (usually if not always) the CHARACTER_LENGTH.