Oracle Database Copy Failed Using SQL Developer A few days ago while I tried perform database copy, using the database copy feature in SQL Developer, from a remote server to a local server. I got some warnings and one of them was like this:

  "Error occurred executing DDL for TABLE:MASTER_DATA".

And then I clicked yes, but the result of database copy was unexpected, there were only few tables has been copied.

When I tried to see DDL from SQL section/tab on one of table, I got this kind of information:

  -- Unable to render TABLE DDL for object COMPANY_DB_PROD.MASTER_DATA with DBMS_METADATA attempting internal generator.

I also got this message and I believe this message showed up because there's something wrong with DDL on my database so tables won't be created.

  ORA-00942: table or view does not exist

I've never encountered this problem before and I always perform database copy every day since two years ago.

For the record before this problem occurred, I have removed old .arch files manually not by RMAN and I never using any RMAN commands. I also have removed old .xml log files, because these two type of files have made my remote server storage full.

How to trace and fix this kind of problem? Is there any corruption on my Oracle?

The problem was caused by datafile has reached its max size though. I have resolved the problem by following the answer of this discussion
https://stackoverflow.com/questions/25350703/ora-01652-unable-to-extend-temp-segment-by-128-in-tablespace-system-how-to-ext

Thank you everyone for the help.


Preparing query in postgreSQL with variable table name? I'm trying to do an INSERT INTO a table whose name is in the variable myTable.

In MySQL, I'd do it like this:

SET @SQL = CONCAT('INSERT INTO ',myschema'.'mytable,' SELECT action_name, user, ...');
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

I checked into postgreSQL documentation for prepared statements, and I couldn't find any similar examples.

I can assemble query into a string:

sql_query := 'INSERT INTO ' || mySchema || '.' || myTable || ' SELECT action_name, user, ';

But how do I execute it?

To write procedural code and use variables with PostgreSQL, the most common way is to use the plpgsql language, in a function or in a DO block.
Executing Dynamic Commands in the documentation has all the details you need.

Note that a dynamic SQL statement does not require a PREPARE like in your MySQL example. It's possible to EXECUTE a dynamic string directly containing the SQL query, and in most cases that's how it's done.

What might be confusing to a beginner in Postgres is that it has two distinct forms of EXECUTE:

EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] in a plpgsql code block is a plpgsql instruction.
EXECUTE name [ ( parameter [, ...] ) ] is an SQL query, where name is the name of prepared statement previously declared with PREPARE.


Full text search on two tsvector columns I'm trying to do a full text search across two columns of two separate tables in PostgreSQL 9.4.2. The tables and columns are:

article.article_title
keyword.keyword

Where there is a many-to-many relationship between the two. I think I've got most of the way following this blog post. However, I am struggling to formulate the query across the two columns. This query is returning results:
SELECT distinct title, pub_date, web_id, city_points, city_name FROM (
  SELECT article.article_title as title,
  article.pub_date as pub_date,
  article.web_id as web_id,
  string_agg(st_asText(city.geom), ', ') as city_points,
  string_agg(city.city, ', ') as city_name,
  article.title_tsv ||
  keyword.keyword_tsv
  as document
  from article, article_keywords, keyword, article_cities, city
  where article.id = article_keywords.article_id
  and keyword.id = article_keywords.keyword_id
  and article.id = article_cities.article_id
  and city.id = article_cities.city_id
  GROUP BY article.id, keyword.id) p_search
WHERE p_search.document @@ to_tsquery('putin');

But I think the query suffers many of the same deficiencies from a previous question I asked here.
I am also trying to return the following columns in the query:

article.pub_date
article.web_id
city.city_points
city.city_name

I am pretty new to full text search - and SQL in general.
Let me know if you need any more information.

In parts similar to my last answer:

SELECT a.id, a.article_title AS title, a.pub_date, a.web_id
       c.city_points, c.city_names
FROM   article a
LEFT   JOIN (
   SELECT ac.article_id AS id
        , string_agg(st_asText(c.geom), ', ') AS city_points
        , string_agg(c.city, ', ') AS city_names
   FROM   article_cities ac
   JOIN   city           c ON c.id = ac.city_id
   GROUP  BY 1
   ) c USING (id)
WHERE a.title_tsv @@ to_tsquery('putin')
OR    EXISTS (
   SELECT 1
   FROM   article_keywords ak
   JOIN   keyword          k ON k.id = ak.keyword_id
   WHERE  ak.article_id = a.id
   AND    k.keyword_tsv @@ to_tsquery('putin')
   );

What's new here: we are only interested in the existence of a matching keyword, we are not even displaying them. (You may want to rethink that: shouldn't it become obvious from the result why the row was found?)

So I am using an EXISTS semi-join as alternative WHERE clause. Related:

Count where previous record exists in either of two tables

Once again we aggregate cities per article_id before joining to article, so we need no aggregate or DISTINCT in the outer SELECT since we do not multiply rows in the join.

And once again use LEFT [OUTER] JOIN to include articles that are not linked to any cities at all.

Be aware that a list of comma-separated tables combined with WHERE conditions to join them is effectively the same as [INNER] JOIN on a join condition. Basic in the manual here.


Time to apply transaction logs: does it matter how many logs? When restoring from a backup in SQL Server, the procedure is to restore the .bak file and then apply any .trn files since the last full backup.

Does it make a difference how many .trn files there are, if they cover the same transactions? I.e. is it faster or slower restore a 1-hour .trn file vs twelve 5-minute .trn logs?

There is a very small amount of overhead associated with reading headers from backup files.  The more backup files, the more overhead needed; however the amount of data actually read for each header is relatively small. The actual rollback/roll-forward work performed by SQL Server would be no different - SQL Server looks at the transactions in the restore file, and applies them to the database.

If your database has a lot of transactions per second, then each log backup will have a fair number of transactions within it, and the above logic applies.  If, however, you have a very low number of transactions over the 5 minute period, then the amount of data in any given 5-minute log backup would be quite small, and having lots of small log backups might increase the time needed for restore.

Having transaction log backups taken every 5 minutes, instead of every hour, gives you more up-to-date backups.  You could conceivably take that a bit too far, by say, doing a BACKUP LOG every 15 seconds.  This would create a huge number of backup files that would result in a myriad of other issues such as time required to read the file system structures for the folder containing the backups.  The key here is to use a bit of common sense, and balance the need for recent backups against the need to keep the number of files as small as you can.


How to improve speed of MYSQL query I am wondering how I can improve the speed of this query:

SELECT
    caf.user_id,
    caf.family_number,
    caf.card_number,
    caf.suspension_start_date,
    caf.suspension_end_date,
    caf.balance,
    util.room,
    caf.meal_plan_type,
    student.special_diet,
    student.specify_other,
    users.surname,
    users.type,
    users.name,
    users.contact
FROM
    (SELECT
        *
    FROM
        caf_student_cards) AS caf
        LEFT JOIN
    (SELECT
        user_id, special_diet, specify_other
    FROM
        students) AS student ON caf.user_id = student.user_id
        LEFT JOIN
    (SELECT
        resource_id AS room, user_id
    FROM
        utilizations) AS util ON caf.user_id = util.user_id
        LEFT JOIN
    (SELECT
        surname, name, type, user_id, contact
    FROM
        system_users) AS users ON caf.user_id = users.user_id
WHERE
    contact > 0;

While I don't do a lot of work in MySQL, at the least I can suggest a simplification of the query. In the FROM clause of the main query, JOIN the target tables directly, instead of making a subquery from each of the tables to join:

FROM
    caf_student_cards AS caf
    LEFT JOIN students AS student ON caf.user_id = student.user_id
    LEFT JOIN utilizations AS util ON caf.user_id = util.user_id
    LEFT JOIN system_users AS users ON caf.user_id = users.user_id

NOTE: You'll lose your renaming of util.resource_id, so you'll also need to change:

    util.room,

to:

    util.resource_id as room,

I can't think of any potential benefits of doing things the way you have (certainly not when your subquery is doing a SELECT *), and I've seen references that lead me to believe MySQL may be materializing the subqueries (meaning it's creating a copy of the table with the columns you list), which is likely to cause unnecessary extra work and resource consumption.


MySQL Syntax Error 1064 On Set Let's see if I can edit this and put the whole procedure in.

I am trying to convert an Oracle database to MySQL. I have all the tables, keys, indexes, and views converted. I now need to convert a stored procedure to MySQL.

I have most of it done, and there is only one hang up on my code:

set dns1_tmp = X.X.X.X;
SET dns2_tmp = X.X.X.X;

This gives me an error of 1064 Syntax Error: Missing semicolon

I have tested the rest of my procedure, and it works fine. It creates it, runs it, and retrieves data from it, but only if I remove those two lines.

Any ideas on what I can do?

Whole stored procedure:

DELIMITER //

USE `TEST`//

DROP PROCEDURE IF EXISTS `proc_IN`//
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_IN`
(IN DNIS VARCHAR(20),
IN MSISDN VARCHAR(20),
IN AVPAIR1 VARCHAR(20),
IN AVPAIR2 VARCHAR(20),
IN GROUPID VARCHAR(20),
OUT DNS1 VARCHAR(15),
OUT DNS2 VARCHAR(15),
OUT AUTHSTAT VARCHAR(100))
BEGIN
declare dns1_tmp varchar(15);
declare dns2_tmp varchar(15);
set dns1_tmp = X.X.X.X;
SET dns2_tmp = X.X.X.X;
DECLARE avpair1_tmp varchar(15);
DECLARE avpair2_tmp varchar(15);
DECLARE grpid_tmp varchar(15);

DECLARE C_USER CURSOR FOR SELECT AVPAIR1, AVPAIR2, DNS1, DNS2, GROUPID FROM GRP, ALLMEMBER WHERE ALLMEMBER.GROUPID=GRP.GROUPID
    UNION
    SELECT AVPAIR1, AVPAIR2, DNS1, DNS2, GROUPID FROM GRP;
OPEN C_USER;
FETCH C_USER INTO AVPAIR1, AVPAIR2, DNS1, DNS2, GROUPID;
LOOP
    FETCH C_USER INTO avpair1_tmp, avpair2_tmp, dns1_tmp, dns2_tmp, grpid_tmp;
    INSERT INTO duplog VALUES(DNIS, MSISDN, avpair1_tmp, avpair2_tmp, dns1_tmp,dns2_tmp, grpid_tmp, SYSDATE);
END LOOP;
IF C_USER%ROWCOUNT > 1 THEN
        INSERT INTO duplog VALUES(DNIS, MSISDN, AVPAIR1, AVPAIR2, DNS1,DNS2, GROUPID, SYSDATE);
            SET AUTHSTAT := 'ok';
        elseif C_USER%ROWCOUNT = 1 THEN
            SET AUTHSTAT := 'ok';
ELSE
            SET AUTHSTAT := NULL;
END IF;

CLOSE C_USER;
COMMIT;
END //
DELIMITER ;

UPDATE:

I have tried single quotes, double quotes, and back ticks. When I do the code in this fashion

set dns1_tmp = 'X.X.X.X';
SET dns2_tmp = 'X.X.X.X';

I get a different error of Syntax Error: Missing end

The IP Addresses in your DNSx_tmp variables need to be enclosed in single quotes in order to make them character strings (as they have been Declared).

The SET DNSx_tmp; variables need to be moved below all the DECLARE statements.

You also need to add a BEGIN clause above DECLARE c_user and the matching END clause before your final ENDclause.


Advice on indexing a search table, should I or shouldn't I? I'm making a search page on my website and I have 3 different areas that need to be searchable.

Should I make an index table for each using MyISAM engine. Or should I just put the extra data inside of the previous tables using InnoDB?

MyISAM (separated index table)

benefits

faster loading/search

cons

need to use CRON jobs to update it daily, same data in 2 places

InnoDB (all data in same table)

benefits

data all in one place, easier to maintain, instant updating

cons

possibly slower loading times

Does anybody have any advice on this?

You should use only InnoDB.  Period, Full Stop.

MyISAM is going away.  MyISAM is rarely faster than InnoDB.

With 5.6, you get FULLTEXT indexing in InnoDB.  With 5.7, you get SPATIAL indexing.  So, upgrade if you need to.


Data integration name of best practices to avoid corruption, caused by becoming out of sync? I have identified an issue in the design of a system and I am trying to find the right language to describe the cause of the issue so I can report it to managers, and get the issue fixed.

The problem is we have a web service syncing data by copying changes (based on a datetime of last edit column) in a student enrollments table from one system to another system. But because the sources system deletes rows when a student drops a subject, drops are not being synced.

To be able to detect the drops the system needs to maintain a state of what it has copied to the second system so it can recognize when rows have been dropped or needs to validate that they tables are in sync, but I am not sure how to describe this and reference a specific best practice or methodology to backup my argument.

Does anyone know the name of the best practices or data integrity rules that would prevent this type of logical error in the design?

It's an issue of data integrity. You have two systems that should reflect the same data, and they don't.

Conceptually, a transaction in one system isn't truly complete until it has been applied to both systems. There are several ways to do this: replication is probably the most comprehensive, but comes with a certain amount of both system and human overhead.

The method you're using involves asynchronously applying the transactions. You finish in one system, and catch up the other system periodically. The problem you've identified is that a certain type of transaction is not being caught, and thus is never being applied to the remote system.

This means that, as it stands, the database system as a whole (taking both systems involved as one system, for this table at least), fails to be ACID compliant.

It's not Atomic; a delete transaction in the primary system fails to complete ever, thus the transaction is never completed, and never will be.

It's not Consistent (covered adequately above).

You can probably make an argument for not being Isolated or Durable, if you want to.

And, note that the individual systems may be fine; it's the fact that you've created a hybrid system, and the problem lies there.

There are a number of fixes. I know one's already in the works, but I will mention one that worked for my company: we used a delete trigger to record the deleted records, and made removing deleted records a part of the sync process.

The key is in the fact that you've got a hybrid, home-grown database system. There's nothing wrong with that (for small amounts of data, replication or log shipping entire DBs to get one table are overkill), but it is possible to miss a facet when building such a system, and your system is going to have fewer eyes on it than Oracle, MS SQL Server, or MySQL.

Hope that's of some assistance.


To replicate, or not to replicate 1.Intro

Hello, I am running web application on 2 separate geographical locations(e.g. Europe, Hong Kong). Let's suppose Hong Kong server is an Amazon EC2 instance(paying for disk space, IO disk operations and traffic). Inspect the following figure:

2.Problem

When you should remotely access DB server A and when you should add DB server B and replicate in between these 2 DB servers(MySQL master-master) ?

3.Additional questions

The cost of either approach can be easily calculated. I just need to "load test" the following variables for particular web application:

-replication traffic/db access traffic

-disk operations

-disk space

-num of users(load test)

Q: What's the best way to get the replication traffic ? Is there any good replication monitoring software or I should stick with wireshark, collectd ?

Q: Are there any good blogs, papers, articles writing about similar topic ?

Thank you :)

Use maatkit heartbeat. You will need another perl process running is very helpful.

 You can also use mk-slave-delay to generate lag. Protection purposes.


Apply settings to multiple output columns in SSIS flat file source Is there an easier way to apply property settings to multiple output columns in a SSIS flat file source, rather than manually applying to each output column? (SQL Server 2014.)  I am attempting to export data from a large text file source to an Azure database and the process is very slow.  I have read some tuning guides that suggest that the "FastParse" and "Length" properties should be set.  The problem is, my data source is columnar (which is transposed to rows further down the track) and it is tedious to set properties individually for a large amount of columns.

In general it would also be beneficial to know if it is possible to bulk-apply other such settings.

In the advanced tab of the connection manager of the flat file data source. You can mark multiple columns by holding CTRL and then set the properties they have in common.


MySQL: Illegal mix of collations I've tried using a stored procedure to create indices, and got the following error:

ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and
                    (utf8_unicode_ci,IMPLICIT) for operation '='

There's no line number or any other debugging hint.

What's the best way to debug this error?
Are there any good guidelines to avoid encoding inconsistencies?

After some trial and error, I've learned how and where to apply COLLATE:

Converted lines like:

SELECT SOMETHING
FROM SOMEWHERE
WHERE table_schema = given_database
AND   table_name   = given_table
AND   index_name   = given_index;

To:

SELECT SOMETHING
FROM SOMEWHERE
WHERE table_schema COLLATE utf8_unicode_ci = given_database
AND   table_name   COLLATE utf8_unicode_ci = given_table
AND   index_name   COLLATE utf8_unicode_ci = given_index;


How can I configure SQL Server Aliases on a SQL Server Client Machine Even after installing SSMS on a SQL Server Client machine, I found that SQL Server Configuration Manager would not run.  Manually loading the mof file via Microsoft's Instructions was sufficient to repair the "Cannot connect to WMI provider" error, but this only caused the 32 bit configuration manager to show up.  I also want to configure 64-bit aliases.

Historically, I've avoided this problem by using the SQL Server Install Media to install SSMS, but I was hoping this was no longer necessary now that SSMS was released separately.  Absent a documented recommendation from Microsoft, I'd rather avoid the commonly suggested hack of copying sqlmgmprovider.dll from other machines.

Try using cliconfg.exe instead.  This is shipped with Windows.

c:\windows\system32\cliconfg.exe - 64Bit aliases
c:\windows\syswow64\cliconfg.exe - 32Bit aliases


Select Most Recently Updated Row Based on Sequence ID with Three Tables I've previously asked how to use a sequence ID to select the most recently updated row, but I'm not quite sure how to apply it to a more advanced query involving multiple joins. Here is my current query:

select
  p.Person_ID,
  p.Person_Name,
  p.Person_Age,
  d.Department_Name,
  c.Company_Name,
  c.Company_City,
  c.Company_State
from Person p
left join Department d
  on p.Person_Department_ID=d.Department_ID
left join (
  select Company_ID, Company_Name, Company_City, Company_State
  from Company
  group by Company_ID, Company_Name, Company_City, Company_State
) as c on c.Company_ID=d.Department_Company_ID
where c.Company_ID=1

I have made an example on SQLFiddle for this question. I want to only select the most recent Person (max(Person_Seq_ID)), and only show the most up-to-date Department name next to that Person (max(Department_Seq_ID)). There should only be seven records returned in my uploaded example. How do I do this?

Note that only Person and Department have sequence IDs—not Company.

Unless I missed something, the following will give you what is needed :

SELECT * FROM
(
select
  p.Person_ID,
  p.Person_Name,
  p.Person_Age,
  d.Department_Name,
  c.Company_Name,
  c.Company_City,
  c.Company_State,
  p.Person_Seq_ID,
  d.Department_ID,
  d.Department_Seq_ID,

  MAX(p.Person_Seq_ID) OVER(PARTITION BY p.Person_ID) as mPerson,
  MAX(d.Department_Seq_ID) OVER(PARTITION BY p.Person_ID, d.Department_ID) as mDep

from Person p
left join Department d
  on p.Person_Department_ID=d.Department_ID
left join (
  select Company_ID, Company_Name, Company_City, Company_State
  from Company
  group by Company_ID, Company_Name, Company_City, Company_State
) as c on c.Company_ID=d.Department_Company_ID
where c.Company_ID=1
  ) a
WHERE Person_Seq_ID = mPerson and Department_Seq_ID = mDep

Here is an updated link showing this on SQLFiddle.


Adding around 200 rows in a table grows size of the table by 400kb where avg row size is 0.2KB We are running into an issue where if we look at the avg row size (0.2kb) x number of rows (200) inserted in one of our table, the size should grow by around 40 KB.  However the total used space has increased by around 400 KB.  I believe there is additional index size to be considered.  However it still should not show this big difference. What possibly should we look at to understand this difference and what is happening?

This is happening with couple of our tables which only do inserts related to data collected from a third party system.  It does not do any updates on any rows.  Also we have an index on the table on the primary key which is an ID (number) field.

Check that if you have indexes (clustered/nonclustered, full text).
Use sp_spaceused 'your_table_name' to check that you can actually discard indexes.
Check what type of table is used.
In your example of 0.2KB=205 Bytes you will have 38 rows per data page if your table is heap and 39 rows per data page if it is clustered table.

Please see below the example:

IF EXISTS (SELECT * FROM sys.tables
            WHERE name = 'sparse_pages')
    DROP TABLE sparse_pages;
GO
CREATE TABLE sparse_pages
(
KeyField SMALLINT --IDENTITY (1,1) PRIMARY KEY
, Filler VARCHAR(8000) null
)
GO

SET NOCOUNT ON
INSERT INTO sparse_pages( Filler) values ( REPLICATE('a', 192))
GO 39

-- Average row size now 205 Bytes
WAITFOR DELAY '00:00:03';
GO
SELECT 'This is Heap. Note Data Space'
GO
-- Check table size
sp_spaceused 'sparse_pages'
GO

IF EXISTS (SELECT * FROM sys.tables
            WHERE name = 'sparse_pages')
    DROP TABLE sparse_pages;
GO
CREATE TABLE sparse_pages
(
KeyField SMALLINT IDENTITY (1,1) PRIMARY KEY
, Filler VARCHAR(8000) null
)
GO

SET NOCOUNT ON
INSERT INTO sparse_pages( Filler) values ( REPLICATE('a', 192))
GO 39

-- Average row size now 205 Bytes
WAITFOR DELAY '00:00:03';
GO
SELECT 'This is Clustered Index. Note Data Space'
GO
-- Check table size
sp_spaceused 'sparse_pages'
GO

Check for random inserts/updates/deletes in your table. This may be an issue as free space is not reclaimed back. The free space may be wasted during page splits too.

Please see below the example with average row size 205 Bytes and 200 rows (just like in your case). Table data size is 1.57 MB:

IF EXISTS (SELECT * FROM sys.tables
            WHERE name = 'sparse_pages')
    DROP TABLE sparse_pages;
GO
CREATE TABLE sparse_pages
(
KeyField SMALLINT IDENTITY (1,1) PRIMARY KEY
, Filler VARCHAR(8000) null
)
GO

Enter the data

SET NOCOUNT ON
INSERT INTO sparse_pages( Filler) values ( REPLICATE('a', 8700))
INSERT INTO sparse_pages( Filler) values ( REPLICATE('a', 192))
GO 200

DELETE FROM sparse_pages
WHERE LEN(Filler)>300
GO
-- Average row size now 205 Bytes
WAITFOR DELAY '00:00:03';
GO
-- Check table size
sp_spaceused 'sparse_pages'
GO


ON DELETE cascade alternate I am trying to create a database for 2 Tables like:

  Table1 : tb1-id(pk) | tb2-id | other

  Table2 : tb2-id(pk) | other

  FOREIGN KEY Table2(tb2-id) REFERENCES Table1(tb1-id) ON DELETE CASCADE

Table pseudo-code:

CREATE TABLE Table1 (
tbl1-id        INT PRIMARY KEY
, tbl2-id      INT FOREIGN KEY REFERENCES Table2(tbl2-id) ON DELETE CASCADE
...
)

Table2:

CREATE TABLE Table 2(
, tbl2-id      INT PRIMARY KEY
...
)

There can be 2 records in Table1 that point to a single record in Table2.  If we DELETE 1 of the 2 records pointing to Table2 from Table1 MySQL would also DELETE the referenced record from Table2.  I want the referenced record to be deleted only when both the referencing records in Table1 are deleted.

Is there any other MySQL method/constraint that can fulfill the above requirement.

Reading your question literally, "if we delete 1 of 2 records pointing to Table2 from Table1 would also delete referenced record from Table2", you just need an ordinary ON DELETE CASCADE, as you've shown: Table2 references Table1, and deleting a record in Table1 causes all related records in Table2 to be deleted, even if those records are being pointed to by other records in Table1.

However, if your goal is instead to delete from Table2 when there are no more associated records in Table1, whereas there are normally two such records, you'll need something more elaborate.

I think better in concrete terms, so based on your pre-edit question, I'll call Table1 Guardians (because "parents" can be confusing for SQL-heads when they're actually the child half of a relationship) and Table2 Students (similarly, avoiding the word "children").  The goal is to ensure that any students that do not have any guardians get deleted.

If polygamy and divorce are unheard on, one option would be to have two NULLable fields in Students, FatherID and MotherID (or Parent1 and Parent2, to allow for step-parents, gay marriage, etc.).  Set these to reference Guardians with ON DELETE SET NULL, and add an ON UPDATE trigger to Students to delete a record if both fields become NULL.  If Bob's father is deleted but he still has a mother, he's OK, but if his mother is deleted the poor orphan gets wiped from the database.  This assumes that a CASCADE UPDATE event triggers triggers, I don't know that for a fact.

A more robust solution would be to create a third table relating Guardians to Students.  It would include fields GuardianID, StudentID, EffectiveDate (these three making up a candidate key), StopDate, and maybe fields to indicate the rights of the guardian; perhaps only one has legal custody, and the other should not be allowed to remove the student from school grounds.  This would allow for death or divorce: just set the old record's StopDate.  Insert a new record if the remaining guardian ever remarries.  You could create an ON UPDATE trigger and an ON DELETE trigger on this third table, checking to see if there are now any students which lack active guardians.

However, before putting too much engineering into this, consider: do you really need to cascade?  Keep the referential integrity, of course, but just enforce deletion logic in your business logic layer.  For that matter, does it even make sense to delete students?  If you're asked how many students took a specific class in 2011, but you've deleted some because they've subsequently dis-enrolled, you'll have an undercount.


Slave DB promoted as Master DB when Master DB is down. New data added on the new master while old master is down I am currently setting up a call center system (Vicidial) having a Master DB and a Slave DB.
When the Master would go down, I have made a script on the SlaveDB that would execute "stop slave" on the slave mysql service and make it as a new master by pointing the other servers (Web, Dialer, Archive) to the slave as their new Master database server.

When the master is up again, I would run another script on the SlaveDB that would point the servers back to the original MasterDB and would let the slave DB become slave again to the original MasterDB by executing "start slave".

My question is ~
If there are new data added on the New Master while the original master is down, how do I replicate this data to the original master when it goes back up before I start demoting the new master to slave again?

Answers and advice/constructive criticisms will be much appreciated.

P.S. I am a newbie in Databases and scripting.

Thanks in advance!

As @jkavalik said with your current setup the only option you have is to recreate the master as the slave of the new master and do a switchback if necessary.

Keep in mind that you will have some data loss because you can have some unreplicated transactions on the failed master unless you enable semi-sync replication.

  Sidenote: you don't have to write your own scripts for
  moving/promoting slaves if you don't want to. There are plenty of open
  source solutions which can do this for you. For example you can give a
  try to orchestrator. I used this in production setup with over
  4000 servers.

  Jetpants is also a popular toolset developed by Tumblr which can also handle cloning and many other common tasks.

  But there are also other solutions if you are not satisfied with these.

Further down the road

If you want to have better HA and/or more automated way of operation you can look at the following solution. (this list is not complete only some pointers to give you a start)

Percona XtraDB cluster

With XtraDB cluster you can ensure consistency and have true HA with even under 100ms failover time (depending on your failover technology).

MySQL Fabric

It's Oracle's in house HA solution. Requires client libraries so make sure your application can handle it properly.

MySQL MMM

One of the old player on the MySQL HA market but worth to mention.

I hope this helps.


Is it possible to index or cache non-immutable function (based on date) I have a function in my Postgres database where the calculation is based on how old a record is - so given the same parameters, it always outputs a different value.

However, I'd like to index or cache the results for about an hour or two, because accuracy is much less important than read speed and load efficiency. Is this possible to do in Postgres, or do I need to use an external system to help? For example using cron to calculate a temporary table on a time interval which I access rather than using the function directly in queries. This is Postgres 11.

The basic algorithm is a measure of hotness, which is essentially a measure of popularity (measured by votes), but decaying over time (like Reddit posts).

The function code is below. It is a bit verbose; for the purpose of the question it probably could just be votes - ageInDays.

-- reddit's hotness algorithm, allegedly
-- https://medium.com/hacking-and-gonzo/how-reddit-ranking-algorithms-work-ef111e33d0d9
-- def hot(ups, downs, date):
--     s = score(ups, downs) // This is ups - downs for reddit, but we don't have downvotes
--     order = log(max(abs(s), 1), 10)
--     sign = 1 if s > 0 else -1 if s < 0 else 0
--     seconds = epoch_seconds(date) - 1134028003
--     return round(sign * order + seconds / 45000, 7)
create function epochSeconds(timestamp) returns int as $$
  select extract(epoch from $1)::int;
$$ language sql stable;

-- we do log2(n+1) instead of log10(n) since we get fewer votes than reddit
create function hotnessOrder(votes integer) returns numeric as $$
  select log(2, greatest(votes + 1, 1));
$$ language sql stable;

create function hotnessSign(votes integer) returns integer as $$
  select(case when votes > 0 then 1 else 0 end);
$$ language sql stable;

create function hotnessSeconds(creation timestamp) returns integer as $$
  select(epochSeconds(creation) - 1134028003);
$$ language sql stable;

-- We inflate hotnessSeconds divisor by a lot. Reddit's is 45000. We want good entities
-- to stay on the front page for several days
create function hotness(votes integer, creation timestamp) returns double precision as $$
  select round(
    ((hotnessSign(votes) * hotnessOrder(votes))
      + (hotnessSeconds(creation) / 5 / 45000))::numeric
  , 7)::double precision;

$$ language sql stable;

create function entity_hotness(entityId integer) returns double precision as $$

  select hotness(entity_votes(entityId), created)
  from entity
  where entity.id = entityId;

$$ language sql stable;

I'd much rather have a simple solution in Postgres that does not involve external dependencies as this project is intended to be as simple as possible. But this question is just to ask if it's possible to do without external tooling.

You could use materialized views, but you would still need some external tool (cron, etc.) to refresh them on a regular basis.  You could install some PostgreSQL-based scheduling system, but that would also count as an external tool as they don't ship with PostgreSQL.  (And would probably rely on cron internally anyway).

You could have the querent (an already existing "external tool") itself do the refresh.  It is a bit tricky for it to know when to, though. You would have to store the last refresh time someplace, then trigger a refresh if that is too old, then update that stored time.  If it needs to refresh before returning a result, then it will kind of suck for the unlucky user to whom that task falls.


Limit MySQL server login attempts without fail2ban I would like to limit login attempts via phpMyAdmin to protect against brute-force attacks. I found solution above, but it is based on fail2ban.

[mysqld-iptables]
enabled  = true
filter   = mysqld-auth
action   = iptables[name=mysql, port=3306, protocol=tcp]
       sendmail-whois[name=MySQL, dest=root, sender=fail2ban@example.com]
logpath  = /var/log/mysqld.log
maxretry = 5

Are there any other, maybe "native" solution for this purpose? Should I use maybe access restriction instead of limiting?

No, phpMyAdmin doesn't have any built-in protection directly against brute force attacks. phpMyAdmin can log failed connection attempts to syslog, a feature which was added specifically so that fail2ban could be easily used for this protection (rather than re-developing the wheel in the application itself). You may be able to work something out with MySQL's resource limits, but those are really designed for restricting valid authenticated users, not stopping brute force attacks. As an alternative, if you have a firewall front end, you may be able to enact some protections there if you really wish to not use fail2ban, but the officially sanctioned means is indeed using fail2ban.


select data on base of comparing multiple values between two columns in same table question id (1,3,2,3) responses id(1,6,4,7)

     Question_Id   Response_Id
               1 =>1
               3=>6
               2=>4
               3=>7

I need to select those distinct users from users_response(following) table who answered all questions mentioned above and have the same answer as mentioned under response_id (1,6,4,7) to the question_id(1,3,2,3) respectively.

Result should be only '2'.

Can you please help me or give me some hint to deal with it. Any dynamic way because count of question_id and response_id can be increased. like in this example here are only total 4 conditions are given but it could go up or down.

 SELECT * FROM users_responses
 WHERE
   (Question_Id = 1 AND Question_Id = 3)
 AND Question_Id = 2 AND Question_Id = 3
 AND (Question_Id = 1 AND Response_Id = 1);

but I'm getting no row while there is at least one row with user_id 2 is fulfilling conditions.

There are many solutions for this kind of problem (it's called relational division).

The most simple ways to write it would be either by self-joining the table (4 times) or with a GROUP BY and HAVING clause:

SELECT user_id
FROM users_responses
WHERE (question_id = 1 AND response_id = 1)
   OR (question_id = 3 AND response_id = 6)
   OR (question_id = 2 AND response_id = 4)
   OR (question_id = 3 AND response_id = 7)
GROUP BY user_id
HAVING COUNT(DISTINCT question_id, response_id) = 4 ;

If there is a UNIQUE constraint on (user_id, question_id, response_id), you can simplify the HAVING clause to:

HAVING COUNT(*) = 4 ;


Does dropping index on column A affect queries on column B? I have a table like below.

Column |         Type
-------------------------------
id     | integer
foo_1  | character varying(255)
foo_2  | text
foo_3  | character varying(255)
foo_4  | character varying(255)
foo_5  | character varying(255)
foo_6  | character varying(255)
foo_7  | character varying(255)
foo_8  | character varying(255)
foo_9  | timestamp without time zone
foo_10 | timestamp without time zone
foo_11 | timestamp without time zone
foo_12 | integer
foo_13 | boolean
foo_14 | integer
foo_15 | integer
foo_16 | integer
foo_17 | character varying
foo_18 | character varying
foo_19 | character varying
foo_20 | character varying
foo_21 | character varying
foo_22 | boolean
foo_23 | character varying
foo_24 | integer
foo_25 | integer
foo_26 | boolean

Indexes:
"items_pkey" PRIMARY KEY, btree (id)
"index_items_on_foo_12_and_foo_25" UNIQUE, btree (foo_12, foo_25)
"index_items_on_foo_25" btree (foo_25)
"index_items_on_foo_12_and_foo_11" btree (foo_12, (foo_11 IS NULL), foo_11) WHERE foo_11 IS NULL
"index_items_on_foo_12_and_foo_18_and_foo_19" btree (foo_12, foo_18, foo_19)

Referenced by:
TABLE "bar_1" CONSTRAINT "fk_rails_0b050ce365" FOREIGN KEY (item_id) REFERENCES items(id)
TABLE "bar_2" CONSTRAINT "fk_rails_3a20ccae66" FOREIGN KEY (item_id) REFERENCES items(id)
TABLE "bar_3" CONSTRAINT "fk_rails_6a4a43dcc1" FOREIGN KEY (item_id) REFERENCES items(id)
TABLE "bar_4" CONSTRAINT "fk_rails_92770ef494" FOREIGN KEY (item_id) REFERENCES items(id)
TABLE "bar_5" CONSTRAINT "fk_rails_f9da65bc10" FOREIGN KEY (item_id) REFERENCES items(id)

I want to do an UPDATE:

UPDATE items
SET foo_1 = 'foo' || items.id || 'bar',
    foo_8 = NULL,
    foo_17 = NULL,
    foo_20 = NULL,
    foo_21 = NULL,
    foo_23 = NULL;

Does dropping any of the indexes of foreign keys might improve the time of the above update? None of the updated columns are in the indexes or constraints.

Since none of the updated columns are in indexes or constraints, you could conceivably do a 'HOT' (Heap-Only-Tuple) update, which doesn't need to maintain the indexes. But for that to work you have to have room in each table block to hold both the old and the new data at the same time. Once it needs to migrate rows to different blocks (because there is no room in the current block) then it needs to maintain all the indexes to point to the new block. Since you are updating every row in the table in a single transaction, this is unlikely to be practical as space for the old tuples cannot be recycled that quickly. You could help promote the HOT updates by setting the table fillfactor to a low value (<50) before loading the table data, but this unlikely to be an optimal solution.  Better to just drop the index and constraints, or to delay creating them in the first place, as discussed in your related question.


How to remove stuck mode in SQL Server? I was clearing tables by using query in SQL Server. A query that normally takes 2 minutes took the better part of 3 hours... and it wasn’t even close to being complete. Therefore, I killed SQL Server in task manager because I felt like I had no other choice.

Upon logging back in, the database has been (in Recovery). According to various posts I read online, simply detaching it and reattaching it should work fine however, when I try to run queries to detach it I get the following error:

Alter database failed because a lock couldn’t be obtained for database <sel>

I also read that this was due to a connection not being terminated but everything I tried gives that same lock error. I made sure I was in master, I reset the PC, un-plugged network table, tried the commands in the GUI. Nothing is letting me detach said database or remove it from In recovery.

What can I do?

You did blunder by restarting SQL Server. You should have not done that.Unless SQL server will recover the database fully and comes to state where database finds itself in same consistent state as it was before the force shutdown it will show in recovery state. Your best bet is to wait and watch you cannot run any transaction on recovering database. Also since you manually killed SQL server process while it was  rolling back a query its even more worse. Now when SQL server database will come online the whole rollback process will start from beginning not from point where you killed SQL server process/query. So it might take time and unless it fully rolls back the previous killed query it won't come online neither you can force it to.

One advantage you can have is if you have Enterprise edition SQL Server will come online after redo phase of recovery. So that might be little relief to you.

PS: Seriously avoid any such random suggesstion given on web. You should take this as a lesson to never kill SQL server process or restart SQL Server if certain query is taking time. You should wait and chec rollback status of query using below script

select session_id,
blocking_session_id,
wait_type,
percent_complete,
((estimated_completion_time)/1000) as Estimated_Complettion_time_Sec,
((total_elapsed_time)/1000) Toatal_Elapsed_time_Sec
from sys.dm_exec_requests where status='recovery'


Is there any benefit to this additional table? Sorry for the vague question title, I'm not very experienced with db administration

Essentially I have a web application that allows polls to be created with any amount of options, then users can answer a poll and select their options and assign a priority to each answer (first choice, second choice, etc)

So there's a poll table, poll_options table, and a votes table. For each answer a user selects, a new row is added to the votes table

+------+---------+----------------+---------+----------+
| id   | poll_id | poll_option_id | user_id | priority |
+------+---------+----------------+---------+----------+
| 1    | 7       | 4              | 16      | 1        |
| 2    | 7       | 1              | 20      | 1        |
| 3    | 7       | 3              | 20      | 2        |
| 4    | 8       | 1              | 16      | 1        |
| 5    | 8       | 2              | 16      | 2        |
| 6    | 8       | 3              | 16      | 3        |
| 7    | 8       | 4              | 16      | 4        |
| 8    | 8       | 3              | 2       | 1        |
| 9    | 8       | 1              | 2       | 2        |
+------+---------+----------------+---------+----------+

Now, assuming that table is acceptable, what comes to mind is that if there are 10 polls with 10 options each and 10 users, and they all vote for each thing on every poll... that's 1000 rows already - if I need to check if a user has voted in a poll already, I have to look up the current poll ID and user ID

SELECT COUNT(*) FROM votes WHERE user_id = 16 AND poll_id = 7
1

Is it bad design that it has to go through thousands of rows to find that? Would it be better to have a smaller table between the polls table and the votes, for checking if a user has voted in a poll, all the polls the user has voted in, etc.? Or would this have no performance benefit over my current design?

Would look something like this I guess

+------+---------+---------+
| id   | poll_id | user_id |
+------+---------+---------+
| 1    | 7       | 16      |
| 2    | 7       | 20      |
| 3    | 8       | 16      |
| 4    | 8       | 2       |

Then I'd add the id from this new vote_item table to the votes table as a FK (?) so I can grab the vote_item id via the user_id and poll_id, and return all the rows in the votes table with that vote_item_id

If I need to check if a user has voted in a poll already, I have to look up the current poll ID and user ID
   SELECT COUNT(*) FROM votes WHERE user_id = 16 AND poll_id = 7 ;

This would be efficient with an index on (user_id, poll_id). But if you don't need the count but just whether a user has taken a poll, you only need an EXISTS subquery. Either SELECT EXISTS (...) or WHERE EXISTS (...), depending on what you want to do with this check:
... EXISTS (SELECT * FROM votes WHERE user_id = 16 AND poll_id = 7)

It will be a bit more efficient than the COUNT() query - assuming that the above index has been added.

Is it bad design that it has to go through thousands of rows to find that?

No, if you have the index, it won't go through thousands or millions of rows. It will do a single index seek.

Would it be better to have a smaller table between the polls table and the votes, for checking if a user has voted in a poll, all the polls the user has voted in, etc.?

It might be better, yes. The smaller (in number of rows) table means that the indexes will be smaller, too. So you would use an EXISTS subquery on a smaller index. For the specific query, the difference in efficiency would be very small though. For different queries, say "How nay users have taken this poll?" or *"How many users have taken each poll?", you'd get larger benefit as they require a scan of the whole index.

Or would this have no performance benefit over my current design?

So, it depends on what kind of queries you have.

Would look something like this I guess

    +------+---------+---------+
    | id   | poll_id | user_id |
    +------+---------+---------+
    | 1    | 7       | 16      |
    | 2    | 7       | 20      |
    | 3    | 8       | 16      |
    | 4    | 8       | 2       |

Then I'd add the id from this new vote_item table to the votes table as a FK (?) so I can grab the vote_item id via the user_id and poll_id, and return all the rows in the votes table with that vote_item_id.

Not exactly. The table only needs user_id and poll_id and a UNIQUE constraint on (poll_id, user_id). The id is useless for this many-to-many table. In most many-to-many tables, it's common to have two unique indexes, on (a,b) and (b,a). So, I suggest you have that (poll_id, user_id) as the primary key and a unique index on (user_id, poll_id) in the new poll_users table, if you decide to add this table.
You are right about the FOREIGN KEY though. You would add a foreign key from votes (poll_id, user_id) that REFERENCES poll_users (poll_id, user_id) and remove the individual foreign keys from votes to polls (poll_id) and users (user_id).
By the way the id in the votes looks useless, too. I'd have a UNIQUE constraint on (poll_id, user_id, poll_option_id) (meaning: no user can answer the same poll option in a poll twice) and throw away that id.


How can I move a MySQL database to another drive? I'm using MySQL 5.5 on a local machine to analyze a large amount of government data. I've created a local database that resides on my default drive (Win7 C: drive). I'd like to store the data on my E: drive, a large eSATA external drive.

What steps should I take?

Shutdown mysql.
Move all the files in your current data directory to the new location (check out the location in step 3 - datadir parameter).
Locate my.ini file (it is in the mysql installation directory). Change datadir parameter value to point to the new location.
Start mysql.


How to get best mark for each Student I can't find a solution although it should be easy one.

I have some students and all I need is to get the highest mark of each one in Math.

TableName: Students.
Columns:
Name Mark
John 90
John 84
John 92
John 60
Yan  75
Yan  88
Yan  60

I want to get this results:

Name Mark
John 92
Yan  88

I have tried to do top 1, but it gives me only one student. I need to do somehow top 1 on each student, but I don't know how to do that.

Please help me to figure it out.

Thanks in advance.

Have you tried:

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


How to design to avoid two-way reference between two tables I am having a problem with foreign keys between two tables. I am trying to model a relationship between members of a club and their relatives. This is a straightforward many-to-one relationship, with many relatives related to one member. However, one and just one of this relatives must be the main contact. Also, many members can have the same relative as main contact (siblings to their father as an example).

EDIT: Members are kids, and relatives must be adults, so relatives can't be members, and members can't be main contact of another member.

A FK on member table refering the PK of relatives would work, but this will leave me with a circular reference and a "chicken-or-egg" problem.

What is the right approach to this kind of problems? Maybe I am just thinking it too much.

Schema is more or less like this:

+--------------+    +-------------+
| member       |    | relatives   |
+--------------+    +-------------+
|id            |<-+ |id           |<-+
|name          |  +-|*member_id   |  |
|address       |    |name         |  |
|phone         |    |relationship |  |
|...           |    |phone        |  |
|*main_contact |-+  |...          |  |
+--------------+ |  +-------------+  |
                 +-------------------+

Sounds like you may need a self-join:

Get rid of the relatives table entirely
Define the main_contact as a foreign_key to the id column of the member table.

Or depending on the relationship a many-to-many resolver table in the middle:

Get rid of member_id from relatives
Get rid of main_contact
from from member
Add a new table member_relative with the
columns member_id, relative_id and a column to indicate
relationship_type.


Leading zeros are truncated when I spool to CSV I have spooled a file to CSV using sqlplus. Everything works great but there is one  issue. I have mobile numbers which start with 0, for example 04589 and 02586.

After the spooling is complete, 0s are truncated and the end results are 4589 and 2586.

I want the output to display in CSV as:

04589
02586

String are written between "" in the CSV. So your datatype is not string type, or the tool you use for viewing the CSV automatically converts and trims it.

[oracle@o71 ~]$ sqlplus -S bp/bp
set markup csv on
set echo off
spool 1.csv
select '04589' as phone from dual
union all select '02586' from dual;

"PHONE"
"04589"
"02586"

exit
[oracle@o71 ~]$ cat 1.csv

"PHONE"
"04589"
"02586"

[oracle@o71 ~]$


Wait for an AlwaysOn replica to be synchronized in Powershell I am working on a disaster recovery script. One part of it is not being consistent.

This is the code:

...
## Switch replica to Synchronous
Set-SqlAvailabilityReplica -AvailabilityMode "SynchronousCommit" `
-Path SQLSERVER:\Sql\Server1\Instance\AvailabilityGroups\SCOM-DBs\AvailabilityReplicas\Server2

## Wait until the replica is synchronized
$NewPrimary = Get-Item SQLSERVER:\Sql\Server1\Instance\AvailabilityGroups\AG-DBs\AvailabilityReplicas\Server2

Write-Host -NoNewline "Waiting for DBs to be synchronized"
while ($NewPrimary.RollupSynchronizationState -notlike 'Synchronized') {
   Write-Host  -NoNewline "."
   Start-Sleep -s 2
}
...

Sometimes gets synchronizing and it does not change even though SSMS dashboard changes. Is there a better in Powershell way to detect when the replica is synchronized?

I found my problem.

I was working under the wrong assumption that the objects were automatically refreshed in Powershell. I added a "$NewPrimary.Refresh()" insinde of the while loop and that did the trick.

I hope this helps someone with the same wrong assumption in the future.


PostgreSQL Slave has more files in pg_xlog than /wal_archive Background: for various reasons I am switching PostgreSQL Slave from a Streaming replication to a warm standby replication.

The streaming replication was setup using this guide: http://technology.trapeze.com/journal/postgresql-streaming-replication-ubuntu-1004/

To make the switch to a warm standby: I am following instructions for warm standby here (1) https://wiki.postgresql.org/wiki/Warm_Standby and here (2) http://www.postgresql.org/docs/current/static/pgstandby.html

I am finding that on the slave there are more log files in pg_xlog/ than in wal_archive/ (files sent from Master). How is that possible? I think it's hindering my warm setup.

in /var/lib/postgresql/9.3/main/pg_xlog/ of Master I have files from 000000010000000000000001 to 00000001000000000000001A.backup to 00000001000000000000001C
in /var/lib/postgresql/wal_archive/ of Slave I have files from 000000010000000000000001 to 00000001000000000000001A.backup to 00000001000000000000001B
in /var/lib/postgresql/9.3/main/pg_xlog/ of Slave I have files from 00000001000000000000001C to 000000010000000000000023

recovery.conf contains:

restore_command = 'pg_standby -d -s 2 -t /tmp/pgsql.trigger.5442 [/wal_archive] %f %p %r 2>>standby.log'

when I restart the Slave it complains that it is missing the xxx1C log file

First question, how is it possible for the Slave to have more pg_xlog/ log files than the Master?

Second question, to resync the servers in warm standby mode: do I have to do pg_basebackup again (to essentially copy Master's /data and /pg_xlog directory) to the Slave?

Just trying to understand PostgreSQL :) Thanks in advance!!

I found a very similar question here, that seems to have never been answered: http://www.postgresql.org/message-id/CAE7ByhgTJ6xOe4f3aa42Kn0_5gfEzWW1FZ9QQqsC6RP-Fnh_ow@mail.gmail.com

how is it possible for the Slave to have more pg_xlog/ log files than the Master?

The whole point of archiving WAL on the master to some external location is to let the master then delete it to free space in its pg_xlog, while replicas might still need it.

A replica can have more archives in pg_xlog than the master, and older ones, if it's lagging behind the master due to failure to keep up with replay. However, with pg_standby that shouldn't happen - the archive might contain more xlogs, but the replica should only be reading them on-demand.

It's hard to be specific, because you've given a broad description of the issue rather than actual directory listings, and haven't explained the exact steps you followed to set up the replica. Or shown the exact log file output from the replica. So the best I can do is "it sounds like the replica setup is broken somehw".

  to resync the servers in warm standby mode: do I have to do pg_basebackup again (to essentially copy Master's /data and /pg_xlog directory) to the Slave?

Assuming that here /data is the main datadir, containing global, base, pg_clog, etc, and that pg_xlog is the transaction logs from a different disk: Yes, that's right.

You must use the pg_basebackup command, though, or follow the instructions in the manual for correct file system level copies using pg_start_backup() and rsync/cp.

You also have to make sure you've stopped the replica first. Overwriting its datadir while it's running will make it quite upset.

Streaming replication vs warm standby

Hot vs warm standby is orthogonal to streaming vs log shipping replication.

What you're trying to do is use log shipping instead of streaming replication. It doesn't matter for this purpose if the replica is a hot standby or a warm standby, i.e. whether or not it's accepting queries.

Personally I recommend using both methods - use streaming, and fall back to log shipping if there's a problem with streaming. PostgreSQL does this automatically if both are configured.


Search for Arabic text ignoring diacritics, alef hamza differences, and kashida in SQL Server and Oracle Well, I have a table with an Arabic column which may contain variant forms of the same word in multiple rows, for example the word "أسمى" might be in the following forms:

with diacritics: "أَسْمَى";
with changing the last letter of "ى" into "ي" so it would be like "أسمي";
with kashida or "ـ" in some part of the word so it would possibly be "أسمــى";
with variant forms of alef hamza (أ - إ - ا - ء), so it might be "اسمى" or "إسمى";
any combination of the former cases, i.e diacritics and kashida.

I'm looking for a way to store these values in the database (actually I need a solution for SQL Server and another one for Oracle), and to retrieve them regardless of these differences.

I found that I should use an Arabic collation like this Arabic_CI_AI but this collation only helped me in sorting out the problems #1 and #2.

In addition I considered using a fulltext index on the column, but this has its drawbacks and it doesn't provide a full solution.

How can I resolve this?

Not sure but based on my understanding, this is related to the language/character set.

I use Oracle database and I do store arabic values within the database.

The character set I use is UTF8, I have also tried storing the different examples provided for the same word and I see it working fine.

I am sure SQL Server also supports such character set.


Select where value in row is different and update I have a large table that includes rows like the following:

Query:

select *
from cartographic_text_vm
where fid = 'osgb5000005104723440'
order by ckpt;

Results:

-[ RECORD 1 ]---------------+---------------------------------------------------------
change_history              | 2012-09-05,New
descriptive_group           | Buildings Or Structure
descriptive_term            |
feature_code                | 10026
fid                         | osgb5000005104723440
cartographic_text_version   | 1
make                        | Manmade
physical_level              | 50
physical_presence           |
text_render_anchor_position | 4
text_render_font            | 1
text_render_height          | 2.125
text_render_orientation     | 0
text_string                 | (Secondary)
theme                       | Buildings
version_date                | 2012-09-06 00:00:00
primary_key                 | 8240079
partition_key               | 2
random_number               | 9
geom                        | 0101000020346C000085EB51B8486B1B417B14AE47E9AFFB40
ckpt                        | 1
end_ckpts                   | {7}
transact_status             |

-[ RECORD 2 ]---------------+---------------------------------------------------------
change_history              | 2012-09-05,New,2015-10-25,TextChange
descriptive_group           | Buildings Or Structure
descriptive_term            |
feature_code                | 10026
fid                         | osgb5000005104723440
cartographic_text_version   | 2
make                        | Manmade
physical_level              | 50
physical_presence           |
text_render_anchor_position | 4
text_render_font            | 1
text_render_height          | 2.125
text_render_orientation     | 0
text_string                 | (secondary)
theme                       | Buildings
version_date                | 2015-10-26 00:00:00
primary_key                 | 8240079
partition_key               | 2
random_number               | 124
geom                        | 0101000020346C000085EB51B8486B1B417B14AE47E9AFFB40
ckpt                        | 7
end_ckpts                   | {61}
transact_status             |

-[ RECORD 3 ]---------------+---------------------------------------------------------
change_history              | 2012-09-05,New,2015-10-25,TextChange,2016-04-04,Modified
descriptive_group           | Buildings Or Structure
descriptive_term            |
feature_code                | 10026
fid                         | osgb5000005104723440
cartographic_text_version   | 3
make                        | Manmade
physical_level              | 50
physical_presence           |
text_render_anchor_position | 4
text_render_font            | 1
text_render_height          | 2.125
text_render_orientation     | 0
text_string                 | (secondary)
theme                       | Buildings
version_date                | 2016-04-05 00:00:00
primary_key                 | 123465 <- needs to be changed to 8240079
partition_key               | 2
random_number               | 135
geom                        | 0101000020346C0000AE47E17A446B1B41C3F5285CE7B0FB40
ckpt                        | 61
end_ckpts                   |
transact_status             |

I want to search for rows that have the same value for fid, but different values for primary_key. The database has a versioning extension enabled, so it's important that each version of a fid has the same primary_key.

When you look at the three records above, they are all in different checkpoints as denoted by the value in the ckpt field. The lowest number is the original version of the record, all of the records that have a higher ckpt value are modifications of that original record. I want to update any records that have a different primary_key from the original record to that value. As you can see in Record 3, the primary_key is 123456 which needs to be updated to the primary_key in record 1.

Any pointers in how the SQL should look?

update cartographic_text_vm
set primary_key = new.primary_key
from
    (select *
     from cartographic_text_vm
     where (fid, ckpt) in
         (select fid, min(ckpt)
          from cartographic_text_vm
          group by fid)
    ) new
where cartographic_text_vm.fid = new.fid
  and cartographic_text_vm.primary_key != new.primary_key;


Multiple PIVOTs SQL Server I have a TSQL query that uses PIVOT to create columns called Analyst and Supervisor from the ApprovalLevelName column.

This is the current output:

In my query, I have ApprovedTime column, and Status column (commented out in the query below).

I want to also PIVOT these so that they both appear for analyst and supervisor.
i.e. AnalystStatus, SupervisorStatus, AnalystApprovedTime, and SupervisorApprovedTime.

Below is how I want it to look...

How can I do this? Or is it possible in SQL? I tried using APPLY and another PIVOT but no success.

Query:

SELECT PivotTbl.*
FROM
(
    SELECT
        Code
        ,Period
        ,Task
        ,ApprovalLevelName
        ,ApprovedBy
        ,CommentText
        ,CommentPostedBy
        --,ApprovedTime
        --,Status
    FROM [Tasks].[TaskRecordDetail] tr
) AS SourceTbl
PIVOT
(
    MAX(ApprovedBy)
    FOR ApprovalLevelName IN ([Analyst],[Supervisor])
) AS PivotTbl

Here is how the data looks without a PIVOT:

If these are the only two ApprovalLevelName values that are possible, and ignoring for now what you want to do with comments (since both rows could have comments), here is an approach that doesn't use PIVOT explicitly but still gets the results you want.

Table and sample data:

CREATE TABLE #tr
(
    Code              char(4),
    Period            datetime,
    Taskname          varchar(32),
    ApprovalLevelName varchar(32),
    ApprovedBy        varchar(32),
    Status            varchar(32),
    ApprovedTime      datetime
);

INSERT #tr VALUES . -- please use unambiguous date formats
('RTX4','20180331','Test1','Analyst',    'user1','Approved','20180406'),
('RTX4','20180331','Test1','Supervisor', 'user2','Approved','20180406'),
('RTX4','20180331','Test2','Analyst',     NULL,   NULL,      NULL),
('RTX4','20180331','Test2','Supervisor',  NULL,   NULL,      NULL);

Query:

;WITH x AS
(
  SELECT *, dr = CASE ApprovalLevelName WHEN 'Analyst' THEN 1 ELSE 2 END
  FROM #tr WHERE ApprovalLevelName IN ('Analyst','Supervisor')
)
SELECT Code, Period, Taskname,
  Analyst    = MAX(CASE dr WHEN 1 THEN ApprovedBy END),
  Supervisor = MAX(CASE dr WHEN 2 THEN ApprovedBy END),
  AnalystStatus    = MAX(CASE dr WHEN 1 THEN Status END),
  SupervisorStatus = MAX(CASE dr WHEN 2 THEN Status END),
  AnalystApprovedTime    = MAX(CASE dr WHEN 1 THEN ApprovedTime END),
  SupervisorApprovedTime = MAX(CASE dr WHEN 2 THEN ApprovedTime END)
FROM x
GROUP BY Code, Period, Taskname
ORDER BY Code, Period, Taskname;

Some repetition there, which goes up if you have more levels. Still, while PIVOT might seem simpler as a high level concept, it's also designed for simpler cases and when all of the possible output values are known, and this solution is more scalable in that way (and performance should be similar).

Results of the query seem to match your requirements:

Don't forget to clean up:

DROP TABLE #tr;


That particular function is a SQLCLR User-Defined Aggregrate (UDA). It is being called multiple times, once per each output row (pre-HAVING clause, if one is present). A UDA maintains state between executions so in some respects it does access all rows within a particular group, but it only gets one row at a time, and needs to store info for future calls.

Otherwise, SQLCLR functions cannot accept multiple rows. T-SQL functions, however, do have the ability to accept multiple rows via a Table-Valued Parameter (TVP).


migrating to a bigger server - how to copy over differences We have a very large database, one table (logs) contains the majority of the data (around 13 billion rows) total database size is approx 6.5TB and I have 900gb of space remaining before server is full.
We have a delete script for old data, but it doesn't effect size, as indexes are fragmented, it would be nice to reclaim this but i know it will introduce a huge downtime if i rebuild the one big index.
The system is always on, we do have quiet periods like sunday night, but any downtime needs to be max 2 hours.
I was planning on copying over the 1TB .bak file to new server, restore, rebuild indexes, then I somehow need to look at the differences between what changes, and update the new server with these differences (new or updates records)
My question is:
Is there a free tool built into sql server that can detect differences, and copy over new/changed data if I "link" the servers? (the one big table does not allow updates, only inserts/deletes)
We are using sql server 2017 ent
As a side note and for full picture, we do not use compression (row or page, this is a different route we are looking at too - if we can do without down time)

Change Data Capture is meant to capture changes that happen in tables and offers you a way to then read what those changes are to synchronize data to a target server.


Am I supposed to let PG know in advance before I back up its datadir? I run PostgreSQL on Windows. Its data dir is located in "My documents". I often back up that entire dir tree (among others) with ROBOCOPY, which "robustly" copies the dir tree to local and external disks.
Since PG is running at the same time as my ROBOCOPY script, I just had the idea that maybe this is not safe? Should I "let PG know" that I'm about to back up/copy its data dir, and then let it know when I'm done? Or is that unnecessary? Maybe even not a "thing" at all?
It strikes me that it could theoretically be bad if PG is writing to its database files while ROBOCOPY is also copying the same files. But maybe this has been solved fully 30 years ago and I'm just still stuck in early 1990s computing thinking.
I also (separately) use pg_dump regularly. The idea of shutting down PG entirely many times a day is not an attractive one.

In order to create a usable online file system backup, you have to do one of the following:

Run pg_basebackup with -X stream (the default in later versions). That will do everything for you.

If you want to use this Robocopy, you have to use the low-level API:

enable WAL archiving by setting archive_mode and archive_command to archive WAL segments (you might use Robocopy there)

run SELECT pg_start_backup('label'); to get the database into backup mode

backup the data directory with Robocopy

end backup mode with SELECT pg_stop_backup();

To recover, you wilk have to use restore_command
Note that I showed you the comparatively simple deprecated "exclusive backup", the "non-exclusive backup" is somewhat more complicated.

Consult the documentation for details.


Best Indexing Strategy for Query with Equality[A], Range[B], Group By[C], AND Order By[count(P)] I have a poorly performing query:

SELECT  user_id, count(item_id) as count
FROM table items
WHERE category = 'magazine'
AND created_at > 1384754400
GROUP BY user_id
ORDER BY count(item_id) desc
LIMIT 100

Whats the optimal indexing strategy in order to optimize this query?

Table Details

500million records with the following structure / cardinalities:

PRIMARY KEY (item_id) - cardinality: 500 M
user_id - cardinality: ~ 25 M
category - cardinality: ~ 2.5 M
created_at - cardinality: ~ 150 M

Indexing:

I have individual indexes on each the user_id, category and created_at fields

I also have the following covering indexes:

(category, user_id) - this is the one the query optimizer defaults to when running explain
(category, created_at)
(category, created_at, user_id) - this is one I attempted to create in order to optimize this query, however, it doesn't seem to be working very well.

If you ONLY want to optimise for this query. This is the best index:

ALTER TABLE items ADD INDEX (category, created_at, user_id)

This optimises the value of the filters, which reduces the total amount of data you touch. By adding user_id, item_id at the end of the query, you make the index covering and it saves you a lookup into the primary index.

We can assume that item_id is NOT NULL (as it is the PRIMARY index).

However, because the MySQL optimiser is pretty stupid, you may need to rewrite like this:

SELECT  user_id, SUM(count) AS count
FROM
(
  SELECT category, created_at, user_id, COUNT(*) as count
  FROM items
  WHERE category = 'magazine'
  AND created_at > 1384754400
  GROUP BY category, created_at, user_id
) AS d
GROUP BY user_id
ORDER BY count DESC
LIMIT 100


Availability Group of SQL Servers 2012, 2012 and 2016 possible? We have two SQL servers combined into Availability Group in primary data center (dc1):

dc1 server 1: SQL Server 2012 SP3 on Windows 2016
dc1 server 2: SQL Server 2012 SP3 on Windows 2012 R2

We are going to open new data center (dc2) which is going eventually to be new primary data center
In dc2 there is going to be third SQL server

dc2 server 3: SQL Server 2016 (or 2012) on Windows 2016

We would like to have SQL Server 2016 on server 3, but here are my questions:

1) --> What are potential problems if we add SQL Server 2016 as a third node to existing Availability Group that has two SQL Servers 2012?

2) --> Will AG be working properly or ? If you have any practical experience of doing so, please share

3) --> Do we really need to deploy server 3 as SQL Server 2012 (instead of 2016) in order for AG to be working ?

4) --> What are best options (aside of AG) to keep SQL servers 1, 2, and 3 in sync if we chose to deploy server 3 as SQL Server 2016 ? is that transactional replication

Thanks in advance!

1) --> What are potential problems if we add SQL Server 2016 as a third node to existing Availability Group that has two SQL Servers 2012?

Once you failover(or failover happens) to 2016, you wont be able to sync other replicas. You should use this when you want to do upgrade with minimal downtime.

Another issue is about the new query optimizer introduced in sql server 2014 and up. I have seen some queries perform badly on the newer version. So putting 2016 as new node introduces extra risk.

  2) --> Will AG be working properly or ? If you have any practical experience of doing so, please share

Above answer covers this question. A failover to 2016 will break replica sync status. Once you upgrade to higher version, the boot page gets upgraded which prevents tlog restoration of higher version to lower version.

  3) --> Do we really need to deploy server 3 as SQL Server 2012 (instead of 2016) in order for AG to be working ?

Yes - if you are planning to keep all the 3 replicas in sync.

No - if you are planning to upgrade with minimal downtime.

Make sure to sync sql agent jobs, logins, etc since they are not automatically synced.

  4) --> What are best options (aside of AG) to keep SQL servers 1, 2, and 3 in sync if we chose to deploy server 3 as SQL Server 2016 ? is that transactional replication

Replication is the only option available with some additional things to consider

Replication will be hard to maintain when using with AGs.
Depending on how you implement sync status in AGs, you will need to enable trace flag 1448
All the tables should have PKs defined if they are needed for replication.
You wont be able to truncate tables once you publish articles in replication.

My advise is to use sql server 2012. You can do an upgrade later by properly testing your application on sql server 2016.


How can I get all CREATE VIEW statements in Teradata? How to get all CREATE VIEW statements with a single query?
This Query is used to get the definition of a single view:
show view db.tablename;

You'll need to use the catalog views (h/t to Dieter for alerting me to truncation of RequestText in TablesV):
SELECT
  Tbl.DatabaseName
 ,Tbl.TableName
 ,COALESCE(TblText.LineNo,1) AS LineNo
 ,COALESCE(TblText.RequestText,Tbl.RequestText) AS RequestText
FROM
  DBC.TablesV Tbl
LEFT JOIN
  DBC.TableTextV TblText
    ON TblText.DatabaseName = Tbl.DatabaseName
            AND TblText.TableName = Tbl.TableName
        AND TblText.TableKind = Tbl.TableKind
WHERE
  DatabaseName = <Database name>
    AND TableKind = 'V'


Name for a "lookup" table with values in separate rows I am looking to put a name on a particular situation.  When you remove a comma-delimited column and split it into separate rows in a new table, what is that type of table called?

In the example below, I could keep FavoriteFoods in the top table (Albert Einstein would have "Apple Pie, Soup") but I want each food to have its own row.

Is the second table a "cross join" table that hasn't been normalized?

Please share if you know the name for this.  I will do more research once I know the name.

Removing repeating groups (your comma delimited values) and moving this data into another table where each value is a row in that table is called normalization.  In particular, you're moving from 0 Normal Form (0NF) to a higher normal form, (1NF or higher, depending on what functional dependencies remain).

In your specific example, once you split out the favourite foods into a child table you might be in either 1NF or 3NF, depending on whether you also have a "master table" of food types.

The new child table where the favourite foods are recorded might be considered an intersection table, particularly if you also have a master table of all of the foods listed one record per type of food.  This is the typical way in which a many-to-many relationship is implemented in a relational database.

If you have this master table of food types, you would be in 3NF.  If you don't your child table might not be considered to be 3NF, just 1NF, because the list of foods is subject to update anomalies and possibly delete anomalies - depending on how your system views foods themselves, independent of their relationship to people.


Query runs in serial and parallel mode in two identical databases on the same instance I have two databases in the same SQL Server instance.
One is a test database with a smaller number of records and total size of 500GB.
Other one is production database with a size of 2.2 TB.
Both database are similar with same database objects.
The server is enabled for parallel execution with MAXDOP = 8 and Cost Threshold for Parallelism = 20. There are 40 logical CPUs on SQL Server 2016 Standard edition.

When I run a somewhat complex query that has multiple joins and views, on the production database the query executes fast in parallel mode.

When I run the same query in the test database (on the same instance) the query runs in serial mode. Data in test database is static too.

I do not understand why it is not running in parallel mode.

From my research, since the test database is small compared to the production database, maybe the cost of the query in the test db does not exceed 20 (cost threshold for parallelism). How can I calculate the cost of the query to confirm?

I made the query run in parallel by removing a scalar function and replacing it with an inline function, but the same query runs differently in two identical databases, that is why I am confused. I have gone through the post which explains why SQL Server does not engage parallelism.

Estimated Subtree Cost is different in both. The one running in serial has a low value of 44. The parallel one has a higher value of 312.

Since the parallel one has to deal with more data, its individual node subtree cost is higher and adds up to 312. So maybe cost is the real issue?

My cost threshold for parallelism is 20 and the one running in serial has an estimated subtree cost of 44 (> 20). So shouldn't it run in parallel?

The cost that is determined by the query optimizer is really, a best guess. The guess is based off of CPU/IO resources the optimizer believes that the query will use. If you want to really get some good information (including query costs) install sp_blitzcache - https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit


Run Server Agent Job on Schedule only if CPU is idle I have a job set up in SQL Server Agent to run an update query. I want this job to execute every night between midnight and 5am and I want it to repeat every 5 minutes during that time frame. But I only want the job to actually execute if the CPU is idle, that is the CPU utilization is below say 20%. I know I can set up the schedule, and I can also set up the CPU Idle requirement separately. Is there a way for me to set it up so that both 'schedules' have to be true for the job to run?

Thanks

Or you can have one job that runs every 5 minutes with 2 steps:

Step #1 -- checks the CPU Usage (PowerShell), just google and there are plenty of scripts out there; If <20% exit with success go to Step 2, Else Exit with Error and finish the job (No Step #2)

Step#2 -- is the actual UPDATE.

Or as Ken suggested.


Looking for advice on how to integrate data from 100+ client DB's into a centralized reporting database I am a SQL Developer (not DBA or Architect) for a small (~50 employees) SaaS company. I am tasked with figuring out how to:

Offload operational reporting from our 100+ OLTP databases
Allow those reports to run against data from multiple client databases
Position our company to provide more analytics-based solutions in the future

I have read a number of articles on various technologies like transactional replication (specifically the many-to-one/central subscriber model), SQL service broker, log shipping, Change Tracking (CT), and Change Data Capture (CDC, my understanding is this is enterprise-only), and I am not sure what path is best to pursue.

I am hoping some of you with integration expertise may have encountered a setup similar to ours and be able to point me down a successful path or direct me to some resources that would be helpful.

Due to cost constraints, our solution must work within SQL Server Standard Edition. Also, the solution must be reasonable to support/maintain within our small organization.

Basic configuration:

We currently have 100+ individual client databases, most deployed on SQL servers at our data center, but some deployed on client servers within their data center that we can remote into. These are all SQL Server 2008 R2 databases, but we are planning to upgrade to SQL 2016 soon.

We use database projects and dacpacs to ensure the schema is the same across all client databases that would be integrated. However, since we do not force all clients to upgrade to new versions at the same time, some schema differences are possible between upgrades. The solution must be flexible enough not to break if client A is on software version 1.0 and client B is on version 1.1.

Operational reports are currently run directly from each client's OLTP database. We are concerned about the impact this will have on the application's performance if we do not offload it.

High-Level Requirements:

Our clients are hospital sterile processing departments (SPD’s) who want up-to-the-moment reports on what they’ve processed so far, where inventory is, etc. SPD's process inventory around the clock, including weekends and holidays. Since one of the main purposes of this effort is to better support operational reporting, we would like the data to be as close to real-time as possible to continue meeting clients’ needs.

Currently we have some SPD’s in separate databases that are actually part of the same hospital system. These clients want the ability to report against all the SPD’s in their system.

Strategically speaking, we would like the ability to easily aggregate data across all our clients to support our internal analytics initiatives. Our expectation is that we would be able to use the collected operational data as a source for data marts/warehouse.

Thoughts so far:

Transactional replication seems like it would provide the most "real-time" solution. I found this response to be especially helpful, but I am concerned that with the potential for schema differences it will not work for us: SQL Server Many-to-One replication

Log shipping doesn't sound ideal given that the log cannot restore while queries are active. I either have to kick everyone out so the log can restore or the data will become stale. I am unclear as to whether this method could be used to centralize data from multiple databases, since each shipped log would only be for the individual database it came from.

Using SQL service broker, latency may be unpredictable if a queue were unable to keep up with the number of messages to process.

CT only identifies a version for each table row. Latency would be dependent on how quickly we could process something like an SSIS package against each database to retrieve the data and insert it in a central repository.

Do we need to consider replicating each database individually and then perhaps use some sort of data virtualization technique to combine data from the various replicated sources?

Any advice or direction you are willing to provide would be greatly appreciated.

Do we need to consider replicating each database individually and then perhaps use some sort of data virtualization technique to combine data from the various replicated sources?

Yes.  You can host multiple subscriber databases on a single instance, and then query across them with views, or load them into a consolidated database.


V$SQL table elapsed time Does the elapsed_time column in the v$sql table indicate the time per query exeuction or is it a sum of all executions?

From the 11.2 Database Reference documentation:

  Elapsed time (in microseconds) used by this cursor for parsing,
  executing, and fetching

Here is a demonstration:

SQL> SELECT sql_text, elapsed_time FROM v$sql WHERE sql_text like '%lewap %';

SQL_TEXT
-----------------------------------------------------------------------------
ELAPSED_TIME
------------
SELECT sql_text, elapsed_time FROM v$sql WHERE sql_text like '%lewap %'
        5112

SQL> SELECT sql_text, elapsed_time FROM v$sql WHERE sql_text like '%lewap %';

SQL_TEXT
-----------------------------------------------------------------------------
ELAPSED_TIME
------------
SELECT sql_text, elapsed_time FROM v$sql WHERE sql_text like '%lewap %'
       26332

SQL> SELECT sql_text, elapsed_time FROM v$sql WHERE sql_text like '%lewap %';

SQL_TEXT
-----------------------------------------------------------------------------
ELAPSED_TIME
------------
SELECT sql_text, elapsed_time FROM v$sql WHERE sql_text like '%lewap %'
       65886

SQL> SELECT sql_text, elapsed_time FROM v$sql WHERE sql_text like '%lewap %';

SQL_TEXT
-----------------------------------------------------------------------------
ELAPSED_TIME
------------
SELECT sql_text, elapsed_time FROM v$sql WHERE sql_text like '%lewap %'
      103225

If you need the timing for one execution you could set timing on in an SQL*Plus session, turn on tracing with TIMED_STATISTICS, or add a comment to the SQL so that there is only one execution in v$sql.


How to best manage a queue of external processes My scenario is a web app that creates jobs that need to be processes by an R script. We are currently have the web app execute a SQL stored proc that executes the R script. However, we quickly realized this is unworkable. The first job eats a lot of memory up and then all subsequent jobs fail. We did set aside 40% of the servers memory for external processes, as described in MSFT's docs here:

https://docs.microsoft.com/en-us/sql/advanced-analytics/administration/how-to-create-a-resource-pool?view=sql-server-2017

I appreciate any advice or recommendations you may have.

You could have a background job (SQL Agent or other) that runs the stored procedure, ensuring that you only have one instance running at a time.

The web app, instead of directly invoking the procedure, could just write a row to a "job table" or "queue table" and the background process works off of that.


Fail SSIS "Execute Process Task" if false is returned I have an "Execute Process Task" that calls Powershell and checks if a folder is empty. I'm wanting the SSIS job to fail if $false is returned.

Here is the code

param ([string] $FolderLocation)

$FolderLocation = 'C:\Temp\IsFolderEmpty\'

if( (Get-ChildItem $FolderLocation | Measure-Object).Count -eq 0)
{
    #Folder Empty
    return $true

} Else {

    return $false

}

Any time I run this in SSIS, it always runs whether $true or $false is returned. I've tried creating a new variable and using it in the StandardOutputVariable but haven't had any luck.

Try using a Script Task to check the value of the SSIS variable that is set as the StandardOutputVariable of the Execute Process Task.  If this value is false the Dts.Events.FireError method raises an error event that can be used to fail the package.  The code below is an example of this using C#, where an error is raised when the variable is false.  The SSIS variable will need to be set in the ReadOnlyVariables field of the Script Task to allow it to be accessed.

    if (!Convert.ToBoolean(Dts.Variables["User::YourVariable"].Value))
    {
        Dts.Events.FireError(0, "Error", "Error Raised from Folder Check", null, 0);
    }


Help me improve my query Help me improve this query. It took 8 minutes to generate 16k rows only.

SELECT TPG.group_id
INTO   #tempgroups
FROM   [sqlcdb-h05].[cosmocallv6].[dbo].tpersonnel AS TP
       INNER JOIN [sqlcdb-h05].[cosmocallv6].[dbo].tpersonnelprofile AS TPP
               ON TP.person_id = TPP.person_id
       INNER JOIN [sqlcdb-h05].[cosmocallv6].[dbo].tprofilegroups TPG
               ON TPP.profile_id = TPG.profile_id
WHERE  ( TP.person_id = 766 )
       AND ( TP.tenant_id = 112 )

SELECT DISTINCT AC.Agent_ID, AC.Group_ID, AC.GroupName
INTO #TempAgent
FROM [offlinedbsvr].[PLDTCustomerCare].[dbo].AgentConfiguration AC INNER JOIN
     #TempGroups ON AC.Group_ID = #TempGroups.Group_ID

DELETE FROM #TempAgent
WHERE NOT([dbo].[GetUpdatedAgentGroup](#TempAgent.Agent_ID) = #TempAgent.Group_ID)

-- End Grouping
BEGIN
    SELECT DISTINCT
        [Date] = CONVERT(VARCHAR(25), arrivaltime, 101),
        [Start Time] = arrivaltime,
        [End Time] = terminatedtime,
        [Duration] = DATEDIFF(ss, arrivaltime, terminatedtime),
        [Agent First Name] = co.agentfirstname,
        [Agent Last Name] = co.agentlastname,
        [Group Name] = groupname,
        [Phone Number Called] = outdialingaddress
    FROM
        [offlinedbsvr].[PLDTCustomerCare].[dbo].StreamSummary ss
            INNER JOIN [offlinedbsvr].[PLDTCustomerCare].[dbo].msgcallout co ON ss.call_id = co.call_id
            INNER JOIN #TempAgent ac ON co.agent_id = ac.agent_id
    WHERE
        arrivaltime BETWEEN '4/1/2014' AND '4/2/2014' AND
        ss.calldirection_id = 1
    ORDER BY
        [Date],
        [Group Name],
        [Agent Last Name],
        [Agent First Name],
        [Start Time],
        [End Time]

For the getUpdatedAgentGroup function, here's the code:

SELECT TOP 1 @AgentGroup = AC.Group_ID
FROM [offlinedbsvr].[PLDTCustomerCare].[dbo].AgentConfiguration AC
WHERE AC.Agent_ID = @AgentID
ORDER BY TIMESTAMP DESC

A very fast shot: do you need the sorting? Especially in your getUpdatedAgentGroup function? This may can be a quickfix. Another issue are indexes, have you them in place?


SQL Data warehouse and normalization forms I don't know if it's a simple question but does a data warehouse have different rules when it comes to normalization forms?

At the moment I am creating some tables in a data warehouse, and they simply do not fulfill the normalization forms rule. I have tried to find a method to fulfill them, but it seems to be pointless.

Actually it is common to denormalise in a data warehouse.

For instance I will  have the full address of customers with the state and the country as columns in my Customer dimension. The data will be duplicated a lot for the customers living in the same city, but I won't have to do two costly joins to retrieve the state and then the country from two other tables.

Most of Data Warehouse are modelled as Star Schemas, where the fact table (the event you want to measure) is directly joined to dimensions (e.g. Time, Customer, Product, ...). It can be a constellation if you have multiple fact tables sharing the same conformed dimensions. You can also have some kind of normalisation where for instance the State and the Country would be in another dimension joined to the Customer dimension, but some reporting tools behave better with a pure Star Schema.


Mysql could not be started because unknown variable 'log-syslog=1' Platform System： WSL（ubuntu 18.04）.

when installed mysql-server 8.0 sucess， i try to start mysql. But it's fail to start.Then checked the error.log.

2019-02-26T03:13:51.609895Z 0 [ERROR] [MY-000067] [Server] unknown variable 'log-syslog=1'.

how to fix it ?

I've run into the same problem as yourself and found this question while looking for solution. Eventually I found the culprit.

You will find that /etc/mysql/mysql.conf.d/mysqld_safe_syslog.cnf contains 2 rows:

[mysqld_safe]
syslog

The only thing you have to do is remove syslog. I just commented it out:

[mysqld_safe]
#syslog

And now it works.
It is weird that it comes preconfigured like that since it is deprecated/removed (I did a clean install of v8.0.15).


how to sum values from subquerys i need to collect some values from multiple tables and sum this values as a column to the result table.
my query looks like this:
SELECT
u.ID as id,
(
    SELECT `meta_value` as user_name FROM `wxe4_usermeta`
    WHERE `umeta_id` = u.ID AND `meta_key` = 'nickname'
) as user_name,
(
    SELECT SUM(rounds) FROM wxe4_170 WHERE user = u.ID
) as a170_score,
(
    select IF (count(*) > 0, count(*)*66, 0) FROM wxe4_aroundtheworld WHERE user = u.ID
) as atw_score,
(
    select IF (count(*) > 0, count(*)*100, 0) FROM wxe4_X100 WHERE user = u.ID
) as x100_score,
SUM(a170_score + atw_score + x100_score) as darts_total_thrown
FROM darts.wxe4_users as u

the sum throws a "Error Code: 1054. Unknown column 'a170_score' in 'field list'" Error.
what i get without sum:
| id | user_name | a170_score | atw_score | x100_score |
--------------------------------------------------------
| 1  | someUser  | 449        | 3102      | 200        |

what i expect with sum:
| id | user_name | a170_score | atw_score | x100_score | darts_total_thrown |
----------------------------------------------------------------------------
| 1  | someUser  | 449        | 3102      | 200        | 3751              |

Why cant i access these values and how to resolve this?

You can't use the new columns that way. use a subquery to use the data to calculate
SELECT
wxe4_usern.*,(wxe4_usern.a170_score + wxe4_usern.atw_score + wxe4_usern.x100_score) as darts_total_thrown
FROM
(SELECT
u.ID as id,
(
    SELECT `meta_value` as user_name FROM `wxe4_usermeta`
    WHERE `umeta_id` = u.ID AND `meta_key` = 'nickname'
) as user_name,
(
    SELECT SUM(rounds) FROM wxe4_170 WHERE user = u.ID
) as a170_score,
(
    select IF (count(*) > 0, count(*)*66, 0) FROM wxe4_aroundtheworld WHERE user = u.ID
) as atw_score,
(
    select IF (count(*) > 0, count(*)*100, 0) FROM wxe4_X100 WHERE user = u.ID
) as x100_score
FROM darts.wxe4_users as u) wxe4_usern


Understanding oracle SCN I am currently working as junior Oracle DBA in a company.Now my team leads always tell me that I have to understand how SCN works and what I can do with the knowledge about SCN.So,are they really correct?
I know SCN is system change number and a little more something about SCN but where can I find the exhaustive documentation and practice material related to SCN?

According to the Glossary from the Oracle Documentation

  System Change Number. A database ordering primitive. The value of an SCN is the logical point in time at which changes are made to a database.

There is lots of info you can easily Google

http://oracledbascriptsfromajith.blogspot.com/2009/05/understanding-scnsystem-change-number.html
http://www.adp-gmbh.ch/ora/concepts/scn.html

Even the DBA StackExchange already has questions around the SCN

How do I find my current SCN?
RMAN-06054: media recovery requesting unknown archived log, Where is SCN kept?
Commit vs Fast Commit vs Commit Cleanout in Oracle Database
more...


Dump pglogical Config to files We use the tool etckeeper to keep track of the configuration of servers.

I would like to dump the configuration of pglogical to files for this purpose.

I tried this:

db@host:~$ pg_dump -t 'pglogical.replication_set'

But the output looks empty:

--
-- PostgreSQL database dump
--

-- Dumped from database version 11.5
-- Dumped by pg_dump version 11.5

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- PostgreSQL database dump complete
--

How can I dump the configuration of pglogical?

Use Case:

dump it hourly and store it in git to see what was changed.
compare two hosts to see if the configuration is equal.

The table belongs to the extension pglogical, so pg_dump doesn't dump it.

Objects that belong to an extension don't get included in the dump, they are subsumed in the CREATE EXTENSION command from a database dump, because that object will be created by the CREATE EXTENSION command. If the object itself got dumped too, restoring the dump would cause an error, because the object already exists after CREATE EXTENSION.

You could use psql and do something like

\copy (SELECT * FROM pglogical.replication_set ORDER BY ...) TO 'some.file' (FORMAT 'csv')


Why does join syntax with stacked on clauses work? I have three versions of the same select query (SQL Fiddle here):

CREATE TABLE MainTable(ID INT)
CREATE TABLE SubTable(ID INT, Type CHAR(2))
CREATE TABLE SubTableType(Type CHAR(2))

INSERT MainTable VALUES (1), (2)
INSERT SubTable VALUES (1, 'AB'), (2, 'AB'), (3, 'CD')
INSERT SubTableType VALUES ('AB')

SELECT  *
FROM    MainTable P
JOIN    SubTable L
ON  P.ID = L.ID
JOIN    SubTableType LT
ON  L.Type = LT.Type

SELECT  *
FROM    MainTable P
JOIN    SubTable L
JOIN    SubTableType LT
ON  L.Type = LT.Type
ON  P.ID = L.ID

SELECT  *
FROM    MainTable P
JOIN    SubTable L
JOIN    SubTableType LT
ON  P.ID = L.ID
ON  L.Type = LT.Type

The first two select statements work and produce the same result. The third reports the error,

  The multi-part identifier "P.ID" could not be bound.

This question appears to claim that the first two queries are semantically equivalent, though they may perform differently. My question is rather about the syntax. I've always written joins according to the first pattern, and I didn't even realize that the second pattern was permissible. Clearly it is, but then why isn't the third pattern also acceptable? The main documentation doesn't seem to touch on this, so I'm trying to find documentation that spells out what is and isn't permissible.

The ON clause location determines join order. You can do this with parentheses, which makes it much clearer:

SELECT  *
FROM    MainTable P
        INNER JOIN
          ( SubTable L
            INNER JOIN
            SubTableType LT
            ON  L.Type = LT.Type
          )
        ON  P.ID = L.ID

You can now just omit the parentheses, and you come up with your second query which forces L JOIN LT to be performed first, followed by the join to P.

The error you are getting is because your 3rd query doesn't preserve the Chiastic Order. Look at it with the parentheses, and it will become clear:

SELECT  *
FROM    MainTable P
        INNER JOIN
        ( SubTable L
          INNER JOIN
           SubTableType LT
           ON  P.ID = L.ID --<<< P is not a valid identifier here
         )
        ON  L.Type = LT.Type

My recommendation - if you need to force join order, always use the parentheses, and proper indentation of the code, which will make this immediately visible and easy to understand.

Since you are only using INNER JOINs, order doesn't matter, and the engine is free to choose to perform the join in any order it sees fit. When OUTER JOINs are involved, the order does matter, so you need to be able to force which tables you want joined first.


NLS_LANG AL32UTF8/UTF8 for Oracle database I recently installed a new Oracle database on Ubuntu using this tutorial.

I got an error

oracle@ubuntu:/home/bruno$ echo $NLS_LANG
/u01/app/oracle/product/11.2.0/xe/bin/nls_lang.sh
oracle@ubuntu:/home/bruno$ /u01/app/oracle/product/11.2.0/xe/bin/nls_lang.sh
AMERICAN_AMERICA.AL32UTF8
oracle@ubuntu:/home/bruno$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Mar 17 11:00:45 2016
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
ERROR:
ORA-12705: Cannot access NLS data files or invalid environment specified
Enter user-name:

After searching, I found a possible solution:

oracle@ubuntu:/home/bruno$ export NLS_LANG=AMERICAN_AMERICA.UTF8
oracle@ubuntu:/home/bruno$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Mar 17 11:03:15 2016
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>

What is the most elegant way to correct this error?

Answer

export NLS_LANG = $($ORACLE_HOME/bin/nls_lang.sh)

In the original tutorial:

export NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`

Notice the backticks. The value of NLS_LANG variable should be the output of the above command, not the file name with absolute path. Just set the value of NLS_LANG, as you already did:

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8


Should I restart the instance after adjusting Memory Allocation and MAXDOP? As far as I know adjusting Maximum server memory and Max Degree of Parallelism does not require the instance to be restarted. Will new settings always come into effect without restart?

Both of those server configuration options do not require a restart of the database engine.  Running RECONFIGURE will be sufficient in this case.

exec sp_configure 'show advanced options', 1
go
reconfigure
go

exec sp_configure 'max server memory', 12288
go
exec sp_configure 'max degree of parallelism', 4
go

reconfigure
go

-- the new configuration has taken effect

Please see BOL for reference.