texts: Evaluate a string value as a computed expression in an SQL statement (sthg like EVAL)

texts:
Is it possible to evaluate a string value as a computed expression in an SQL statement like EVAL in some programming languages ?

>>> EDIT 4 : more precisely : an expression that contains a list of numbers separated by an arithmetic operator... <<<

For example :
Table A :

  CREATE TABLE A (
    id INT NOT NULL PRIMARY KEY,
    note INT,
    detailNote VARCHAR(20)
  );

insert a row :

    INSERT INTO A (id, note, detailNote)
      VALUES (1,20,"2+4+2+2+2+0+2+0+2");

I would like to be able to update 'note' column with the value of the evaluation of 'detailNote' column such as :

    UPDATE A
      SET note = CAST(detailNote AS UNSIGNED);

or :

    UPDATE A
      SET note = 1*(detailNote);

These 2 trials produce an error : "truncated incorrect integer/double value ..."

Many thanks for any help !

>>> EDIT. 1 <<<
I am aware that the DB model is NOT the right one; it should be :

  CREATE TABLE A (
    id INT NOT NULL PRIMARY KEY,
    note INT
  );
  CREATE TABLE B (
    id INT NOT NULL REFERENCES A(id),
    seq INT NOT NULL,
    detNote INT,
    PRIMARY KEY (id, seq)
  );
    INSERT INTO A (id, note)
      VALUES (1,20);
    INSERT INTO B (id, seq, detNote)
      VALUES (1,1,2);
    INSERT INTO B (id, seq, detNote)
      VALUES (1,2,4);
    INSERT INTO B (id, seq, detNote)
      VALUES (1,3,2);

etc.
and the update query could be :

    UPDATE A
      SET note = (SELECT SUM(detNote) FROM B WHERE A.id = B.id);

But, the use of the string is more compact and the content is sure.

>>> EDIT. 2 <<<
1st solution (bad one, but exists...) : (because I am sure of integer (0-9) position :

    SELECT (SUBSTR(detailNote,1,1) +SUBSTR(detailNote,3,1)
      + SUBSTR(detailNote,5,1) +SUBSTR(detailNote,7,1)
      + SUBSTR(detailNote,9,1) +SUBSTR(detailNote,11,1)
      + SUBSTR(detailNote,13,1) +SUBSTR(detailNote,15,1)
      + SUBSTR(detailNote,17,1) +SUBSTR(detailNote,19,1))
    FROM A;

>>> EDIT. 3 <<<
one could tell me to add 10 INT fields in A...but one string is easier to manipulate...


texts:
I am trying to extract data from tables in a delimiter separated value format. To extract data i am concatenating all the columns of the table with each other and then converting the concatenated record into binary and  storing into a table. Below is the code that i am using

DECLARE
l_temp_blob blob;
begin
dbms_lob.createtemporary(l_temp_blob, TRUE);
For src in (select A||'~'||B||'~'||C||..........||'`'||Z AS DAT FROM <TABLE>)
LOOP
dbms_lob.append(l_temp_blob,utl_raw.cast_to_raw(src.DAT||chr(10)));
END LOOP;
insert into temp_table('FIle',l_temp_blob);
COMMIT;
END;

I am facing severe performance issue in this, a table with 88k records is getting extracted in 8 hours. My tables have CLOB columns in them along with VARCHAR2 and NUMBER columns.

Is there a better way to do this. Since we don't have direct access to server where database is installed we cannot use Oracle Export utility and Data Pump utility.


texts: Is it a good practice to build in as many foreign keys as possible?

texts:
For example, there are three tables: players, dungeons, and instances.

A player has many dungeons and each dungeon has many instances, like in a well-known game of World of Warcraft. Each player would have a dungeon_id as a foreign key and each dungeon would have an instance_id as a foreign key. That would be a sufficient minimalist approach. A player of each instance can be accessed through the dungeon table. This would result in 1 less column, but a bit longer query.

Another approach is to add player_id to instances table as a foreign key. Now players can queried directly from instances table. Shorter query, but one more column for a foreign key.

Which approach is better in the long run? In practice it seems like adding a foreign key everywhere where it is logically possible makes it easier. But then wouldn't it result in tighter coupling and make code harder to refactor?


texts:
I have a very large table (1.9 million records) that currently has a unique index. I need to change that unique index to a non-unique index.

Because I am trying to avoid an "outage" from this activity, I am considering taking this clever approach to creating the new non-unique index. Creating the index in this way means that a working index is always available to benefit queries of that table--this benefit solves one of the arguments for an outage.

What is not clear to me is what adverse affects I am risking by building this index without restricting updates/inserts on the table. If those risks are negligible, that would remove the remaining need for an outage.

What complications arise from edits to a table receiving a new index?


texts:
I'm running Neo4j v4.x on an Azure VM (Linux Ubunto 16.04). Everything was running fine until my project required a resizing for more storage. This seemingly went well as I proceeded to load data, but then a glitch of unknown type caused the Neo4j server to stop. I could no longer access it from the Neo4j broweser or from python code that queries it. I've restarted the VM. No effect. Even redeployed the VM without effect. From Putty:
neo4j status
Neo4j is not running

I've tried numerous commands, but this one comes closest to trying to do something:
sudo ssh {myusername}@{vm ip} "systemctl restart neo4j V4_0.service"

It asked for my password and accepted it (rejected errors) and then gave me

Failed to restart neo4j.service: Interactive authentication required.
See system logs and 'systemctl status neo4j.service' for details.
Failed to restart V4_0.service: Interactive authentication required.
See system logs and 'systemctl status V4_0.service' for details.

I thought I'd authenicated. In debugging, i tried entering bad username or password and was immediately rejected as expected.
Another set of info generated by the command
root@neo4jVM:/etc/init.d# neo4j start

result:

Directories in use:
home:         /var/lib/neo4j
config:  /etc/neo4j
logs:         /var/log/neo4j
plugins:   /var/lib/neo4 j/plugins
import:       /var/lib/neo4j/import
data:   /var/lib/neo4j/data
certificates: /var/lib/neo4j/certificates
run: /var/run/neo4j Starting Neo4j.
WARNING: Max 1024 open files allowed,minimum of 40000 recommended. See the Neo4j manual. Started neo4j (pid> 71995). It is available at http://0.0.0.0:7474/ There may be a shortdelay until the server is ready. See /var/log/neo4j/neo4j.log for current status.

Despite the promising message, it never started and Http://0.0.0.0:7474 did not connect.
for clarification, I tried variations on the one command in Putty: sudo ssh dastumpf@104.43.228.191 "systemctl restart neo4j" sudo ssh dastumpf@104.43.228.191 "systemctl restart neo4j.service" etc.
Exploring the Neo4j log, I found this ....LifecycleManagingDatabaseService@78116659' was successfully initialized, but failed to start.
What am I missing?


texts: Oracle DBCA utility: "Listener is not up or database service is not registered with it"

texts:
I'm a software engineer with some experience on SQL Server but a newbie on Oracle.

I'm attempting to run dbca on a remote linux machine to reconfigure a database that was initially configured to idle with zero memory (so I'm told). Enterprise Manager is not configured properly and my listener.ora is as follows:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS =
        (PROTOCOL = TCP)
        (HOST = Myhost)
        (PORT = 1521)
      )
    )
  )

This database is not on port 1521, it's on port 15321. I can connect to it using SQL Developer by manually creating a connection with the correct port, etc.

I get the error in the title after I press finish in dbca. There is also a log file that is very long. How do I proceed to fix dbca? Ultimately I need to configure Enterprise Manager.


texts:
Starting point at the sandbox sb1, sb2, sb3:
All boxes identical.
A Fresh debian 9 install
mariadb 10.2
Galera3
build a test galera cluster with empty databases works fine.
All three nodes come up and work as expected.
then after stopping the test cluster
copy a LVM snapshot from a production mariadb server over to sb1
start mariadb server with standalone config to see if all databases and tables are correct.
(config I took from the server the data come from)
shutdown and restart the standalone to see if there is a problem.
All is fine, server starts and stops as expected.
Then change configs to fit galera
try to bootstrap with
> galera_new_cluster
and that
https://pastebin.com/e5jm4Tec
is what happens.
my.cnf
[mysqld]
#mysql settings
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_size=0
query_cache_type=0
bind-address=0.0.0.0

max_connections=700
log-error=/var/log/mysql/error.log

#------------------------------------------------------------
#galera settings
#------------------------------------------------------------

wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_provider_options="gcache.size=512M"

datadir = /var/lib/mysql
wsrep_sst_method=rsync
#wsrep_sst_method = xtrabackup-v2

wsrep_cluster_name="tam_cluster"
 wsrep_cluster_address="gcomm://162.255.86.161,74.208.250.175,162.255.86.145,93.115.26.72"

#for each machine different
wsrep_node_address="162.255.86.145"
wsrep_node_name="node_5"

!includedir /etc/mysql/conf.d/

someone any idea what's going wrong?


texts: What is the meaning of & in where clause

texts:
What does & indicate in below query? What is the meaning of it?

select id, name
from test
where num & 1 = 1;


texts: the role of " Intent lock " in transaction and concurrency

texts:
According to the book Microsoft Sql server 2012, Chapter 13 (Transactions and concurrency):

  If an exclusive page lock is held in one page of a table, another
  process can not get even a shared table lock for that table. This
  hierarchy is protected using intent locks. A process acquiring an
  exclusive page lock, update page lock or intent exclusive page lock
  first acquires an intent exclusive lock on that table. This intent
  exclusive table lock prevents another process from acquiring the
  shared table lock on that table.

And according to another paragraph of this book:

  ... lock compatibility is an issue only when the locks affect the same
  object. For example two or more process can hold exclusive page locks
  simultaneously, as long as the locks are on different pages.

I think these two paragraphs are in contradiction with each other, because in the first one it is said that if there is an exclusive lock on a page a table, other type of locks can't be granted on the whole table to other processes In the second paragraph it is said that it can be granted if it is on a different page.

Which one is true?


texts: Does visual studio (2015 or above) needs to be installed in a server in order to transfer/create SSIS packages there?

texts: At work we have an old server and we're moving everything from there to our new server. We're a very small team, and one of my team-members says without visual studio 2015 in the new server he can not transfer/create SSIS packages there. Any thoughts on this?


texts: Are there reasons to not use SCHEMABINDING on database objects?

texts:
Not using schema binding seems like turning off compiler warnings - you don't find out you broke something until runtime.

The benefit is that schema bound objects protects the database from accidental or deliberate changes that could break dependencies later.

The cost of the protection is that maintenance is more complicated because schema bound objects sometimes require altering multiple objects in a particular order to achieve what would otherwise be a simple localized change. This cost is relative since without the schema binding a change could silently break something that requires more maintenance later.

I think the benefit outweighs the cost, especially for established databases that aren't changing much (less changes so less maintenance) or where you don't know the dependencies (less familiar so more risk).

Are there situations where schema binding shouldn't be used?


texts: Elastic queries with encrypted columns in tables using symmetric keys

texts:
I have elastic queries running cross-subscription for everything except tables with encryption.

Now I need to tackle tables that are encrypted.

How (if at all possible) do I open the keys on the remote server?
i.e.

  OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert;

Do I somehow need to bring the cert and key over to the database where I'm running the query?


texts: Waiting for table level lock on wordpress site

texts:
We are experiencing issues with mySQL loading 100% of the server's CPU. While checking the running processes we found many of the following:

Waiting for table level lock | SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'

The table is configured to MyISAM.

Any suggestion of how to optimize mysql?


texts:
I'm unclear about the true meaning in the definitions for IMMUTABLE, VOLATILE and STABLE functions.

I read the documentation, specifically the definitions of each.

  IMMUTABLE indicates that the function cannot modify the database and
  always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use
  information not directly present in its argument list. If this option
  is given, any call of the function with all-constant arguments can be
  immediately replaced with the function value.

  STABLE indicates that the function cannot modify the database, and
  that within a single table scan it will consistently return the same
  result for the same argument values, but that its result could change
  across SQL statements. This is the appropriate selection for functions
  whose results depend on database lookups, parameter variables (such as
  the current time zone), etc. (It is inappropriate for AFTER triggers
  that wish to query rows modified by the current command.) Also note
  that the current_timestamp family of functions qualify as stable,
  since their values do not change within a transaction.

  VOLATILE indicates that the function value can change even within a
  single table scan, so no optimizations can be made. Relatively few
  database functions are volatile in this sense; some examples are
  random(), currval(), timeofday(). But note that any function that has
  side-effects must be classified volatile, even if its result is quite
  predictable, to prevent calls from being optimized away; an example is
  setval().

My confusion comes in with the condition for IMMUTABLE and STABLE that the function ALWAYS or CONSISTENTLY returns the same result given the same arguments.

The IMMUTABLE definition states that the function does not database lookups or otherwise use information not directly present in its argument list.  So, to me, that means such functions are used to manipulate data provided by the client, and should not have SELECT statements...although that just sounds a bit odd to me.

With STABLE, the definition is similar in that it says it should consistently return the same result.  So, to me, that means that everytime the function is called with the same arguments, it should return the same results (same exact rows, every single time).

So, to me...that means that any function that performs a SELECT on a table or tables that can be updated, should only be volatile.

But, again...that doesn't sound right to me.

Bringing this back to my use-case, I am writing functions that perform SELECT statements with multiple JOIN's on tables that are constantly being added to, so the function calls would be expected to return different results each time it's called, even with the same arguments.

So, does that mean that my functions should be VOLATILE?  Even though the documentation indicates relatively few database functions are volatile in this sense?

Thank you!


texts: Wrapping code in function is causing a huge delay

texts:
I have the following function:

CREATE FUNCTION [dbo].[FunctionScore]
(
     @Param1
     ...
    ,@ParamN
)
RETURNS TABLE
RETURN
(
    SELECT T1.[Col001]
          ,SUM(ISNULL(F1.[Pts], T2.[Pts])) AS [Pts]
          ,SUM(ISNULL(F1.[PtsOf], T2.[PtsOf])) AS [PtsOf]
    FROM [dbo].[Table01] T1
    INNER JOIN [dbo].[Table02] T2
        ON T1.[Col001] = T2.[Col001]
    LEFT JOIN [dbo].[Function01](@Param1, ... , @ParamN) AS F1
        ON T1.[Col001] = F1.[Col001]
    GROUP BY T1.[Col001]
)
GO

Its body statement is executed for 0 seconds (with given parameters).
The function itself (with the same parameter is never executed - I was waiting for 20 minutes and only part of the result was returned).

There must be something wrong with the statistics and respectively the query execution plan that is created because the function is executed instantly:

If I change the LEFT JOIN to LEFT MERGE JOIN
If I comment the SUM clauses and return only T1.[Col001]

The [dbo].[Functuin01] inline table value function.

Could anyone tell how can I debug what is causing the delay?


texts: How to use a column's latest date in query?

texts:
SELECT
smart_allusers_karen.lastlogin,
smart_allusers_karen.supemail,
smart_allusers_karen.regionname
FROM
smart_allusers_karen
WHERE lastlogin < MAX(smart_allusers_karen.lastlogin) - INTERVAL 30 DAY AND
      lastlogin > MAX(smart_allusers_karen.lastlogin) - INTERVAL 60 DAY
ORDER BY lastlogin

Getting error when trying to get the MAX date.  Also not sure if this is the fastest way to do this.


texts: Reporting Services TempDB lock blocking report generation

texts:
Two days ago one of our reports in Reporting Services started to take much longer to generate.  Its generation time had increased from 4 seconds to 2 minutes.

Investigation found:

The Stored Procedure ran in 1 second and the query plan was acceptable
The report ran as expected in 4 seconds on a dev machine in Visual
Studio

As soon as the report was published to the production server it ran much, much slower.

Using Activity monitor showed that the report was being blocked with a lock "LCK_M_S"

I turned on trace 1222 to check for deadlocks but no deadlocks were reported.

The only information I could get on what was going on was by the report "Top Transactions by Locks Count" for the ReportServer database.  It listed locking transactions on the ReportServerTempDB.

This pattern was repeatable with the affected report for two days, you would run it, see the blocking process in the Activity monitor and the report taking the much longer 2 minutes.

Just as we were looking further into the problem, it suddenly disappeared, nobody in the team had changed anything, code or data, and now the report is back to running in 3-4 seconds.

What happened?

Without dev intervention the report which was repeatedly taking 2 minutes to run and had been for 2-3 days suddenly returns to running in 4 seconds and the blocking process seen in Activity monitor is gone?


texts: Does Resource Governor help protect page life expectancy?

texts:
I have several large tables that I need to copy into partitioned copies on a transactional database.

I am worried about the hit that this will cause to performance of my production applications.

In an attempt to mitigate this, I am looking into Resource Governor.  I am going to set my script to use a resource pool that is limited to a max of 5% CPU and Memory.

The only part about this that I am not sure of is the effect on PLE.

As I understand it, the select from the table will read the value in to the cache.  This will force out something else that was in the cache. (The basic concept of PLE.)  But will the "forced out" items be limited to the 5% I have specified?

Meaning after I have used the 5% of the memory, will it only force out cached items in the same 5%? (Leaving the other 95% unaffected.)

Another way of asking this is: Is the memory limit of 5% the same as saying a cache limit of 5%?


texts: #1215 - Cannot add foreign key constraint ERROR even though the columns are exactly the same

texts:
Last night I ran SQL commands in phpMyAdmin very similar to the following to setup a players table and a games table linked by a foreign key.  It worked without errors, making google_id in my games table a foreign key.

CREATE TABLE players(
    google_id VARCHAR(36) PRIMARY KEY,
    display_name VARCHAR(255) NOT NULL,
    given_name VARCHAR(255) NOT NULL,
    family_name VARCHAR(255) NOT NULL,
    picture_url VARCHAR(2083) NOT NULL,
    leaderboard_initials VARCHAR(40),
    operating_system VARCHAR(255) NOT NULL,
    modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE games(
    game_id VARCHAR(255) PRIMARY KEY,
    google_id VARCHAR(36) NOT NULL,
    won INT(1) NOT NULL,
    score INT(10) NOT NULL,
    num_in_code INT(2) NOT NULL,
    num_colours INT(2) NOT NULL,
    num_guesses INT(2) NOT NULL,
    turn INT(2) NOT NULL,
    rank INT(10) NOT NULL,
    modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Then make the google_id a foreign key in games:

ALTER TABLE
    games ADD FOREIGN KEY(google_id) REFERENCES role(google_id);

Today the third command gave the following error:

Error
SQL query:

ALTER TABLE
games ADD FOREIGN KEY(google_id) REFERENCES role(google_id)
MySQL said:

#1215 - Cannot add foreign key constraint

AFAIK my google_id is exactly the same data type so this answer hasn't helped me

I've also read this answer but can't see anything wrong with my three queries.

Can anybody spot my mistake?

EDIT: Sorry people, this was a typo.  role should have been players


texts:
I have a table with 2 columns. Days is the days from Start Date to today.

How should Trigger be to automatically increase Days every day?

Start Date      Days
19/07/17         19
25/07/17         13
07/08/17         0


texts: Is there a way to shrink a Log File in SQL Server without affecting Point in Time Recovery?

texts:
My friend (who doesn't use stackoverflow) asked me a peculiar and interesting question just now.

Is there a way to "disconnect a Log file, or shrink a log file without affecting Point in Time Recovery"? as I quote his exact words. He is working against a live system, and somehow, the Log file has grown to 72GB

All I know is you can Delete a log file (as indicated in the snippet below from technet)

USE master;
GO
ALTER DATABASE AdventureWorks2012
REMOVE FILE test1dat4;
GO

And Truncate it

DBCC SHRINKFILE(<log_file_name_Log>)
BACKUP LOG <database> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<log_file_name_Log>)

I am no SQL Server wiz, and from my experience which I have, I cannot help him - though this is something I want to know the answer myself -

Any advice?


texts: Good SQL Agent job wrapper... TRY/CATCH, Custom logging. (Review and provide input)

texts:
I'm looking for input on a small snippet of code i wrote. We use SQL Agent jobs to prepare big databases from production, before we ship the to test-environments. To get better feel for duration of the various truncate, index-rebuilds, anonymization in the many agent job steps, i've been looking at logging each job step to a log table. I've created a "Start log event" SP, which returns the PK INT of the logevent. And a "Complete log event" SP, which updates timestamps and returnCodes.

In order to grab the possible errors, i'm introducing TRY/CATCH, and are THROWing it afterwards, to make errors manageable by SQL Agent browser.

I also want it to dynamically grab the agent job name and step name, to make it really re-usable.

Below you can find an example of a stepID with this wrapper.

What do you think? input and ideas are appreciated.
(Yes, truncate table will fail in snippet.. its on purpose)

DECLARE @id INT                     -- ID variable used for the RestoreCommandLog returned ID
DECLARE @ErrNumber INT              -- Error Number code if exception is thrown
DECLARE @ErrMessage NVARCHAR(2048)  -- Error message text for human interactions
DECLARE @job_Name NVARCHAR(255)     -- SQL Agent job name
DECLARE @Step_Name NVARCHAR(255)    -- SQL Agent job step name
DECLARE @Command NVARCHAR(MAX)      -- SQL Statement associated with

-- Get Sql Agent job info
SELECT  @job_Name = CAST(t1.name AS NVARCHAR(255)), @Step_Name =  CAST(t2.step_name AS NVARCHAR(255)),  @Command = t2.command FROM msdb.dbo.sysjobs t1 INNER JOIN msdb.dbo.sysjobsteps t2 ON t1.job_id = t2.job_id AND t2.step_id = $(ESCAPE_NONE(STEPID))  AND t2.job_id = $(ESCAPE_NONE(JOBID))

/****************************************
            --- MAIN ---
****************************************/
IF EXISTS (SELECT name FROM master.sys.databases WHERE name = N'<my database>' and state_desc = 'ONLINE')
BEGIN
    BEGIN TRY
        EXEC @id = [Operation].[dbo].sp_AddRestoreCommandLogEvent @CommandName = @job_Name, @CommandMessage = @Step_Name, @sqlstring = @Command

        /***    DO YOUR STUFF HERE  ***/
        USE <my_database>
        EXEC sp_MSforeachtable 'TRUNCATE TABLE --?'
        EXEC [Operation].[dbo].sp_CompleteRestoreCommandLogEvent @EventId = @id, @ReturnCode = '0', @ErrorMessage = 'Success'

    END TRY
    BEGIN CATCH
        SELECT @ErrNumber = ERROR_NUMBER(), @ErrMessage = ERROR_MESSAGE()
        EXEC [Operation].[dbo].sp_CompleteRestoreCommandLogEvent @EventId = @id, @ReturnCode = @ErrNumber, @ErrorMessage = @ErrMessage;
        THROW
    END CATCH;
END
ELSE
BEGIN
    EXEC [Operation].[dbo].sp_AddRestoreCommandLogEvent @CommandName = @job_Name, @CommandMessage = @Step_Name, @sqlstring = @Command, @ReturnCode = '-1', @ErrorMessage = 'Warning - object is this step was not found, skipping logic'
END


texts: Bash Script gives error in a loop passing thru multiple Mysqldump

texts:
The script

I'm experiencing an issue with this bash script:

dump_all_dbs(){
    $DOMAIN="mysql.domain.com"
    $DBS_FOLDER_BK="$HOME/_backup/"
    $DATA=("'1' 'dbname' 'dbuser' 'dbpassword' 'Yes'" "'2' 'dbname2' 'dbuser2' 'dbpassword2' 'Yes'")

    for i in "${DATA[@]}"; do

            i=($i)

            # if 'Yes' do it if 'No' skip it
            CHECK="${i[4]}"

            if [ $CHECK == "'Yes'" ];then

                # Extract the var from DATA
                ID=${i[0]}
                DBNAME=${i[1]}
                DBUSR=${i[2]}
                DBPSW=${i[3]}

                # Clean from ' "
                ID="$(tr -d "\"\`'" <<<$ID)"
                DBNAME="$(tr -d "\"\`'" <<<$DBNAME)"
                DBUSR="$(tr -d "\"\`'" <<<$DBUSR)"

                # Go to the backup folder
                cd "$DB_FOLDER_BK"

                # Mysqldump
                mysqldump -v -P3306 -h"$DOMAIN" -u"$DBUSR" -p"$DBPSW" "$DBNAME" | gzip -c > "$DBNAME".gz
            else
                # Is 'No' just skip
                continue
            fi
    done
}

dump_all_dbs_echoed(){
        mysqldump -v -P3306 -hmysql.domain.com -udbusername0 -p'password0' dbname0 | gzip -c > dbname0.gz
        mysqldump -v -P3306 -hmysql.domain.com -udbusername1 -p'password1' dbname1 | gzip -c > dbname1.gz
        mysqldump -v -P3306 -hmysql.domain.com -udbusername2 -p'password2' dbname2 | gzip -c > dbname2.gz
        mysqldump -v -P3306 -hmysql.domain.com -udbusername3 -p'password3' dbname3 | gzip -c > dbname3.gz
}

(NOTE: the password params in the original script was -p not -password,
echoed mysqldump with -password'password1'does not work)

Excluding the big security issue in storing in plain text password and usernames.

The question

I would like to ask why if I'm running the script I receive this error:

mysqldump: Got error: 1045: Access denied for user 'dbuser'@'myDSLconnection.domain.com' (using password: YES) when trying to connect

but if I run the single the mysqldump one by one via terminal or  "dumps_all_dbs_echoed" everything works perfectly (I did it echoing the params of the mysqldump and copy paste them)

anyone have an idea?

Security Issue

how people treat the issue of store password or hashes?
I've tried pgp the file, but it seems not the right way for a chron process.


texts: Migrate SQL Server from 2012 to 2014 (11 to 12)

texts:
I have just installed SQL Server Management studio 2014 and I also need to update my (local) server to accept a new database via a bak file from a version 12 server. When I look in Server Properties I see my server has Version 11.0.5858.0 (which is 2012 sp2).

How do I upgrade/migrate my server to version 12?

I've looked at a couple of links: Upgrade Database Engine, Upgrade to SQL Server 2014 Using the Installation Wizard (Setup). I've also seen suggestions that I need to backup and restore databases, which I assume would mean creating a new local (I forgotten how to do this, to tell the truth) and moving them there?


texts: Active Directory group permissions not being applied to user

texts: I have an odd situation. An Active Directory group was recently created for the sole purpose of being a securable in SQL Server. I created a login based on the AD group, a user based on the login, and granted permissions to the user. All pretty standard stuff. When I impersonate one of the group members (using execute as login='domain\username'), I'm able to perform the granted action. However, when the user himself tries it, he gets "permission denied". I'm at a loss to explain the behavior and subsequently fix it.


texts: SQL Server - How does contained database user password expiration work?

texts:
I've recently started working with contained databases and I do not understand if/how contained database users have passwords that expire.

MSDN documentation says they do - With a SQL Server login, there's the option box to Enforce Password Expiration but this does not exist (as far as I can tell) for contained database users.

These contained database accounts have been around for a while and our domain policy is to reset passwords every 6 months, but we have never had to change these passwords, and the applications are connecting just fine using the same password for the past couple of years.

My first question is how do I know that "Password Expiration" is being enforced for contained database users? With SQL Logins there's a is_expiration_checked column in sys.sql_logins.

With traditional SQL logins I can use following function to get the time a SQL Server login was changed

LOGINPROPERTY('login','PasswordLastSetTime')

Is there something similar for contained database users?


texts:
If I create a new schema and set the schema owner to another schema, for example dbo, what is the meaning of that in regards to my new schema?

Does my new schema inherit the same permissions from it's owner dbo?


texts: How can I alter the fill factor of an index outside of an ALTER INDEX command?

texts: I am using a COTS package to perform index rebuilds across my SQL Server portfolio. The package unfortunately doesn't have an option to set index fill factors globally for an individual instance or DB (I can set them when scheduling rebuilds on an index-by-index basis, but with 1,600+ indexes, I'd like to set them in advance in bulk). Outside of an ALTER INDEX command, is there a way I can redefine existing index fill factors?


texts: ERD Diagram based on Crow's Foot model for Milky Way Jewelry

texts:
this was on my final exam this past winter exam season, in search for the answer since i got it wrong:

  Draw an ERD diagram based on the Crow's Foot model for a database to support the basic operations of “Milky Way Jewelry”.

Specify:

Entity sets, their attributes, and primary keys
Relationship sets and their descriptive attributes (if such exist)
Connectivities and cardinalities
Decompose all M-M relationships, if any
If found, replace any multivalued attributes and composite attributes

Each piece of jewelry for sale is assigned a unique number, and is described by the name, sale price and a style.

Each piece of jewelry can be made from one or more material, e.g. white gold and diamond. The same material can be used to make many pieces of jewelry. The *materials used** and their amount for each piece must be recorded in the database.

Materials are described by material code, title and feature. A material can be a mixture of other materials. Then it is necessary to record the composition of the material including the percentage of each mixed material.
The same material can be used to make many kinds of mixtures.

For each piece of jewelry it is necessary to record the artist who created the piece of art. Information about artists is to be recorded in the database. It includes artist ID, name, address and phone number. One artist can create many pieces of jewelry. Each piece of jewelry can be only made by one artist.


texts: How to update Status using IF Condition in Mysql trigger

texts:
I'm trying to write an update trigger that will Design_Update_Status to Customized when a date is entered in Custom_Design_Update_Date field and if nothing is entered that field it should update Design_Update_Status to Standard.

As of now i am using a trigger  and this doesnot update the status correctly.

IF NEW.Custom_Design_Update_Date = ''
THEN
SET NEW.Design_Update_Status = 'STANDARD';
else
SET NEW.Design_Update_Status = 'CUSTOMIZED';
END IF;

tried a lot in this, cant figure out a solution. help needed in this regard.

Thanks,
Acube.


texts: Will altering a column name in a MySQL master be replicated to the slave

texts:
I have a replicated MySQL database that until recently was supported by a DBA. Unfortunately, he has now left so we're playing catch up.

From what I can tell the replication that is set up is:

binary_log based (i.e. not GTID)
row based
the table being altered is InnoDB

I'm basing this on a few things

The output from SHOW SLAVE STATUS references the binary log
The mysql.gtid_executed table on the master and slave are both empty
The output from SHOW GLOBAL VARIABLES LIKE 'binlog_format'; is ROW

The master server is using MySQL version 5.7.9. The slave is using MySQL version 5.7.17

If I change a column name in the master database using the command

ALTER TABLE table_name CHANGE COLUMN old_column_name new_column_name DATETIME NOT NULL

will the change be replicated to the slave?

Or do I need to change the column name in the slave as well?


texts: Logshipping from SQL Enterprise 2012 SP1 to 2012 SP3?

texts:
I have a requirement to setup a warm standby from an SQL Enterprise 2012 SP1 server. I can only use log shipping.

I understand that versions have to be the same for the STANDBY option to work.

Question is - can I restore from SQL 2012 SP1 to 2012 SP3 and use standby mode? Or is that counted as a different version?

And if so, can I log ship from enterprise to standard for example?


texts:
This is what i've tried

UPDATE (SELECT * FROM music GROUP BY songalbum)
SET albumID = (SELECT albumID FROM albumList GROUP BY album )
WHERE songalbum = albumList.album

this give the error #1248 - Every derived table must have its own alias

UPDATE music
SET albumID = (SELECT albumID FROM albumList )
WHERE albumID = NULL

UPDATE music
SET albumID = (SELECT albumID FROM albumList )
WHERE albumID = NULL
  AND songalbum = (select album FROM albumList)

both run but have no affected rows.

I am trying to populate the albumID in the music table with the albumIDs that are in the albumList table.


texts: Postgres - Only allow insert if TWO columns match with a separate table

texts:
I have three tables: a users table, a books table, and a chapters table.

Each book has an id called identifier_id (for good business reasons that I cannot work around). This id is guaranteed to be unique per-user, i.e. no user will ever have two books with the same id. It is not, however, guaranteed to be globally unique. Each book also has a foreign key user_id.

Each chapter has user_id and book_id foreign keys - the book_id foreign key points to identifier_id on the books table.

When I add chapters to a book, I want to add a database-level contraint that a chapter insert will be rejected unless the user_id of the chapter matches the user_id of the book AND the book_id of the chapter matches a book_id in the books table.

Because the book_id is not globally unique, I cannot put a unique index on it. Is there a way to do this?


texts:
We have Oracle 11gR1 RAC two instances on two different servers, new undo tablespace UNDOTBS20140508 created 30G, I want to remove the old one UNDOTBSX02, I did the following:

CREATE UNDO TABLESPACE UNDOTBS20140508  DATAFILE '+DATA/....../UNDOTBS20140508' SIZE 20G;
ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS20140508;
DROP TABLESPACE UNDOTBSX02 INCLUDING CONTENTS AND DATAFILES;

When trying to drop the UNDOTBSX02 it show the following error:

Error starting at line : 13 in command -
DROP TABLESPACE UNDOTBSX02 INCLUDING CONTENTS AND DATAFILES
Error report -
SQL Error: ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 57
ORA-30013: undo tablespace 'UNDOTBSX02' is currently in use
00604. 00000 -  "error occurred at recursive SQL level %s"
*Cause:    An error occurred while processing a recursive SQL statement
           (a statement applying to internal dictionary tables).
*Action:   If the situation described in the next error on the stack
           can be corrected, do so; otherwise contact Oracle Support.

I checked UNDOTBSX02 for any pending transaction and it is empty.
So how to drop it?

Note : I am not Oracle DBA, but I have to do this task.


texts: Can I use symlink or data directory for ARCHIVE MySQL tables?

texts:
All mention of either using a symlink or data/index separate data directories state that MyISAM is the only supported (https://dev.mysql.com/doc/refman/5.5/en/symbolic-links.html).  However, I found this bug report (http://bugs.mysql.com/bug.php?id=49808), that states "only supported for MyISAM and ARCHIVE tables".  This also may be just for partitioning.

I believe just "testing" this is not an option, because the MySQL documentation mysteriously states "For files used by tables for other storage engines, you may get strange problems if you try to use symbolic links. "

Does the ARCHIVE storage engine support symlink or separate data directories?

UPDATE: Related question


texts:
I have created a sample database called TestDB. After that, I ran this select:

select * from sys.master_files

I found my .MDF, .LDF files from this select. Then I want to find out the .NDF file location . Is it possible to see those files like .NDF, .MDF , .LDF?


texts: Can we Link sql windows server with oracle unix server?

texts:
while inserting data through linked server it is happening but when i  execute the stored procedure in getting error like

OLE DB provider "OraOLEDB.Oracle" for linked server "server ip" returned message "New transaction cannot enlist in the specified transaction coordinator. ".
Msg 7391, Level 16, State 2, Procedure ORSmainscSpProcssHdrSav, Line 543
The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "server ip" was unable to begin a distributed transaction.

sql server-windows server 2008 R2  64bit
oracle linked server->unix server
i had checked the MSDTC connection in sql server and everything is fine. can anyone help? thanks in advance.


texts: Partitioning MySQL for "expired" transactions to improve performance

texts:
I am dealing with an existing application that uses the database as a sort of transaction log in several cases, for example orders or payments. These tables are large (20 - 60 million rows) and poorly designed -- string keys, denormalized and so on, and performance is poor.

In the cases I am considering, the records could be considered "expired" based on some business logic, and while the data is needed for historical and statistical transactions, at runtime, operations need only apply to unexpired rows.  Unexpired rows account for something like 5% of the whole data set.

It's runtime performance I want to improve.

We're not in a position to redesign the system yet, but it occurs to me that some combination of partitions or perhaps conditional indexes might help.  For example some data expires monthly, and we could run a monthly task to mark the records expired in a column of the table.  One table has a date column which appears to have been designed for this purpose, although it is not even indexed!

So if our runtime queries are scoped to include a condition like "expired_on is not null" my hope is that we could get near-steady-state performance using an index ... But is this a good case to use either or both of a conditional index (only unexpired rows) or partitions, perhaps creating a new one each month to include the prior month's now-expired records?

We could create some sort of archiving system, or roll up tables for historical stats, but for now I am looking to buy time :-). Any suggestions greatly appreciated.


texts: The connection is broken and recovery is not possible - sql server error

texts:
I am having the following sql server error, while creating tempDB files.
Any ideas?

  The connection is broken and recovery is not possible.  The client
  driver attempted to recover the connection one or more times and all
  attempts failed.  Increase the value of ConnectRetryCount to increase
  the number of recovery attempts. (Microsoft SQL Server, Error: 0)


texts:
What is the best way to approach the database design for a group-buying site?

If you are not familiar with group buying, people purchase a deal, and when "n" amount of people purchase the deal, the deal is activated and the product is shipped to the people who ordered the deal. However, the deal does not ship until "n" people buy it. Moreover, the requirement is that a single deal can have many "group-buys", meaning that once "n" amount of people buy the deal, the deal does not close, but rather restarts for "k" amount of times.

What I thought of was to have a "deal" table that has many "group" tables (where each of the groups represents one of the "k" number of times the deal can be group buyed), and each "group" table has "n" amount of order tables. The group is "activated" when the group table has the correct "n" threshold number of orders for that deal.

An example
    Deal ---> Group ---> order
       |            ----> order
       |
       ------> Group

Some problems I have with this design:

Upon initialization of the deal, you have to create all the "n" group rows that will be related to the deal, even though some of the "n" groups may not have any orders (especially if nobody purchases the deal). Thus, someone could easily specify to be able to group-buy the deal 100,000 times (making us create 100,000 rows), even if nobody group buys their product. Plus, if that person wants to delete their deal, we have to delete 100,000 rows.
Keeping track of the groups is a pain. When you process an order how do you know which group to add the order to? What if somebody (or everybody) wants a refund within a group, then which group do we add a new order to, since their might be one group with some people, and another group with some people.


texts: Schema review for system to advertise cars

texts:
Ok, so I am carrying out a rather simple task as part of a job requiretement. Here is the task in a nutshell:

Design a form for creating ad for selling car
Form has 2 have:

2 drop downs:

Car make
Car model (car model has to be dynamic, based on selected model

textaera for short description
Atleast 10 checkboxes with various properties (air con, electric windows etc)

Database has to be implemented in a way, so it is easy to search

Here is EER for the design I have at the moment:

And for those that prefer SQL, you can find it here

So couple points I want to ask:

Initially I have thought to only have reference to "car_model" in "posts" table. Cause technically I could access the car make via the "car_model" table. But then decided that for search purposes it would be easier to have both keys in this table, correct decision?

Another point, is with properties. Obviously this is a many to many relationship (many posts, can have many properties), so a relationship table is an obvious choice. But as far as searching goes, this complicates a bit, cause now for example I am searching for particular model, with particular properties I need to look up 2 tables. Is there better way? I was thinking to have an extra "text" field in the "post" table with comma separated list with all property ids, and use a `LIKE %property%" query (in addition to the relationships table). Any better solution?

And finally, just in general, any other suggestions given the specification?

Both answer's really good an helpful, but accepted the xuma202 answer, as he needs some of that sweet rep points more at the moment :D


texts: tempdb log file usage huge by sleeping status sessions but not relased the space

texts: We have a alert threshold of tempdb usage on drive level at 90% and getting lot of alerts. But the first problematic point is the tempdb usage occupied whole drive of 500GB among Log file only occupied 95% and not able to increase the log file for other works on tempdb. While we checked the open transactions we seen sleeping sessions are there 2days and 1day before time stamps and not released the space to other transactions. I think it's worst behavior the sleeping sessions blocked the space and released to the others. We need the script for Is really those sessions are occupieing the LOG file space and not released them. (I came to know jdbc drivers sessions suddenly drops and so that the sleeping sessions are not released the space to other transactions). Can you provide Is there any script to identify the same.


texts: Tsql help - doing a large update from CSV file to a table in ssms

texts:
I have been given a csv file with just ids in there and those ids are in a table in ssms with columns that require updating. Out of 90,000 ids in that table, only 83,000 of them require updating from the csv file.
the main table is:
ID  ENABLED    AGREED
1       1                       no
2       1                       no
3       1                       no
4       0                       yes

I know I can do an update on the table from the ids i have been given in the csv such as:
update table1
set enabled = 0,
agreed = 'yes',
where id in('1','2','3')

however, my problem is, there are 83,000 of them that require updating to those specific conditions and wanted to find the best way to do this. I have been told to write a script to import the CSV into a temp table, then join that temp table onto the main table and perform the update rather than hard code a list of id's in a script.
How can I do this? Does anyone know a better way?


texts: MySQL Cluster "ALTER ONLINE TABLE ... STORAGE MEMORY" doubt

texts:
I have a MySQL Cluster database where the data is both in-memory and written to disk.

I'm trying to understand the ALTER ONLINE TABLE ... STORAGE MEMORY statement, so that I can use this technique to minimize DDL operations downtime.

The documentation does not clarify my doubts.

I have a table like this

CREATE TABLE `mytable` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`nome` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=ndbcluster AUTO_INCREMENT=103 DEFAULT CHARSET=utf8

I'm adding a new field to the table with the ONLINE schema change

ALTER ONLINE TABLE `mytable` ADD `myfield` INT STORAGE MEMORY;

Query is successful, the new field exists and is correctly writable for all nodes.

If I ask the table schema to mysql

SHOW CREATE TABLE `mytable`;

mysql says

CREATE TABLE `mytable` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`nome` varchar(255) NOT NULL,
`myfield` int(11) /*!50606 STORAGE MEMORY */ DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=ndbcluster AUTO_INCREMENT=103 DEFAULT CHARSET=utf8

So what I understand is that I have a table that is written to disk but has one field that is not. Am I right?

Is this field and its contents saved with the MySQL Cluster Backup system?

If the Cluster globally dies, I'm going to loose the field and its content?

Thanks!


texts: SSIS 2008: Illegal characters in path when trying to rename a file

texts:
I have a task that should rename a file
I have a variable called RenameFileName
\\servername\csv\BACKUP\" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".csv

When I run the task I get the error
Illegal characters in path

Am I doing anything wrong? Below are a list of the variables I have tried
\\\\servername\\csv\\BACKUP\\" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".csv

\\servername\csv\BACKUP\" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".csv

"\\servername\csv\BACKUP\" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".csv"

"\\\\servername\\csv\\BACKUP\\" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".csv"

Please note that the following works:
\\\\servername\\csv\\BACKUP\\ABC.csv

This however does not work
\\\\servername\\csv\\BACKUP\\"+1+".csv

It errors as soon a I put in double quotes. Single quotes are fine and they get put in the filename
Thanks


texts:
Which are the minimum privileges required to get a backup of a MySQL database schema? I am interested only on the schema, not on the data.

Are SELECT privileges required?


texts: How to copy blob (binary data) from one DB to DB using a script?

texts:
We are trying to copy a blob (binary data) from one Database to another Database using a simple script. This is the steps we do.

Select the value using MSSQL Studio Client.
Copy it into the clipboard.
Paste it into anINSERT script using notepad.
Apply it into the destination database.

We notice that the value is now an ASCII that starts with 0x255.... which seems to be the hexadecimal representation of the binary data. Is there a way we can convert it back to binary during the insert?

Is there a better way to copy blob data using scripts? Assume, we don't have direct connection to destination Database.


texts: Window function vs group by method: find value for MAX ID

texts:
I'm trying to understand the performance issue with Window function when i select value for MAX ID

--Old group By method
SELECT
    tj.MainProcedure ,
    tj.StartTime ,
    tj.EndTime ,
    tj.Threshold,
    tj.LastStatus ,
    tj.rc
    ErrorMessage
FROM dbo.tbl_TraceJobExecutions tj WITH (NOLOCK)
INNER JOIN
    (
        SELECT b.MainProcedure,
        ErrorMessage
        FROM
            (
            SELECT MainProcedure, MAX(RowID) AS MaxRowID
            FROM tbl_TraceJobExecutions_ErrorHandling WITH (NOLOCK)
            GROUP BY MainProcedure
            )a
        INNER JOIN tbl_TraceJobExecutions_ErrorHandling b WITH (NOLOCK)
        ON b.MainProcedure = a.MainProcedure
        AND b.RowID = a.MaxRowID
        ) eh
ON eh.MainProcedure = tj.MainProcedure

ORDER by tj.MainProcedure, tj.EndTime DESC

--VS Window Function
SELECT tbl_TraceJobExecutions.*,MaxVal.ErrorMessage FROM tbl_TraceJobExecutions
INNER JOIN (
    SELECT
        MAX(tbl_TraceJobExecutions_ErrorHandling.RowID) OVER (PARTITION BY tbl_TraceJobExecutions_ErrorHandling.MainProcedure) AS MaxID,
        tbl_TraceJobExecutions_ErrorHandling.MainProcedure,
        RowID,
        ErrorMessage
    FROM tbl_TraceJobExecutions_ErrorHandling
) AS MaxVal ON MaxVal.MainProcedure = tbl_TraceJobExecutions.MainProcedure AND

MaxVal.RowID = MaxVal.MaxID
ORDER by tbl_TraceJobExecutions.MainProcedure, tbl_TraceJobExecutions.EndTime DESC

The IO statistic:

  (10 row(s) affected)
  Table 'tbl_TraceJobExecutions'. Scan count 0, logical reads 46, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'tbl_TraceJobExecutions_ErrorHandling'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  (1 row(s) affected)

  (10 row(s) affected)
  Table 'Worktable'. Scan count 3, logical reads 1776, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'tbl_TraceJobExecutions_ErrorHandling'. Scan count 1, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'tbl_TraceJobExecutions'. Scan count 1, logical reads 79, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  (1 row(s) affected)

What is wrong with my code?
Thank you


texts: Is it possible to have MySQL slave outside firewall

texts:
For various reasons I need one of the databases from intranet MySQL server to be made available to (insecure) online server. Due to the requirements it was decided that a db dump will be used via cron to transfer data as the firewall does not allow any connections to be initiated from outside. Then MySQL Master-Slave solution was suggested. From what I have understood, the slave should be able to connect to the master via (preferably insecure) TCP protocol. Such a solution is preferable, however, via secure TCP connections without permitting the slave to initiate (possibly lost) connections to the master.

I would like to know if it is possible to have Master-Slave replication via secure TCP where connections are always initiated by the Master to the slave. The use case does not require too much data transfer, and data base size is within 5GB on a 1GBPS intranet. It is acceptable that there could be some delay in updating to the slave.


texts: What is the undo pass that SQL Server does during a RESTORE WITH STANDBY operation?

texts:
It's my understanding that SQL Server generates undo files when applying a restore to a database using the RESTORE WITH STANDBY.

From the MSDN documentation (Emphasis mine):

  The standby file is used to keep a "copy-on-write" pre-image for pages modified during
  the undo pass of a RESTORE WITH STANDBY. The standby file allows a
  database to be brought up for read-only access between transaction log
  restores and can be used with either warm standby server situations or
  special recovery situations in which it is useful to inspect the
  database between log restores. After a RESTORE WITH STANDBY operation,
  the undo file is automatically deleted by the next RESTORE operation.
  If this standby file is manually deleted before the next RESTORE
  operation, then the entire database must be re-restored. While the
  database is in the STANDBY state, you should treat this standby file
  with the same care as any other database file. Unlike other database
  files, this file is only kept open by the Database Engine during
  active restore operations.

  The standby_file_name specifies a standby file whose location is
  stored in the log of the database. If an existing file is using the
  specified name, the file is overwritten; otherwise, the Database
  Engine creates the file.

  The size requirement of a given standby file depends on the volume of
  undo actions resulting from uncommitted transactions during the
  restore operation.

What is the undo pass referred to at the beginning?

From what I understand, those are the operations written in the log file that haven't been committed. If this is correct, why are those operations on the log file if they haven't been committed in the first place, and why does the RESTORE WITH STANDBY operation need to store them someplace to be able to bring the database up for read-only access (in other words, why can't they just be thrown away)?


texts: Group data by non-unique keys by distinct time range


texts: Query showing a number instead of the column value

texts:
I have a query (query_clients) based on a table (Person). One of the columns I am pulling is a StateAbbr column, which is a lookup to the StateAbbr table. The primary key in the StateAbbr table is the abbreviation itself instead of a numeric key. However, when I run the query, the StateAbbr column shows up as a numeric value instead of the actual value from the column.

I have tried joining on the StateAbbr table to pull the actual column from there, but I must be doing it wrong, because it causes the whole query results to go blank. I have also tried making sure the bound column is correct when I create a form off the table, but changing that value just made the column show up blank instead.

Any suggestions? Thank you.


texts: How to switch Quiescing to Normal Mode in Oracle?

texts:
I have two instance database Oracle, they are ORCL11 and ORCL10,.... I've configured them, it looks like correct. Then, I connect to replication user admin ORCL10

conn repadmin/repadmin@orcl10

And run SQL command in replication admin user ORCL10,...

BEGIN DBMS_REPCAT.RESUME_MASTER_ACTIVITY (gname=>'scott_repg');end;/

Then, i connect to replication admin user ORCL11 to see gname status.

conn repadmin/repadmin@orcl11;
select gname,status from dba_repgroup;

It display gname = SCOTT_REPG and status = NORMAL,.... but if connect to replication admin ORCL10

conn repadmin/repadmin@orcl10;
select gname,status from dba_repgroup;

It display that gname = SCOTT_REPG and STATUS = QUIESCING, I think it does not have any impact, so I test my replication on ORCL10, for example :

conn system/ok@orcl10;
insert into DEPT values (101,'Financial');
select*from DEPT;

It works on table DEPT ORCL10 (1 row created), but when i switch to ORCL11 then run simple sql query :

conn system/ok@orcl11;
select*from DEPT;

it doesn't show anything, i've searched it on Google but there is nothing tutorial how to fix that, I think the problem is about how to switch QUIESCING to NORMAL mode at ORCL10. I've try this way to fix it :

ALTER SYSTEM QUIESCE RESTRICTED;
ALTER SYSTEM UNQUIESCE;

But it still doesn't solve the problem,..... Would you help me to fix this problem ??? thanks !!!


texts: SQL Server: deadlocked on lock communication buffer resources

texts:
What could be possible reason for this deadlock type? (not deadlock in general)

  Lock communication buffer resources

Is this indicated system is low in memory and buffers count ran out of limit?

Detailed Error:

  Transaction (Process ID 59) was deadlocked on lock communication
  buffer resources with another process and has been chosen as the
  deadlock victim. Rerun the transaction


texts: MongoDB:Too many connections from Secondary

texts:
We have a 3 node replica set running on MongoDB 3.2, and having an issue with Replication.

On Primary server, connection count will keep increasing as one of the secondary will keep making new connections to primary until the connection count is too high(30000+) and primary is down.

[root@EKAUSHALPRD mongo]# netstat | grep 27017 | wc -l

329

[root@EKAUSHALPRD mongo]# netstat | grep 27017 | wc -l

576

[root@EKAUSHALPRD mongo]# netstat | grep 27017 | wc -l

576

[root@EKAUSHALPRD mongo]# netstat | grep 27017 | wc -l

576

[root@EKAUSHALPRD mongo]# netstat | grep 27017 | wc -l

588

[root@EKAUSHALPRD mongo]# netstat | grep 27017 | wc -l

590

[root@EKAUSHALPRD mongo]# netstat | grep 27017 | wc -l

596

[root@EKAUSHALPRD mongo]# netstat | grep 27017 | wc -l

602

[root@EKAUSHALPRD mongo]# netstat | grep 27017 | wc -l

770

[root@EKAUSHALPRD mongo]# netstat | grep 27017 | wc -l

1492

[root@EKAUSHALPRD mongo]# netstat | grep 27017 | wc -l

1895

There is not a single query or write operation running on the server.All we have is these kind of requests keep coming from one of the secondary while the other one works fine:

   query: { ismaster: true }  ntoreturn: -1 ntoskip: 0

10.117.241.104:27017  <<--  10.117.241.104:41470   480 bytes  id:83e9c1 8645057 - 7129
        reply n:1 cursorId: 0

In mongo logs we see first a new connection is established and then
Successfully authenticated as principal username on dbanme


texts: Database Engine and SSISDB architecture advice

texts:
I'm seeking some architecture advice involving the database engine and Integration Services components of SQL Server 2017.  We have an existing SQL Server environment which is due for an upgrade and has historically been used in a less than ideal way.  With an upgrade to a new SQL Server environment now on the horizon, as a systems administrator I want to use this opportunity to rethink the existing architecture to provide a more robust flexible environment going forward.

The Current Environment

A SQL Server 2012 Enterprise instance running on a 2-node Windows Failover Cluster.  The database engine is clustered and DTS packages are stored in a clustered volume and these packages are executed as jobs via the SQL Server Agent.  They do not use the explicit Integration Services component

The Problem

Staff who administer the data warehouse and create/manage the DTS packages have traditionally insisted on working directly on the clustered server nodes via RDP.  This creates access issues with the systems admins who are responsible for the servers themselves.  The data staff also require a variety of software tools which has led to persistent disk space issues over time.  Additionally, many of these software tools are not cluster-aware and are only installed on one node, effectively leaving us with a crippled cluster right from the start.  The result is that any time there is a planned or unplanned failover, there is pressure to get the original primary node back online asap because most jobs are failing because they rely on local software which is not cluster-aware.

A Proposed Solution

Install the database engine alone on its own cluster with no RDP access allowed by any staff.  They will receive just the SQL Server clustered instance endpoint URL.  Install the Integration Services component on a separate server and propose they begin using the SSISDB catalog to store their packages instead of the file system.  My understanding is that there are many advantages to using the SSISDB.

Questions

This only shifts the RDP access issue to another server.  Because the data staff collaborate in real-time on packages and rely on all the same software tools, they argue they must have direct access to the server in order to provide a shared identical environment.  My belief is that there must be a more modern design approach where they can use their development tools on their local workstations and connect to SSIS using the SSMS, where the jobs will be published and scheduled/executed.  Any software that packages rely on (ftp, ssh, perl, python, etc) would be installed and managed by server admins on the SSIS.  Since packages would be stored in the SSISDB, there would no longer be a need for the shared storage which has traditionally housed the package files and this should remove the need for any direct RDP access.  Is my belief logical or is there still some argument for data developers to have direct server access?
Now the SSISDB becomes a single point of failure since the packages are no longer part of the failover cluster as they traditionally have been.  How would I build high availability into this component?  I've read about SSIS Scale Out features but that seems more about balancing load rather than fault tolerance.  I've begun reading about AlwaysOn availability groups, could that be a solution?

Any and all design advice is much appreciated.  Thanks.


texts: I have 7 pages in my SSRS report and also I'm used page break option. Is there any way available to export all the pages in single excel sheet. Please provide me solutions


texts: SQL Server Compact - Will it perform well with heavier traffic sites?

texts:
I'm using Orchard CMS, and on initial setup, the default setting is to use SQL Server Compact. I'm not too knowledgeable about different database types and performance.

So, will SQL Server Compact be able to handle a website getting 1,000+ hits? Assuming all else with the hosting provider is exceptional (bandwidth, up-time, etc).


texts: How to set foreign key on two tables in MySQL?

texts:
I am struggling in resolving a db design for MySQL 5.7 to solve the following scenario:
There are category dimensions that change each day, I store them in category_entities. They belong to a category which relates to a merchant and his products:
merchants
merchant_id | merchant
1           | amazon
2           | ebay

PK: merchant_id

products
prod_id    | merchant_id | product
1          | 1           | jumper big
2          | 1           | hat red
3          | 2           | shoe black
3          | 1           | shoe black2

PK: merchant_id, prod_id

categories
cat_id     | merchant_id | category
1          | 1           | jumpers in green
2          | 1           | hats for woman
3          | 1           | shoes
4          | 2           | jumpers
5          | 2           | hats for children
6          | 2           | shoes

PK: cat_id

category_entities (30M rows)
cat_id | prod_id | characteristica | date
1      | 1       | 23              | 2021-01-07
2      | 2       | 22              | 2021-01-07
1      | 2       | 22              | 2021-01-08
2      | 3       | 01              | 2021-01-08
3      | 1       | 22              | 2021-01-08
4      | 2       | 01              | 2021-01-08

PK: cat_id, prod_id, date

So the category table defines what categories exists and the category_entities talbe shows the value for each day
How can I set a foreign key on the tables (categories, category_entities) on table products using prod_id, merchant_id. Those fields are the primary key of product.
I as reading about polymorphic assosiations but I am not sure this is one and how to solve this.


texts: Visual Studio extract database values to excel with header text!

texts:
I am trying to extract the values from an mdf database to excel, but i can't display the header texts(title fields). I can only view the values. In order to make this work i need a query like this below, but it seems that select with cte
gives me an error.


texts: Elasticsearch and product characteristics

texts:
I have a small question about elasticsearch. Let's say I have a database with products to sell, like an ecommerce.

I know that elasticsearch can help me to find the best product with, for instance a full text search on the description and sorting by score.

Let's say someone is looking for sport shoes, and I don't sell any, but I do sell shoes.
What would be the best way to show the user I don't have any? The full text search will for sure give me everyday shoes in the results, since the word "shoes" is in the query, but I would prefer the user to know I don't sell any rather than seeing products that don't match his query.

Is it as simple as setting a score threshold and playing with it until having good results, or are there specific settings?

I'm also interested in generic answers having nothing to do with elasticsearch.

Thanks a lot!


texts: Service Broker and procedure which contains Execute as

texts:
We have queue on Service Broker:

ALTER QUEUE [dbo].[my_queue] WITH
        STATUS = ON ,
        RETENTION = OFF ,
        ACTIVATION (
                      STATUS = ON ,
                      PROCEDURE_NAME = [dbo].[my_detect_event] ,
                      MAX_QUEUE_READERS = 1 ,
                      EXECUTE AS N'dbo'
                    ),
        POISON_MESSAGE_HANDLING (STATUS = OFF)

Procedure has code (i cut some text with RECEIVE, because it's not linked to the issue):

ALTER PROCEDURE dbo.my_detect_event
        AS
            BEGIN
                ......cut text with RECEIVE TOP (1) message_body FROM my_queue INTO @NotificationStore
                --EXECUTE AS LOGIN = 'user_sa'
                INSERT INTO dbo.log
                        ( time, text)
                VALUES  ( GETDATE(),
                          'test'
                          )
                ......
            END

user_sa - has SA rights.
If the service fires an event, then the table data is inserted successfully.
But if you uncomment the line EXECUTE AS LOGIN = 'user_sa', then appears the error:

  Cannot execute as the server principal because the principal "user_sa" does not exist, this type of principal cannot be impersonated, or you do not have permission.

Pls help to find solutions.
Thanks in advance!

Technical info:
Microsoft SQL Server 2012 - 11.0.5582.0 (X64)  Enterprise Edition


texts: SQL Server Replication breaks up after doing DBCC CheckDB with Allow data loss on Primary

texts:
We have one way transactional replication enabled on SQL Server 2008 databases. Due to some issues we had to run DBCC checkDB, bringing the database to single user mode. When we complete the DBCC activity and enable replication again, replication won't work. We had to initialize snapshot again for replication to work.

Can you please help me understand why replication is breaking in this scenario? I understand the DBCC drops some rows and indexes in a table, but as per my understanding when we enable replication these changes should be propagated to subscriber without any issues.

When I checked with my senior team member, I was told this is due to LSN mismatch, but I am not able to understand logically.

Any help is much appreciated.


texts: How do you import an exported dump file into a pre-built oracle 11g virtual machine?

texts:
I have successfully downloaded and installed this pre-built virtual machine with Oracle 11g. I am able to access the scott/tiger database and view table contents successfully.

I am currently trying to import a dump file I exported using the "expdp"

The expdp command worked perfectly and generated a dump file of size 114MB.

I secure copied the 114MB dump file to my pre-built virtual machine and attempted to run the command $ORACLE_HOME/bin/impdp

however no such command exists on this pre-built virtual machine.  Does this mean I cannot import anything into the prebuilt virtual machine?

How do I import a simple dump file into the pre-built virtual machine?


texts: Postgressql text search: pg_trgm index with order by going very slowly

texts:
I have a really slow query(~30 seconds) on a large text search DB that I could really use some help with.

I'm searching for ordered text, in a long file path string. So the path could be: folder1/cat 123/dog 234

I'm using ilike '%search_term_1% %search_term_2%' to do searches. So cat dog would return the result above.

I have a table with two columns:

create table file (
  id bigserial primary key,
  path varchar(2048) not null,
  peers integer);

I want to do a text search on path, and order by peers desc nulls last

I've created the following two indices to speed up the queries:

create index idx_file_path_tri on file using gin (path gin_trgm_ops);
create index idx_file_peers on file(peers nulls last);

Here's the explain anaylze results:

explain analyze select * from file where path ilike '%cat%' order by peers desc nulls last limit 15;

It looks like I need a compound index with the gin, but it won't let me build one based on peers desc nulls last...

Limit  (cost=7512.09..7512.13 rows=15 width=126) (actual time=342729.147..342729.153 rows=15 loops=1)
   ->  Sort  (cost=7512.09..7516.48 rows=1753 width=126) (actual time=342729.144..342729.145 rows=15 loops=1)
         Sort Key: peers DESC NULLS LAST
         Sort Method: top-N heapsort  Memory: 27kB
         ->  Bitmap Heap Scan on file  (cost=809.59..7469.09 rows=1753 width=126) (actual time=2143.088..342610.395 rows=219
580 loops=1)
               Recheck Cond: ((path)::text ~~* '%cat%'::text)
               Heap Blocks: exact=38190
               ->  Bitmap Index Scan on idx_file_path_tri  (cost=0.00..809.15 rows=1753 width=0) (actual time=2108.286..2108
.286 rows=223590 loops=1)
                     Index Cond: ((path)::text ~~* '%cat%'::text)
 Planning time: 0.328 ms
 Execution time: 342729.330 ms
(11 rows)

Note: the queries go extremely quickly without the order by.


texts: Why are bulk multi-column key queries so slow in MySQL?

texts:
(For this question, I am using AWS/Aurora MySQL with a reasonably-spec'd RDS instance)

Consider the following schema:

Table T:
    col0: the usual autoincrement primary key
    col1: varchar
    col2: varchar
    col3: varchar
    col4...N: various data

Consider that there is a unique index on:

<col1, col2, col3>

And a non-unique index on:

<col1, col2>

And consider the following query:

SELECT * FROM T
WHERE
    (col1 = 'val1' AND col2 = 'id1') OR
    (col1 = 'val2' AND col2 = 'id2') OR
    ...
    (col1 = 'valN' AND col2 = 'idN');

I would (perhaps naively) expected MySQL to figure out that each element of the OR set matched the (non-unique) index, and performed the query in the way it would have if I had said:

WHERE col0 in (v1, v2, ... , vN)

But it doesn't seem to do that: the timing for these two queries is WAY OFF, on the order of 10x slower for the "or of ands" query. EVEN WITH the secondary key lookup, and the fact that it's a string column lookup, 10x seems a bit severe. Note that EXPLAIN claims to be using the correct/expected index whether I specify (col1, col2) or (col1, col2, col3)

Please note also that:

SELECT * from T
WHERE
    col1 in (list1)
AND
    col2 in (list2);

Is also slow when there are a lot of different values in list1 and list2. Doing an "and" for the three columns is almost intractably slow.

Perhaps not surprisingly, this query works better than the "or of ands" when list1 is of length 1.


texts: Understanding SQL Developer user accounts

texts:
I have installed this Docker image today: https://hub.docker.com/_/oracle-database-enterprise-edition
I followed the instructions here: https://www.youtube.com/watch?v=ciYsDbBx80s.  Basically I ran the following commands:
docker pull store/oracle/database-enterprise:12.2.0.1
docker run -d -p 1521:1521 --name oracle store/oracle/database-enterprise:12.2.0.1
docker exec -it 0500ab7ee75c bash -c "source /home/oracle/.bashrc; sqlplus /nolog"

Then in sqlpus:
connect sys as sysdba
Enter password: anyrandompassword
alter session set "_ORACLE_SCRIPT"=true;
create user dummy identified by dummy;
SQL> GRANT ALL PRIVILEGES TO dummy;
SQL>ALTER USER sysdba IDENTIFIED BY Password1 ACCOUNT UNLOCK;

I have two questions:

Why is the SQLDeveloper password for the dummy user: dummy.  I have not specified this.

It appears that I can now login to SQL Developer with the following credentials (as described here: https://stackoverflow.com/questions/9129664/unable-to-log-in-to-database-as-sys-with-oracle-sql-developer):
Username: SYS AS SYSDBA
Password: Password1

However, when I run the following command in sqlplus
connect sys as sysdba

I am then prompted for a password, however any password appears to work.


texts: Hiding or masking sensitive information in the SQL Server audit log

texts:
I'm looking into auditing of who are accessing the data in all tables in a database. We do have the additional requirement that we in the log can't have any information that can be used to identify a person.

The first part can easily done using SQL Server audit to log all select, insert and delete statements for a specified database. It is the second part that I have problem with. The whole SQL statement including any personal information in e.g the where clause will be stored in the 'statement' column. This meaning that whoever that have access to the audit log will be able to see this information and identify the person in question. We do not need the information that are stored in the 'statement' column. The information stored in 'database_name', 'schema_name' and 'object_name' columns are enough for us.

My question is are there any way to prevent SQL Server audit to store the SQL statement at all or at least in clear text in the 'statement'column?


texts: Mysqld draining memory, using way more then assigned and not giving any back

texts:
This is a production server.
Whenever mysqld is restarted the memory consumption slowly builds up to about 75 to 90% of my total memory (while only 8 out of 32GB is allocated to mysqld).
Sometimes this takes 5 hours, sometimes this takes 16 hours.
Usually it sticks around that percentage for a good amount of hours.
If that was just it it would be fine, but after some time it then starts using even more memory, Until it overflows, causing my 32GB swap drive to fill up to 100% sometimes.
Now things start to rapidly slow down to a crawl with mostly timeouts. It does recover after some time to the point it will no longer timeout for a while, but the memory never frees up so its constantly on the verge of dropping out again.
Anyone have an insight on how can this be?
Why is mysqld using so much memory and not returning any free mem?
And why is it not respecting at all the buffer limits that were set?
I have played around for some time with the mariadb.conf, but even reverting all back to default doesn’t seem to have much impact on the memory usage.
Running
mysqld  Ver 10.3.27-MariaDB-0+deb10u1

top stats
mysql     20   0   33.2g  25.1g   3760 S 521.6  80.1   1122:26 mysqld

Memory/buffer specs from mysql tuner
[--] Physical Memory     : 31.3G
[--] Max MySQL memory    : 8.0G
[--] Other process memory: 1.4G
[--] Total buffers: 7.5G global + 2.9M per thread (151 max threads)
[--] P_S Max memory usage: 104M
[--] Global Buffers
[--]  +-- Key Buffer: 128.0M
[--]  +-- Max Tmp Table: 256.0M
[--] Query Cache Buffers
[--]  +-- Query Cache: OFF - DISABLED
[--]  +-- Query Cache Size: 0B
[--] Per Thread Buffers
[--]  +-- Read Buffer: 128.0K
[--]  +-- Read RND Buffer: 256.0K
[--]  +-- Sort Buffer: 2.0M
[--]  +-- Thread stack: 292.0K
[--]  +-- Join Buffer: 256.0K

Mariadb conf
skip-name-resolve
performance_schema      = ON
query_cache_type        = 0
query_cache_size        = 0
tmp_table_size          = 256M
max_heap_table_size     = 256M
innodb_log_files_in_group       = 4
innodb_buffer_pool_size        = 7G
innodb_status_file                      #extra reporting
innodb_file_per_table                   #enable always
innodb_flush_log_at_trx_commit  = 2     #2/0 = perf, 1 = ACID
innodb_table_locks              = 0
innodb_lock_wait_timeout        = 60
innodb_thread_concurrency       = 24
innodb_commit_concurrency       = 2
innodb_log_file_size   = 384M
innodb_buffer_pool_instances = 7


texts: Is it normal to share CPUs on a VM of SQL Server?

texts:
Our IT set up a SQL Server as a VM on a big VMWare box that contains other VMs.
The CPUs are setup as shared.  As a result, any queries that might need multiple CPUs take 30x longer than if I limited it to a single CPU.  Example:

SELECT TOP 2000 lwa.Message INTO #foo
FROM dbo.LogWidgetsAPI lwa (NOLOCK)
ORDER BY lwa.TimeStamp

vs

SELECT TOP 2000 lwa.Message INTO #foo
FROM dbo.LogWidgetsAPI lwa (NOLOCK)
ORDER BY lwa.TimeStamp
OPTION (MAXDOP 1)  ------------- Force it to run on a single CPU

1st example uses parallelism and takes about 30 seconds or so.  2nd one forces use of a single CPU and take 20 milliseconds.

Note: after running the single CPU query I go back to the running the multi-cpu one and the timing & plan is the same - so I don't think the issue is related to "cold cache" vs "warm cache"

So my theory is that because first query uses multiple CPUs, it must wait till all CPUs in question are idle, and therefore it just waits.

So my question.  Should SQL Server VMs have dedicated CPUs or shared ones are normal?

Here is the plan which uses parallelism.
Here is the plan which forces a single CPU.


texts: Select all from table using procedure oracle

texts:
I am trying to execute the select command in a procedure.Initially i retrived all of the columns of the table in the procedure using cursor. The real problem comes when I am trying to access the elements of the coloumn. It is printing the names of the columns again without printing the elements of the columns and also with an error!

set serveroutput on;
CREATE OR REPLACE PROCEDURE show_all1
AS
cursor gen_row is
select COLUMN_NAME from ALL_TAB_COLUMNS where TABLE_NAME='TESTNIRMAL';
cursor gen1_row(i varchar) is
select i  from testnirmal;
a1 varchar2(22);
a2 varchar2(10);
BEGIN
open gen_row;
for data in(SELECT * from testnirmal)
loop
fetch gen_row into a1;
open gen1_row(a1);
fetch gen1_row into a2;
dbms_output.put_line(a2);
close gen1_row;
end loop;
close gen_row;
close gen1_row;
END show_all1;
/

DEF_ID and ID1 are the two columns in my table and the table name is testnirmal.
The procedure is created without any compilation errors.
But while executing the procedure I am getting this.

  DEF_ID
  ID1 BEGIN show_all1; END;

  ERROR at line 1: ORA-01001: invalid cursor ORA-06512: at "USER.SHOW_ALL1", line 20 ORA-06512: at line 1


texts: Using T-SQL, is it possible to a split a result set or table and then insert into two different temp tables?

texts:
I have one table that holds payment data. I'd like to insert orders that ended in a declined state into #tmp_Declined. Then I'd like to insert payments that were originally declined but then authorized when using an alternative paymethod into #tmp_Authorized.

I can do this using 2 select into statements with slightly different where clauses but using the same base table.

I considered an except example like the following:

select *
into #tmp_Declined
from Payments

except

select *
from Payments
where WasOrginallyDeclined = 1
and MostRecentPaymentAccepted = 1

This approach though still populates one temp table at time.Is there a reserved word or query "trick" that will insert into #tmpA where conditions met and then insert into #tmpB whatever wasn't inserted in #tmpA?

I'd like to be able to write to 2 tables with one transaction.


texts: What AWS instance to choose for write-heavy PostgreSQL database server

texts:
We are now having problems trouble to tackle the intensive traffic to our servers during peak hours; see this related question on tuning the DB. At
the moment we are using a smaller cloud service for the server hosting. Currently we are looking
at the possibility to move our server to AWS. The problems we are having are mainly related
to our database server and therefore we would like to have AWS-instance which is significantly better
than the one we are using now.

The stats for the cloud server where the database server is located are as follows:

8 CORES
16GB RAM
100 GB, RAID-10 * SAS 15.000 RPM

Is there anything in particular you should think about when choosing what amazon instance to use
for a PostgreSQL database server?


texts: Configure MSSQL relations for a multi tenant

texts:
I have some issues configuring the relations between 3 entities.
To put into contest.
Tenant - the entity without the others cannot exist
DocumentSettings
Font
A Tenant can have multiple Fonts (1-*). I have a section which allows for different settings of a document to be set e.g. (heading, paragraph, footer ...) using a different Font.
The relation between a Tenant and Document Settings is a 1-0/1, Tenant can exist by itself, and it may have at most one DocumentSettings.
And this is what I cannot figure how to do, each column entry of the DocumentSetting can have at most 1 Font and should have a reference to it.
Representation
   tbl.Tenant        tbl.Document                    tbl.Font
   TenantId: PK      TenantId:          PK,FK        FontId: PK
                     Heading_FontId:    FK:
                     SubHeading_FontId: FK:
                     Body_FontId:       FK:

sample data for tbl.Document
    TenantId  Heading_FontId  SubHeading_FontId   Body_FontId

Valid
       1           1              null               2
       2          22              22                 11
       3          null            null               null

Invalid
       4           22              11                22       => tenant with id 4 ok, but FontIds are invalid, tenant with id 2 ONLY, can use these FontIds
       1           2               4                 5        => not possible , tenantId 1 already exists


texts:
I have Percona fork of MySQL 5.7 installed where I have defined innodb_buffer_pool_size in /etc/percona-server.conf.d/mysqld.cnf as follows

innodb_buffer_pool_size = 10G

But, when I login to MySQL prompt and run command show global variables like 'innodb_buffer_pool_size' it shows as follows

mysql> show global variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)

and no matter what value I set in /etc/percona-server.conf.d/mysqld.cnf for innodb_buffer_pool_size the value for innodb_buffer_pool_size in MySQL prompt remains same.

Also, when I run show global variables like 'innodb_buffer_pool_%size' in MySQL prompt it shows as follows

mysql> show global variables like 'innodb_buffer_pool_%size' ;
+-------------------------------+-----------+
| Variable_name                 | Value     |
+-------------------------------+-----------+
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_size       | 134217728 |
+-------------------------------+-----------+
2 rows in set (0.00 sec)

Can someone tell me why innodb_buffer_pool_size value is not changing? while I have enough memory on my server.


texts:
As part of a SSIS ETL process, I have multiple staging tables. I have used xxxx just to resemble data:
e.g.
tblStage1

Field_ABC  Field_ZXC  Field_QWE
 xxxx       xxxxxxxxx   xxxxx

tblStage2

Field_PLM   Field_WER   Field_YTE
 xxxxx       xxxxxx      xxxxxxx

I then have the 'real' tables that they need to be inserted into:

tblDest1
FldABC    FldZXC    FldQWE

tblDest2
FldPLM  FldWER   FldYTE

Using info from the Information Schema I have built a mapping table that details which table and field from the staging tables map to which real table:
tblMapping:

StageTble   StageColumn   RealTable    RealCol    OrdinalPosition
tblStage1   Field_ABC     tblDest1     FldABC     1
tblStage1   Field_ZXC     tblDest1     FldZXC     2
tblStage1   Field_QWE     tblDest1     FldQWE     3
tblStage2   Field_PLM     tblDest2     FldPLM     1
tblStage2   Field_WER     tblDest2     FldWER     2
tblStage2   Field_YTE     tblDest2     FldYTE     3

How do I loop through each staging table and insert into the real tables\columns based on the values in tblMapping?

Would this be possible via a Cursor and a Loop?

Any help would be greatly appreciated.


texts:
I have a 3 members replica set version 2.6.1 with following configuration

                    "_id" : 0,
                    "host" : "10.125.16.22:30000",
                    "priority" : 2

                    "_id" : 1,
                    "host" : "10.125.16.22:40000"

                    "_id" : 2,
                    "host" : "10.125.16.22:60000",
                    "priority" : 0,
                    "slaveDelay" : 3600,
                    "hidden" : true

And i want id:2 to sync from id:1.

I execute

db.adminCommand( { replSetSyncFrom: "10.125.16.22:40000" } )

and got

"syncFromRequested" : "10.125.16.22:40000",
"prevSyncTarget" : "10.125.16.22:30000",
"ok" : 1

But on rs.status the sync is still syncingTo : "10.125.16.22:30000"

I found on documentation:

  Changed in version 2.4: The sync target falls more than 30 seconds
  behind another member of the replica set; the mongod will revert to
  the default sync target.

Is this what happened in my case? if yes is there any workaround?


texts: Where's my text highlighting in SSMS 2014?

texts:
In visual studio I find it very helpful to find where a variable is being used by highlighting it and all occurrences of that var will appear highlighted as well.

Is there a way to enable this feature in Sql Server Management studio 2014 while viewing scripts?

Text highlighting is mentioned here.

Thanks!


texts: Looking for suggestions to Migrate Amazon RDS Large Instance to non-amazon host - Live

texts:
We have an Amazon RDS Large instance with a database of about 20GB that needs to be migrated without having to take our website down or minimize downtime to less than a couple of hours.

And I am looking for strategies in which this could work effectively? Our RDS has both innodb and myisam tables.  How possible is this to do while live without corrupting data and what kind of time do you think it would take to migrate a 21 Gb db? Would it be wise to migrate first to a ec2 server so we would have more capabilities? Can you create a read-only replication of the main RDS and clone that to the EC2 for more protection?
Thoughts?


texts: I have a [Tag] table will associated with multiple entity tables, should I create mapping table for each or only one table?

texts:
The [tag] table schema:
___________________________________
|column |type        |note        |
|-------+------------+------------|
|id     |bigint      |primary key |
|name   |varchar(20) |            |
-----------------------------------

the entity1 tag associate table [entity1_tag_map] schema:
__________________________________
|column     |type   |note        |
|-----------+-------+------------|
|entity1_id |bigint |primary key |
|tag_id     |bigint |primary key |
----------------------------------

but I have more than 5 entity need to associate to tag table. it means I will create 5 or more table like this.
It will cause some trouble if I make some operations on the tag table (such as merging or deleting) - I have to update all the mapping table, and also need to modifed the code about [tag] table when continue to development.
The other idea is only create one table, and add a column for entity type:
_________________________________________
|column      |type         |note        |
|------------+-------------+------------|
|entity_type |nvarchar(50) |primary key |
|entity_id   |bigint       |primary key |
|tag_id      |bigint       |primary key |
-----------------------------------------
data:
_________________________________
|entity_type |entity_id |tag_id |
|------------+----------+-------|
|entity1     |1         |1      |
|entity1     |1         |2      |
|entity1     |2         |2      |
|entity1     |2         |9      |
|entity2     |1         |3      |
|entity3     |1         |1      |
|entity3     |1         |4      |
---------------------------------

Is this better than the previous one?
For example, I need to delete a tag, so I need to delete reference at first.
The sql in the second case is
delete [entity_tag_map] where [tag_id]=@tagid

But the first case is
delete [entity1_tag_map] where [tag_id]=@tagid
delete [entity2_tag_map] where [tag_id]=@tagid
delete [entity3_tag_map] where [tag_id]=@tagid
delete [entity4_tag_map] where [tag_id]=@tagid
delete [entity5_tag_map] where [tag_id]=@tagid
...

delete [entityN_tag_map] where [tag_id]=@tagid

I always need to do somethings when the project add a new entity.


texts:
I have a table showing which roles recursively grant access to which resources within a database. For example:

The Default_Role grants access to the App_Role, the App_Role grants access to the Security_Role, and the Security_Role grants access to three tables (customers, sales, users). So a member of the Default_Role is granted all of these, but Default_Role is not granted access to the Sys_Role, and is not granted access to the System or Admin tables.
CREATE TABLE SQLTest(
     DBName     NVARCHAR(100) NULL
    ,Privilege    NVARCHAR(100) NULL
    ,PrivilegeType NVARCHAR(100) NULL
    ,PrivilegeDetail NVARCHAR(100) NULL
    ,TableName NVARCHAR(100) NULL
)

INSERT INTO SQLTest
VALUES
('TSDB','Default_Role','Role','App_Role',NULL),
('TSDB','App_Role','Role','Security_Role',NULL),
('TSDB','Sys_Role','Role','Security_Role',NULL),
('TSDB','Security_Role','Table','Customers','Customers'),
('TSDB','Security_Role','Table','Sales','Sales'),
('TSDB','Security_Role','Table','Users','Users'),
('TSDB','Sys_Role','Table','System','System'),
('TSDB','Sys_Role','Table','Admin','Admin')

What is the best way to flatten this out so you can see all the access (roles and tables) granted to the Default_Role, without showing additional access not granted to the Default_Role? Like this:

Tried putting together this example, but doesn't work.
http://www.sqlfiddle.com/#!18/f9a27/2


texts: MySQL slow query log to identify queries that scan tables

texts:
MySQL seems not to have setting to show you queries that use SOME indexes but still also do table scans

We have some badly performing queries and were hoping to find them systematically but the log_queries_not_using_indexes setting seems only to look for queries that use NO INDEXES AT ALL, rather than using full table scans.

Anyone to confirm? Is there a new option coming?


texts: Access is denied for SQLDumper.exe in MS SQL Server

texts:
I want to run SQLDumper.exe with process ID 6392 which is the ID of my Express edition. While running I get this error from the OS

access is denied

I've given full control to the SQL Express account name but still get the error. What access should I grant in order to make it work properly?
Thanks for your time!


texts: Calculate percentages by using JOINS result in postgresql

texts:
I am trying to calculate percentages by joining 3 tables data to get the percentages of positive_count, negative_count, neutral_count of each user's tweets. I have succeeded in getting positive, negative and neutral counts, but failing to get the same as percentages instead of counts. Here is the query to get counts:

SELECT t1.u_id,count(*) as total_tweets_count
, (SELECT count(*) from t1,t2,t3 c WHERE  t1.u_id='18839785' AND
t1.u_id=t2.u_id AND t2.ts_id=t3.ts_id AND t3.sentiment='Positive') as pos_count
, (SELECT count(*) from t1,t2,t3 WHERE  t1.u_id='18839785' AND
t1.u_id=t2.u_id AND t2.ts_id=t3.ts_id AND t3.sentiment='Negative') as neg_count
, (SELECT count(*) from t1,t2,t3 WHERE  t1.u_id='18839785' AND
t1.u_id=t2.u_id AND t2.ts_id=t3.ts_id AND t3.sentiment='Neutral') as neu_count
FROM t1,t2,t3 WHERE t1.u_id='18839785' AND t1.u_id=t2.u_id AND
t2.ts_id=t3.ts_id GROUP BY t1.u_id;

 u_id | total_tweets_count | pos_count | neg_count | neu_count
 -----------------+--------------------+-----------+-----------+-----------
 18839785|         88 |        38 |        25 |        25
 (1 row)

Now I want the same in percentages instead of counts. I have written the query in the following way but failed.

SELECT total_tweets_count
,pos_count, round((pos_count * 100.0) / total_tweets_count, 2) AS
pos_per
,neg_count, round((neg_count * 100.0) / total_tweets_count, 2) AS
neg_per
,neu_count, round((neu_count * 100.0) / total_tweets_count, 2) AS
neu_per
FROM (SELECT count(*) as total_tweets_count
,count(a.u_id='18839785' AND a.u_id=b.u_id AND b.ts_id=c.ts_id AND
c.sentiment='Positive') AS pos_count
,count(a.u_id='18839785' AND a.u_id=b.u_id AND b.ts_id=c.ts_id AND
c.sentiment='Negative') AS neg_count
,count(a.u_id='18839785' AND a.u_id=b.u_id AND b.ts_id=c.ts_id AND
c.sentiment='Neutral') AS neu_count

FROM t1,t2, t3 WHERE a.u_id='18839785' AND a.u_id=b.u_id AND
b.ts_id=c.ts_id GROUP BY a.u_id) sub;

Can anyone help me out in achieving as percentages for each user data?


texts:
Can I use local variables in a trigger if I expect it to replicate on a slave ?

I have a trigger that uses a local variable @userId that is set prior to the triggering query. So every time a certain query is run the variable @userId is set and the trigger can make use of it.

My question is, can I expect the bin log to work correctly if my binlog_format is STATEMENT ?

I know from the documentation that session variables will not work, but they do not mention local variables.

  in statement-based replication, session variables are not replicated properly when used in statements that update tables.


texts: Is there a plugin to add a map behind the spatial results?

texts: Using SSMS 18.5.1 and dealing with spatial results. I would like to visualize this data against a map of the world using Google Maps, Bing Maps, or any other mapping software. Is there a plugin or a way to accomplish this within SSMS?


texts:
I am creating a fully dynamic application but have ran into a bit of a hiccup. There are multiple 'undefined' fields that can be defined by the users. The only problem is redisplaying them. Currently they are showing as [UDF_0] to [UDF_7], i.e. :

SELECT [TABLE].[UDF_0],
       [TABLE].[UDF_1],
       [TABLE].[UDF_2],
       [TABLE].[UDF_3],
       [TABLE].[UDF_4],
       [TABLE].[UDF_5],
       [TABLE].[UDF_6],
       [TABLE].[UDF_7]
FROM [TABLE]

Would obviously display as:

UDF_0 || UDF_1 || etc...

What I would like to be able to do is display them something to this effect, but I can't for the life of me figure it out.

EXECUTE PROCEDURE [dbo].[GetProjectInfo] @Project varchar(100)

AS
BEGIN

SELECT [TABLE].[UDF_0] AS (SELECT [TBL_PROJECT_DESC].[UDF_0]
                           FROM [TBL_PROJECT_DESC]
                           WHERE [TBL_PROJECT_DESC].[PROJECT_NAME]=@Project),
       --etc....
FROM [TABLE]

Desired display would be :

Field Name 0 || Field Name 1 || etc...


texts: Why would a SELECT INTO cause an arithmetic overflow?

texts:
I am doing this

SELECT * INTO table1 FROM Table0

I get an arithmetic overflow on a datetime column (error converting to smalldatetime), however the destinations schema that got created has a datetime column and not a smalldatetime column..


texts: Parse a column with a "range" in a WHERE in SQL Server

texts:
I'm querying a table that has several alphanumeric key columns...

KEY1  KEY2  KEY3 ... SOMEDATA  SOMEOTHERDATA...
s001
s002
s003  s004  s005
s006

The keys are the PK from another table we need to join on, which is annoying but not difficult. But then they decided to do this...

s010-s105

Which means s010, s011...s015. Is there any way to do a query that breaks this out so I can find a given key, say s013, in these columns?


texts: Is there a quickish, straighforward way to measure Transactions Per Second on SQL Server 2000?

texts:
The sys.dm_os_performance_counters DMV introduced in SQL Server 2005 makes this task somewhat trivial. However, my Google-Fu has been blocked in finding a way to do this in SQL Server 2000.

How can I measure transactions per second in SQL Server 2000?


texts: SQL column=ALL(query): Equivalent meaning

texts:
I know that the expression:

WHERE column <> ALL(subquery)

could have been written as:

WHERE column NOT IN(subquery)

What would be the equivalent for

WHERE column = ALL(subquery)

I don’t think there would be any differences between dialects of SQL which support the ALL operator.